T-SQL 语句（五） —— 存储过程

2019-11-08 20:31:05

字体：大中小

来源：转载

供稿：网友

T-SQL 语句（五） —— 存储过程

一、存储过程简介

1、存储过程：一组预先写好的能实现某种功能的T-SQL 程序，指定一个程序名后编译后并将其存在SQL Server 中，以后要实现该功能可调用这个程序来完成。 2、存储过程的优点：　　ａ、执行速度快、效率高，存储过程会被编译成二进制可执行代码，运行存储过程时不需要编译，可加快执行速度。　　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 employeeGO -- 一个存储过程就是一个批处理，在遇到 GO 时查询编辑器会认为存储过程代码已经结束EXEC pr_employee_allCREATE PROC pr_getbyid_employee @id INTAS SELECT * FROM employee WHERE id = @idGOEXEC pr_getbyid_employee 5CREATE PROC pr_getbyname_employee @name NVARCHAR(20) = '默认'AS SELECT * FROM employee WHERE name LIKE '%' + @name + '%'GOEXEC pr_getbyname_employee -- 返回没有记录EXEC pr_getbyname_employee default -- 传递默认值，返回“默认”EXEC pr_getbyname_employee @name = '张三' -- 指定参数名，返回“张三”CREATE PROC pr_sumage_employee AS RETURN SELECT sum(age) FROM employeeGOCREATE PROC #tmp_proc AS SELECT * FROM employee WHERE id > 8 GOEXEC #tmp_proc

三、修改存储过程

ALTER PROC pr_employee_all AS -- 除了 ALTER PROC 修改存储过程的语句语法与创建存储过程的语法代码相同 SELECT * FROM employee ORDER BY id DESCGOEXEC 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 返回数字GODECLARE @count INT @count = EXEC pr_count_product_amount -- 执行求商品数量总计的存储过程PRINT @countCREATE PROC pr_count_order_amount AS DECLARE @count INT output -- 使用 output 返回数据 SELECT @count = count(id) FROM order RETURN @count -- 使用 Return 返回数字GODECLARE @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'GOCREATE PROC pr_group;2 WITH ENCRYPTION AS PRINT '存储过程组，编号2'GOEXEC pr_group;1EXEC pr_group;2

九、CLR 存储过程

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

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

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 CLRFunFROM 'C:/MyProject/CLRFun/CLRFun.dll';GO

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

CREATE PROC CLR_OrderQtySum @value INTAS EXTERNAL NAME CLR_SP.StoredProcedures.OrderQtySumGO

5、执行 CLR 存储过程

DECLARE @value INTEXEC CLRSP @value outputPRINT @value

上一篇：Redis 3.0集群搭建测试（一）

下一篇：Windows-MySQL安装命令