一、创建存储过程与存储函数
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 |
+--------+
| 外层 |
&#