存储过程与函数

逃离我推掉我的手 2023-06-25 06:13 42阅读 0赞

### 存储过程与函数 ###

*  1. 存储过程的定义
 *  2. 存储过程的创建
 *  3. 存储过程的操作
 *   *  3.1 存储过程的调用
     *  3.2 存储过程的查看
     *   *  3.2.1 SHOW PROCEDURE STATUS查看存储过程的状态
         *  3.2.2 SHOW CREATE PROCEDURE 查看存储过程的信息
         *  3.2.3 INFORMATION\_SCHEMA.ROUTINES查看存储过程的信息
     *  3.3 存储过程的删除
 *  4. 自定义函数
 *   *  4.1 自定义函数的创建
     *  4.2 自定义函数的调用
     *  4.3 变量
     *   *  4.3.1 定义变量
         *  4.3.2 变量赋值
         *   *  4.3.2.1 变量赋值(1)
             *  4.3.2.2 变量赋值(2)
     *  4.4 流程控制语句
     *   *  4.4.1 if语句
         *  4.4.2 CASE语句
         *  4.4.3 LOOP语句
         *  4.4.4 LEAVE语句
         *  4.4.5 ITERATE语句
         *  4.4.6 REPEAT语句
         *  4.4.7 WHILE语句
     *  4.5 光标的使用
     *   *  4.5.1 声明光标
         *  4.5.2 打开光标
         *  4.5.3 使用光标
         *  4.5.4 关闭光标
     *  4.6 定义条件和处理程序
     *   *  4.6.1 定义条件
         *  4.6.2 定义处理程序

# 1. 存储过程的定义 #

存储过程是一组完成特定功能的SQL语句集合  
**将常用或复杂的工作，预先用SQL语句写好并用一个指定名称存储起来，这个过程经编译和优化后存储在数据库服务器中，因此称为存储过程**

# 2. 存储过程的创建 #

**语法**

CREATE PROCEDURE sp_name([proc_parameter[,...]]) [characteristic] routine_body

*  CREATE PROCEDURE：为创建存储过程关键字
 *  sp\_name:存储过程名称，默认为存储过程与当前数据库关联。要明确的把子程序与一个给定数据库关联起来，可以在创建子程序的时候指定其名字为db\_name.sp\_name.
 *  proc\_parameter:指定存储过程的参数列表。列表形式 如下：其中，IN表示输入参数；type表示参数的类型，该类型可以是MySQL数据库任意类型。

[IN / OUT / INOUT] param_name type

*  characteristics指定存储过程的特性，有以下取值。
    
     *  LANGUAGE SQL: 说明routine\_body部分是SQL语句组成的，当前系统支持的语言为SQL，SQL是LANGUAGE特性的唯一值。
     *  \[NOT\] DETERMINISTIC: 指明存储过程执行的结果是否确定。DETERMINISTIC表示结果是确定的，相同的输入是相同的输出；\[NOT\] DETERMINISTIC表示结果是不确定的，相同的输入得到不同的输出。如果没有指定任意一个值，默认为\[NOT\] DETERMINISTIC。
     *  COUTAINS SQL / NO SQL / READS SQL DATA / MODIFIES SQL DATA: 知名子程序使用SQL语句的限制。
        
         *  COUTAINS SQL:表明子程序包含SQL语句，但是不包含读写数据的语句。
         *  NO SQL:表明子程序不包含SQL语句
         *  READS SQL DATA: 表明子程序包含写数据的语句。
         *  默认情况下，系统会指定为 CONTAINS SQL。
     *  SQL SECURITY \{DEFINER / INVOKER\}: 指明谁有权限来执行。DEFINER表示只有定义者才能执行；INVOKER表示拥有权限的调用者可以执行。默认情况下，系统指定为DEFINER。
     *  COMMENT ‘string’：注释信息，可以用来描述存储过程或函数。

*  routine\_body是SQL代码的内容，可以用BEGIN…END来表示SQL代码的开始和结束。

CREATE PROCEDURE pro_emp(in deptno int)
    BEGIN
    SELECT *
    FROM emp
    WHERE dno=deptno;
    END

> deptno:输入参数

CREATE PROCEDURE count_emp(out cc int)
    BEGIN
    SELECT COUNT(empno) into cc
    FROM emp
    WHERE empsex='女';
    END

> cc为输出类型，接收女雇员的人数。  
> select count(empno) into cc中的into语句实现了将count(empno)统计信息赋值给输出参数cc。SQL变量名不能和列名一样。如果select … into 语句中包含一个对列的引用，并包含一个与列相同名字的局部变量，MySQL当前把参考解释为一个变量的名字。

