sqlserver 脚本和批处理指令小结[MSSQL防范]

一.脚本底子
1.USE语句
设置当前数据库.
2.声明变量
语法:DECLARE @变量名 变量范例
在声明变量后,给变量赋值之前,变量的值为NULL.
将系统函数赋给声明的变量,这个办法可以使我们能更安全地利用值,该值只有人为地改变时它才变更.假如直接利用系统函数本身,那么当它发生变更时,有
时会无法肯定其毕竟为什么,因为大大都系统函数值是由系统肯定的.这简单在你不预期的情形下招致系统改变了值,惹起不可预期的后果.
(1).给变量赋值
SET:当举行变量赋值是,该值已经知道是切当值大概是其他变量时,利用SET.
SELECT:当变量赋值基于一个查询时,利用SELECT.
(2).系统函数
SQL Server 2005中有30多个无参的系统函数,此中一些最重要的以下:
@@ERROR: 返回当前衔接下,最后履行的T-SQL语句的错误代码,如无错误返回0.
@@FETCH_STATUS: 和FETCH语句配合利用.
@@IDENTITY: 返回最后一句运行语句的、自动生成的标识值,作为最后INSERT大概SELECT INTO语句的后果.
@@ROWCOUNT: 返回最后一个语句影响的行数.
@@SERVERNAME: 返回脚本正在其上运行的本地服务的名字.
@@TRANCOUNT: 返回活动事件的数目,分外是针对当前衔接的事件的瓶颈程度.

二.批处理
(1).GO单独占一行.在同一行上,T-SQL语句不能在GO语句之前.
(2).全部语句从脚本开始处大概上一个GO语句开始编译,直到下一个GO语句大概脚本完毕,将这段代码编译到一个履行筹划中并彼此独立地送往服务器.前一
个履行筹划中发生错误,不会影响后一个履行筹划.
(3).GO不是一个T-SQL号令,只是被编辑工具辨认的号令.当编辑工具碰到GO,它把GO看做一个完毕批处理的标志,将其打包,然后作为一个独立单元发送到
服务器——不包含GO,服务器关于GO没有任何概念.
1.批处理中的错误
语法错误,运行时错误.
2.什么时刻利用批处理
(1).单独成批处理的语句
有几个号令必须单独成批处理,它们包含:
CREATE DEFAULT
CREATE PROCEDURE
CREATE RULE
CREATE TRIGGER
CREATE VIEW
假如想将这些语句中的任何一条和其他语句构成单独的一个脚本,那么需求采取一个GO语句将它们辨别断开,归入各自的批处理中.
(2).利用批处理成立优先级
利用批处理最坚固的例子是,当需求考虑语句履行的优先次序时,也就是说,需求一个任务在另一个任务开始前履行.
比方:
CREATE DATABASE Test
复制代码 代码以下:
CREATE TABLE TestTable
(
col1 INT,
col2 INT
)

履行语句,会发现生成的表没有在Test数据库中,而是在master数据库中（假如当前利用的数据库是系统数据库）.因为在履行脚本的时刻,利用的数据
库是系统数据库,该数据库是当前的,所以生成的表在系统数据库中.看起来,应当在成立表之前指定数据库Test.但是,这样仍旧存在问题.解析器试图校
验代码,发现我们用USE号令引用的数据库并不存在.缘由在于成立数据库的语句和成立表的语句写在一个批处理中,在履行该脚本之前,当然数据库还没有
成立.按照批处理的要求,我们将成立数据库和成立表的脚本用GO语句分为两个独立的批处理.精确代码以下:
复制代码 代码以下:
CREATE DATABASE Test
GO

USE Test
CREATE TABLE TestTable
(
col1 INT,
col2 INT
)

三.动态SQL:利用EXE号令生成代码
语法:EXEC/EXECUTE ({<字符串变量> | '<字面值号令字符串>'})
1.EXEC的作用域
真正的调用EXEC语句的行,拥有同该EXEC语句正在运行的批或历程中的其他代码相同的作用域.但是作为EXEC语句后果而被履行的代码,被认为是在它自
己的批中.
比方:
DECLARE @OutVar VARCHAR(50)
EXEC ('SELECT @OutVar = FirstName FROM Contact WHERE ContactID = 1')
这里系统会报错,指出必须声明变量@OutVar.因为EXEC的语句单独成为一个批处理,此中的变量不能和其外的作用域相沟通,只在这个批处理中有效.此
时,@OutVar的值为NULL.精确的写法以下:
EXEC ('DECLARE @OutVar VARCHAR(50)
SELECT @OutVar = FirstName FROM Contact WHERE ContactID = 1')
这里,我们看到两中差别的作用域,这两种作用域间不能彼此沟通.假如不采取外部机制,比方一个暂时表,我们就没有办法实目前内部作用域和外部作用域
之间传送信息.有一个例外的事情是可以在EXEC的区域内部呈现,并且也能在EXEC履行后被看到,这就是系统函数.因此,像@@ROWCOUNT这样的变量仍旧
可以被利用.
2.安全上下文和EXEC
当赋予某人权利运行一个存储历程,意味着他也能得到权利去履行存储历程内部的行动.比方,有一个存储历程用来列出去年内全部的雇佣员工.此中有权限
履行该存储历程的人,才可以履行并返回后果——即便他没有权限直接拜候人力资源的员工表.
这样隐含权限关于EXEC语句是无效的.在默许情形下,任安在一个EXEC语句内部成立的参照,都将在当前用户的安全上下文中运行.因此,我们有权利去访
问一个叫spNewEmployee的存储历程,但是却没有权利去拜候员工表.假如spNewEmployee通过一个简单的SELECT语句得到值,那么一切正常.但是假如
pNewEmployee利用EXEC语句去履行一个SELECT语句,这个EXEC语句将失利——因为没有权利拜候员工表.
3.用户自定义函数和EXEC关联
不能在同一个语句中同时运行一个函数和EXEC语句.比方:
DECLARE @Num INT
SET @Num = 3
EXEC ('SELECT LEFT(LastName, ' + CAST(@Num AS VARCHAR) + ') AS FilingName FROM Contact')
这个语句会返回一个错误消息,因为CAST函数需求在EXEC所在行之前被解析.精确代码以下:
DECLARE @Num INT
DECLARE @str VARCHAR(255)
SET @Num = 3
SET @str = 'SELECT LEFT(LastName, ' + CAST(@Num AS VARCHAR) + ') AS FilingName FROM Contact'
EXEC (@str)
这个例子工作正常,因为EXEC的输入值已经是一个完好的字符串.
4.EXEC和用户自定义函数
普通来说,不答应用户自定义函数内部利用EXEC去运行动态SQL,但是,利用EXEC运行一个存储历程,少数情形是合理的.