澳门新萄京 8

存储过程,2008从入门到精通

1.锁

当多个用户同时对同一个数据进行修改时会产生并发问题,使用事务就可以解决这个问题。但是为了防止其他用户修改另一个还没完成的事务中的数据,就需要在事务中用到锁。
SQL Server
2008提供了多种锁模式:排他锁,共享锁,更新锁,意向锁,键范围锁,架构锁和大容量更新锁。
查询sys.dm_tran_locks视图可以快速了解SQL Server 2008内的加锁情况。

SELECT * FROM sys.dm_tran_locks;

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

存储过程优点

运行T-SQL语句进行编程有两种办法,一种是把T-SQL语句全部写在应用程序中,并存储在本地;另一种是把部分T-SQL语句编写的程序作为存储过程存储在SQL
Server中,只有本地的应用程序调用存储过程。大多数程序员偏向使用后者,原因在于存储过程具有以下优点:

  • 一次编译,多次执行。第一次执行某个过程时,将编译该过程以确定检索数据的最优访问计划。
    如果已经生成的计划仍保留在数据库引擎计划缓存中,则该过程随后执行的操作可能重新使用该计划。
  • 可在应用程序中多次调用;修改存储过程不会影响应用程序源代码。
  • 存储过程存储在服务中,能够减少网络流量。比如一个需要数百行T-SQL代码的操作可以通过一条执行存储过程代码的语句来调用,而不需要在网络中发送数百行代码。
  • 存储过程可被作为一种安全机制来充分利用。可以只授予用户执行存储过程的权限,而不授予用户直接访问存储过程中涉及的表的权限。这样,用户只能通过存储过程来访问表,并进行有限的操作,从而保证了表中数据的安全。使用授权操作设置各个用户的权限

2.游标

游标是类似于C语言指针一样的结构,是一种数据访问机制,允许用户访问单独的数据行。游标主要由游标结果集和游标位置组成。游标结果集是定义游标的SELECT语句返回行的集合,游标位置是指向这个结果集中某一行的指针。
示例1:用游标检索出student表中每行记录
Student表记录如图所示
澳门新萄京 1
执行下列语句

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--释放游标资源

结果如图所示
澳门新萄京 2

存储过程分类

(1)系统存储过程
  SQL
Server提供的存储过程,用于执行与系统相关的任务,主要存储在master数据库并以sp_为前缀,例如sp_addtype、sp_rename等。

澳门新萄京 3

(2)扩展存储过程
  扩展存储过程是以在SQL
Server环境之外执行的动态链接库(Dymatic-Link)Libraries,DDL)来实现的,执行系统存储过程不能胜任的任务,如发邮件、文件处理等,通常以前缀xp_开头。执行扩展存储过程的方式与存储过程的相似。

(3)临时存储过程
  临时存储过程首先是本地存储过程。SQL
Server支持两种临时存储过程:局部临时过程和全局临时过程。
  如果存储过程的前面有一个符号“#”,那么它就是局部临时过程,只能在一个用户会话中使用,在当前会话结束时就会被除去。
  如果存储过程的前面有两个符号“##”,那么把该存储过程称为全局临时存储过程,可以在所有用户会话中使用,在使用该过程的最后一个会话结束时除去。

(4)用户定义的存储过程
  用户自定义的存储过程由用户创建的一组T-SQL语句集合组成,可以接收和返回用户提供的参数,完成某些特定功能。
  存储过程创建好且语法正确后,系统将存储过程的名称存储在当前数据库的系统表sysobject中;将存储过程的文本存储在当前数据库的系统表syscomments中。

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

游标定义参数LOCAL表示该游标只能作用于本次批处理或函数或存储过程。游标定义参数GLOBAL表示该游标可以作用于全局。
执行下列语句

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

执行结果如下
澳门新萄京 4
语句中,声明了一个student表的游标stu_cursor,在打开游标时提示游标不存在。因为该游标参数是LOCAL,只能作用于当前批处理语句中,而打开游标语句和声明语句不在一个批处理中。如果去掉第一个GO,使两个语句在同一个批处理中,就能顺利执行不会报错。
执行下列语句

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

执行结果:命令已成功完成
和LOCAL参数对比,GOLBAL参数设置游标作用于全局,因此OPEN和DECLARE语句不在同一个批处理中依然可以成功执行。

创建存储过程

存储过程语法如下:

