Sql Server 存储过程及游标

Sql-Server

存储过程

存储过程Procedure是一组为了完成特定功能的SQL语句集合，经编译后存储在数据库中，用户通过指定存储过程的名称并给出参数来执行存储过程中可以包含逻辑控制语句和数据操纵语句，它可以接受参数、输出参数、返回单个或多个结果集以及返回值。由于存储过程在创建时即在数据库服务器上进行了编译并存储在数据库中，所以存储过程运行要比单个的SQL语句块要快。同时由于在调用时只需用提供存储过程名和必要的参数信息，所以在一定程度上也可以减少网络流量、简单网络负担。

存储过程案例

创建数据库

create schema test; -- 创建schema create table test.Student( sid int IDENTITY(1,1) PRIMARY KEY NOT NULL, sname nchar(10), age int, sex char(2) );

插入数据

insert into test.Student(sname,age,sex) values(N'赵日天',18,'M'),(N'龙傲天',19,'M');

系统自带proc

exec sp_databases; --查看数据库 exec sp_tables; --查看表 exec sp_columns Student;--查看列 exec sp_helpIndex Student;--查看索引 exec sp_helpdb;--数据库帮助，查询数据库信息 select * from sys.objects --查询所有存储过程

创建proc语法

create proc | procedure pro_name [{@参数数据类型} [=默认值] [output], {@参数数据类型} [=默认值] [output], .... ] as SQL_statements

不同案列展示

-- 无参procdure 创建 create proc slt_pro as select top(1) * from test.Student; exec slt_pro; go -- 带参proc 创建 if (object_id('slt_pro_name', 'P') is not null) drop proc slt_pro_name go create proc slt_pro_name(@sname nchar(10)) as print '%'+ LTrim(RTrim(@sname))+'%' select top(2) * from test.Student where sname like '%'+ LTrim(RTrim(@sname))+'%'; exec slt_pro_name N'天' ; -- 带通配符参数存储过程 if (object_id('slt_pro_name_tpf', 'P') is not null) drop proc slt_pro_name_tpf go create proc slt_pro_name_tpf(@sname nchar(10) = N'%天%', @nextName nchar(10) = N'%龙%') as select * from student where name like @name or name like @nextName; go exec slt_pro_name_tpf; exec slt_pro_name_tpf '%o%', '%t%'; -- 带输出参数存储过程 if (object_id('slt_ipt_opt','P') is not null) drop proc slt_ipt_opt go create proc slt_ipt_opt( @id int,-- 默认输入参数 @sname nchar(10) out,-- 输出参数 @age int output -- 输入输出参数？？ ) as select @sname = sname, @age = age from test.Student where sid = @id and age = @age; go declare @id int, @sname nchar(10), @age int; set @id = 1; set @age = 18; exec slt_ipt_opt @id,@sname out,@age output; select @sname,@age; print @sname print @age -- WITH RECOMPILE 不缓存 /* 通常看到存储过程发生重编译有几种原因，少数的重编译未必是坏事 某表中数据的更改，由于数据的实时性，先前的执行计划效率将会降低，必然需要重编译; 另外一种情况是手动执行sp_recompile存储过程来强制存储过程的重编译，或者以WITH RECOMPILE选项来执行存储过程。 */ -- WITH ENCRYPTION 用于加密存储过程的文本 if (object_id('slt_encryption', 'P') is not null) drop proc slt_encryption go create proc slt_encryption with encryption as select top(1) * from test.Student; go exec slt_encryption; exec sp_helptext 'slt_encryption'; -- 加密无法访问 exec sp_helptext 'slt_ipt_opt'; --可以明文显示出proc的代码 -- 带游标参数存储过程 if (object_id('slt_cursor', 'P') is not null) drop proc slt_cursor go create proc slt_cursor @cur cursor varying output as set @cur = cursor forward_only static for select [sid],sname,age from test.Student; open @cur; go -- 调用 declare @exec_cur cursor; declare @sid int, @sname nchar(10), @age int; exec slt_cursor @cur = @exec_cur output;--调用存储过程 fetch next from @exec_cur into @sid, @sname, @age; while (@@fetch_status = 0) begin fetch next from @exec_cur into @sid, @sname, @age; print 'id: ' + convert(varchar, @sid) + ', name: ' + LTRIM(RTRIM(@sname)) + ', age: ' + convert(char, @age); end close @exec_cur; deallocate @exec_cur;--删除游标 -- 分页存储过程 row_number完成分页 if (object_id('slt_page', 'P') is not null) drop proc slt_page go create proc slt_page @startIndex int, @endIndex int as select count(*) as row_count from test.Student; select * from ( select row_number() over(order by sid) as rowId, * from test.Student )temp where temp.rowId between @startIndex and @endIndex go exec slt_page 1, 2 if (object_id('pro_page', 'P') is not null) drop proc pro_page go create procedure pro_page( @pageIndex int, @pageSize int ) as declare @startRow int, @endRow int set @startRow = (@pageIndex - 1) * @pageSize +1 set @endRow = @startRow + @pageSize -1 select * from ( select *, row_number() over (order by [sid] asc) as number from test.Student ) t where t.number between @startRow and @endRow; exec pro_page 3, 2; -- 第几页，页面数据条数

