《SQL Server 2008从入门到理解》–20180716

3.4.3.改动存储过程

ALTER
PROCEDURE
语句修改存储过程,只需将下面示例中的CREATE修改成ALTER运行就行了。

3.3.2.范围存储过程内的名号

在存储过程内,假设用于语句的对象没有限制架构,则架构将默认为该存储过程的架构。如果成立该存储过程的用户没有范围INSERT,SELECT,UPDATE或DELETE语句中援引的表名或试图名,则默认情况下通过该存储过程举行的拜访将面临该过程创制者权限的限定。假使有其他用户要动用存储过程,则兼具用于数据定义语言(DDL)的言辞(如CREATE,ALTER,EXECUTE,DROP,DBCC或动态SQL语句)的目标名应当用该对象架构的名号来限制。

3.4.1.创造存储过程

示例3:将示例2用存储过程实现
Student表的数码如图所示
ca88官网 1
执行下列语句

CREATE PROCEDURE alter_data
@a int--参数
AS
BEGIN
DECLARE stu_cursor CURSOR FORWARD_ONLY LOCAL
FOR SELECT stu_no,stu_enter_score FROM student
OPEN stu_cursor
DECLARE @score INT
DECLARE @stu_no VARCHAR(8)
FETCH NEXT FROM stu_cursor INTO @stu_no,@score--把游标值赋值给变量@score
WHILE @@FETCH_STATUS=0 
BEGIN
IF @score>=@a
BEGIN
UPDATE student
SET stu_enter_score=@score-100 WHERE stu_no=@stu_no 
END
FETCH NEXT FROM stu_cursor INTO @stu_no,@score--把游标值赋值给变量@score
END
CLOSE stu_cursor
DEALLOCATE stu_cursor
END
GO
EXEC dbo.alter_data '600'

结果如图所示
ca88官网 2

3.存储过程

仓储过程是一组用于完成一定效能的语句集,经过编译后存储在数据库中。在SQL
Server 2008中,既可以用T-SQL编写存储过程,也足以用CLR编写存储过程。

2.2.游标分为游标变量和游标类型

如下列语句

--语句1
DECLARE stu_cursor CURSOR
FOR SELECT * FROM student--声明student表的游标名称为stu_cursor并赋值
GO

--语句2
DECLARE @stu_cursor CURSOR--声明游标类型的变量@stu_cursor
SET @stu_cursor=CURSOR FOR--给该变量赋值
SELECT * FROM student

在语句1中一向阐明了一个游标并赋值,而语句2中扬言了游标类型的变量@stu_cursor,然后给该变量赋值。这两者是不同的。

3.2.扩大存储过程

扩充存储过程是指能够动态加载和运转的DLL,允许利用编程语言(如C语言)创设自己的外表例程。扩大存储过程平昔在SQL
Server 2008的实例的地点空间中运作,可以运用SQL
Server扩充存储过程API完成编程。

3.3.3.加密存储过程的定义

即使要创制存储过程并确保其他用户无法查看该存储过程的定义,则可以利用WITH
ENCRYPTION,这样,过程定义将以不足读的花样储存。

3.4.2.3.序列存储过程sp_depends查看存储过程有关消息

举行下列语句

EXEC sp_depends 'alter_data'

结果如图所示
ca88官网 3

3.3.4.SET语句选项

当成立或者更改T-SQL存储过程后,数据库引擎将保留SET
QUOTED_IDENTIFIER和SET
ANSI_NULLS的装置,执行存储过程时将使用这多少个本来设置而忽视任何客户端会话的ET
QUOTED_IDENTIFIER和SET
ANSI_NULLS设置。其他SET选项在成立或转移存储过程后不保留。

ca88官网,1.锁

当两个用户同时对同一个数额开展改动时会暴发并发问题,使用工作就可以化解那么些问题。不过为了以防万一其他用户修改另一个还没完成的事情中的数据,就需要在事情中用到锁。
SQL Server
2008提供了多种锁格局:排他锁,共享锁,更新锁,意向锁,键范围锁,架构锁和大容量更新锁。
查询sys.dm_tran_locks视图可以长足领悟SQL Server 2008内的加锁情状。

SELECT * FROM sys.dm_tran_locks;

注:关于锁的知识书中没细讲,将在事后的博客中补充。

2.4.游标的简短利用

示例2:将student表中stu_enter_score大于600分的学生都减去100分
Student表中的数据如图所示
ca88官网 4
进行下列语句

--游标的简单应用
DECLARE stu_cursor CURSOR FORWARD_ONLY LOCAL
FOR SELECT stu_no,stu_enter_score FROM student
OPEN stu_cursor
DECLARE @score INT
DECLARE @stu_no VARCHAR(8)
FETCH NEXT FROM stu_cursor INTO @stu_no,@score--把游标值赋值给变量@score
WHILE @@FETCH_STATUS=0 
BEGIN
IF @score>=600
BEGIN
UPDATE student
SET stu_enter_score=@score-100 WHERE stu_no=@stu_no 
END
FETCH NEXT FROM stu_cursor INTO @stu_no,@score--把游标值赋值给变量@score
END
CLOSE stu_cursor
DEALLOCATE stu_cursor
GO

结果如图所示
ca88官网 5

3.4.2.翻看存储过程

