简介:个人学习分享,如有错误,欢迎批评指正。
一、概念
简单的说,存储过程就是一组SQL语句集,功能强大,可以实现一些比较复杂的逻辑功能,类似于JAVA语言中的方法;
存储过就是数据库 SQL 语言层面的代码封装与重用。
有输入输出参数,可以声明变量,有if/else, case,while等控制语句,通过编写存储过程,可以实现复杂的逻辑功能;
函数的普遍特性:模块化,封装,代码复用;
速度快,只有首次执行需经过编译和优化步骤,后续被调用可以直接执行,省去以上步骤;
格式
delimiter 自定义结束符号
create procedure 储存名([ in ,out ,inout ] 参数名 数据类形…)
begin
sql语句
end 自定义的结束符合
delimiter ;
数据准备
-- 1:创建数据库
create database mydb7_procedure;
use mydb7_procedure;
-- 2:创建数据
-- 创建部门表
create table dept(
deptno int primary key, -- 部门编号
dname varchar(14), -- 部门名称
loc varchar(13) -- 部门地址
);
insert into dept values(10,'教研部','北京');
insert into dept values(20,'学工部','上海');
insert into dept values(30,'销售部','广州');
insert into dept values(40,'财务部','武汉');
create table emp(
empno int, -- 员工编号
job varchar(50), -- 工作名字
ename varchar(50), -- 员工名字
mar int, -- 上级领导编号
hiredate date, -- 入职日期
sal int, -- 薪资
COMM int, -- 奖金
deptno int -- 部门编号
);
-- 添加部门和员工之间的主外键关系
alter table emp add constraint foreign key emp(deptno) references dept (deptno);
insert into emp values(1001,'文员','甘宁',1013,'2000-12-17',8000,null,20);
insert into emp values(1002,'销售员','黛绮丝',1006,'2001-02-20',16000,3000,30);
insert into emp values(1003,'销售员','殷天正',1006,'2001-02-22',12500,5000,30);
insert into emp values(1004,'经理','刘备',1009,'2001-04-02',29750,null,20);
insert into emp values(1005,'销售员','谢逊',1006,'2001-09-28',12500,14000,30);
insert into emp values(1006,'经理','关羽',1009,'2001-05-01',28500,null,30);
insert into emp values(1007,'经理','张飞',1009,'2001-06-09',24500,null,10);
insert into emp values(1008,'分析师','诸葛亮',1004,'2007-04-19',30000,null,20);
insert into emp values(1009,'董事长','曾阿牛',null,'2001-11-17',50000,null,10);
insert into emp values(1010,'销售员','韦一笑',1006,'2001-09-08',15000,0,30);
insert into emp values(1011,'文员','周泰',1008,'2007-05-23',11000,null,20);
insert into emp values(1012,'文员','程普',1006,'2001-12-03',9500,null,30);
insert into emp values(1013,'分析师','庞统',1004,'2001-12-03',30000,null,20);
insert into emp values(1014,'文员','黄盖',1007,'2002-01-23',13000,null,10);
create table salgrade(
grade int, -- 等级
losal double, -- 最低工资
hisal double -- 最高工资
);
insert into salgrade values(1,7000,12000);
insert into salgrade values(2,12010,14000);
insert into salgrade values(3,14010,20000);
insert into salgrade values(4,20010,30000);
insert into salgrade values(5,30010,99990);
二、变量
1.局部变量
用户自定义,在begin/end块中有效。
语法
语法: 声明变量 declare var_name type [default var_value];
举例:declare nickname varchar(32);
-- 切换到目标数据库
USE mydb7_procedure;
delimiter $$
create procedure proc02()
begin
declare var_name01 varchar(20) default 'aaa'; -- 定义局部变量
set var_name01 = 'zhangsan'; -- 给变量赋值
select var_name01; -- 输出变量的值
end $$
delimiter ;
-- 调用存储过程
call proc02();
MySQL 中还可以使用 SELECT…INTO 语句为变量赋值。其基本语法如下:
select col_name […] into var_name[,…]
from table_name where condition
其中: col_name 参数表示查询的字段名称; var_name 参数是变量的名称; table_name 参数指表的名称;
condition 参数指查询条件。 注意:当将查询结果赋值给变量时,该查询语句的返回结果只能是单行单列。
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();
2.用户变量
用户自定义,当前会话(连接)有效。类比java的成员变量
语法
语法: @var_name 不需要提前声明,使用即声明
代码
delimiter $$
create procedure proc04()
begin
set @var_name01 = 'ZS';
select @var_name01;
end $$
delimiter ;
call proc04() ;
select @var_name01 ; --可以看到结果
3.系统变量
介绍
系统变量又分为全局变量与会话变量
全局变量在MYSQL启动的时候由服务器自动将它们初始化为默认值,这些默认值可以通过更改my.ini这个文件来更改。
会话变量在每次建立一个新的连接的时候,由MYSQL来初始化。MYSQL会将当前所有全局变量的值复制一份。来做为会话变量。
也就是说,如果在建立会话以后,没有手动更改过会话变量与全局变量的值,那所有这些变量的值都是一样的。
全局变量与会话变量的区别就在于,对全局变量的修改会影响到整个服务器,但是对会话变量的修改,只会影响到当前的会话
(也就是当前的数据库连接)。
有些系统变量的值是可以利用语句来动态进行更改的,但是有些系统变量的值却是只读的,对于那些可以更改的系统变量,我们可以利用set语句进行更改。
3.1. 系统变量-全局变量
由系统提供,在整个数据库有效。
格式
语法: @@global.var_name
代码
-- 查看全局变量
show global variables;
-- 查看某全局变量
select @@global.auto_increment_increment;
-- 修改全局变量的值
set global sort_buffer_size = 40000;
set @@global.sort_buffer_size = 40000;
select @@global.sort_buffer_size;
3.2. 系统变量-会话变量
由系统提供,当前会话(连接)有效
格式
语法: @@session.var_name
代码
-- 查看会话变量
show session variables;
-- 查看某会话变量
select @@session.auto_increment_increment;
-- 修改会话变量的值
set session sort_buffer_size = 50000;
set @@session.sort_buffer_size = 50000 ;
select @@session.sort_buffer_size;
三、参数传递
1.参数传入-in
in 表示传入的参数, 可以传入数值或者变量,即使传入变量,并不会更改变量的值,可以内部更改,仅仅作用在函数范围内。
-- 封装有参数的存储过程,传入员工编号,查找员工信息
delimiter $$
create procedure dec_param01(in param_empno varchar(20))
begin
select * from emp where empno = param_empno;
end $$
delimiter ;
call dec_param01('1001');
call dec_param01('1002');
-- 封装有参数的存储过程,可以通过传入部门名和薪资,查询指定部门,并且薪资大于指定值的员工信息
delimiter $$
create procedure proc07(in dname varchar(50),in sal decimal(7,2))
begin
select * from dept a, emp b where b.sal > sal and a.dname = dname;
end $$
delimiter ;
call proc07('学工部',20000);
call proc07('销售部',10000);
2.参数传出-out
out 表示从存储过程内部传值给调用者
-- ---------传出参数:out---------------------------------
use mydb7_procedure;
-- 封装有参数的存储过程,传入员工编号,返回员工名字
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(1002, @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_dname,@o_sal);
select @o_dname;
select @o_sal;
3.参数传入传出-inout
inout 表示从外部传入的参数经过修改后可以返回的变量,既可以使用传入变量的值也可以修改变量的值(即使函数执行完)
-- 传入一个数字,传出这个数字的10倍值
delimiter $$
create procedure proc10(inout num int)
begin
set num = num * 10;
end $$
delimiter ;
set @inout = 3;
call proc10(@inout);
select @inout;
-- 传入员工名,拼接部门号,传入薪资,求出年薪
-- 关羽 ----> 30_关羽
delimiter $$
create procedure proc11(inout inout_ename varchar(50), inout inout_sal int)
begin
select concat_ws('_',deptno,ename) into inout_ename from emp where emp.ename = inout_ename;
set inout_sal = inout_sal * 12;
end $$
delimiter ;
set @inout_ename = '关羽';
set @inout_sal = 3000;
call proc11(@inout_ename,@inout_sal);
select @inout_ename ;
select @inout_sal;
三者区别
in 输入参数,意思说你的参数要传到存过过程的过程里面去,在存储过程中修改该参数的值不能被返回
out 输出参数:该值可在存储过程内部被改变,并向外输出
inout 输入输出参数,既能输入一个值又能传出来一个值)
五、流程控制
1.分支
1.1. if
IF语句包含多个条件判断,根据结果为TRUE、FALSE执行语句,与编程语言中的if、else if、else语法类似,其语法格式如下:
– 语法
if search_condition_1 then statement_list_1
[elseif search_condition_2 then statement_list_2] …
[else statement_list_n]
end if
/*
sal < 10000 :试用薪资
sal >= 10000 and sal < 20000 :转正薪资
sal >= 20000 :元老薪资
*/
delimiter $$
create procedure proc_13_if(in in_ename varchar(20))
begin
declare var_sal decimal(7,2);
declare result varchar(20);
select sal into var_sal from emp where ename = in_ename;
if var_sal < 10000
then
set result = '试用薪资';
elseif var_sal < 20000
then
set result = '转正薪资' ;
else
set result = '元老薪资' ;
end if;
select result;
end $$
delimiter ;
-- 调用
call proc_13_if('关羽');
call proc_13_if('程普');
1.2. case
CASE是另一个条件判断的语句,类似于编程语言中的switch语法
– 语法一(类比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
-- 流程控制语句:case
/*
支付方式:
1 微信支付
2 支付宝支付
3 银行卡支付
4 其他方式支付
*/
-- 格式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(2);
call proc14_case(4);
-- 格式2
delimiter $$
create procedure proc_15_case(in score int)
begin
case
when score < 60
then
select '不及格';
when score < 80
then
select '及格' ;
when score >= 80 and score < 90
then
select '良好';
when score >= 90 and score <= 100
then
select '优秀';
else
select '成绩错误';
end case;
end $$
delimiter ;
call proc_15_case(88);
2.循环
循环是一段在程序中只出现一次,但可能会连续运行多次的代码。
循环中的代码会运行特定的次数,或者是运行到特定条件成立时结束循环。
循环分类:
while
repeat
loop
循环控制:
leave 类似于 break,跳出,结束当前所在的循环
iterate类似于 continue,继续,结束本次循环,继续下一次
1.1. while
语法
【标签:】while 循环条件 do
循环体;
end while【 标签】;
代码
-- 存储过程-循环-while
use mydb7_procedure;
-- 创建测试表
create table user (
uid int primary key,
username varchar(50),
password varchar(50)
);
/*
【标签:】while 循环条件 do
循环体;
end while【 标签】;
*/
-- 需求:向表中添加指定条数的数据
-- -------存储过程-循环-while
delimiter $$
create procedure proc16_while(in insertCount int)
begin
declare i int default 1;
label:while i <= insertCount do
insert into user(uid, username, password) values(i,concat('user-',i),'123456');
set i = i + 1;
end while label;
end $$
delimiter ;
call proc16_while(10);
-- -------存储过程-循环控制-while + leave
-- leave:直接跳出while循环
truncate table user;
delimiter $$
create procedure proc17_while_leave(in insertCount int) -- 10
begin
declare i int default 1;
label:while i <= insertCount do -- 10
insert into user(uid, username, password) values(i,concat('user-',i),'123456');
if i = 5 then
leave label;
end if;
set i = i + 1;
end while label;
select '循环结束';
end $$
delimiter ;
call proc17_while_leave(10);
-- -------存储过程-while+iterate
-- iterate:跳过本次循环的剩余代码,进入下一次循环
create table user2 (
uid int ,
username varchar(50),
password varchar(50)
);
use mydb7_procedure;
truncate table user2;
delimiter $$
create procedure proc17_while_iterate(in insertCount int) -- 10
begin
declare i int default 0;
label:while i < insertCount do -- 10
set i = i + 1;
if i = 5 then
iterate label;
end if;
insert into user2(uid, username, password) values(i,concat('user-',i),'123456');
end while label;
select '循环结束';
end $$
delimiter ;
call proc17_while_iterate(10); -- 1 2 3 4 6 7 8 9 10
1.2. repeat
语法
[标签:]repeat
循环体;
until 条件表达式
end repeat [标签];
代码
-- -------存储过程-循环控制-repeat
use mydb7_procedure;
truncate table user;
delimiter $$
create procedure proc18_repeat(in insertCount int)
begin
declare i int default 1;
label:repeat
insert into user(uid, username, password) values(i,concat('user-',i),'123456');
set i = i + 1;
until i > insertCount
end repeat label;
select '循环结束';
end $$
delimiter ;
call proc18_repeat(100);
1.3. loop
1.4. 循环控制
语法
[标签:] loop
循环体;
if 条件表达式 then
leave [标签];
end if;
end loop;
-- -------存储过程-循环控制-loop
/*
[标签:] loop
循环体;
if 条件表达式 then
leave [标签];
end if;
end loop;
*/
use mysql7_procedure;
truncate table user;
delimiter $$
create procedure proc19_loop(in insertCount int)
begin
declare i int default 1;
label: loop
insert into user(uid, username, password) values(i,concat('user-',i),'123456');
set i = i + 1 ;
if i > insertCount
then
leave label;
end if ;
end loop label;
end $$
delimiter ;
call proc19_loop(100);
六、游标
游标(cursor)是用来存储查询结果集的数据类型 , 在存储过程和函数中可以使用光标对结果集进行循环的处理。光标的使用包括光标的声明、OPEN、FETCH 和 CLOSE.
语法
– 声明语法
declare cursor_name cursor for select_statement
– 打开语法
open cursor_name
– 取值语法
fetch cursor_name into var_name [, var_name] …
– 关闭语法
close cursor_name
代码
-- 操作游标(cursor)
-- 声明游标
-- 打开游标
-- 通过游标获取值、
-- 关闭游标
use mydb7_procedure;
drop procedure if exists proc19_cursor;
-- 需求:输入一个部门名,查询该部门员工的编号、名字、薪资 ,将查询的结果集添加游标
delimiter $$
create procedure proc19_cursor(in in_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,sal
from dept a, emp b
where a.deptno = b.deptno and a.dname = in_dname;
-- 打开游标
open my_cursor;
-- 通过游标获取值
label:loop
fetch my_cursor into var_empno, var_ename,var_sal;
select var_empno, var_ename,var_sal;
end loop label;
-- xxxxx
-- 关闭游标
close my_cursor;
end $$;
delimiter ;
call proc19_cursor('销售部');
-- 游标 + 句柄
/*
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
特别注意:
在语法中,变量声明、游标声明、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;
八、存储函数
MySQL存储函数(自定义函数),函数一般用于计算和返回一个值,可以将经常需要使用的计算或功能写成一个函数。
存储函数和存储过程一样,都是在数据库中定义一些 SQL 语句的集合。
存储函数与存储过程的区别
1.存储函数有且只有一个返回值,而存储过程可以有多个返回值,也可以没有返回值。
2.存储函数只能有输入参数,而且不能带in, 而存储过程可以有多个in,out,inout参数。
3.存储过程中的语句功能更强大,存储过程可以实现很复杂的业务逻辑,而函数有很多限制,如不能在函数中使用insert,update,delete,create等语句;
4.存储函数只完成查询的工作,可接受输入参数并返回一个结果,也就是函数实现的功能针对性比较强。
5.存储过程可以调用存储函数。但函数不能调用存储过程。
6.存储过程一般是作为一个独立的部分来执行(call调用)。而函数可以作为查询语句的一个部分来调用.
格式
创建存储函数使用create function关键字,其基本形式如下:
create function func_name ([param_name type[,…]])
returns type
[characteristic …]
begin
routine_body
end;
参数说明:
(1)func_name :存储函数的名称。
(2)param_name type:可选项,指定存储函数的参数。type参数用于指定存储函数的参数类型,该类型可以是MySQL数据库中所有支持的类型。
(3)RETURNS type:指定返回值的类型。
(4)characteristic:可选项,指定存储函数的特性。
(5)routine_body:SQL代码内容。
代码
use mydb7_procedure;
set global log_bin_trust_function_creators=TRUE; -- 信任子程序的创建者
-- 创建存储函数-没有输输入参数
drop function if exists myfunc1_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 myfunc2_emp;
delimiter $$
create function myfunc2_emp(in_empno int) returns varchar(50)
begin
declare out_name varchar(50);
select ename into out_name from emp where empno = in_empno;
return out_name;
end $$
delimiter ;
select myfunc2_emp(1008);
九、总结和练习
1.总结
2.代码练习
-- 练习
/*
创建下个月的每天对应的表user_2021_12_01、user_2022_12_02、...
需求描述:
我们需要用某个表记录很多数据,比如记录某某用户的搜索、购买行为(注意,此处是假设用数据库保存),当每天记录较多时,如果把所有数据都记录到一张表中太庞大,需要分表,我们的要求是,每天一张表,存当天的统计数据,就要求提前生产这些表——每月月底创建下一个月每天的表!
*/
-- 思路:循环构建表名 user_2021_11_01 到 user_2020_11_30;并执行create语句。
create database mydb18_proc_demo;
use mydb18_proc_demo;
drop procedure if exists proc22_demo;
delimiter $$
create procedure proc22_demo()
begin
declare next_year int; -- 下一个月的年份
declare next_month int; -- 下一个月的月份
declare next_month_day int;-- 下一个月最后一天的日期
declare next_month_str varchar(2); -- 下一个月的月份字符串
declare next_month_day_str varchar(2);-- 下一个月的日字符串
-- 处理每天的表名
declare table_name_str varchar(10);
declare t_index int default 1;
-- declare create_table_sql varchar(200);
-- 获取下个月的年份
set next_year = year(date_add(now(),INTERVAL 1 month)); -- 2021
-- 获取下个月是几月
set next_month = month(date_add(now(),INTERVAL 1 month)); -- 11
-- 下个月最后一天是几号
set next_month_day = dayofmonth(LAST_DAY(date_add(now(),INTERVAL 1 month))); -- 30
if next_month < 10
then set next_month_str = concat('0',next_month); -- 1 ---》 01
else
set next_month_str = concat('',next_month); -- 12
end if;
while t_index <= next_month_day do
if (t_index < 10)
then set next_month_day_str = concat('0',t_index);
else
set next_month_day_str = concat('',t_index);
end if;
-- 2021_11_01
set table_name_str = concat(next_year,'_',next_month_str,'_',next_month_day_str);
-- 拼接create sql语句
set @create_table_sql = concat(
'create table user_',
table_name_str,
'(`uid` INT ,`uname` varchar(50) ,`information` varchar(50)) COLLATE=\'utf8_general_ci\' ENGINE=InnoDB');
-- FROM后面不能使用局部变量!
prepare create_table_stmt FROM @create_table_sql;
execute create_table_stmt;
DEALLOCATE prepare create_table_stmt;
set t_index = t_index + 1;
end while;
end $$
delimiter ;
call proc22_demo();
该内容主要是本人对哔站up黑马程序员的mysql课程的学习总结!
结~~~