数据库之——存储过程（学习笔记）

灰太狼 2022-05-19 19:55 317阅读 0赞

### **一、存储过程和函数的引入** ###

存储过程和函数时在数据库中定义一些SQL语句的集合，然后直接调用这些存储过程和函数来执行已经定义好的SQL语句。存储过程和函数可以避免开发人员重复的编写相同的SQL语句。而且，存储过程和函数是在MySQL服务器中存储和执行的，可以减少客户端和服务器段的数据传输；

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

**2.1 创建存储过程**

<table> 
 <tbody> 
 <tr> 
 <td> CREATE PROCEDURE sp_name([proc_parameter[,...]]) &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; [characteristic...]routine_body sp_name 参数是存储过程的名称； proc_parameter 表示存储过程的参数列表； characteristic 参数指定存储过程的特性； routine_body 参数是 SQL 代码的内容，可以用 BEGIN...END 来标志 SQL 代码的开始和结束。 proc_parameter 中的每个参数由 3 部分组成。这 3 部分分别是输入输出类型、参数名称和参数类型。 [IN |OUT|INOUT]param_nametype 其中，IN 表示输入参数；OUT 表示输出参数；INOUT 表示既可以是输入，也可以是输出；param_name 参数是 存储过程的参数名称；type 参数指定存储过程的参数类型，该类型可以是 MySQL 数据库的任意数据类型； Characteristic 参数有多个取值。其取值说明如下： LANGUAGESQL：说明 routine_body 部分是由 SQL 语言的语句组成，这也是数据库系统默认的语言。 [NOT]DETERMINISTIC ：指明存储过程的执行结果是否是确定的。DETERMINISTIC 表示结果是确定的。每 次执行存储过程时，相同的输入会得到相同的输出。NOTDETERMINISTIC 表示结果是非确定的，相同的输入 可能得到不同的输出。默认情况下，结果是非确定的。 {CONTAINS SQL|NO SQL|READSSQLDATA|MODIFIES SQLDATA} ：指明子程序使用 SQL 语句的限制； CONTAINS SQL 表示子程序包含 SQL 语句，但不包含读或写数据的语句；NO SQL 表示子程序中不包含 SQL 语句；READSSQLDATA 表示子程序中包含读数据的语句；MODIFIES SQLDATA表示子程序中包含写数据的 语句。默认情况下，系统会指定为 CONTAINSSQL； SQLSECURITY{ DEFINER |INVOKER }；指明谁有权限来执行。DEFINER 表示只有定义者自己才能够执行； INVOKER 表示调用者可以执行。默认情况下，系统指定的权限是 DEFINER。 COMMENT‘string’ ：注释信息； </td> 
 </tr> 
 </tbody> 
</table>

例：

//创建存储过程
    DELIMITER &&
    CREATE PROCEDURE pro_book (IN bT INT,OUT count_num INT)
        READS SQL DATA
        BEGIN
            SELECT COUNT(*) FROM t_book WHERE bookTypeId = bT;
        END
    &&
    DELIMITER;
    
    //调用存储过程
    CALL pro_book(1,@total)

![70][]

**2.2创建存储函数**

<table> 
 <tbody> 
 <tr> 
 <td> CREATE FUNCTION sp_name([func_parameter[,...]]) &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; RETURNS type &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; [characteristic...]routine_body &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; sp_name 参数是存储函数的名称；func_parameter 表示存储函数的参数列表；RETURNStype 指定返回值的 类型；characteristic 参数指定存储过程的特性，该参数的取值与存储过程中的取值是一样的；routine_body 参数 是 SQL 代码的内容，可以用 BEGIN...END 来标志 SQL 代码的开始和结束； &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; func_parameter 可以由多个参数组成，其中每个参数由参数名称和参数类型组成，其形式如下： &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; param_name type 其中，param_name 参数是存储函数的参数名称；type 参数指定存储函数的参数类型， 该类型可以是 MySQL 数据库的任意数据类型； </td> 
 </tr> 
 </tbody> 
</table>

//创建存储函数
    DELIMITER &&
    CREATE FUNCTION func_book(bookId INT)
        RETURNS VARCHAR(20)
        BEGIN
           RETURN(SELECT bookName FROM t_book WHERE id=bookId);
        END
    &&
    DELIMITER;
    
    //调用存储函数
    SELECT func_book(2)

![70 1][]

**2.3 变量的使用**

1，定义变量

>         DECLARE var\_name \[,...\] type \[DEFAULT value\].

