T-SQL编程 #

T-SQL扩展特性：
├── 变量声明和使用
├── 流程控制语句
├── 错误处理机制
├── 内置函数
├── 存储过程
├── 触发器
└── 动态SQL

-- 声明变量
DECLARE @name NVARCHAR(50);
DECLARE @age INT;
DECLARE @salary DECIMAL(10,2) = 5000.00;  -- 声明并赋值

-- 同时声明多个变量
DECLARE 
    @id INT = 1,
    @name NVARCHAR(50) = 'John',
    @active BIT = 1;

-- 使用SET赋值
SET @name = 'Jane';

-- 使用SELECT赋值
SELECT @name = name FROM users WHERE id = 1;

-- 使用变量
SELECT * FROM users WHERE name = @name;

-- 打印变量
PRINT @name;
PRINT 'Name: ' + @name;
PRINT 'Age: ' + CAST(@age AS VARCHAR);

-- 声明表变量
DECLARE @temp_users TABLE (
    id INT,
    name NVARCHAR(50),
    email VARCHAR(100)
);

-- 插入数据
INSERT INTO @temp_users
SELECT id, name, email FROM users WHERE status = 1;

-- 使用表变量
SELECT * FROM @temp_users;

-- 表变量限制：
-- 1. 不能有索引
-- 2. 不能作为存储过程参数（除非使用表类型）
-- 3. 作用域限制在批处理内

-- 全局变量（系统变量）

-- 当前版本
SELECT @@VERSION;

-- 受影响的行数
INSERT INTO users (name) VALUES ('Test');
SELECT @@ROWCOUNT AS '受影响行数';

-- 最后插入的标识值
INSERT INTO users (name) VALUES ('Test');
SELECT @@IDENTITY AS '最后ID';
SELECT SCOPE_IDENTITY() AS '当前作用域ID';

-- 事务计数
SELECT @@TRANCOUNT;

-- 错误号
SELECT @@ERROR;

-- 服务器名称
SELECT @@SERVERNAME;

-- 服务名称
SELECT @@SERVICENAME;

-- 当前语言
SELECT @@LANGUAGE;

-- 日期格式
SELECT @@DATEFIRST;  -- 一周的第一天

-- 基本IF语句
DECLARE @age INT = 25;

IF @age >= 18
    PRINT 'Adult';
ELSE
    PRINT 'Minor';

-- 多条件IF
IF @age < 18
    PRINT 'Minor';
ELSE IF @age < 65
    PRINT 'Adult';
ELSE
    PRINT 'Senior';

-- 使用BEGIN...END块
IF EXISTS (SELECT 1 FROM users WHERE id = 1)
BEGIN
    PRINT 'User exists';
    UPDATE users SET status = 1 WHERE id = 1;
END
ELSE
BEGIN
    PRINT 'User not found';
    INSERT INTO users (name) VALUES ('New User');
END

-- WHILE循环
DECLARE @i INT = 1;

WHILE @i <= 10
BEGIN
    PRINT 'Iteration: ' + CAST(@i AS VARCHAR);
    SET @i = @i + 1;
END

-- 使用BREAK和CONTINUE
DECLARE @i INT = 0;

WHILE @i < 10
BEGIN
    SET @i = @i + 1;
    
    IF @i = 5
        CONTINUE;  -- 跳过本次迭代
    
    IF @i = 8
        BREAK;  -- 退出循环
    
    PRINT @i;
END

-- 简单CASE
SELECT 
    name,
    CASE status
        WHEN 1 THEN 'Active'
        WHEN 0 THEN 'Inactive'
        ELSE 'Unknown'
    END AS status_text
FROM users;

-- 搜索CASE
SELECT 
    name,
    salary,
    CASE 
        WHEN salary >= 6000 THEN 'High'
        WHEN salary >= 4000 THEN 'Medium'
        ELSE 'Low'
    END AS salary_level
FROM users;

-- CASE在ORDER BY中
SELECT * FROM users
ORDER BY 
    CASE status
        WHEN 1 THEN 1
        WHEN 0 THEN 2
        ELSE 3
    END;

-- CASE在UPDATE中
UPDATE users
SET status = CASE 
    WHEN salary >= 5000 THEN 1
    ELSE 0
END;

-- 等待指定时间
WAITFOR DELAY '00:00:05';  -- 等待5秒
PRINT 'Waited 5 seconds';

-- 等待到指定时间
WAITFOR TIME '14:30:00';  -- 等待到14:30
PRINT 'It is now 14:30';

-- 带超时的查询
BEGIN
    WAITFOR (SELECT * FROM users FOR XML), TIMEOUT 5000;
END

-- GOTO语句（不推荐使用）
DECLARE @i INT = 1;

StartLoop:
PRINT @i;
SET @i = @i + 1;

IF @i <= 5
    GOTO StartLoop;

PRINT 'Loop finished';

-- 基本错误处理
BEGIN TRY
    SELECT 1/0;  -- 除零错误
END TRY
BEGIN CATCH
    PRINT 'Error occurred';
    PRINT 'Error Number: ' + CAST(ERROR_NUMBER() AS VARCHAR);
    PRINT 'Error Message: ' + ERROR_MESSAGE();
    PRINT 'Error Severity: ' + CAST(ERROR_SEVERITY() AS VARCHAR);
    PRINT 'Error State: ' + CAST(ERROR_STATE() AS VARCHAR);
    PRINT 'Error Line: ' + CAST(ERROR_LINE() AS VARCHAR);
    PRINT 'Error Procedure: ' + ISNULL(ERROR_PROCEDURE(), 'N/A');
