SQL Server 存储过程详解

SQL Server 存储过程详解

◆优点：

执行速度更快。存储过程只在创造时进行编译，而一般SQL语句每执行一次就编译一次，所以使用存储过程执行速度更快。

存储过程用于处理复杂的操作时，程序的可读性更强、网络的负担更小。

使用存储过程封装事务性能更佳。

能有效的放注入，安全性更好。

可维护性高，在一些业务规则发生变化时，有时只需调整存储过程即可，而不用改动和重编辑程序。

更好的代码重用。

◆ 缺点：

存储过程将给服务器带来额外的压力。

存储过程多多时维护比较困难。

移植性差，在升级到不同的数据库时比较困难。

调试麻烦，SQL语言的处理功能简单。

总之复杂的操作或需要事务操作的SQL建议使用存储过程，而参数多且操作简单SQL语句不建议使用存储过程。

存储过程定义

存储过程是一组 Transact-SQL 语句，它们只需编译一次，以后即可多次执行。因为

Transact-SQL 语句不需要重新编译，所以执行存储过程可以提高性能。

触发器是一种特殊的存储过程，不由用户直接调用。创建触发器时，将其定义为在对特定表或列进行特定类型的数据修改时激发。

存储过程的设计规则

CREATE PROCEDURE 定义自身可以包括任意数量和类型的 SQL 语句，但以下语句除外。

不能在存储过程的任何位置使用这些语句。

CREATE AGGREGATE、 CREATE RULE、CREATE DEFAULT、 CREATE

SCHEMA、CREATE 或 ALTER FUNCTION、CREATE 或 ALTER TRIGGER、CREATE

或 ALTER PROCEDURE、CREATE 或 ALTER VIEW、SET PARSEONLY、SET

SHOWPLAN_ALL、SET SHOWPLAN_TEXT、 SET SHOWPLAN_XML、USE

database_name

其他数据库对象均可在存储过程中创建。可以引用在同一存储过程中创建的对象，只要引用时已经创建了该对象即可。

可以在存储过程内引用临时表。

如果在存储过程内创建本地临时表，则临时表仅为该存储过程而存在；退出该存储过程后，临时表将消失。

如果执行的存储过程将调用另一个存储过程，则被调用的存储过程可以访问由第一个存

储过程创建的所有对象，包括临时表在内。

如果执行对远程 Microsoft SQL Server 2005 实例进行更改的远程存储过程，则不能回滚这些更改。远程存储过程不参与事务处理。

存储过程中的参数的最大数目为 2100。

存储过程中的局部变量的最大数目仅受可用内存的限制。

根据可用内存的不同，存储过程最大可达 128 MB

实现存储过程

CREATE { PROC | PROCEDURE } [schema_name.] procedure_name [ ; number ]

