mysql存储过程详解

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

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

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

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

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

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

7、默认值OUT输出参数:该值可在存储过程内部被改变，并可返回INOUT输入输出参数:调用时指定，并且可被改变和返回I . IN参数例子创建： DELIMITER II9 mysql CREATE PROCEDURE demo_in_parameter(IN p_in int).1 - - BEGIN11 - SELECT p_in;12 - SET p_in=2;13 - SELECT p_in;|4 - END;i - II丁 mysql DELIMITER ;执行结果：17 mysql SET p_in=1;IS mysql CALL demo_in_parameter(p_in);二 +-

8、+工 I p_in |二 +-+丘| 1 |二 +-一+24+| p_in |+-+ | 2 | +3031 mysql SELECT p_i n;工+门 | p_in |3 - +L | 1 |5 +p_id的值以上可以看出，p_in虽然在存储过程中被修改，但并不影响n .out参数例子创建：5 mysql DELIMITER /強 mysql CREATE PROCEDURE demo_out_parameter(OUT p_out int) g - BEGIN40 - SELECT p_out;41 - SET p_out=2;42 - SELECT p_out;? - END;怕- /

9、上 mysql DELIMITER ;执行结果：46 mysql SET p_out=1;47 mysql CALL sp_demo_out_parameter(p_out);-:.+49 | p_out |” +二 | NULL |:-；+53 SELECT p_out;： +| p_out |5 +：| 2 |、+m. INOUT参数例子创建：E mysql DELIMITER /67 mysql CREATE PROCEDURE demo_inout_parameter(INOUT p_inout int) BEGIN帥- SELECT p_inout;10 - SET p_inout=

10、2;71 - SELECT pn out;二- END;? - /耳 mysql DELIMITER ;-执行结果：mysql SET p_i nout=1;76 mysql CALL demo_inout_parameter(p_inout);+TS | p_inout |+| 1 |、一 +2 +84 | p_inout |z +| 2 | SELECT p_inout;：.+91 | p_in out |、.匚 +Y丨2|：貝 +(4).变量I .变量定义DECLARE variable, name ,variable_ name. datatype DEFAULT value; 其中，

11、datatype为 MySQL 的数据类型，如：int, float, date, varchar(length) 例如：95 DECLARE l_int int unsigned default 4000000;算 DECLARE l_numeric number(8,2) DEFAULT 9.95;97 DECLARE l_date date DEFAULT 1999-12-31：第 DECLARE l_datetime datetime DEFAULT 1999-12-31 23:59:59;99 DECLARE l_varchar varchar(255) DEFAULT This w

12、ill not be padded;n.变量赋值SET 变量名 =表达式值 ,variable_name = expression .川.用户变量i .在MySQL客户端使用用户变量mysql SELECT Hello World i nto x;.1 .| mysql SELECT x;二+li-S| x|：+.1 门| Hello World |工，“+.11 mysql SET y=Goodbye Cruel World;.1 / mysql SELECT y;.1 & +门1 y|+.1.1 | Goodbye Cruel World |.1.1 ： +114.1.1 mysql SE

13、T z=1+2+3;.1.1 ； mysql SELECT z;.11 +-+11X I z |.1.1； +-+工丨6 |.1+-+ii .在存储过程中使用用户变量I?., mysql CREATE PROCEDURE GreetWorld( ) SELECT CONCAT(greeti ng, World);!? mysql SET greeting=Hello;i 山 mysql CALL GreetWorld();+二 | CONCAT(greeting, World) |r-./ +二 | Hello WorldIiii 在存储过程间传递全局范围的用户变量130 mysql CREA

