第10章 可编程对象1-变量、批处理、流程控制、游标、临时表、表类型

10.1 变量

1、变量的声明和赋值

DECLARE @i AS INT; 
SET @i = 10;

DECLARE @i AS INT = 10;                        /*2008新增，允许在声明时同时初始化变量的值*/

2、set语句一次只能给一个变量赋值

DECLARE @firstname AS NVARCHAR(20) , @lastname AS NVARCHAR(40);

SET @firstname = ( SELECT   firstname
                   FROM     HR.Employees
                   WHERE    empid = 3);                
SET @lastname = ( SELECT    lastname
                  FROM      HR.Employees
                  WHERE     empid = 3);          
                       
SELECT  @firstname AS firstname , @lastname AS lastname;

 有一种一次给多个变量赋值的非标准写法：

DECLARE @firstname AS NVARCHAR(20), @lastname AS NVARCHAR(40);      
                       
SELECT  
    @firstname = firstname , 
    @lastname = lastname
FROM HR.Employees
WHERE empid = 3;     

SELECT  @firstname AS firstname , @lastname AS lastname;

 10.2 批处理

 批处理是作为一个单元而进行分析和执行的一组T-SQL语句。

批处理要经历的过程：

（1）分析：语法检查

（2）解析：检查引用的对象和列是否存在、是否具有访问权限

（3）优化：作为一个执行单元

在SSMS中，用命令 GO 标识一批T-SQL语句的结束。注意，GO命令是客户端工具的命令，而非T-SQL服务器的命令。

1、批处理是语法分析的单元

同时提交的多个批处理，能通过语法分析的单元将继续提交给SQL server执行；中间出错的单元则会报错，出错的单元不再继续提交给SQL server。

2、变量是属于定义它们的批处理的局部变量

3、不能在同一个批处理中编译的语句

下列语句不能在同一个批处理中和其他语句同时编译，需要单独放在一个批中：

  • CREATE DEFAULT
  • CREATE FUNCTION
  • CREATE PROCEDURE
  • CREATE RULE
  • CREATE SCHEMA
  • CREATE TRIGGER
  • CREATE VIEW

4、批处理是语句解析的单元

当设计批处理的边界时，应牢记这一事实。检查出具对象是否存在，最佳实践是将DDL与DML分割到不同的批中。

USE tempdb;
IF OBJECT_ID('dbo.T1', 'U') IS NOT NULL DROP TABLE dbo.T1;
CREATE TABLE dbo.T1(col1 INT);
GO

ALTER TABLE dbo.T1 ADD col2 INT;
GO                                     -- 不加GO会报错，因为col2还尚未存在，select不能去选择
SELECT col1, col2 FROM dbo.T1;
GO

 5、Go n 选项

Go之前的批处理执行n次。

 10.3 流程控制语句

1、IF...ELSE 语句

T-SQL使用的是三值逻辑，当条件为TRUE时，执行IF下的语句块；当条件取值为 FALSE 或 UNKNOWN（例如，涉及到NULL） 时都可以激活ELSE语句块。

如果需要在IF或ELSE部分运行多条语句，可以使用语句块，语句块的边界是用一对BEGIN和END关键字标识的。

IF DAY(CURRENT_TIMESTAMP) = 1
/* 如果是当月的第一天，对数据库进行完全备份 */
BEGIN
  PRINT 'Today is the first day of the month.';
  PRINT 'Starting a full database backup.';
  BACKUP DATABASE TSQLFundamentals2008
    TO DISK = 'C:\Temp\TSQLFundamentals2008_Full.BAK' WITH INIT;
  PRINT 'Finished full database backup.';
END
ELSE
/* 如果不是当月的第一天，对数据库进行差异备份 */
BEGIN
  PRINT 'Today is not the first day of the month.'
  PRINT 'Starting a differential database backup.';
  BACKUP DATABASE TSQLFundamentals2008
    TO DISK = 'C:\Temp\TSQLFundamentals2008_Diff.BAK' WITH INIT;
  PRINT 'Finished differential database backup.';
END
GO

2、WHILE语句

WHILE关键字后的条件为TRUE时，循环继续；当条件为 FALSE 或 UNKNOWN 时 循环终止。

（1）BREAK 中断循环

