Mysql存储过程详解

《Mysql存储过程详解》由会员分享，可在线阅读，更多相关《Mysql存储过程详解（13页珍藏版）》请在装配图网上搜索。

1、MySQL存储过程详解mysql 存储过程详解1. 存储过程简介我们常用的操作数据库语言 SQL 语句在执行的时候需要要先编译，然后执行，而存储过程 (Stored Procedure)是一组为了完成特定功能的SQL语句集，经编译后存储在数据库中， 用户通过指定存储过程的名字并给定参数(如果该存储过程带有参数)来调用执行它。 一个存储过程是一个可编程的函数，它在数据库中创建并保存。它可以有 SQL 语句和一些 特殊的控制结构组成。当希望在不同的应用程序或平台上执行相同的函数，或者封装特定功 能时，存储过程是非常有用的。数据库中的存储过程可以看做是对编程中面向对象方法的模 拟。它允许控制数据的访

2、问方式。存储过程通常有以下优点：(1) .存储过程增强了 SQL 语言的功能和灵活性。存储过程可以用流控制语句编写，有很强的 灵活性，可以完成复杂的判断和较复杂的运算。(2) .存储过程允许标准组件是编程。存储过程被创建后，可以在程序中被多次调用，而不必 重新编写该存储过程的 SQL 语句。而且数据库专业人员可以随时对存储过程进行修改，对 应用程序源代码毫无影响。.存储过程能实现较快的执行速度。如果某一操作包含大量的Transaction-SQL代码或分别 被多次执行，那么存储过程要比批处理的执行速度快很多。因为存储过程是预编译的。在首 次运行一个存储过程时查询，优化器对其进行分析优化，并且给

3、出最终被存储在系统表中的 执行计划。而批处理的Transaction-SQL语句在每次运行时都要进行编译和优化，速度相对要 慢一些。(4) .存储过程能过减少网络流量。针对同一个数据库对象的操作(如查询、修改)，如果这一 操作所涉及的Transaction-SQL语句被组织程存储过程，那么当在客户计算机上调用该存储过 程时，网络中传送的只是该调用语句，从而大大增加了网络流量并降低了网络负载。(5) .存储过程可被作为一种安全机制来充分利用。系统管理员通过执行某一存储过程的权限 进行限制，能够实现对相应的数据的访问权限的限制，避免了非授权用户对数据的访问，保 证了数据的安全。2. 关于MySQL

4、的存储过程存储过程是数据库存储的一个重要的功能，但是MySQL在5.0以前并不支持存储过程，这 使得MySQL在应用上大打折扣。好在MySQL 5.0终于开始已经支持存储过程，这样即可以 大大提高数据库的处理速度，同时也可以提高数据库编程的灵活性。3. MySQL存储过程的创建(1). 格式MySQL存储过程创建的格式：CREATE PROCEDURE过程名(过程参数,.)特性 . 过程体这里先举个例子：mysql DELIMITER / mysql CREATE PROCEDURE proc1(OUT s int)- BEGIN- SELECT COUNT(*) INTO s FROM us

5、er;- END- /mysql DELIMITER ;注：(1) 这里需要注意的是DELIMITER 和DELIMITER ;两句，DELIMITER是分割符的意思，因为 MySQL 默认以;为分隔符，如果我们没有声明分割符，那么编译器会把存储过程当成 SQL 语句进行处理，则存储过程的编译过程会报错，所以要事先用DELIMITER关键字申明当前段 分隔符，这样MySQL才会将;当做存储过程中的代码，不会执行这些代码，用完了之后要 把分隔符还原。(2) 存储过程根据需要可能会有输入、输出、输入输出参数，这里有一个输出参数s,类 型是 int 型，如果有多个参数用,分割开。(3) 过程体的开始

6、与结束使用BEGIN与END进行标识。这样，我们的一个MySQL存储过程就完成了，是不是很容易呢?看不懂也没关系，接下来， 我们详细的讲解。(2) . 声明分割符 其实，关于声明分割符，上面的注解已经写得很清楚，不需要多说，只是稍微要注意一点的 是：如果是用MySQL的Administrator管理工具时，可以直接创建，不再需要声明。(3) . 参数MySQL存储过程的参数用在存储过程的定义，共有三种参数类型IN,OUT,INOUT形式如：CREATE PROCEDURE(IN |OUT |INOUT 参数名 数据类形.)IN 输入参数:表示该参数的值必须在调用存储过程时指定，在存储过程中修改

7、该参数的值不 能被返回，为默认值OUT 输出参数:该值可在存储过程内部被改变，并可返回INOUT 输入输出参数:调用时指定，并且可被改变和返回I . IN参数例子创建:mysql DELIMITER /mysql CREATE PROCEDURE demo_in_parameter(IN p_in int)- BEGIN- SELECT p_in;- SET p_in=2;- SELECT p_in;- END;- /mysql DELIMITER ;执行结果: mysql SET p_in=1;mysql CALL demo_in_parameter(p_in);+| p_in |+| 1