可以透过选用系统存储过程依然目录视图查看存储过程的定义

2.1.游标定义的参数LOCAL和GLOBAL

游标定义参数LOCAL表示该游标只好功效于此次批处理或函数或存储过程。游标定义参数GLOBAL表示该游标可以成效于大局。
实践下列语句

DECLARE stu_cursor CURSOR LOCAL
FOR SELECT * FROM student
GO
OPEN stu_cursor
GO

举行结果如下
ca88官网 6
语句中,注脚了一个student表的游标stu_cursor,在开拓游标时提示游标不存在。因为该游标参数是LOCAL,只好功用于当下批处理语句中,而开辟游标语句和表明语句不在一个批处理中。即使去掉第一个GO,使三个语句在同一个批处理中,就能无往不利执行不会报错。
实践下列语句

DECLARE stu_cursor1 CURSOR GLOBAL
FOR SELECT * FROM student
GO
OPEN stu_cursor1
GO

举办结果:命令已成功完成
和LOCAL参数相比较,GOLBAL参数设置游标功效于大局,因而OPEN和DECLARE语句不在同一个批处理中依然能够成功执行。

3.1.用户定义的储存过程

该种存储过程是指封装了可选择代码的模块或者经过,有2种档次:T-SQL存储过程和CLR存储过程。
T-SQL存储过程是指保存的T-SQL语句集合
CLR存储过程是指对Microsoft .NET Framework公共语言运行时(CLR)方法的引用

3.4.4.剔除存储过程

施行下列语句删除存储过程

DROP PROCEDURE alter_data

2.游标

游标是相近于C语言指针一样的社团,是一种多少访问机制,允许用户访问单独的数据行。游标首要由游标结果集和游标地点组成。游标结果集是概念游标的SELECT语句重返行的联谊,游标地点是指向这些结果集中某一行的指针。
示例1:用游标检索出student表中每行记录
Student表记录如图所示
ca88官网 7
执行下列语句

USE test
DECLARE stu_cursor CURSOR FOR
SELECT * FROM student--声明student表的游标stu
OPEN stu_cursor--打开游标
FETCH NEXT FROM stu_cursor--移动该记录指针
WHILE @@FETCH_STATUS=0--@@FETCH_STATUS用于保存FETCH操作的结束信息,=0表示有记录检索成功
BEGIN
FETCH NEXT FROM stu_cursor--游标指针移动到下一条记录
END
CLOSE stu_cursor--关闭游标
DEALLOCATE stu_cursor--释放游标资源

结果如图所示
ca88官网 8

3.4.2.4.目录视图查看存储过程

实践下列语句

SELECT * FROM sys.procedures

结果如图所示
ca88官网 9

3.4.2.1.图形化界面

如下图
ca88官网 10

3.4.2.2.连串存储过程sp_helptext查看存储过程定义

实施下列语句

EXEC sp_helptext 'alter_data'

结果如图所示
ca88官网 11

3.4.采纳存储过程

3.3.体系存储过程

系统存储过程是指储存在源数据库中,以sp起头的蕴藏过程,出现在每个系统定义数据库和用户定义数据库的sys架构中。

3.3.1.创造存储过程规则

在统筹和开创存储过程时,应该满足一定的牢笼和规则。

  • CREATE
    PROCEDURE定义自己可以包括擅自数量和类型的SQL语句,但下表中的语句除外。不可以在存储过程的别样地点应用那个言辞。
  • 可以引用在统一存储过程中开创的目标,只要引用时已开立了该目的
  • 可以在仓储过程内引用临时表
  • 假如在蕴藏过程中开创了地面临时表,该临时表仅为该存储过程而留存,退出该存储过程后,该临时表会消失
  • 一旦执行的囤积过程调用了另一个囤积过程,被调用的仓储过程可以访问首个存储过程的持有目标,包括临时表
  • 假设执行对长途SQL Server
    2008实例举办转移的长距离存储过程,这一个改动将不能够被回滚。远程存储过程不出席事务处理
  • 仓储过程中的参数的最大数额为2100
  • 储存过程中的局部变量的最大数目仅受可用内存的限量
  • 基于可用内存的不等,存储过程最大可达128MB
语句 语句 语句
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

2.3.游标参数FORWARD_ONLY和SCROLL

FORWARD_ONLY参数设置游标只好从结果集的初步向截至方向读取,使用FETCH语句时只好用NEXT,而SCROLL参数设置游标可以从结果集的随机方向,任意位置移动。如下列语句

--语句1,默认FORWARD_ONLY
DECLARE stu_cursor CURSOR LOCAL
FOR SELECT * FROM student
OPEN stu_cursor
FETCH NEXT FROM stu_cursor
GO
--语句2,FORWARD_ONLY参数,FETCH时只能从开始往结束方向
DECLARE stu_cursor CURSOR FORWARD_ONLY LOCAL
FOR SELECT * FROM student
OPEN stu_cursor 
FETCH NEXT FROM stu_cursor
GO
--语句3,SCROLL参数,FETCH时可以从任意位置往任意方向
DECLARE stu_cursor CURSOR SCROLL LOCAL
FOR SELECT * FROM student
OPEN stu_cursor
FETCH LAST FROM stu_cursor
GO

相关文章