第10章可编程对象2-动态SQL、用户定义函数、存储过程、触发器、错误处理

10.6 动态SQL

SQL Server允许用字符串来动态构造T-SQL代码的一个批处理，接着再执行这个批处理，这种功能称为动态SQL（dynamic SQL）。

两种执行动态SQL的方法：

使用 EXEC
使用 sp_executesql 存储过程

1、 EXEC 命令

是 T-SQL 最早提供的一种用于执行动态SQL的方法。输入既支持普通字符，也支持Unicode字符。

DECLARE @sql AS VARCHAR(100);
DECLARE @myname AS VARCHAR(20);
SET @myname = 'alex'
SET @sql = 'PRINT ''My name is '+ @myname + '.'';';
EXEC(@sql);
GO

注意提防SQL注入。

2、sp_executesql 存储过程

它支持输入和输出参数，因而更安全和灵活，且性能也比EXEC好，但只支持Unicode字符串作为其输入的批处理代码（即字符串外面用 N‘ ’包裹）。

它包含两个参数和一个参数赋值部分：

第一个参数@stmt：要运行的批处理代码的Unicode字符串
第二个参数@params：也是一个Unicode字符串，包含@stmt中所有输入和输出参数的声明。
参数赋值部分：为输入和输出参数指定值，各参数之间用逗号隔开。

DECLARE @sql AS NVARCHAR(100);
SET @sql = N' SELECT @myname,@age ';
EXEC sp_executesql
  @stmt = @sql,
  @params = N'@myname AS VARCHAR(20), @age AS INT',
  @myname = 'alex',
  @age = 18
GO

为了使用输出参数，只需要简单地在参数声明和参数赋值部分同时指定 OUTPUT 关键字。

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;                                  --将输出赋值给变量 @numrows

  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

使用OUT的实例

10.7 例程1--用户定义函数

例程（routine）是为了计算结果或执行任务而对代码进行封装的一种编程对象。SQLserver支持三种例程：

用户定义函数
存储过程
触发器

用户定义函数（UDF，user-defined function）有两种：标量UDF和表值UDF。

（表值UDF分为：内联表值函数和多语句表值函数，详见：SQL SERVER 用户自定义函数（UDF）深入解析）

UDF的最大优点是可以在查询中集成。标量UDF可以出现在查询中返回单个值得表达式处，表值UDF只能在查询的FORM子句中出现。

UDF不允许有任何副作用，即不能对数据库中的任何架构或数据进行修改。

USE TSQLFundamentals2008;
IF OBJECT_ID('dbo.fn_age') IS NOT NULL DROP FUNCTION dbo.fn_age;
GO

CREATE FUNCTION dbo.fn_age
(
  @birthdate AS DATETIME,
  @eventdate AS DATETIME
)
RETURNS INT
AS
BEGIN
  RETURN
    DATEDIFF(year, @birthdate, @eventdate)
    - CASE WHEN 100 * MONTH(@eventdate) + DAY(@eventdate)
              < 100 * MONTH(@birthdate) + DAY(@birthdate)
           THEN 1 ELSE 0
      END
END
GO

-- Test function
SELECT
  empid, firstname, lastname, birthdate,
  dbo.fn_age(birthdate, CURRENT_TIMESTAMP) AS age
FROM HR.Employees;

一个函数体内可以包含多个RETURN子句，也可以包含流程控制代码、计算代码等等。但是函数必须由一个RETURN子句返回一个值。

10.7 例程2--存储过程

存储过程可以有输入和输出参数，可以返回查询的结果集，也允许调用具有副作用的代码（即不但可以对数据进行修改，也可以对数据库架构进行修改）。

和使用普通代码相比，使用存储过程还可以获得如下好处：

封装逻辑处理
更好的控制安全性
整合错误处理
提高执行性能

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 = '20071001',
  @todate   = '20080101',
  @numrows  = @rc OUTPUT;

SELECT @rc AS numrows;
GO

10.7 例程3--触发器

触发器是一种特殊的存储过程，一种不能被显式执行，而必须依附于一个事件的过程。

SQL Server支持把触发器和两种类型的事件相关：

数据操作事件（如 INSERT）------ DML触发器
数据定义事件（如 CREATE TABLE）------ DDL触发器

1、DML触发器

SQL Server支持两种DML触发器：

AFTER 触发器：与之关联的事件完成后才触发，只能定义在持久化的表上。
INSTEAD OF 触发器：是为了代替与之关联的事件操作，可定义在持久化的表或视图上。

在触发器代码中，可以访问称为inserted和deleted的两个表：

inserted表：包含当执行 INSERT 和 UPDATE 语句时受影响行的新数据的镜像。
deleted表：包含当执行 DELETE 和 UPDATE 语句时受影响行的旧数据的镜像。

注意：不能在 'inserted' 表和 'deleted' 表中使用 text、ntext 或 image 列。

对于INSTEAD OF 触发器，inserted表和deleted表包含导致触发器触发的修改操作打算要影响的行。

（1）AFTER触发器

包括：INSERT、DELETE、UPDATE三种类型

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

2、DDL触发器

SQL Server 支持在两个作用域内创建 DDL触发器：

数据库作用域的事件（如 CREATE TABLE）
服务器作用域内的事件（如 CREATE DATABASE）

SQL Server 只支持AFTER类型的DDL触发器。

10.8 错误处理

1、TRY...CATCH 块

如果TRY块中的代码没有错误，会忽略CATCH块；如果TRY块中有错误，会执行CATCH块中的代码。

BEGIN TRY
  PRINT 10/2;
  PRINT 'No error';
END TRY
BEGIN CATCH
  PRINT 'Error';
END CATCH
GO
------以上代码结果为-------
--5
--No error
-----------------------------

BEGIN TRY
  PRINT 10/0;
  PRINT 'No error';
END TRY
BEGIN CATCH
  PRINT 'Error';
END CATCH
GO
------以上代码结果为-------
--error
-----------------------------

2、错误函数

通常，在CATCH块中进行的错误处理会涉及检查导致错误的原因，采取某种处理操作。SQL Server可以通过一组函数来反馈有关错误的信息。

ERROR_NUMBER：返回一个整数，代表错误的错误号
ERROR_MESSAGE：返回错误的消息文本。要得到错误号和错误消息的列表，可以查询 sys.messages 目录视图。
ERROR_SEVERITY和 ERROR_STATE：返回错误的严重级别和状态号

USE tempdb;
IF OBJECT_ID('dbo.Employees') IS NOT NULL DROP TABLE dbo.Employees;
CREATE TABLE dbo.Employees
(
  empid   INT         NOT NULL,
  empname VARCHAR(25) NOT NULL,
  mgrid   INT         NULL,
  CONSTRAINT PK_Employees PRIMARY KEY(empid),
  CONSTRAINT CHK_Employees_empid CHECK(empid > 0),
  CONSTRAINT FK_Employees_Employees
    FOREIGN KEY(mgrid) REFERENCES dbo.Employees(empid)
);
GO

-- Detailed Example
BEGIN TRY

  INSERT INTO dbo.Employees(empid, empname, mgrid)
    VALUES(1, 'Emp1', NULL);
  -- Also try with empid = 0, 'A', NULL

END TRY
BEGIN CATCH

  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');
 
END CATCH
GO

View Code

可创建一个存储过程，以封装可以重用的错误处理代码。

posted @ 2018-02-12 14:50 seaidler 阅读(163) 评论(0) 收藏举报

刷新页面返回顶部

第10章 可编程对象2-动态SQL、用户定义函数、存储过程、触发器、错误处理