SQL必知必会19-22

–19储存过程

–create PROCEDURE MailingListCount as
–Declare @cnt integer
–select @cnt=COUNT(*)
–from Customers
–where Not cust_email is null;
–return @cnt

–调用存储过程
–Declare @ReturnValue int
–execute @ReturnValue=MailingListCount;
–select @ReturnValue;

–例子在Oreders表中插入一个新的订单
–create PROCEDURE NewOrder @cust_id char(10) AS
----为订单号声明变量
–declare @order_num integer
----获取当前最高订单号
–select @order_num=MAX(order_num)
–From Orders
----确定下一个订单号
–select @order_num=@order_num+1
----插入新订单
–insert into Orders(order_num,order_date,cust_id)
–values(@order_num,GETDATE(),@cust_id)
----返回订单号
–Return @order_num;

–不同版本的SQL Server代码 创建储存过程
–在 SQL Server 上 可在 全局 变量@@ IDENTITY 中 得到， 它 返回 到 调用 程序（ 这里 使用 SELECT 语句）。
–这个设置适合在主键自增的状态,否则会报错
–create PROCEDURE NewOreder2 @cust_id char(10) AS
–INSERT INTO Orders(cust_id)
–values(@cust_id)
–select order_num=@@IDENTITY;

–执行储存过程
–不能将值 NULL 插入列 ‘order_num’，表 ‘MyTestDB.dbo.Orders’；列不允许有 Null 值。INSERT 失败。
–exec NewOrder 10004
–go

–使用proc 替代PROCEDURE也可以

–create proc NewOreder3 @cust_id char(10) AS
–INSERT INTO Orders(cust_id)
–values(@cust_id)
–select order_num=@@IDENTITY;

–创建储存过程
–create proc query_Orders
–as
– select * from Orders
– go

–执行储存过程
–exec query_Orders
–go

--创建输入带参数的储存过程
–create proc query_vendors
–@vend_id int
–as
– select *
– from Vendors v
– where vend_id >= @vend_id
– go

--执行储存过程
–exec query_vendors 1003
–go

-20事务管理

事务（ transaction） 指 一组 SQL 语句；
–回退（ rollback） 指 撤销 指定 SQL 语句 的 过程；
–提交（ commit） 指 将 未 存储 的 SQL 语句 结果 写入 数据库 表；
–保留 点（ savepoint） 指 事务处理 中 设置 的 临时 占位符（ placeholder）， 可 以对 它 发布 回退（ 与 回退 整个 事务处理 不同）。
–事务处理 用来 管理 INSERT、 UPDATE 和 DELETE 语句。 不能 回退 SELECT 语句

–begin transaction
---- 这个中间的句子要么完全的执行或者完全不执行
–commit transaction

–delete from Orders;
–rollback; --撤销操作

–begin transaction
–delete OrderItems where order_num=20011
–delete Orders where order_num=20011
–commit transaction

--保留点,每个保留点都要能够标识它的唯一名字,以便回退时候回退到哪里
–begin TRANSACTION
–insert into Customers(cust_id,cust_name)
–values(‘10015’,‘Toys GaoGao’);
–save transaction StartOrders; --保留点
–insert into Orders(order_num,order_date,cust_id)
–values(20100,‘2021/9/04’,‘10017’);
–if @@ERROR <> 0 ROLLBACK transaction StartOrders; –@@ERROR 检查是否操作成功,如果@@ERROR返回一个非0值,标识有错误发生,事务退回保留点
–insert into OrderItems(order_num,order_item,prod_id,quantity,item_price)
–values(20100,1,‘BR01’,100,5.42);
–if @@ERROR <> 0 ROLLBACK transaction StartOrders;
–insert into OrderItems(order_num,order_item,prod_id,quantity,item_price)
–values(20100,2,‘BR03’,100,10.42);
–if @@ERROR <> 0 ROLLBACK transaction StartOrders;

–COMMIT transaction;

21创建游标

DECLARE CustCursor CURSOR
–for
–select * from Customers
–where cust_email is null

–open CustCursor

–close CustCursor

22

设置主键
–alter table Vendors
–Add PRIMARY key (vend_id);
–设置外键
–alter table Orders
–add foreign key (cust_id) references Customers(cust_id);

