专栏首页me的随笔T-SQL基础(六)之可编程对象

T-SQL基础(六)之可编程对象

变量

-- 声明变量
DECLARE @variable_name [AS] variable_type;
-- 变量赋值
SET @variable_name = variable_value;

示例如下:

DECLARE @age INT;
-- SET一次只能操作一个变量
SET @age = 26;

T-SQL提供了使用SELECT语句来给变量赋值的扩展功能:

SELECT @age = 30;

也可以使用子查询来给变量赋值:

USE WJChi;
​
SET @age =
(
    SELECT Age FROM dbo.UserInfo WHERE Name = '雪飞鸿'
);

注意,上述SET语句中的子查询必须只能返回标量,否则会报错,示例如下:

USE WJChi;
​
SET @age =
(
    SELECT Age FROM dbo.UserInfo
);

执行报错:

子查询返回的值不止一个。当子查询跟随在 =、!=、<、<=、>、>= 之后,或子查询用作表达式时,这种情况是不允许的。

批是一条或多条被客户端作为整体发送给SQL Server进行执行的T-SQL语句,SQL Server以GO命令来标识一个批的结束,注意,GO语句不能使用分号结尾。SQL Server以批为单位进行词法、语法分析及语句执行等工作。一个批中的错误不会影响另一个批中语句的执行,因为不同的批在逻辑上彼此独立,不同批中包含的语句互相独立,彼此互不影响

批是一个解析单元,因此,即便在同一个批中修改了表结构,然后执行增删改查操作会引发解析错误,因为在同一批中的增删改查语句并不知道表结构已发生了变化。

GO n:表示执行n次批中的语句,如:

USE WJChi;
​
SELECT * FROM dbo.UserInfo;
GO 5

流程控制

IF...ELSE...

句式结构如下:

IF condition
BEGIN
    -- do something
END
ELSE IF condition
BEGIN
    -- do something
END
ELSE
BEGIN
    -- do something
END;

IF...ELSE...支持嵌套

WHILE

句式结构如下:

WHILE condition
BEGIN
    -- do something
END;

TRY...CATCH... & 错误处理

句式结构如下:

BEGIN TRY
    -- do something
END TRY
BEGIN CATCH
    -- do something
END CATCH;

SQL Server提供了一组描述错误的函数:

函数

作用

ERROR_NUMBER()

获取错误编号

ERROR_MESSAGE()

获取错误的文本信息

ERROR_SEVERITY()

获取错误严重级别

ERROR_STATE()

获取错误状态

ERROR_LINE()

获取错误发生行号

ERROR_PROCEDURE()

获取错误发生的过程名

也可以通过语句:

SELECT * FROM sys.messages;

来获取错误相关信息。可以使用THROW语句来抛出错误。

其它

RETURN、CONTINUE、BREAK、WAITFOR、GOTO

更多详细内容,参考微软官方文档:Control-of-Flow

临时表

SQL Server支持三种临时表:本地临时表、全局临时表和表变量。这三种临时表创建后都存储在tempdb数据库中。

本地临时表

创建本地临时表的方式不普通的数据表相同,但本地临时表仅在它被创建的会话中可见,会话结束后,临时表也会被销毁。

临时表以#开头,如:#UserInfo。临时表中的数据存储在磁盘中。

全局临时表

与本地临时表最大的不同是:全局临时表对所有会话可见,当全局临时表不在被任何会话引用时,会被SQL Server销毁。

全局临时表以##开头,如:##UserInfo

可通过语句:

SELECT * FROM tempdb..sysobjects WHERE name LIKE '%temp%'

来查看创建的临时表信息:

表变量

表变量的声明与普通变量类似,使用DECLARE语句。表变量只在创建它的会话中可见,且只对当前批可见。

一个显式事务回滚,事务中对临时表的修改也会回滚,但对已完成的表变量修改,则不会回滚。数据量较少时建议使用表变量,数据量较大时推荐使用临时表。

表变量 vs 临时表

表变量与临时表类似,但二者有所区别临时表更多的强调它是数据表,表变量着重点则在于变量上

表类型

当创建了表类型,就会在数据库中保留表的定义,可以复用它创建表变量,也可作为存储过程和自定义函数的输入参数。

CREATE TYPE TableType AS TABLE
(
    Id INT PRIMARY KEY
);
​
DECLARE @t TableType;

删除表类型

DROP TYPE TableType;

点击此处,查看有关类型的更多内容。

动态执行SQL

SQL Server中可以使用两种方式来执行动态SQL:EXEC命令与sql_executesql存储过程。

EXEC

EXEC是T-SQL提供的执行动态SQL的原始技术,接收一个字符串作为输入并执行字符串中的语句:

USE WJChi;
​
EXEC('SELECT * FROM dbo.UAddress');

EXEC支持正则与Unicode字符作为输入。

sql_executesql

sql_executesql存储过程在EXEC命令之后引入,与EXEC相比,sql_executesql更安全,更灵活,可以支持输入与输出参数。但,sql_executesql只支持Unicode字符作为输入。