DECLARE @i AS INT;
SET @i = 1
WHILE @i <= 10
BEGIN
  IF @i = 6 BREAK;
  PRINT @i;
  SET @i = @i + 1;
END;
GO

1
2
3
4
5
以上执行结果

（2）CONTINUE 跳过当前循环

DECLARE @i AS INT;
SET @i = 0
WHILE @i < 10
BEGIN
  SET @i = @i + 1;
  IF @i = 6 CONTINUE;
  PRINT @i;
END;
GO
1
2
3
4
5
7
8
9
10
以上执行结果

10.4 游标

查询结果往往是一个含有多条记录的集合，游标机制允许用户逐行地访问、处理这些记录。

1、使用游标的步骤

（1）在某个查询的基础上声明游标，即把游标与查询结果集联系起来；

（2）打开游标；

（3）从第一个游标记录中把列值取到指定的变量；

（4）当还没有超出游标的最后一行时（@@FETCH_STATUS函数的返回值是0），循环遍历游标记录；在每一次遍历中，从当前游标记录中把列值提取到指定的变量，再为当前行执行相应的处理；

（5）关闭游标；

（6）释放游标。

    DECLARE @vgz CHAR(10);
    
    /* 声明游标，并将去年今天充值单的单号载入 */
    DECLARE vgz_cursor CURSOR FOR
        SELECT  VipAddValueID
        FROM    dbo.VIPAddValue
        WHERE   VipAddValue_Date = CONVERT(CHAR(10), GETDATE() - 365, 120); 
   
    /* 使用游标，循环处理 */        
    OPEN vgz_cursor;                                     -- 打开游标，行指针指向结果集的第一行之前               
    FETCH NEXT FROM vgz_cursor INTO @vgz;                -- 载入第一个充值单号，即将行指针指向第一行
    WHILE @@FETCH_STATUS = 0                             -- 未超出游标的最后一行时，@@FETCH_STATUS一直为0
        BEGIN 
            EXEC dbo.zp_OverdueCheck @vgz;  
            FETCH NEXT FROM vgz_cursor INTO @vgz;        -- 载入下一个充值单号
        END; 
    CLOSE vgz_cursor;                                    -- 关闭游标，删除前还可以再被打开                  
    DEALLOCATE vgz_cursor;                               -- 释放游标，即删除游标

2、FETCH的用法

FETCH 
      [ NEXT | PRIOR | FIRST | LAST] 
FROM 
      { 游标名 | @游标变量名 } [ INTO @变量名 [，…] ]

 （1）INTO @变量名[,…]

把提取操作的列数据放到局部变量中。各个局部变量从左到右与游标结果集中的相应列相对应。各变量的数据类型必须与相应的结果列的数据类型匹配或是结果列数据类型所支持的隐性转换。变量的数目必须与游标选择列表中的列的数目一致。

（2）@@FETCH_STATUS

每执行一个FETCH操作之后，通常都要查看一下全局变量@@FETCH_STATUS中的状态值，以此判断FETCH操作是否成功。该变量有三种状态值：

    •   0  表示成功执行FETCH语句。
    •  -1  表示FETCH语句失败，例如移动行指针使其超出了结果集。
    •  -2  表示被提取的行不存在。

由于@@FETCH_STATU是全局变量，在一个连接上的所有游标都可能影响该变量的值。因此，在执行一条FETCH语句后，必须在对另一游标执行另一FETCH 语句之前测试该变量的值才能作出正确的判断

SET NOCOUNT ON;
USE TSQLFundamentals2008;

DECLARE @Result TABLE
(
  custid     INT,
  ordermonth DATETIME,
  qty        INT, 
  runqty     INT,
  PRIMARY KEY(custid, ordermonth)
);

DECLARE
  @custid     AS INT,
  @prvcustid  AS INT,
  @ordermonth DATETIME,
  @qty        AS INT,
  @runqty     AS INT;

DECLARE C CURSOR FAST_FORWARD /* read only, forward only */ FOR
  SELECT custid, ordermonth, qty
  FROM Sales.CustOrders
  ORDER BY custid, ordermonth;

OPEN C

FETCH NEXT FROM C INTO @custid, @ordermonth, @qty;

SELECT @prvcustid = @custid, @runqty = 0;

