T-SQL 语句(五) —— 存储过程

2/13/2017来源:SQL技巧人气:1165

T-SQL 语句(五) —— 存储过程

一、存储过程简介

1、存储过程:一组预先写好的能实现某种功能的T-SQL 程序,指定一个程序名后编译后并将其存在SQL Server 中,以后要实现该功能可调用这个程序来完成。 2、存储过程的优点:   a、执行速度快、效率高,存储过程会被编译成二进制可执行代码,运行存储过程时不需要编译,可加快执行速度。   b、模块化编程,创建后可被多次调用而不必重新编写该T-SQL语句,一次修过所有调用该存储过程的程序多得到的结果都会被修改,提高了程序的可移植性。   c、减少网络流量:客户端调用时只需指定存储过程名称和参数,而不用传送整个T-SQL语句,利于减少网络流量,提高运行速度。   d、安全性,可以作为一种安全机制为不同权限的用户使用不同的存储过程。 3、存储过程的类型:   a、系统存储过程:系统存储过程一般以“sp_”为前缀,从物理意义上讲系统存储过程存储在 Resource 数据库中   b、扩展存储过程:通常以“xp_”为前缀,是由其他编程语言(如C#)创建的外部存储过程,内容不存储在SQL Server 中而是以 DLL 形式存在。   c、用户自定义存储过程:用户自定义存储过程分为 T-SQL 存储过程和 CLR 存储过程两种。T-SQL 存储过程保存 T-SQL 语句集合,可以接受和返回用户提供的参数;CLR 存储过程时针对 CLR 方法的引用,可以接受和返回用户提供的参数,CLR 存储过程在.NET Framework 程序中是作为公共静态方法实现的。

二、创建存储过程
CREATE PROC pr_employee_all AS SELECT * FROM employee GO -- 一个存储过程就是一个批处理,在遇到 GO 时查询编辑器会认为存储过程代码已经结束 EXEC pr_employee_all CREATE PROC pr_getbyid_employee @id INT AS SELECT * FROM employee WHERE id = @id GO EXEC pr_getbyid_employee 5 CREATE PROC pr_getbyname_employee @name NVARCHAR(20) = '默认' AS SELECT * FROM employee WHERE name LIKE '%' + @name + '%' GO EXEC pr_getbyname_employee -- 返回没有记录 EXEC pr_getbyname_employee default -- 传递默认值,返回“默认” EXEC pr_getbyname_employee @name = '张三' -- 指定参数名,返回“张三” CREATE PROC pr_sumage_employee AS RETURN SELECT sum(age) FROM employee GO CREATE PROC #tmp_proc AS SELECT * FROM employee WHERE id > 8 GO EXEC #tmp_proc
三、修改存储过程
ALTER PROC pr_employee_all AS -- 除了 ALTER PROC 修改存储过程的语句语法与创建存储过程的语法代码相同 SELECT * FROM employee ORDER BY id DESC GO EXEC pr_employee_all
四、存储过程的返回值

1、存储过程以“return n”的形式返回一个整数值。 2、存储过程指定一个 OUTPUT 的返回参数以返回值。 3、存储过程执行 T-SQL 语句返回数据集,如 SELECT 语句

CREATE PROC pr_count_product_amount AS DECLARE @count INT SELECT @count = sum(id) FROM employee RETURN @count -- 使用 Return 返回数字 GO DECLARE @count INT @count = EXEC pr_count_product_amount -- 执行求商品数量总计的存储过程 PRINT @count CREATE PROC pr_count_order_amount AS DECLARE @count INT output -- 使用 output 返回数据 SELECT @count = count(id) FROM order RETURN @count -- 使用 Return 返回数字 GO DECLARE @count INT EXEC pr_count_order_amount @count output -- 执行求订单数量总计的存储过程 PRINT @count --SET NOCOUNT ON 关闭显示受影响行数
五、存储过程的嵌套调用

存储过程可以多次嵌套调用,调用最多层次可以嵌套到32层,可以使用@@NESTLEVEL 来查看当前正在执行的存储过程的嵌套层数

ALTER PROC pr_nest -- 嵌套调用 @i INT = 0 AS BEGIN PRINT 'This is pr_nest,Level' PRINT @@NESTLEVEL SET @i = @i + 1 IF @i < 3 -- 设定退出条件 EXEC pr_nest @i END GO EXEC pr_nest default
六、加密存储过程
CREATE PROC pc_test WITH ENCRYPTION AS PRINT 'Hello World' -- 跟视图加密类似使用户无法查看存储过程的内容
七、查看存储过程源码
EXEC sp_helptext pr_test -- 查看源码,加密存储过程则无法查看
八、使用存储过程组
CREATE PROC pr_group;1 WITH ENCRYPTION AS PRINT '存储过程组,编号1' GO CREATE PROC pr_group;2 WITH ENCRYPTION AS PRINT '存储过程组,编号2' GO EXEC pr_group;1 EXEC pr_group;2
九、CLR 存储过程

1、开启 SQL Server 对 CLR 存储过程的支持

EXEC sp_configure 'clr','1' -- 开启SQL Server 对 CLR 存储过程的支持 GO RECONFIGURE GO

2、编写 CLR 存储过程代码

using System; using System.Data; using System.Data.SqlClient; using System.Data.SqlTypes; using Microsoft.SqlServer.Server; namespace CLR_SP { public partial class StoredProcedures { [Microsoft.SqlServer.Server.SqlProcedure] public static void OrderQtySum(out SqlInt16 value)--存储过程返回参数使用 out关键字 { //设置连接 SqlConnection conn = new SqlConnection(); conn.ConnectionString = "Context Connection=true"; conn.Open(); //设置命令 SqlCommand cmd = new SqlCommand(); cmd.Connection = conn; cmd.CommandText = "SELECT TOP 10 OrderQty FROM OrderDetail"; //以只进方式读取 SQL Server 中的数据 value = 0; SqlDataReader reader = cmd.ExecuteReader(); while(reader.Reader()) { value += reader.GetSqlInt16(0);//获取指定列的值 } } [Microsoft.SqlServer.Server.SqlProcedure] public static void ExecuteToClient(SqlInt32 orderID) { SqlConnection conn = new SqlConnection(); conn.ConnectionString = "Context Connection=true"; conn.Open(); SqlCommand cmd = new SqlCommand(); cmd.Connection = conn; cmd.CommandText = "SELECT ProductID,OrderQty,UnitPrice FROM OrderItem WHERE OrderID=@orderID"; cmd.Parameters.AddWithValue("@orderID",orderID); SqlContext.Pipe.ExecuteAndSend(cmd); } } }

3、编译 CLR 存储过程代码成为程序集并注册到 SQL Server

CREATE ASSEMBLY CLRFun FROM 'C:\MyProject\CLRFun\CLRFun.dll'; GO

4、创建CLR 存储过程的引用

CREATE PROC CLR_OrderQtySum @value INT AS EXTERNAL NAME CLR_SP.StoredProcedures.OrderQtySum GO

5、执行 CLR 存储过程

DECLARE @value INT EXEC CLRSP @value output PRINT @value