//创建存储过程
    DELIMITER &&
    CREATE PROCEDURE pro_user()
        BEGIN
            DECLARE a,b VARCHAR(20);//定义变量
            INSERT INTO t_user VALUES(NULL,a,b);
        END
    &&
    DELIMITER;
    
    //调用存储过程
    CALL pro_user();

![70 2][]

2，为变量赋值

>             SET var\_name = expr\[,var\_name=expr\]...

>                  SELECT col\_name\[,..\] INTO var\_name\[,...\]
> 
>                               FROM table name WHERE condition

//创建存储过程
    DELIMITER &&
    CREATE PROCEDURE pro_user2()
           BEGIN
              DECLARE a,b VARCHAR(20);//定义变量
              //SET a='java1234',b='123456';//方式一：直接为变量赋值
              SELECT userName2,password2 INTO a,b FROM t_user2 WHERE id2=1;//方式二：从其它表中查询给a,b赋值进去
              INSERT INTO t_user VALUES(NULL,a,b);
           END
    &&
    DELIMITER;
    
    //调用存储过程
    CALL pro_user2;

![70 3][]

**2.4 游标的使用**

查询语句可能查询出多条记录，在存储过程和函数中使用游标来逐条读取查询结果中的记录。游标的使用包括声明游标、打开游标、使用游标和关闭游标。游标必须声明在处理程序之前，并且声明在变量和条件之后。

1，声明游标

> DECLARE cursor\_name CURSOR FOR select\_statement;

2，打开游标

> OPEN cursor\_name;

3，使用游标

> FETCH cursor\_name INTO var\_name\[,var\_name...\];

4，关闭游标

> CLOSE cursor\_name

DELIMITER &&
    CREATE PROCEDURE pro_user4()
        BEGIN
            DECLARE a,b VARCHAR(20);//声明变量
            DECLARE cur_t_user2 CURSOR FOR SELECT userName2,password2 FROM t_user2;//声明游标
            OPEN cur_t_user2;//打开游标
            FETCH cur_t_user2 INTO a,b;//使用游标
            INSERT INTO t_user VALUES(NULL,a,b);
            CLOSE cur_t_user2;//关闭游标
        END
    &&
    DELIMITER;

**2.5 流程控制的使用**

存储过程和函数中可以使用流程控制来控制语句执行。MySQL中可以使用IF语句、CASE语句、LOOP语句、LEAVE语句、ITERATE语句、REPEAT语句和WHILE语句来进行流程控制。

1，IF语句

>       IF search\_condition THEN statement\_list
> 
>            \[ELSEIF search\_condition THEN statement\_list\]...
> 
>            \[ELSE statement\_list\]
> 
>       END IF

//创建存储过程
    DELIMITER &&
    CREATE PROCEDURE pro_user5(IN bookId INT)
        BEGIN
            SELECT COUNT(*) INTO @num FROM t_user2 WHERE id = bookId;//将查询到的数据放到全局变量中@num代表全局变量
            IF @num>0 THEN UPDATE t_user SET userName='java12345' WHERE id = bookId;//有数据，那么就更新一条数据
            ELSE
                INSERT INTO t_user VALUES(NULL,'23123321','23123321');//没有数据就插入一条数据
            END IF;
        END
    &&
    DELIMITER;
    //调用存储过程
    CALL pro_user5(5);//有id=5的就更新数据，没有数据iu插入一条新数据

2，CASE语句

> CASE case\_value
> 
>      WHEN when\_value THEN statement\_list
> 
>     \[WHEN when\_value THEN statement\_list\]...
> 
>     \[ELSE statement\_list\]
> 
> END CASE

//创建存储过程
    DELIMITER &&
    CREATE PROCEDURE pro_user6(IN bookId INT)
        BEGIN
            SELECT COUNT(*) INTO @num FROM t_user2 WHERE id = bookId;
            CASE @num
                WHEN 1 THEN UPDATE t_user SET userName='java12345' WHERE id=bookId;
                WHEN 2 THEN INSERT INTO t_user VALUES(NULL,'213123','123123');
                ELSE INSERT INTO t_user VALUES(NULL,'213123123','213123')；
        END
    &&
    DELIMITER;
    //调用存储过程
    CALL pro_user6(5);//有id=5的就更新数据，没有数据iu插入一条新数据

3，LOOP，LEAVE语句

> LOOP语句可以使某些特定的语句重复执行，实现一个简单的循环。但是LOOP语句本身没有停止循环的语句，必须是遇到LEAVE语句等才能停止循环。LOOP语句的语法的基本形式如下
> 
> \[begin\_label:\]LOOP
> 
>             Statement\_list
> 
> END LOOP\[end\_label\]
> 
>  
> 
> LEAVE语句主要用于跳出循环控制。语法形式如下：
> 
> LEAVE label