CREATE PROCEDURE|PROC [schema_name.] procedure_name
    -- Add the parameters for the stored procedure here
    [ { @parameter [ type_schema_name. ] data_type }  
        [ VARYING ] [ = default ] [ OUT | OUTPUT | [READONLY]  
    ] [ ,...n ]   
    [WITH <procedure_option> [ ,...n ]]
    [FOR REPLICATION]
AS { [ BEGIN ] sql_statement [;] [ ...n ] [ END ] }

<procedure_option> ::=   
    [ ENCRYPTION ]  
    [ RECOMPILE ]  
    [ EXECUTE AS Clause ]  
  • schema_name:该过程所属的架构的名称。如果在创建过程时未指定架构名称,则自动分配正在创建过程的用户的默认架构。
  • 可以通过使用一个#符号在procedure_name之前创建本地临时过程(#procedure_name)或两个#符号创建全局临时过程(##
    procedure_name)
    。局部临时程序仅对创建了它的连接可见,并且在关闭该连接后将被删除。
    全局临时程序可用于所有连接,并且在使用该过程的最后一个会话结束时将被删除。
  • @parameter:指定过程中的参数,是局部的,可以声明一个或多个。
  • 如果指定了FOR REPLICATION,则无法声明参数。
  • parameter可以是输入参数or输出参数,若为输入参数IN可以不写,系统默认;若为输出参数则要加上OUTPUT。
  • 表值参数只能是 INPUT 参数,并且这些参数必须带有 READONLY 关键字。
  • 光标数据类型只能是输出参数和必须附带由 VARYING 关键字。
  • OUT | OUTPUT指示参数是输出参数,使用 OUTPUT
    参数将值返回给过程的调用方。
  • [ =default ]:参数的默认值。
    如果默认定义值,该函数可以执行而无需指定该参数的值。
  • WITH ENCRYPTION:SQL Server加密syscomments表中包含CREATE
    PROCEDURE语句文本的条目,即对用户隐藏存储过程的文本,不能从syscomments表中获取该存储过程的信息。
  • WITH
    RECOMPILE:指示数据库引擎不缓存该过程的计划,该过程将在每次运行时重新编译。如果指定了FOR
    REPLICATION,则不能使用此选项。
  • EXECUTE AS子句:指定在其中执行过程的安全上下文。

关于参数

  • 存储过程参数也可以带有默认值,如:

create procedure pun_info @pubname varchar(20)='ALGOdata'
  • 存储过程参数可以带有通配符,如:

create procedure pun_info 
   @name varchar(20)='D%'
as
  select name from authors where name like @name

关于输出
①OUTPUT参数
  如果在过程定义中为参数指定 OUTPUT
关键字,则存储过程在退出时可将该参数的当前值返回至调用程序。若要用变量保存参数值以便在调用程序中使用,则调用程序必须在执行存储过程时使用
OUTPUT 关键字。
  也可以在执行过程时为 OUTPUT 参数指定输入值。
这将允许过程从调用程序接收值,使用该值更改或执行操作,然后将新值返回给调用程序。
②使用返回代码返回数据
  过程可以返回一个整数值(称为“返回代码”),以指示过程的执行状态。
使用 RETURN 语句指定过程的返回代码。 与 OUTPUT
参数一样,执行过程时必须将返回代码保存到变量中,才能在调用程序中使用返回代码值。
澳门新萄京 ,  RETURN是从查询或过程中无条件退出,不执行位于 RETURN
之后的语句。RETURN返回的不能是空值,如果过程试图返回空值,将生成警告信息并返回
0
值。用输出参数OUTPUT可以输出任意类型的结果(不包括表类型),而RETURN只能返回整型并且总能返回一个整型值。一般的RETURN用来返回返回代码(如0表示执行成功,1表示未指定所需参数值)。
  RETURN和OUTPUT还可以出现在同一存储过程中,详见示例(3)。

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,然后给该变量赋值。这两者是不同的。

局限与限制

①在单个批处理中,CREATE PROCEDURE 语句不能与其他 Transact-SQL
语句组合使用。
②以下语句不能用于存储过程主体中的任何地方。

澳门新萄京 5

③过程可以引用尚不存在的表。 在创建时,只进行语法检查。
直到第一次执行该过程时才对其进行编译。
只有在编译过程中才解析过程中引用的所有对象。
因此,如果语法正确的过程引用了不存在的表,则仍可以成功创建;但如果被引用的表不存在,则过程将在执行时将失败。
④不能将某一函数名称指定为参数默认值或者在执行过程时传递给参数的值。
但是,您可以将函数作为变量传递,如以下示例中所示:

-- Passing the function value as a variable.  
DECLARE @CheckDate datetime = GETDATE();  
EXEC dbo.uspGetWhereUsedProductID 819, @CheckDate;   

⑤如果该过程对 SQL Server 的远程实例进行更改,将无法回滚这些更改。
远程过程不参与事务。

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

