数据库后台Transact-SQL程序设计1,存储过程,触发器与游标

最新推荐文章于 2023-05-29 23:14:15 发布

JaydenCheng - e\acc

最新推荐文章于 2023-05-29 23:14:15 发布

阅读量794

点赞数

分类专栏：数据库三级文章标签：数据库

本文链接：https://blog.csdn.net/JaydenCheng/article/details/123919773

版权

数据库三级专栏收录该内容

7 篇文章 0 订阅

订阅专栏

数据库后台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.