WHILE @@FETCH_STATUS = 0
BEGIN
  IF @custid <> @prvcustid
    SELECT @prvcustid = @custid, @runqty = 0;

  SET @runqty = @runqty + @qty;

  INSERT INTO @Result VALUES(@custid, @ordermonth, @qty, @runqty);
  
  FETCH NEXT FROM C INTO @custid, @ordermonth, @qty;
END

CLOSE C;

DEALLOCATE C;

SELECT 
  custid,
  CONVERT(VARCHAR(7), ordermonth, 121) AS ordermonth,
  qty,
  runqty
FROM @Result
ORDER BY custid, ordermonth;
GO
实例

 10.5 临时表

SQL Server 支持三种类型的临时表：

  • 局部临时表
  • 全局临时表
  • 表变量

三种类型的表都在tempdb数据库中创建，都有对应的表作为其物理表示，不要以为只是在内存中。

1、局部临时表

表名前加 # 作为前缀。

作用范围：只对创建它的会话在创建级和调用堆栈内部级（内部的过程、函数、触发器以及动态批处理）是可见的，会话结束自动删除。

使用场景：

（1）当需要把中间结果临时保存起来，以供以后查询这些临时数据时

（2）需要多次访问某个开销昂贵的处理结果（尤其是此结果是个非常小的集合）时，可以将开销昂贵的工作只做一次，将结果保存进临时表备用。

例如：比较当年和前一年的销售额

USE TSQLFundamentals2008;

IF OBJECT_ID('tempdb.dbo.#MyOrderTotalsByYear') IS NOT NULL
  DROP TABLE dbo.#MyOrderTotalsByYear;
GO

SELECT
  YEAR(O.orderdate) AS orderyear,
  SUM(OD.qty) AS qty
INTO dbo.#MyOrderTotalsByYear
FROM Sales.Orders AS O
  JOIN Sales.OrderDetails AS OD
    ON OD.orderid = O.orderid
GROUP BY YEAR(orderdate);

SELECT Cur.orderyear, Cur.qty AS curyearqty, Prv.qty AS prvyearqty
FROM dbo.#MyOrderTotalsByYear AS Cur
  LEFT OUTER JOIN dbo.#MyOrderTotalsByYear AS Prv
    ON Cur.orderyear = Prv.orderyear + 1;
GO

2、全局临时表

表名前加 ## 作为前缀

作用范围：除了创建它的会话，其它所有会话也都可见。当创建它的会话断开数据库联结，而且也没有活动在引用全局临时表时，此表被自动删除。

使用场景：需要和所有人共享数据时。

3、表变量

像变量一样使用，但它在tempdb数据库中也有对应的表作为其物理表示，而不仅仅在内存中。

作用范围：只对当前会话的当前批处理可见。

使用场景：对于少量的数据，使用表变量性能更好，而不是去使用临时表。

DECLARE @MyOrderTotalsByYear TABLE
(
  orderyear INT NOT NULL PRIMARY KEY,
  qty       INT NOT NULL
);

INSERT INTO @MyOrderTotalsByYear(orderyear, qty)
  SELECT
    YEAR(O.orderdate) AS orderyear,
    SUM(OD.qty) AS qty
  FROM Sales.Orders AS O
    JOIN Sales.OrderDetails AS OD
      ON OD.orderid = O.orderid
  GROUP BY YEAR(orderdate);

SELECT Cur.orderyear, Cur.qty AS curyearqty, Prv.qty AS prvyearqty
FROM @MyOrderTotalsByYear AS Cur
  LEFT OUTER JOIN @MyOrderTotalsByYear AS Prv
    ON Cur.orderyear = Prv.orderyear + 1;
GO

 表类型

SQL Server2008 引入的。通过创建表类型，可以把表的定义保存到数据库中，以后再定义表变量、存储过程和用户定义函数的输入参数时，可以将表类型作为表的定义而重用。

创建：

CREATE TYPE dbo.OrderTotalsByYear AS TABLE
(
  orderyear INT NOT NULL PRIMARY KEY,
  qty       INT NOT NULL
);

使用：

DECLARE @MyOrderTotalsByYear AS dbo.OrderTotalsByYear;

 

 

 

 

posted @ 2018-02-10 23:30  seaidler  阅读(176)  评论(0)    收藏  举报