MySQL存储过程

过程和函数 ( MySQL )

1、环境

1.1、delimiter

在MySQL数据库环境下，修改 delimiter 可以参照以下方式:

    mysq >  delimiter $
    mysql > select now() from dual ;
             -> select` current_timestamp from dual ;
             -> $
             
             +---------------------+
             | now()               |
             +---------------------+
             | 2018-11-29 14:23:16 |
             +---------------------+
             1 row in set (0.00 sec)
             
             +---------------------+
             | current_timestamp   |
             +---------------------+
             | 2018-11-29 14:23:16 |
             +---------------------+
             1 row in set (0.00 sec)

1.2、变量

• 用户自定义变量

使用 set 指令显式声明变量并赋值

    mysql>  set  @变量名 = 取值 ;

比如:

    mysql> set  @name = '穆桂英' ;

查询变量的值

    mysql> select @变量名 from dual ;

比如

    mysql> select  @name from dual ;

在 MySQL 中 ， @ 开头的是用户自定义变量，首次在函数或过程中使用时即声明

    调用函数或过程时即声明变量:
    mysql> call  count_human_male( @male_count ) ; 
    通过 select 可以查询该变量的值:
    mysql> select @male_count from dual ;

• MySQL数据库系统变量

比如 事务隔离级别: @@tx_isolation

    查询系统变量，获取当前事务的隔离级别
    mysql>  select @@tx_isolation from dual ;
    
    修改当前会话的事务隔离级别 ( 读未已提交 )
    mysql> set session tx_isolation = 'read-uncommitted';
    修改当前会话的事务隔离级别 ( 读已提交 )
    mysql> set session tx_isolation = 'read-committed';
    修改当前会话的事务隔离级别 ( 可重复读 )
    mysql> set session tx_isolation = 'repeatable-read';
    修改当前会话的事务隔离级别 ( 序列化 )
    mysql> set session tx_isolation = 'serializable';

比如 事务提交方式: @@autocommit

    查询系统变量，获取当前事务提交方式
    mysql>  select @@autocommit from dual ;
    1 表示 自动提交 ( 每执行一条 DML 语句就提交一次事务 )
    0 表示 手动提交 ( 需要通过 commit 来提交事务 或 通过 rollback 回滚事务 )
    
    设置事务提交方式
    mysql>  set   autocommit  =  false ;  -- 不要再自动提交
    mysql>  set   autocommit  =  true ; -- 自动提交 (默认值)

2、过程 ( procedure )

数据库中的过程( procedure ) ，就是存储过程( stored procedure )。

2.1、创建过程

创建过程的语法:

    CREATE PROCEDURE 过程名称 (  参数类型   名称   数据类型  [ ,  参数类型   名称   数据类型 ] ) 
    BEGIN
            -- 在 BEGIN  和 END 之间书写 过程要完成的操作
    END

其中:

• 参数名称之前的 参数类型分为三种类型: IN 、OUT 、INOUT
• 参数名称应该尽量避免与数据库中的列名相同
• 参数的数据类型建议指定数据类型和长度限制，比如 INT(10)

2.2、 参数类型

• IN : 传入参数

    CREATE PROCEDURE remove_human( IN hid INT(10) )
    BEGIN
        DELETE FROM t_humans WHERE id = hid ;
    END;

调用过程

    mysql> call remove_human( 12 ) ;

• OUT : 传出参数

定义带有传出参数的过程:

    CREATE PROCEDURE count_human_male ( OUT mcount INT )
    BEGIN
          SELECT count(*)  INTO mcount FROM t_humans WHERE gender = '男'  ;
    END ;

调用带有传出参数的 过程:

    mysql> CALL 过程名称( @用户变量 )

比如调用 count_human_male 过程，并向其传递参数( 这里的 @male_count 为用户自定义变量 )

    mysql>  CALL  count_human_male( @male_count ) ;

通过查询 @male_count 变量来获取 过程 传出的数值

    mysql>  SELECT @male_count FROM dual ;

• INOUT : 传入/传出参数

CREATE PROCEDURE get_name( IN hid INT(5) , INOUT x  VARCHAR(50) )
BEGIN
    SELECT name INTO x FROM t_humans WHERE id = hid AND married = x ;
END;

调用过程:

    mysql> set @suibian = 'N' ;
    mysql> call get_name( 1 , @suibian );
    mysql> select @suibian from dual ;

2.3、查看过程

• 列出所有"过程"

查询方式为:

    SELECT name FROM mysql.proc WHERE db = '数据库名' AND type = 'procedure' ;

其中:

mysql 为 数据库名称 ( 这个数据库由 MySQL 来维护 )

proc 为 mysql 数据库 中的表

name 、db 、 type 都是 proc 表中的列

比如查询 ycpower 数据库中的所有过程，可以使用:

   mysql> select name from mysql.proc where type='procedure' and db='ycpower' ;

• 查看过程的创建代码

    mysql> show create procedure 过程名称 ;

比如:

    mysql> show create procedure count_human_male ;

• 查看过程的状态

    mysql> show procedure status ;

2.4、删除过程

    mysql> drop procedure 过程名称 ; 

3、函数( function )

数据库中的函数( function ) ，也被某些人称作 存储函数( stored function )。