例如：以下就是进行封装成固定的代码块
create procedure proc01() begin select empno,ename from emp; end $$ delimiter;2.调用存储过程：
call 定义的过程名称
例如：
call proc01();2.声明/定义变量(用户变量，全局变量，会话变量) 1.定义/声明变量;
declare var_name01 varchar(20) default 'aaa';
利用存储过程改变声明变量
delimiter $$ create procedure proc02() begin declare var_name01 varchar(20) default 'aaa'; -- 声明/定义变量 set var_name01 = 'shangsan'; select var_name01; end $$ delimiter; call proc02();2.定义用户变量;
@var_name01;
例如：
delimiter $$ create procedure proc04() begin set @var_name01 = '北京'; end $$ delimiter; call proc04(); select @var_name01;3.定义全局变量：
@@global.var_name
-- 查看全局变量 show global variables; -- 查看某个局部变量 select @@global.auto_increment_increment; -- 修改全局变量 set global sort_buff_size = 50000; set @@global.sort_buff_size = 50000;4.定义会话变量
用法于与全局变量和用户变量类似
@@session.var_name
只在当前会话下起作用
3.关键字in,out,inout的使用 indelimiter $$
create procedure proc06(in param_empno int)
begin
select * from emp where empno = param_empno;
end $$
delimiter;
call proc06(1001);
delimiter $$
create procedure proc07(in dname varchar(50), in sal decimal(7,2))
begin
select * from dept a,emp b where a.deptno = b.deptno and a.dname = dname and b.sal = sal;
end $$
delimiter;
call proc07('学工部'，20000);
out关键字：
delimite $$ create procedure proc08(in in_empno int,out out_name varchar(50)) begin select ename into out_ename from emp where empno = in_empno; end $$ delimiter; call proc08(1001,@o_ename); select @o_ename;inout关键字：
-- inout
delimiter $$
create procedure proc10(inout num int)
begin
set num = num * 10;
end $$
delimiter ;
set @inout_num = 3;
call proc10(@inout_num);
select @inout_num;
-- 传入员工名字，拼接部门号，传入薪资，求出年薪
delimiter $$
create procedure proc11(inout ename varchar(50),inout sal int)
begin
select concat_ws('_',deptno,ename) from emp where emp.ename = ename;
set sal = sal * 12;
end $$
delimiter;
set @inout_name='关羽';
set @inout_sal = 3000;
call proc11(@inout_name,@inout_sal);
select @inout_sal;
select@inout_name;
4.存储过程中的判断
-- 控制-判断
-- 语法
-- 案例1
-- 输入学生的成绩，来判断成绩的级别
delimiter $$
create procedure proc_12_if(in score int)
begin
if score <60
then
select '不及格';
elseif score>=60 and score<80
then
select'及格';
elseif score>=80 and score<90
then
select '良好';
elseif score>=90 and score<100
then
select '优秀';
elseif score>100
then
select '错误';
end if;
end $$
delimiter ;
set @score = 55;
call proc_12_if(@score);
delimiter $$
create procedure proc_13_if(in in_name varchar(20))
begin
declare var_sal decimal(7,2);
declare result varchar(20);
seelct sal into var_sal from emp where ename = in_name;
if var_sal<10000
then
set result = '试用薪资';
elseif var_cal < 20000
then
set result = '转正薪资';
else
set result = '元老薪资';
end if;
end $$
delimiter ;
-- 调用：
call proc_13_if('关羽')
-- 流程控制语句：case
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);
-- 格式2
delimiter $$
create procedure proc_14_case(in score int)
begin
case
when score <60
then
select '不及格';
when score>=60 and score<80
then
select'及格';
when score>=80 and score<90
then
select '良好';
when score>=90 and score<100
then
select '优秀';
when score>100
then
select '错误';
end case;
end $$
delimiter ;
5.存储过程中的循环：
-- 循环:
create table user(
uid int primary key,
username varchar(50),
password varchar(50)
);
delimiter $$
create procedure proc16_while(in insertCount int)
begin
declare i int default 1;
lable: while i<=insertCount do
insert into user(uid,username,password) values (i,concat('user-',i),'123456');
set i = i+1;
end while lable;
end $$
delimiter;
call proc17_while_leave(10);
-- while+leave
truncate table user;
delimiter $$
create procedure proc17_while_leave(in insertCount int)
begin
declare i int default 1;
lable: while i<=insertCount do
insert into user(uid,username,password) values (i,concat('user-',i),'123456');
if i = 5 then
leave lable;
end if;
set i = i+1;
end while lable;
end $$
delimiter;
-- while + iterate 跳过本次循环，进行下次循环
delimiter $$
create procedure proc18_while_iterate(in insertCount int)
begin
declare i int default 1;
lable: while i<=insertCount do
insert into user(uid,username,password) values (i,concat('user-',i),'123456');
set i = i+1;
if i = 5 then
iterate lable;
end if;
end while lable;
end $$
delimiter;
-- repeat
delimiter $$
create procedure proc18_repeat(in insertCount int)
begin
declare i int default 1;
lable: repeat
insert into user(uid,username,password) values (i,concat('user-',i),'123456');
set i = i+1;
until i>insertCount
end repeat lable;
end $$
delimiter;
call proc18_repeat(10);
-- loop循环
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(10);
6.存储过程中的游标
-- 游标(cursor) -- 声明游标 -- 打开游标 -- 通过游标获取值 -- 关闭游标 delimiter $$ create procedure proc_18_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; -- 关闭游标 close my_cursor; end $$ delimiter; call proc19_cursor();7.存储结构中的异常处理：
delimiter $$ create procedure proc_21_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; -- 定义句柄：定义异常处理方式 declare continue handler for 1329 set flag = 0; -- 打开游标 open my_cursor; -- 通过游标获取值 label: loop fetch my_cursor into var_empno,var_ename,var_sal; -- if flag = 1 then select var_empno,var_ename,var_sal; else leave label; end loop label; -- 关闭游标 close my_cursor; end $$ delimiter; call proc21_cursor();
