Transact-SQL语言-存储过程 （转）

存储过程

系统存储过程是SQL Server系统创建的存储过程，它的目的在于能够方便地从系统表中查询信息，或者完成与更新数据库表相关的管理任务或其他的系统管理任务。系统存储过程可以在任意一个数据库中执行。系统存储过程创建并存放于系统数据库master中，并且名称以sp_或者xp_开头。一些系统过程只能由系统管理员使用，而有些系统过程通过授权可以被其他用户使用。

系统存储过程的部分示例如下：
sp_addtype：用于定义一个用户定义数据类型；
sp_configure：用于管理服务器配置选项设置；
xp_sendmail：用于发送电子邮件或寻呼信息；
sp_stored_procedures：用于返回当前数据库中的存储过程的清单；
sp_help：用于显示参数清单和其数据类型；
sp_depends：用于显示存储过程依据的对象或者依据存储过程的对象；
sp_helptext：用于显示存储过程的定义文本；
sp_rename：用于修改当前数据库中用户对象的名称。

 

注释

--

/*  */

 

变量

变量是一种语言中必不可少的组成部分。Transact-SQL语言中有两种形式的变量，一种是用户自己定义的局部变量，另外一种是系统提供的全局变量。

1. 局部变量
      局部变量是一个能够拥有特定数据类型的对象，它的作用范围仅限制在程序内部。局部变量被引用时要在其名称前加上标志“＠”，而且必须先用DECLARE命令定义后才可以使用。 
定义局部变量的语法形式如下：
       DECLAERlocal_variable   data_type} […n]
其中，参数@local_variable用于指定局部变量的名称，变量名必须以符号@开头，并且局部变量名必须符合SQL Server的命名规则。参数data_type用于设置局部变量的数据类型及其大小。data_type可以是任何由系统提供的或用户定义的数据类型。但是，局部变量不能是 text，ntext 或 image 数据类型。
使用DECLARE命令声明并创建局部变量之后，会将其初始值设为NULL，如果想要设定局部变量的值，必须使用SELECT命令或者SET命令。其语法形式为：
      SET { { @local_variable = expression }  或者 SELECT { @local_variable = expression } [ ,...n ]
其中，参数@local_variable是给其赋值并声明的局部变量，参数expression是任何有效的SQL Server表达式。

 

1. 局部变量
例3-26　创建一个@myvar 变量，然后将一个字符串值放在变量中，最后输出 @myvar 变量的值。
     程序清单如下：
     DECLARE @myvar  char(20)
     select  @myvar = 'This is a test'
     SELECT @myvar
     GO
例3-27　通过查询给变量赋值。
     程序清单如下：
     USE adventureworks
     GO
     DECLARE @rows int
     SET @rows = (SELECT COUNT(*)  FROM  humanresources.employee)

 

2. 全局变量
     除了局部变量之外，SQL Server系统本身还提供了一些全局变量。全局变量是SQL Server系统内部使用的变量，其作用范围并不仅仅局限于某一程序，而是任何程序均可以随时调用。全局变量通常存储一些SQL Server的配置设定值和统计数据。用户可以在程序中用全局变量来测试系统的设定值或者是Transact-SQL命令执行后的状态值。在使用全局变量时应该注意以下几点：
    （1）全局变量不是由用户的程序定义的，它们是在服务器级定义的。
    （2）用户只能使用预先定义的全局变量。
    （3）引用全局变量时，必须以标记符“@@”开头。
    （4）局部变量的名称不能与全局变量的名称相同，否则会在应用程序中出现不可预测的结果。

 

2. 全局变量
例3-29　显示到当前日期和时间为止试图登录SQL Server的次数。
   程序清单如下：
   SELECT GETDATE( )  AS  '当前的时期和时间',
   @@CONNECTIONS AS  '试图登录的次数'

 

运算符

在SQL Server 2005中，运算符的优先等级从高到低如下所示，如果优先等级相同，则按照从左到右的顺序进行运算。
（1）括号：（）；
（2）乘、除、求模运算符：*，/，%；
（3）加减运算符：＋，－；
（4）比较运算符：=，>，<，>=，<=，<>，!=，!>，!<；
（5）位运算符：^，&，|；
（6）逻辑运算符：NOT；
（7）逻辑运算符：AND；
（8）逻辑运算符：OR。

 

函数

Transact-SQL 编程语言提供了四种函数：行集函数、聚合函数、Ranking函数、标量函数。

 

1. 行集函数
行集函数可以在Transact-SQL语句中当作表引用。
例3-33　通过行集函数OPENQUERY()执行一个分布式查询，以便从服务器local中提取表department中的记录。
   程序清单如下：
   select * from  openquery(local,’select * from department’)

 

2. 聚合函数（AVG,SUM.COUNT)
聚合函数用于对一组值进行计算并返回一个单一的值。除COUNT 函数之外，聚合函数忽略空值。聚合函数经常与 SELECT 语句的 GROUP BY 子句一同使用。仅在下列项中聚合函数允许作为表达式使用：SELECT 语句的选择列表（子查询或外部查询）；COMPUTE 或 COMPUTE BY 子句；HAVING 子句。

