MySql存储过程的作用及语法

MySQL使用存储过程的作用

1、使用了存过程，很多相似性的删除，更新，新增等操作就变得轻松了，并且以后也便于管理！

2、存储过程因为SQL语句已经预编绎过了，因此运行的速度比较快。   

3、存储过程可以接受参数、输出参数、返回单个或多个结果集以及返回值。可以向程序返回错误原因。     

4、存储过程运行比较稳定，不会有太多的错误。只要一次成功，以后都会按这个程序运行。     

5、存储过程主要是在服务器上运行，减少对客户机的压力。   

6、存储过程可以包含程序流、逻辑以及对数据库的查询。同时可以实体封装和隐藏了数据逻辑。   

7、存储过程可以在单个存储过程中执行一系列SQL语句。   

8、存储过程可以从自己的存储过程内引用其它存储过程，这可以简化一系列复杂语句。

MySQL使用存储过程的争议

尽管存储过程有诸多优点，但是对于存储过程的使用，一直都存在着很多争议，比如有些公司对于大型项目要求使用存储过程，而有些公司在手册中明确禁止使用存储过程，为什么这些公司对存储过程的使用需求差别这么大呢？

我们得从存储过程的特点来找答案。

你能看到存储过程有很多好处。

首先存储过程可以一次编译多次使用。存储过程只在创造时进行编译，之后的使用都不需要重新编译，这就提升了 SQL 的执行效率。其次它可以减少开发工作量。将代码封装成模块，实际上是编程的核心思想之一，这样可以把复杂的问题拆解成不同的模块，然后模块之间可以重复使用，在减少开发工作量的同时，还能保证代码的结构清晰。还有一点，存储过程的安全性强，我们在设定存储过程的时候可以设置对用户的使用权限，这样就和视图一样具有较强的安全性。最后它可以减少网络传输量，因为代码封装到存储过程中，每次使用只需要调用存储过程即可，这样就减少了网络传输量。同时在进行相对复杂的数据库操作时，原本需要使用一条一条的 SQL 语句，可能要连接多次数据库才能完成的操作，现在变成了一次存储过程，只需要连接一次即可。

基于上面这些优点，不少大公司都要求大型项目使用存储过程，比如微软、IBM 等公司。但是国内的阿里并不推荐开发人员使用存储过程，这是为什么呢？

存储过程虽然有诸如上面的好处，但缺点也是很明显的。

它的可移植性差，存储过程不能跨数据库移植，比如在 MySQL、Oracle 和 SQL Server 里编写的存储过程，在换成其他数据库时都需要重新编写。

其次调试困难，只有少数 DBMS 支持存储过程的调试。对于复杂的存储过程来说，开发和维护都不容易。

此外，存储过程的版本管理也很困难，比如数据表索引发生变化了，可能会导致存储过程失效。我们在开发软件的时候往往需要进行版本管理，但是存储过程本身没有版本控制，版本迭代更新的时候很麻烦。

最后它不适合高并发的场景，高并发的场景需要减少数据库的压力，有时数据库会采用分库分表的方式，而且对可扩展性要求很高，在这种情况下，存储过程会变得难以维护，增加数据库的压力，显然就不适用了。

了解了存储过程的优缺点之后，我想说的是，存储过程既方便，又有局限性。尽管不同的公司对存储过程的态度不一，但是对于我们开发人员来说，不论怎样，掌握存储过程都是必备的技能之一。

MySQL存储过程的创建 

先定义一个结束分隔符，控制sql语句的执行

delimiter $      //意思是用$作为sql语句的结束符

参数 

MySQL存储过程的参数用在存储过程的定义，共有三种参数类型,IN,OUT,INOUT,形式如： 

CREATE PROCEDURE([[IN |OUT |INOUT ] 参数名 数据类形...]) 

IN 输入参数:表示该参数的值必须在调用存储过程时指定，在存储过程中修改该参数的值不能被返回，为默认值 

OUT 输出参数:该值可在存储过程内部被改变，并可返回 

INOUT 输入输出参数:调用时指定，并且可被改变和返回 

①IN参数例子 

创建: 

DELIMITER $ 
CREATE PROCEDURE demo(IN p_in int) 
 BEGIN 
 SELECT p_in; 
 SET p_in=2; 
 SELECT p_in; 
END$

执行结果: 