执行存储过程

调用存储过程使用Execute|Exec关键字,不能省略。

Execute|Exec
{
  [@整形变量=]
  存储过程名[,n]|@存储过程变量名
  [[@过程参数=]参数值|@可变参数名 [OUTPUT]|[DEFAULT]]
  [,..,n]
  [WITH RECOMPILE]
}
  • @整形变量:可选,代表存储过程的返回状态。
  • n:可选,用于对同名的过程分组。
  • @过程参数:为存储过程的参数赋值。

SQL Server提供了两种传递参数的办法:
(1)按位置传递参数,即传递的参数和定义时的参数顺序一致,如:
execute au_info ‘Dull’,’Ann’
(2)通过参数名传递,采用“参数=值”的形式,此时各个参数可以任意排序,如:
execute au_info @firstName=’Dull’,@lastName=’Ann’ 或
execute au_info @lastName=’Ann’,@firstName=’Dull’

  • OUTPUT:指定该参数为输出参数。
  • DEFAULT:指明该参数使用默认值。如果该参数定义时没有指定默认值,则不能使用DEFAULT选项。
  • WITH RECOMPILE:强制在执行存储过程时重新对其进行编译。

【示例】
(1)带OUTPUT参数的存储过程——最后的返回值存储在调用程序声明的OUTPUT变量中

create procedure Query_Relationer
   @QueryCID int,                   -- 输入的形参
   @QueryRName varchar(20) OUTPUT   -- 输出的形参
as
begin
  if exists(select rid from Customer where cid = @QueryCID)
    select @QueryRName = RName from Relationer
    where rid = (select rid from Customer where cid = @QueryCID and cStatus = 1)
  else
    set @QueryRName = '不存在'
end
go

调用过程如下:

declare @Relationer_name varchar(20),@Cust_ID int
execute Query_Relationer @Cust_ID=20103530,@Relationer_name OUTPUT
print '客户ID为'+convert(char(8),@Cust_ID)+'的联系人是:'+@Relationer_name

(2)带Return参数的存储过程

create proc up_user
as
delcare @age int
begin
  select @age=uage from user
  return @age
end

(3)同时带Return和output参数的存储过程

create proc up_user
@id int,
@name varchar(20) output
as
 declare @age int
 begin
  select @age=stuage,@name=stuname from stuinfo where uid=@id
  return @age
 end

调用过程如下:

declare @age int
declare @name varchar(20)
exec @age=up_user 2,@name output
-- 输出age和name
select @age,@name

2.4.游标的简单应用

示例2:将student表中stu_enter_score大于600分的学生都减去100分
Student表中的数据如图所示
澳门新萄京 6
执行下列语句

--游标的简单应用
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

结果如图所示
澳门新萄京 7

存储过程传递集合参数以及返回、接收结果集

(1)传递集合参数

A、传递多个形参

B、使用表值参数
  使用表值参数类型将多个行插入表中。
一下示例将创建参数类型,声明表变量来引用它,填充参数列表,然后将值传递给存储过程。
存储过程使用这些值将多个行插入表中。

/* Create a table type. */  
CREATE TYPE LocationTableType AS TABLE   
( LocationName VARCHAR(50)  
, CostRate INT );  
GO  

/* Create a procedure to receive data for the table-valued parameter. */  
CREATE PROCEDURE usp_InsertProductionLocation  
    @TVP LocationTableType READONLY  
    AS   
    SET NOCOUNT ON  
    INSERT INTO [AdventureWorks2012].[Production].[Location]  
           ([Name]  
           ,[CostRate]  
           ,[Availability]  
           ,[ModifiedDate])  
        SELECT *, 0, GETDATE()  
        FROM  @TVP;  
GO  

/* Declare a variable that references the type. */  
DECLARE @LocationTVP   
AS LocationTableType;  

/* Add data to the table variable. */  
INSERT INTO @LocationTVP (LocationName, CostRate)  
    SELECT [Name], 0.00  
    FROM   
    [AdventureWorks2012].[Person].[StateProvince];  

/* Pass the table variable data to a stored procedure. */  
EXEC usp_InsertProductionLocation @LocationTVP;  
GO  

(2)返回结果集

A、使用 OUTPUT 游标参数
  以下示例使用 OUTPUT
游标参数将过程的局部游标传递回执行调用的批处理、过程或触发器。
  首先,创建在 Currency表上声明并打开一个游标的过程:

IF OBJECT_ID ( 'dbo.uspCurrencyCursor', 'P' ) IS NOT NULL  
    DROP PROCEDURE dbo.uspCurrencyCursor;  