例
计算 Adventure Works Cycles 的副总所用的平均休假小时数以及总的病假小时数。对检索到的所有行，每个聚合函数都生成一个单独的汇总值。
程序清单如下。

USE AdventureWorks;
GO
SELECT AVG(VacationHours)as 'Average vacation hours', 
    SUM  (SickLeaveHours) as 'Total sick leave hours'
FROM HumanResources.Employee
WHERE Title LIKE 'Vice President%' 

 

3. Ranking函数
Ranking函数为查询结果数据集分区中的每一行返回一个序列值。依据此函数，一些行可能取得和其他行一样的序列值。Transact-SQL提供以下一些Ranking函数：RANK；DENSE_RANK；NTILE；ROW_NUMBER。

4. 标量函数
标量函数用于对传递给它的一个或者多个参数值进行处理和计算，并返回一个单一的值。标量函数可以应用在任何一个有效的表达式中。标量函数可分为如表3-4所示的几大类
函数分类 解释
配置函数 返回当前的配置信息
游标函数 返回有关游标的信息
日期和时间函数 对日期和时间输入值进行处理
数学函数 对作为函数参数提供的输入值执行计算
元数据函数 返回有关数据库和数据库对象的信息
安全函数 返回有关用户和角色的信息
字符串函数 对字符串（char 或 varchar）输入值执行操作
系统函数 执行操作并返回有关SQL Server中的值、对象和设置的信息
系统统计函数 返回系统的统计信息
文本和图像函数 对文本或图像输入值或列执行操作，返回有关这些值的信息

 

1）字符串函数
字符串函数可以对二进制数据、字符串和表达式执行不同的运算，大多数字符串函数只能用于char和varchar数据类型以及明确转换成char和varchar的数据类型，少数几个字符串函数也可以用于binary和varbinary数据类型。
字符串函数可以分为以下几大类：
基本字符串函数：UPPER，LOWER，SPACE，REPLICATE，STUFF，REVERSE，LTRIM，RTRIM。
字符串查找函数：CHARINDEX，PATINDEX。
长度和分析函数：DATALENGTH，SUBSTRING，RIGHT。
转换函数：ASCH，CHAR，STR，SOUNDEX，DIFFERENCE。

 

LTRIM

例3-38　使用 LTRIM 函数删除字符变量中的起始空格。
程序清单如下：
DECLARE @string_to_trim varchar(60)
SET @string_to_trim = '    Five spaces are at the beginning of this    string.'
SELECT 'Here is the string without the leading spaces: ' +
LTRIM(@string_to_trim)

 

CHARINDEX

例3-39　使用可选的start_location参数从addressline1列的第2个字符开始查找“shoe”
程序清单如下。
USE adventureworks
SELECT CHARINDEX('shoe', addressline1,2) FROM person.address
WHERE addressid = '5'

 

SUBSTRING

例3-40显示如何只返回字符串的一部分。该查询在一列中返回person.contact表中的姓氏，在另一列中返回person.contact表中的名字首字母。
程序清单如下：
USE adventureworks
SELECT lastname, SUBSTRING(firstname, 1, 1)
FROM person.contact
ORDER BY lastname

 

STUFF

例3-41　在第一个字符串（abcdef）中删除从第二个位置（字符 b）开始的三个字符，然后在删除的起始位置插入第二个字符串，创建并返回一个字符串。
程序清单如下：
SELECT STUFF('abcdef', 2, 3, 'ijklmn')

 

2）日期和时间函数
       日期和时间函数用于对日期和时间数据进行各种不同的处理和运算，并返回一个字符串、数字值或日期和时间值。与其他函数一样，可以在SELECT语句的SELECT和WHERE子句以及表达式中使用日期和时间函数。
函数                          参数                                           功能
DATEADD （datepart,number,date） 以datepart指定的方式，返回date加上number之和
DATEDIFF （datepart,date1,date2） 以datepart指定的方式，返回date2与date1之差
DATENAME （datepart,date） 返回日期date中datepart指定部分所对应的字符串
DATEPART （datepart,date） 返回日期date中datepart指定部分所对应的整数值
DAY （date） 返回指定日期的天数
GETDATE （） 返回当前的日期和时间
MONTH （date） 返回指定日期的月份数
YEAR （date） 返回指定日期的年份数

 

