您的位置: 首页  >  IT文章  >  MS SQL 惯用SQL语句（四）：创建、修改存储过程、游标等sql

MS SQL 惯用SQL语句（四）：创建、修改存储过程、游标等sql

分类: IT文章 • 2024-11-12 21:57:12
MS SQL 常用SQL语句（四）：创建、修改存储过程、游标等sql

4、存储过程操作：

--1、创建存储过程
--语法结构：
--Create Proc 过程名称
--@Parameter 参数类型
--[,@Parameter 参数类型output]
--As
--Begin
--....
--end

--不带参数
create proc newProc
as
begin
declare @str varchar(50);
set  @str='123456';
print @str;
end

--带参数
create procedure newProc2
@testStr varchar(50),--若只有一个参数则不用逗号
@testStr2 varchar(50)
As
begin
	print '第一个参数的值为：'+@testStr;
	print '第二个参数的值为：'+@testStr2;
end

--带输出值
create procedure newProc3
@test int,
@test2 int,
@testSum int output
as
begin
	set @testSum = @test+@test2;
end

--2、执行存储过程
--语法结构：exec|execute proc|procedure procedureName [parameters];
exec newProc;--不带参数，也没有返回值
exec newProc2 '123456','ABCDEFG';--带参数，没有返回值

--带参数，有返回值
declare @testSum int;
set @testSum=100;
print @testSum;
exec newProc3 10,20,@testSum output;
print @testSum;

--3、查看存储过程：
select * from sysobjects where xtype='P'
--或者
SELECT name, definition
FROM sys.sql_modules AS m
INNER JOIN sys.all_objects AS o ON m.object_id = o.object_id
WHERE o.[type] = 'P'

--xtype可替换为如下参数：
--C = CHECK 约束
--D = 默认值或 DEFAULT 约束
--F = FOREIGN KEY 约束
--L = 日志 
--FN = 标量函数
--IF = 内嵌表函数
--P = 存储过程
--PK = PRIMARY KEY 约束（类型是 K）
--RF = 复制筛选存储过程
--S = 系统表
--TF = 表函数
--TR = 触发器
--U = 用户表
--UQ = UNIQUE 约束（类型是 K）
--V = 视图
--X = 扩展存储过程

--4、修改存储过程
--修改与创建语法结构一致，就是将创建的create关键字修改成alter关键字即可,至于业务逻辑可以根据实际情况修改。
alter proc newProc
as
begin
declare @str varchar(50);
set  @str='123abc';
print @str;
end

--5、重命名存储过程名称
--语法结构：exec sp_rename '存储过程的原名称','存储过程的新名称';
exec sp_rename 'newProc','newProc1';
--可能会产生警告：更改对象名的任一部分都可能会破坏脚本和存储过程。

--6、删除存储过程
--语法结构：exec procedure 存储过程名称[,存储过程,存储过程];
drop procedure newProc;
drop procedure newProc2,newProc3;

5、游标操作：

--1、创建游标
--语法结构：
/*
declare 游标名称 [Insensitive] cursor
[local|global]
[forward_only|scroll]
[static|keyset|dynamic|fast_forward]
[read_only|scroll_locks|optimistic]
[type_waring]
for SQL语句
[For [read only|update]]
*/
--只读游标
declare testCursor cursor for
select * from employee
for read only;--该游标只读

--普通游标
declare testCursor2 cursor local scroll for
select * from employee;
--(sql server2005中不能添加local关键字，否则“游标可以创建成功open的时候会提示游标不存在”)

--可更新游标
declare testCursor3 cursor local scroll for
select * from employee
for update;


--2、打开游标
open testCursor2;

--3、使用游标
--一条一条往后获取
fetch next from testCursor2;

--一条一条往前获取（当前游标不能为只进游标）
fetch prior from testCursor2;
--报错：提取类型 prior 不能与只进游标一起使用。因为testCursor为只进游标

--获取第一条记录
fetch first from testCursor2;

--获取最后一条记录
fetch last from testCursor2;

--跳转到指定条数:获取游标的第二条记录，这个第二条以整个游标中的数据为起点
fetch absolute 1 from testCursor2;

--使用变量进行跳转
declare @index int;
set @index = 3;
fetch absolute @index from testCursor2;


--相对定位：以当前位置为起点，正数表示向前，负数表示向后
--目前游标的位置为4，relative 1表示向前一条也就是第5条，-1表示向后一条也就是第3条
fetch relative 5 from testCursor2;

--从游标中获取值存入到变量中
declare @empId int;
declare	@empName varchar(50);
declare @empAge smallint;
declare	@empPay money;
declare	@empJob varchar(50);
declare	@empAddress varchar(200);
declare	@telephone varchar(50);
declare	@empReward money;
declare	@total money;

