Skip to content

Latest commit

 

History

History
306 lines (267 loc) · 6.09 KB

10-存储过程.md

File metadata and controls

306 lines (267 loc) · 6.09 KB

存储过程

简介

  • 存储过程是先经过编译并存储在数据库中的一段 SQL 语句的集合
  • 调用存储过程可以简化应用开发人员的很多工作
  • 减少数据库和应用服务器之间的传输, 对于提高数据处理的效率很有好处
  • 存储过程思想上很简单, 就是数据库 SQL 语句层面的代码封装与重用
  • 特点
    • 封装, 复用
    • 可以传递参数, 也可以返回数据
    • 减少网络交互, 效率提升
  • 命令行中修改 SQL 结束符
    • delimiter

基本语法

use `java_basic`;
create procedure p()
begin
    select count(*) from `student`;
end;

call p();

select *
from `information_schema`.`ROUTINES`
where `ROUTINE_SCHEMA` = 'java_basic';

show create procedure func;

drop procedure if exists p;

变量

  • 系统变量
    • 分类
      • global
      • session
    • 在 session 或 global 中的设置在重启后都会失效, 需要永久设置需在配置文件中修改
  • 用户定义变量
    • 作用域为当前会话
  • 局部变量
    • declare
    • 作用域为 begin...end 之间
  • 存储过程的参数
    • in(默认): 入参
    • out: 出参
    • inout: 入/出参
  • if
    • if 条件 then 代码
    • elseif 条件 then 代码
    • ...
    • else 代码
    • end if;
  • case
    • 用法 1
      • case 变量
      • when 值 then 代码
      • ...
      • else 代码
      • end case;
    • 用法 2
      • case
      • when 条件 then 代码
      • ...
      • else 代码
      • end case;
  • while
    • while 条件
    • do
    • 代码
    • end while;
  • repeat
    • repeat
    • 代码
    • until
    • 结束条件
    • end repeat;
  • loop
    • 关键字
      • leave 标签
        • 跳出循环
      • iterate 标签
        • 进入到下一次循环
    • 标签: loop
    • 代码
    • end loop 标签;
  • 游标
  • 条件处理程序
    • action
      • continue
      • exit
    • condition
      • sqlstate: 指定的状态码
      • sqlwarning: 01 开头 sqlstate 代码的简写
      • not found: 02 开头 sqlstate 代码的简写
      • sqlexception: 没有被 sqlwarning 或 not found 捕获的 sqlstate 代码的简写
-- 系统变量
show session variables;
show global variables like '%commit%';
select @@session.autocommit;

set @@session.autocommit = 1;
-- 用户定义变量
set @name = 'stu01';
set @gender := 1,@age := 18;

select @name, @gender, @age;

select count(*)
into @counter
from `student`;

select @counter;
-- 局部变量
drop procedure if exists p;
create procedure p()
begin
    declare stu_count int;
    set stu_count := 0;
    select count(*) into stu_count from `student`;
    select stu_count;
end;

call p();
-- if
drop procedure if exists p;
create procedure p(in score int, out message varchar(32))
begin
    if score < 0 or score > 100 then
        set message := 'ERR';
    elseif score < 60 then
        set message := 'D';
    elseif score < 70 then
        set message := 'C';
    elseif score < 80 then
        set message := 'B';
    else
        set message := 'A';
    end if;
end;

call p(100, @message);
select @message;
-- case
drop procedure if exists p;
create procedure p(in gender int)
begin
    case gender
        when 1 then select '男';
        when 2 then select '女';
        else select 'ERR';
        end case;
end;

call p(1);
call p(2);
call p(0);

drop procedure if exists p;
create procedure p(in month int, out message varchar(32))
begin
    case
        when 1 <= month and month <= 3 then set message := '第一季度';
        when 4 <= month and month <= 6 then set message := '第二季度';
        when 7 <= month and month <= 9 then set message := '第三季度';
        when 10 <= month and month <= 12 then set message := '第四季度';
        else set message := 'ERR';
        end case;
end;

call p(12, @message);
select @message;
-- while
drop procedure if exists p;
create procedure p(in n int)
begin
    declare sum int default 0;
    while n > 0
        do
            set sum := sum + n;
            set n := n - 1;
        end while;
    select sum;
end;

call p(10);
-- repeat
drop procedure if exists p;
create procedure p(in n int)
begin
    declare sum int default 0;
    repeat
        set sum := sum + n;
        set n := n - 1;
    until
        n = 0
        end repeat;
    select sum;
end;

call p(10);
drop procedure if exists func;
create procedure p(in n int)
begin
    declare sum int default 0;
    sum_loop:
    loop
        if n = 0 then
            leave sum_loop;
        end if;
        if n % 2 = 0 then
            set n := n - 1;
            iterate sum_loop;
        end if;
        set sum := sum + n;
        set n := n - 1;
    end loop sum_loop;
    select sum;
end;

call p(10);
-- 游标
drop procedure if exists p;
create procedure p(in min_age int)
begin
    declare stu_name varchar(32);
    declare stu_age tinyint unsigned;
    declare stu_cur cursor for select `name`, `age` from `student` where `age` >= min_age;
    declare exit handler for sqlstate '02000' close stu_cur;

    create table if not exists student_temp
    (
        `id`   int auto_increment primary key not null comment '编号',
        `name` varchar(32)                    not null comment '姓名',
        `age`  tinyint unsigned               not null comment '年龄'
    ) engine = memory;

    truncate table `student_temp`;

    open stu_cur;
    while true
        do
            fetch stu_cur into stu_name,stu_age;
            insert into `student_temp` (`name`, `age`) value (stu_name, stu_age);
        end while;
    close stu_cur;
end;

存储函数

  • 语法
    • create function 函数名(形参列表) returns 类型
    • 特征
    • begin
    • 代码
    • return ...
    • end;
  • 特征(characteristic)
    • deterministic:
    • no sql: 不含 SQL 语句
    • reads sql data:
-- 存储函数
drop function if exists f;
create function f(min int, max int) returns int
    no sql
begin
    return rand() * (max - min) + min;
end;

select f(200, 100.0);