//创建存储过程
    DELIMITER &&
    CREATE PROCEDURE pro_user7(IN totalNum INT)
        BEGIN
            aaa:LOOP
                SET totalNum = bobalNum-1;
                IF totalNum=0 THEN LEAVE aaa;
                ELSE INSERT INTO t_user VALUES(totalNum,'213123','123123');
                END IF;
            END LOOP aaa;
        END
    &&
    DELIMITER;
    //调用存储过程
    CALL pro_user7;

4，ITERATOR语句

> ITERATE语句也是用来跳出循环的语句。但是，ITERATE语句是跳出本次循环，然后直接进入下一次循环。基本语法：
> 
> ITERATE label;

DELIMITER &&
    //创建存储过程
    CREATE PROCEDURE pro_user8(IN totalNum INT)
        BEGIN
            aaa:LOOP
                SET totalNum = totalNum-1;
                IF totalNum=0 THEN LEAVE aaa;
                ELSEIF totalNum=3 THEN ITERATE aaa;
                END IF;
                INSERT INTO t_user VALUES(totalNum,'2131244','12312455');
            END LOOP aaa;
        END
    &&
    DELIMITER；
    //调用存储过程
    CALL pro_user8;

5，REPEAT语句

> REPEAT语句是有条件控制的循环语句。当满足特定条件时，就会跳出循环语句。REPEAT语句的基本语法形式如下：
> 
> \[begin\_label:\]REPEAT
> 
>    Statement\_list
> 
>    UNTIL search\_condition
> 
> END REPEAT\[end\_label\]

DELIMITER &&
    CREATE PROCEDURE pro_user9(IN totalNum INT)
        BEGIN
            REPEAT
                SET total=totalNum-1
                INSERT INTO t_user VALUES(totalNum,'21323','123213');
                UNTIL totalNum=1;
            END REPEAT;
        ENG
    &&
    DELIMITER;
    
    
    CALL pro_user9(11)

>   6，WHILE语句(经典)
> 
> \[begin\_label:\]WHILE search\_condition DO
> 
>       Statement\_list
> 
> END WHILE\[end\_label\]

DELIMITER &&
    CREATE PROCEDURE pro_user10(IN totalNum INT)
        BEGIN
            WHILE totalNum>0 DO
                INSERT INTO t_user VALUES(totalNum,'122315','3142321');
                SET totalNum = totalNum-1;
            END WHILE;
        END
    &&
    DELIMITER；
    
    
    CALL pro_user10(11);

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

3.1  调用存储过程

> CALL sp\_name(\[parameter\[,,,\]\])

3.2  调用存储函数

> fun\_name(\[parameter\[,...\]\])

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

4.1  SHOW STATUS 语句查看存储过程和函数的状态

> SHOW \{PROCEDURE | FUNCTION\} STATUS\[LIKE 'pattern'\];

4.2  SHOW CREATE 语句查看存储过程的函数的定义

> SHOW CREATE \{PROCEDURE|FUNCTION\} sp\_name

4.3  从information\_schema.Routines表中查看存储过程和函数的信息

### 五、修改存储过程和函数 ###

> ALTER\{PROCEDURE|FUNCTION\}sp\_name\[characteristic...\]
> 
> characteristic:
> 
> \{CONTAINSSQL\}NOSQL|READSSQLDATA|MODIFIESSQLDATA\}
> 
> |SQLSECURITY\{DEFINER |INVOKER \}
> 
> |COMMENT‘string’
> 
> 其中，sp\_name 参数表示存储过程或函数的名称；characteristic 参数指定函数的特性。CONTAINS SQL 表示子程 序包含 SQL 语句，但不包含读或写数据的语句；NO SQL 表示子程序中不包含 SQL 语句；READS SQLDATA 表 示 子 程 序 中 包 含 数 据 的 语 句 ；MODIFIES SQL DATA 表 示 子 程 序 中 包 含 写 数 据 的 语 句 。SQL SECURITY\{ DEFINER | INVODER \} 指明谁有权限来执行。DEFINER 表示只有定义者自己才能够执行； INVODER 表示调用者可以执行。COMMENT‘string’ 是注释信息。

### 六、删除存储过程 ###

> DROP \{PROCEDURE|FUNCTION\} sp\_name;

[70]: /images/20220520/130833dcfa614c17bedfa00341095bbf.png
[70 1]: /images/20220520/78f7d436891548859d5bb9aa4b10b661.png
[70 2]: /images/20220520/02b207e7aacf491aa5f9c217e368579c.png
[70 3]: /images/20220520/368ee5114f03427aa22254cf0d69b2fd.png