# 3. 存储过程的操作 #

## 3.1 存储过程的调用 ##

**语法**

CALL sp_name([parameter[,..]])

CALL调用存储过程，sp\_name是存储过程名称，parameter是存储过程的参数

**示例**

CREATE PROCEDURE avg_emp(IN deptno INT, OUT avgage FLOAT)
    BEGIN
    SELECT AVG(empage) INTO avgage
    FROM emp
    WHERE dno = deptno;
    END
    
    CALL avg_emp(1, @aa)
    
    SELECT @avg

## 3.2 存储过程的查看 ##

### 3.2.1 SHOW PROCEDURE STATUS查看存储过程的状态 ###

SHOW STATUS语句只能查看存储过程操作哪一个数据库，存储过程的名称，类型，谁定义的，创建和修改时间，字符编码等信息。 **不能查询存储过程具体定义**

**语法**

SHOW PROCEDURE STATUS [LIKE 'pattern']

> like表示匹配存储过程的名称

**示例**

SHOW PROCEDURE STATUS like 'a%'\G

> 查询所有以字母‘a’开头的存储过程信息

### 3.2.2 SHOW CREATE PROCEDURE 查看存储过程的信息 ###

查询存储过程具体定义

**语法**

SHOW CREATE PROCEDURE sp_name

> 返回一个可用来重新创建已命名存储过程的确切字符串

**示例**

SHOW CREATE PROCEDURE avg_emp

### 3.2.3 INFORMATION\_SCHEMA.ROUTINES查看存储过程的信息 ###

INFORMATION\_SCHEMA是信息数据库，其中保存着关于Mysql服务器所维护的所有其他数据库的信息。该数据库中的ROUTINES表提供存储过程的信息。通过查询该表可以查询相关存储过程的信息

**语法**

select * from information_schema.routines where routine_name='sp_name';

> routine\_name字段存储所有存储子程序的名称；sp\_name是需要查询的存储过程名称

**示例**

select * from information_schema.routines where routine_name='avg_emp';

修改存储过程的特性

ALTER {
       PROCEDURE / FUNCTION} sp_name [characteristic ...]

> sp\_name为待修改的存储过程名称；  
> characteristic来指定特性，可能取值如下：

{countains sql / no sql / reads sql data / modifies sql data / sql security { definer / invoker } / comment 'string'}

## 3.3 存储过程的删除 ##

**语法**

DROP PROCEDURE sp_name;

**示例**

DROP PROCEDURE avg_emp;
    select * from information_schema.routines where routine_name='avg_emp';

# 4. 自定义函数 #

## 4.1 自定义函数的创建 ##

**语法**

CREATE FUNCTION sp_name([func_parameter[, ...]])
    RETURNS type
    [characteristic ...] routine_body

*  CREATE FUNCTION创建函数的关键字
 *  sp\_name参数是自定义函数的名称
 *  func\_parameter表示自定义函数的参数列表。func\_parameter可以由多个参数组成，其中每个参数名称和参数类型组成，其形式如下。
    
     *  param\_name type
     *  其中：parma\_name是自定义函数的参数名称，type是参数类型，可以是任意数据类型
 *  returns type: 指的是返回值的类型
 *  characteristic：是自定义函数的特性，同存储过程一样。
 *  routine\_body: 是SQL代码的内容，可以用begin…end来标志SQL代码的开始和结束

**示例**

CREATE FUNCTION name_emp(eno INT)
    RETURNS VARCHAR(20)
    BEGIN
     RETURN(
      SELECT empname FROM emp WHERE empno=eno
     );
    END;

returns语句只能对function做指定，对函数而言是强制的。它用来指定函数的返回类型，而且函数体必须包含一个return value语句

## 4.2 自定义函数的调用 ##

自定义函数的调用和内部函数的调用方法一致，没有本质区别

select name_emp(1);

## 4.3 变量 ##

**存储过程和自定义函数中，都可以定义和使用变量**

变量的定义使用DECLARE关键字，定义后为变量赋值。作用域：begin…end程序段中

### 4.3.1 定义变量 ###

**语法**

DECLARE var_name[,...] TYPE [DEFAULT VALUE]

> default value 为变量默认值。可以是常数，也可以是表达式。如果没有默认值，初始值为NULL

**示例**

declare empdept char(10) default '财务部';

### 4.3.2 变量赋值 ###

#### 4.3.2.1 变量赋值(1) ####

**语法**

SET var_name = expr [, var_name = expr] ...

> set是赋值关键字；expr：赋值表达式

