七、t_sql程序设计基础

一、逻辑控制语句
1.begin...end

--while
declare @count int
set @count=0
while @count<10
    begin
 set @count=@count+1
 print @count
    end

示例
在本例中，BEGIN 和 END 定义一系列一起执行的 Transact-SQL 语句。如果没有包括 BEGIN...END 块，IF 条件仅使 ROLLBACK TRANSACTION 执行并且不返回打印信息。

USE pubs
GO
CREATE TRIGGER deltitle
ON titles
FOR delete
AS
IF    (SELECT COUNT(*) FROM deleted, sales
      WHERE sales.title_id = deleted.title_id) > 0
   BEGIN
      ROLLBACK TRANSACTION
      PRINT 'You can't delete a title with sales.'
END

2)
SET NOCOUNT OFF
GO
USE pubs
GO
SET NOCOUNT ON
GO
DECLARE @msg varchar(255)
IF (SELECT COUNT(price)
   FROM titles
   WHERE title_id LIKE 'BU%' AND price < 20) > 0
  
   BEGIN
     SET @msg = 'There are several books that are a good value at under $20. These books are: '
         PRINT @msg
     SET NOCOUNT OFF     
      SELECT title
      FROM titles
      WHERE price < 20
   END
ELSE
   BEGIN
     SET @msg = 'There are no books under $20. '
         PRINT @msg
     SELECT title
     FROM titles
     WHERE title_id
     LIKE 'BU%'
     AND
     PRICE <10
   END

2.case
CASE
计算条件列表并返回多个可能结果表达式之一。

CASE 具有两种格式：

简单 CASE 函数将某个表达式与一组简单表达式进行比较以确定结果。
CASE 搜索函数计算一组布尔表达式以确定结果。
两种格式都支持可选的 ELSE 参数。
语法
简单 CASE 函数：