–唯一约束
–用来保证一列（ 或 一组 列）中的数据是唯一的。
–它们类似于主键， 但存在以下重要区别。
–表可包含多个唯一约束， 但每个表 只允 一个主键。 唯一约束列可包含 NULL 值。
–唯一约束列可修改或更新。 唯一约束列的值可重复使用。
–与主键不一样， 唯一约束不能用来定义 键。

–检查约束
–用来保证 一列（ 或 一组 列）中的 数据 满足一组指定的条件。检查约束的常见用途有以下几点。
–检查最小或最大值。例如，防止0 个物品的订单（即使0是合法的数）。
–指定 范围。例如，保证发货日期大于等于今天的日期，但不超过 今天起一年后的 日期。只允许特定的值。
–例如，在性别字段中只允许M或 F。

--索引
–用来 排序 数据 以 加快 搜索 和 排序 操作 的 速度。 想像 一本 书 后的 索引（ 如 本书 后的 索引）， 可以 帮助 你 理解 数据库 的 索引。
–假如 要找 出 本书 中 所有 的“ 数据 类型” 这个 词， 简单 的 办法 是 从 第 1 页 开始， 浏览 每一 行。 虽然 这样做 可以 完成任务，
–但 显然 不是 一种 好的 办法。 浏览 少数 几 页 文字 可能 还行， 但 以 这种 方式 浏览 整部 书 就不 可行 了。 随着 要 搜索 的 页数 不断增加，
–找出 所需 词汇 的 时间 也会 增加。 这就 是 书籍 要有 索引 的 原因。 索引 按 字母 顺序 列出 词汇 及其 在 书中 的 位置。 为了 搜索“ 数据 类型” 一 词，
–可在 索引 中 找出 该 词， 确定 它 出现 在 哪些 页 中。 然后 再 翻到 这些 页， 找出“ 数据 类型” 一 词。 使 索引 有用 的 因素 是什么？ 很 简单，
–就是 恰当 的 排序。 找出 书中 词汇 的 困难 不在 于 必须 进行 多少 搜索， 而在于 书 的 内容 没有 按 词汇 排序。 如果 书 的 内容 像 字典 一样 排序，
–则 索引 没有 必要（ 因此 字典 就 没有 索引）。

–触发器
–是 特殊 的 存储 过程， 它在 特定 的 数据库 活动 发生 时 自动 执行。
–触发器 可以 与 特定 表上 的 INSERT、 UPDATE 和 DELETE 操作（ 或 组合） 相 关联。
–与 存储 过程 不一样（ 存储 过程 只是 简单 的 存储 SQL 语句）， 触发器 与 单个 的 表 相 关联。
–与 Orders 表上 的 INSERT 操作 相 关联 的 触发器 只在 Orders 表中 插入 行 时 执行。
–类似 地， Customers 表上 的 INSERT 和 UPDATE 操作 的 触发器 只在 表上 出现 这些 操作 时 执行。
–触发器 内 的 代码 具有 以下 数据 的 访问 权： INSERT 操作 中的 所有 新 数据；
–UPDATE 操作 中的 所有 新 数据 和 旧 数据； DELETE 操作 中 删除 的 数据。 根据 所 使用 的 DBMS 的 不同，
–触发器 可在 特定 操作 执行 之前 或之后 执行。
–下面 是 触发器 的 一些 常见 用途。
–保证 数据 一致。 例如， 在 INSERT 或 UPDATE 操作
–中将 所有 州名 转换 为 大写。 基于 某个 表 的 变动 在 其他 表上 执行 活动。
–例如， 每当 更新 或 删除 一行 时 将 审计 跟踪 记录 写入 某个 日志 表。
–进行 额外 的 验证 并 根据 需要 回退 数据。
–例如， 保证 某个 顾客 的 可用 资金 不 超限 定， 如果 已经 超出， 则 阻塞 插入。
–计算 计算 列 的 值 或 更新 时间 戳。

–创建一个触发器,对所有操作insert和UPdate 将Customers中的cust_state列转为大写

–create trigger customer_state
–on Customers
–for insert,update
–as
–update Customers
–set cust_state=UPPER(cust_state)
–where Customers.cust_id=Customers.cust_id;