T-SQL基础(六)之可编程对象
小编:啊南 233阅读 2020.11.30
-- 声明变量 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;
点击此处,查看有关类型的更多内容。
动态执行SQLSQL Server中可以使用两种方式来执行动态SQL:EXEC命令与sql_executesql存储过程。
EXECEXEC是T-SQL提供的执行动态SQL的原始技术,接收一个字符串作为输入并执行字符串中的语句:
USE WJChi; ? EXEC('SELECT * FROM dbo.UAddress');
EXEC支持正则与Unicode字符作为输入。
sql_executesqlsql_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;存储过程
存储过程与函数有相似之处,如都体现了封装的思想,但存储过程可以执行更为复杂的逻辑,可以有多个返回值。创建存储过程语句如下:
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)
相关推荐
- SQL Server 常用函数 1.获取当前时间--GetDate()2.DatePart() 函数3.字符串截取--substring三个参数,第一个参数需要截取的,第二个参数,截取字符串的起始位置(起始位置是1),第三个参数需要截取字符串的长度。4.日期增减函数-DateAdd三个参数,第一参数合法的日期表达式,第二个参…
- 3DMAX提示和技巧 本主题标识使用 Civil View 的一些重要提示和技巧。常规使用屏幕分辨率至少为 1280x1024 的 Civil View。低于此分辨率时,一些面板将占用过多屏幕空间。 将视口设置为线框显示以达到最佳性能。 要尽可能简化用户界面,请在单个视口中工作并关闭 3ds Max 命令面…