CASE input_expression
    WHEN when_expression THEN result_expression
        [ ...n ]
    [
        ELSE else_result_expression
    END

CASE 搜索函数：

CASE
    WHEN Boolean_expression THEN result_expression
        [ ...n ]
    [
        ELSE else_result_expression
    END
示例

--case把值传给一个变量
use northwind
declare @dis real
declare @coup char(5)
set @coup='CDKIG'
set @dis=
  case @coup
 when 'CXDFG' then 10
 when 'CDKIG' then 7.5
 when 'CKIDG' then 8
  END
print @dis
A. 使用带有简单 CASE 函数的 SELECT 语句
在 SELECT 语句中，简单 CASE 函数仅检查是否相等，而不进行其它比较。下面的示例使用 CASE 函数更改图书分类显示，以使其更易于理解。

USE pubs
GO
SELECT   Category =
      CASE type
         WHEN 'popular_comp' THEN 'Popular Computing'
         WHEN 'mod_cook' THEN 'Modern Cooking'
         WHEN 'business' THEN 'Business'
         WHEN 'psychology' THEN 'Psychology'
         WHEN 'trad_cook' THEN 'Traditional Cooking'
         ELSE 'Not yet categorized'
      END,
   CAST(title AS varchar(25)) AS 'Shortened Title',
   price AS Price
FROM titles
WHERE price IS NOT NULL
ORDER BY type, price
COMPUTE AVG(price) BY type
GO

B. 使用带有简单 CASE 函数和 CASE 搜索函数的 SELECT 语句
在 SELECT 语句中，CASE 搜索函数允许根据比较值在结果集内对值进行替换。下面的示例根据图书的价格范围将价格（money 列）显示为文本注释。

USE pubs
GO
SELECT    'Price Category' =
      CASE
         WHEN price IS NULL THEN 'Not yet priced'
         WHEN price < 10 THEN 'Very Reasonable Title'
         WHEN price >= 10 and price < 20 THEN 'Coffee Table Title'
         ELSE 'Expensive book!'
      END,
   CAST(title AS varchar(20)) AS 'Shortened Title'
FROM titles
ORDER BY price
GO

C. 使用带有 SUBSTRING 和 SELECT 的 CASE 函数
下面的示例使用 CASE 和 THEN 生成一个有关作者、图书标识号和每个作者所著图书类型的列表。

USE pubs
SELECT SUBSTRING((RTRIM(a.au_fname) + ' '+
   RTRIM(a.au_lname) + ' '), 1, 25) AS Name, a.au_id, ta.title_id,
   Type =
  CASE
    WHEN SUBSTRING(ta.title_id, 1, 2) = 'BU' THEN 'Business'
    WHEN SUBSTRING(ta.title_id, 1, 2) = 'MC' THEN 'Modern Cooking'
    WHEN SUBSTRING(ta.title_id, 1, 2) = 'PC' THEN 'Popular Computing'
    WHEN SUBSTRING(ta.title_id, 1, 2) = 'PS' THEN 'Psychology'
    WHEN SUBSTRING(ta.title_id, 1, 2) = 'TC' THEN 'Traditional Cooking'
  END
FROM titleauthor ta JOIN authors a ON ta.au_id = a.au_id

3.GOTO
权限
GOTO 语句的权限默认情况下授予任何有效用户。

示例

--利用goto、 return实现while

declare @counter int
set @counter=0
counter:
    set @counter=@counter+1
    goto checkresult
checkresult:
    if @counter<=10
 begin
    print cast(@counter as char)
    goto counter
 end
    else
 return

下面给出用 GOTO 循环代替 WHILE 循环的示例。

 

说明  这里未定义 tnames_cursor 游标。本例只用于演示。

USE pubs
GO
DECLARE @tablename sysname
SET @tablename = N'authors'
table_loop:
   IF (@@FETCH_STATUS <> -2)
   BEGIN  
      SELECT @tablename = RTRIM(UPPER(@tablename))
      EXEC ("SELECT """ + @tablename + """ = COUNT(*) FROM "
            + @tablename )
      PRINT " "
   END
   FETCH NEXT FROM tnames_cursor INTO @tablename
IF (@@FETCH_STATUS <> -1) GOTO table_loop
GO

4 IF ELSE
A. 使用一个 IF...ELSE 块
下面的示例显示带有语句块的 IF 条件。如果书的平均价格不低于 $15，那么就显示文本：Average title price is more than $15.

USE pubs

IF (SELECT AVG(price) FROM titles WHERE type = 'mod_cook') < $15
BEGIN
   PRINT 'The following titles are excellent mod_cook books:'
   PRINT ' '
   SELECT SUBSTRING(title, 1, 35) AS Title
   FROM titles
   WHERE type = 'mod_cook'
END
ELSE
   PRINT 'Average title price is more than $15.'

B. 使用多个 IF...ELSE 块
下面的示例使用了两个 IF 块。如果书的平均价格不低于 $15，那么就显示文本：Average title price is more than $15。如果现代烹调书的平均价格高于 $15，则显示现代烹调书价格昂贵的语句。

USE pubs

IF (SELECT AVG(price) FROM titles WHERE type = 'mod_cook') < $15
BEGIN
   PRINT 'The following titles are excellent mod_cook books:'
   PRINT ' '
   SELECT SUBSTRING(title, 1, 35) AS Title
   FROM titles
   WHERE type = 'mod_cook'
END
ELSE
   IF (SELECT AVG(price) FROM titles WHERE type = 'mod_cook') > $15
BEGIN
   PRINT 'The following titles are expensive mod_cook books:'
   PRINT ' '
   SELECT SUBSTRING(title, 1, 35) AS Title
   FROM titles
   WHERE type = 'mod_cook'
END

5.RETURN
示例
A. 从过程返回
下例显示如果在执行 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

B. 返回状态代码
下例检查指定作者所在州的 ID。如果所在的州是加利福尼亚州 (CA)，将返回状态代码 1。否则，对于任何其它情况（state 的值是 CA 以外的值或者 au_id 没有匹配的行），将返回状态代码 2。

CREATE PROCEDURE checkstate @param varchar(11)
AS
IF (SELECT state FROM authors WHERE au_id = @param) = 'CA'
   RETURN 1
ELSE
   RETURN 2

下例显示从 checkstate 执行中返回的状态。第一个显示的是在加利福尼亚州的作者；第二个显示的是不在加利福尼亚州的作者，第三个显示的是无效的作者。必须先声明 @return_status 局部变量后才能使用它。

DECLARE @return_status int
EXEC @return_status = checkstate '172-32-1176'
SELECT 'Return Status' = @return_status
GO

示例
A. 使用 WAITFOR TIME
下例在晚上 10:20 执行存储过程 update_all_stats。

BEGIN
   WAITFOR TIME '22:20'
   EXECUTE update_all_stats
END

有关使用此过程更新数据库中所有统计的更多信息，请参见 UPDATE STATISTICS 中的示例。

B. 使用 WAITFOR DELAY
下例说明如何对 WAITFOR DELAY 选项使用局部变量。将创建一个存储过程，该过程将等待可变的时间量，然后将所经过的小时、分钟和秒数信息返回给用户。

CREATE PROCEDURE time_delay @@DELAYLENGTH char(9)
AS
DECLARE @@RETURNINFO varchar(255)
BEGIN
   WAITFOR DELAY @@DELAYLENGTH
   SELECT @@RETURNINFO = 'A total time of ' +
                  SUBSTRING(@@DELAYLENGTH, 1, 3) +
                  ' hours, ' +
                  SUBSTRING(@@DELAYLENGTH, 5, 2) +
                  ' minutes, and ' +
                  SUBSTRING(@@DELAYLENGTH, 8, 2) +
                  ' seconds, ' +
                  'has elapsed! Your time is up.'
   PRINT @@RETURNINFO
END
GO

EXEC time_delay '000:00:10'
GO

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

USE pubs
GO
WHILE (SELECT AVG(price) FROM titles) < $30
BEGIN
   UPDATE titles
      SET price = price * 2
   SELECT MAX(price) FROM titles
   IF (SELECT MAX(price) FROM titles) > $50
      BREAK
   ELSE
      CONTINUE
END
PRINT 'Too much for the market to bear'

B. 在带有游标的过程中使用 WHILE
以下的 WHILE 结构是名为 count_all_rows 过程中的一部分。下例中，该 WHILE 结构测试用于游标的函数 @@FETCH_STATUS 的返回值。因为 @@FETCH_STATUS 可能返回 –2、-1 或 0，所以，所有的情况都应进行测试。如果某一行在开始执行此存储过程以后从游标结果中删除，将跳过该行。成功提取 (0) 后将执行 BEGIN...END 循环内部的 SELECT 语句。

USE pubs
DECLARE tnames_cursor CURSOR
FOR
   SELECT TABLE_NAME
   FROM INFORMATION_SCHEMA.TABLES
OPEN tnames_cursor
DECLARE @tablename sysname
--SET @tablename = 'authors'
FETCH NEXT FROM tnames_cursor INTO @tablename
WHILE (@@FETCH_STATUS <> -1)
BEGIN
   IF (@@FETCH_STATUS <> -2)
   BEGIN  
      SELECT @tablename = RTRIM(@tablename)
      EXEC ('SELECT ''' + @tablename + ''' = count(*) FROM '
            + @tablename )
      PRINT ' '
   END
   FETCH NEXT FROM tnames_cursor INTO @tablename
END
CLOSE tnames_cursor
DEALLOCATE tnames_cursor

6.waitfor
--waitfor
declare @wait int
set @wait=3
begin
   waitfor delay @wait
   print '时间到！'
end

 

二、变量
8全局变量
例：
select @@connections ,@@cpu_busy,@@cursor_rows,@@datefirst
select @@dbts,@@error,@@fetch_status,@@identity,@@idle
select @@io_busy,@@langid,@@language,@@lock_timeout,@@max_connections
select @@max_precision,@@nestlevel,@@microsoftversion,@@options
select @@pack_received,@@pack_sent,@@packet_errors,@@procid,@@remserver
select @@rowcount,@@servername,@@servicename,@@spid,@@textsize
select @@timeticks,@@total_errors,@@total_read,@@total_write,@@trancount,@@version

9.系统全局变量
select @@version
select @@language
select @@servername

10.--声明一个变量
declare @myvar int
--将其赋予一个值
set @myvar=1
--显示其值
print @myvar
11.局部变量
emp:
use pubs
go

declare @myvar int
set @myvar=10

select *
from employee
where job_id=@myvar

三、函数

1.聚合函数

go
select sum(unitprice)as sum,
 avg(unitprice) as avg,
 count(*) as count,
 max(unitprice)as max,
 min(unitprice)as min,
 count(unitprice)as count1
from [order details]
group by productid
having  count(unitprice)>30
order by count1 desc

2.转换函数（convert）
use pubs
go
select substring(title,1,10)as title,ytd_sales
from titles
where convert(char(20),ytd_sales) like '3%'
go

declare @myval decimal(5,2)
set @myval=193.57
select convert(varbinary(20),@myval)as varbinary
select convert(decimal(6,3),convert(varbinary(20),@myval))as decimal

select convert(char(8),getdate(),1)as aa

3.日期函数

select getdate()
select convert(datetime,'2002-07-09')as aa
select datediff(dd,convert(datetime,'2002-07-28'),getdate())
select datepart(mm,getdate())

4.数学函数

select abs(-5.0)as a1 ,abs(1.3)as a2,abs(0) as a3

select pi() as pi1

select square(8) as square1

select sqrt(8)as sqrt1

5.字符串函数

select title_id as title, convert(varchar(8),price)+ '   '+notes as price
from titles

select charindex('cheng','chengliming') as cheng

select len('chengliming') as ming

select len('chengliming')-charindex('g','chengliming') as lenming

select substring('chengliming',3,4) as sub
select lower('cLIMING') AS ASE

6.系统函数

select host_id() as host
select host_name() as hostname

select user_name() as username
select user_id() as userid
select suser_name() as suser
select suser_sid() as sus