Sqlserver(可编程对象)

最新推荐文章于 2022-11-06 22:52:21 发布

懒笔头

最新推荐文章于 2022-11-06 22:52:21 发布

阅读量563

点赞数

分类专栏： SQL

本文链接：https://blog.csdn.net/csdnsunyf/article/details/102670914

版权

SQL 专栏收录该内容

21 篇文章 0 订阅

订阅专栏

引言：

本章包括变量、批、流元素、游标和临时表，像用户定义函数、存储过程和触发器这样的例程，以及动态SQL。

1.1 变量

变量允许你在声明的同一批中l临时存储将来要使用的数据值，先简单介绍下批：批就是作为一个单元发送给SQL Server执行的一条或多条T-SQL语句。

可以使用DECLARE语句声明一个或多个变量，然后使用SET语句给单个变量赋值。

例如，下面的代码声明了一个名为@i的INT数据类型变量，并将其赋值为10.

DECLARE @i AS INT;

SET @i = 10;

SQL Server 2008 和SQL Server2012支持在同一条语句中声明并初始化，如下：

DECLARE @i AS INT = 10;

给标量变量赋值时，值必须是一个标量表达式的结果，该表达式可以是一个标量子查询。例如: 下面的代码声明了一个名为@empname的变量，并使用返回ID等于3的雇员姓名的标量子查询结果为其赋值。

DECLARE @empname AS NVARCHAR(31);

SET @empname = (SELECT firstname + N' ' + lastname
                FROM HR.Employees
                WHERE empid = 3);

SET语句一次只能操作一个变量，如果需要为多个属性赋值，你需要使用多个SET语句。当你需要使用相同行的多个属性值时，这会引起不必要的开销。例如下面的代码使用两个单独的SET语句将ID等于3的雇员的名和姓赋予了两个独立的变量。

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

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

SQL Server还支的非标准的赋值SELECT语句，它允许使用单个语句查询数据并分配来自同一行的多个值给多个变量。下面时一个这样的示例。

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

SELECT
  @firstname = firstname,
  @lastname  = lastname
FROM HR.Employees
WHERE empid = 3;

确定只有一个符合条件的行时，赋值SELECT具有可预测的行为。但是，请注意，如果查询包含多个符合条件的行，代码不会失败，每个符合条件的行都会赋值，并且访问每一行，当前行的值会覆盖变量中现有值，当SELECT语句赋值完成后，变量中的值是SQL发生访问最后一行的数据。例如，下面的赋值SELECT具有两个符合条件的行

DECLARE @empname AS NVARCHAR(31);

SELECT @empname = firstname + N' ' + lastname
FROM HR.Employees
WHERE mgrid = 2;

赋值SELECT完成后，变量中最终雇员信息依赖与SQL发生访问这些行的顺序，并且你无法控制该顺序。

由于SET语句要求使用一个标量子查询来从表中获取数据，因此它比赋值SELECT更安全。如果标量子查询返回了多个值，会运行失败。

1.2 批

批是由客户端应用程序作为一个单元发送给SQL Server执行的一条或多条T-SQL语句。SQL Server以批为单元进行分析(语法检查)、解析(检查引用对象和列是否存在)、权限检查和最佳化chu'li处理。

不要混淆事物和批。事物是工作的原子单元，一个批可以有多个事务，一个事务可以分成多个批提交。当事务中途被取消或回滚时，SQL Server会撤销自事务开始后发生的活动部分，而与批的开始无关。

客户端用程序编程接口(API),如ADO.NET为用户提供了提交一批代码到SQL Server执行的方法。SQL Server实用工具，如SQL Server Management Studio、SQLCMD和OSQL，提供了一个叫做GO的命令来提示结束一个批。注意，GO是一个客户端命令，而不是一个T-SQ务器命令

1.2.1 以批为单语法分析

批是作为一个单元分析和执行的命令组。成功分析成功，然后SQL Server将尝试执行该批。批中出现了语法错误，则整个批不会提交给SQL Server执行。例如，下面的代码有3个批，其中第二个批具有语法错误(第二个查询中是FOM，而不是FROM)

-- Valid batch
PRINT 'First batch';
USE TSQL2012;
GO
-- Invalid batch
PRINT 'Second batch';
SELECT custid FROM Sales.Customers;
SELECT orderid FOM Sales.Orders;
GO
-- Valid batch
PRINT 'Third batch';
SELECT empid FROM HR.Employees;
GO

由于第二个批具有语法错误，则整个批不会提交给SQL Server执行。第一和第三批因为通过了语法验证，因此提交执行。

1.2.2 批和变量

变量对于定义它的批而言是局部化的，如果尝试引用在另一个批中定义的变量，就会收到一条错误说该变量未定义。例如，下面的代码在一个批中声明了一个变量并输出了其内容，然后在另一个批中尝试输出其内容。

DECLARE @i AS INT = 10;
-- Succeeds
PRINT @i;
GO

-- Fails
PRINT @i;
GO

1.2.3 不能被组合在同一个批中的语句

