MYSQL存储过程（一）

CREATE[DEFINER = user]  PROCEDURE sp_name ([proc_parameter[,...]])[characteristic ...] routine_body
-- proc_parameter参数部分，可以如下书写：[IN| OUT | INOUT ] param_name type
-- type类型可以是MySQL支持的所有类型-- routine_body(程序体)部分，可以书写合法的SQL语句 BEGIN ... END-- 声明结束符。因为MySQL默认使用‘;’作为结束符，而在存储过程中，会使用‘;’作为一段语句的结束，导致‘;’使用冲突delimiter $$
CREATE PROCEDURE hello_procedure ()BEGINSELECT'hello procedure';END $$
call hello_procedure();

语法：
声明变量 declare var_name type [default var_value];举例：declare p_name varchar(32);-- set赋值create procedure p_01()begin  declare p_name varchar(32) default 'unkown';set p_name ='ZS';-- set p_name := 'ls';select p_name;end$$
-- into赋值delimiter $$
create procedure sp_var_into()begin  declare emp_name varchar(32) default 'unkown';  declare emp_no int default 0;select e.empno,e.enameinto emp_no,emp_name from emp e where e.empno=7839;select emp_no,emp_name;end$$

语法： 
@var_name
不需要提前声明，使用即声明
-- 赋值delimiter $$
create procedure p_02()beginset @p_name ='ZS';-- set p_name := 'ls';end$$
call p_02() $$
select @p_name$$  --可以看到结果

语法：
@@session.var_nameshow session variables;-- 查看会话变量select @@session.unique_checks;-- 查看某会话变量set @@session.unique_checks=0;--修改会话变量

语法：
@@global.var_name-- 查看全局变量中变量名有char的记录show global variables like'%char%';-- 查看全局变量character_set_client的值select @@global.character_set_client;

-- 语法in| out | inout param_name type
-- IN类型演示delimiter $$
create procedure sp_01(in age int)beginset @user_age = age;end$$
call sp_01(10) $$
select @user_age$$
-- OUT类型，只负责输出！-- 需求：输出传入的地址字符串对应的部门编号。delimiter $$
create procedure sp_02(in loc varchar(64),out dept_no int(11))beginselect d.deptnointo dept_no from dept d where d.loc= loc;--此处强调，要么表起别名，要么入参名不与字段名一致end$$
delimiter ;--测试set @dept_no =100;call sp_01('DALLAS',@dept_no);select @dept_no;-- INOUT类型 delimiter $$
create procedure sp_03(inout name varchar)beginset name = concat('hello',name);end$$
delimiter ;set @user_name ='小明';call sp_03(@user_name);select @user_name;

-- 语法IF search_condition THEN statement_list
[ELSEIF search_condition THEN statement_list] ...
[ELSE statement_list]END IF
-- 前置知识点：timestampdiff(unit,exp1,exp2) 取差值exp2-exp1差值，单位是unitselect timestampdiff(year,e.hiredate,now())from emp e where e.empno='7499';-- 需求：入职年限<=38是新手 >38并且<=40老员工 >40元老delimiter $$
create procedure sp_hire_if()begin  declare result varchar(32);  if timestampdiff(year,'2001-01-01',now())>40    then set result ='元老';  elseif timestampdiff(year,'2001-01-01',now())>38    then set result ='老员工';  else 
set result ='新手';  end if;select result;end$$
delimiter ;

-- 语法一（类比java的switch）：CASE case_value
    WHEN when_value THEN statement_list
[WHEN when_value THEN statement_list] ...
[ELSE statement_list]END CASE
-- 语法二：CASE
    WHEN search_condition THEN statement_list
[WHEN search_condition THEN statement_list] ...
[ELSE statement_list]END CASE
-- 需求：入职年限年龄<=38是新手 >38并 <=40老员工 >40元老delimiter $$
create procedure sp_hire_case()begin  declare result varchar(32);  declare message varchar(64);  case
    when timestampdiff(year,'2001-01-01',now())>40    then 
set result ='元老';set message ='老年人';  when timestampdiff(year,'2001-01-01',now())>38    then 
set result ='老员工';set message ='中年人';  else 
set result ='新手';set message ='青年';  end case;select result;end$$
delimiter ;

-- 语法[begin_label:] LOOP
    statement_list
END LOOP [end_label]需要说明，loop是死循环，需要手动退出循环，我们可以使用`leave`来退出。
可以把leave看成我们java中的break；与之对应的，就有`iterate`（继续循环）——类比java的continue
--需求：循环打印1到10-- leave控制循环的退出delimiter $$
create procedure sp_flow_loop()begin  declare c_index int default 1;  declare result_str  varchar(256) default '1';  cnt:loop
    if c_index >=10    then leave cnt;    end if;set c_index = c_index +1;set result_str = concat(result_str,',',c_index);  end loop cnt;select result_str;end$$
-- iterate + leave控制循环delimiter $$
create procedure sp_flow_loop02()begin  declare c_index int default 1;  declare result_str  varchar(256) default '1';  cnt:loop
set c_index = c_index +1;set result_str = concat(result_str,',',c_index);    if c_index <10 then 
      iterate cnt;    end if;-- 下面这句话能否执行到？什么时候执行到？ 当c_index < 10为false时执行    leave cnt;  end loop cnt;select result_str;end$$

[begin_label:] REPEAT
    statement_list
UNTIL search_condition  -- 直到…为止，才退出循环END REPEAT [end_label]-- 需求：循环打印1到10delimiter $$
create procedure sp_flow_repeat()begin  declare c_index int default 1;-- 收集结果字符串  declare result_str varchar(256) default '1';  count_lab:repeat
set c_index = c_index +1;set result_str = concat(result_str,',',c_index);    until c_index >=10  end repeat count_lab;select result_str;end$$

类比java的while(){}[begin_label:] WHILE search_condition DO
    statement_list
END WHILE [end_label]-- 需求：循环打印1到10delimiter $$
create procedure sp_flow_while()begin  declare c_index int default 1;-- 收集结果字符串  declare result_str varchar(256) default '1';  while c_index <10 do
set c_index = c_index +1;set result_str = concat(result_str,',',c_index);  end while;select result_str;end$$

-- 退出 LEAVE can be used within BEGIN ... END or loop constructs (LOOP, REPEAT, WHILE).LEAVE label

-- 继续循环 ITERATE can appear only within LOOP, REPEAT, and WHILE statementsITERATE label