SET @p_in=1;
CALL demo(@p_in);
+------+ 
| p_in | 
+------+ 
| 1    | 
+------+ 
+------+ 
| p_in | 
+------+ 
| 2    | 
+------+
SELECT @p_in;
+-------+ 
| @p_in | 
+-------+ 
|  1    | 
+-------+

以上可以看出，p_in虽然在存储过程中被修改，但并不影响@p_id的值 

②OUT参数例子 

创建: 

DELIMITE $
CREATE PROCEDURE demo(OUT p_out int) 
 BEGIN 
 SELECT p_out; 
 SET p_out=2; 
 SELECT p_out; 
 END$

执行结果: 

SET @p_out=1; 
CALL demo(@p_out);
+-------+ 
| p_out | 
+-------+ 
| NULL  | 
+-------+ 
+-------+ 
| p_out | 
+-------+ 
| 2     | 
+-------+
SELECT @p_out; 
+-------+ 
| p_out | 
+-------+ 
| 2     | 
+-------+

 

③INOUT参数例子 

创建: 

DELIMITER $
CREATE PROCEDURE demo_inout_parameter(INOUT p_inout int) 
 BEGIN 
 SELECT p_inout; 
 SET p_inout=2; 
 SELECT p_inout; 
 END$

执行结果: 

SET @p_inout=1; 
CALL demo_inout_parameter(@p_inout) ;
+---------+ 
| p_inout | 
+---------+ 
| 1       | 
+---------+ 
+---------+ 
| p_inout | 
+---------+ 
| 2       | 
+---------+
SELECT @p_inout;
+----------+ 
| @p_inout | 
+----------+ 
| 2        | 
+----------+

 

变量

①定义变量

例如: 

DECLARE l_int int unsigned default 4000000; 
DECLARE l_numeric number(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';

②变量赋值 

SET 变量名 = 表达式值 [,variable_name = expression ...] 

③用户变量 

ⅰ. 在MySQL客户端使用用户变量 

SELECT 'Hello World' into @x; 
SELECT @x; 
+-------------+ 
| @x          | 
+-------------+ 
| Hello World | 
+-------------+ 
SET @y='Goodbye Cruel World'; 
SELECT @y; 
+---------------------+ 
| @y           　　　　| 
+---------------------+ 
| Goodbye Cruel World | 
+---------------------+ 
SET @z=1+2+3; 
SELECT @z; 
+------+ 
| @z   | 
+------+ 
| 6    | 
+------+

ⅱ. 在存储过程中使用用户变量 

CREATE PROCEDURE GreetWorld( ) 
 BEGIN
 SELECT CONCAT(@greeting,' World'); 
 SET @greeting='Hello'; 
 END$
 CALL GreetWorld( ); 
+----------------------------+ 
| CONCAT(@greeting,' World') | 
+----------------------------+ 
| Hello World          　　　 | 
+----------------------------+

 

④MySQL存储过程的修改 

ALTER PROCEDURE

更改用CREATE PROCEDURE 建立的预先指定的存储过程，其不会影响相关存储过程或存储功能。 

⑤MySQL存储过程的删除 

删除一个存储过程比较简单，和删除表一样： 

DROP PROCEDURE

从MySQL的表格中删除一个或多个存储过程。

⑥MySQL存储过程的控制语句 

(1). 变量作用域 

内部的变量在其作用域范围内享有更高的优先权，当执行到end。变量时，内部变量消失，此时已经在其作用域外，变量不再可见了，应为在存储 

过程外再也不能找到这个申明的变量，但是你可以通过out参数或者将其值指派 

给会话变量来保存其值。 

CREATE PROCEDURE proc3() 
 begin 
 declare x1 varchar(5) default 'outer'; 
 begin 
 declare x1 varchar(5) default 'inner'; 
 select x1; 
 end; 
 select x1; 
 end$

(2). 条件语句 

A:if-then -else语句 

CREATE PROCEDURE proc2(IN parameter int) 
 begin 
 declare var int; 
 set var=parameter+1; 
 if var=0 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$

B:case语句： 

CREATE PROCEDURE proc3 (in parameter int) 
 begin 
 declare var int; 
 set var=parameter+1; 
 case var 
 when 0 then 
 insert into t values(17); 
 when 1 then 
 insert into t values(18); 
 else 
 insert into t values(19); 
 end case; 
 end$

C:loop ·····end loop: 

loop循环不需要初始条件，这点和while 循环相似，同时和repeat循环一样不需要结束条件, leave语句的意义是离开循环。 

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$

D：while语句    while ···· end while：

CREATE PROCEDURE proc4()  
 begin 
 declare var int;  
 set var=0;  
 while var<6 do  
 insert into t values(var);  
 set var=var+1;  
 end while;  
 end$

E:repeat 语句       repeat···· end repeat：

它在执行操作后检查结果，而while则是执行前进行检查。

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$

⑦ITERATE迭代 

 ITERATE: 

通过引用复合语句的标号,来从新开始复合语句 

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); 
 set v=v+1; 
 if v>=5 then 
 leave LOOP_LABLE; 
 end if; 
 end loop; 
 end$