以下语句不能与其他语句组合在同一个批中: CREATE DEFAULT、CREATE FUNCTION、CREATE PROCEDURE、CREATE RULE、CREATE SCHEMA、CREATE TRIGGER和CREATE VIEW。例如，下在的代码在同一个批中具有一个IF语句，后面跟着一个CREATE VIEW语句，因此是无效的。

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

1.2.4 以批为单元的解析

批是一个解析单元。这意味着检查对象和列是否存在发生在批级。在设计批的边界时，应牢记这个事实。当用户在同一批中将架构更改应用到对象并尝试操作对象数据时，SQL Server可能不知道它们的架构更改，并且因解析错误导致数据操作语句失败。下面通过一个例子来演示该问题，然后推荐一下最佳做法。

运行下面的代码，在当前数据库中创建一个叫做T1的表，其包含一个名为col1的列。

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

接下来，试图在同一个批中T1添加一个名为col2的列，并查询该新增列

ALTER TABLE dbo.T1 ADD col2 INT;
SELECT col1, col2 FROM dbo.T1;
GO

即使代码看起来是完全有效的，但是解析解析阶段批会失败，

在SELECT语句解析的时点，T1只有一列，引用col2会导致错误。避免此类问题的最佳做法就是将DDL和DML语句分隔到不同批次中，如下面示例。

ALTER TABLE dbo.T1 ADD col2 INT;
GO
SELECT col1, col2 FROM dbo.T1;
GO

1.2.5 GO n 选项

GO命令不是真正的T-SQL命令，他实际上是一个用于SQL Server客户端工具的命令，如SSMS，用于表示批的结束。此命令支持一个指示要执行批多少次的参数。要查看GO命令如何使用该参数工作，首先使用下面的代码创建一个T1表

IF OBJECT_ID('dbo.T1', 'U') IS NOT NULL DROP TABLE dbo.T1;
CREATE TABLE dbo.T1(col1 INT IDENTITY CONSTRAINT PK_T1 PRIMARY KEY);
GO

col1列con从标识属性自动获取值。注意，如果使用默认约束从一个序列对象生成值，那么该演示也是一样的。接下来，运行一下代码，屏蔽DML语句生成的指示多少行受影响的默认输出。

SET NOCOUNT ON;

最后，运行下面代码，定义一个具有INSERT DEFAULT VALUES语句的批，并执行批100次。

INSERT INTO dbo.T1 DEFAULT VALUES;
GO 100

SELECT * FROM dbo.T1;

此查询返回100行，col1中是1~100的数值

1.3 流元素

流元素允许用户控制代码流。T-SQL提供了流元素控制的基本形式，包括 IF... ELSE 和 WHILE元素。

1.3.1 IF... ELSE 流元素

IF... ELSE 元素允许用户基于一个谓词来控制代码流。如果谓词为TRUE，则执行指定的语句或语句块；如果谓词为FALSE或UNKNOWN，则可以选择执行其他语句或语句块。

例如：

IF YEAR(SYSDATETIME()) <> YEAR(DATEADD(day, 1, SYSDATETIME()))
  PRINT 'Today is the last day of the year.';
ELSE
  PRINT 'Today is not the last day of the year.';
GO

如果需要控制的流涉及两个以上情况，则可以嵌套 IF... ELSE元素。例如:

IF YEAR(SYSDATETIME()) <> YEAR(DATEADD(day, 1, SYSDATETIME()))
  PRINT 'Today is the last day of the year.';
ELSE
  IF MONTH(SYSDATETIME()) <> MONTH(DATEADD(day, 1, SYSDATETIME()))
    PRINT 'Today is the last day of the month but not the last day of the year.';
  ELSE 
    PRINT 'Today is not the last day of the month.';
GO

如果需要在IF或ELSE部分运行超过一条语句，那么需要使用一个语句块。可以使用BEGIN和END关键字标记语句块的边界。

例如：

IF DAY(SYSDATETIME()) = 1
BEGIN
  PRINT 'Today is the first day of the month.';
  PRINT 'Starting first-of-month-day process.';
  /* ... process code goes here ... */
  PRINT 'Finished first-of-month-day database process.';
END
ELSE
BEGIN
  PRINT 'Today is not the first day of the month.';
  PRINT 'Starting non-first-of-month-day process.';
  /* ... process code goes here ... */
  PRINT 'Finished non-first-of-month-day process.';
END

1.3.2 WHILE元素

T-SQL提供了WHILE元素，使用户能够在循环中执行代码。只要WHILE关键字后指定的谓词为TRUE，while 元素会重复执行一个语句或语句块。

示例：

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

如果在循环体中的某一点，想要跳出当前循环并继续执行出现在循环体后面的语句，应当使用break命令，例如:

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

在循环体中的某一点，如果想要跳过当前迭代中活动的剩余部分，并再次计算循环的谓词，那么应当使用 continue命令。例如:

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

1.3.3 使用 IF 和 WHILE的示例

SET NOCOUNT ON;
IF OBJECT_ID('dbo.Numbers', 'U') IS NOT NULL DROP TABLE dbo.Numbers;
CREATE TABLE dbo.Numbers(n INT NOT NULL PRIMARY KEY);
GO

