存储格式
CREATE PROCEDURE 存储过程名称(参数模式 参数名 参数类型)
BEGIN
存储过程体
ENDdelimiter问题
在Mysql中，因为存储过程中的SQL语句必须以分号结尾，而mysql的cmd窗口或者Navicat等图形化操作软件在遇到sql语句加分号时，会直接执行语句，而发送冲突导致存储过程创建失败。因此我们要修改存储过程的结束符，让mysql可以识别。
DELIMITER $
CREATE PROCEDURE LOGIN_YZ(INOUT a INT,INOUT b INT)
BEGIN
SET a = 2;
SET b = b*2;
END$
DELIMITER ;这个范例是以$作为开始结束，这个符号我们可以自由选择，注意别跟存储过程中的字符重复就好。
存储参数
一个存储过程可以有多个参数，一个参数又分为参数模式、参数名和参数类型。
参数模式
存储过程中的参数分别是 in，out，inout三种参数模式；
- in代表输入参数（默认情况下为in参数），表示该参数的值必须由调用程序指定。
- ou代表输出参数，表示该参数的值经存储过程计算后，将out参数的计算结果返回给调用程序，用来存储输出的变量。
- inout代表即时输入参数，又是输出参数，表示该参数的值即可有调用程序制定，又可以将inout参数的计算结果返回给调用程序。
范例如下：
-
```sql -- IN类型演示 delimiter $$ create procedure sp_param01(in age int) begin set @user_age = age; end$$ call sp_param01(10) $$ select @user_age$$ ``` ```sql -- OUT类型，只负责输出！ -- 需求：输出传入的地址字符串对应的部门编号。 --建表 CREATE TABLE `dept` ( `deptno` INT(11) NOT NULL COMMENT '部门编号', `dname` VARCHAR(32) NULL COMMENT '部门名称' , `loc` VARCHAR(64) NULL COMMENT '部门地址' , PRIMARY KEY (`deptno`) USING BTREE ) COLLATE='utf8_general_ci' ENGINE=InnoDB ; INSERT INTO DEPT VALUES (10,'ACCOUNTING','NEW YORK'); INSERT INTO DEPT VALUES (20,'RESEARCH','DALLAS'); INSERT INTO DEPT VALUES (30,'SALES','CHICAGO'); INSERT INTO DEPT VALUES (40,'OPERATIONS','BOSTON'); -- delimiter $$ create procedure sp_param02(in loc varchar(64),out dept_no int(11)) begin select d.deptno into dept_no from dept d where d.loc = loc; --此处强调，要么表起别名，要么入参名不与字段名一致 end$$ delimiter ; --测试 set @dept_no = 100; call sp_param02('DALLAS',@dept_no); select @dept_no; ``` ```sql -- INOUT类型 delimiter $$ create procedure sp_param03(inout name varchar) begin set name = concat('hello' ,name);--拼接 end$$ delimiter ; set @user_name = '小明'; call sp_param03(@user_name); select @user_name; ```
参数名
存储过程命名，要注意存储跟其他表，视图的区分，下边是比较通用的命名规范，实际工作中要结合公司业务需要和实际需求进行命名。
-
存储过程名以_proc结尾，表示procedure。之后多个单词以下划线（_）进行连接。存储过程命名中应体现其功能。存储过程名不能超过30个字符。
-
存储过程中的输入参数以i_开头，输出参数以o_开头。
-
命名应使用小写。
参数类型
参数类型就是定义字符类型，这里主要说下几个需要注意的参数类型。
- 小数类型，decimal：表示高精度十进制数的数据类型。 适用于需要精确计算和不希望由于浮点数舍入误差而引入错误的场景，比如银行。 float：适合于需要较小范围和较低精度的浮点数计算。 double：double是64位双精度浮点数的数据类型，与float相比，double提供了更大的范围和更高的精度。decimal(length, precision)用于表示精度确定（小数点后数字的位数确定）的小数类型，length决定该小数的最大位数，precision用于设置精度（小数点后数字的位数)。例如：decimal (5,2)表示小数取值范围：-999.99～999.99。
变量及赋值
局部变量
用户自定义，在begin/end块中有效。赋值范例如下
-- set赋值
delimiter $$
create procedure sp_var01()
begin
declare nickname varchar(32) default 'unkown';--声明变量
set nickname = 'ZS';
-- set nickname := 'SF';
select nickname;
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.ename into emp_no,emp_name from emp e where e.empno = 7839;
select emp_no,emp_name;
end$$用户变量
用户自定义，当前会话（连接）有效。语法@＋你的变量名，用户变量无需声明，范例如下:
delimiter $$
create procedure sp_var02()
begin
set @nickname = 'ZS';
-- set nickname := 'SF';
end$$
call sp_var02() $$
select @nickname$$ 会话变量
由系统提供，当前会话（连接）有效 ，语法：@@session.var_name。
show session variables; -- 查看会话变量
select @@session.unique_checks; -- 查看某会话变量
set @@session.unique_checks = 0; --修改会话变量全局变量
由系统提供，整个mysql服务器有效。语法：@@global.var_name。
-- 查看全局变量中变量名有char的记录
show global variables like '%char%';
-- 查看全局变量character_set_client的值
select @@global.character_set_client; 流程控制-判断
语法:
IF search_condition THEN statement_list
[ELSEIF search_condition THEN statement_list] ...
[ELSE statement_list]
END IF
范例：
-- 前置知识点：timestampdiff(unit,exp1,exp2) 取差值exp2-exp1差值，单位是unit
select timestampdiff(year,e.hiredate,now()) from emp e where e.empno = '7499';
--建表
CREATE TABLE `emp` (
`empno` INT(11) NOT NULL COMMENT '员工编号',
`ename` VARCHAR(32) NULL COMMENT '员工姓名' COLLATE 'utf8_general_ci',
`job` VARCHAR(10) NULL COMMENT '职位' COLLATE 'utf8_general_ci',
`mgr` INT(11) NULL COMMENT '上级编号',
`hiredate` DATE NOT NULL COMMENT '入职时间',
`sal` DECIMAL(7,2) NOT NULL DEFAULT '0.00' COMMENT '薪资',
`comm` DECIMAL(7,2) NULL COMMENT '年终奖金',
`deptno` INT(11) NOT NULL COMMENT '部门编号',
PRIMARY KEY (`empno`) USING BTREE,
INDEX `FK_emp_dept` (`deptno`) USING BTREE,
CONSTRAINT `FK_emp_dept` FOREIGN KEY (`deptno`) REFERENCES `procedure_demo`.`dept` (`deptno`) ON UPDATE RESTRICT ON DELETE RESTRICT--如果子表中有匹配的记录,则不允许对父表对应候选键进行update/delete操作
);
insert into emp values
(7369,'smith','clerk',7902,'1980-12-17',800,null,20);
insert into emp values
(7499,'allen','salesman',7698,'1981-02-20',1600,300,30);
insert into emp values
(7521,'ward','salesman',7698,'1981-02-22',1250,500,30);
insert into emp values
(7566,'jones','manager',7839,'1981-02-04',2975,null,20);
insert into emp values
(7654,'martin','salesman',7698,'1981-09-28',1250,1400,30);
insert into emp values
(7698,'blake','manager',7839,'1981-05-01',2850,null,30);
insert into emp values
(7782,'clark','manager',7839,'1981-06-09',2450,null,10);
insert into emp values
(7788,'scott','analyst',7566,'1987-07-13')-85,3000,null,20);
insert into emp values
(7839,'king','president',null,'1981-11-17',5000,null,10);
insert into emp values
(7844,'turner','salesman',7698,'1981-09-08',1500,0,30);
insert into emp values
(7876,'adams','clerk',7788,'1987-07-13')-51,1100,null,20);
insert into emp values
(7900,'james','clerk',7698,'1981-12-03',950,null,30);
insert into emp values
(7902,'ford','analyst',7566,'1981-12-03',3000,null,20);
insert into emp values
(7934,'miller','clerk',7782,'1982-01-23',1300,null,10);
```sql
-- 需求：入职年限<=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 ;流程控制-循环
loop循环
语法：
[begin_label:] LOOP
statement_list
END LOOP [end_label]
需要说明，loop是死循环，需要手动退出循环，我们可以使用`leave`来退出。与之对应的，就有`iterate`（继续循环）。
```sql
--需求：循环打印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$$while循环
语法：
[begin_label:] WHILE search_condition DO
statement_list
END WHILE [end_label]
范例：
-- 需求：循环打印1到10
delimiter $$
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$$死循环处理：
-- 如有死循环处理，可以通过下面的命令查看并结束
show processlist;
kill id;
游标
语法：
-- 声明语法
DECLARE cursor_name CURSOR FOR select_statement
-- 打开语法
OPEN cursor_name
-- 取值语法
FETCH cursor_name INTO var_name [, var_name] ...
-- 关闭语法
CLOSE cursor_name
范例：
-- 需求：按照部门名称查询员工，通过select查看员工的编号、姓名、薪资。（注意，此处仅仅演示游标用法）
delimiter $$
create procedure sp_create_table02(in dept_name varchar(32))
begin
declare e_no int;
declare e_name varchar(32);
declare e_sal decimal(7,2);
declare lp_flag boolean default true;
--创建游标
declare emp_cursor cursor for
select e.empno,e.ename,e.sal
from emp e,dept d
where e.deptno = d.deptno and d.dname = dept_name;
-- handler 句柄 未查到任何数据，lp_flag 改为false，继续执行
declare continue handler for NOT FOUND set lp_flag = false;
open emp_cursor;
emp_loop:loop
fetch emp_cursor into e_no,e_name,e_sal;
if lp_flag then
select e_no,e_name,e_sal;
else
leave emp_loop;
end if;
end loop emp_loop;
set @end_falg = 'exit_flag';
close emp_cursor;
end$$
call sp_create_table02('RESEARCH');
```
> 特别注意：
>
> 在语法中，变量声明、游标声明、handler声明是必须按照先后顺序书写的，否则创建存储过程出错
646
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
