mysql数据存储和函数_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)