《SQL Server 2008从入门到精通》–20180710

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代码的操作可以通过一条执行存储过程代码的语句来调用,而不需要在网络中发送数百行代码。
  • 存储过程可被作为一种安全机制来充分利用。可以只授予用户执行存储过程的权限,而不授予用户直接访问存储过程中涉及的表的权限。这样,用户只能通过存储过程来访问表,并进行有限的操作,从而保证了表中数据的安全。使用授权操作设置各个用户的权限

《Microsoft Sql server 2008 Internals》读书笔记订阅地址:

创建用户定义函数。这是一个已保存 Transact-SQL 或公共语言运行时 (CLR)
例程,该例程可返回一个值。用户定义函数不能用于执行修改数据库状态的操作。与系统函数一样,用户定义函数可从查询中调用。标量函数和存储过程一样,可使用
EXECUTE 语句执行。

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.使用Transact-SQL语言编程
    • 1.1.数据定义语言DDL
    • 1.2.数据操纵语言DML
    • 1.3.数据控制语言DCL
    • 1.4.Transact-SQL语言基础
  • 2.运算符
    • 2.1.算数运算符
    • 2.2.赋值运算符
    • 2.3.位运算符
    • 2.4.比较运算符
    • 2.5.逻辑运算符
    • 2.6.连接运算符
    • 2.7.一元运算符
    • 2.8.运算符的优先级
  • 3.控制语句
    • 3.1.BEGIN
      END语句块
    • 3.2.IF
      ELSE语句块
    • 3.3.CASE分支语句
    • 3.4.WHILE语句
    • 3.5.WAITFOR延迟语句
    • 3.6.RETURN无条件退出语句
    • 3.7.GOTO跳转语句
    • 3.8.TRY
      CATCH错误处理语句
  • 4.常用函数
    • 4.1.数据类型转换函数

存储过程分类

(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中。

用户定义函数可使用 ALTER
FUNCTION
修改,使用 DROP
FUNCTION
删除。

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语句不在同一个批处理中依然可以成功执行。

1.使用Transact-SQL语言编程

尽管SQL Server
2008提供了图形化界面,但只有一种Transact-SQL语言能够直接与数据库引擎进行交互。根据执行功能特点可以将Transact-SQL语言分成3大类:数据定义语言DDL,数据操纵语言DML,数据控制语言DCL。

创建存储过程

存储过程语法如下:

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)。

《Microsoft Sql server 2008 Internals》索引目录:

图片 5 Transact-SQL
语法约定

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

1.1.数据定义语言DDL

是最基础的Transact-SQL语言类型,用来创建数据库和创建,修改,删除数据库中的各种对象,为其他语言的操作提供对象。例如数据库,表,触发器,存储过程,视图,函数,索引,类型及用户等都是数据库中的对象。常见的DDL语句包括

CREATE TABLE--创建表
DROP TABLE--删除表
ALTER TABLE--修改表

局限与限制

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

图片 6

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

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

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

《Microsoft Sql server 2008
Internal》读书笔记–目录索引

图片 7语法

Scalar Functions
CREATE FUNCTION [ schema_name. ] function_name 
( [ { @parameter_name [ AS ][ type_schema_name. ] parameter_data_type 
    [ = default ] } 
    [ ,...n ]
  ]
)
RETURNS return_data_type
    [ WITH <function_option> [ ,...n ] ]
    [ AS ]
    BEGIN 
                function_body 
        RETURN scalar_expression
    END
[ ; ]

Inline Table-valued Functions
CREATE FUNCTION [ schema_name. ] function_name 
( [ { @parameter_name [ AS ] [ type_schema_name. ] parameter_data_type 
    [ = default ] } 
    [ ,...n ]
  ]
)
RETURNS TABLE
    [ WITH <function_option> [ ,...n ] ]
    [ AS ]
    RETURN [ ( ] select_stmt [ ) ]
[ ; ]

Multistatement Table-valued Functions
CREATE FUNCTION [ schema_name. ] function_name 
( [ { @parameter_name [ AS ] [ type_schema_name. ] parameter_data_type 
    [ = default ] } 
    [ ,...n ]
  ]
)
RETURNS @return_variable TABLE < table_type_definition >
    [ WITH <function_option> [ ,...n ] ]
    [ AS ]
    BEGIN 
                function_body 
        RETURN
    END
[ ; ]

CLR Functions
CREATE FUNCTION [ schema_name. ] function_name 
( { @parameter_name [AS] [ type_schema_name. ] parameter_data_type 
        [ = default ] } 
    [ ,...n ]
)
RETURNS { return_data_type | TABLE <clr_table_type_definition> }
    [ WITH <clr_function_option> [ ,...n ] ]
    [ AS ] EXTERNAL NAME <method_specifier>
[ ; ]

Method Specifier
<method_specifier>::=
    assembly_name.class_name.method_name

Function Options
<function_option>::= 
{
    [ ENCRYPTION ]
  | [ SCHEMABINDING ]
  | [ RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT ]
  | [ EXECUTE_AS_Clause ]
}

<clr_function_option>::=
}
    [ RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT ]
  | [ EXECUTE_AS_Clause ]
}

Table Type Definitions
<table_type_definition>:: = 
( { <column_definition> <column_constraint> 
  | <computed_column_definition> } 
        [ <table_constraint> ] [ ,...n ]
) 

<clr_table_type_definition>::= 
( { column_name data_type } [ ,...n ] )

