存储过程语法及实例

1、存储过程语法及实例】存储过程如同一门程序设计语言，同样包含了数据类型、流程控制、输入和输出和它自己的函数库。-基本语法-一.创建存储过程create procedure sp_name()begin.end二.调用存储过程1.基本语法：call sp_name()注意：存储过程名称后面必须加括号，哪怕该存储过程没有参数传递三.删除存储过程1.基本语法：drop procedure sp_name/2.注意事项(1)不能在一个存储过程中删除另一个存储过程，只能调用另一个存储过程四.其他常用命令1.show procedure status显示数据库中所有存储的存储过程基本信息，包括所属数据库，存

2、储过程名称，创建时间等2.show create procedure sp_name显示某一个mysql存储过程的详细信息-数据类型及运算符-一、基本数据类型：略二、变量：自定义变量：DECLARE a INT ; SET a=100; 可用以下语句代替：DECLARE a INT DEFAULT 100;变量分为用户变量和系统变量，系统变量又分为会话和全局级变量用户变量：用户变量名一般以开头，滥用用户变量会导致程序难以理解及管理1、 在mysql客户端使用用户变量mysql SELECT Hello World into x;mysql SELECT x;mysql SET y=Goodby

3、e Cruel World;mysql select y;mysql SET z=1+2+3;mysql select z;2、 在存储过程中使用用户变量mysql CREATE PROCEDURE GreetWorld( ) SELECT CONCAT(greeting, World);mysql SET greeting=Hello;mysql CALL GreetWorld( );3、 在存储过程间传递全局范围的用户变量mysql CREATE PROCEDURE p1( ) SET last_procedure=p1;mysql CREATE PROCEDURE p2( ) SELEC

4、T CONCAT(Last procedure was ,last_procedure);mysql CALL p1( );mysql CALL p2( );三、运算符：1.算术运算符+ 加 SET var1=2+2; 4- 减 SET var2=3-2; 1* 乘 SET var3=3*2; 6/ 除 SET var4=10/3; 3.3333DIV 整除 SET var5=10 DIV 3; 3% 取模 SET var6=10%3 ; 12.比较运算符 大于 12 False 小于 21 False= 小于等于 2= 大于等于 3=2 TrueBETWEEN 在两值之间 5 BETWEEN

5、 1 AND 10 TrueNOT BETWEEN 不在两值之间 5 NOT BETWEEN 1 AND 10 FalseIN 在集合中 5 IN (1,2,3,4) FalseNOT IN 不在集合中 5 NOT IN (1,2,3,4) True= 等于 2=3 False, != 不等于 23 False 严格比较两个NULL值是否相等 NULLNULL TrueLIKE 简单模式匹配 Guy Harrison LIKE Guy% TrueREGEXP 正则式匹配 Guy Harrison REGEXP Ggreg FalseIS NULL 为空 0 IS NULL FalseIS NO

6、T NULL 不为空 0 IS NOT NULL True3.逻辑运算符4.位运算符| 或& 与 右移位 非(单目运算，按位取反)注释：mysql存储过程可使用两种风格的注释双横杠：-该风格一般用于单行注释c风格：/* 注释内容 */ 一般用于多行注释-流程控制-一、顺序结构二、分支结构ifcase三、循环结构for循环while循环loop循环repeat until循环注：区块定义，常用begin.end;也可以给区块起别名，如：lable:begin.end lable;可以用leave lable;跳出区块，执行区块以后的代码begin和end如同C语言中的 和 。-输入和输出-mys

7、ql存储过程的参数用在存储过程的定义，共有三种参数类型,IN,OUT,INOUTCreate procedure|function(IN |OUT |INOUT 参数名 数据类形.)IN 输入参数表示该参数的值必须在调用存储过程时指定，在存储过程中修改该参数的值不能被返回，为默认值OUT 输出参数该值可在存储过程内部被改变，并可返回INOUT 输入输出参数调用时指定，并且可被改变和返回IN参数例子：CREATE PROCEDURE sp_demo_in_parameter(IN p_in INT)BEGINSELECT p_in; -查询输入参数SET p_in=2; -修改select p_