DATEDIFF
例3-42　显示在humanresources.employee 表中雇用日期到当前日期间的天数。
程序清单如下。

USE adventureworks
SELECT DATEDIFF(day, hiredate, getdate())  AS diffdays
FROM humanresources.employee

 

GETDATE
例3-43　从GETDATE函数返回的日期中提取月份名。
程序清单如下：
SELECT DATENAME(month, getdate())  AS  'Month Name'

 

3）数学函数
数学函数用于对数字表达式进行数学运算并返回运算结果。数学函数可以对SQL Server提供的数字数据（decimal、integer、float、real、money、smallmoney、smallint 和 tinyint）进行处理。在SQL Server中，常用的数学函数如表3-8所示。

 

CEILING()，FLOOR()，ROUND()

例3-45　在同一表达式中使用CEILING()，FLOOR()，ROUND()函数。
程序清单如下：
select  ceiling(13.4),  floor(13.4),  round(13.4567,3)

（4）系统函数
系统函数用于返回有关SQL Server系统、用户、数据库和数据库对象的信息。系统函数可以让用户在得到信息后，使用条件语句，根据返回的信息进行不同的操作。与其他函数一样，可以在SELECT语句的SELECT和WHERE子句以及表达式中使用系统函数。

 

CAST

例3-46 示例检索列表价格的第一位是 3 的产品的名称，并将ListPrice转换为 int。
程序清单如下。

USE AdventureWorks
GO
SELECT SUBSTRING(Name, 1, 30) AS ProductName, ListPrice
FROM Production.Product
WHERE CAST(ListPrice AS int) LIKE '3%'
GO

CONVERT

例3-47　用style 参数将当前日期转换为不同格式的字符串。
程序清单如下：

SELECT '101'=CONVERT(char, GETDATE(), 101),
'1'=CONVERT(char, GETDATE(), 1),
'112'=CONVERT(char, GETDATE(), 112) 

 

例3-48　从adventureworks数据库中返回person.contact表的首列名称
程序清单如下。
USE adventureworks
SELECT COL_NAME(OBJECT_ID('person.contact'), 1)

 

例3-49　检查 sysdatabases 中的每一个数据库，使用数据库标识号来确定数据库名称。
程序清单如下：

USE master
SELECT dbid, DB_NAME(dbid)  AS  DB_NAME
FROM sysdatabases
ORDER BY dbid 

 

流程控制语句

流程控制语句是指那些用来控制程序执行和流程分支的语句，在SQL Server 2005中，流程控制语句主要用来控制SQL语句、语句块或者存储过程的执行流程。

 

1． IF…ELSE语句
例3-51　显示带有语句块的IF条件。如果产品的平均价格不低于$15，那么就显示文本：Average price is more than $15
程序清单如下。

USE adventureworks
IF (SELECT AVG(listprice) FROM production.product WHERE color = 'blue') < $15
BEGIN
   PRINT 'The following product are excellent:'
   PRINT ' '
   SELECT SUBSTRING(name, 1, 15) AS name
   FROM production.product
   WHERE color = 'blue' 
END
ELSE
   PRINT 'Average price is more than $15.' 

 

2． BEGIN…END语句
BEGIN…END语句能够将多个Transact-SQL语句组合成一个语句块，并将它们视为一个单元处理。在条件语句和循环等控制流程语句中，当符合特定条件便要执行两个或者多个语句时，就需要使用BEGIN…END语句。

例3-52　利用 BEGIN 和 END 语句使得 IF 语句在取值为 FALSE 时跳过语句块。
程序清单如下：

IF (@@ERROR <> 0)
BEGIN
   SET @ErrorSaveVariable = @@ERROR
   PRINT 'Error encountered, ' + 
         CAST(@ErrorSaveVariable AS VARCHAR(10))
END 

3．GO 语句
Go 语句是批的结束语句。批是一起提交并作为一个组执行的若干SQL语句。
例3-53  用Go 语句作为批的结束语句。

程序清单如下：
USE adventureworks
GO
DECLARE @MyMsg VARCHAR(50)
SELECT @MyMsg = 'Hello, World.'
GO -- @MyMsg 在Go语句后失效

 