GO  
CREATE PROCEDURE dbo.uspCurrencyCursor   
    @CurrencyCursor CURSOR VARYING OUTPUT  
AS  
    SET NOCOUNT ON;  
    SET @CurrencyCursor = CURSOR  
    FORWARD_ONLY STATIC FOR  
      SELECT CurrencyCode, Name  
      FROM Sales.Currency;  
    OPEN @CurrencyCursor;  
GO  

接下来,运行以下批处理:声明一个局部游标变量,执行上述过程以将游标赋值给局部变量,然后从该游标提取行。

DECLARE @MyCursor CURSOR;  
EXEC dbo.uspCurrencyCursor @CurrencyCursor = @MyCursor OUTPUT;  
WHILE (@@FETCH_STATUS = 0)  
BEGIN;  
     FETCH NEXT FROM @MyCursor;  
END;  
CLOSE @MyCursor;  
DEALLOCATE @MyCursor;  
GO  

B、使用OUTPUT返回多个输出参数
  这种方法缺点在于如果结果集中几百个元素,那么在存储过程就要声明几百个变量,十分麻烦。

CREATE PROCEDURE Student.singS
      @id int,
      @name varchar(20) OUTPUT,
      @age int OUTPUT
AS
   select name,age from Student where id=@id
GO

调用段:

DECLARE @name varchar(20),@age int
EXECUTE Student.singS 28, @name OUTPUT,@age OUTPUT
print '学生的姓名为:'+@name+',年龄为:'+@age

C、SELECT返回结果集
  在存储过程中写一段返回一个结果集的SELECT语句,如果在调用段中仅仅EXEC
procedure_name
[parameter1…parametern],那么该SELECT语句的结果仅仅只会输出到屏幕上,而不能用这个结果集做后续处理。如果要保存此结果集,只有一种方法,即通过使用
INSERT/EXEC
将其存储到永久表、临时表或表变量中,从而将结果流式处理到磁盘。

①把结果集存储在临时表
创建存储过程:

CREATE PROCEDURE Proc1
 @a varchar(50)
AS
 SELECT id,name FROM Table1 WHERE name=@a

调用段:

-- 创建一个临时表,和存储过程的结果集结构一致
CREATE TABLE #t1
(
  id int,
  name varchar(50)
)

-- 把结果集插入临时表中
INSERT INTO #t1 EXEC Proc1 'Ada'
-- do something with results
--用完之后要把临时表清空
DROP TABLE #t1

②把结果集存储在表变量
  但这种方法在查询的数据量较大的情况下比较影响性能,查询速度较慢,在数据量较小的情况下这种差异并不明显。

create proc proc1 as
   select col1 from dbo.table1;

create proc proc2 as
   declare @t table(col1 int);
   insert @t (col1) exec proc1;
   -- do something with results

3.存储过程

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

管理存储过程

①查看存储过程信息

澳门新萄京 8

②修改存储过程

ALTER PROCEDURE|PROC [schema_name.] procedure_name
    -- Add the parameters for the stored procedure here
    [ { @parameter [ type_schema_name. ] data_type }  
        [ VARYING ] [ = default ] [ OUT | OUTPUT | [READONLY]  
    ] [ ,...n ]   
    [WITH <procedure_option> [ ,...n ]]
    [FOR REPLICATION]
AS { [ BEGIN ] sql_statement [;] [ ...n ] [ END ] }

<procedure_option> ::=   
    [ ENCRYPTION ]  
    [ RECOMPILE ]  
    [ EXECUTE AS Clause ]

③删除存储过程

DROP { PROC | PROCEDURE } [ IF EXISTS ] { [ schema_name. ] procedure } [ ,...n ]  

3.1.用户定义的存储过程

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

3.2.扩展存储过程

扩展存储过程是指可以动态加载和运行的DLL,允许使用编程语言(如C语言)创建自己的外部例程。扩展存储过程直接在SQL
Server 2008的实例的地址空间中运行,可以使用SQL
Server扩展存储过程API完成编程。

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

3.3.2.限定存储过程内的名称

在存储过程内,如果用于语句的对象没有限定架构,则架构将默认为该存储过程的架构。如果创建该存储过程的用户没有限定INSERT,SELECT,UPDATE或DELETE语句中引用的表名或试图名,则默认情况下通过该存储过程进行的访问将受到该过程创建者权限的限制。如果有其他用户要使用存储过程,则所有用于数据定义语言(DDL)的语句(如CREATE,ALTER,EXECUTE,DROP,DBCC或动态SQL语句)的对象名应该用该对象架构的名称来限定。

发表评论

电子邮件地址不会被公开。 必填项已用*标注