游标

游标是SQL Server的一种数据访问机制，它允许用户访问单独的数据行。用户可以对每一行进行单独的处理，从而降低系统开销和潜在的阻隔情况，用户也可以使用这些数据生成的SQL代码并立即执行或输出。

游标是一种处理数据的方法，主要用于存储过程，触发器和 T_SQL脚本中，它们使结果集的内容可用于其它T_SQL语句。在查看或处理结果集中向前或向后浏览数据的功能。类似与C语言中的指针，它可以指向结果集中的任意位置，当要对结果集进行逐条单独处理时，必须声明一个指向该结果集中的游标变量。

SELECT 语句返回的是一个结果集，但有时候应用程序并不总是能对整个结果集进行有效地处理，游标便提供了这样一种机制，它能从包括多条记录的结果集中每次提取一条记录，游标总是与一跳SQL选择语句相关联，由结果集和指向特定记录的游标位置组成。

SQL Server支持3中游标实现：

基于DECLARE CURSOR 语法，主要用于T_SQL脚本，存储过程和触发器。支持OLE DB和ODBC中的API游标函数，API服务器游标在服务器上实现。由SQL Server Native Client ODBC驱动程序和实现ADO API的DLL在内部实现。

游标创建语法

DECLARE cursor_name CURSOR [ LOCAL | GLOBAL] - cursor_name:是所定义的T_SQL 服务器游标的名称。 - LOCAL：对于在其中创建批处理、存储过程或触发器来说，该游标的作用域是局部的。 - GLOBAL：指定该游标的作用域是全局的 [ FORWARD_ONLY | SCROLL ] - FORWARD_ONLY: 指定游标只能从第一行滚动到最后一行。 [ STATIC | KEYSET | DYNAMIC | FAST_FORWARD ] - STATIC: 定义一个游标，以创建将又该游标使用的数据临时复本，对游标的所有请求都从tempdb中的这以临时表中不得到应答；因此，在对该游标进行提取操作时返回的数据中不反映对基表所做的修改，并且该游标不允许修改。 - KEYSET: 指定当游标打开时，游标重的行的成员身份和顺序已经固定。对行进行唯一标识的键值内置在tempdb内一个称为keyset的表中。 - DYNAMIC: 定义一个游标，以反映在滚动游标时对结果集内的各行所做的所有数据更改。行的数据值、顺序和成员身份在每次提取时都会更改，动态游标不支持ABSOLUTE提取选项。 - FAST_FORWARD: 指定启动了性能优化的FORWARD_ONLY、READ_ONLY游标。如果指定了SCROLL或FOR_UPDATE,则不能指定FAST_FORWARD。 [ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ] - SCROLL_LOCKS:指定通过游标进行的定位更新或删除一定会成功。 - OPTIMISTIC:指定如果行自读入游标以来已得到更新，则通过游标进行的定位更新或定位删除不成功。 [ TYPE_WARNING ] - TYPE_WARNING:指定游标从所请求的类型隐式转换为另一种类型时，向客户端发送警告消息。 FOR select_statement - select_statement:是定义游标结果集中的标准SELECT语句。 [ FOR UPDATE [ OF column_name [,...n] ] ]

游标案列

DECLARE @varcursor cursor,@name nchar(10),@age int; -- 申明游标变量 DECLARE cursor_student cursor for select sname,age from test.Student;-- 创建游标 open cursor_student -- 打开游标 set @varcursor=cursor_student -- 为游标变量赋值 fetch next from @varcursor into @name,@age -- @@FETCH_STATUS 只有在fetch语句之后才能改变为@@FETCH_STATUS=0 while (@@FETCH_STATUS=0) -- 判断fetch语句是否执行成功 begin fetch next from @varcursor into @name,@age -- 读取游标变量中的数据 print @name end close @varcursor -- 关闭游标 deallocate @varcursor -- 释放游标

@@ FETCH_STATUS

0 FETCH语句成功。1 FETCH语句失败，或者该行超出了结果集。2 提取的行丢失。9 游标未执行获取操作。 参考文章 https://www.cnblogs.com/hoojo/archive/2011/07/19/2110862.html https://www.cnblogs.com/selene/p/4480328.html