四时宝库

一、 什么是存储过程

1、可以理解为 sql 语句集，可以同时执行多条sql，实现较复杂的逻辑。

2、关于存储过程，这里讲的比较好，请参考：https://www.cnblogs.com/chenpi/p/5136483.html

二、简单的存储过程

1、 语法：

CREATE PROCEDURE pro_now() -- 存储过程名称，自定义BEGIN -- 开始存储过程       # 需要执行操作的sql语句集,可对数据表 进行CRUD 操作       -- insert some sql hereend; -- 结束存储过程

2、创建一个查询当前时间的存储过程：

CREATE  PROCEDURE pro_now() BEGIN    SELECT now();end;

3、调用存储过程： call pro_now(); -- call 关键字, "pro_now()" 存储过程名称

4、查看已创建的存储过程： show PROCEDURE STATUS [ where name='pro_now'] ;

补充： 关于mysql查看存储过程函数相关命令

5、删除存储过程：DROP PROCEDURE pro_now; -- "pro_now" 存储过程名称

三、创建带参数的存储过程

1、 带输入参数的存储过程， 使用关键字： in

a、示例如下：

CREATE PROCEDURE pro_now_in(in time  VARCHAR(20) CHARACTER set "utf8")	        -- CHARACTER set "utf8",设定字符集，解决中文乱码BEGIN	 SELECT now(),time;end;

b、调用存储过程：

set @time='当前时间';call pro_now_in(@time); --  call pro_now_in('当前时间'); 这样也可以

c、结果如下：

2、带输出参数的存储过程，使用关键字： out

a、示例如下：

CREATE PROCEDURE pro_now_out(out time  VARCHAR(20),out title VARCHAR(20) CHARACTER set utf8)BEGIN	 SELECT now(),'当前时间' into time , title;end;

b、调用存储过程：

call pro_now_out(@times,@title);SELECT @title AS "标题",@times AS "时间";

c、结果如下：

3、带输入、输出参数的存储过程，使用关键字： inout

a、示例如下：

CREATE PROCEDURE pro_now_inout(inout name VARCHAR(20),in title VARCHAR(10), out time VARCHAR(10))BEGIN	 SELECT CONCAT(name,'<--->',title) AS name,now() into name,time;end;

b、调用存储过程

set @name='jack';set @title='toady';call pro_now_inout(@name,@title,@time);select @name as 'name and title',@time as 'time';

c、结果如下：

d、理解：

• in （输入）： name 、title
• out （输出）： name 、 time
• CONCAT(name,'<--->',title) 对应 name 输出、 now() 对应 time 输出

四、创建带流程控制语句的存储过程

1、if 语句

a、示例如下：

CREATE PROCEDURE pro_if(in num INT)     BEGIN	    DECLARE result VARCHAR(20) CHARACTER set  utf8 DEFAULT null;	    IF num = 0 THEN  -- 开始if判断，注意用一个等号"="	        set result='num 为0啦'; -- 满足条件	    ELSEIF num > 0 THEN -- 下一个if判断		set result='num 大于 0';  	    ELSEIF num < 0 THEN		set result='num 小于 0';	    ELSE -- 所有条件不满足的情况下		set result='num is null or other status';	    end if; -- 结束if 判断 	    SELECT result; end;

b、调用存储过程：

call pro_if('33');

c、结果如下：

2、case 语句

a、示例如下：

CREATE PROCEDURE pro_case(in num INT)BEGIN	DECLARE result VARCHAR(20) CHARACTER set  utf8 DEFAULT null;	case num  -- 开始case 判断	when  2 THEN  -- 满足条件执行		set result='num 值是2'; 	 when -2 THEN  		set result='num 值是-2';	else  -- 所有条件不满足，执行		set result='num 不等于2和-2';	end case ; -- 结束case语句	SELECT result;end;

b、调用存储过程

call pro_case(-2);

c、结果如下：

3、while 循环语句

a、示例如下：

CREATE PROCEDURE pro_while(in num INT)BEGIN	DECLARE i int;	DECLARE result int;	set i=0;	set result=0;	while i < num DO -- 开始while 循环		set result=result+i;		set i=i+1;	end while; -- 结束while 循环 	SELECT result,i;end;

b、调用存储过程：

call pro_while(100);

c、结果如下：

五、创建带游标循环的存储过程

1、示例如下：

CREATE PROCEDURE pro_cursor(out count int)
BEGIN	declare  paper_id  VARCHAR(1000) ; -- 论文主键id	
declare doctroName VARCHAR(1000) character set gbk; -- 医生名称	
DECLARE paper_hos VARCHAR(1000); -- 医院id	
DECLARE paper_room      VARCHAR(100); -- 医生专业	
declare done int DEFAULT false ; -- 自定义控制游标循环变量,默认false	
DECLARE  my_cursor CURSOR
for (SELECT id,authorName,hospitalId,room					
     from yee_article_paper_authorid ); -- 定义游标并输入结果集 
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=TRUE; -- 绑定控制变量到游标,游标循环结束自动转true
OPEN my_cursor; -- 打开游标
myLoop:LOOP -- 开始循环体,myLoop为自定义循环名,结束循环时用到 
FETCH my_cursor into paper_id,doctroName,paper_hos,paper_room ;  -- 将游标当前读取行的数据顺序赋予自定义变量12  
if done THEN -- 判断是否继续循环  		
LEAVE myLoop;-- 结束循环	END IF;	 -- 自己要做的事情,在 sql 中直接使用自定义变量即可  
insert into temp(str_id,name,hospitalId,room) 	
VALUES(paper_id,doctroName,paper_hos,paper_room);
COMMIT; -- 提交事务 
END  LOOP myLoop; -- 结束
自定义循环体	CLOSE my_cursor; -- 关闭游标
# 循环结束后，统计导入个数	SELECT count(id)  count from temp into count; -- 计算个数
END