目录
概述
概念：
- 简单的说，存储过程就是一组SQL语句集，功能强大，可以实现一些比较复杂的逻辑功能，类似于JAVA语言中的方法;
- 存储过程就是数据库SQL语言层面的代码封装与重用
特性：
- 有输入输出参数，可以声明变量，有if/else, case,while等控制语句，通过编写存储过程，可以实现复杂的逻辑功能;
- 函数的普遍特性:模块化，封装，代码复用;
- 速度快，只有首次执行需经过编译和优化步骤，后续被调用可以直接执行，省去以上步骤;
变量
局部变量
用户自定义，在begin/end块中有效
定义方法：
语法1:
声明变量 declare var_name type [default var_value];
语法2:
select col_name [ ...] into var_name[,...]from table_name wehre condition
- col_name：参数表示查询的字段名称;
- var_name：参数是变量的名称;
- table_name：参数指表的名称;
- condition：参数指查询条件。
注意:当将查询结果赋值给变量时，该查询语句的返回结果只能是单行单列。
delimiter $$
create procedure proc02()
BEGIN
declare var_name01 varchar(20) default 'aaa'; -- 声明/定义变量，var_name01为局部变量
set var_name01 = 'zhangsan';-- 给变量赋值
select var_name01; -- 输出变量的值
END $$
delimiter;
call proc02();
-- 使用select into为变量赋值
delimiter $$
create procedure proc03()
begin
declare my_ename varchar(20);
select ename into my_ename from emp where empno=1001;
select my_ename;
end $$
delimiter;
call proc03();
用户变量
语法：
@var_name -- 不需要提前声明
用户自定义，当前会话连接有效
-- 用户变量
delimiter $$
create procedure proc04()
BEGIN
set @var_name01='beijing';
select @var_name01;
END $$
delimiter;
call proc04();系统变量
- 系统变量又分为全局变量与会话变量
- 全局变量在MYSQL启动的时候由服务器自动将它们初始化为默认值，这些默认值可以通过更改my.ini这个文件来更改。
- 会话变量在每次建立一个新的连接的时候，由MYSQL来初始化。MYSQL会将当前所有全局变量的值复制一份。来做为会话变量。
- 也就是说，如果在建立会话以后，没有手动更改过会话变量与全局变量的值，那所有这些变量的值都是一样的。
- 全局变量与会话变量的区别就在于，
- 对全局变量的修改会影响到整个服务器
- 对会话变量的修改，只会影响到当前的会话（也就是当前的数据库连接）。
- 有些系统变量的值是可以利用语句来动态进行更改的，但是有些系统变量的值却是只读的，对于那些可以更改的系统变量，我们可以利用set语句进行更改。
全局变量
语法： @@global.var_name
-- 全局变量
-- 查看全局变量
show global variables;
-- 查看某全局变量
select @@global.sort_buffer_size;
-- 修改全局变量
set global sort_buffer_size = 40000;
set @@global sort_buffer_size = 30000;会话变量
语法： @@session.var_name
-- 系统变量
-- 查看会话变量
show session variables;
-- 查看某会话变量
select @@session.auto_increment_increment;
-- 修改会话变量
set session sort_buffer_size = 40000;
set @@session sort_buffer_size = 40000;
参数传递
in
- in 表示传入的参数，可以传入数值或者变量，即使传入变量，并不会更改变量的值,可以内部更改,仅仅作用在函数范围内。
-- -------------------传入参数： in --------------------------------
-- 封装有参数的存储过程,传入员工编号，查找员工信息
delimiter $$
create procedure proc06(in empno int)
BEGIN
SELECT *from emp where emp.empno = empno;
END $$
delimiter ;
call proc06(1001);
-- 封装有参数的存储过程，可以通过传入部门名和薪餐，查询指定部门，并且薪资大于指定值的员工信息
delimiter $$
create procedure proc07(in param_dname varchar(50),in param_sal decimal(7,2))
BEGIN
select * from dept a,emp b where a.deptno = b.deptno and a.dname = param_dname and b.sal > param_sal;
END $$
delimiter;
call proc07('学工部',20000)out
-- ----------------- 传出参数: out-----------------------
-- ---返回一个值---
-- 封装有参数的存储过科，传入员工编号，返回员工名字
delimiter $$
create procedure proc08(in empno int ,out out_ename varchar(50))
begin
select ename into out_ename from emp where emp.empno = empno;
end $$
delimiter;
call proc08(1001,@o_ename);
select @o_ename;
-- ---返回多个值---
-- 封装有参数的存储过程，传入员工编号，返回员工名字和薪资
delimiter $$
create procedure proc09(in empno int ,out out_ename varchar(50),out out_sal decimal(7,2))
begin
select ename,sal into out_ename,out_sal
from emp
where emp.empno = empno;
end $$
delimiter ;
call proc09(1001,@o_ename,@o_sal);
select @o_ename,@o_sal;inout
- inout表示从外部传入的参数经过修改后可以返回的变量，既可以使用传入变量的值也可以修改变量的值（即使函数执行完)
-- 传入员工名，拼接部门号。传入薪资，求出年薪
delimiter $$
CREATE PROCEDURE proc10 ( INOUT inout_ename VARCHAR ( 20 ), INOUT inout_sal INT ) BEGIN
SELECT
concat( deptno, '_', inout_ename ) INTO inout_ename
FROM
emp
WHERE
ename = inout_ename;
SET inout_sal = inout_sal * 12;
END $$
delimiter;
-- 传入参数
SET @inout_ename = '关羽';
SET @inout_sal = 3000;
-- 调用参数
CALL proc10 ( @inout_ename, @inout_sal );
-- 输出
SELECT @inout_sal;
SELECT @inout_ename;流程控制
分支语句
if
-- 输入员工名字，判断工资水平
delimiter $$
create procedure proc12_if(in in_ename varchar(20))
begin
declare result varchar(20); -- 定义局部变量，用于存储临时变量
declare var_sal decimal(7,2);
if var_sal <10000
then set result = '试用薪资';
elseif var_sal < 20000
then set result = '转正薪资';
else
set result = '元老薪资';
end if;
select result;
END $$
delimiter;
call proc12_if('庞统')case
-- 方法1：
delimiter $$
create procedure proc14_case(in pay_type int)
BEGIN
case pay_type
when 1 then select '微信支付' ;
when 2 then select '支付宝';
when 3 then select '银行卡';
else select '其他';
end case;
END $$
delimiter;
call proc14_case(1)
-- 方法2：
delimiter $$
create procedure proc15_case(in pay_type int)
BEGIN
case
when pay_type=1 then select '微信支付' ;
when pay_type=2 then select '支付宝';
when pay_type=3 then select '银行卡';
else select '其他';
end case;
END $$
delimiter;
call proc15_case(1)循环语句
循环控制:
- leave类似于break，跳出,结束当前所在的循环
- iterate类似于continue，继续,结束本次循环，继续下一次
while
while
while + leave
while + iterate
repeat
loop
游标 cursor
定义：
游标(cursor)是用来存储查询结果集的数据类型,在存储过程和函数中可以使用光标对结果集进行循环的处理。光标的使用包括光标的声明、OPEN、FETCH和CLOSE.
-- 需求 输入部门名，查询该部门员工的编号、姓名、薪资
delimiter $$
create procedure proc19_cursor(in para_dname varchar(50) );
begin
-- 定义局部变量
declare var_empno int;
declare var_ename varchar(50);
declare var_sal DECIMAL(7,2);
-- 声明游标
declare my_cursor cursor for
select empno,ename,SMALLINT
from dept a,emp b
where a.deptno = b.deptno and a.dname = para_dname;
-- 打开游标
open my_cursor；
-- 通过游标获取值
fetch my_cursor into var_empno,var_ename,var_sal;
select var_empno,var_ename,var_sal;
-- 关闭游标
close my_cursor
end $$
delimiter;
call proc19_cursor('销售部')异常处理 ---- 句柄handler
语法：
在语法中，变量声明、游标声明、handler声明是必须按照先后顺序书写的，否则创建存储过程出错。
-- 游标 + 句柄
/*
DECLARE handler_action HANDLER
FOR condition_value [, condition_value] ...
statement
handler_action: {
CONTINUE
| EXIT
| UNDO
}
condition_value: {
mysql_error_code
| condition_name
| SQLWARNING
| NOT FOUND
特别注意：
在语法中，变量声明、游标声明、handler声明是必须按照先后顺序书写的，否则创建存储过程出错。
*/
use mysql7_procedure;
drop procedure if exists proc21_cursor_handler;
-- 需求：输入一个部门名，查询该部门员工的编号、名字、薪资 ，将查询的结果集添加游标
delimiter $$
create procedure proc21_cursor_handler(in in_dname varchar(50))
begin
-- 定义局部变量
declare var_empno int;
declare var_ename varchar(50);
declare var_sal decimal(7,2);
-- 定义标记值
declare flag int default 1;
-- 声明游标
declare my_cursor cursor for
select empno,ename,sal
from dept a, emp b
where a.deptno = b.deptno and a.dname = in_dname;
-- 定义句柄：定义异常的处理方式
/*
1:异常处理完之后程序该怎么执行
continue :继续执行剩余代码
exit : 直接终止程序
undo: 不支持
2: 触发条件
条件码:
1329
条件名：
SQLWARNING
NOT FOUND
SQLEXCEPTION
3:异常触发之后执行什么代码
设置flag的值 ---》 0
*/
declare continue handler for 1329 set flag = 0;
-- 打开游标
open my_cursor;
-- 通过游标获取值
label:loop
fetch my_cursor into var_empno, var_ename,var_sal;
-- 判断flag，如果flag的值为1，则执行，否则不执行
if flag = 1 then
select var_empno, var_ename,var_sal;
else
leave label;
end if;
end loop label;
-- xxxxx
-- 关闭游标
close my_cursor;
end $$;
delimiter ;
call proc21_cursor_handler('教研部');
drop table user_2021_11_03;
存储函数
-- 使用存储函数可能会报错，因此需要添加允许创建函数权限信任
set global log_bin_trust_function_creators = true;
-- 创建存储函数 -- 没有参数
drop function if exists my_fun1_emp;
delimiter $$
create function myfunc1_emp() returns int
begin
-- 定义局部变量
declare cnt int default 0;
select count(*) into cnt from emp;
return cnt;
END $$
delimiter ;
select myfunc1_emp();
-- 创建存储函数 -- 有输入参数
-- 需求：传入一个员工的编号，返回员工姓名
drop function if exists my_fun2_emp;
delimiter $$
create function my_fun2_emp(in_empno int) returns varchar(50)
begin
-- 定义局部变量
declare out_ename varchar(50);
select ename into out_ename from emp where empno = in_empno;
return out_ename;
END $$
delimiter ;
select my_fun2_emp(1009);
对比存储函数和存储过程
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
