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)

巴特西

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

最新文章

热门文章