DECLARE @i AS INT = 1;
WHILE @i <= 1000
BEGIN
  INSERT INTO dbo.Numbers(n) VALUES(@i);
  SET @i = @i + 1;
END
GO

1.4 游标

使用游标通常包括以下几个步骤:

基于查询声明游标
打开游标
从第一个游标记录中提取属性值给变量
遍历游标记录，直至到达游标的末尾(@@FETCH_STATUS函数值为0)；在循环的每次迭代中，从游标的当前记录中提取属性值给变量，并为当前值执行所需的处理
关闭游标
释放游标

示例:

SET NOCOUNT ON;

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

1.5 临时表

当需要临时在表中存储数据时，在某些情况下用户可能不想使用永久表。假设需要数据仅对当前会话可见，甚至是仅对当前批。举一个例子，假设需要在数据处理期间存储临时数据。

SQL Serverzh支持3种临时表: 本地临时表、全局临时表和表变量，在某些情况下，你可能会发现临时表比使用永久表更方便。

1.5.1 本地临时表

可以通过将数字符号作为命名前缀来创建一个本地临时表，如 #T1。3种类型的临时表都在 tempdb数据库中创建。

示例：

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

CREATE TABLE #MyOrderTotalsByYear
(
  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 dbo.#MyOrderTotalsByYear AS Cur
  LEFT OUTER JOIN dbo.#MyOrderTotalsByYear AS Prv
    ON Cur.orderyear = Prv.orderyear + 1;
GO

-- Try accessing the table from another session
SELECT orderyear, qty FROM dbo.#MyOrderTotalsByYear;

1.5.2 全局临时表

当创建了全局临时表，它对所有的会话可见。当创建全局临时表的会话断开了连接并且没有对该表的活动引用时，SQL Server自动销毁该表。可以通过以两个数字符号为前缀的min命名方式创建全局临时表，如##T1.

-- Global Temporary Tables
CREATE TABLE dbo.##Globals
(
  id  sysname     NOT NULL PRIMARY KEY,
  val SQL_VARIANT NOT NULL
);

-- Run from any session
INSERT INTO dbo.##Globals(id, val) VALUES(N'i', CAST(10 AS INT));

-- Run from any session
SELECT val FROM dbo.##Globals WHERE id = N'i';

1.5.3 表变量

表变量在某些方面类似于本地临时表，并且在其他方面有所不同。声明表变量与声明其他变量很想象，也是使用 DECLARE语句。

就像本地临时表一样，表变量在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

1.5.4 表类型

SQL Server 2008 和 SQL Server2012支持表类型，当创建了表类型，就会在数据库中保留表的定义，可以在以后重新使用它作为表变量的表定义，以及作为存储过程和用户定义函数的输入参数。

例如，下面的代码在当前数据库中创建了一个名为 dbo.OrderTotalsByYear的表类型。

-- Table Types
IF TYPE_ID('dbo.OrderTotalsByYear') IS NOT NULL
  DROP TYPE dbo.OrderTotalsByYear;

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

表类型创建之后，每当需要声明一个基于该表类型定义的表变量时，就不需要重复该代码，而只需要简单的将 dbo.OrderTotalsByYear 指定为变量的类型，像下面这样。

-- Use table type
DECLARE @MyOrderTotalsByYear AS dbo.OrderTotalsByYear;

1.6 动态SQL

SQL Server允许以一个字符串来构造一个T-SQL批，然后执行该批处理，此功能成为动态SQL。SQL Server提供了两种执行动态SQL的方法：使用EXEC(EXECUTE的缩写)和使用sp_executesql存储过程。

1.6.1 EXEC 命令

EXEC 命令是T-SQL 提供的执行动态SQL的原始技术。EXEC 接受括号内的一个字符串作为输入并执行字符串中的代码批。EXEC支持正则和Unicode字符作为输入。

示例:

DECLARE @sql AS VARCHAR(100);
SET @sql = 'PRINT ''This message was printed by a dynamic SQL batch.'';';
EXEC(@sql);
GO

1.6.2 sp_executesql 存储过程

sp_executesql 存储过程是在 EXEC命令之后引入的。从其具有接口的意义上看，它更安全、更灵活；也就是说，它支持输入和输出参数。

注意：与 EXEC不同，sp_executesql仅支持Unicode字符串作为输入的代码批。

sp_executesql 过程具有两个输入参数和一个任务部分。在名为 @stmt的第一个参数中，指定希望运行的代码批的Unicode字符串，在名为@params的第二个参数中，提供输入和输出参数声明的Unicode字符串。然后指定输入和输出参数任务，任务之间逗号分隔。

示例：

DECLARE @sql AS NVARCHAR(100);

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

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

1.6.3 使用动态SQL的 PIVOT

PIVOT运算符的静态查询示例：

-- Static PIVOT
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;

对于静态查询，必须提前知道在PIVOT运算符的IN子句中指定哪些值(此处是订单年度)。这意味着每年都需要修改代码。

相反，可以从数据中查询唯一的订单年度，构造一个基于所查询到的订单年度的动态SQL代码批，修改如下：

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 sys.sp_executesql @stmt = @sql;
GO