8、in;-查看修改后的值END;执行结果:mysql set p_in=1mysql call sp_demo_in_parameter(p_in)略mysql select p_in;略以上可以看出，p_in虽然在存储过程中被修改，但并不影响p_id的值OUT参数例子创建:mysql CREATE PROCEDURE sp_demo_out_parameter(OUT p_out INT)BEGINSELECT p_out;/*查看输出参数*/SET p_out=2;/*修改参数值*/SELECT p_out;/*看看有否变化*/END;执行结果:mysql SET p_out=1mysql

9、CALL sp_demo_out_parameter(p_out)略mysql SELECT p_out;略INOUT参数例子：mysql CREATE PROCEDURE sp_demo_inout_parameter(INOUT p_inout INT)BEGINSELECT p_inout;SET p_inout=2;SELECT p_inout;END;执行结果：set p_inout=1call sp_demo_inout_parameter(p_inout) /略select p_inout;略附：函数库mysql存储过程基本函数包括：字符串类型，数值类型，日期类型一、字符串类CH

10、ARSET(str) /返回字串字符集CONCAT (string2 , ) /连接字串INSTR (string ,substring ) /返回substring首次在string中出现的位置,不存在返回0LCASE (string2 ) /转换成小写LEFT (string2 ,length ) /从string2中的左边起取length个字符LENGTH (string ) /string长度LOAD_FILE (file_name ) /从文件读取内容LOCATE (substring , string ,start_position ) 同INSTR,但可指定开始位置LPAD (s

11、tring2 ,length ,pad ) /重复用pad加在string开头,直到字串长度为lengthLTRIM (string2 ) /去除前端空格REPEAT (string2 ,count ) /重复count次REPLACE (str ,search_str ,replace_str ) /在str中用replace_str替换search_strRPAD (string2 ,length ,pad) /在str后用pad补充,直到长度为lengthRTRIM (string2 ) /去除后端空格STRCMP (string1 ,string2 ) /逐字符比较两字串大小,SUBS

12、TRING (str , position ,length ) /从str的position开始,取length个字符,注：mysql中处理字符串时，默认第一个字符下标为1，即参数position必须大于等于1mysql select substring(abcd,0,2);+| substring(abcd,0,2) |+| |+1 row in set (0.00 sec)mysql select substring(abcd,1,2);+| substring(abcd,1,2) |+| ab |+1 row in set (0.02 sec)TRIM(BOTH|LEADING|TRAI

13、LING padding FROMstring2) /去除指定位置的指定字符UCASE (string2 ) /转换成大写RIGHT(string2,length) /取string2最后length个字符SPACE(count) /生成count个空格二、数值类型ABS (number2 ) /绝对值BIN (decimal_number ) /十进制转二进制CEILING (number2 ) /向上取整CONV(number2,from_base,to_base) /进制转换FLOOR (number2 ) /向下取整FORMAT (number,decimal_places ) /保留

14、小数位数HEX (DecimalNumber ) /转十六进制注：HEX()中可传入字符串，则返回其ASC-11码，如HEX(DEF)返回也可以传入十进制整数，返回其十六进制编码，如HEX(25)返回19LEAST (number , number2 ,.) /求最小值MOD (numerator ,denominator ) /求余POWER (number ,power ) /求指数RAND(seed) /随机数ROUND (number ,decimals ) /四舍五入,decimals为小数位数注：返回类型并非均为整数，如：(1)默认变为整形值mysql select round(1

15、.23);+-+| round(1.23) |+-+| 1 |+-+1 row in set (0.00 sec)mysql select round(1.56);+-+| round(1.56) |+-+| 2 |+-+1 row in set (0.00 sec)(2)可以设定小数位数，返回浮点型数据mysql select round(1.567,2);+-+| round(1.567,2) |+-+| 1.57 |+-+1 row in set (0.00 sec)SIGN (number2 ) /返回符号,正负或0SQRT(number2) /开平方三、日期类型ADDTIME (da

16、te2 ,time_interval ) /将time_interval加到date2CONVERT_TZ (datetime2 ,fromTZ ,toTZ ) /转换时区CURRENT_DATE ( ) /当前日期CURRENT_TIME ( ) /当前时间CURRENT_TIMESTAMP ( ) /当前时间戳DATE (datetime ) /返回datetime的日期部分DATE_ADD (date2 , INTERVAL d_value d_type ) /在date2中加上日期或时间DATE_FORMAT (datetime ,FormatCodes ) /使用formatcode