4．CASE语句
CASE语句可以计算多个条件式，并将其中一个符合条件的结果表达式返回。CASE语句按照使用形式的不同，可以分为简单CASE语句和搜索CASE语句。
它们的语法形式分别为：
CASE input_expression    WHEN when_expression THEN result_expression        [ ...n ]    [         ELSE else_result_expression    ENDCASE   
 WHEN Boolean_expression THEN result_expression        [ ...n ]    [         ELSE else_result_expression    END

例3-54　使用CASE函数去更改产品的分类显示，以使其更易于理解
程序清单如下。

USE adventureworks
SELECT   Category = 
      CASE color
         WHEN 'red' THEN 'red color'
         WHEN 'black' THEN 'black color'
         WHEN 'silver' THEN 'silver color'
         WHEN 'yellow' THEN 'yellow color'
         WHEN 'blue' THEN 'blue color'
      END,
   CAST(name AS varchar(25)) AS 'Shortened name',
   listprice AS Price
FROM production.product
WHERE listprice IS NOT NULL
ORDER BY color, price
COMPUTE AVG(listprice) BY color

例3-55　使用搜索CASE函数，根据产品的价格范围将价格显示为文本注释
程序清单如下。

USE adventureworks
SELECT    'Price Category'= 
     CASE 
        WHEN listprice IS NULL THEN 'Not yet priced'
         WHEN listprice < 10 THEN 'Very Reasonable Title'
         WHEN listprice >= 10 and listprice < 20 THEN 'Coffee Table Title'
         ELSE 'Expensive!'
      END,
   CAST(name AS varchar(20)) AS 'Shortened name'
FROM production.product
ORDER BY listprice
GO

5．WHILE…CONTINUE…BREAK语句 
WHILE…CONTINUE…BREAK语句用于设置重复执行 SQL 语句或语句块的条件。只要指定的条件为真，就重复执行语句。其中，CONTINUE语句可以使程序跳过CONTINUE语句后面的语句，回到WHILE循环的第一行命令。BREAK语句则使程序完全跳出循环，结束WHILE语句的执行。
其语法形式为：

 

例3-56　在嵌套的 IF...ELSE 和 WHILE 中使用 BREAK 和 CONTINUE。如果平均价格少于 $30，WHILE 循环就将价格加倍，然后选择最高价。如果最高价少于或等于 $50，WHILE 循环重新启动并再次将价格加倍。该循环不断地将价格加倍直到最高价格超过 $50，然后退出 WHILE 循环并打印一条消息。
程序清单如下：

USE adventureworks
WHILE (SELECT AVG(listprice)  FROM production.product) < $30
BEGIN
   UPDATE production.product
      SET listprice = listprice * 2
   SELECT MAX(listprice) FROM production.product
   IF (SELECT MAX(listprice) FROM production.product) > $50
      BREAK
   ELSE
      CONTINUE
END
PRINT 'Too much for the market to bear'

 

6．GOTO语句
GOTO语句可以使程序直接跳到指定的标有标识符的位置处继续执行，而位于GOTO语句和标识符之间的程序将不会被执行。GOTO语句和标识符可以用在语句块、批处理和存储过程中，标识符可以为数字与字符的组合，但必须以“: ”结尾。如：‘a1: ’。在GOTO语句行，标识符后面不用跟“: ”。GOTO语句的语法形式为：
GOTO label
……
label:

 

例3-57　利用GOTO语句求出从1加到5的总和。
程序清单如下：

declare    @sum   int,   @count   int
select   @sum=0,   @count=1
label_1:
select   @sum=@sum+@count
select   @count=@count+1
if   @count<=5
goto   label_1
select   @count   @sum 

 

7．WAITFOR语句
WAITFOR语句用于暂时停止执行SQL语句、语句块或者存储过程等，直到所设定的时间已过或者所设定的时间已到才继续执行。
WAITFOR语句的语法形式为：
WAITFOR { DELAY 'time' | TIME 'time' }
其中，DELAY用于指定时间间隔，TIME用于指定某一时刻，其数据类型为datetime，格式为‘hh:mm:ss’。
例3-58　使用WAITFOR TIME语句，以便在晚上10:20执行存储过程 update_all_stats。
程序清单如下：
BEGIN
   WAITFOR TIME '22:20'
   EXECUTE update_all_stats
END

8．RETURN语句
例3-59　显示如果在执行 findjobs 时没有给出用户名作为参数，RETURN 则将一条消息发送到用户的屏幕上然后从过程中退出。如果给出用户名，将从适当的系统表中检索由该用户在当前数据库内创建的所有对象名。
程序清单如下：

CREATE PROCEDURE findjobs @nm sysname = NULL
AS 
IF @nm IS NULL
   BEGIN
     PRINT 'You must give a username'
      RETURN
   END
ELSE
   BEGIN
      SELECT o.name, o.id, o.uid
      FROM sysobjects o INNER JOIN master..syslogins l
         ON o.uid = l.sid
      WHERE l.name = @nm
   END