存储过程(Stored Procedure)是 SQL 语句和控制语句的预编译集合,以一个名称存储并作为一个单元处理。
其效率比函数更高。增强 SQL 语句的功能和灵活性。减少网络流量。
客户端重启,存储过程不会失效。定界符只对客户端有效,客户端重启,定界符失效。
数据库系统有三层结构:客户端、DBMS、服务端。我们的各种请求都是通过客户端发送给 DBMS,DBMS 将 SQL 语句编译成二进制,然后执行,接着缓存在 DBMS 中(再次执行时效率提高),接着发送给数据库服务端,然后服务端再返回数据给 DBMS,DBMS 再返回给客户端显示。
而存储过程,是将某些常用的请求,在 DBMS 中编译好二进制存储在 DBMS 中,以后再发送同样请求,就免去了编译的开销。
模块化编程,可以提高速度。但是可移植性不好。
创建存储过程
创建之前必须先选择(USE)数据库。
CREATE
[DEFINER = {user|CURRENT_USER}]
PROCEDURE sp_name([proc_parameter[,...]])
[characteristic ...] routine_body
proc_parameter:
[IN|OUT|INOUT] param_name type
参数
- IN,表示该参数的值必须在调用存储过程时指定
- OUT,表示该参数的值可以被存储过程改变,并且可以被返回
- INOUT,表示该参数的调用时指定,并且可以被改变和返回
可以这样理解,IN 就是要传入的,值必须在调用时指定,不能返回(值传递),OUT 是返回的(指针传递)。INOUT 传入之后被返回的。
过程体
- 过程体由合法的 SQL 语句构成
- 过程体是绝大部分 SQL 语句
- 复合结构(超过两个 SQL 语句)使用 BEGIN…END
- 复合结构可以包含声明,循环,控制结构
调用存储过程
CALL sp_name[()]
如果存储过程没有参数,则调用的时候可以不加括号。
删除存储过程
用 DROP PROCDURE sp_name;
我们应该已经发现了,所有 CREATE 创建的东西,都需要用 DROP 来删除。包括:
数据库、表、视图、触发器、函数、存储过程
举例
delimiter //
CREATE PROCEDURE
removeuserbyid(IN u_id INT UNSIGNED)
begin
DELETE
FROM users
WHERE id = u_id;END//
-- 调用
CALL removeuserbyid(1)//
注意参数的名字不能和字段名相同。
CREATE PROCEDURE
remove2(IN p_id INT signed,
OUT usernums INT UNSIGNED)
begin
DELETE
FROM users
WHERE id=p_id;
SELECT count(id)
FROM users
INTO usernums;
end//
-- 调用
CALL remove2(2,@nums)//
-- 查看变量nums的值
SELECT @nums//
返回值存储在变量@nums 中。
修改存储过程
ALTER PROCEDURE sp_name[characteristic ...]
COMMENT 'string'
|{COUNTAINS SQL|NO SQL|READS SQL DATA|MODIFIES SQL DATA}
|SQL SECURITY{DEFINER|INVOKER}|
与函数对比
存储过程实现的功能更复杂一些;而函数的针对性更强。
时间经常用存储过程对表作操作。
存储过程可以用多个返回值。
存储过程独立出现。
函数出现在其他 SQL 语句内部。