存储过程是什么
-
是一组为了完成特定功能的SQL 语句集合
-
经编译后保存在数据库中
-
通过指定存储过程的名字并给出参数的值
-
MySQL5.0版本开始支持存储过程,使数据库引擎更加灵活和强大
-
可带参数,也可返回结果
-
可包含数据操纵语句、变量、逻辑控制语句等
优点:
-
减少网络流量
-
提升执行速度
-
减少数据库连接次数
-
安全性高
-
复用性高
缺点:
可移植性差
批量执行SQL操作的方法
DELIMITER指令修改SQL语句结束符
mysql> SELECT USER(); #;结束SQL语句
+----------------+
| USER() |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.01 sec)
mysql> DELIMITER // #定义//为SQL语句结束符
mysql> SELECT USER()// #测试
+----------------+
| USER() |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)
mysql> DELIMITER ; #定义;为SQL语句结束符
mysql> SELECT USER(); #测试
+----------------+
| USER() |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)
创建存储过程
语法:
-
CREATE PROCEDURE 过程名 ([过程参数[,…]])
-
[特性] #可选项,用于设置存储过程的行为
-
存储过程体
mysql> USE tarena; #切换到tarena库
mysql> DELIMITER // #声明//为定界符,方便定义存储过程
mysql> CREATE PROCEDURE stat() #定义存储过程
-> BEGIN
-> SELECT COUNT(*) FROM tarena.departments;
-> SELECT COUNT(*) FROM tarena.employees;
-> END
-> //
mysql> DELIMITER ; #声明;为定界符,方便后续操作
调用存储过程
语法:
CALL 存储过程名([参数1,参数2, …]);
mysql> CALL stat(); #CALL关键字(打电话通知)
mysql> CALL tarena.stat(); #接收到了,调用
通过DEFINER和SQL SECURITY特性控制存储过程的执行权限
语法:
CREATE
[DEFINER = { user | CURRENT_USER }] #定义DEFINER,默认为当前用户
PROCEDURE 存储过程名
[SQL SECURITY { DEFINER | INVOKER } | …]#指定DEFINER或INVOKER权限
BEGIN
…
END
查看存储过程
SHOW CREATE PROCEDURE 存储过程名;
mysql> SHOW PROCEDURE STATUS\G #查看所有存储过程
mysql> DESC mysql.proc\G #用于存储存储过程的表
mysql> SELECT * FROM mysql.proc WHERE db='tarena' AND name='stat';
mysql> SELECT db,name,body FROM mysql.proc WHERE db='tarena' AND name='stat';
删除存储过程
mysql> DROP PROCEDURE stat; #删除存储过程