fetch absolute 1 from testCursor2 into @empId,@empName,@empAge,@empPay,@empJob,@empAddress,@telephone,@empReward,@total;

print '编号：'+cast(@empId as varchar(30));
print '姓名：'+@empName;
print '年龄：'+cast(@empAge as varchar(30));
print '工资：'+cast(@empPay as varchar(30));
print '职务：'+@empJob;
print '住址：'+@empAddress;
print '电话：'+@telephone;
print '奖金：'+cast(@empReward as varchar(30));
print '总共收入：'+cast(@total as varchar(30));

--4、关闭游标：游标关闭之后还可以再打开
--语法结构：close 游标名称
close testCursor;

--5、释放游标:游标释放之后不能再打开
--语法结构：deallocate 游标名称
deallocate testCursor

创建游标的语法结构参数说明：

[local|global]：游标的作用范围,分别表示局部(即游标所在的过程/触发器/批处理程序中使用)和全局(即除了可以在过程/触发器/批处理程序中使用之外整个会话层都可以使用),默认为local

[forward_only|scroll]：只进游标和上下调整游标,若定义成只进游标那么该游标只能从前往后获取数据不能从后往前或跳着读取数据,而scroll游标可以随意获取数据。默认为forward_only。Fetch Next是唯一支持的提取选项。如果在指定Forward_Only是不指定Static、KeySet、Dynamic关键字，默认为Dynamic游标。如果Forward_Only和Scroll没有指定，Static、KeySet、Dynamic游标默认为Scroll，Fast_Forward默认为Forward_Only

[static|keyset|dynamic|fast_forward]：Static:静态游标。KeySet：键集游标。Dynamic：动态游标，不支持Absolute提取选项。Fast_Forward：指定启用了性能优化的Forward_Only、Read_Only游标。如果指定啦Scroll或For_Update，就不能指定他啦。

[read_only|scroll_locks|optimistic]：Read_Only:不能通过游标对数据进行删改。Scroll_Locks：将行读入游标是，锁定这些行，确保删除或更新一定会成功。如果指定啦Fast_Forward或Static，就不能指定他啦。Optimistic：指定如果行自读入游标以来已得到更新，则通过游标进行的定位更新或定位删除不成功。当将行读入游标时，sqlserver不锁定行，它改用timestamp列值的比较结果来确定行读入游标后是否发生了修改，如果表不行timestamp列，它改用校验和值进行确定。如果已修改改行，则尝试进行的定位更新或删除将失败。如果指定啦Fast_Forward,则不能指定他。

[type_waring]:指定将游标从所请求的类型隐式转换为另一种类型时向客户端发送警告信息。

[For [read only|update]]：只读游标和更新游标，若不定义就是普通游标

 

游标的分类：

根据游标检测结果集变化的能力和消耗资源的情况不同，SQL Server支持的API服务器游标分为一下4种：
静态游标: 静态游标的结果集，在游标打开的时候建立在TempDB中，不论你在操作游标的时候，如何操作数据库，游标中的数据集都不会变。例如你在游标打开的时候，对游标查询的数据表数据进行增删改，操作之后，静态游标中select的数据依旧显示的为没有操作之前的数据。如果想与操作之后的数据一致，则重新关闭打开游标即可。


动态游标:这个则与静态游标相对，滚动游标时，动态游标反应结果集中的所有更改。结果集中的行数据值、顺序和成员在每次提取时都会变化。所有用户做的增删改语句通过游标均可见。如果使用API函数或T-SQL Where Current of子句通过游标进行更新，他们将立即可见。在游标外部所做的更新直到提交时才可见。

 

只进游标：只进游标不支持滚动，只支持从头到尾顺序提取数据，数据库执行增删改，在提取时是可见的，但由于该游标只能进不能向后滚动，所以在行提取后对行做增删改是不可见的。
 

键集驱动游标：打开键集驱动游标时，该有表中的各个成员身份和顺序是固定的。打开游标时，结果集这些行数据被一组唯一标识符标识，被标识的列做删改时，用户滚动游标是可见的，如果没被标识的列增该，则不可见，比如insert一条数据，是不可见的，若可见，须关闭重新打开游标。

静态游标在滚动时检测不到表数据变化，但消耗的资源相对很少。动态游标在滚动时能检测到所有表数据变化，但消耗的资源却较多。键集驱动游标则处于他们中间，所以根据需求建立适合自己的游标，避免资源浪费。
关于游标更详细的讲解可参考：http://www.cnblogs.com/knowledgesea/p/3699851.html

 

 

相关推荐