**示例**

declare var1, var2 int;
    declare var3 char(20);
    set var1=10, var2=20, var3='hello';

#### 4.3.2.2 变量赋值(2) ####

**语法**

select col_name [,...] into var_name [,...] from table_name where condition

> j将select选定的列值直接存储在对应位置的变量中；col\_name是查询的字段名称

**示例**

declare emp_name char(20);
    select empname into emp_name
    from emp
    where empno = 10;

## 4.4 流程控制语句 ##

### 4.4.1 if语句 ###

**语法**

if search_condition then statement_list
    [elseif search_condition then statement_list]...
    [else statement_list]
    end if

*  search\_condition：表示条件判断语句，如果该参数值为TRUE，执行相应的SQL语句，如果为假，执行ELSE子句中的语句。
 *  statement\_list: 表示不同条件的执行语句，可以包含一条或多条语句。

### 4.4.2 CASE语句 ###

也是条件判断，可以实现比if更复杂的条件判断

形式1

**语法**

case case_value
    when when_value then statement_list
    [when when_value then statement_list]...
    [else statement_list]
    end case

*  case\_value参数表示条件判断的表达式，决定哪个when子句被执行。
 *  when\_value 参数表示表达式可能的取值，
 *  如果某个when\_value和case\_value表达式的结果相同，则执行then关键字后面的语句

case deptno
    when 1 then select '电脑部';
    when 2 then select '财务部';
    when 3 then select '营销部';
    end case;

形式2

**语法**

case
    when search_condition then statement_list
    [when search_condition then statement_list]...
    [else statement_list]
    end case

*  search\_condition参数表示条件判断语句
 *  statement\_list参数表示不同条件的执行语句。
 *  when语句逐条执行

case
    when deptno = 1 then select '电脑部';
    when deptno = 2 then select '财务部';
    when deptno = 3 then select '营销部';
    end case;

### 4.4.3 LOOP语句 ###

LOOP语句可以重复执行特定语句，本身不进行条件判断，没有停止循环的语句，必须使用LEAVE语句才能停止循环，跳出循环过程  
**语法**

[begin_label:]LOOP
    statement_list
    END LOOP [end_label]

*  begin\_label和end\_label参数分别表示循环开始和结束的标志，两个标志必须相同，且都可以省略。
 *  statement\_list表示需要循环执行的语句

declare ss int default 0;
    add_sum: LOOP
    set ss=ss+1
    END LOOP add_sum;

### 4.4.4 LEAVE语句 ###

t跳出任何被标注的流程控制语句

**语法**

LEAVE label

*  label参数表示循环的标志。LEAVE和循环或BEGIN…END语句一起使用

**示例**

declare ss int default 0;
    add_sum:loop
    set ss=ss+1;
    if ss>100 then leave add_sum;
    end if;
    end loop add_sum;

### 4.4.5 ITERATE语句 ###

跳出本次循环，进入下一次循环

只可以出现在LOOP，repeat，和while语句中

**语法**

ITERATE label

*  label参数表示循环的标志。

**示例**

create procedure pp(a int)
    begin
    la: loop
    set a = a+1;
    if a < 10 then iterate la;
    end if;
    leave la;
    end loop la;
    set @x = a;
    end

### 4.4.6 REPEAT语句 ###

带条件判断的循环过程。

循环语句执行完对表达式进行判断：为真，结束循环；为假，继续重复循环

**语法**

[begin_label:] REPEAT
    statement_list
    UNTIL search_condition
    END REPEAT [end_label]

*  begin\_label和end\_label为开始标记和结束标记，两者均可省略；
 *  statement\_list:循环的执行语句
 *  search\_condition:表示结束循环的条件，为真，跳出循环，为假，重复循环

**示例**

declare aa int default 0;
    repeat
    set aa = aa + 1;
    until aa >= 20;
    end repeat;

### 4.4.7 WHILE语句 ###

带条件判断的循环过程。

首先对表达式进行判断：为真，执行循环内语句；为假，退出循环过程

**语法**

[begin_label:] WHILE search_condition DO
    statement_list
    END WHILE [end_label]

*  begin\_label和end\_label为开始标记和结束标记，两者均可省略；
 *  statement\_list:循环的执行语句
 *  search\_condition:表示条件判断语句，为真，执行循环内语句，为假，退出循环

**示例**

declare aa int default 0;
    while aa <= 20 do
    set aa = aa + 1;
    end while;

## 4.5 光标的使用 ##

光标又叫游标

MySQL中的光标只能在存储过程和自定义函数中使用