17、s格式显示datetimeDATE_SUB (date2 , INTERVAL d_value d_type ) /在date2上减去一个时间DATEDIFF (date1 ,date2 ) /两个日期差DAY (date ) /返回日期的天DAYNAME (date ) /英文星期DAYOFWEEK (date ) /星期(1-7) ,1为星期天DAYOFYEAR (date ) /一年中的第几天EXTRACT (interval_name FROM date ) /从date中提取日期的指定部分MAKEDATE (year ,day ) /给出年及年中的第几天,生成日期串MAKETIME

18、(hour ,minute ,second ) /生成时间串MONTHNAME (date ) /英文月份名NOW ( ) /当前时间SEC_TO_TIME (seconds ) /秒数转成时间STR_TO_DATE (string ,format ) /字串转成时间,以format格式显示TIMEDIFF (datetime1 ,datetime2 ) /两个时间差TIME_TO_SEC (time ) /时间转秒数WEEK (date_time ,start_of_week ) /第几周YEAR (datetime ) /年份DAYOFMONTH(datetime) /月的第几天HOUR(

19、datetime) /小时LAST_DAY(date) /date的月的最后日期MICROSECOND(datetime) /微秒MONTH(datetime) /月MINUTE(datetime) /分注：可用在INTERVAL中的类型：DAY ,DAY_HOUR ,DAY_MINUTE ,DAY_SECOND ,HOUR ,HOUR_MINUTE ,HOUR_SECOND ,MINUTE ,MINUTE_SECOND,MONTH ,SECOND ,YEARDECLARE variable_name ,variable_name. datatype DEFAULT value; 其中，dat

20、atype为mysql的数据类型，如:INT, FLOAT, DATE, VARCHAR(length)例：DECLARE l_int INT unsigned default ; DECLARE l_numeric NUMERIC(8,2) DEFAULT 9.95; DECLARE l_date DATE DEFAULT 1999-12-31; DECLARE l_datetime DATETIME DEFAULT 1999-12-31 23:59:59;DECLARE l_varchar VARCHAR(255) DEFAULT This will not be padded;SQL S

21、erver版语法为了方便说明，数据库使用SQL Server的示例数据库，Northwind和pubs，如果SQL Server中没有的话，可以按下面的方法安装 1，下载SQL2000SampleDb.msi,下载地址是： /downloads/details.aspx?FamilyId=-0356-46A0-8DA2-EEBC53A68034&displaylang=en 2，安装后，到默认目录C:SQL Server 2000 Sample Databases 有instnwnd.sql ，instpubs.sql两个文件 3，在sql ser

22、ver中运行这两个sql 就可以创建你Northwind和pubs数据库。 下面开始学T-SQL的语法 一.注释 - 单行注释，从这到本行结束为注释,类似C+,c#中/ /* */ 多行注释，类似C+，C#中/* */ 二.变量（int, smallint, tinyint, decimal,float,real, money ,smallmoney, text ,image, char, varchar。） 语法： DECLARE local_variable data_type ,.n 例如： declare ID int -申明一个名为ID的变量，类型为int型 三.在SQL Serve

23、r窗口中打印出变量的值 语法： PRINT any ASCII text | local_variable | FUNCTION | string_expr 四.变量赋值 例如： -从数据表中取出第一行数据的ID,赋值给变量id，然后打印出来 Declare ID int Set ID = (select top(1) categoryID from categories) Print ID 在SQL中，我们不能像代码那样直接给变量赋值，例如id = 1，如果要达到这样的功能，可以这样写： Declare ID int Set ID = (select 1) - 类似 ID=1 Select

24、id=1 - 类似 ID=1 Print ID 五.变量运算(+,-,*,/,) 以下必要时候省略变量申明 Set ID = (select 1+5) -类似 ID=1+5 Set ID=(select 1-ID) -类似 ID=1-ID 六.比较操作符 ? (greater than). ? (less than). ? = (equals). ? = (greater than or equal to). ? != (not equal to). ? (not equal to). ? ! (not greater than). 没什么说的 七.语句块：Begin end 将多条语句作为一

