存储过程基本语法sql存储过程入门.doc

-基本语法-一.创建存储过程create procedure sp_name()begin.end二.调用存储过程1.基本语法：call sp_name()注意：存储过程名称后面必须加括号，哪怕该存储过程没有参数传递三.删除存储过程1.基本语法：drop procedure sp_name/2.注意事项(1)不能在一个存储过程中删除另一个存储过程，只能调用另一个存储过程四.其他常用命令1.show procedure status显示数据库中所有存储的存储过程基本信息，包括所属数据库，存储过程名称，创建时间等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=Goodbye 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( ) SELECT 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 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 NOT 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语言中的 和 。-输入和输出-mysql存储过程的参数用在存储过程的定义，共有三种参数类型,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_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 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存储过程基本函数包括：字符串类型，数值类型，日期类型一、字符串类CHARSET(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 (string2 ,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 ) /逐字符比较两字串大小,SUBSTRING (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|TRAILING 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 ) /保留小数位数HEX (DecimalNumber ) /转十六进制注：HEX()中可传入字符串，则返回其ASC-11码，如HEX(DEF)返回4142143也可以传入十进制整数，返回其十六进制编码，如HEX(25)返回19LEAST (number , number2 ,.) /求最小值MOD (numerator ,denominator ) /求余POWER (number ,power ) /求指数RAND(seed) /随机数ROUND (number ,decimals ) /四舍五入,decimals为小数位数注：返回类型并非均为整数，如：(1)默认变为整形值mysql select round(1.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 (date2 ,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 ) /使用formatcodes格式显示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 (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(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;其中，datatype为mysql的数据类型，如:INT, FLOAT, DATE, VARCHAR(length)例：DECLARE l_int INT unsigned default 4000000;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 Server版语法为了方便说明，数据库使用SQL Server的示例数据库，Northwind和pubs，如果SQL Server中没有的话，可以按下面的方法安装 1，下载SQL2000SampleDb.msi,下载地址是： /downloads/details.aspx?FamilyId=06616212-0356-46A0-8DA2-EEBC53A68034&displaylang=en 2，安装后，到默认目录C:SQL Server 2000 Sample Databases 有instnwnd.sql ，instpubs.sql两个文件 3，在sql server中运行这两个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 Server窗口中打印出变量的值 语法： 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 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 将多条语句作为一个块，类似与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 上面的例子用到了比较操作符，语句块，和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语句取出每一行数据进行操作，这就需要用到游标。 语法： 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) 申明变量 -申明一个游标 DECLARE 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 关闭游标 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 where 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; try 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 ?,?,?); 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(); 上面的例子没有问题,针对你的情况,我又写了一个,应该可以解决你现在的问题 - 新建一个表 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 Begin 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 NewOrderID