新手入门MySQL——篇7

MySQL存储过程

................................
delimiter 自定义结束符号aa1
create procedure 存储名称 ([in, out, inout] 参数名 数据类型)
begin 
	sql语句
end 自定义结束符号aa1
delimiter ;
................................

1——入门探路

create database aa1;
use aa1;
delimiter &&
create procedure pro1()
	begin
		select ename, salary from employee
	end &&
delimiter ; 
call pro1() -- 调用存储过程

2——变量定义

declare 变量名称 变量类型 [default 默认值]
例子1： declare a varchar(20) default '2022-04-14';  -- {局部使用}变量定义
例子2： set a = 'hello python'  			  -- 变量赋值
例子3： select a;               			  -- 输出变量值
例子4：declare name1 varchar(20);
 select ename into name1 from employee; 
 select name1;                            -- insert into赋值

{用户变量} @变量名 -- 直接用,不用定义,使用即声明（外部可以使用）
例子1：
delimiter &&
create procedure pro02()
	begin
	 set @age = 18;
	 select @age;		
	end &&
delimiter ;;	
call pro02(); -- 18
select @age ; -- 18

系统变量

3——参数传递

• in
• out
• inout

delimiter &&
create procedure pc1( in a1 varchar(10), in a2  int(10) )
	begin
		select * from employee where ename = a1 and age > a2;
	end &&
delimiter ;;
call pc1('张三', 22)	;

delimiter &&
create procedure pro2(in a1 varchar(10), out b1 int(10) )
	begin
		select age into b1 from employee where empno = a1;
	end &&
delimiter ;;
call pro2('10001', @age1);
select @age1;
select a1, b1 into c1, d1 from employee;

传入a1,经过中间处理再次传出
delimiter &&
create procedure pr2(inout s1 int)
	begin
		set s1 = s1 * 10 + 5;
	end &&
delimiter ;;
set @ss = 12;
call pr2(@ss);
select @ss;

delimiter &&
create procedure pr2(inout name varchar(20), inout sal int)
	begin 
		select concat_ws('_', ename, deptno) into ss1 from employee where ename = name;
    set sal = sal * 12;
	end &&
delimiter ;;
set @s1 = '张三';
set @s2 = 13000;
call pr2(@s1, @s2);
select @s1;
select @s2;

4——流程控制

• if语句
• case 语句
• while – do
• repeat – until
• loop – leave

例子1：录入成绩,判断等级
delimiter &&
create procedure pr3(in score int)
	begin 
		if     score <60 then select '好可惜哦,不及格';
		elseif score <70 then select '加把油,你可以的';
		elseif score <90 then select '看好你哦';
		else select '棒棒哒(#^.^#)';
        end if;
	end && 
delimiter ;;
call pr3(65);
call pr3(75);
call pr3(98);

例子2：case语句判断付款方式
delimiter &&
create procedure pr4(in num int)
	begin 
		case num 
					when 1 then select '支付宝支付';
					when 2 then select '微信支付';
					when 3 then select '云闪付';
					else select '信用卡支付';
		end case;
	end &&
delimiter ;;
call pr5(2);

例子3：case-when判断收入等级
delimiter &&
create procedure pr5(in sal int)
	begin 
		case 
			when sal > 20000 then select '高收入群体';
			when sal < 15000 then select '中等收入';
			when sal < 10000 then select '温饱阶段';
			else select '低保群体';				
		end case;
	end &&
delimiter ;;
call pr4(10090);

5——cursor 游标

declare 游标名称 cursor for select语句   -- 声明
open 游标名称                           -- 打开
fetch 游标名称 into 变量名称             -- 取值
close 游标名称                         -- 关闭

例子4：使用游标取值
delimiter &&
create procedure pr6(in dept varchar(10))
	begin 
	-- a. 定义局部变量
			declare e_name varchar(20);
			declare e_sal decimal(7,2);
   -- b. 定义游标
		declare my_cursor cursor for
			select ename, salary from employee e, dept d
			where e.dept_no = d.deptno and d.dname = dept;

   -- c. 打开游标
		open my_cursor;
   -- d.取值
		fetch my_cursor into e_name, e_sal;
		select e_name, e_sal;
   -- e.关闭游标
		close my_cursor;
	end &&
delimiter ;;
call pr6('销售部');

6——异常处理

delimiter &&
create procedure pro7(in dname varchar(10))
	begin 
		declare var_empno int;
    declare var_ename varchar(20);
		
		declare flag int default 1;  -- 声明标记值
		
    declare cur01 cursor for     -- 声明游标
		select empno, ename from 
		dept a, emp b
    where a.deptno = b.deptno and b.deptno = dname;
		-- 定义异常处理 (条件码,条件名,触发条件,触发之后执行)
	declare continue handler for 1329 set flag=0;
		-- 打开游标
		open cur01;
		ss1: loop 
			fetch cur01 into var_empno, var_ename;
				if flag = 1 then 
					select var_empno, var_ename;
				else leave ss1;
				end if;
		end loop ss1;
		close cur01;
	end &&
delimiter ;;

7——存储函数

create function 函数名称(参数列表 参数类型) returns 类型
begin
  ....
end;

例子1：无参函数——返回数据总量
delimiter &&
create function fun1() returns int
	begin
		declare cn1 int;
		select count(*) into cn1 from emp;
		return cn1;
	end &&
delimiter ;;

例子2：有参函数——返回员工姓名
set global log_bin_trust_function_creates = true; -- 也可以在my.ini中设置
drop function if exists fun2;
delimiter &&
create function fun2(emp_no int) returns varchar(20)
	begin
		declare e_name varchar;
		select ename into e_name from emp where empno = emp_no;
		return e_name;
	end &&
delimiter ;;
select fun2(1002);