[ { @parameter [ type_schema_name. ] data_type } [ VARYING ] [ = default ]

[ [ OUT [ PUT ] ] --名称、类型、默认值、方向

[ ,...n ]

[ WITH [ ,...n ]

[ FOR REPLICATION ]

AS

{ [;][ ...n ] | } --SQL语句

[;]

::=

[ ENCRYPTION ]

[ RECOMPILE ] --运行时编译

[ EXECUTE_AS_Clause ]

::= { [ BEGIN ] statements [ END ] }

::= EXTERNAL NAME

assembly___name

执行存储过程

使用 Transact-SQL EXECUTE 语句。如果存储过程是批处理中的第一条语句，那么不使用 EXECUTE 关键字也可以执行存储过程

使用 sp_procoption 让SQLSERVER 自动执行存储过程

sp_procoption [ @ProcName = ] 'procedure' , [ @OptionName = ] 'option' ,

[ @OptionValue = ] 'value' --过程的名称、option 的唯一值为 startup、设置为开启（true 或

on）还是关闭（false 或 off）。

用TSQL语句编写存储过程

一、变量和参数

DECLARE 语句通过以下操作初始化 Transact-SQL 变量：

指定名称。名称的第一个字符必须为一个 @。

指定系统提供的或用户定义的数据类型和长度。对于数值变量还指定精度和小数位数。对于 XML 类型的变量，可以指定一个可选的架构集合。

将值设置为 NULL。

如：DECLARE @MyCounter int

第一次声明变量时，其值设置为 NULL。若要为变量赋值，请使用 SET 语句。这是为变量赋值的首选方法。也可以通过 SELECT 语句的选择列表中当前所引用值为变量赋

值。

参数用于在存储过程和函数以及调用存储过程或函数的应用程序或工具之间交换数据：

输入参数允许调用方将数据值传递到存储过程或函数。

输出参数允许存储过程将数据值或游标变量传递回调用方。用户定义函数不能指定输出参数。

每个存储过程向调用方返回一个整数返回代码。如果存储过程没有显式设置返回代码的值，则返回代码为 0。

二、流程控制语句

1、BEGIN 和 END 语句

BEGIN 和 END 语句用于将多个 Transact-SQL 语句组合为一个逻辑块。在控制流语句必须执行包含两条或多条 Transact-SQL 语句的语句块的任何地方，都可以使用

BEGIN 和 END 语句。

如：

IF (@@ERROR <> 0)

BEGIN

SET @ErrorSaveVariable = @@ERROR

PRINT 'Error encountered, ' +

CAST(@ErrorSaveVariable AS VARCHAR(10))

END

2、GOTO 语句

GOTO 语句使 Transact-SQL 批处理的执行跳至标签。不执行 GOTO 语句和标签之间的语句。

IF(1=1)

GOTO calculate_salary

print 'go on' --条件成立则跳过此句。

calculate_salary:

print 'go to'

3、IF...ELSE 语句

IF 语句用于条件的测试。得到的控制流取决于是否指定了可选的 ELSE 语句：

if(1=1)

print 1

else if(2=2)

print 2

else if(3=3)

print 3

else

print 0

4、RETURN 语句

RETURN 语句无条件终止查询、存储过程或批处理。存储过程或批处理中

RETURN 语句后面的语句都不执行。当在存储过程中使用 RETURN 语句时，此语句可以指定返回给调用应用程序、批处理或过程的整数值。如果 RETURN 未指定值，则存储过程返回 0

5、WAITFOR 语句

WAITFOR 语句挂起批处理、存储过程或事务的执行，直到发生以下情况：

已超过指定的时间间隔。

到达一天中指定的时间。

指定的 RECEIVE 语句至少修改一行或并将其返回到 Service Broker 队列。

WAITFOR 语句由下列子句之一指定：

DELAY 关键字后为 time_to_pass，是指完成 WAITFOR 语句之前等待的时间。完成 WAITFOR 语句之前等待的时间最多为 24 小时。

如：

WAITFOR DELAY '00:00:02'

SELECT EmployeeID FROM Employee;

TIME 关键字后为 time_to_execute，指定 WAITFOR 语句完成所用的时间。

GO

BEGIN

WAITFOR TIME '22:00';

DBCC CHECKALLOC;

END;

GO

RECEIVE 语句子句，从 Service Broker 队列检索一条或多条消息。使用

RECEIVE 语句指定 WAITFOR 时，如果当前未显示任何消息，该语句将等待消息到达队列。

TIMEOUT 关键字后为 timeout，指定 Service Broker 等待消息到达队列的时间长度（毫秒）。可以在 RECEIVE 语句或 GET CONVERSATION GROUP 语句中指定

TIMEOUT。

6、BREAK 或 CONTINUE 语句

只要指定的条件为 True 时，WHILE 语句就会重复语句或语句块。REAK 或

CONTINUE语句通常和WHILE一起使用。BREAK 语句退出最内层的 WHILE 循环，CONTINUE 语句则重新开始 WHILE 循环。

go

declare @Num int

declare @ID int

declare @i int

set @i=1

while(exists(select * from T where Num<5 )) --获取数量小于5的记录

begin

select @Num=Num,@ID=ID from T where Num<5 order by ID desc

print Str(@i)+ '编号：'+Str(@ID)+ ' 值'+str(@Num)

update T set Num=Num*2 where ID=@ID

set @i=@i+1

if(@i>3)

break --退出循环

end

7、CASE 语句

CASE 函数用于计算多个条件并为每个条件返回单个值。CASE 函数通常的用途是将代码或缩写替换为可读性更强的值

--用法一：

select ID,

Grade=Case Num

when 1 then '不及格'

when 2 then '不及格'

when 3 then '不及格'

when 4 then '良好'

else '优秀'

end

from T

---用法二：

select ID,

Grade=Case

when Num<3 then '不及格'

when Num=3 then '及格'

when Num=4 then '良好'

when Num>4 then '优秀'

end

from T

三、运行时生成语句

Transact-SQL 支持使用下列两种方法于运行时在 TTransact-SQL 脚本、存储过程和触发器中生成 SQL 语句：

使用 sp_executesql 系统存储过程执行 Unicode 字符串。sp_executesql 支持与

RAISERROR 语句类似的参数替换。

使用 EXECUTE 语句执行字符串。EXECUTE 语句不支持已执行字符串中的参数替换。

四、处理数据库引擎错误

在 Transact-SQL 中有两种方式可以获取错误信息：

1、在 CATCH 构造的 CATCH 块的作用域内，您可以使用以下系统函数：

ERROR_LINE()，返回出现错误的行号。

ERROR_MESSAGE()，返回将返回给应用程序的消息文本。该文本包括为所有可替换参数提供的值，如长度、对象名或时间。

ERROR_NUMBER() 返回错误号。

ERROR_PROCEDURE()，返回出现错误的存储过程或触发器的名称。如果在存储过程或触发器中未出现错误，该函数返回 NULL。

ERROR_SEVERITY() 返回严重性。

ERROR_STATE()，返回状态。

2、在执行任何 Transact-SQL 语句之后，您可以立即使用 @@ERROR 函数测试错误并检索错误号。

RAISERROR

RAISERROR 用于将与 SQL Server Database Engine 生成的系统错误或警告消息使用相同格式的消息返回到应用程序中。

3、PRINT

PRINT 语句用于将消息返回到应用程序。PRINT 采用字符或 Unicode 字符串表达式作为参数，并将字符串作为消息返回到应用程序。

1使用不带参数的存储过程

使用 JDBC 驱动程序调用不带参数的存储过程时，必须使用 call SQL 转义序列。不带参数的 call 转义序列的语法如下所示：

{call procedure-name}

作为实例，在 SQL Server 2005 AdventureWorks 示例数据库中创建以下存储过程：

CREATE PROCEDURE GetContactFormalNames

AS

BEGIN

SELECT TOP 10 Title + ' ' + FirstName + ' ' + LastName AS FormalName

FROM t

END此存储过程返回单个结果集，其中包含一列数据（由 t 表中前十个联系人的称呼、名称和姓氏组成）。

在下面的实例中，将向此函数传递 AdventureWorks 示例数据库的打开连接，然后使用

executeQuery 方法调用 GetContactFormalNames 存储过程。

public static void executeSprocNoParams(Connection con) ...{

try ...{

Statement stmt = Statement();

ResultSet rs = eQuery("{call tactFormalNames}");

while (()) ...{

n(ing("FormalName"));

}

();

();

}

catch (Exception e) ...{

tackTrace();

}

}2使用带有输入参数的存储过程

使用 JDBC 驱动程序调用带参数的存储过程时，必须结合 SQLServerConnection 类的

prepareCall 方法使用 call SQL 转义序列。带有 IN 参数的 call 转义序列的语法如下所示：

{call procedure-name[([parameter][,[parameter]]...)]}

构造 call 转义序列时，请使用 ?（问号）字符来指定 IN 参数。此字符充当要传递给该存储过程的参数值的占位符。可以使用 SQLServerPreparedStatement 类的 setter 方法之一为参数指定值。可使用的 setter 方法由 IN 参数的数据类型决定。

向 setter 方法传递值时，不仅需要指定要在参数中使用的实际值，还必须指定参数在存储过程中的序数位置。例如，如果存储过程包含单个 IN 参数，则其序数值为 1。如果存储过程包含两个参数，则第一个序数值为 1，第二个序数值为 2。

作为如何调用包含 IN 参数的存储过程的实例，使用 SQL Server 2005 AdventureWorks 示例数据库中的 uspGetEmployeeManagers 存储过程。此存储过程接受名为 EmployeeID 的单个输入参数（它是一个整数值），然后基于指定的 EmployeeID 返回雇员及其经理的递归列表。下面是调用此存储过程的 Java 代码：

public static void executeSprocInParams(Connection con) ...{

try ...{

PreparedStatement pstmt = eStatement("{call

EmployeeManagers(?)}");

(1, 50);

ResultSet rs = eQuery();

while (()) ...{

n("EMPLOYEE:");

n(ing("LastName") + ", " + ing("FirstName"));

n("MANAGER:");

n(ing("ManagerLastName") + ", " +

ing("ManagerFirstName"));

n();

}

();

();

}

catch (Exception e) ...{

tackTrace();

}

} 3使用带有输出参数的存储过程

使用 JDBC 驱动程序调用此类存储过程时，必须结合 SQLServerConnection 类的

prepareCall 方法使用 call SQL 转义序列。带有 OUT 参数的 call 转义序列的语法如下所示：

{call procedure-name[([parameter][,[parameter]]...)]}

构造 call 转义序列时，请使用 ?（问号）字符来指定 OUT 参数。此字符充当要从该存储过程返回的参数值的占位符。要为 OUT 参数指定值，必须在运行存储过程前使用

SQLServerCallableStatement 类的 registerOutParameter 方法指定各参数的数据类型。

使用 registerOutParameter 方法为 OUT 参数指定的值必须是 所包含的

JDBC 数据类型之一，而它又被映射成本地 SQL Server 数据类型之一。有关 JDBC 和 SQL

Server 数据类型的详细信息，请参阅了解 JDBC 驱动程序数据类型。

当您对于 OUT 参数向 registerOutParameter 方法传递一个值时，不仅必须指定要用于此参数的数据类型，而且必须在存储过程中指定此参数的序号位置或此参数的名称。例如，如果存储过程包含单个 OUT 参数，则其序数值为 1；如果存储过程包含两个参数，则第一个序数值为 1，第二个序数值为 2。

作为实例，在 SQL Server 2005 AdventureWorks 示例数据库中创建以下存储过程： 根据指定的整数 IN 参数 (employeeID)，该存储过程也返回单个整数 OUT 参数 (managerID)。根据 ee 表中包含的 EmployeeID，OUT 参数中返回的值为

ManagerID。

在下面的实例中，将向此函数传递 AdventureWorks 示例数据库的打开连接，然后使用

execute 方法调用 GetImmediateManager 存储过程：

public static void executeStoredProcedure(Connection con) ...{

try ...{

CallableStatement cstmt = eCall("{call ediateManager(?, ?)}");

(1, 5);

erOutParameter(2, R);

e();

n("MANAGER ID: " + (2));

}

catch (Exception e) ...{

tackTrace();

}

} 本示例使用序号位置来标识参数。或者，也可以使用参数的名称（而非其序号位置）来标识此参数。下面的代码示例修改了上一个示例，以说明如何在 Java 应用程序中使用命名参数。请注意，这些参数名称对应于存储过程的定义中的参数名称：

CREATE PROCEDURE GetImmediateManager

@employeeID INT,

@managerID INT OUTPUT

AS

BEGIN

SELECT @managerID = ManagerID

FROM ee

WHERE EmployeeID = @employeeID

END 存储过程可能返回更新计数和多个结果集。Microsoft SQL Server 2005 JDBC Driver 遵循 JDBC 3.0 规范，此规范规定在检索 OUT 参数之前应检索多个结果集和更新计数。也就是说，应用程序应先检索所有 ResultSet 对象和更新计数，然后使用

方法检索 OUT 参数。否则，当检索 OUT 参数时，尚未检索的 ResultSet 对象和更新计数将丢失。

4 使用带有返回状态的存储过程

使用 JDBC 驱动程序调用这种存储过程时，必须结合 SQLServerConnection 类的

prepareCall 方法使用 call SQL 转义序列。返回状态参数的 call 转义序列的语法如下所示：

{[?=]call procedure-name[([parameter][,[parameter]]...)]}

构造 call 转义序列时，请使用 ?（问号）字符来指定返回状态参数。此字符充当要从该存储过程返回的参数值的占位符。要为返回状态参数指定值，必须在执行存储过程前使用

SQLServerCallableStatement 类的 registerOutParameter 方法指定参数的数据类型。

此外，向 registerOutParameter 方法传递返回状态参数值时，不仅需要指定要使用的参数的数据类型，还必须指定参数在存储过程中的序数位置。对于返回状态参数，其序数位置始终为 1，这是因为它始终是调用存储过程时的第一个参数。尽管

SQLServerCallableStatement 类支持使用参数的名称来指示特定参数，但您只能对返回状态参数使用参数的序号位置编号。

作为实例，在 SQL Server 2005 AdventureWorks 示例数据库中创建以下存储过程：

CREATE PROCEDURE CheckContactCity

(@cityName CHAR(50))

AS

BEGIN

IF ((SELECT COUNT(*)

FROM s

WHERE City = @cityName) > 1)

RETURN 1

ELSE

RETURN 0

END 该存储过程返回状态值 1 或 0，这取决于是否能在表 s 中找到

cityName 参数指定的城市。

在下面的实例中，将向此函数传递 AdventureWorks 示例数据库的打开连接，然后使用

execute 方法调用 CheckContactCity 存储过程：

public static void executeStoredProcedure(Connection con) ...{

try ...{

CallableStatement cstmt = eCall("{? = call ontactCity(?)}");

erOutParameter(1, R);

ing(2, "Atlanta");

e();

n("RETURN STATUS: " + (1));

}

();

catch (Exception e) ...{

tackTrace();

}

}5 使用带有更新计数的存储过程

使用 SQLServerCallableStatement 类构建对存储过程的调用之后，可以使用 execute 或

executeUpdate 方法中的任意一个来调用此存储过程。executeUpdate 方法将返回一个 int

值，该值包含受此存储过程影响的行数，但 execute 方法不返回此值。如果使用 execute 方法，并且希望获得受影响的行数计数，则可以在运行存储过程后调用 getUpdateCount 方法。

作为实例，在 SQL Server 2005 AdventureWorks 示例数据库中创建以下表和存储过程：

CREATE TABLE TestTable

(Col1 int IDENTITY,

Col2 varchar(50),

Col3 int);

CREATE PROCEDURE UpdateTestTable

@Col2 varchar(50),

@Col3 int

AS

BEGIN

UPDATE TestTable

SET Col2 = @Col2, Col3 = @Col3

END; 在下面的实例中，将向此函数传递 AdventureWorks 示例数据库的打开连接，并使用 execute 方法调用 UpdateTestTable 存储过程，然后使用 getUpdateCount 方法返回受存储过程影响的行计数。

public static void executeUpdateStoredProcedure(Connection con) ...{

try ...{

CallableStatement cstmt = eCall("{call TestTable(?, ?)}");

ing(1, "A");

(2, 100);

e();

int count = ateCount();

();

n("ROWS AFFECTED: " + count);

}

catch (Exception e) ...{

tackTrace();

}

}

本文来自CSDN博客，转载请标/java2000_net/archive/2008/04/21/

明出处：