SQL存储过程02.docx

1、SQL存储过程02存储过程如同一门程序设计语言，同样包含了数据类型、流程控制、输入和输出和它自己的函数库。-基本语法-一.创建存储过程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=Goodbye

3、 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

4、 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 NOT

6、 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语言中的 和 。-输入和输出-mysq

7、l存储过程的参数用在存储过程的定义，共有三种参数类型,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_i

8、n;-查看修改后的值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 C

9、ALL 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存储过程基本函数包括：字符串类型，数值类型，日期类型一、字符串类CHA

10、RSET(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 (st

11、ring2 ,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 ) /逐字符比较两字串大小,SUBST

12、RING (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|TRAIL

13、ING 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)返回4142143也可以传入十进制整数，返回其十六进制编码，如HEX(25)返回19LEAST (number , number2 ,.) /求最小值MOD (numerator ,denominator ) /求余POWER (number ,power ) /求指数RAND(seed) /随机数ROUND (number ,decimals ) /四舍五入,decimals为小数位数注：返回类型并非均为整数，如：(1)默认变为整形值mysql select r

15、ound(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) /开平方三、日期类型ADDTI

16、ME (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 ) /使用form

17、atcodes格式显示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 ) /给出年及年中的第几天,生成日期串MAK

18、ETIME (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) /月的第几

19、天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;

20、其中，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

21、 padded;SQL Server版语法为了方便说明，数据库使用SQL Server的示例数据库，Northwind和pubs，如果SQL Server中没有的话，可以按下面的方法安装 1，下载SQL2000SampleDb.msi,下载地址是： 2，安装后，到默认目录C:SQL Server 2000 Sample Databases 有instnwnd.sql ，instpubs.sql两个文件 3，在sql server中运行这两个sql 就可以创建你Northwind和pubs数据库。 下面开始学T-SQL的语法 一.注释 - 单行注释，从这到本行结束为注释,类似C+,c#中/ /*

22、 */ 多行注释，类似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 四.变量赋值 例

23、如： -从数据表中取出第一行数据的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

24、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 语法：

25、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

26、/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 | SCRO

27、LL 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 -循环