MySQL存储过程的基本函数

(1).字符串类 

CHARSET(str)                     //返回字串字符集 
CONCAT (string2 [,... ])         //连接字串 
INSTR (string ,substring )       //返回substring首次在string中出现的位置,不存在返回0 
LCASE (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开头,直到字串长度为length 
LTRIM (string2 )                 //去除前端空格 
REPEAT (string2 ,count )         //重复count次 
REPLACE (str ,search_str ,replace_str ) //在str中用replace_str替换search_str 
RPAD (string2 ,length ,pad)       //在str后用pad补充,直到长度为length 
RTRIM (string2 )                  //去除后端空格 
STRCMP (string1 ,string2 )         //逐字符比较两字串大小, 
SUBSTRING (str , position [,length ]) //从str的position开始,取length个字符

注：mysql中处理字符串时，默认第一个字符下标为1，即参数position必须大于等于1 

select substring('abcd',0,2); 
+-----------------------+ 
| substring('abcd',0,2) | 
+-----------------------+ 
+-----------------------+
select substring('abcd',1,2); 
+-----------------------+ 
| substring('abcd',1,2) | 
+-----------------------+ 
| ab          　　　　　　| 
+-----------------------+
TRIM([[BOTH|LEADING|TRAILING] [padding] FROM]string2) //去除指定位置的指定字符 
UCASE (string2 ) //转换成大写 
RIGHT(string2,length) //取string2最后length个字符 
SPACE(count) //生成count个空格

(2).数学类 

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)返回19 

LEAST (number , number2 [,..]) //求最小值 
MOD (numerator ,denominator ) //求余 
POWER (number ,power ) //求指数 
RAND([seed]) //随机数 
ROUND (number [,decimals ]) //四舍五入,decimals为小数位数]

注：返回类型并非均为整数，如： 

(1)默认变为整形值 

select round(1.23); 
+-------------+ 
| round(1.23) | 
+-------------+ 
| 1           | 
+-------------+
select round(1.56); 
+-------------+ 
| round(1.56) | 
+-------------+ 
| 2           | 
+-------------+

(2)可以设定小数位数，返回浮点型数据 

select round(1.567,2); 
+----------------+ 
| round(1.567,2) | 
+----------------+ 
| 1.57           | 
+----------------+

 

(3).日期时间类 

ADDTIME (date2 ,time_interval ) //将time_interval加到date2 
CONVERT_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格式显示datetime 
DATE_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) //小时

视频学习中的例子

1

create procedure p1()
begin
    select 'hello' from dual;
end$
call p1()$

2 

create procedure p2()
begin
declare age int default 18;
declare height int default 180;
select concat('年龄是',age,'身高是',height);
end$

3

create procedure p3()
begin
declare age int default 18;
set age:= age+20;
select concat('20年后年龄是',age);
end$

4

create procedure p4()
begin
declare age int default 18;
if age >=18 then
select  '成年';
else
select '未成年';
end if;
end$

5

create procedure p5(width int ,height int)
begin
select concat('你的面积是', width * height ) as area;
if width >height then
select '你挺胖';;
elseif width < height then
select ‘你挺瘦’;
else
select '你挺方';
endif;
end$

6

create procedure p6()
begin
declare total int default 0;
declare num int default 0;
while num <100 do
set num:=num+1;
set total:= total +num;
end while;
select total;
end$

7

create procedure p7(in n int)
begin
declare total int default 0;
declare num int default 0;
while num <n do
set num:=num+1;
set total:= total +num;
end while;
select total;
end$

8

create procedure p8(in n int ,out total int)
begin
declare num int default 0;
set total :=0; #null  碰到任何操作都会变成null
while num<n do
set num:=num+1;
set total :=total+num;
end while;
end$
call p8(100,@summary)$
select @summary