<column_definition>::=
{
    { column_name data_type }
    [ [ DEFAULT constant_expression ] 
      [ COLLATE collation_name ] | [ ROWGUIDCOL ]
    ]
    | [ IDENTITY [ (seed , increment ) ] ]
    [ <column_constraint> [ ...n ] ] 
}
<column_constraint>::= 
{
    [ NULL | NOT NULL ] 
    { PRIMARY KEY | UNIQUE }
      [ CLUSTERED | NONCLUSTERED ] 
        [ WITH FILLFACTOR = fillfactor 
        | WITH ( < index_option > [ , ...n ] )
      [ ON { filegroup | "default" } ]
  | [ CHECK ( logical_expression ) ] [ ,...n ]
}

<computed_column_definition>::=
column_name AS computed_column_expression 

<table_constraint>::=
{ 
    { PRIMARY KEY | UNIQUE }
      [ CLUSTERED | NONCLUSTERED ] 
            ( column_name [ ASC | DESC ] [ ,...n ] )
        [ WITH FILLFACTOR = fillfactor 
        | WITH ( <index_option> [ , ...n ] )
  | [ CHECK ( logical_expression ) ] [ ,...n ]
}

<index_option>::=
{ 
    PAD_INDEX = { ON | OFF }   | FILLFACTOR = fillfactor 
  | IGNORE_DUP_KEY = { ON | OFF }
  | STATISTICS_NORECOMPUTE = { ON | OFF } 
  | ALLOW_ROW_LOCKS = { ON | OFF }
  | ALLOW_PAGE_LOCKS ={ ON | OFF } 
}

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

1.2.数据操纵语言DML

是用于操纵表和视图中的数据的语句,例如查询数据(SELECT),插入数据(INSERT),更新数据(UPDATE)和删除数据(DELETE)等。

执行存储过程

调用存储过程使用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

上文主要介绍计划缓存的时机和计划缓存冲突。本文将继续关注计划指南(Plan
Guide)和优化指示(Optimization
Hints),由于篇幅所限,计划指南分两部分,第一部分包括:优化提示、计划指南的意图、计划指南的类型。第二部分包括:计划指南的管理、计划指南的考虑因素、计划指南的有效性、从计划缓存中冻结计划。本文是第一部分。

图片 8备注

用户定义函数为标量值函数或表值函数。如果 RETURNS
子句指定了一种标量数据类型,则函数为标量值函数。可以使用多条
Transact-SQL 语句定义标量值函数。

如果 RETURNS 子句指定
TABLE,则函数为表值函数。根据函数主体的定义方式,表值函数可分为内联函数或多语句函数。有关详细信息,请参阅。

下列语句在函数内有效:

  • 赋值语句。

  • TRY…CATCH 语句以外的流控制语句。

  • 定义局部数据变量和局部游标的 DECLARE 语句。

  • SELECT 语句,其中的选择列表包含为局部变量分配值的表达式。

  • 游标操作,该操作引用在函数中声明、打开、关闭和释放的局部游标。只允许使用以
    INTO 子句向局部变量赋值的 FETCH 语句;不允许使用将数据返回到客户端的
    FETCH 语句。

  • 修改 table 局部变量的 INSERT、UPDATE 和 DELETE 语句。

  • 调用扩展存储过程的 EXECUTE 语句。

  • 有关详细信息,请参阅。

2.4.游标的简单应用

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

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

结果如图所示
图片 10

1.3.数据控制语言DCL

涉及到权限管理的语言称为数据控制语言,主要用于执行有关安全管理的操作。如授予权限(GRANT),收回权限(REVOKE),拒绝授予主体权限,并防止主体通过组或角色成员继承权限(DENY

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

(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

■优化提示(Optimization Hints)

嵌套用户定义函数

用户定义函数可以嵌套;也就是说,用户定义函数可相互调用。被调用函数开始执行时,嵌套级别将增加;被调用函数执行结束后,嵌套级别将减少。用户定义函数的嵌套级别最多可达
32 级。如果超出最大嵌套级别数,整个调用函数链将失败。

注意:
从 Transact-SQL 用户定义函数对托管代码的任何引用都将计入 32 级嵌套限制的一个级别。从托管代码内部调用的方法不根据此限制进行计数。

3.存储过程

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

1.4.Transact-SQL语言基础

管理存储过程

①查看存储过程信息

图片 11

②修改存储过程

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 ]  

ReCompile  
Recompile提示强制SQL
Server重新编译一个计划。这在一个批处理中仅仅一个语句时特别有用。SQL
Server编译T-SQL批处理为一个Unit,为批处理中的每个语句决定执行计划,直到整个批处理被编译之前,它不会执行任何语句。这意味着如果批处理中包含变量声明和赋值,但赋值在编译阶段并没有实际发生。当下列批处理被优化时,SQL
Server并没有为变量定义一个值:

函数属性

《SQL Server 2008从入门到精通》–20180710。在 SQL Server 的早期版本中,函数只能分为确定性函数和不确定性函数两类。在
SQL Server 2005
中,函数具有下列属性。这些属性的值确定了函数是否可用于持久化计算列或索引计算列。

3.1.用户定义的存储过程

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

1.4.1.常量与变量

常量不多说。在SQL Server
2008中,存在两种变量。一种是系统定义和维护的全局变量,一种是用户定义用来保存中间结果的局部变量。

USE Northwind2;
DECLARE @custID nchar(10);
SET @custID = 'LAZYK';
SELECT * FROM Orders WHERE CustomerID = @custID; 

属性 说明 注意

IsDeterministic

函数是确定性函数还是不确定性函数。

确定性函数中允许本地数据访问。例如,如果每次使用一组特定输入值和相同数据库状态调用函数时,函数都返回相同结果,则该函数将被标记为确定性函数。

IsPrecise

函数是精确函数还是不精确函数。

不精确函数包含浮点运算之类的运算。

IsSystemVerified

SQL Server 可验证函数的精度和确定性属性。

 

SystemDataAccess

函数可以访问 SQL Server 的本地实例中的系统数据(系统目录或虚拟系统表)。

 

UserDataAccess

函数可以访问 SQL Server 的本地实例中的用户数据。

包含用户定义表和临时表,但不包含表变量。

Transact-SQL 函数的精度和确定性属性由 SQL Server
自动确定。有关详细信息,请参阅。CLR
函数的数据访问权限和确定性属性可由用户指定。有关详细信息,请参阅 。

若要显示这些属性的当前值,请使用 OBJECTPROPERTYEX。

3.2.扩展存储过程

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

1.4.1.1.系统全局变量

系统全局变量分为两大类,一类是与当然SQL
Server连接或与当前处理有关的全局变量,如@@Rowcount表示最近一个语句影响的行数。@@error表示保存最近执行操作的错误状态。一类是与整个SQL
Server系统有关的全局变量,如@@Version表示当前SQL Server的版本信息。

SELECT @@VERSION AS 当前版本;--查看当前SQL Server的版本信息

结果如图所示
图片 12

这个针对select语句的计划将表明SQL
Server正在扫描完整的聚集索引。因为在优化期间,SQL
Server并不知道什么值将被搜索,不能使用索引统计的直方图得到一个好的行数的估算。如果我们用常量LAZYK取代变量,SQL
Server能决定仅仅有数行将被修饰,将有选择地使用CustomID上的非聚集索引。Recompile Hint此时非常有用,因为它告诉优化器在语句被执行前准备一个针对单个select语句的新计划。在Set选项后语句执行:

对调用用户定义函数的计算列进行索引

如果用户定义函数具有下列属性值,则可以在索引中使用调用用户定义函数的计算列:

  • IsDeterministic = true

  • IsSystemVerified = true(计算列是持久性计算列时除外)

  • UserDataAccess = false

  • SystemDataAccess = false

有关详细信息,请参阅。

3.3.系统存储过程

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

1.4.1.2.局部变量

局部变量能够拥有特定数据类型,有一定的作用域,一般用于充当计数器计算或控制循环执行次数,或者用于保存数据值。局部变量前只有1个@符,用DECLARE语句声明局部变量。

USE test
DECLARE @StudentId varchar(20)
SET @StudentId=(
SELECT Student.stu_no
FROM Student
WHERE stu_enter_score='603')
SELECT @StudentId AS 入学分数为603的学生学号
GO

结果如图所示
图片 13

USE Northwind2;  
DECLARE @custID nchar(10);
SET @custID = 'LAZYK';
SELECT * FROM Orders WHERE CustomerID = @custID
OPTION (RECOMPILE); 

从函数中调用扩展存储过程

如果在函数中调用扩展存储过程,则该过程不能向客户端返回结果集。向客户端返回结果集的任何
ODS API 都将返回 FAIL。扩展存储过程可以连接回 SQL Server
的实例;不过,该过程不应尝试与调用扩展存储过程的函数同时联接到同一事务。

与通过批处理或存储过程进行调用相似,扩展存储过程在运行 SQL Server 的
Windows 安全帐户的上下文中执行。存储过程的所有者在授予用户 EXECUTE
权限时应考虑这一点。

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.运算符

 

函数调用

可在使用标量表达式的位置调用标量值函数。这包括计算列和 CHECK
约束定义。也可以使用 EXECUTE
语句执行标量值函数。在允许表表达式的情况下,可在 SELECT、INSERT、UPDATE
或 DELETE 语句的 FROM 子句中调用表值函数。有关详细信息,请参阅。

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

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

2.1.算数运算符

在SQL Server
2008中,算数运算包括加(+)减(-)乘(*)除(/)取模(%)。举一个简单的例子。
示例1:在Student表中添加一列,列名为stu_age,根据Student表的stu_birthday列计算stu_age列并插入数据。(演示插入整列数据的方法)
Student表数据如图所示
图片 14
执行下面的语句

ALTER TABLE Student
ADD stu_age int;--在Student表中添加stu_age列
CREATE TABLE #agetemp(stu_no varchar(8),age int);--新建一个临时表
INSERT INTO #agetemp(stu_no,age)--在临时表中插入学号和计算出来的年龄
SELECT Student.stu_no,YEAR(GETDATE())-YEAR(stu_birthday)--利用函数和运算符计算年龄
FROM Student;
UPDATE Student
SET Student.stu_age=#agetemp.age--将临时表中的age列数据整个复制到Student表的stu_age列
FROM #agetemp
WHERE Student.stu_no=#agetemp.stu_no--条件是两个表的stu_no列值相等
GO
SELECT * FROM Student

结果如图所示
图片 15

注意:一个变量并不同于一个参数,即使他们用同样的方式被写。因为过程仅在被执行时编译。而SQL
Server总是使用一个定义的参数值,当前一个已编译计划使用不同的参数时问题出现了。然而,对于一个局部变量,当使用变量的语句被编译时,这个值是未知的,直到Recompile提示被使用。

在 CLR 函数中使用参数和返回值

如果在 CLR 函数中指定了参数,则这些参数应为 SQL Server 类型,即以前为
scalar_parameter_data_type 定义的类型。有关将 SQL Server
系统数据类型与 CLR 集成数据类型或 .NET Framework
公共语言运行时数据类型进行比较的信息,请参阅 。

为了使 SQL Server 在类中重载时引用正确方法,<method_specifier>
中指示的方法必须具有下列特征:

  • 接收 [ ,…n ] 中指定的参数数量。

  • 通过值而不是引用来接收所有参数。

  • 使用与 SQL Server 函数中指定的类型兼容的参数类型。

如果 CLR 函数的返回数据类型指定表类型 (RETURNS TABLE),则
<method_specifier> 中方法的返回数据类型应为 IEnumerator 或
IEnumerable 类型,且假定由函数创建者来实现接口。与 Transact-SQL
函数不同,CLR 函数不能在 <table_type_definition> 中包含 PRIMARY
KEY、UNIQUE 或 CHECK 约束。 <table_type_definition>
中指定的列的数据类型,必须与 <method_specifier>
中的方法在执行时返回的结果集中的对应列的类型匹配。创建函数时不执行上述类型检查。

有关对 CLR 函数编程的详细信息,请参阅 。

3.3.3.加密存储过程的定义

如果要创建存储过程并确保其他用户无法查看该存储过程的定义,则可以使用WITH
ENCRYPTION,这样,过程定义将以不可读的形式存储。

2.2.赋值运算符

即等号(=),将表达式的值赋予另一个变量。举一个简单的例子。
示例2:计算Student表中学生的平均入学成绩并打印。
Student表的数据如图所示,stu_enter_score列存放了学生的入学成绩
图片 16
执行下面的语句

DECLARE @average int--声明@average变量
SET @average=(--将计算出的平均值赋值给@average
SELECT AVG(stu_enter_score)
FROM Student)
PRINT @average--打印@average的值

结果如图所示
图片 17

Optimize For   Optimize
For提示告知优化器似乎一个特别的值被用作一个变量或参数。执行使用一个真实的值,记住,Optimize
For提示并不强制SQL Server被重编译,它仅仅指示SQL
Server假定一个变量或参数有一个特别的值,此时,SQL
Server已经决定查询需要优化。

不允许的 SQL 语句

下列 Service Broker 语句不能包含在 Transact-SQL 用户定义函数的定义中:

  • BEGIN DIALOG CONVERSATION

  • END CONVERSATION

  • GET CONVERSATION GROUP

  • MOVE CONVERSATION

  • RECEIVE

  • SEND

3.3.4.SET语句选项

当创建或者更改T-SQL存储过程后,数据库引擎将保存SET
QUOTED_IDENTIFIER和SET
ANSI_NULLS的设置,执行存储过程时将使用这些原始设置而忽略任何客户端会话的ET
QUOTED_IDENTIFIER和SET
ANSI_NULLS设置。其他SET选项在创建或更改存储过程后不保存。

2.3.位运算符

位运算符包括与运算(&),或运算(|)和异或运算(^),可以对两个表达式进行位操作,这两个表达式可以是整型数据或二进制数据。Transact-SQL首先把整型数据转换为二进制数据,然后按位运算。举个简单的例子。
示例3:声明2个int型变量@num1,@num2,对这两个赋值且做与或异或运算。
执行下面的语句

DECLARE @num1 int,@num2 int
SET @num1=5 
SET @num2=6
SELECT @num1&@num2 AS 与,
@num1|@num2 AS 或,
@num1^@num2 AS 异或

结果如图所示
图片 18
扩展示例4:写一个十进制转换为二进制的函数

CREATE FUNCTION Bin_con_dec(@dec int)--定义十进制转换为二进制函数
RETURNS varchar(20)
AS
BEGIN
DECLARE @quo int,@remainder varchar(20),@quo1 int
SET @quo=@dec
SET @remainder=''
WHILE @quo<>0
BEGIN
SET @quo1=@quo/2
SET @remainder=CAST(@quo%2 AS varchar(20))+@remainder
SET @quo=@quo1
END
RETURN @remainder
END

执行上面的函数后,运行下列语句验证函数正确性

PRINT dbo.Bin_con_dec(42)

结果为101010,函数定义正确。

Keep Plan   Keep
Plan提示放宽一个查询的重编译阈值,特别查询访问临时表时。我们在前面的章节中提到过,当访问一个临时表的查询而表的变化达到6个时,查询被重编译。如果使用KeepPlan,则临时表的重编译阈值等同于固定表。

查看有关函数的信息

若要显示 Transact-SQL 用户定义函数的定义,请使用函数所在数据库中的
sys.sql_modules
目录视图。

例如:

图片 19复制代码

USE AdventureWorks;
GO
SELECT Definition 
FROM sys.sql_modules AS m
JOIN sys.objects AS o ON m.object_id = o.object_id 
    AND TYPE IN ('FN', 'IF', 'TF');
GO
注意:
不能使用 sys.sql_modules 查看使用 ENCRYPTION 选项创建的函数定义;不过,可显示有关加密函数的其他信息。

若要显示有关 CLR 用户定义函数的信息,请使用函数所在数据库中的 sys.assembly_modules
目录视图。

若要显示有关用户定义函数中定义的参数的信息,请使用函数所在数据库中的
sys.parameters
目录视图。

若要显示有关函数引用的对象的报表,请使用 sys.sql_dependencies。

3.4.使用存储过程

2.4.比较运算符

也称关系运算符,用于比较两个值的关系,常见的有等于(=),大于(>),小于(<),大于等于(>=),小于等于(<=),不等于(<>或!=)
示例5:从Student表中查询入学成绩在平均分以上的学生信息
Student表的数据如图所示
图片 20
执行下列语句

DECLARE @ave int
SET @ave=(SELECT AVG(stu_enter_score) FROM Student)
SELECT *FROM Student
WHERE stu_enter_score>=@ave;

结果如下图所示
图片 21

注:不能直接把代码写成下面的形式

SELECT * FROM Student
WHERE stu_enter_score>=AVG(stu_enter_score)

消息147,级别15,状态1,第2 行
聚合不应出现在WHERE 子句中,除非该聚合位于HAVING
子句或选择列表所包含的子查询中,并且要对其进行聚合的列是外部引用。

因为AVG是聚合函数。

Keepfixed Plan Keepfixed
plan提示因为优化冲突而阻止所有的重编译。定义该提示,查询将仅仅在强制重编译或基础表的架构改变时重编译。

图片 22参数

schema_name
用户定义函数所属的架构的名称。

function_name
用户定义函数的名称。函数名称必须符合有关的规则,并且在数据库中以及对其架构来说是唯一的。

注意:
即使未指定参数,函数名称后也需要加上括号。

@parameter_name
用户定义函数的参数。可声明一个或多个参数。

函数最多可以有 1,024
个参数。执行函数时,如果未定义参数的默认值,则用户必须提供每个已声明参数的值。

通过将 at 符号 (@)
用作第一个字符来指定参数名称。参数名称必须符合有关标识符的规则。参数是对应于函数的局部参数;其他函数中可使用相同的参数名称。参数只能代替常量,而不能用于代替表名、列名或其他数据库对象的名称。

注意:
在存储过程或用户定义函数中传递参数时,或在批语句中声明和设置变量时,不会遵守 ANSI_WARNINGS。例如,如果将变量定义为 char(3) 类型,然后将其值设置为多于三个字符,则数据将截断为定义大小,并且 INSERT 或 UPDATE 语句可以成功执行。

[ type_schema_name. ] parameter_data_type
参数的数据类型及其所属的架构,后者为可选项。对于 Transact-SQL
函数,可以使用除 timestamp 数据类型之外的所有数据类型(包括 CLR
用户定义类型)。对于 CLR 函数,可以使用除 text、ntext、image 和
timestamp 数据类型之外的所有数据类型(包括 CLR
用户定义类型)。不能将非标量类型 cursor 和 table 指定为 Transact-SQL
函数或 CLR 函数中的参数数据类型。

如果未指定 type_schema_name,则 SQL Server 2005 Database
Engine 将按以下顺序查找 scalar_parameter_data_type:

  • 包含 SQL Server 系统数据类型名称的架构。

  • 当前数据库中当前用户的默认架构。

  • 当前数据库中的 dbo 架构。

[ = default ]
参数的默认值。如果定义了 default 值,则无需指定此参数的值即可执行函数。

注意:
可以为除 varchar(max) 和 varbinary(max) 数据类型之外的 CLR 函数指定默认参数值。

如果函数的参数有默认值,则该函数检索默认值时必须指定 DEFAULT
关键字。此行为与在存储过程中使用具有默认值的参数不同,在后一种情况下,不提供参数同样意味着使用默认值。

return_data_type
标量用户定义函数的返回值。对于 Transact-SQL 函数,可以使用除 timestamp
数据类型之外的所有数据类型(包括 CLR 用户定义类型)。对于 CLR
函数,可以使用除 text、ntext、image 和 timestamp
数据类型之外的所有数据类型(包括 CLR 用户定义类型)。不能将非标量类型
cursor 和 table 指定为 Transact-SQL 函数或 CLR 函数中的返回数据类型。

function_body
指定一系列定义函数值的 Transact-SQL
语句,这些语句在一起使用不会产生负面影响(例如修改表)。function_body
仅用于标量函数和多语句表值函数。

在标量函数中,function_body 是一系列 Transact-SQL
语句,这些语句一起使用的计算结果为标量值。

在多语句表值函数中,function_body 是一系列 Transact-SQL
语句,这些语句将填充 TABLE 返回变量。

scalar_expression
指定标量函数返回的标量值。

TABLE
指定表值函数的返回值为表。只有常量和 @local_variables
可以传递到表值函数。

在内联表值函数中,TABLE 返回值是通过单个 SELECT
语句定义的。内联函数没有关联的返回变量。

在多语句表值函数中,@return_variable 是 TABLE
变量,用于存储和汇总应作为函数值返回的行。只能将 @return_variable
指定用于 Transact-SQL 函数,而不能用于 CLR 函数。

select_stmt
定义内联表值函数的返回值的单个 SELECT 语句。

EXTERNAL NAME <method_specifier>, assembly_name.class_name.method_name
指定将程序集与函数绑定的方法。assembly_name 必须与 SQL Server
中当前数据库内具有可见性的现有程序集匹配。class_name 必须是有效的 SQL
Server
标识符,并且必须作为类存在于程序集中。如果类具有以命名空间限定的名称,该名称使用句点
(.) 来分隔命名空间的各部分,则必须使用方括号 ([ ]) 或引号 (” “)
分隔类名称。method_name 必须是有效的 SQL Server
标识符,并且必须作为静态方法存在于指定类中。

注意:
默认情况下,SQL Server 不能执行 CLR 代码。可以创建、修改和删除引用公共语言运行时模块的数据库对象;不过,只有在启用 之后,才能在 SQL Server 中执行这些引用。若要启用此选项,请使用 sp_configure

<table_type_definition>, ( { <column_definition> <column_constraint> ,   | <computed_column_definition> } ,   [ <table_constraint> ] [ ,…n ], ) ,
定义 Transact-SQL
函数的表数据类型。表声明包含列定义和列约束(或表约束)。表始终放在主文件组中。

< clr_table_type_definition > , ( { column_name data_type } [ ,…n ] ),
定义 CLR
函数的表数据类型。表声明仅包含列名称和数据类型。表始终放在主文件组中。

<function_option>::= and <clr_function_option>::=

指定函数将具有以下一个或多个选项:

ENCRYPTION
指示数据库引擎 对包含 CREATE FUNCTION 语句文本的目录视图列进行加密。使用
ENCRYPTION 可以防止将函数作为 SQL Server 复制的一部分发布。不能为 CLR
函数指定 ENCRYPTION。

SCHEMABINDING
指定将函数绑定到其引用的数据库对象。如果其他架构绑定对象也在引用该函数,此条件将防止对其进行更改。

只有发生下列操作之一时,才会删除函数与其引用对象的绑定:

  • 删除函数。

  • 在未指定 SCHEMABINDING 选项的情况下,使用 ALTER 语句修改函数。

只有满足以下条件时,函数才能绑定到架构:

  • 函数为 Transact-SQL 函数。

  • 该函数引用的用户定义函数和视图也绑定到架构。

  • 该函数引用的对象是用由两部分组成的名称引用的。

  • 该函数及其引用的对象属于同一数据库。

  • 执行 CREATE FUNCTION 语句的用户对该函数引用的数据库对象具有
    REFERENCES 权限。

不能为 CLR 函数或引用别名数据类型的函数指定 SCHEMABINDING。

RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT
指定标量值函数的 OnNULLCall 属性。如果未指定,则默认为 CALLED ON NULL
INPUT。这意味着即使传递的参数为 NULL,也将执行函数体。

如果在 CLR 函数中指定了 RETURNS NULL ON NULL INPUT,它指示当 SQL Server
接收到的任何一个参数为 NULL 时,它可以返回
NULL,而无需实际调用函数体。如果 <method_specifier> 中指定的 CLR
函数的方法已具有指示 RETURNS NULL ON NULL INPUT 的自定义属性,但 CREATE
FUNCTION 语句指示 CALLED ON NULL INPUT,则优先采用 CREATE FUNCTION
语句指示的属性。不能为 CLR 表值函数指定 OnNULLCall 属性。

EXECUTE AS 子句
指定用于执行用户定义函数的安全上下文。所以,您可以控制 SQL Server
使用哪一个用户帐户来验证针对该函数引用的任何数据库对象的权限。

注意:
不能为内联用户定义函数指定 EXECUTE AS。

有关详细信息,请参阅EXECUTE AS 子句
(Transact-SQL)。

< column_definition >::=

定义表数据类型。表声明包含列定义和约束。对于 CLR 函数,只能指定
column_name 和 data_type。

column_name
表中列的名称。列名称必须符合标识符规则,并且在表中必须是唯一的。column_name
可以由 1 至 128 个字符组成。

data_type
指定列数据类型。对于 Transact-SQL 函数,可以使用除 timestamp
之外的所有数据类型(包括 CLR 用户定义类型)。对于 CLR 函数,可以使用除
text、ntext、image、char、varchar、varchar(max) 和 timestamp
之外的所有数据类型(包括 CLR 用户定义类型)。在 Transact-SQL 或 CLR
函数中,非标量类型 cursor 不能指定为列数据类型。

DEFAULT constant_expression
指定当插入过程中没有显式提供值时为列提供的值。constant_expression
可以是常量、NULL 或系统函数值。DEFAULT 定义可以应用于除具有 IDENTITY
属性的列之外的任何列。不能为 CLR 表值函数指定 DEFAULT。

COLLATE collation_name
指定列的排序规则。如果未指定,则为此列分配数据库的默认排序规则。排序规则名称既可以是
Windows 排序规则名称,也可以是 SQL
排序规则名称。有关排序规则的列表及详细信息,请参阅 Windows 排序规则名称
(Transact-SQL)和
SQL 排序规则名称
(Transact-SQL)。

COLLATE 子句只能用来更改数据类型为 char、varchar、nchar 和 nvarchar
的列的排序规则。

不能为 CLR 表值函数指定 COLLATE。

ROWGUIDCOL
指示新列是行的全局唯一标识符列。对于每个表,只能将其中的一个
uniqueidentifier 列指定为 ROWGUIDCOL 列。ROWGUIDCOL 属性只能分配给
uniqueidentifier 列。

ROWGUIDCOL
属性并不强制实现列中存储的值的唯一性。该属性也不会为插入表的新行自动生成值。若要为每列生成唯一值,请在
INSERT 语句中使用 NEWID 函数。可以指定默认值;但是,不能将 NEWID
指定为默认值。

IDENTITY
指示新列是标识列。在为表添加新行时,SQL Server
将为该列提供唯一的增量值。标识列通常与 PRIMARY KEY
约束一起使用,作为表的唯一行标识符。可以将 IDENTITY 属性分配给
tinyint、smallint、int、bigint、decimal(p,0) 或 numeric(p,0)
列。每个表只能创建一个标识列。不能将绑定默认值和 DEFAULT
约束用于标识列。必须同时指定 seed 和
increment,或者二者都不指定。如果二者都未指定,则取默认值 (1,1)。

不能为 CLR 表值函数指定 IDENTITY。

seed
要分配给表中第一行的整数值。

increment
要加到表中后续行的 seed 值上的整数值。

< column_constraint >::= and < table_constraint>::=

为指定列或表定义约束。对于 CLR 函数,允许的唯一约束类型为
NULL。不允许命名约束。

NULL | NOT NULL
确定列中是否允许空值。严格讲来,NULL 不是约束,但可以像指定 NOT NULL
那样指定它。不能为 CLR 表值函数指定 NOT NULL。

PRIMARY KEY
一个约束,该约束通过唯一索引来强制指定列的实体完整性。在表值用户定义函数中,只能对每个表中的一列创建
PRIMARY KEY 约束。不能为 CLR 表值函数指定 PRIMARY KEY。

UNIQUE
一个约束,该约束通过唯一索引为一个或多个指定列提供实体完整性。一个表可以有多个
UNIQUE 约束。不能为 CLR 表值函数指定 UNIQUE。

CLUSTERED | NONCLUSTERED
指示为 PRIMARY KEY 或 UNIQUE 约束创建聚集索引还是非聚集索引。PRIMARY KEY
约束使用 CLUSTERED,而 UNIQUE 约束使用 NONCLUSTERED。

只能为一个约束指定 CLUSTERED。如果为 UNIQUE 约束指定了
CLUSTERED,并且指定了 PRIMARY KEY 约束,则 PRIMARY KEY 使用
NONCLUSTERED。

不能为 CLR 表值函数指定 CLUSTERED 和 NONCLUSTERED。

CHECK
一个约束,该约束通过限制可输入一列或多列中的可能值来强制实现域完整性。不能为
CLR 表值函数指定 CHECK 约束。

logical_expression
返回 TRUE 或 FALSE 的逻辑表达式。

<computed_column_definition>::=

指定计算列。有关计算列的详细信息,请参阅 CREATE
TABLE
(Transact-SQL)。

column_name
计算列的名称。

computed_column_expression
定义计算列的值的表达式。

<index_option>::=

为 PRIMARY KEY 或 UNIQUE
索引指定索引选项。有关索引选项的详细信息,请参阅 CREATE INDEX
(Transact-SQL)。

PAD_INDEX = { ON | OFF }
指定索引填充。默认值为 OFF。

FILLFACTOR = fillfactor
指定一个百分比,指示在创建或更改索引期间,数据库引擎 对各索引页的叶级填充的程度。fillfactor
必须为介于 1 至 100 之间的整数值。默认值为 0。

IGNORE_DUP_KEY = { ON | OFF }
指定当对唯一聚集索引或唯一非聚集索引的多行插入事务中出现重复键值时的错误响应。默认值为
OFF。

STATISTICS_NORECOMPUTE = { ON | OFF }
指定是否重新计算分布统计信息。默认值为 OFF。

ALLOW_ROW_LOCKS = { ON | OFF }
指定是否允许行锁。默认值为 ON。

ALLOW_PAGE_LOCKS = { ON | OFF }
指定是否允许页锁。默认值为 ON。

3.4.1.创建存储过程

示例3:将示例2用存储过程实现
Student表的数据如图所示
图片 23
执行下列语句

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'

结果如图所示
图片 24

2.5.逻辑运算符

逻辑运算符的作用是对条件进行测试。ALL,AND,ANY,BETWEEN,EXISTS,IN,LIKE,NOT,ALL,SOME。下面用SOME来举例。SOME的作用是如果在一组比较中,有些为true那就为true。
示例6:查询Student表中是否存在入学成绩高于平均分的学生,如果存在,输出true,不存在输出false。
Student表的stu_enter_score列(入学成绩)数据如图所示
图片 25
执行下面的语句

USE test
IF (SELECT AVG(stu_enter_score) FROM Student)<=SOME(SELECT stu_enter_score FROM Student)
PRINT 'true'
ELSE
PRINT 'false'
GO

结果如图所示
图片 26

Parameterization** **
Parameterization提示重定义了一个数据库的Parameterization选项。如果数据库被设置为Parameterization
Forced,使用Parameterization提示的独立查询能够避免它,并且适合严格的条件列表才会参数化。或者,如果数据库设置为Parameterization Simple,独立的查询能够被在一步一步的基础上被参数化。

图片 27权限

需要在数据库中具有 CREATE FUNCTION 权限,并对创建函数时所在的架构具有
ALTER 权限。如果函数指定用户定义类型,则需要对该类型具有 EXECUTE 权限。

3.4.2.查看存储过程

可以通过使用系统存储过程或者目录视图查看存储过程的定义

2.6.连接运算符

加号(+)是字符串连接运算符,可以用它把字符串串连起来,在示例4的十进制转二进制函数中,就用上了加号。
示例7:将Student表的stu_name列和stu_enter_score列放在同一列显示,列名为score
Student表的数据如图所示
图片 28
执行下列语句

SELECT stu_name+CAST(stu_enter_score AS VARCHAR(3)) AS score FROM Student

执行结果如图所示
图片 29

注:stu_enter_score列数据类型为int,加号只对字符串类型数据有效,因此要用CAST函数将stu_enter_score的数据类型转换为varchar(3),这样才能实现字符串拼接。

Use Plan   use
Plan提示,在第八章中讨论过,作为一种强制SQL
Server使用一个可能定义其他提示的计划的方式。定义的计划必须是XML格式,并能被从一个通过使用SET
SHOWPLAN_XML ON选项的合适计划的查询中获取。因为USE
Plan提示在查询提示中包括一个复杂的XML文档,它们是使用计划向导的最佳实践。

图片 30示例

3.4.2.1.图形化界面

如下图
图片 31

2.7.一元运算符

一元运算符只对一个表达式执行操作,该表达式可以是数字数据类型中的任何一种数据类型。SQL
Server 2008提供的一元运算符包含正(+),负(-),位反(~)。
示例8:声明一个int数据类型变量@num并赋值,对该变量做正负位反操作。
执行下列语句

DECLARE @num INT
SET @num=45
SELECT +@num AS 正,-@num AS 负,~@num AS 位反
GO

结果如图所示
图片 32

注:位反操作符用于取一个数的补数,只能用于整数。

■计划指南的意图

A. 使用计算 ISO 周的标量值用户定义函数

以下示例将创建用户定义函数 ISOweek。此函数使用日期参数来计算 ISO
周数。要使此函数能正确计算,必须在调用该函数前调用 SET DATEFIRST 1

另外,该示例将显示如何使用 EXECUTE AS
子句指定可在其中执行存储过程的安全上下文。在该示例中,CALLER
选项指定该过程将在调用该过程的用户的上下文中执行。您还可以指定
SELF、OWNER 和 user_name 等其他选项。有关详细信息,请参阅EXECUTE AS 子句
(Transact-SQL)。

图片 33复制代码

USE AdventureWorks;
GO
IF OBJECT_ID (N'dbo.ISOweek', N'FN') IS NOT NULL
    DROP FUNCTION dbo.ISOweek;
GO
CREATE FUNCTION dbo.ISOweek (@DATE datetime)
RETURNS int
WITH EXECUTE AS CALLER
AS
BEGIN
     DECLARE @ISOweek int
     SET @ISOweek= DATEPART(wk,@DATE)+1
          -DATEPART(wk,CAST(DATEPART(yy,@DATE) as CHAR(4))+'0104')
--Special cases: Jan 1-3 may belong to the previous year
     IF (@ISOweek=0) 
          SET @ISOweek=dbo.ISOweek(CAST(DATEPART(yy,@DATE)-1 
               AS CHAR(4))+'12'+ CAST(24+DATEPART(DAY,@DATE) AS CHAR(2)))+1
--Special case: Dec 29-31 may belong to the next year
     IF ((DATEPART(mm,@DATE)=12) AND 
          ((DATEPART(dd,@DATE)-DATEPART(dw,@DATE))>= 28))
          SET @ISOweek=1
     RETURN(@ISOweek)
END;
GO

下面是函数调用。请注意,DATEFIRST 设置为 1

图片 34复制代码

SET DATEFIRST 1;
SELECT dbo.ISOweek(CONVERT(DATETIME,'12/26/2004',101)) AS 'ISO Week';

下面是结果集: 

图片 35复制代码

ISO Week
----------------
52
3.4.2.2.系统存储过程sp_helptext查看存储过程定义

执行下列语句

EXEC sp_helptext 'alter_data'

结果如图所示
图片 36

2.8.运算符的优先级

优先级 运算符
1 ~(位反)
2 *(乘),/(除),%(取模)
3 +(正),-(负),+(加),+(连接),-(减),&(位与)
4 =,>,<,>=,<=,<>,!=,!>,!<(比较运算符)
5 ^(位异或),位或(符号打不出来,前面有,自己翻)
6 NOT
7 AND
8 ALL,ANY,BETWEEN,IN,LIKE,ALL,SOME
9 =(赋值)

当表达式中的运算符有相同的优先级时,按照它们在表达式中的位置,一元运算符按从右往左运算,二元运算符(对两个表达式作用的运算符)按从左往右运算。
示例9:验证运算符优先级
执行下列语句

DECLARE @result INT,@num INT
SET @num=45
SET @result=@num+(~@num)*4-@num/(~@num)
SELECT @result AS result
GO

结果如图所示
图片 37
计算代码中的表达式
@result=@num+(~@num)4-@num/(~@num)
=@num+(-46)
4-@num/(-46)
=45+(-46)4-45/(-46)
=45+(-46)
4
=-139

 尽管在大多数情况下推荐你允许查询优化器来决定每个查询的最佳计划,但有时查询优化器并不能得出最稳定性计划,你可能需要一个方式使用Hint获取合理的性能。

B. 创建内联表值函数

以下示例将返回内联表值函数。对于销售给商店的每个产品,该函数返回三列,分别为
ProductIDName 以及各个商店年初至今总数的累计 YTD Total

图片 38复制代码

USE AdventureWorks;
GO
IF OBJECT_ID (N'Sales.fn_SalesByStore', N'IF') IS NOT NULL
    DROP FUNCTION Sales.fn_SalesByStore;
GO
CREATE FUNCTION Sales.fn_SalesByStore (@storeid int)
RETURNS TABLE
AS
RETURN 
(
    SELECT P.ProductID, P.Name, SUM(SD.LineTotal) AS 'YTD Total'
    FROM Production.Product AS P 
      JOIN Sales.SalesOrderDetail AS SD ON SD.ProductID = P.ProductID
      JOIN Sales.SalesOrderHeader AS SH ON SH.SalesOrderID = SD.SalesOrderID
    WHERE SH.CustomerID = @storeid
    GROUP BY P.ProductID, P.Name
);
GO

若要调用该函数,请运行此查询。

图片 39复制代码

SELECT * FROM Sales.fn_SalesByStore (602);
3.4.2.3.系统存储过程sp_depends查看存储过程相关信息

执行下列语句

EXEC sp_depends 'alter_data'

结果如图所示
图片 40

3.控制语句

计划指南,在SQL
Server2005中新增的功能,提出一种解决方案,可以给你一个机制仅增加Hint到查询而不需改变查询本身。首要地,一个计划向导告诉优化器,如果它试图优化一个特殊格式的查询,它应该增加一个Hint到查询。SQL
Server支持三种类型的计划指南:SQL,Object和templete。

C. 创建多语句表值函数

以下示例创建了表值函数 fn_FindReports(InEmpID)。如果提供一个有效雇员
ID,该函数将返回一个表,该表对应于直接或间接向该雇员报告的所有雇员。该函数使用递归公用表表达式
(CTE) 来生成雇员的层次结构列表。有关递归 CTE 的详细信息,请参阅 WITH common_table_expression
(Transact-SQL)。

图片 41复制代码

USE AdventureWorks;
GO
IF OBJECT_ID (N'dbo.fn_FindReports', N'TF') IS NOT NULL
    DROP FUNCTION dbo.fn_FindReports;
GO
CREATE FUNCTION dbo.fn_FindReports (@InEmpID INTEGER)
RETURNS @retFindReports TABLE 
(
    EmployeeID int primary key NOT NULL,
    Name nvarchar(255) NOT NULL,
    Title nvarchar(50) NOT NULL,
    EmployeeLevel int NOT NULL,
    Sort nvarchar (255) NOT NULL
)
--Returns a result set that lists all the employees who report to the 
--specific employee directly or indirectly.*/
AS
BEGIN
   WITH DirectReports(Name, Title, EmployeeID, EmployeeLevel, Sort) AS
    (SELECT CONVERT(Varchar(255), c.FirstName + ' ' + c.LastName),
        e.Title,
        e.EmployeeID,
        1,
        CONVERT(Varchar(255), c.FirstName + ' ' + c.LastName)
     FROM HumanResources.Employee AS e
          JOIN Person.Contact AS c ON e.ContactID = c.ContactID 
     WHERE e.EmployeeID = @InEmpID
   UNION ALL
     SELECT CONVERT(Varchar(255), REPLICATE ('| ' , EmployeeLevel) +
        c.FirstName + ' ' + c.LastName),
        e.Title,
        e.EmployeeID,
        EmployeeLevel + 1,
        CONVERT (Varchar(255), RTRIM(Sort) + '| ' + FirstName + ' ' + 
                 LastName)
     FROM HumanResources.Employee as e
          JOIN Person.Contact AS c ON e.ContactID = c.ContactID
          JOIN DirectReports AS d ON e.ManagerID = d.EmployeeID
    )
-- copy the required columns to the result of the function 
   INSERT @retFindReports
   SELECT EmployeeID, Name, Title, EmployeeLevel, Sort
   FROM DirectReports 
   RETURN
END;
GO
-- Example invocation
SELECT EmployeeID, Name, Title, EmployeeLevel
FROM dbo.fn_FindReports(109)
ORDER BY Sort;
3.4.2.4.目录视图查看存储过程

执行下列语句

SELECT * FROM sys.procedures

结果如图所示
图片 42

3.1.BEGIN END语句块

BEGIN END可以定义SQL
Server语句块,使这些语句作为一组语句执行,允许语句嵌套。举例请见示例4

计划指南在标准版。企业版、评估版、和开发版中可用。如果从一个支持的SQL
Server版本中detach一个包含计划指南的数据库,然后再附加玛个不支持的版本,如workgroup版本或Express版本中。SQL
Server不使用任何计划指南。包含计划指南信息的元数据仍然可用。

D. 创建 CLR 函数

以下示例假定在本地计算机的默认位置安装了 ,并且已编译了
StringManipulate.csproj 示例应用程序。有关详细信息,请参阅 。

该示例将创建 CLR 函数 len_s。在创建该函数之前,程序集
SurrogateStringFunction.dll 已在本地数据库中注册。

图片 43复制代码

DECLARE @SamplesPath nvarchar(1024);
-- You may have to modify the value of the this variable if you have
--installed the sample someplace other than the default location.
SELECT @SamplesPath = REPLACE(physical_name, 'Microsoft SQL Server/MSSQL.1/MSSQL/DATA/master.mdf', 'Microsoft SQL Server/90/Samples/Engine/Programmability/CLR/') 
    FROM master.sys.database_files 
    WHERE name = 'master';

CREATE ASSEMBLY [SurrogateStringFunction]
FROM @SamplesPath + 'StringManipulate/CS/StringManipulate/bin/debug/SurrogateStringFunction.dll'
WITH PERMISSION_SET = EXTERNAL_ACCESS;
GO

CREATE FUNCTION [dbo].[len_s] (@str nvarchar(4000))
RETURNS bigint
AS EXTERNAL NAME [SurrogateStringFunction].[Microsoft.Samples.SqlServer.SurrogateStringFunction].[LenS];
GO

3.4.3.修改存储过程

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

3.2.IF ELSE语句块

用于指定T-SQL语句的执行条件,若条件为真,则执行条件表达式后面的语句,条件为假时,可以试用ELSE关键字指定要执行的T-SQL语句。举例请见示例4

■计划指南的类型

3.4.4.删除存储过程

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

DROP PROCEDURE alter_data

3.3.CASE分支语句

示例10:将Student表的学生,性别和籍贯打印出来,要求籍贯只能显示省内,省外或自治区。
Student表的数据如图所示
图片 44
执行下列语句

SELECT stu_name AS 姓名,stu_sex AS 性别,
(CASE stu_native_place
WHEN '浙江' THEN '省内'
WHEN '内蒙古' THEN '自治区'
WHEN '西藏' THEN '自治区'
WHEN '宁夏' THEN '自治区'
WHEN '新疆' THEN '自治区'
WHEN '广西' THEN '自治区'
ELSE '省外'
END) AS 籍贯 
FROM Student

结果如图所示
图片 45

可以使用sp_Create_plan_guide存储过程创建三种类型的计划。关于sp_create_plan_guide的用法,参看MSDN:

3.4.WHILE语句

用于设置重复执行T-SQL语句或语句块的条件。
示例11:用“*”在屏幕上输出一个宽度为9的菱形。
执行下列语句

DECLARE @width int,@j int
SET @width=9--@width为菱形的最大宽度
SET @j=1--@j表示每行打印的“*”符号的个数
WHILE @j<=@width
BEGIN
PRINT SPACE((@width-@j)/2)+REPLICATE('*',@j)--SPACE函数打印n个空字符,REPLICATE打印n个特定字符串
SET @j=@j+2
END
SET @j=@width-2
WHILE @j>0
BEGIN
PRINT SPACE((@width-@j)/2)+REPLICATE('*',@j)
SET @j=@j-2
END

结果如图所示
图片 46

3.5.WAITFOR延迟语句

WAITFOR延迟语句可以让在它之后的语句在一个指定的时刻或是时间间隔后执行,可以悬挂起批处理,存储过程或事务的执行。
示例12:在某个时间点查询Student表学号为20180101的学生信息

BEGIN
WAITFOR TIME '15:03'--在15点03分查询
SELECT * FROM Student
WHERE stu_no='20180101'
END

示例13:在3分钟后查询Student表学号为20180102的学生信息

BEGIN
WAITFOR DELAY '00:03'--在3分钟后查询
SELECT * FROM Student
WHERE stu_no='20180102'
END

图片 47

通用的格式如下:(注意顺序不能变化)
sp_Create_plan_guide
‘plan_guide_name’,’statement_text’,’type_Of_plan_guide’,’object_name_or_batch_text’,’parameter_list’,’hints’

3.6.RETURN无条件退出语句

该语句表示无条件终止查询,批处理或存储过程的执行。存储过程和批处理RETURN语句后面的语句都不再执行。当在存储过程中使用该语句时,可以指定返回给调用应用程序、批处理或过程的整数值。如果RETURN未指定返回值,则存储过程的返回值是0

对象计划指南

3.7.GOTO跳转语句

该语句使T-SQL批处理的执行跳转至指定标签。由于该语句破坏结构化语句的结构,尽量少用
示例13:将GOTO作为分支机制
执行下面语句

DECLARE @Counter int;  
SET @Counter = 1;  
WHILE @Counter < 10  
BEGIN   
    SELECT @Counter  
    SET @Counter = @Counter + 1  
    IF @Counter = 4 GOTO Branch_One --Jumps to the first branch.  
    IF @Counter = 5 GOTO Branch_Two  --This will never execute.  
END  
Branch_One:  
    SELECT 'Jumping To Branch One.'  
    GOTO Branch_Three; --This will prevent Branch_Two from executing.  
Branch_Two:  
    SELECT 'Jumping To Branch Two.'  
Branch_Three:  
SELECT 'Jumping To Branch Three.';

结果如图所示
图片 48
当Counter=4时,执行GOTO语句输出Branch
One,执行完这个语句之后就打破了WHILE循环,接着执行Branch_One语句中的GOTO,输出Branch
Three,结束。

注:在WHILE循环中使用GOTO会打破循环。

示例14:用GOTO语句实现示例11中打印菱形的功能
执行下列语句

DECLARE @width int,@j int,@i int
SET @width=9--@width为菱形的最大宽度
SET @j=1--@j表示每行打印的“*”符号的个数
SET @i=1--@i表示下一行打印第i行
Set3:PRINT SPACE((@width-@j)/2)+REPLICATE('*',@j)--SPACE函数打印n个空字符,REPLICATE打印n个特定字符串
SET @i=@i+1
IF @i<=(@width+1)/2
GOTO Set1
ELSE
GOTO Set2
Set1:
SET @j=@j+2
GOTO Set3
Set2:
SET @j=@j-2
IF @j>=1
GOTO Set3

结果如图所示
图片 49

一个object类型的计划指南,标示你对在出现在SQL
Server对象的上下文的T-SQL感兴趣,可能是创建计划指南的数据库中的存储过程、用户自定义函数、或触发器。举例,我们假定一个存储过程Sales.GetOrderByCountry,使用country作为一个参数。在检查错误和其他有效性后,它返回一个结果集。更进一步,假定我们的测试已经决定给定的参数值”US”以最佳计划。这里使用Optimize
for提示,以下示例创建一个计划指南,它与在基于应用程序的存储过程的上下文中所执行的查询匹配,并将
OPTIMIZE FOR 提示应用于该查询。

3.8.TRY CATCH错误处理语句

如果TRY块内部发生错误,会将控制传递给CATCH块内的语句组。TRY
CATCH构造捕捉所有严重级别大于10但不会终止数据库连接的错误。
示例15:TRY CATCH的示例
执行下列语句

BEGIN TRY
SELECT * FROM Student
SELECT 120/0 FROM Student
END TRY
BEGIN CATCH
SELECT ERROR_MESSAGE() AS '错误信息'
END CATCH

执行结果如图所示
图片 50
语句中3个select语句全部都执行了。如果把报错的select语句放到正常的select语句前面,正常的select语句还能不能执行呢?执行下列语句

BEGIN TRY
SELECT 120/0 FROM Student
SELECT * FROM Student
END TRY
BEGIN CATCH
SELECT ERROR_MESSAGE() AS '错误信息'
END CATCH

结果如图所示
图片 51
正常select语句无法执行。TRY
CATCH语句的逻辑是,一旦TRY语句块中出现问题语句,立刻跳转到CATCH语句块,TRY语句块接下去的语句不再执行。

下面是此存储过程:

4.常用函数

IF OBJECT_ID(N'Sales.GetSalesOrderByCountry', N'P') IS NOT NULL
    DROP PROCEDURE Sales.GetSalesOrderByCountry;
GO
CREATE PROCEDURE Sales.GetSalesOrderByCountry
    (@Country_region nvarchar(60))
AS
BEGIN
    SELECT *
    FROM Sales.SalesOrderHeader AS h
    INNER JOIN Sales.Customer AS c ON h.CustomerID = c.CustomerID
    INNER JOIN Sales.SalesTerritory AS t
        ON c.TerritoryID = t.TerritoryID
    WHERE t.CountryRegionCode = @Country_region;
END 

4.1.数据类型转换函数

默认情况下SQL
Server会对一些数据类型进行自动转换,这种转换称为隐式转换。遇到无法自动转换,则需要用CAST()函数和CONVERT()函数转换,这种转换称为显式转换。CAST()函数和CONVERT()函数的功能是相同的,CAST函数更容易使用,CONVERT函数的优点是可以指定日期和数值格式。
示例16:将Student表中的学号转换为日期格式
下面两句语句的功能是一样的,执行下列语句

SELECT stu_name,CAST(stu_no AS DATE) AS 学号转换成日期,stu_enter_score,stu_birthday FROM Student
SELECT stu_name,CONVERT(DATE,stu_no) AS 学号转换成日期,stu_enter_score,stu_birthday FROM Student

结果如图所示
图片 52
示例17:用CONVERT()函数将stu_birthday转化成指定格式的日期
执行下列语句

SELECT stu_name,CONVERT(VARCHAR(20),stu_birthday,101) FROM Student
--CONVERT函数将DATE类型的stu_birthday字段转化为字符串,并限定了样式,代码101

结果如图所示
图片 53

注:在上述代码中,CONVERT(DATE,stu_birthday,101)这么写是没用的。101格式码只对日期格式转化为字符串有效,其他格式转化为日期格式是无效的。

其他常用函数太简单了这边不写了,略。

 

下面是为此存储过程中的查询所创建的计划指南:

EXEC sp_create_plan_guide
    @name =  N'Guide1',
    @stmt = N'SELECT *
              FROM Sales.SalesOrderHeader AS h
              INNER JOIN Sales.Customer AS c
                 ON h.CustomerID = c.CustomerID
              INNER JOIN Sales.SalesTerritory AS t
                 ON c.TerritoryID = t.TerritoryID
              WHERE t.CountryRegionCode = @Country_region',
    @type = N'OBJECT',
    @module_or_batch = N'Sales.GetSalesOrderByCountry',
    @params = NULL,
    @hints = N'OPTION (OPTIMIZE FOR (@Country_region = N''US''))';

 

一旦该计划在AdventureWorks2008数据库中被创建,每次存储过程”Sales.GetSalesOrderByCountry”被编译时,计划里揭示的语句如果实际参数值为’US’时被优化,这个存储过程中的其他语句不受此计划影响,如果定义的查询发生在Sales.GetSalesOrderByCountry外,计划指南不会调用。

SQL计划指南 

一个SQL类型的计划指南显示你关注特别的SQL语句,如一个独立的语句或一个特别的批处理。被送到SQL
Server的T-SQL语句被通过CLR对象或扩展存储过程,或EXEC调用的其他动态SQL语句结构,而作为批处理运行。为了在计划指南中使用它们,它们的类型被设置为SQL。

对于一个单独的SQL语句,@module_or_beach参数应该被设置为NULL,以便SQL
Server假定批处理和语句有同样的值,

如果是一个大的批处理,完整的批处理文本需要在@module_or_beach参数中定义。如果一个批处理被定义为一个SQL指南、这个文本需要和应用程序中的完全一样。这在前面章节已以讨论。这个规则并不与adhoc查询计划重用一样严格,但它们很接近。

这里有一个例子,指示SQL
Server在一个特殊的查询被作为一个单独的查询时使用仅仅一个CPU(没有并行):

EXEC sp_create_plan_guide
@name = N'plan_SalesOrderHeader_DOP1',
@stmt = N'SELECT TOP 10 *
      FROM Sales.SalesOrderHeader
      ORDER BY OrderDate DESC',
@type = N'SQL',
@module_or_batch = NULL,
@params = NULL,
@hints = N'OPTION (MAXDOP 1)';

 

一旦该查询指南被创建,每次该定义的语句在批处理中被碰到时,它有一个计划仅仅使用一个CPU。如果发生在一个在的批处理时,计划指南不调用。

模板计划指南

一个Templete类型的计划指南,仅仅使用Parameterization Forced或**ParameterizationSimple提示去重定义数据库的Parameterization**数据库设置。有一个存储过程sp_get_query_template用来生成模板。sp_get_query_template的用法,请看MSDN:
来看一个使用模板指南和强制参数化的例子,首先清除你的过程缓存,然后执行这两个查询:

DBCC FREEPROCCACHE;
GO
SELECT * FROM AdventureWorks.Sales.SalesOrderHeader AS h
INNER JOIN AdventureWorks.Sales.SalesOrderDetail AS d
  ON h.SalesOrderID = d.SalesOrderID
WHERE h.SalesOrderID = 45639;
GO
SELECT * FROM AdventureWorks.Sales.SalesOrderHeader AS h
INNER JOIN AdventureWorks.Sales.SalesOrderDetail AS d
  ON h.SalesOrderID = d.SalesOrderID
WHERE h.SalesOrderID = 45640; 

 

 用前面介绍的一个视图sp_cacheobjects查看,也可以使用sys.syscacheobjects():

SELECT objtype, dbid, usecounts, sql
FROM sp_cacheobjects
WHERE cacheobjtype = 'Compiled Plan';

SELECT objtype, dbid, usecounts, sql
FROM sys.syscacheobjects
WHERE cacheobjtype = 'Compiled Plan';

 

此时因为查询被看作复杂化,SQL Server并没有自动参数化。查询结果为null

 为了创建一个强制这种类型的语句参数化。我们首先需要调用存储过程sp_get_query_template(),传递两上变量作为输出参数。一个参数包含查询的参数化版本,另一个包含参数列表和数据类型。我们试着强制参数化(注意与上面查询比较):

DECLARE @sample_statement nvarchar(max);
DECLARE @paramlist nvarchar(max);
EXEC sp_get_query_template
  N'SELECT * FROM AdventureWorks.Sales.SalesOrderHeader AS h
   INNER JOIN AdventureWorks.Sales.SalesOrderDetail AS d
     ON h.SalesOrderID = d.SalesOrderID
   WHERE h.SalesOrderID = 45639;',
  @sample_statement OUTPUT,
  @paramlist OUTPUT
SELECT @paramlist as parameters, @sample_statement as statement
EXEC sp_create_plan_guide @name = N'Template_Plan',
  @stmt = @sample_statement,
  @type = N'TEMPLATE',
  @module_or_batch = NULL,
  @params = @paramlist,
  @hints = N'OPTION(PARAMETERIZATION FORCED)';

 

DBCC FREEPROCCACHE;
GO
SELECT * FROM AdventureWorks.Sales.SalesOrderHeader AS h
INNER JOIN AdventureWorks.Sales.SalesOrderDetail AS d
  ON h.SalesOrderID = d.SalesOrderID
WHERE h.SalesOrderID = 45639;
GO
SELECT * FROM AdventureWorks.Sales.SalesOrderHeader AS h
INNER JOIN AdventureWorks.Sales.SalesOrderDetail AS d
  ON h.SalesOrderID = d.SalesOrderID
WHERE h.SalesOrderID = 45640;
GO

图片 54

 下文将继续关注计划指南的第二部分:计划指南的管理、计划指南的考虑因素、计划指南的有效性、从计划缓存中冻结计划。

网站地图xml地图