MYSQL 存储过程，游标

1 基础语法结构

类似Java中封装的方法

-- 在这里插入代码片-- 声明结束符。因为mysql默认使用';' 最为结束符，而在存储过程中，会使用';'作为一段语句的结束,导致';' 使用冲突
delimiter $$
-- 创建存储过程
CREATE PROCEDURE HELLO()
BEGIN
 select "你好啊世界";
END $$

--调用存储过程
CALL HELLO();

2 变量及赋值

类似Java中局部变量和成员变量的声明和使用

2.1 局部变量

用户自定义变量 仅在Begin/End 块中有效

示例：DECLARE first_name VARCHAR ( 32 ) DEFAULT ( “小王” );

普通赋值

-- 注意：执行前先删除原有的HELLO 函数 不然会有冲突  DROP PROCEDURE HELLO;
delimiter $$
CREATE PROCEDURE HELLO () BEGIN
-- 使用 DECLARE 声明变量
	DECLARE first_name VARCHAR ( 32 ) DEFAULT ( "小王" );
	SELECT  first_name;
--- 使用 set 关键字赋值
	SET first_name = "李四";
	SELECT  first_name;
	
END $$ 

CALL HELLO ();

into赋值 （将查询出来的值赋值给局部变量）

delimiter $$

CREATE PROCEDURE HELLO5 () BEGIN
	DECLARE first_name VARCHAR ( 32 ) DEFAULT ( "小王" );
 
 SELECT a.name  into  first_name from  person a where a.id = 1 ;
 SELECT first_name ;

END $$ 

CALL HELLO5 ();

2.2 用户变量

用户自定义，当前会话（连接）有效 类比Java对象的成员变量

示例：@nick_name

普通赋值

delimiter $$

CREATE PROCEDURE HELLO1 () BEGIN
  SET @var_name="小西";
	select @var_name;
END $$ 

CALL HELLO1 ();

select @var_name;

into 赋值

delimiter $$

CREATE PROCEDURE HELLO11 () BEGIN
 select name into @var_name from person where id=1;
END $$ 

CALL HELLO11 ();

select @var_name;

3 出入参数

示例：in | out | inout param_name type

入参

delimiter $$
-- 声明varchar要传输长度 name varchar(20)
CREATE PROCEDURE Helloin2(in age int) BEGIN
 select age;
END $$ 

CALL Helloin2(18);

出参

delimiter $$
-- 声明varchar要传输长度 name varchar(20)
CREATE PROCEDURE Helloout6(in ids int,out var_name varchar(20)) BEGIN
 select name into var_name from person where id=ids;
END $$ 

CALL Helloout6(2,@var_name);

select @var_name;

出入参

delimiter $$
-- 声明varchar要传输长度 name varchar(20)
CREATE PROCEDURE Helloinout(INOUT name varchar(20)) BEGIN
 select CONCAT("热烈欢迎",name,"同学") into name;
END $$ 

set @var_name ="小范同学";

CALL Helloinout(@var_name);

select @var_name;

4 流程控制-判断

if 判断

delimiter $$ 

CREATE PROCEDURE Helloinout22(in ids int) 
BEGIN
 DECLARE falg VARCHAR(20) DEFAULT('');
 DECLARE age int ;
 
 select TIMESTAMPDIFF(year,time,now()) into age  from   person  where id =ids;
 
 
   if age <20  then 
	  set falg ="老员工";
		
		elseif age >20 then 
		set falg ="新员工";
		
	  else 
		    set falg= "不是本公司员工";
				
				end if;
		select falg;
END $$ 

CALL Helloinout22(1);

5 流程控制-循环

5.1 loop

loop 是死循环 需要有leave 做中断 不然会一直循环下去

delimiter $$


CREATE PROCEDURE Helloinout20(in number int) 
BEGIN
 -- 循环名称:loop
 loop_name:  LOOP
	 select number;

  if number >=10 then 
	 LEAVE loop_name;
	 end if;
	 set number =number + 1;
 
END LOOP loop_name;
END $$ 

CALL Helloinout20(1);

5.2 while

while 会一直判断条件是否满足，不满足就会终止

delimiter $$ 

CREATE PROCEDURE Helloinout25() 
BEGIN
 DECLARE num_index int DEFAULT(1);
 -- 收集字符串
 DECLARE str_flag varchar(32) DEFAULT('1');
 
 while num_index < 10  DO
  set num_index = num_index +1 ;
  set str_flag= CONCAT(str_flag,",",num_index);
END WHILE;
   select str_flag;
 
END $$ 

CALL Helloinout25();

6 流程控制-退出、继续循环

• leave 类比Java break
• iterate 类比Java Continue

7 游标

用游标得到得到某一个结果集，逐行处理数据 （类比Java 的 ResultSet ）。

注意：存储过程的声明顺序必须是 先 变量 -> 游标 -> handler 句柄 必须按照先后顺序写的，否则创建存储过程除 并且 变量的声明不要跟结果集的名称相对应

• 游标的语法

-- 声明语法
DECLARE cursor_name CURSOR  FOR list_data
-- 打开语法
OPEN cursor_name
-- 取值语法
FETCH cursor_name
-- 关闭语法
CLOSE cursor_name

• 实战：获取指定部门员工的编号，姓名，薪资。

delimiter $$ 

CREATE PROCEDURE Helloinout30(in detp_name varchar(23)) 
BEGIN
 -- 编号
 DECLARE person_id int ; 
 -- 姓名
 DECLARE names varchar(23) ;
 -- 薪资
 DECLARE moneys DECIMAL(7,2) ;
 -- handler 句柄报错标识变量
 DECLARE lp_flag boolean DEFAULT true;
 
 -- 声明游标
 DECLARE data_result CURSOR for
 select id ,name ,money FROM person where dept= detp_name;
 
 -- 声明handler句柄给 针对1329 报错进行触发处理，1329的错误就是游标遍历的no data 异常
 DECLARE CONTINUE HANDLER FOR 1329 SET lp_flag =false ; 
 
 -- 打开游标
 OPEN data_result;
 -- 循环
 for_loop: LOOP
 
 -- 取值
	FETCH data_result  into  person_id, names , moneys ;
 -- 对no data 的错误表示判断，true 就继续遍历
	IF lp_flag THEN
	  select  person_id, names , moneys;
		else 
		LEAVE for_loop; 
	END IF; 
		
 END LOOP for_loop;
 
 set @end_flag="已经结束了";

END $$ 

CALL Helloinout30('it');

select @end_flag;