笔记-Microsoft SQL Server 2008技术内幕：T-SQL语言基础-10 可编程对象

关于批处理

下列语句不能在同一批处理中和其他语句同时编译：CREATE DEFAULT、CREATE FUNCTION、CREATE PROCEDURE、CREATE RULE、CREATE SCHEMA、CREATE TRIGGER及CREATE VIEW。例如，以下代码包含一个IF语句，之后在同一批处理中跟着一个CREATE VIEW语句，SQL Server将会报错：

IF OBJECT_ID('Sales.MyView', 'V') IS NOT NULL DROP VIEW Sales.MyView;

CREATE VIEW Sales.MyView
AS

SELECT YEAR(orderdate) AS orderyear, COUNT(*) AS numorders
FROM Sales.Orders
GROUP BY YEAR(orderdate);
GO

 应该改为：

IF OBJECT_ID('Sales.MyView', 'V') IS NOT NULL DROP VIEW Sales.MyView;
GO

CREATE VIEW Sales.MyView
AS

SELECT YEAR(orderdate) AS orderyear, COUNT(*) AS numorders
FROM Sales.Orders
GROUP BY YEAR(orderdate);
GO

 GO n 选项，n表示批处理需要执行的次数，比如：

INSERT INTO dbo.T1 DEFAULT VALUES;
GO 100

游标

基本代码：

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;

临时表

局部临时表，在命名时以单个#作为前缀，例如#T1。全局临时表，在命名时用两个#作为前缀，如##T1。

动态SQL

DECLARE @sql AS NVARCHAR(100);

SET @sql = N'SELECT orderid, custid, empid, orderdate
FROM Sales.Orders
WHERE orderid = @orderid;';

EXEC sp_executesql
  @stmt = @sql,
  @params = N'@orderid AS INT',
  @orderid = 10248;
GO

 带有输出参数的sp_executesql，

DECLARE @Counts TABLE
(
  schemaname sysname NOT NULL,
  tablename sysname NOT NULL,
  numrows INT NOT NULL,
  PRIMARY KEY(schemaname, tablename)
);

DECLARE
  @sql AS NVARCHAR(350),
  @schemaname AS sysname,
  @tablename  AS sysname,
  @numrows    AS INT;

DECLARE C CURSOR FAST_FORWARD FOR
  SELECT TABLE_SCHEMA, TABLE_NAME
  FROM INFORMATION_SCHEMA.TABLES;

OPEN C

FETCH NEXT FROM C INTO @schemaname, @tablename;

WHILE @@fetch_status = 0
BEGIN
  SET @sql =
    N'SET @n = (SELECT COUNT(*) FROM '
    + QUOTENAME(@schemaname) + N'.'
    + QUOTENAME(@tablename) + N');';

  EXEC sp_executesql
    @stmt = @sql,
    @params = N'@n AS INT OUTPUT',
    @n = @numrows OUTPUT;

  INSERT INTO @Counts(schemaname, tablename, numrows)
    VALUES(@schemaname, @tablename, @numrows);

  FETCH NEXT FROM C INTO @schemaname, @tablename;
END

CLOSE C;

DEALLOCATE C;

SELECT schemaname, tablename, numrows
FROM @Counts;
GO

 在PIVOT中使用动态SQL。先看静态的PIVOT SQL：

SELECT *
FROM (SELECT shipperid, YEAR(orderdate) AS orderyear, freight
      FROM Sales.Orders) AS D
  PIVOT(SUM(freight) FOR orderyear IN([2006],[2007],[2008])) AS P;

 动态的SQL如下：

DECLARE
  @sql       AS NVARCHAR(1000),
  @orderyear AS INT,
  @first     AS INT;

DECLARE C CURSOR FAST_FORWARD FOR
  SELECT DISTINCT(YEAR(orderdate)) AS orderyear
  FROM Sales.Orders
  ORDER BY orderyear;

SET @first = 1;

SET @sql = N'SELECT *
FROM (SELECT shipperid, YEAR(orderdate) AS orderyear, freight
      FROM Sales.Orders) AS D
  PIVOT(SUM(freight) FOR orderyear IN(';

OPEN C

FETCH NEXT FROM C INTO @orderyear;

WHILE @@fetch_status = 0
BEGIN
  IF @first = 0
    SET @sql = @sql + N','
  ELSE
    SET @first = 0;

  SET @sql = @sql + QUOTENAME(@orderyear);

  FETCH NEXT FROM C INTO @orderyear;
END

CLOSE C;

DEALLOCATE C;

SET @sql = @sql + N')) AS P;';

EXEC sp_executesql @stmt = @sql;
GO

例程

SQL Server支持三种例程：用户定义函数、存储过程、触发器。

存储过程：

USE TSQLFundamentals2008;
IF OBJECT_ID('Sales.usp_GetCustomerOrders', 'P') IS NOT NULL
  DROP PROC Sales.usp_GetCustomerOrders;
GO

CREATE PROC Sales.usp_GetCustomerOrders
  @custid   AS INT,
  @fromdate AS DATETIME = '19000101',
  @todate   AS DATETIME = '99991231',
  @numrows  AS INT OUTPUT
AS
SET NOCOUNT ON;

SELECT orderid, custid, empid, orderdate
FROM Sales.Orders
WHERE custid = @custid
  AND orderdate >= @fromdate
  AND orderdate < @todate;

SET @numrows = @@rowcount;
GO

DECLARE @rc AS INT;

EXEC Sales.usp_GetCustomerOrders
  @custid   = 1, -- Also try with 100
  @fromdate = '20070101',
  @todate   = '20080101',
  @numrows  = @rc OUTPUT;

SELECT @rc AS numrows;
GO

触发器：

CREATE TRIGGER trg_T1_insert_audit ON dbo.T1 AFTER INSERT
AS
SET NOCOUNT ON;

INSERT INTO dbo.T1_Audit(keycol, datacol)
  SELECT keycol, datacol FROM inserted;
GO

错误处理

基本代码：

BEGIN TRY
  PRINT 10/2;
  PRINT 'No error';
END TRY
BEGIN CATCH
  PRINT 'Error';
END CATCH
GO

下面是一个封装了错误处理代码的存储过程：

IF OBJECT_ID('dbo.usp_err_messages', 'P') IS NOT NULL
  DROP PROC dbo.usp_err_messages;
GO

CREATE PROC dbo.usp_err_messages
AS
SET NOCOUNT ON;

IF ERROR_NUMBER() = 2627
BEGIN
  PRINT 'Handling PK violation...';
END
ELSE IF ERROR_NUMBER() = 547
BEGIN
  PRINT 'Handling CHECK/FK constraint violation...';
END
ELSE IF ERROR_NUMBER() = 515
BEGIN
  PRINT 'Handling NULL violation...';
END
ELSE IF ERROR_NUMBER() = 245
BEGIN
  PRINT 'Handling conversion error...';
END
ELSE
BEGIN
  PRINT 'Handling unknown error...';
END

PRINT 'Error Number  : ' + CAST(ERROR_NUMBER() AS VARCHAR(10));
PRINT 'Error Message : ' + ERROR_MESSAGE();
PRINT 'Error Severity: ' + CAST(ERROR_SEVERITY() AS VARCHAR(10));
PRINT 'Error State   : ' + CAST(ERROR_STATE() AS VARCHAR(10));
PRINT 'Error Line    : ' + CAST(ERROR_LINE() AS VARCHAR(10));
PRINT 'Error Proc    : ' + COALESCE(ERROR_PROCEDURE(), 'Not within proc');
GO