存储过程

向右看齐 2022-05-23 05:07 115阅读 0赞

*  一、创建存储过程和函数
    
     *  1.创建存储过程
     *  2.创建存储函数
     *  3.变量
        
         *  I 局部变量
         *  II 用户变量
         *  III 会话变量
     *  光标
     *  5.流程控制
 *  二、 调用存储过程和函数
 *  三、查看存储过程和函数
 *  四、删除存储过程和函数

# 一、创建存储过程和函数 #

存储过程可以分为存储过程和存储函数，MySQL中创建存储过程和函数使用的语句分别是：CREATE PROCEDURE和CREATE FUNCTION。使用CALL语句来调用存储过程，只能用输出变量返回值，函数可以从语句外调用（既通过引用函数名），亚俄能返回标量值，存储过程可以调用其他存储过程。

## 1.创建存储过程 ##

*  创建存储过程，使用CREATE PROCEDURE语句，基本语法格式如下：

create procedure sp_name([proc_parameter]) BEGIN routine_body END

*  proc\_parameter:为指定存储过程的列表参数，列表的形式为：`[IN|OUT|INOUT] param_name type`
 *  如果需要为输出参数赋值使用INTO关键字，·seletct column into para where condition;·

## 2.创建存储函数 ##

*  创建存储函数，需要使用CREATE FUNCTION 语句，基本语句格式如下：

create function f_name([proc_parameter])
    returns type
    routine body

*  **注意**：指定参数为IN、OUT 或INOUT只对PROCEDURE是合法的，FUNCTION种总是默认IN参数。RETURNS子句只对FUNCTION做指定，对函数而言这是强制的，它用来指定函数的返回值，而且函数体必须包含一个RETURN value语句。

## 3.变量 ##

### I 局部变量 ###

*  局部变量一般用在sql语句块中，比如存储过程的begin/end。其作用域仅限于该语句块，在该语句块执行完毕后，局部变量就消失了。
 *  定义变量：在存储过程中使用DECLARE语句定义变量，语法格式为`declare var_name,... data_type [default value];`
 *  变量赋值：MySQL使用SET语句为变量赋值，语法格式为`set var_name = expr,...;`  
    
    
     *  MySQL中还可以通过SELECT…INTO为一个或多个变量赋值，语法格式为`select col_name,... into var_name,... table_expr;`

### II 用户变量 ###

*  用户变量的作用域要比局部变量要广。用户变量可以作用于当前整个连接，但是当当前连接断开后，其所定义的用户变量都会消失。
 *  用户变量使用如下(这里我们无须使用declare关键字进行定义，可以直接这样使用)：`select @变量名`
 *  对用户变量赋值有两种方式，一种是直接用”=”号，另一种是用”:=”号。其区别在于使用set命令对用户变量进行赋值时，两种方式都可以使用；当使用select语句对用户变量进行赋值时，只能使用”:=”方式，因为在select语句中，”=”号被看作是比较操作符。

### III 会话变量 ###

*  服务器为每个连接的客户端维护一系列会话变量。在客户端连接时，使用相应全局变量的当前值对客户端的会话变量进行初始化。设置会话变量不需要特殊权限，但客户端只能更改自己的会话变量，而不能更改其它客户端的会话变量。会话变量的作用域与用户变量一样，仅限于当前连接。当当前连接断开后，其设置的所有会话变量均失效。
 *  设置会话变量有如下三种方式：  
    
    
     *  set session var\_name = value;
     *  set @@session.var\_name = value;
     *  set var\_name = value;
 *  查看一个会话变量也有如下方式  
    
    
     *  select @@var\_name;
     *  select @@session.var\_name;

## 光标 ##

查询语句可能返回多条记录，如果数据量非常大，需要在存储过程中和存储函数中使用光标来逐条读取查询结果集中的记录，应用程序可以根据需要滚动或浏览其中的数据，需要注意的是，光标必须在声明处理程序之前被声明，并且变量和条件还必须在声明光标或处理程序之前被声明。

*  声明光标：MySQL中使用DECLARE关键字来声明光标，其语法格式为`declare cursor_name cursor for select_statement;`
 *  打开光标：`open cursor_name;`
 *  使用光标：`fetch cuesor_name into val_name [,...];`
 *  关闭光标：`close cursor_name;`
 *  **注意：** MySQL中光标只能在存储过程和函数中使用

## 5.流程控制 ##

*  IF语句

if expr_condition then
        statement_list;
    elseif expr_condition then
        statement_list;
    else
        statement_list;
    end if;

*  CASE语句

case 
        when expr_condition then
            statement_list;
        ...
        else
            statement_list;
    end case;

*  LOOP语句

[loop_label:] loop
        statement_list;
    end loop [loop_label];

*  LEVAE语句：类似break用于跳出循环

leave label；

*  ITERATE语句，类似jump，跳转到语句开头

iterate label;

*  REPEAT语句：类似do while

[repeat_label:] repeat
        statement_list;
    until expr_condition
    end repeat [repeat_lable];

*  WHILE语句：

[while_label:] while expr_condition do
        statement_list;
    end while [while_lable];

# 二、 调用存储过程和函数 #

存储过程必须使用CALL语句调用，并且存储过程和数据库相关，如果要执行其他数据库中的存储过程，需要指定数据库名称，例如CALL dbname.procname。存储函数的调用与MySQL中预定义的函数的调用方式相同。

# 三、查看存储过程和函数 #

*  使用SHOW STATUS语句查看存储过程和函数的状态：`show {procedure|function} status [like 'pattern'];`
 *  使用SHOW CREATE语句查看存储过程和函数的定义：`show create {procedure|function} sp_name;`
 *  从information\_schema.Routines表中查看存储过程和函数的信息。

# 四、删除存储过程和函数 #

*  删除使用DROP语句：`drop {procedure|function} [if exists] sp_name;`