MySql存储过程的作用及语法

首页 > 代码库 > MySql存储过程的作用及语法

MySql存储过程的作用及语法

2024-09-22 21:54:41 218人阅读

MySQL使用存储过程的作用

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

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

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

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

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

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

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

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

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) //小时

视频学习中的例子

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

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

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

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

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$

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$

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$

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

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

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$

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$

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$

#逻辑处理游标越界

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$

游标越界的时候，在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 循环

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$

MySql存储过程的作用及语法

声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉：投诉/举报工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。

联系
我们

首页 > 代码库 > MySql存储过程的作用及语法