Mysql数据库（八）存储过程与存储函数

　　一、创建存储过程与存储函数

　　1.创建存储过程（实现统计tb_borrow1数据表中指定图书编号的图书的借阅次数）

mysql> delimiter //
mysql> CREATE PROCEDURE proc_count(IN id INT,OUT borrowcount INT)
    -> READS SQL DATA
    -> BEGIN
    -> SELECT count(*) INTO borrowcount FROM tb_borrow1 WHERE bookid=id;
    -> END
    -> //
Query OK, 0 rows affected (0.00 sec)

　　2.创建存储函数 （实现统计tb_borrow1数据表中指定图书编号的图书的借阅次数）

mysql> CREATE FUNCTION func_count(id INT)
    -> RETURNS INT(10)
    -> BEGIN
    -> RETURN(SELECT count(*) FROM tb_borrow1 WHERE bookid=id);
    -> END
    -> //
Query OK, 0 rows affected (0.00 sec)

　　3.变量的引用

　　（1）局部变量（以DECLARE声明，仅在BEGIN至END范围内有效）

mysql> CREATE PROCEDURE proc_local()
    -> BEGIN
    -> DECLARE x CHAR(10) DEFAULT '外层';
    -> BEGIN
    -> DECLARE x CHAR(10) DEFAULT '内层';
    -> SELECT x;
    -> END;
    -> SELECT x;
    -> END;
    -> //
Query OK, 0 rows affected (0.00 sec)

mysql> CALL proc_local()//
+--------+
| x      |
+--------+
| 内层   |
+--------+
1 row in set (0.00 sec)

+--------+
| x      |
+--------+
| 外层   |
+--------+
1 row in set (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

　　（2）全局变量（不用声明即可使用，在整个过程中有效，以“@”作为起始字符）

mysql> CREATE PROCEDURE proc_glocal()
    -> BEGIN
    -> SET @t='外层';
    -> BEGIN
    -> SET @t='内层';
    -> SELECT @t;
    -> END;
    -> SELECT @t;
    -> END;
    -> //
Query OK, 0 rows affected (0.00 sec)

mysql> CALL proc_glocal()//
+--------+
| @t     |
+--------+
| 内层   |
+--------+
1 row in set (0.00 sec)

+--------+
| @t     |
+--------+
| 内层   |
+--------+
1 row in set (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

　　（3）为变量赋值（分为使用SET关键字为变量赋值和使用SELECT...INTO语句为变量赋值）

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

 　　从tb_bookinfo表中查询barcode为17120107的记录，将该记录下的price字段内容赋值给变量book_price

SELECT price INTO book_price FROM tb_bookinfo WHERE barcode='17120107';

　　4.光标的运用

　　通过MySQL查询数据库，其结果可能为多条记录。在存储过程和函数使用光标可以实现逐条读取结果集中的记录。光标必须声明在处理程序之前，且声明在变量和条件之后。

　　（1）声明光标

mysql> DECLARE cursor_book CURSOR FOR SELECT barcode,bookname,price FROM tb_bookinfo WHERE typeid=4;

　　（2）打开光标

OPEN cursor_book;

　　（3）使用光标　　

FETCH cursor_book INTO tem_barcode,tem_bookname,tem_price;

　　（4）关闭光标

CLOSE cursor_book;

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

　　1.调用存储过程

mysql> select * from tb_borrow1;
+----+----------+--------+------------+------------+----------+--------+
| id | readerid | bookid | borrowTime | backTime   | operator | ifback |
+----+----------+--------+------------+------------+----------+--------+
|  1 |        4 |      7 | 2018-04-19 | 2018-04-20 | mr       |      1 |
|  2 |        4 |      7 | 2018-04-17 | 2018-04-18 | mr       |      0 |
|  3 |        2 |      6 | 2018-04-19 | 2018-04-21 | mr       |      0 |
+----+----------+--------+------------+------------+----------+--------+
3 rows in set (0.00 sec)

Query OK, 1 row affected (0.00 sec)

+--------------+
| @borrowcount |
+--------------+
|            2 |
+--------------+
1 row in set (0.00 sec)

　　2.调用存储函数

mysql> SET @bookid=7;
    -> CALL func_count(@bookid);
    -> //
Query OK, 0 rows affected (0.00 sec)

　　三、查看存储过程和函数

　　1.SHOW STATUS语句

mysql> SHOW FUNCTION STATUS LIKE 'func_count'\G
*************************** 1. row ***************************
                  Db: db_library
                Name: func_count
                Type: FUNCTION
             Definer: root@localhost
            Modified: 2018-04-19 09:01:09
             Created: 2018-04-19 09:01:09
       Security_type: DEFINER
             Comment:
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: utf8_general_ci
1 row in set (0.00 sec)

　　2.SHOW CREATE语句

mysql> SHOW CREATE PROCEDURE proc_count\G
*************************** 1. row ***************************
           Procedure: proc_count
            sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
    Create Procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `proc_count`(IN id INT,OUT borrowcount INT)
    READS SQL DATA
BEGIN
SELECT count(*) INTO borrowcount FROM tb_borrow1 WHERE bookid=id;
END
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: utf8_general_ci
1 row in set (0.00 sec)

　　四、修改存储过程和函数

mysql> ALTER PROCEDURE proc_count
    -> MODIFIES SQL DATA
    -> SQL SECURITY INVOKER;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW CREATE PROCEDURE proc_count\G
*************************** 1. row ***************************
           Procedure: proc_count
            sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
    Create Procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `proc_count`(IN id INT,OUT borrowcount INT)
    MODIFIES SQL DATA
    SQL SECURITY INVOKER
BEGIN
SELECT count(*) INTO borrowcount FROM tb_borrow1 WHERE bookid=id;
END
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: utf8_general_ci
1 row in set (0.00 sec)

　　五、删除存储过程和函数

mysql> DROP PROCEDURE proc_count;
Query OK, 0 rows affected (0.01 sec)

mysql> DROP FUNCTION func_count;
Query OK, 0 rows affected (0.00 sec)