END CATCH

-- 错误处理函数
BEGIN TRY
    -- 可能出错的代码
    INSERT INTO users (id, name) VALUES (1, 'Test');
END TRY
BEGIN CATCH
    DECLARE @ErrorMessage NVARCHAR(4000);
    DECLARE @ErrorSeverity INT;
    DECLARE @ErrorState INT;
    
    SELECT 
        @ErrorMessage = ERROR_MESSAGE(),
        @ErrorSeverity = ERROR_SEVERITY(),
        @ErrorState = ERROR_STATE();
    
    -- 重新抛出错误
    RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState);
END CATCH

-- 使用RAISERROR
RAISERROR('This is an error message', 16, 1);

-- 带参数的RAISERROR
RAISERROR('User %s not found with ID %d', 16, 1, 'John', 1);

-- 使用sp_addmessage创建自定义错误消息
EXEC sp_addmessage 
    @msgnum = 50001,
    @severity = 16,
    @msgtext = 'Custom error: User %s cannot be deleted';

-- 使用自定义错误消息
RAISERROR(50001, 16, 1, 'John');

-- 使用THROW（SQL Server 2012+）
THROW 50001, 'Custom error message', 1;

-- 重新抛出错误
BEGIN TRY
    SELECT 1/0;
END TRY
BEGIN CATCH
    THROW;  -- 重新抛出原始错误
END CATCH

-- THROW vs RAISERROR
-- THROW：总是设置@@ERROR，不需要格式化
-- RAISERROR：可以格式化消息，需要指定严重级别

-- 事务错误处理
BEGIN TRY
    BEGIN TRANSACTION;
    
    INSERT INTO users (name) VALUES ('User1');
    INSERT INTO users (name) VALUES ('User2');
    -- 可能出错的操作
    
    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;
    
    PRINT 'Transaction rolled back: ' + ERROR_MESSAGE();
END CATCH

-- 使用EXEC执行动态SQL
DECLARE @sql NVARCHAR(1000);
SET @sql = 'SELECT * FROM users';
EXEC(@sql);

-- 拼接字符串
DECLARE @table_name NVARCHAR(50) = 'users';
SET @sql = 'SELECT * FROM ' + @table_name;
EXEC(@sql);

-- 使用sp_executesql（推荐）
DECLARE @sql NVARCHAR(1000);
DECLARE @param_def NVARCHAR(500);
DECLARE @user_id INT = 1;

SET @sql = N'SELECT * FROM users WHERE id = @id';
SET @param_def = N'@id INT';

EXEC sp_executesql @sql, @param_def, @id = @user_id;

-- 返回值
DECLARE @sql NVARCHAR(1000);
DECLARE @param_def NVARCHAR(500);
DECLARE @count INT;

SET @sql = N'SELECT @cnt = COUNT(*) FROM users WHERE status = @status';
SET @param_def = N'@status BIT, @cnt INT OUTPUT';

EXEC sp_executesql @sql, @param_def, @status = 1, @cnt = @count OUTPUT;

PRINT 'Count: ' + CAST(@count AS VARCHAR);

-- 不安全的方式（SQL注入风险）
DECLARE @name NVARCHAR(50) = "'; DROP TABLE users; --";
DECLARE @sql NVARCHAR(1000);
SET @sql = 'SELECT * FROM users WHERE name = ''' + @name + '''';
EXEC(@sql);  -- 危险！

-- 安全的方式（参数化）
DECLARE @name NVARCHAR(50) = "'; DROP TABLE users; --";
DECLARE @sql NVARCHAR(1000);
SET @sql = N'SELECT * FROM users WHERE name = @safe_name';
EXEC sp_executesql @sql, N'@safe_name NVARCHAR(50)', @safe_name = @name;

-- 使用QUOTENAME
DECLARE @table_name NVARCHAR(50) = 'users';
DECLARE @sql NVARCHAR(1000);
SET @sql = N'SELECT * FROM ' + QUOTENAME(@table_name);
EXEC(@sql);

-- GO分隔批处理
SELECT * FROM users;
GO

SELECT * FROM orders;
GO

-- GO可以指定执行次数
SELECT GETDATE();
GO 5  -- 执行5次

-- CREATE PROCEDURE必须是批处理第一条语句
-- 错误
SELECT * FROM users;
CREATE PROCEDURE usp_test AS SELECT 1;
GO

-- 正确
SELECT * FROM users;
GO
CREATE PROCEDURE usp_test AS SELECT 1;
GO

-- 这是单行注释
SELECT * FROM users;  -- 行尾注释

/*
 * 这是多行注释
 * 可以跨越多行
 */
SELECT * FROM users;

T-SQL编码规范：
├── 使用有意义的变量名
├── 添加适当的注释
├── 使用一致的缩进
├── 使用BEGIN...END块
├── 使用错误处理
├── 避免使用GOTO
├── 使用参数化查询
└── 使用SET NOCOUNT ON

-- 使用SET NOCOUNT ON
CREATE PROCEDURE usp_example
AS
BEGIN
    SET NOCOUNT ON;  -- 减少网络流量
    
    -- 操作
    SELECT * FROM users;
END

-- 避免使用游标
-- 使用集合操作替代
-- 差
DECLARE cur CURSOR FOR SELECT id FROM users;
-- 好
UPDATE users SET status = 1 WHERE status = 0;