8、|+| p_in |+| 2 |+mysql SELECT p_in;+| p_in |+| 1 |+以上可以看出，p_in虽然在存储过程中被修改，但并不影响p_id的值II .OUT参数例子创建:mysql DELIMITER /mysql CREATE PROCEDURE demo_out_parameter(OUT p_out int)- BEGIN- SELECT p_out;- SET p_out=2;- SELECT p_out;- END;- /mysql DELIMITER ;执行结果:mysql SET p_out=1;mysql CALL sp_demo_out_param

9、eter(p_out);+| p_out |+| NULL |+| p_out |+|2|+mysql SELECT p_out;+| p_out |+|2|+III. INOUT参数例子创建:mysql DELIMITER /mysql CREATE PROCEDURE demo_inout_parameter(INOUT p_inout int) - BEGIN- SELECT p_inout;- SET p_inout=2;- SELECT p_inout;- END;- /mysql DELIMITER ;执行结果:mysql SET p_inout=1;mysql CALL demo

10、_inout_parameter(p_inout) ;+| p_inout |+|1|+| p_inout |+|2|+mysql SELECT p_inout;+| p_inout |+| 2 |+ (4). 变量I. 变量定义DECLARE variable_name ,variable_name. datatype DEFAULT value;其中，datatype 为 MySQL 的数据类型，如:int, float, date, varchar(length) 例如:DECLARE l_int int unsigned default ;DECLARE l_numeric numbe