作用：存储过程或自定义函数中查询可能返回多条记录，光标可以逐条读取查询结果集中的记录

> 光标必须在处理程序之前声明，在变量和条件之后声明。

**光标使用步骤**

1.  声明光标
2.  打开光标
3.  使用光标
4.  关闭光标

### 4.5.1 声明光标 ###

**语法**

DECLARE cursor_name CURSOR FOR select_statement

*  cursor\_name:光标的名称
 *  select\_statement:查询语句，返回一个结果集，声明的光标基于该结果集进行操作。

> 可以在子程序中定义多个光标，但是一个块中的每一个光标必须有唯一的名称。

**示例**

declare cursor_emp cursor for select empno, empname from emp

### 4.5.2 打开光标 ###

**语法**

OPEN cursor_name

*  cursor\_name:先前声明的光标

**示例**

open cursor_emp;

### 4.5.3 使用光标 ###

**语法**

FETCH cursor_name INTO var_name [, var_name]...

*  cursor\_name:先前声明并打开的光标
 *  var\_name: 表示将光标声明中的select语句中的查询信息存储在该参数中；必须在光标声明前定义好。

**示例**

fetch cursor_emp into e_no, e_name;

**表示将select语句查询得到的empno, empname存储在变量e\_no,e\_name中**

### 4.5.4 关闭光标 ###

**语法**

CLOSE cursor_name

*  cursor\_name:先前声明并打开的光标
 *  如果未被明确的关闭，光标在它被声明的复合语句的末尾被关闭。

**示例**

close cursor_emp;

## 4.6 定义条件和处理程序 ##

程序运行过程可能出现问题，用定义条件和处理程序事先定义该问题，保证存储过程和自定义函数在遇到警告或错误时能够继续进行

### 4.6.1 定义条件 ###

**语法**

DECLARE condition_name CONDITION FOR condition_value
    
    其中：
    condition_value:
    SQLSTATE[VALUE] sqlstate_value / mysql_error_code

*  condition\_name：条件名称
 *  condition\_value: 为条件类型
 *  sqlstate\_value和mysql\_error\_code都可以表示MySQL的错误
 *  sqlstate\_value：长度为5的字符串类型的错误代码
 *  mysql\_error\_code: 为数值类型错误代码

方法1：使用sqlstate\_value

**示例**

declare command_not_find condition for sqlstate '43000';

方法2：使用mysql\_error\_code

**示例**

declare command_not_find condition for 1120;

### 4.6.2 定义处理程序 ###

**语法**

DECLARE handler_type HANDLER FOR condition_value[,...] sp_statement

*  handler\_type表示 CONTINUE / EXIT / UNDO 语句中的handler\_type为错误处理的方式
    
     *  continue：表示遇到错误不处理，继续执行。
     *  exit: 表示遇到错误马上退出。
     *  undo: 表示遇到错误后撤销之前的操作。
 *  condition\_value:可能有以下取值

SQLSTATE[VALUE] sqlstate_value
    / condition_name
    / SQLWARNING
    / NOT FOUND
    / SQLEXCEPTION
    / mysql_error_code

condition\_value表示错误的类型，有以下取值

*  SQLSTATE\[VALUE\]sqlstate\_value: 字符串错误值。
 *  condition\_name: 使用declare dondition 定义的错误条件名称。
 *  SQLWARNING: 匹配所有01开头的SQLSTATE错误代码。
 *  NOT FOUND: 匹配所有02开头的SQLSTATE错误代码。
 *  SQLEXCEPTION: 匹配所有没有被SQLWARNING或NOT FOUND捕获的SQLSTATEC错误代码。

**示例1**

DECLARE CONTINUE HANDLER FOR SQLSTATE'23S00'
    SET @X=20;

**该方法是定义捕获sqlstate\_value值。如果遇到sqlstate\_value值为23S00,执行CONTINUE操作，并且给变量x赋值为2**

**示例2**

DECLARE CONTINUE HANDLER FOR 1146 SET @X=20;

**该方法是定义捕获mysql\_error\_code值。如果mysql\_error\_code值为1146,执行CONTINUE操作，并且给变量x赋值为2**

**示例3**

declare no_table condition for 1150;
    DECLARE CONTINUE HANDLER FOR no_table
    set @info=`no_table`;

**先定义no\_table条件，遇到1150错误就执行CONTINUE操作，并输出NO\_TABLE信息**

**示例4**

DECLARE EXIT HANDLER FOR SQLWARNING SET @info='ERROR';

**SQLWARNING捕获所有以01开头的sqlstate\_value值，然后执行EXIT操作，并且输出‘ERROR’信息**