数据库后台Transact-SQL程序设计1,存储过程,触发器与游标
数据库后台Transact-SQL程序设计
T-SQL中,变量名需要以@符号开头.RETURN的含义不一定是返回某个返回值,而应推广为从查询或过程中无条件退出,之后的语句是不执行的.T-SQL中=默认是判断是否相等的符号,没有原生的赋值号,需要赋值时需要结合其他关键字与上下文,例如DECLARE,SELECT,SET等.
存储过程
将子程序模块存储在数据库中,供有权限的用户反复调用执行,这个子程序就是存储过程.存储过程存储入数据库时会经过DBMS的编译优化,执行效率高.采用存储过程,合理使用存储过程可以提高数据处理速度.常用存储过程实现动态条件查询.
定义存储过程时,不需要像定义函数那样将参数使用括号括起来,但一定需要有AS.与函数不同的是,输出需要在头部指定,并在变量名后声明output.此处输出类似于C语言的输出值接收参数,在函数外声明一个变量,将指针传入函数,函数将值写入指针指明的地址中,类似于上述过程.
创建一个存储过程:
CREATE PROCEDURE 过程名
@x int, @y int, @z int output AS...
引用存储过程,输出参数需要在变量后显式声明.
DECLARE @输出接收变量 int
EXEC 过程名 值1, 值2, @输出接收变量 output
定义一个计算毛利润的存储过程实例:
CREATE PROCEDURE 毛利计算过程
@商品号 int, @年份 int, @总毛利 int output
AS
DECLARE @进货价 int, @销售价 int, @数量 int
BEGIN
SELECT @进货价 = 单价 FROM 商品表 WHERE 商品号 = @商品号
SELECT @销售价 = 销售单价, @数量 = SUM(销售数量) FROM 销售表
WHERE 商品号 = @商品号 AND 销售时间 = @年份
IF @进货价 IS NULL THEN
ROLLBACK; RETURN;
END IF -- 检查商品表内是否有指定商品
IF @销售价 IS NULL THEN
ROLLBACK; RETURN;
END IF -- 检查销售表内是否有指定商品
SET @总毛利 = @数量 * (@销售价-@进货价)
END
GO
触发器
触发器的三种类型:INSERT,UPDATE,DELETE.基本格式CREATE TRIGGER 触发器名 ON 表\视图 FOR\INSETEAD OF 操作 AS....触发器分为前后两种触发器,后触发器使用FOR或AFTER关键字,前触发器使用INSTEAD OF,需要注意不要忘了OF.
在触发器中,常常会用到inserted与deleted两个临时表,这两个表由SQL SERVER自动创建与管理.inserted用于存储INSERT语句中的值和UPDATE更新用的值,deleted用于存储被DELETED的值和UPDATE影响的值,在这里其实可以将UPDATE理解成先DELETED再INSERT的一个过程.
需要注意的是,前触发器被触发时,表中的数据并没有变化,但其会影响到的数据都会存储到inserted与deleted临时表中.触发器中可以正常调用这两个临时表.前触发器定义时的关键字非常形象,INSTEAD OF就是替换掉的意思,说明触发触发器的语句其实不会被执行,至于到底会做出什么动作,由触发器决定.
触发器基本格式…中一般为:
DECLARE @变量
SELECT @变量=属性 FROM inserted
SET 计算@变量值
UPDATE 属性 SET @变量 FROM 表
WHERE 主键 IN (SELECT 主键 IN inserted)
END
定义一个往销售表插入销售记录时自动计算利润的后触发器:
CREATE TRIGGER 利润计算
ON 销售表 FOR INSERT AS
DECLARE @进价 money, @销售价 money, @数量 int, @利润 money
BEGIN
SELECT @销售价 = inserted.销售价格,
@数量 = inserted.销售数量 FROM inserted
SELECT @进价 = 商品表.进价
FROM 商品表 WHERE 商品号 IN (SELECT 商品号 IN inserted)
SET @利润 = (@销售价-@进价) * @数量
UPDATE 销售表 SET 本次利润 = @利润
WHERE 商品号 IN (SELECT 商品号 IN inserted)
AND 销售时间 (SELECT 销售时间 IN inserted)
END
游标
游标允许用户在结果集中以行为单位进行操作.游标的声明周期:声明,打开,提取数据,关闭,释放.
声明游标,注意查询语句前的介词是FOR,如果声明了INSENSITIVE,则不能通过游标对结果集的基本表实现修改:
DECLARE 游标名 [INSENSITIVE][SCROLL] CURSOR
FOR 查询语句
打开游标,只能打开已经声明了但没有打开的游标,不能重复打开.
OPEN 游标名
提取数据,默认在结果集第一行的位置.不显式声明自动使用NEXT,默认的提取方式只有NEXT,除非使用的是SCROLL声明的游标.
FETCH [提取方式 FROM] 游标名 [INTO @局部变量]
关闭与释放游标,关闭后不会释放资源,需要手动释放.关闭后可以重新手动打开.
CLOSE 游标名
DEALLOCATE 游标名
在处理大量数据时,尽量避免使用游标,因为游标的效率较差,如果游标操作的数据超过1万行,那么就应该考虑改写.在提取数据时,常常用到@@FETCH_STATUS全局变量,其值表示游标当前数据提取状态,类型是int,0:成功,-1:失败,-2:不存在.
资料
部分关键字及其释义
| word | infomations |
|---|---|
| trigger | 触发器 |
| schema | 图标,纲要,架构 |
| scheme | 方案,构想 |
| drop | 下降,放弃,删除 |
| exists | 存在 |
| cursor | 光标,游标 |
| declare | 宣告,断言,声明 |
| restore | 恢复 |
| fetch | 取来,拿来 |
| intersect | 相交,交叉 |
| except | 除了,除外 |
| procedure | 程序,步骤 |
| execute | 处决,执行 |
| partition | 隔断,分治,分区 |
| bind | 绑定,捆绑 |
| cluster | 簇,团,聚集 |
| grant | 允许,授予 |
| bulk | 大量,大批 |
| process | 工序,进程 |
| revoke | 撤销,废除 |
| cascade | 瀑布,悬挂 |
| security | 安全,担保 |
| reference | 提及,参考,引用 |
| identity | 身份,特性 |
| deal | 交易,协议,处理 |
| locate | 位于,位置 |
| insensitive | 漠不关心的,不做修改的 |
| alter | 变更,改动 |
| constraint | 约束,限制 |
| foreign | 外国的,外来的,外部的 |
参考
[1]何玉洁, 刘乃嘉. 全国计算机等级考试三级教程-数据库技术[M]. 高等教育出版社. 2020.
[2]Ben Forta. SQL必知必会[M]. 人民邮电出版社. 2020.
[3]史嘉权. 数据库系统概论[M]. 清华大学出版社. 2006.
[4]褚华, 霍邱艳. 软件设计师教程[M]. 清华大学出版社. 2018.
[5]王珊, 陈红. 数据库系统原理教程[M]. 清华大学出版社. 1998.
[6]汤小丹, 梁红兵, 哲凤屏, 汤子瀛. 计算机操作系统[M]. 西安电子科技大学出版社. 2014.