11、r(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;II. 变量赋值SET 变量名 = 表达式值 ,variable_name = expression .III. 用户变量i .在MySQL客户端使用用户变量mysql SELECT Hello World into x;mysql SELEC

12、T x;+| x |+| Hello World |+mysql SET y=Goodbye Cruel World;mysql SELECT y;+| y|+| Goodbye Cruel World |+ mysql SET z=1+2+3;mysql SELECT z;+| z |+| 6 |+ii. 在存储过程中使用用户变量mysql CREATE PROCEDURE GreetWorld( ) SELECT CONCAT(greeting, World); mysql SET greeting=Hello;mysql CALL GreetWorld( );+| CONCAT(gree

13、ting, World) |+| Hello World |+iii. 在存储过程间传递全局范围的用户变量mysql CREATE PROCEDURE p1() SET last_procedure=p1;mysql CREATE PROCEDURE p2() SELECT CONCAT(Last procedure was ,last_proc); mysql CALL p1( );mysql CALL p2( );+| CONCAT(Last procedure was ,last_proc |+| Last procedure was p1 |+ 用户变量名一般以开头 滥用用户变量会导致

14、程序难以理解及管理(5). 注释MySQL 存储过程可使用两种风格的注释 双模杠：-该风格一般用于单行注释c 风格： 一般用于多行注释 例如：mysql DELIMITER /mysql CREATE PROCEDURE procl -name 存储过程名- (IN parameter1 INTEGER)- BEGIN- DECLARE variable1 CHAR(10);- IF parameter1 = 17 THEN- SET variable1 = birds;- ELSE- SET variable1 = beasts;- END IF;- INSERT INTO table1 V

15、ALUES (variable1);- END- /mysql DELIMITER ;4. MySQL 存储过程的调用用 call 和你过程名以及一个括号，括号里面根据需要，加入参数，参数包括输入参数、输出 参数、输入输出参数。具体的调用方法可以参看上面的例子。5. MySQL 存储过程的查询我们像知道一个数据库下面有那些表，我们一般采用show tables;进行查看。那么我们要查 看某个数据库下面的存储过程，是否也可以采用呢？答案是，我们可以查看某个数据库下面 的存储过程，但是是令一钟方式。我们可以用select name from mysql.proc where db= 数据库名;或者

16、select routine_name from information_schema.routines where routine_schema=S(据库名；或者show procedure status where db数据库名；进行查询。如果我们想知道，某个存储过程的详细，那我们又该怎么做呢？是不是也可以像操作表一样 用 describe 表名进行查看呢？答案是：我们可以查看存储过程的详细，但是需要用另一种方法：SHOW CREATE PROCEDURE 数据库.存储过程名； 就可以查看当前存储过程的详细。6.MySQL 存储过程的修改ALTER PROCEDURE更改用 CREATE

17、PROCEDURE 建立的预先指定的存储过程，其不会影响相关存储过程或存储功7. MySQL 存储过程的删除 删除一个存储过程比较简单，和删除表一样： DROP PROCEDURE从 MySQL 的表格中删除一个或多个存储过程。8. MySQL 存储过程的控制语句(1) . 变量作用域内部的变量在其作用域范围内享有更高的优先权，当执行到end。变量时，内部变量消失, 此时已经在其作用域外，变量不再可见了，应为在存储过程外再也不能找到这个申明的变量，但是你可以通过out参数或者将其值指派 给会话变量来保存其值。mysql DELIMITER / mysql CREATE PROCEDURE pr

18、oc3()- begin- declare x1 varchar(5) default outer;- begin- declare x1 varchar(5) default inner;- select x1;- end;- select x1;- end;- /mysql DELIMITER ;(2) . 条件语句I .if-then -else语句mysql DELIMITER /mysql CREATE PROCEDURE proc2(IN parameter int)- begin- declare var int;- set var=parameter+1;- if var=0

19、then- insert into t values(17);- end if;- if parameter=0 then- update t set s1=s1+1;- else- update t set s1=s1+2;- end if;- end;- /mysql DELIMITER ;II. case 语句：mysql DELIMITER /mysql CREATE PROCEDURE proc3 (in parameter int)- begin- declare var int;- set var=parameter+1;- case var- when 0 then- inse

20、rt into t values(17);- when 1 then- insert into t values(18);- else- insert into t values(19);- end case;- end;- / mysql DELIMITER ;(3) . 循环语句I . while end while： mysql DELIMITER / mysql CREATE PROCEDURE proc4()- begin- declare var int;- set var=0;- while var insert into t values(var);- set var=var+

21、1;- end while;- end;- / mysql DELIMITER ;II. repeat end repeat： 它在执行操作后检查结果，而 while 则是执行前进行检查 mysql DELIMITER /mysql CREATE PROCEDURE proc5 ()- begin- declare v int;- set v=0;- repeat- insert into t values(v);- set v=v+1;- until v=5- end repeat;- end;- / mysql DELIMITER ;III. loop end loop:loop 循环不需

22、要初始条件，这点和 while 循环相似，同时和 repeat 循环一样不需要结束条 件, leave 语句的意义是离开循环。mysql DELIMITER /mysql CREATE PROCEDURE proc6 () - begin- declare v int;- set v=0;- LOOP_LABLE:loop- insert into t values(v);- set v=v+1;- if v =5 then- leave LOOP_LABLE;- end if;- end loop;- end;- / mysql DELIMITER ;W. LABLES 标号：标号可以用在

23、begin repeat while 或者 loop 语句前，语句标号只能在合法的语句前面使用 可以跳出循环，使运行指令达到复合语句的最后一步。(4) . ITERATE 迭代I . ITERATE:通过引用复合语句的标号,来从新开始复合语句mysql DELIMITER /mysql CREATE PROCEDURE proc10 ()- begin- declare v int;- set v=0;- LOOP_LABLE:loop- if v=3 then- set v=v+1;- ITERATE LOOP_LABLE;- end if;- insert into t values(v)

24、;- set v=v+1;- if v=5 then- leave LOOP_LABLE;- end if;- end loop;- end;- /mysql DELIMITER ;(1).字符串类CHARSET(str) /返回字串字符集CONCAT (string2 ,. ) /连接字串INSTR (string ,substring ) /返回 substring 首次在 string 中出现的位置,不存在返回 0LCASE (string2 ) /转换成小写LEFT (string2 ,length ) /从 string2 中的左边起取 length 个字符LENGTH (strin

25、g ) /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_strRP

26、AD (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 i

27、n 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 个空格(2).数学类ABS (number2 ) /绝对值BIN (decimal_nu

28、mber ) /十进制转二进制CEILING (number2 ) /向上取整 CONV(number2,from_base,to_base) /进制转换FLOOR (number2 ) /向下取整FORMAT (number,decimal_places ) /保留小数位数HEX (DecimalNumber ) /转十六进制注：HEX()中可传入字符串，则返回其ASC-11码，如HEX(DEF)返回也可以传入十进制整数，返回其十六进制编码，如HEX(25)返回19LEAST (number , number2 ,.) /求最小值MOD (numerator ,denominator ) /

29、求余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.56

30、72);+| round(1.5672) |+|1.57 |+1 row in set (0.00 sec)SIGN (number2 ) /(3).日期时间类ADDTIME (date2 ,time_interval ) /将 time_interval 加到 date2CONVERT_TZ (datetime2 fromTZ toTZ ) /转换时区CURRENT_DATE ( ) /当前日期CURRENT_TIME ( ) /当前时间CURRENT_TIMESTAMP ( ) /当前时间戳DATE (datetime ) /返回 datetime 的日期部分DATE_ADD (date2

31、 , INTERVAL d_value d_type ) /在 date2 中加上日期或时间 DATE_FORMAT (datetime ,FormatCodes ) /使用 formatcodes 格式显示 datetime DATE_SUB (date2 , INTERVAL d_value d_type ) /在 date2 上减去一个时间 DATEDIFF (date1 ,date2 ) /两个日期差DAY (date ) /返回日期的天DAYNAME (date ) /英文星期DAYOFWEEK (date ) /星期(1-7) ,1为星期天DAYOFYEAR (date ) /一年

32、中的第几天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) /分返回符号,正负或0SQRT(number2) /开平方