MYSQL存储过程专题-注释详解

发布时间:2022-07-05 发布网站:脚本宝典
脚本宝典收集整理的这篇文章主要介绍了MYSQL存储过程专题-注释详解脚本宝典觉得挺不错的,现在分享给大家,也给大家做个参考。

原文链接,本文增加了注释、补充了部分运行错误、使用了不同MySQL版本 MYSQL存储过程专题

MySQL存储过程

0.环境说明:

软件 版本
mysql 8.0
navicat

1.使用说明

​ 存储过程时数据库的一个重要的对象,可以封装SQL语句集,可以用来完成一些较复杂的业务逻辑,并且可以入参出参(类似于java中的方法的书写)。

​ 创建时会预先编译后保存,用户后续的调用都不需要再次编译。

// 把edITUser类比成一个存储过程
public void editUser(User user,String username){
    String a = "nihao";
    user.setUsername(username);
}
main(){
    User user = new User();
	editUser(user,"张三");
    user.getUseranme();   //java基础还记得不
}

​ 大家可能会思考,用sql处理业务逻辑还要重新学,我用java来处理逻辑(比如循环判断、循环查询等)不行吗?那么,为什么还要用存储过程处理业务逻辑呢?

优点:
	在生产环境下,可以通过直接修改存储过程的方式修改业务逻辑(或bug),而不用重启服务器。
	执行速度快,存储过程经过编译之后会比单独一条一条执行要快。
	减少网络传输流量。
	方便优化。
缺点:
	过程化编程,复杂业务处理的维护成本高。
	调试不便
	不同数据库之间可移植性差。-- 不同数据库语法不一致!

2.准备:

数据库参阅资料中的sql脚本;

delimiter $$ --声明结束符

3.语法

官方参考网址 https://dev.mysql.COM/doc/refman/5.6/en/sql-statements.htML https://dev.mysql.com/doc/refman/5.6/en/sql-compound-statements.html


#### 3.0 语法结构

