存储过程(Stored Procedure)是一组为了完成特定功能的SQL语句集合,可以包含逻辑控制语句和数据操纵语句,并且可以返回结果集。其经编译后存储在数据库中。用户可以通过指定存储过程的名称并传递参数(如果需要)来执行它。下面给出MySQL与SQL Server的存储过程的语法对比。
MySQL
语法:
create procedure 存储过程名称([ [IN|OUT|INOUT] 参数名称 参数的数据类型 , ...])
begin
...
end;
#存储过程的执行或调用:
CALL Procedure_name([参数1,[参数2]...]);
- 存储过程的参数名称不需要带@前缀,参数的赋值与使用同DECLARE声明的局部变量一致。
- IN 输入参数:表示该参数的值必须在调用存储过程时指定,在存储过程中修改该参数的值不能返回到外部
- OUT 输出参数:该值可在存储过程内部被改变,并可返回,但其初始值为NULL,读取不了外部的值
- INOUT 输入输出参数:调用时指定,在存储过程内部可被改变并返回到外部,且其初始值为外部的值
- 注意:OUT与INOUT参数在调用时必须以变量的形式赋值,而不能用常量
示例:
1、IN参数的例子:
DROP PROCEDURE IF EXISTS test_proc;
CREATE PROCEDURE test_proc(in p_in int)
BEGIN
SELECT p_in;
SET p_in = 2;
SELECT p_in;
END;
set @a = 1;
CALL test_proc(@a);
SELECT @a;
-- 结果:
+------+
| p_in |
+------+
| 1 |
+------+
+------+
| p_in |
+------+
| 2 |
+------+
+------+
| @a |
+------+
| 1 |
+------+
2、OUT参数的例子:
DROP PROCEDURE IF EXISTS test_proc;
CREATE PROCEDURE test_proc(out p_in int)
BEGIN
SELECT p_in;
SELECT 2 into p_in;
SELECT p_in;
END;
set @a = 1;
CALL test_proc(@a);
SELECT @a;
-- 结果:
+------+
| p_in |
+------+
| null |
+------+
+------+
| p_in |
+------+
| 2 |
+------+
+------+
| @a |
+------+
| 2 |
+------+
3、INOUT参数的例子:
DROP PROCEDURE IF EXISTS test_proc;
CREATE PROCEDURE test_proc(inout p_in int)
BEGIN
SELECT p_in;
SELECT 2 into p_in;
SELECT p_in;
END;
set @a = 1;
CALL test_proc(@a);
SELECT @a;
-- 结果:
+------+
| p_in |
+------+
| 1 |
+------+
+------+
| p_in |
+------+
| 2 |
+------+
+------+
| @a |
+------+
| 2 |
+------+
存储过程的查看与删除:
#存储过程的查看:
#查看所有存储过程的状态:
SHOW PROCEDURE STATUS [ like ‘pattern’] ;
#查看指定存储过程的定义:
SHOW CREATE PROCEDURE proc_name ;|\G
#从系统表里查看存储过程的信息:
SELECT * FROM information_schema.Routines Where ROUTINE_NAME = ‘proc_name’;
-------------------------------------------------------------------
#存储过程的删除:
DROP PROCEDURE [IF EXISTS] proc_name;
SQL Server
语法:
create proc | procedure pro_name
[{@参数 数据类型} [=默认值] [output],
{@参数 数据类型} [=默认值] [output],
....
]
as
begin
...
end;
#存储过程的执行或调用:
DELCARE @other_param int
SET @other_param = 333
EXEC Procedure_name;
EXECUTE Procedure_name 1,2; --为参数赋值时,可以不带参数名
EXECUTE Procedure_name @param1=1,@other_param output; --但也可以带上参数名,与Python函数的参数赋值类似
EXECUTE Procedure_name @param1=1,@param2=@other_param output; --输出型参数,若需要输出,则在调用时必须带output关键字
- 注意:OUTPUT参数在调用时必须以变量的形式赋值,而不能用常量
- 存储过程的参数如果不带output则是输入型参数,只能接受而不能输出,类似MySQL里的in类型
- 存储过程的参数如果带了output则是输出型参数,既可以接受也可以输出,类似MySQL的inout类型
- 参数列表中的output关键字表示对应的参数可以被输出,但前提是调用存储过程且为该参数赋值时有带上output关键字,如果赋值时没带output关键字,那即使参数列表有带output关键字,该参数也会被当做输入型参数。
- 即参数列表里的output关键字表示该参数既可当作输入型参数,也可当作输出型参数,具体取决于调用与赋值时有没有带上output关键字。但如果参数列表里不带output关键字,那调用赋值时就不能带output关键字,即该参数只能当作输入性参数。
示例:
# 不带参数的存储过程
-- 创建存储过程
if (exists (select * from sys.objects where name = 'proc_get_student'))
drop proc proc_get_student
go
create proc proc_get_student
as
select * from student;
-- 调用、执行存储过程
exec proc_get_student;
---------------------------------------------------------------------------
# 带输入型参数的存储过程
-- 创建存储过程
IF OBJECT_ID (N'PROC_ORDER_COUNT', N'P') IS NOT NULL
DROP procedure PROC_ORDER_COUNT;
GO
CREATE procedure PROC_ORDER_COUNT
@city nvarchar(50)
AS
SELECT COUNT(OrderID) FROM Orders WHERE City=@city
-- 调用、执行存储过程
EXEC PROC_ORDER_COUNT N'GuangZhou';
EXEC PROC_ORDER_COUNT @city=N'GuangZhou';
---------------------------------------------------------------------------
# 带输出型参数的存储过程
-- 创建存储过程
IF OBJECT_ID (N'PROC_ORDER_COUNT', N'P') IS NOT NULL
DROP procedure PROC_ORDER_COUNT;
GO
CREATE procedure PROC_ORDER_COUNT
@city nvarchar(50),
@count_num int output
AS
SELECT @count_num = COUNT(OrderID) FROM Orders WHERE City=@city
-- 调用、执行存储过程
DECLARE @count int
EXEC PROC_ORDER_COUNT N'GuangZhou', @count output;
EXEC PROC_ORDER_COUNT N'GuangZhou', @count_num=@count output;
select @count;
存储过程的查看与删除:
#存储过程的查看:
select * from sysobjects where xtype='P'
select * from sys.procedures
-------------------------------------------------------------------
#存储过程的删除:
DROP PROCEDURE sp_name;
IF OBJECT_ID (N'PROC_NAME', N'P') IS NOT NULL
DROP procedure PROC_NAME;