№04 存储过程§1

最新推荐文章于 2022-05-11 15:31:13 发布

原创最新推荐文章于 2022-05-11 15:31:13 发布 · 762 阅读

0 ·

CC 4.0 BY-SA版权

MSSQL2008R2 专栏收录该内容

6 篇文章

订阅专栏

存储过程基础

存储过程是存储在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:指定执行此存储过程的上下文。
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