```sql
-- 存储过程结构
CREATE
    [defineR = user]
	PROCEDURE sp_name ([proc_parameter[,...]])
    [characteristic ...] routine_body
    
-- 1. proc_parameter参数部分,可以如下书写:
	[ IN | OUT | INOUT ] param_name tyPE
	-- type类型可以是MySQL支持的所有类型
	
-- 2. routine_body(程序体)部分,可以书写合法的SQL语句 BEgin ... END

简单演示:

-- 声明结束符。因为MySQL默认使用‘;’作为结束符,而在存储过程中,会使用‘;’作为一段语句的结束,导致‘;’使用冲突
delimiter $$

CREATE PROCEDURE hello_procedure ()
BEGIN
	SELECT 'hello procedure';
END $$

call hello_procedure();

3.1 变量及赋值

类比一下java中的局部变量和成员变量的声明和使用
局部变量:

用户自定义,在begin/end块中有效

语法:
声明变量 declare VAR_name type [default var_value];
举例:declare nickname vArchar(32);
-- 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$$
用户变量:

用户自定义,当前会话(连接)有效。类比java的成员变量

语法: 
@var_name
不需要提前声明,使用即声明
-- 赋值
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; 

3.2 入参出参

-- 语法
in | out | inout param_name type

举例

-- IN类型演示
delimiter $$
create procedure sp_param01(in age int)
begin
	set @user_age = age;
end$$
call sp_param01(10) $$
select @user_age$$
-- OUT类型,只负责输出!
-- 需求:输出传入的地址字符串对应的部门编号。
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;
-- INOUT类型 
delimiter $$
create procedure sp_param03(inout name varchar(49))
begin
	set name = concat('hello' ,name);
end$$
delimiter ;

set @user_name = '小明';
call sp_param03(@user_name);
select @user_name;

3.3 流程控制-判断

官网说明
https://dev.mysql.com/doc/refman/5.6/en/flow-control-statements.html
if
-- 语法
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									';
delimiter $$
-- DROP PROCEDURE IF EXISTS sp_param04;
create procedure sp_param05(in ages timestamp)
begin
 declare result varchar(32);
 if timestampdiff(year,ages,now())>40 
 	then set result = '元老';
 elseif timestampdiff(year,ages,now())>38 
 	then set result = '老员工';
 ELSE 
 	SET result = '新手';
 end if;
 select result;
end $$
delimiter;
 
call sp_param05('1970-02-26 10:00:25');
-- 注意:MYSQL时间戳必须从1970年开始
case

此语法是不仅可以用在存储过程,查询语句也可以用!

-- 语法一(类比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 ;

3.4 流程控制-循环

loop
-- 语法
[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$$
repeat

MYSQL存储过程专题-注释详解

[begin_label:] REPEAT
    statement_list
UNTIL search_condition	-- 直到…为止,才退出循环
END REPEAT [end_label]
-- 需求:循环打印1到10
delimiter $$
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$$
while
类比java的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$$

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

leave
类比java的breake
-- 退出 LEAVE can be used within BEGIN ... END or loop constructs (LOOP, REPEAT, WHILE).
LEAVE label
iterate
类比java的continue
-- 继续循环 ITERATE can appear only within LOOP, REPEAT, and WHILE statements
ITERATE label

3.6 游标

用游标得到某一个结果集,逐行处理数据。

类比jdbc的ResultSet
-- 声明语法
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;
-- 其次声明游标:游标值为query(dept_name)得到的table(e.empno,e.ename,e.sal)
	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 句柄:
-- 关于句柄:https://blog.csdn.net/QQ_43427482/article/details/109898934
-- 看完还没理解,再看:https://www.cnbLOGs.com/phpper/p/7587556.html
-- 这里涉及了SQL STATE:https://blog.csdn.net/u014653854/article/details/78986780
-- 声明handler句柄:当每条SQL传递ERROR STATE为没有值的报错时,设定变量lp_flag为非真,同时继续执行SQL(如不声明,当某条循环报错时,整个SQL将直接停止循环)
	declare continue handler for NOT FOUND set lp_flag = false;
-- 打开游标
	open emp_cursor;
-- 开启LOOP循环:emp_loop
	emp_loop:loop
-- 将游标值传递给三个变量
		fetch emp_cursor into e_no,e_name,e_sal;
-- 如果变量lp_flag为真,则获取这三个参数的值;否则打断emp_loop循环
		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$$
-- 恢复;结束符
delimiter;

-- 使用该存储过程并传参
call sp_create_table02('RESEARCH');

特别注意:

在语法中,变量声明、游标声明、handler声明是必须按照先后顺序书写的,否则创建存储过程出错。

3.7 存储过程中的handler

handler句柄用于定义条件处理

DECLARE handler_action HANDLER
    FOR condition_value [, condition_value] ...
    statement

handler_action: {
    CONTINUE  -- 继续执行
  | EXIT      -- 退出执行
  | UNDO      -- 什么都不做
}

CONTINUE: Execution of the current program continues. -- 继续执行当前程序
EXIT: Execution terminates for the BEGIN ... END compound statement in which the handler is declared. This is true even if the condition occurs in an inner block. -- 停止执行在handler被声明的BEGIN... END的组合程序,即使是在程序内部发生该条件。

condition_value: {
    mysql_error_code
  | SQLSTATE [VALUE] sqlstate_value
  | condition_name
  | SQLWARNING
  | NOT FOUND
  | SQLEXCEPTION
}

SQLWARNING: Shorthand for the class of SQLSTATE values that begin with '01'. -- 即SQL STATE以01开头的集合代称
NOT FOUND: Shorthand for the class of SQLSTATE values that begin with '02'. -- 即SQL STATE以O2开头的集合代称
SQLEXCEPTION: Shorthand for the class of SQLSTATE values that do not begin with '00', '01', or '02'. -- 即SQL STATE不以00、01、02开头的集合代称
-- 各种写法:
	DECLARE exit HANDLER FOR SQLSTATE '42S01' set @res_table = 'EXISTS';
	DECLARE continue HANDLER FOR 1050 set @res_table = 'EXISTS';
	DECLARE continue HANDLER FOR not found set @res_table = 'EXISTS';

4.练习

——大家注意,存储过程的业务过程在java代码中一般也可以实现,我们下面的需求是为了练习存储过程

4.1 利用存储过程更新数据

为某部门(需指定)的人员涨薪100;如果是公司总裁,则不涨薪。
delimiter // -- 定义结束符
create procedure high_sal(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句柄(条件处理)
	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 -- 流程控制
			if e_name = 'king' then 
				iterate emp_loop; -- 继续循环
			else 
				update emp e set e.sal = e.sal + 100 where e.empno = e_no; -- 更新数据
			end if;
		else
			leave emp_loop; -- 离开循环
		end if; -- 结束流程
		
	end loop emp_loop; -- 结束循环
	set @end_falg = 'exit_flag'; -- 声明用户变量
	close emp_cursor; -- 变比游标
end // -- 结束存储过程
delimiter; -- 恢复结束符


call high_sal('ACCOUNTING');

附:本例建表SQL

CREATE TABLE `dept` (
	`deptno` INT(11) NOT NULL COMMENT '部门编号',
	`dname` VARCHAR(32) NULL COMMENT '部门名称' COLLATE 'utf8_general_ci',
	`loc` VARCHAR(64) NULL COMMENT '部门地址' COLLATE 'utf8_general_ci',
	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');
	
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
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
;

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);

CREATE TABLE `salgrade` (
	`grade` INT(11) NULL,
	`losal` INT(11) NULL,
	`hisal` INT(11) NULL
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
;
insert into salgrade values (1,700,1200);
insert into salgrade values (2,1201,1400);
insert into salgrade values (3,1401,2000);
insert into salgrade values (4,2001,3000);
insert into salgrade values (5,3001,9999);

脚本宝典总结

以上是脚本宝典为你收集整理的MYSQL存储过程专题-注释详解全部内容,希望文章能够帮你解决MYSQL存储过程专题-注释详解所遇到的问题。

如果觉得脚本宝典网站内容还不错,欢迎将脚本宝典推荐好友。

本图文内容来源于网友网络收集整理提供,作为学习参考使用,版权属于原作者。
如您有任何意见或建议可联系处理。小编QQ:384754419,请注明来意。