ADO.NET发送到SQL Server的参数化查询语句就是使用sql_executesql来执行的,参数化查询可以有效避免SQL注入攻击。示例如下:

exec sp_executesql N'SELECT * FROM dbo.UAddress WHERE ShortAddress=@sd AND LongAddress=@ld',N'@sd nvarchar(4000),@ld nvarchar(4000)',@sd=N'河南省',@ld=N'河南省郑州市'

函数 & 存储过程 & 触发器

函数

使用函数的目的在于计算逻辑的封装及代码的复用。SQL Server中函数返回值分为:标量与表值两种。

创建函数的CREATE FUNCTION语句必须是当前批中的第一条语句,否则报错:'CREATE FUNCTION' 必须是查询批次中的第一个语句。

创建标量值函数:

CREATE FUNCTION dbo.GetSum
(
    @left AS INT,
    @right AS INT
)
RETURNS INT
AS
BEGIN
    RETURN @left+@right;
END;

创建表值函数:

CREATE FUNCTION dbo.TableFunc
(
    @name AS VARCHAR(8)
)
RETURNS TABLE
AS
RETURN
(
    SELECT *
    FROM dbo.UserInfo
    WHERE Name = @name
);

修改函数定义,将创建函数语句中的CREATE换为ALTER即可。如下所示:

ALTER FUNCTION [dbo].[TableFunc]
(
    @name AS VARCHAR(8)
)
RETURNS TABLE
AS
    RETURN 
    (
        SELECT * FROM dbo.UserInfo WHERE Name=@name
    );

删除函数:

DROP FUNCTION function_name;

SQL Server内置常用函数

存储过程

存储过程与函数有相似之处,如都体现了封装的思想,但存储过程可以执行更为复杂的逻辑,可以有多个返回值。创建存储过程语句如下:

CREATE PROCEDURE HumanResources.uspGetEmployeesTest2   
    @LastName nvarchar(50),   
    @FirstName nvarchar(50)   
AS   
​
    SET NOCOUNT ON;  
    SELECT FirstName, LastName, Department  
    FROM HumanResources.vEmployeeDepartmentHistory  
    WHERE FirstName = @FirstName AND LastName = @LastName  
    AND EndDate IS NULL;  
GO  

更多详细内容,请参阅:存储过程(数据库引擎)

⚠️存储过程移植比较困难

触发器

触发器是特殊的存储过程,在满足条件时(事件被触发),会隐式执行,从这个角度讲,触发器会增加复杂性。

触发器个人接触和使用较少,这里不多介绍。详细内容可参考:CREATE TRIGGER (Transact-SQL)

小结

本章内容较为杂乱,但也都是平时编写T-SQL代码时较为常用的内容。

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • T-SQL基础(五)之增删改

    在前面的文章中对T-SQL的查询做了基本总结,接下来我们看下SQL中的另外一个常用操作——数据的修改。

    雪飞鸿
  • T-SQL基础(五)之增删改

    在前面的文章中对T-SQL的查询做了基本总结,接下来我们看下SQL中的另外一个常用操作——数据的修改。

    雪飞鸿
  • T-SQL基础(一)之简单查询

    SQL: Structured Query Language,结构化查询语言,是一种在关系型数据库中用于管理数据的标准语言。SQL是一种声明式编程语言,即只需表...

    雪飞鸿
  • 3分钟短文 | Laravel 获取模型查询生成的SQL语句

    在程序开发阶段,我们关注于业务逻辑,实现功能。而laravel提供了非常好的 debug 支持,只需在 env 文件内指定 debug = true ,就可以在...

    程序员小助手
  • 【程序猿硬核科普】学习使用Markdown语法写博客

    Markdown 是一种轻量级标记语言,它允许人们使用易读易写的纯文本格式编写文档,在 2004 由约翰·格鲁伯(英语:John Gruber)创建。Markd...

    浩Coding
  • PL/SQL --> 动态SQL

    使用动态SQL是在编写PL/SQL过程时经常使用的方法之一。很多情况下,比如根据业务的需要,如果输入不同查询条件,则生成不同的执行

    Leshami
  • protobuf 序列化到文件及反序列化

    游戏中,将对局的数据保留下来,用于对局回顾及debug等用途,由于协议采用PB,故以二进制的pb格式写入文件,在使用该对局内容的时候,按照格式反序列化出来用于播...

    changan
  • MySQL中特别实用的几种SQL语句送给大家

    高能预警,这是一篇干货满满的MySQL技术文章,总有一天,你必然会用到,记得收藏! -- 来自一位被技术经理毒打多年的程序员的忠告

    陈哈哈
  • “下沉市场”淘宝新用户收割超过拼多多?已婚女性更爱看直播?孩子快上小学了?

    下沉市场电商用户消费结构逐渐从金字塔向橄榄型转变,低端山寨商品逐渐被中端品牌商品取代

    iCDO互联网数据官
  • Elasticsearch学习搜索的笔记

    1.普通查询(全文搜索) 查询name=Smith的文档数据 GET /megacorp/employee/_search { "query" : { ...

    苦咖啡

扫码关注云+社区

领取腾讯云代金券