一、创建存储过程与存储函数
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_libraryName: func_countType: FUNCTIONDefiner: root@localhostModified: 2018-04-19 09:01:09Created: 2018-04-19 09:01:09Security_type: DEFINERComment:
character_set_client: utf8
collation_connection: utf8_general_ciDatabase 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_countsql_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_SUBSTITUTIONCreate 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_ciDatabase 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_countsql_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_SUBSTITUTIONCreate Procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `proc_count`(IN id INT,OUT borrowcount INT)MODIFIES SQL DATASQL SECURITY INVOKER
BEGIN
SELECT count(*) INTO borrowcount FROM tb_borrow1 WHERE bookid=id;
END
character_set_client: utf8
collation_connection: utf8_general_ciDatabase 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)