14、TE PROCEDURE p1() SET last_procedure=p1：T mysql CREATE PROCEDURE p2() SELECT CONCAT(Last procedure was,last_proc);I-./ mysql CALL p1();沁 mysql CALL p2();i 4 +135 | CONCAT(Last procedure was ,last_proc |:? +i | Last procedure was p1| 用户变量名一般以开头 滥用用户变量会导致程序难以理解及管理(5).注释MySQL存储过程可使用两种风格的注释双模杠：-该风格一般用于单

15、行注释c风格：一般用于多行注释例如：I V. mysql DELIMITER /.1 打 mysql CREATE PROCEDURE procl -name 存储过程名 (IN parameter INTEGER)i 工.- BEGIN.1 以- DECLARE variable1 CHAR(10);.1 u - IF parameter = 17 THEN.1 i： - SET variable1 = birds;.1J; - ELSE.1J - SET variable1 = beasts;.1 坑- END IF;眼- INSERT INTO table1 VALUES (variab

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

17、e from in formatio n_schema.routi nes where rout in e_schema=数据库名；或者show procedure status where db数 据库名进行查询。如果我们想知道，某个存储过程的详细，那我们又该怎么做呢？是不是也可 以像操作表一样用describe表名进行查看呢？答案是：我们可以查看存储过程的详细，但是需要用另一种方法：SHOW CREATE PROCEDURE数据库.存储过程名;就可以查看当前存储过程的详细。6.MySQL存储过程的修改ALTER PROCEDURE更改用CREATE PROCEDURE建立的预先指定的存储过

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

19、are x1 varchar(5) default outer; .1 - - begi nI :一 X - declare x1 varchar(5) default inner：- select x1;.1:1-end;.1 ;.l-select x1;.1 ;-end;- /.I U mysql DELIMITER ;(2).条件语句I . if-then -else 语句C mysql DELIMITER /:d mysql CREATE PROCEDURE proc2(IN parameter int)；- begi n；- declare var int;- set var=par

20、ameter+1;C - if var=0 then1 1- insert into t values(17);i- end if;- if parameter=0 then.1 J - update t set s1=s1+1;i；- elsei r： - update t set s1=s1+2;i-l - end if;痣- end;.1 - /工二 mysql DELIMITER ;n . case 语句：.1-.I mysql DELIMITER /.1 mysql CREATE PROCEDURE proc3 (in parameter int)5?- begin.I：- decl

21、are var int;i 忑 - set var=parameter+1;.1 ；- case vari 扎 - whe n 0 the n.$8- insert into t values(17);.1 whe n 1 the n.1； 1- in sert into t values(18);.121- elsei ； - insert into t values(19);- end case;- en d;i - /- mysql DELIMITER ;(3).循环语句I . while nd whilemysql DELIMITER /i號 mysql CREATE PROCEDUR

22、E proc4()号- begi nJ 工- declare var int;.|-set var=O;j-.j-while var in sert into t values(var);.川-set var=var+1;.f：-end while;*-end;207-/ mysql DELIMITER ;n . repeat end repeat :它在执行操作后检查结果，而 while则是执行前进行检查。 mysql DELIMITER /.11 mysql CREATE PROCEDURE proc5 ()二- begi n.- declare v int;- set v=0;.1J-

23、repeat- i ：-in sert into t values(v); I ：-set v=v+1;-un til v=5二- end repeat;二、- end;二：- II二 mysql DELIMITER ;IH . loop end loop:loop循环不需要初始条件，这点和 while循环相似，同时和repeat循环一 样不需要结束条件，leave语句的意义是离开循环。“二 mysql DELIMITER II一二 mysql CREATE PROCEDURE proc6 ()，-,-4 - begi nJ.- declare v int;m： - set v=0;227-

24、LOOP_LABLE:loopjjS- in sert into t values(v);- set v=v+1;/.)- if v =5 then231 - leave LOOP_LABLE;232 -endif;、二- endloop;-4-end;二、- II二 mysql DELIMITER ;IV . LABLES 标号：标号可以用在begin repeat while 或者loop语句前，语句标号只能在合法 的语句前面使用。可以跳出循环，使运行指令达到复合语句的最后一步。(4) . ITERATE 迭代I . ITERATE:通过引用复合语句的标号，来从新开始复合语句二 mysql

25、 DELIMITER IIA mysql CREATE PROCEDURE proclO ()239- begin240- declare v int;241- set v=0;242- LOOP_LABLE:loop243- if v=3 the n244- set v=v+1;245- ITERATE LOOP_LABLE;乂 6- end if;247- insert into t values(v);24S- set v=v+1;24?- if v=5 the n250- leave LOOP_LABLE;251- end if;252- end loop;253- en d;254-

26、 /mysql DELIMITER ;9. MySQL存储过程的基本函数(1).字符串类CHARSET(str) /返回字串字符集CONCAT (stri ng2 ,. ) / 连接字串INSTR (string ,substring ) 返回substring首次在string中出现的位置，不存在返回0LCASE (stri ng2 ) / 转换成小写LEFT (string2 ,length ) /从 string2中的左边起取 length 个字符LENGTH (string ) /string 长度LOAD_FILE (file_name ) / 从文件读取内容LOCATE (subs

27、tring , string ,start_position )同 INSTR,但可指定开始位置LPAD (string2 ,length ,pad ) /重复用pad加在string开头，直到字串长度为lengthLTRIM (stri ng2 ) /去除前端空格REPEAT (string2 ,count ) /重复 count 次REPLACE (str ,search_str ,replace_str )在 str 中用 replace_str替换 search_strRPAD (string2 ,length ,pad) /在 str 后用 pad 补充，直到长度为 lengthRT

28、RIM (stri ng2 ) /去除后端空格STRCMP (string1 ,string2 ) 逐字符比较两字串大小，SUBSTRING (str , position ,length ) / 从 str 的 position 开始，取 length 个字符,注：mysql中处理字符串时，默认第一个字符下标为1,即参数position必须大于 等于1!抒，mysql select substring(abcd,0,2);、+匕 I | substring(abcd,0,2) |-+| |二 +二 1 row in set (0.00 sec)263二：mysql select substr

29、ing(abcd,1,2);+磁；| substring(abcd,1,2) |二 +| ab|+11 row in set (0.02 sec)TRIM(BOTH|LEADING|TRAILING paddi ng FROMstri ng2) / 去除指定位置的指定字符UCASE (stri ng2 ) / 转换成大写RIGHT(string2,length) /取 string2最后 length 个字符SPACE(count)/生成 count个空格数学类ABS (nu mber2 ) 绝对值BIN (decimal_number ) 十进制转二进制CEILING (number2 )

30、/ 向上取整CONV( nu mber2,from_base,to_base)进制转换FLOOR (nu mber2 ) 向下取整FORMAT (nu mber,decimal_places ) /保 留小数位数HEX (DecimalNumber ) / 转十六进制注：HEX()中可传入字符串，则返回其 ASC-11码，如HEX(DEF)返回4142143也可以传入十进制整数，返回其十六进制编码，如HEX(25)返回19LEAST (nu mber , n umber2 ,.)/ 求最小值MOD (nu merator ,de nomin ator ) /求余POWER (nu mber ,

31、power ) /求指数RAND(seed) / 随机数ROUND (number ,decimals ) /四舍五入,decimals为小数位数注：返回类型并非均为整数，如：(1)默认变为整形值? 1 mysql select round(1.23);二二 +二 S | round(1.23) |二 +1 |二 +T ： 1 row in set (0.00 sec)27S-打:mysql select round(1.56);述.+-+屋.i | round(1.56) |M +-+3 |2 |:+-+工；1 row in set (0.00 sec)(2)可以设定小数位数，返回浮点型数据妙mysql select ro un d(1.567,2);二 +迷 | round(1.567,2) |乞 +?：! |1.57 |+.O 1 row in set (0.00 sec)SIGN (nu mber2 ) /日期时间类ADDTIME (date2 ,time_interval ) / 将