9

create procedure p9(inout age int)
begin
set age:=age +20;
end$
set @currage =18$
call p9(@currage)$
select @currage$

10

create procedure p10()
begin
declare pos int default 0;
set pos := floor(5*rand());
case pos
when 1 then select 'still flying';
when 2 then select 'fall in sea';
when 3 then select 'in the island';
else select 'I don't know';
end case;
end$

11

create procedure p11()
begin
declare total int default 0;
declare i int default 0;
repeat 
set i:=i+1;
set total :=total +i;
until i>=100 end repeat;
select total;
end$

12  游标 

create procedure p12()
begin
declare row_gid int;
declare row_num int;
declare row_name varchar(20);
declare getgoods cursor for select gid,num,name from goods;
open getgoods;
fetch getgoods into row_gid,row_num,row_name;
select row_num,row_name;
close getgoods;
end$

13

create procedure p13()
begin
declare row_gid int;
declare row_num int;
declare row_name varchar(20);
declare getgoods cursor for select gid, num , name from goods;
open getgoods;
fetch getgoods into row_gid,row_num,row_name; #cat
select row_num,row_name;
fetch getgoods into row_gid,row_num,row_name;#dog
select row_num,row_name;
fetch getgoods into row_gid,row_num,row_name;#pig
select row_num,row_name;
fetch getgoods into row_gid,row_num,row_name;#error
select row_num,row_name;
close getgoods;
end$

#逻辑处理游标越界

14

create procedure p14()
begin
declare row_gid int;
declare row_num int;
declare row_name varchar(20);
declare cnt int default 0;
declare i int default 0;
declare getgoods cursor for select gid, num , name from goods;
select count(*) into cnt from goods;
open getgoods;
repeat set 
fetch getgoods into row_gid,row_num,row_name; #cat
select row_num,row_name;
until i >= cnt  end repeat;
close getgoods;
end$

15

游标越界的时候，在mysql  cursor中有一个标示，可以declare continue  handler 来操作一个越界标示

declare continue handler for NOT FOUND statement;
create procedure p15()
begin
declare row_gid int;
declare row_num int;
declare row_name varchar(20);
declare you int default 1;
declare getgoods cursor for select gid, num , name from goods;
select count(*) into cnt from goods;
declare continue handler for NOT FOUND set you :=0;  //如果发生not found事件，把you  改为 0
open getgoods;
repeat set 
fetch getgoods into row_gid,row_num,row_name;
select row_num,row_name;
until you=0 end repeat;
close getgoods;
end$
call p15()$
show warnings$

16：上条BUG修复，continue和 exit的区别（exit触发后后面的代码不在执行，而continue继续执行）

create procedure p16()
begin
declare row_gid int;
declare row_num int;
declare row_name varchar(20);
declare you int default 1;
declare getgoods cursor for select gid, num , name from goods;
select count(*) into cnt from goods;
declare exit handler for NOT FOUND set you :=0;#这里的handler类型改成了exit
open getgoods;
repeat set 
fetch getgoods into row_gid,row_num,row_name; 
select row_num,row_name;
until you=0 end repeat;
close getgoods;
end$

17：正确严谨的逻辑

#接下来的存储过程才应该是游标的正确使用方式：

#一定要用continue handler  ，通过逻辑来控制。

create procedure p17()
begin
declare row_gid int;
declare row_num int;
declare row_name varchar(20);
declare you int default 1;
declare getgoods cursor for select gid, num , name from goods;
select count(*) into cnt from goods;
declare continue handler for NOT FOUND set you :=0;
open getgoods;
fetch getgoods into row_gid,row_num,row_name; 
repeat set 
select row_num,row_name;
fetch getgoods into row_gid,row_num,row_name; 
until you=0 end repeat;
close getgoods;
end$

#换成while 循环

18

create procedure p18()
begin
declare row_gid int;
declare row_num int;
declare row_name varchar(20);
declare you int default 1;
declare getgoods cursor for select gid, num , name from goods;
select count(*) into cnt from goods;
declare continue handler for NOT FOUND set you :=0;
open getgoods;
fetch getgoods into row_gid,row_num,row_name; 
while you =1 do
select row_num,row_name;
fetch getgoods into row_gid,row_num,row_name; 
end while;
close getgoods;
end$