MySQL从入门到应用 第五天

1. 存储过程与函数

    1.1 概念 

    存储过程和函数是 事先经过编译并存储在数据库中的一段 SQL 语句的集合，调用存储过程和函数可以简化应用开发人员的很多工作，减少数据在数据库和应用服务器之间的传输，对于提高数据处理的效率是有好处的。

    存储过程和函数的区别在于函数必须有返回值，而存储过程没有。

    函数 ： 是一个有返回值的过程 ；

    过程 ： 是一个没有返回值的函数 ，(out类型参数类似于一个C语言中的地址传递入参，函数内部对参数改变函数外部可以获取，但不叫返回值)；

    1.2 创建存储过程：

CREATE PROCEDURE procedure_name ([ [in/out/inout] 参数名 参数类型, ...]) 

begin

    -- SQL语句

end ;

delimiter $      -- 在DOS下，重新定义语句结束符
create procedure pro_test1()
begin
     select * from account;
end$

    1.3 调用存储过程 ： call pro_name()；

    1.4 删除存储过程： DROP PROCEDURE [IF EXISTS] sp_name ；

    1.5 查看存储过程： 

        1）查看数据库所有的存储过程：select * from mysql.proc where db='db_name'；

        2）查看存储过程的状态信息：show procedure status;

        3）查看存储过程的定义： show create procedure db_name.pro_name；

    1.6 存储过程语法

        1.6.1 变量的定义与赋值

            1）变量定义：DECLARE var_name[,...] type [DEFAULT value] ；

            2）变量赋值： set赋值：SET var_name = expr [, var_name = expr]； into赋值：select 表列名 into 变量名 from 表名；

        1.6.2 if 结构

if 条件表达式1 then 执行语句1

[elseif 条件表达式2 then 执行语句2]

...

[else 执行语句3]

end if;

        1.6.3 case 结构

方式一 :

CASE 变量

WHEN 变量值1 THEN 执行语句1

[WHEN 变量值2 THEN 执行语句2]

...

[ELSE 执行语句3]

END CASE;

CASE

WHEN 条件表达式1 THEN 执行语句1

[WHEN 条件表达式2THEN 执行语句2]

...

[ELSE 执行语句3]

END CASE;

        1.6.4 while 循环结构

while 条件表达式 do    -- 表达式成立，则一直执行语句

      执行语句

end while;

         1.6.5 repeat 结构

REPEAT

    执行语句

UNTIL 条件表达式    -- 表达式成立，跳出循环不在执行语句

END REPEAT;

        1.6.6 loop 结构

[begin_label:] LOOP

     执行语句；      -- 注意执行语句中如果没有跳出循环的语句，则loop会变为死循环

END LOOP [end_label]

        1.6.7 level 结构

delimiter $

CREATE PROCEDURE pro_test11(n int )

BEGIN

    declare total int default 0 ;

    ins: LOOP

        IF n <= 0 then

            leave ins;                  --- level跳出当前循环；

        END IF;

        set total = total + n;

        set n = n - 1 ;

    END LOOP ins;

    select total;

END$

        1.6.8 光标

DECLARE cursor_name CURSOR FOR select_statement ;     -- 声明光标

OPEN cursor_name ;   -- 开启光标

DECLARE EXIT HANDLER FOR NOT FOUND set has_data = 0 ;    -- 当光标没有下一个值时，设置一个变量，用于推出循环取值

FETCH cursor_name INTO var_name [, var_name] ...              -- 遍历光标内的值

CLOSE cursor_name ;               -- 关闭光标

     1.7 存储函数

CREATE FUNCTION function_name([param type ... ])

RETURNS type

BEGIN

...

END;

2. 触发器

    2.1 介绍

        触发器是与表有关的数据库对象，指在 insert/update/delete 之前或之后，触发并执行触发器中定义的SQL语句集合。触发器的这种特性可以协助应用在数据库端确保数据的完整性 , 日志记录 , 数据校验等操作 。

        使用别名 OLD 和 NEW 来引用触发器中发生变化的记录内容，这与其他的数据库是相似的。现在触发器还只支持行级触发，不支持语句级触发。

    2.2 创建触发器

create trigger trigger_name

    before/after insert /update/delete

on tbl_name

    [ for each row ]     -- 行级触发器

begin

    trigger_stmt ;

end;

    2.3 删除触发器

drop trigger [schema_name.]trigger_name；

    2.4 查看触发器

show triggers;