存储过程基础
存储过程是存储在SQL DBS上的过程,这个过程和VB6.0中的过程很像,可以传入参数,只能返回Int型的值用来反馈执行的状态或者自己定义它的其它语义(注意:凡是可以自行转换成Int类型的值也能返回,比如在存储过程中使用 return '0000003',调用的时候发现返回的是3)。
存储过程可以看成是一系列DB操作(DML、DDL)的集合,可以在其中使用事务……所以它可以完成复杂的逻辑,许多复杂常用而连锁的业务都是写在存储过程中的,这样做有很多优点。
下面的主要内容来自官网:CREATE PROCEDURE,官网的教程和文档十分齐全,完全没有必要写诸如我的博客上面的这些废话。但是我看东西看着看着速度就飞快,以为自己明白了就跳过,用的时候又不明白。发现写博客能让我耐心地看这些资料,并增加动手的机会以及留下思考的时间……扯远了。
MSSQL2008R2上用户自建存储过程分为两类:T-SQL Stored Procedure and CLR Stored Procedure(关于它们的区别,我会另外写),此系列创建的都是T-SQL SP。
创建语法:
CREATE { PROC | PROCEDURE } [schema_name.] procedure_name [ ; number ]
[ { @parameter [ type_schema_name. ] data_type }
[ VARYING ] [ = default ] [ OUT | OUTPUT ] [READONLY]
] [ ,...n ]
[ WITH <procedure_option> [ ,...n ] ]
[ FOR REPLICATION ]
AS { [ BEGIN ] sql_statement [;] [ ...n ] [ END ] }
[;]
<procedure_option> ::=
[ ENCRYPTION ]
[ RECOMPILE ]
[ EXECUTE AS Clause ]
参数说明:
大写的单词是关键字,是创建语法的骨架。小写的大部分是参数,或者是标记说明。中括号表示可选,比如[schema_name.]就是说创建存储过程时,模式可写可不写。
- schema_name:模式名(有的地方被翻译成架构),指定存储过程属于哪个模式。
- procedure_name:存储过程名。系统的存储过程多以sp_开头,在创建存储过程时应当避免使用sp_前缀。使用"#"前缀可以创建局部临时存储过程,“##”前缘可以创建全局临时存储过程。临时局部存储过程只对当前创建此存储过程的连接(注意不是用户)可见,当这个连接断开时,创建的临时存储过程就会被删除;全局存储过程对所有连接可见,当最后一个连接断开,它就会被删除。
- :number:这个在此版本之后将会被废弃,可以不用知道它。
- @parameter [type_schema_name.] data_type:@符后接的是参数名,[type_schema_name.] data_type是指定参数的类型,其中[type_schema_name.] 指定类型所属的模式。[VARYING] 只用来约束游标
- [VARYING]:只用来约束游标,和output关键字一起使用,指定游标作为一个output型的参数。(Transact-SQL stored procedures can use the cursor data type only for OUTPUT parameters. If the cursor data type is specified for a parameter, both the VARYING and OUTPUT parameters are required. If the VARYING keyword is specified for a parameter, the data type must be cursor and the OUTPUT keyword must be specified.)
- [ =default ][ OUT | OUTPUT ] [READONLY] [ ,...n ]:default是默认值,可以通过此语法为参数指示默认值,[ OUT | OUTPUT ]两个都可以,指示参数将被程序修改,此修改在过程外被体现,就像C中的传地址。[READONLY]指示参数不可被存储过程修改。[,...n]表示以上参数声明可以被重复n次,之间用","隔开。
- [ WITH <procedure_option> [ ,...n ] ]:存储过程选项,共有三种,在创建语法的下面已列出:
- ENCRYPTION:SQL server 会将原本的存储过程语句加密,无法查看其定义,无法修改,但是可以执行、删除。
- RECOMPILE:重编译。每次执行此存储过程时,SQL server都会重新编译此存储过程,再执行。
- EXECUTE AS Clause:指定执行此存储过程的上下文。
- ENCRYPTION:SQL server 会将原本的存储过程语句加密,无法查看其定义,无法修改,但是可以执行、删除。
- FOR REPLICATION:作为自动应答的存储过程,指定了此属性后,SP不能有参数,也不能被用户调用。
- AS BEGIN ... END:存储过程主体。
调用语法
{ EXEC | EXECUTE } [schema_name.]procedue_name @parameter [VARYING][ OUT| OUTPUT][,...n]
如果SP为参数指定了 OUT|OUTPUT ,那么调用时该位置的参数后一定也要加此标志,否则参数值无法传出SP外。
限制和约束
● 以下语句不能在存储过程中使用(BEGIN...END中,是绝对不可以):
CREATE AGGREGATE | CREATE SCHEMA | SET SHOWPLAN_TEXT |
CREATE DEFAULT | CREATE or ALTER TRIGGER | SET SHOWPLAN_XML |
CREATE or ALTER FUNCTION | CREATE or ALTER VIEW | USE database_name |
CREATE or ALTER PROCEDURE | SET PARSEONLY | |
CREATE RULE | SET SHOWPLAN_ALL |
●不能指定一个函数作为存储过程的参数。
例子
这是一个表无关的存储过程,输入两个数和一个计算符,计算的结果存放在一个OUT型的参数中,用来说明创建一个简单的SP:
CREATE PROC mathMethod
(
@num_a DECIMAL(10,2)=0,
@operator CHAR='+',
@num_b DECIMAL(10,2)=0,
@num_result DECIMAL(10,2)=0 OUTPUT
)
AS
BEGIN
SET @num_result=
CASE @operator
WHEN '+' THEN @num_a+@num_b
WHEN '-' THEN @num_a-@num_b
WHEN '*' THEN @num_a*@num_b
WHEN '/' THEN @num_a/@num_b
ELSE 0
END
END
T-SQL调用:
DECLARE @res DECIMAL(10,2)
EXEC mathMethod 1.2,'*',2.0,@res OUT
SELECT @res;
输出:
2.40