25、个块，类似与C+，C#中的 例如： Begin Set ID1 = (select 1) Set ID2 = (select 2) End 八.If， ifelse 语法： IF Boolean_expression sql_statement | statement_block ELSE sql_statement | statement_block 例如： If id is not null Print id is not null if ID = 1 begin Set ID = (select 1 + 1) end else begin set ID=(select 1+2) end

26、上面的例子用到了比较操作符，语句块，和IF的语法。 九.执行其他存储过程 EXEC 例如 EXEC dbo.Sales by Year Beginning_Date=1/01/90, Ending_Date=1/01/08 十.事务 语法： BEGIN TRANSACTION transaction_name | tran_name_variable 例如 BEGIN TRAN - 做某些操作，例如Insert into if error 0 BEGIN ROLLBACK TRAN END else BEGIN COMMIT TRAN END 十一.游标 我们可以在存储过程中用Select语句

27、取出每一行数据进行操作，这就需要用到游标。 语法： DECLARE cursor_name CURSOR LOCAL | GLOBAL FORWARD_ONLY | SCROLL STATIC | KEYSET | DYNAMIC | FAST_FORWARD READ_ONLY | SCROLL_LOCKS | OPTIMISTIC TYPE_WARNING FOR select_statement FOR UPDATE OF column_name ,.n 例如： DECLARE au_id varchar(11), au_fname varchar(20) 申明变量 -申明一个游标 DE

28、CLARE authors_cursor CURSOR FOR SELECT au_id, au_fname FROM authors -打开游标 OPEN authors_cursor -取出值 FETCH NEXT FROM authors_cursor INTO au_id, au_fname -循环取出游标的值 WHILE FETCH_STATUS = 0 BEGIN Print au_id Print au_fname Print FETCH NEXT FROM authors_cursor INTO au_id, au_fname END CLOSE authors_cursor

29、关闭游标 DEALLOCATE authors_cursor -释放游标 我觉得上面的是存储过程常用的一些东东，如果要更深入的了解，更详细的帮助，请参考SQL Server的帮助文档例子：我自己做了一个,没有问题,你可以看一下 use Northwind go create proc test StartOrderID int, EndOrderID int, Code varchar(1000) Out As Begin Declare tmp int Set Code= Declare #cur_orders cursor for Select OrderID From Orders wh

30、ere OrderID=startOrderID and OrderID=EndOrderID for read only Open #cur_Orders fetch next from #cur_orders into tmp while fetch_Status=0 Begin Set Code=Code+-+convert(varchar(8),tmp) fetch next from #cur_orders into tmp End close #cur_Orders Deallocate #cur_Orders return End go 续2 String ret=null; t

31、ry Class.forName(com.microsoft.jdbc.sqlserver.SQLServerDriver); String url =jdbc:microsoft:sqlserver:/02:1433;DatabaseName=Northwind; String user=sa; String password=; Connection conn= DriverManager.getConnection(url,user,password); CallableStatement stmt=conn.prepareCall(exec test ?,?,?)

32、; stmt.setInt(1,10248); stmt.setInt(2,10284); stmt.registerOutParameter(3,Types.VARCHAR); stmt.setString(3,ret); stmt.execute(); System.out.println(stmt.getString(3); stmt.close(); stmt=null; conn.close(); catch(ClassNotFoundException e) e.printStackTrace(); catch(SQLException e) e.printStackTrace()

33、; 上面的例子没有问题,针对你的情况,我又写了一个,应该可以解决你现在的问题 - 新建一个表 Create table tmpOrders ( OrderID int, CustomerID nchar(5) ) -把Orders 里的OrderID列全部插入,这样Orders与tmpOrders之间就是1:1关系了 insert into tmpOrders Select distinct orderID,tmp from Orders create proc test StartOrderID int, EndOrderID int, Code varchar(1000) Out As B

34、egin Declare newOrderID int Declare newCustomerID nchar(5) Declare DummyInt int Declare DummyChar nchar(5) Set Code= /* 1:1 temp table/formal table is synchronized tmpOrders Orders fetch from Orders, update tmpOrders */ - for temp table Declare #cur_tmpOrders Cursor for select OrderID,CustomerID From tmpOrders where OrderID=startOrderID and OrderID=startOrderID and OrderID=EndOrderID for read only Open #cur_Orders Open #cur_tmpOrders fetch next from #cur_tmpOrders into DummyInt,dummyChar -Important! fetch next from #cur_orders into NewOrde