-
2021-05-05 10:58:23
存储过程:存储过程就是编译好了的一组sql语句。
- 存储过程因为SQL语句已经预编绎过,因此执行速度较快。
- 可保证数据的安全性和完整性。通过存储过程可以使没有权限的用户在控制之下间接地存取数据库,从而保证数据的安全。通过存储过程可以使相关的动作在一起发生,从而可以维护数据库的完整性。
- 可以降低网络的通信量。存储过程主要是在服务器上运行,减少对客户机的压力。
- 存储过程可以接受参数、输出参数、返回单个或多个结果集以及返回值。可以向程序返回错误原因
- 存储过程可以包含程序流、逻辑以及对数据库的查询。同时可以实体封装和隐藏本身的数据处理逻辑。
这里再简单介绍下触发器,触发器是一种特殊的存储过程;
触发器: 当满足触发器条件,则系统自动执行触发器的触发体。
触发时间:有before,after.触发事件:有insert,update,delete三种。触发类型:有行触发、语句触发,语句级的触发器可以在某些语句执行前或执行后被触发。而行级触发器则是在定义了触发条件的表中行数据发生改变时会被触发一次。
举些栗子:
1、 在一个表中定义的语句级触发器,当这个表被删除时,程序就会自动执行触发器里面定义的操作过程。这个删除表操作就是触发器执行的前提条件。
2、 在一个表中定义了行级的触发器,当表中一行数据发生变化时,如删除、新增了一行记录,触发器会被自动执行;
触发器的语法:create [or replace] tigger 触发器名 触发时间 触发事件 on 表名 [for each row] begin pl/sql语句 end
参数解说:
- 触发器名:触发器对象的名称。触发器是数据库自动执行,故名称无实质用途,仅做个人标注。
- 触发时间:指明触发器何时执行,该值可取:
before:表示在数据库动作之前触发器执行;
after:表示在数据库动作之后触发器执行。 - 触发事件:哪些数据库动作会触发此触发器:
insert:数据库插入会触发此触发器;
update:数据库修改会触发此触发器;
delete:数据库删除会触发此触发器。 - 表 名:数据库触发器所在的表。
- for each row:对表的每一行触发器执行一次。如没这一选,则对整个表执行一次。
触发器能干些什么?
1、 允许/限制对表的修改
2、 自动生成派生列,比如自增字段
3、 强制数据一致性
4、 提供审计和日志记录
5、 防止无效的事务处理
6、 启用复杂的业务逻辑举例:
1)、允许/限制对表的修改
下面的触发器在更新表tb_emp之前触发,目的是不允许在周末修改表:create or replace trigger auth_secure before insert or update or DELETE on tb_emp begin IF(to_char(sysdate,'DY')='星期日') THEN RAISE_APPLICATION_ERROR(-20600,'不能在周末修改表tb_emp'); END IF; END; /
2)、使用触发器实现序号自增
创建一个测试表:
create table tab_user( id number(11) primary key, username varchar(50), password varchar(50) );
创建一个序列:
复制代码 代码如下:
create sequence my_seq increment by 1 start with 1 nomaxvalue nocycle cache 20;创建一个触发器:
CREATE OR REPLACE TRIGGER MY_TGR BEFORE INSERT ON TAB_USER FOR EACH ROW--对表的每一行触发器执行一次 DECLARE NEXT_ID NUMBER; BEGIN SELECT MY_SEQ.NEXTVAL INTO NEXT_ID FROM DUAL; :NEW.ID := NEXT_ID; --:NEW表示新插入的那条记录 END;
向表插入数据:
**
insert into tab_user(username,password) values(‘admin’,‘admin’);
insert into tab_user(username,password) values(‘fgz’,‘fgz’);
insert into tab_user(username,password) values(‘test’,‘test’);
COMMIT;**3)、创建触发器,将操作CREATE、DROP存储在log_info表
–创建表CREATE TABLE log_info(
manager_user VARCHAR2(15),
manager_date VARCHAR2(15),
manager_type VARCHAR2(15),
obj_name VARCHAR2(15),
obj_type VARCHAR2(15)
);–创建触发器
set serveroutput on;
CREATE OR REPLACE TRIGGER TRIG_LOG_INFO
AFTER CREATE OR DROP ON SCHEMA
BEGIN
INSERT INTO LOG_INFO
VALUES
(USER,
SYSDATE,
SYS.DICTIONARY_OBJ_NAME,
SYS.DICTIONARY_OBJ_OWNER,
SYS.DICTIONARY_OBJ_TYPE);
END;
/–测试语句
CREATE TABLE a(id NUMBER);
CREATE TYPE aa AS OBJECT(id NUMBER);
DROP TABLE a;
DROP TYPE aa;–查看效果
SELECT * FROM log_info;–相关数据字典-----------------------------------------------------
SELECT * FROM USER_TRIGGERS;–必须以DBA身份登陆才能使用此数据字典
SELECT * FROM ALL_TRIGGERS;SELECT * FROM DBA_TRIGGERS;–启用和禁用
ALTER TRIGGER trigger_name DISABLE;
ALTER TRIGGER trigger_name ENABLE;再来两个实际生产案例:
题目:
–触发器:
–添加员工信息,流水号作为自动编号(通过序列生成),
–并且判断如果工资小于0,则改为0;如果大于10000,则改为10000。CREATE TABLE emp2(
e_id NUMBER,
e_no NUMBER,
e_name VARCHAR2(20),
e_sal NUMBER
)SELECT * FROM emp2;
CREATE SEQUENCE seq_trg_id;
INSERT INTO emp2(e_id,e_no,e_name,e_sal) VALUES(seq_trg_id.nextval,7788,'栩栩', 1000000) INSERT INTO emp2(e_id,e_no,e_name,e_sal) VALUES(seq_trg_id.nextval,7788,'有名',-10)
CREATE OR REPLACE TRIGGER trg_add_emp_info BEFORE INSERT ON emp2 FOR EACH ROW DECLARE -- local variables here BEGIN SELECT seq_trg_id.NEXTVAL INTO :NEW.e_id FROM dual; IF :NEW.e_sal < 0 THEN :NEW.e_sal := 0; ELSIF :NEW.e_sal > 10000 THEN :NEW.e_sal := 10000; END IF; END;
案例二:
为emp建立触发器,将删除的记录放到emp3表中(autoid,deptno,empno,ename,del_rq-删除日期)CREATE OR REPLACE TRIGGER trg_del_emp_info BEFORE DELETE ON emp FOR EACH ROW DECLARE -- local variables here BEGIN INSERT INTO emp3(autoid,deptno,empno,ename,del_rq) VALUES(seq_trg_del_autoid.NEXTVAL,:OLD.deptno,:OLD.empno,:OLD.ename,sysdate); END;
文章结束。**
以下为个人公众号,欢迎扫码关注:
【参考】
https://www.cnblogs.com/sharpest/p/7764662.html
【参考】
https://www.cnblogs.com/quyanhui/p/3386493.html
【参考】
https://blog.csdn.net/justdo2008/article/details/4137779更多相关内容 -
数据库实验系列之3存储过程和触发器实验(存储过程和触发器)
2020-05-18 09:29:09存储过程和触发器实验(至少包括完整的调试通过的8个存储过程和5个触发器)实验9:存储过程实验实验10:触发器实验 实验9:存储过程实验 自拟题目完成8个存储过程的编写及调试,熟练掌握存储过程的使用。也可采用下...存储过程和触发器实验(存储过程和触发器)
本文最初由security9968发布于security9968的csdn博客,禁止任何形式的剽窃行为
转载原创文章请注明,转载自:security的博客实验9:存储过程实验
自拟题目完成8个存储过程的编写及调试,熟练掌握存储过程的使用。也可采用下图中作业上的题目。
SQL语句代码
--1.例1 use 学生作业管理数据库; select * from 学生表; select * from 课程表; select * from 学生作业表; --先查看是否存在名字为student_course的存储过程,如果有,删除 if exists (select name from sysobjects where name='student_course' and type='P') drop procedure stuent_course; --创建存储过程 create procedure student_course as select 学生表.学号,姓名,课程名,作业1成绩 from 学生表,课程表,学生作业表 where 学生表.学号=学生作业表.学号 and 课程表.课程号=学生作业表.课程号 and 姓名='张艳' --执行存储过程 execute student_course; --带输入参数的存储过程 create procedure student_course1 @Studentname varchar(10) as select 学生表.学号,姓名,课程名,作业1成绩 from 学生表,课程表,学生作业表 where 学生表.学号=学生作业表.学号 and 课程表.课程号=学生作业表.课程号 and 姓名=@Studentname; --调用student_course1 execute student_course1 '于兰兰'; --创建一个存储过程用于向学生表中插入记录 create procedure student_insert @学号 int,@姓名 nvarchar(10),@性别 nvarchar(2),@专业班级 nvarchar(10),@出生日期 nvarchar(20),@联系电话 nvarchar(20) as INSERT into 学生表 values(@学号,@姓名,@性别,@专业班级,@出生日期,@联系电话); --执行student_insert execute student_insert '007','阿刚','男','电子06','2000-1-1','13333333333'; --创建存储过程,若没有给出学生姓名,则返回所有学生情况 create procedure student_course2 @StudentName nvarchar(20)=null as if @StudentName is null begin select 学生表.学号,姓名,课程名,作业1成绩 from 学生表,课程表,学生作业表 where 学生表.学号=学生作业表.学号 and 课程表.课程号=学生作业表.课程号 end else begin select 学生表.学号,姓名,课程名,作业1成绩 from 学生表,课程表,学生作业表 where 学生表.学号=学生作业表.学号 and 课程表.课程号=学生作业表.课程号 and 姓名=@Studentname; end execute student_course2 '张志国'; execute student_course2; --使用默认参数值 --使用输出参数 CREATE procedure student_count @CourseName varchar(20), @StudentSum int output as select @StudentSum=COUNT(*) from 课程表,学生作业表 where 课程表.课程号=学生作业表.课程号 and 课程名=@CourseName; --对于带有输出参数的存储过程,调用时需要定义相应的变量用于接收从存储过程返回的参数值 declare @StudentSum1 int; execute student_count '数据结构',@StudentSum1 OUTPUT; SELECT @StudentSum1 as 选数据结构的人数; --创建一个存储过程,输出学生的基本情况 alter procedure student_query @学号 int,@姓名 nvarchar(8) output,@性别 char(2) output as select @姓名=姓名,@性别=性别 from 学生表 where 学号=@学号; --调用存储过程,查看基本情况 declare @姓名1 nvarchar(20); declare @性别1 char(2); execute student_query '7',@姓名1 output,@性别1 output; select @姓名1 as 学生姓名,@性别1 as 学生性别; select * from 学生表; --删除存储过程是 --drop procedure 存储过程名; --market数据库中 use market; --存储过程shanghai,查看上海客户信息 select * from Customers; insert into Customers VALUES(3,'阿美','上海'); create procedure shanghai as select * from Customers where City='上海' execute shanghai; --存储过程Goods,查看指定商品信息,商品编号作为输入参数 select * from Goods; insert into Goods values(1,'牙膏',2.5,'牙膏厂',400,'在售'); insert into Goods values(2,'牙刷',5,'牙刷厂',1200,'热卖'); create procedure cunchuGoods @商品编号 int as select * from Goods where GoodID=@商品编号; execute cunchuGoods @商品编号=2; --存储过程GoodsSum,查看指定客户的所有订单的订货总金额,客户编号作为输入参数,订货总金额作为输出参数 select * from Orders; insert into Orders values(1,1,1,2,5,'2020-1-1'); insert into Orders values(2,1,2,2,10,'2020-1-1'); insert into Orders values(3,2,2,2,10,'2020-1-1'); create procedure cunchuGoodsSum @客户编号 int,@订货总金额 float output as select @订货总金额=OrderSum from Orders where Orders.CustomerID=@客户编号; declare @订货总金额 float; --切记勿忘声明变量 execute cunchuGoodsSum 2,@订货总金额 output; select @订货总金额 as '订货总金额'; --存储过程insert_Goods,向Goods表中插入一条记录 select * from Goods; alter procedure insert_Goods @商品编号 int,@商品名称 nvarchar(20),@价格 float,@供货商 nvarchar(20),@库存量 int,@商品状态 nvarchar(20) as insert into Goods(GoodID,Gname,Price,Provider,Stocks,Status) values(@商品编号,@商品名称,@价格,@供货商,@库存量,@商品状态); execute insert_Goods 3,'牙刷杯',10,'牙刷杯厂',5,'即将断货'; --创建存储过程Goods_Orders1,查看任何指定货品的订单情况,包括订单号,订货客户姓名以及订货数量(使用输入参数) select * from Orders; select * from Goods; select * from Customers; CREATE procedure Goods_Orders1 @指定货品 nvarchar(20) as select OrderID,Cname,Quantity from Orders,Goods,Customers where Orders.GoodID=Goods.GoodID and Orders.CustomerID=Customers.CustomerID AND Goods.Gname=@指定货品; EXECUTE Goods_Orders1 @指定货品='牙膏'; --执行存储过程,如果不给出参数则报错,如果希望不输入参数,即默认值,得到所有货品订单,则新建表Goods_Orders2 execute Goods_Orders1 @指定货品; --报错 create procedure Goods_Orders2 @指定货品 nvarchar(20)=null as if @指定货品 is null begin select OrderID,Cname,Quantity from Orders,Goods,Customers where Orders.GoodID=Goods.GoodID and Orders.CustomerID=Customers.CustomerID end else begin select OrderID,Cname,Quantity from Orders,Goods,Customers where Orders.GoodID=Goods.GoodID and Orders.CustomerID=Customers.CustomerID AND Goods.Gname=@指定货品 end execute Goods_Orders2; execute Goods_Orders2 @指定货品='牙刷'; --创建存储过程Goods_OrderSum,来获得某个货品的订单总额(使用输入输出参数) alter PROCEDURE Goods_OrderSum @货品名称 nvarchar(20),@订单总额1 float output as select @订单总额1=sum(OrderSum) from Orders,Goods where Goods.GoodID=Orders.GoodID and Gname=@货品名称; declare @订单总额1 float; execute Goods_OrderSum '牙膏',@订单总额1 output; select @订单总额1 as '订单总额';
实验10:触发器实验
自拟题目完成5个触发器的编写及调试,熟练掌握触发器的使用。也可采用下图中作业上的题目。
SQL语句代码
use 学生作业管理数据库; --例8 创建一个触发器,当 学生表 中的记录被更新时,显示表中的所有记录 create trigger student_change on 学生表 after insert,update,delete as select * from 学生表; --查看下变化 select * from 学生表; insert into 学生表 values(1,'阿美','女','计科06','2002-1-1','13312313213'); --例9 在 学生表 中创建DELETE触发器,实现对 学生表 和 学生作业表 的级联删除 create trigger studentdelete on 学生表 after delete as delete from 学生作业表 where 学号 in (select deleted.学号 from deleted); --查看下变化 select * from 学生作业表; insert into 学生作业表 values('K001',1,99,99,99); select * from 学生作业表; delete from 学生表 where 姓名='阿美'; select * from 学生作业表; --例10 在学生作业表上创建insert 触发器,当向学生作业表 中添加学生的选课记录时, --检查该学生的学号是否存在,若不存在,则不能将记录插入 create trigger sc_insert on 学生作业表 after insert as if(select count(*) from 学生表,inserted where 学生表.学号=inserted.学号)=0 begin print '学号不存在,不能插入' rollback transaction end; --查看效果 insert into 学生作业表 values('K001',1,99,99,99); --例11 创建update触发器,禁止对学生表 中学生的性别进行修改 create trigger student_update on 学生表 after update as if update(性别) begin print '禁止对学生学号修改' rollback transaction end; --查看效果 select * from 学生表; update 学生表 set 性别='男' where 性别='女' and 学号=7; select * from 学生表; --例12 在学生作业表上创建触发器,当一次向学生作业表中添加多个记录时,删除学号在学生表中不存在的记录, --从而保证数据的一致性,注意,不能在学生作业表中定义外键约束 create trigger sc_insert1 on 学生作业表 after insert as if(select count(*) from 学生表,inserted where inserted.学号=学生表.学号)<>@@ROWCOUNT BEGIN delete from 学生作业表 where 学号 not in (select 学号 from 学生表) END; --例13 在视图上定义instead of 触发器 select * from 学生表; create view birth_view(学号,姓名,性别,生日,专业班级) as select 学号,姓名,性别,出生日期,专业班级 from 学生表; create trigger birth_view_insert on birth_view instead of insert as declare @学号 int declare @姓名 varchar(20); declare @性别 varchar(20); declare @生日 varchar(20); declare @专业班级 varchar(20); select @学号=学号,@姓名=姓名,@性别=性别,@专业班级=专业班级 from inserted; insert into 学生表(学号,姓名,性别,专业班级) values(@学号,@姓名,@性别,@专业班级); --查看效果 insert into birth_view(学号,姓名,性别,专业班级) values(2,'阿红','女','软件04'); use market; --第四章第五题(5)在Customers表上建立删除触发器,实现Customers表和Orders表的级联删除 select * from Customers; select * from Orders; create trigger customers_delete on Customers after delete as delete from Orders where CustomerID in (select deleted.CustomerID FROM DELETED); --第四章第五题(6)在Orders表上建立插入触发器,当向表中添加一条订货记录时,若订单中的商品状态为即将断货(Status='即将断货') --则不能插入该条记录 select * from Customers; select * from Orders; select * from Goods; CREATE trigger orders_insert on Orders AFTER INSERT AS if(select Status from Goods,inserted where Goods.GoodID=inserted.GoodID) in ('即将断货') begin print '即将断货,不能订购' rollback transaction end; --试试效果 insert into Orders values(4,3,3,2,20,'2020-2-2'); --第四章第五题(7)在Orders表上建立插入触发器,当添加订单时,减少Goods表中相应商品的库存量 select * from Customers; select * from Orders; select * from Goods; create trigger orders_insert1 on Orders after insert as UPDATE Goods SET Stocks=Stocks-inserted.Quantity FROM Goods,inserted WHERE Goods.GoodID=inserted.GoodID; --第四章第五题(8)在Orders表上建立触发器,不允许对订单日期进行修改 select * from Customers; select * from Orders; select * from Goods; CREATE TRIGGER orders_create ON Orders AFTER UPDATE as IF UPDATE(Date) BEGIN RAISERROR('不能手动修改',10,1) ROLLBACK TRANSACTION END; --第四章第五题(9)建立触发器,实现参照完整性约束,即若在Orders表中添加一条记录时,则该订单中的商品也必须在 --Goods表中存在,否则不许添加该记录; select * from Customers; select * from Orders; select * from Goods; CREATE TRIGGER orders_create1 ON Orders AFTER INSERT AS IF (SELECT COUNT(*) from Goods,inserted WHERE Goods.GoodId=inserted.GoodID)=0 BEGIN print '这种货物不存在' rollback transaction END;
本文最初由security9968发布于security9968的csdn博客,禁止任何形式的剽窃行为
转载原创文章请注明,转载自:security的博客 -
数据库 SQL 实验报告存储过程和触发器
2022-05-28 21:39:58年级 班号 组号 学号 专业 ... 实验九 存储过程和触发器 实验室 ...年级
班号
组号
学号
专业
日期
姓名
实验名称
实验九 存储过程和触发器
实验室
实验
目的
或
要求
1、掌握存储过程的概念、作用、分类及对应的创建、删除语句的语法格式
2、掌握触发器的概念、作用、分类及对应的创建、删除语句的语法格式
3、了解插入表inserted和删除表deleted的作用及其用法
实验
环境
SQL Server 2014
实验内容或
实验题目
1、创建并执行一个无参数的存储过程proc_product1,通过该存储过程可以查询商品类别名称为“笔记本电脑”的商品的详细信息:包括商品编号、商品名称、品牌、库存量、单价和上架时间信息
2、创建并执行一个带输入参数的存储过程proc_product2,通过该存储过程可以根据输入参数供应商名称和包含关键字的商品名称进行商品具体信息的查询:包括商品编号、商品名称、品牌、库存量、单价、成本价、供应商名称、联系电话信息
3、创建并执行一个带输入参数和输出参数的存储过程proc_product3,通过该存储过程可以根据输入的会员用户名查询出该会员购买的商品信息:包括商品名称、购买数量和实付总金额信息
4、删除存储过程proc_product3
5、创建一个名为tri_insert1的DML触发器,该触发器的作用是:当向会员表member中添加一条记录时,如果新添加的会员编号已经存在于表中,则禁止插入该条记录,并提示该会员已经存在!。
6、创建一个名为tri_update1的DML触发器,该触发器的作用是:不允许修改订单表orders中的discount属性列的值,并给出提示语句“禁止修改orders表中的discount属性列的数据!”
7、创建一个名为tri_insert2的DML触发器,该触发器的作用是:当向employee新插入一条会员记录时,系统自动将部门表department中对应部门的属性列deptotal进行加1处理
8、创建一个名为tri_delete的DML触发器,该触发器的作用是:用删除会员表menmber中某条会员记录的时,系统自动在订单表orders中查询是否有该会员的订单记录,如果有,则同时删除该会员的所有订单记录
9、删除触发器tri_delete
实
验
原
理
(
操
作
步
骤
)
步骤如下:
/*1、创建并执行一个无参数的存储过程proc_product1,通过该存储过程可以查询商品类别名称为
“笔记本电脑”的商品的详细信息:包括商品编号、商品名称、品牌、库存量、单价和上架时间信息*/
create procedure proc_product1
as
select prono,proname,brand,stock,price,ontime
from product
where catno in(
select catno
from category
where catname like'笔记本电脑'
)
go
exec proc_product1
go
/*2、创建并执行一个带输入参数的存储过程proc_product2,通过该存储过程可以根据输入参数供应商名称
和包含关键字的商品名称进行商品具体信息的查询:包括商品编号、商品名称、品牌、库存量、单价、成本价、供应商名称、联系电话信息*/
create procedure proc_product2(@name1 varchar(30),@name2 varchar(30))
as
select product.prono,proname,brand,stock,price,cost,supplier.supname,telephone
from product,supplier
where product.supno=supplier.supno and
supplier.supname=@name1 and product.proname=@name2
exec proc_product2 '顺京通讯有限公司','华为P50 4G全网通智能手机'
go
/*3、创建并执行一个带输入参数和输出参数的存储过程proc_product3,通过该存储过程可以根据输入的会员
用户名查询出该会员购买的商品信息:包括商品名称、购买数量和实付总金额信息*/
create procedure proc_product3(@name3 varchar(30))
as
select product.proname,orders.qty,tatalmoney
from product,orders,member
where product.prono=orders.prono and member.memno=orders.memno and memname=@name3
go
exec proc_product3 '关羽'
go
/*4、删除存储过程proc_product3*/
drop procedure proc_product3
go
/*5、创建一个名为tri_insert1的DML触发器,该触发器的作用是:当向会员表member中添加一条记录时,如
果新添加的会员编号已经存在于表中,则禁止插入该条记录,并提示该会员已经存在!。*/
create trigger tri_insert1
on member
instead of insert
as
declare @no int
select @no =memno from inserted
if exists(select memno from member where memno=@no)
begin
rollback transaction
print'会员已存在!'
end
else
begin
insert into member select*from inserted
print'完成'
end
go
insert
into member
values('2001','李四','上海','10086','cd','123456')
go
/*6、创建一个名为tri_update1的DML触发器,该触发器的作用是:不允许修改订单表orders中的discount属
性列的值,并给出提示语句“禁止修改orders表中的discount属性列的数据!”*/
create trigger tri_update1
on orders
after update
as
if update(discount)
begin
rollback transaction
print'禁止修改orders表中的discount属性列的数据!'
end
go
update orders
set discount =110
where orderno='1501001'
go
/*7、创建一个名为tri_insert2的DML触发器,该触发器的作用是:当向employee新插入一条会员记录时,系
统自动将部门表department中对应部门的属性列deptotal进行加1处理*/
go
create trigger tri_insert2
on employee
after insert
as
begin
update department
set deptotal = deptotal+1
where depno = (select depno from inserted);
end
go
insert
into employee
values('1007','小张','1','男','1111111','xxx','123456')
select* from department
/*8、创建一个名为tri_delete的DML触发器,该触发器的作用是:用删除会员表menmber中某条会员记录的时,
系统自动在订单表orders中查询是否有该会员的订单记录,如果有,则同时删除该会员的所有订单记录*/
go
create trigger tri_delete
on member
for delete
as
delete from orders
where memno=(select memno from deleted)
delete from member
where memname='关平'
select *from orders
/*9、删除触发器tri_delete*/
drop trigger tri_delete
(写不完时,可另加附页。)
组内
分工
(
可选
)
实
验
结
果
分
析
及
心
得
体
会
实验运行结果或者是验证性的结果:
1. /*1、创建并执行一个无参数的存储过程proc_product1,通过该存储过程可以查询商品类别名称为
“笔记本电脑”的商品的详细信息:包括商品编号、商品名称、品牌、库存量、单价和上架时间信息*/
create procedure proc_product1
as
select prono,proname,brand,stock,price,ontime
from product
where catno in(
select catno
from category
where catname like'笔记本电脑'
)
go
exec proc_product1
go
2.
/*2、创建并执行一个带输入参数的存储过程proc_product2,通过该存储过程可以根据输入参数供应商名称
和包含关键字的商品名称进行商品具体信息的查询:包括商品编号、商品名称、品牌、库存量、单价、成本价、供应商名称、联系电话信息*/
create procedure proc_product2(@name1 varchar(30),@name2 varchar(30))
as
select product.prono,proname,brand,stock,price,cost,supplier.supname,telephone
from product,supplier
where product.supno=supplier.supno and
supplier.supname=@name1 and product.proname=@name2
exec proc_product2 '顺京通讯有限公司','华为P50 4G全网通智能手机'
go
3.
/*3、创建并执行一个带输入参数和输出参数的存储过程proc_product3,通过该存储过程可以根据输入的会员
用户名查询出该会员购买的商品信息:包括商品名称、购买数量和实付总金额信息*/
create procedure proc_product3(@name3 varchar(30))
as
select product.proname,orders.qty,tatalmoney
from product,orders,member
where product.prono=orders.prono and member.memno=orders.memno and memname=@name3
go
exec proc_product3 '关羽'
go
/*4、删除存储过程proc_product3*/
drop procedure proc_product3
go
/*5、创建一个名为tri_insert1的DML触发器,该触发器的作用是:当向会员表member中添加一条记录时,如
果新添加的会员编号已经存在于表中,则禁止插入该条记录,并提示该会员已经存在!。*/
create trigger tri_insert1
on member
instead of insert
as
declare @no int
select @no =memno from inserted
if exists(select memno from member where memno=@no)
begin
rollback transaction
print'会员已存在!'
end
else
begin
insert into member select*from inserted
print'完成'
end
go
insert
into member
values('2001','李四','上海','10086','cd','123456')
go
5.
6.
*6、创建一个名为tri_update1的DML触发器,该触发器的作用是:不允许修改订单表orders中的discount属
性列的值,并给出提示语句“禁止修改orders表中的discount属性列的数据!”*/
create trigger tri_update1
on orders
after update
as
if update(discount)
begin
rollback transaction
print'禁止修改orders表中的discount属性列的数据!'
end
go
update orders
set discount =110
where orderno='1501001'
go
7.
/*7、创建一个名为tri_insert2的DML触发器,该触发器的作用是:当向employee新插入一条会员记录时,系
统自动将部门表department中对应部门的属性列deptotal进行加1处理*/
go
create trigger tri_insert2
on employee
after insert
as
begin
update department
set deptotal = deptotal+1
where depno = (select depno from inserted);
end
go
insert
into employee
values('1007','小张','1','男','1111111','xxx','123456')
select* from department
前:
后:
8.
*8、创建一个名为tri_delete的DML触发器,该触发器的作用是:用删除会员表menmber中某条会员记录的时,
系统自动在订单表orders中查询是否有该会员的订单记录,如果有,则同时删除该会员的所有订单记录*/
go
create trigger tri_delete
on member
for delete
as
delete from orders
where memno=(select memno from deleted)
delete from member
where memname='关平'
select *from orders
关平为 2009
/*9、删除触发器tri_delete*/
drop trigger tri_delete
分析结果以及在试验中应注意的问题:
写错no和name 所以数据类型错了
-
SQL Server 2008 R2数据库存储过程和触发器
2022-04-19 20:34:22内容:创建并执行存储过程、修改存储过程、删除存储过程、创建和执行触发器、修改触发器、删除触发器、习题、总结 意义:1.掌握存储过程的概念、功能等2.掌握用户自定义存储过程的创建、修改、删除和执行3.掌握...实验名称:存储过程和触发器
实验目的:
1.掌握存储过程的概念、功能等
2.掌握用户自定义存储过程的创建、修改、删除和执行
3.掌握触发器的概念和功能
4.掌握触发器的创建、修改、删除及其使用方法
实验意义:
1.存储过程的创建可以对数据操作过程加密,使用户只能输入相应参数完成操作得到输出结果,并不能查看到源代码,对于数据库的安全性具有重要意义。
2.存储过程效率较高,通过自定义的存储过程,可以反复进行调用,避免多次重复输入相似的代码从而降低效率。
3.触发器的存在可以使表与表之间实现一定的约束或者实现级联更新的操作,通过触发器的创立,用户在执行某些操作时可能会不满足条件而终止,也可以产生相应的该表或者其他表的数据更新的效果。
实验内容:
- 创建并执行存储过程
- 修改存储过程
- 删除存储过程
- 创建和执行触发器
- 修改触发器
- 删除触发器
- 习题
- 总结与心得体会
实验主要步骤:
- 创建并执行存储过程
1.交互式创建存储过程
在打开显示的界面中,修改参数,输入SQL语句进行创建存储过程如下,通过学生学号来查询学生的姓名、年龄、系名,并设置默认学号为S2。在存储过程中声明变量,定义参数,执行查询SQL赋值语句,并且将查询结果传出存储过程:
执行SQL语句,调用创建的存储过程,先调用参数为默认学号为S2的学生查询存储过程,再调用学号参数为S4的学生的查询过程,并且打印出学号,年龄与系别。
2.T-SQL语句创建存储过程
通过学生姓名和课程名查询该生该课程的成绩
新建查询,创建存储过程,首先声明变量,并且声明变量的类型,采用表的连接方式,并且执行条件语句WHERE选中该学生与该课程,在SELECT中即可完成参数的传出。
执行T-SQL语句,同样先声明变量与存储类型,再采用SELECT进行赋值操作,也可以采用SET对学生名与课程名进行赋值,最后再调用存储过程,将参数输入,最终在调用存储过程后将学生姓名与选课成绩打印出来。
- 修改存储过程
1.交互式修改存储过程Pro_Qsinf
在对象资源管理器中,找到对应表下的存储过程,右击该存储过程,点击“修改”,将变量SNO_IN的长度修改为2,输出变量SAGE_OUT的类型改为TINYINT。
修改之后的结果如下所示,并单击执行,将其再次保存。
2.T-SQL语句式修改存储过程Pro_Qsinf
执行方法是采用Alter语句,将存储过程整体结构重新编写修改,修改查询结果为查询姓名、性别与系名,并且将学号的默认值修改S1
最后在对象资源管理器中将该存储过程刷新后可以看到,该存储过程的列参数的参数类型已经发生对应的变化。
- 删除存储过程
1.交互式删除存储过程Pro_Qsinf
在存储过程中,右击该存储过程,单击删除操作,点击确定即可完成对存储过程的删除操作。
2.T-SQL语句对存储过程进行删除
- 创建和执行触发器
1.交互式为数据库表S创建级联更新的触发器TRIGGER_S
该触发器作用:修改表S中学号时,SC表中该学生选课记录的学号相应发生变化,与外键相似的效果。
在对象资源管理器中对于表C新建触发器如下:
采用与之前新建存储过程相似的方法,将交互式中自带的语句进行修改如下,先提前定义运行条件是当表的SNO发生更新时才执行,再声明变量,进行查询语句创建操作,并且设置对应学号发生变化后,SC表中对应的学号数据也要发生变化,采用SET语句对SC表相应学号进行赋值。
数据校验,将学号为S1学生修改学号为S5:
执行之后,查看学生表的数据,学生“赵就”的学号由S1变成了S5
再查看SC表中的数据,发现原本为S1学生的选课数据变成了S5的选课数据,说明触发器的创建成功。
2.交互式为数据库表SC创建限制更新触发器TRIGGER_SC
修改SC表中一记录的学号,则要求检查表S中是否存在学号相同的记录,若存在则不许修改。
以下为代码创建过程:
对SC表创建触发器,由于更新过程涉及到表DELETED,因此将删除的SNO保存为SNO_OLD,并对S表中的SNO与更新SC表前的SNO进行对比,若存在1或多条数据,由COUNT函数记录,并且当COUNT函数结果不等于0时,触发器停止运行,过程终止(ROLLBACK TRANSACTION)。
对SC表更新学号,观察S表中学号变化情况,可以看出触发器可以正常运转,并且将批处理中止。
3.用SQL语句为数据库表SC创建成绩约束触发器
对更新后插入表内成绩进行判断,满足范围才能执行改变,范围不满足即会中止。
在SC表中插入不合格成绩数据进行测试
插入成绩合格数据再进行测试
修改更新表中数据,不合格数据如下:
修改更新表中数据,合格数据如下:
综合以上四种测试情况,可以验证出该触发器的创建的正确性。
4.用SQL语句为数据库表C创建个级联删除触发器TRIGGER_DC
创建触发器,并且设定触发器条件为DELETE,接下来设置具体触发条件,目标为从C表的改变到SC表中课程的删除。
测试数据,对C表删除课程C1如下:
可以查看SC表中课程C1已经不存在:
- 修改触发器
1.交互式修改数据库表S的触发器TRIGGER_S
与修改存储过程方法极其类似,在对象资源管理器中右击触发器,单击修改。
修改为删除表S中信息,自动删除数据库表中与该学生相关的记录。
2.用SQL语句修改数据库表C的触发器TRIGGER_DC
在表C中删除某课程信息,同时删除数据库表SC与TC的相关课程记录。在触发器中,增加DELETE语句,作用于SC与TC表,并且设置删除条件
- 删除触发器
1.交互式删除触发器
操作步骤如下图所示:
2.T-SQL语句删除触发器
习题训练
存储过程习题
1.利用学生姓名查询该生选修的课程名、成绩,以及任课教师名
首先对存储过程进行创建,声明变量,定义参数,在查询语句中,采用SELECT输出课程名、成绩,以及任课教师名,采用多表连接的方式,并且进行设定筛选条件,打印出结果。
调用该过程程序语句如下,经过测试仅能输出一行程序运行结果,并不满足我们真实的需求,因此在未增加游标的情况下,该存储过程执行结果是并不完整的。
3.利用学生姓名和课程名检索该生该课程的成绩
仍然采用多表连接的方式,输出结果为年龄,查询过程比较简单,创建存储过程如下:
调用存储过程的结果如下,选用一组测试数据,并将结果打印在了显示页面上如下:
7.利用课程名查询选修该课程的学生姓名、系别、成绩,并给出某门课程的查询信息。
在创建存储过程中,采用多表连接的方式,并确定输出列,同时打印出结果。
调用存储过程如下,选择课程为“定位原理”,可以看出基于美存在游标的情况,输出结果仍然只是单行数据,是基于SELECT的赋值方式,仅赋值最后一次赋值的结果。
8.利用教师姓名和课程名检索该教师该认可的课程名、课时数、选课人数、平均成绩、最高成绩。
创建存储过程如下,查询语句中,记录输出的课程名、课时数、并且记录数目、平均成绩值、最高成绩值,同时采用多表连接的方法,判断筛选条件为教师姓名与课程名,并且按照教师姓名、课程名以及课时数进行分组执行查询操作。
调用存储过程如下,并且给出测试用例,输出结果打印至显示页面上:
触发器习题
1.为数据库表SC创建触发器:当插入或修改一个记录时,确保此纪录成绩在0-100分之间。
采用判断语句,对插入或更新表中成绩数据时,进行判断成绩的范围,满足时打印插入或修改成功,不满足时触发器自动中止该过程。
接下来是四种情况验证触发器的创建效果:
可以看出创建成功,可以符合逻辑正常运转。
3.为数据库表C、TC和SC创建参照完整性:级联删除和级联修改触发器
主要过程实现为对C表创建触发器,触发器实现条件为C表的更新与删除,并且是对于SC与TC表相对于课程发生变化,触发器创建如下:
测试更新表CNO数据如下:
可以看出SC与TC均发生相应的变化,课程C3已经全部转化为了C8如下:
再次测试删除操作如下:
可以看出SC与TC均发生相应的变化,课程C7已经全部被删除如下:
综上所述,可以看出修改和删除操作均顺利执行!
4.为数据库表T创建触发器:当职称从“讲师”晋升为“副教授”时,岗位津贴自动增加500元,从“副教授”晋升为“教授”时,津贴自动增加900元。
创建存储过程,该过程是单表内部的级联更新,先在初始表中保存工资值,再删除表中保存删除的职称,在插入表中保存插入的职称,接下来对职称进行判断,满足“讲师”到“副教授”,则设置其工资在原始工资上增加500元,若满足“副教授”到“教授”,再初始工资上增加900元。代码如下:
展示初始的教师T表如下:
执行从“讲师”到“副教授”过程,并且查看结果:
结果如下,可以看出正常运转:
执行从“副教授”到“教授”过程,并且查看结果:
结果如下,可以看出正常运转:
总结与实验心得
(一)实验代码优化部分:
题目:修改SC表中一记录的学号,则要求检查表S中是否存在学号相同的记录,若存在则不许修改。
采用EXISTS语句进行比较效率更加高,因此将前边创建触发器中更新触发器中连接过程修改为之间判断是否存在的过程,提高了SQL代码的执行效率,在进行数据庞大的表时,效率更高,代码对比如下:
优化前:
优化后:
(二)总结
1.在声明变量时需要先确定变量的类型与确定其是为输入或输出变量,对变量进行命名时对变量进行标记输入或输出,并且尽量取名与字面意思相同的名称,便于程序的编写。
2.在定义存储过程时需要注意是否需要给查询输入的参数赋值默认值,有默认值的情况更利于存储过程的调用。
3. 对于调用存储过程时虽然不能使存储过程返回多条数据,但是通过运行调试发现,可以在存储过程中按照常规SQL语句执行,显示出多条数据在结果栏中。
修改存储过程如下:
调用存储过程并且返回查询结果如下:
此过程并未进行赋值,也没有通过存储过程将查询结果数据传出,仅仅是在存储过程中通过查询语句得到查询表结果的页面显示上。具体的多行数据传出过程需要通过后续学习的游标才能真正完成实现。
实验体会存储过程与之前所学编程中函数的异同点:
不同方面:函数一般是之间包含在主程序中的,在同一界面下直接进行调用,而存储过程是单独作为打包文件存在数据库下。并且存储过程是返回对应参数,而函数只是返回值,同时存储过程功能比较强大,可以在其中直接进行表临时操作等。
相同方面:本质相同,均是对语句的打包执行,提高程序代码编写的效率且具有更强的安全性。
存储过程与触发器的差异:
存储过程主要是对于查询并且返回结果的语句执行的打包操作,需要再次执行调用才能完成某种查询执行操作,并且主要是针对查询操作返回参数结果。
触发器主要是实现级联更新、插入、删除等操作,是提前定义好的并且是针对特定的表,可以是针对单表内数据发生变化该表内其他数据对应属性值的变化,或者是单表数据发生变化后引起其他表数据相应的发生变化的过程,是一种数据发生变化后不需要调用而自动执行的过程。
触发器相对于外键的优势:功能比外键更加强大,外键仅仅是通过表的外连接,限制多表数据删除或者插入时的功能;而触发器除了包含外键的功能之外,同时还包含了域约束的效果,以及可以针对单表内进行数据级联更新变化,效果更加全面,而且多表之间也可以提前建立触发器关系,相应数据变化产生的其他数据对应的变化。
-
mysql存储过程与触发器的例题
2021-06-22 16:36:231.创建存储过程 (1)创建一个存储过程,查询女同学选修的课程信息。 #创建学生表 create table st_table( id int auto_increment primary key, name varchar(10), sex varchar(1) ); #创建学生选修课信息表 ... -
数据库系统原理 实验4 触发器和存储过程
2022-05-02 10:52:35数据库系统原理 实验4 触发器和存储过程 -
SqlServer数据库存储过程、函数、触发器10题及答案
2021-12-22 09:57:03SqlServer数据库存储过程、函数、触发器10题及答案 -
数据库原理 实验四 存储过程与触发器、函数.doc
2020-11-25 16:32:28数据库原理 实验四 存储过程与触发器函数 2012级计算机专业 集美大学计算机工程学院 20132014年第一学期 数据库原理实验报告 题目实验四 存储过程与触发器函数 班级 计算12 姓名 学号 日期2014.05 指导老师林颖贤 成... -
PL/SQL编程:过程函数触发器题目分析
2022-01-09 18:59:21编写一个存储过程,能通过“类型名称”直接从商品信息表中获取对应类型的商品数据 题目中的关键字:过程,通过类型名称,直接获取,商品数据 分析:首先理清表中各个字段的关系,类型名称在goodstype表中,商品数据... -
oracle 数据库=存储过程和触发器练习习题(1)
2018-06-01 16:47:58--1. 定义过程,根据雇员编号找到雇员姓名及工资。create or replace procedure pro_select is empno1 emp.empno%type; ename1 emp.ename%type; sal1 emp.sal%type; begin select empno,ena... -
Mysql索引、视图、存储过程和函数、触发器、存储引擎(一)
2021-03-03 21:52:113、存储过程和函数存储过程和函数是 事先经过编译并存储在数据库中的一段 SQL 语句的集合,调用存储过程和函数可以简化应用开发人员的很多工作,减少数据在数据库和应用服务器之间的传输,对于提高数据处理的效率是... -
MySQL 的触发器、存储过程、函数、视图实验
2021-01-28 07:12:06本实验完成如下结果:0.... 建立一个向userinfo表新增记录的存储过程3. 根据userinfo表的出生日期字段,我们将建立一个简单算得年龄的自定义函数4. 创建一个userinfo的视图,调用年龄函数0.准备相关... -
MYSQL如何导出存储过程和触发器?
2021-01-19 03:41:26《大型网站系统与Java中间件实践》读书笔记分布式系统的基础知识 阿姆达尔定律 多线程交互模式 互不通信,没有交集,各自执行各自的任务和逻辑 基于共享容器(如队列)协同的多线程模式->生产者-消费者->队列 通过... -
【048】存储过程与触发器的区别?
2020-04-06 10:36:52♣题目部分 存储过程与触发器的区别? ♣答案部分存储过程:是一个预编译的SQL 语句,优点是允许模块化的设计,就是说只需创建... -
【期末复习】存储过程与触发器
2018-06-24 10:14:14为前缀,并且系统存储过程主要是从系统表中获取信息,从而为系统管理员管理SQL Server 提供支持。 CREATE PROCEDURE Query_Study ( @sno char(6), @sn char(20) OUTPUT , @dept char(10) OUTPUT ) Deleted 表... -
数据库实验五、六(存储过程、游标与触发器)
2021-12-11 20:26:321、 掌握存储过程、函数的定义 2、 掌握存储过程、函数的调用 3、 掌握游标编程 4、掌握触发器程序设计和使用 -
数据库上机实验四(数据完整性与存储过程、触发器)
2022-04-22 08:53:57数据库实验四,关于数据完整性与存储过程、触发器! -
sql server 练习3(Transcat-SQL程序设计,触发器和存储过程)
2018-06-01 00:09:59sql server 的Transcat-SQL程序设计,触发器和存储过程 这篇博客主要简单讲一下sql server数据库的一些高级操作,那就是:Transcat-SQL语句,触发器和存储过程,由于边幅问题,这里这是简单地说一下概念,注意事项... -
数据库中的触发器(加题目)
2021-03-13 22:31:59触发器(trigger)是SQL server 提供给程序员和数据分析员来保证数据完整性的一种方法,它是与表事件相关的特殊的存储过程,它的执行不是由程序调用,也不是手工启动,而是由事件来触发,比如当对一个表进行操作( ... -
数据库实验:数据库和表、查询、视图与安全性、存储过程及游标、触发器、综合实验-水果商店进阶
2021-10-24 22:21:20数据库实验1~8汇总实验一、数据库和表源码1:源码2:小结实验二、查询源码小结实验三、视图、安全性源码:小结实验四、存储过程及游标源码小结实验五、触发器源码:小结实验六、综合实验-水果商店进阶一源码:小结... -
数据库练习--触发器与存储过程(复习)
2022-05-12 18:53:341.创建商品价格修改记录表:price_log,表结构如下: 列名 数据类型 是否为空 其他约束 说明 id ... -- 保留两位小数 用convert(),round(a,x) cast(xx as decimal(10,2)) -- 要注意题目是存储函数!!!不是存储过程... -
存储过程 VS 触发器
2017-05-08 10:01:16一:存储过程 框架 1.查询 Select 查询新闻表News前5条记录,字段有ID,title,caid,且按创建时间降序排序 2.增 Insert 3.删 Delete 4.改Update 【在查询器中设计查询】 查询——在编辑器中设计查询——出现... -
sql实现存储过程索引触发器等
2012-08-14 23:11:46选择题目,AVG(得分) stumarks 组主题 计算AVG(AVG(得分)) 选择以上(按得分顺序排列)*,排名() 从stumarks 从stumarks 选择*,DENSE_RANK(),(按得分顺序排列) 从stumarks 选择*,ROW_... -
mysql存储过程和触发器 --- 一次很有意思的尝试
2017-02-08 12:08:06员工表 Employees ID money, 根据需要自己在添加字段来完成 销售业绩表 Salys SID money 根据需要自己在添加字段来完成 关系是一个员工对应多个销售业绩 ...(最好用触发器和存储过程) 网上看到这个题 -
数据库之MySQL(MySQL学习笔记)——数据库视图、触发器、存储过程
2019-08-23 19:53:51数据库视图 什么是视图? 视图是虚拟表,是对实表的一种映射。 视图还可以从已经存在的视图的基础上定义。 ...简单性、安全性、逻辑数据独立性 ...题目:制作一个视图提供给销售人员,他们只需要知道商品名称、出售... -
第七章 事务、过程、触发器
2016-12-17 11:17:111、事务 (transaction)事务就是将一系列操作作为一个不可分割的整体,要么一起执行失败,要么一起执行成功 。... 存储过程类似Java中的方法,可以反复使用,用来执行一些列应用复杂的应用规则。 ... -
牛客网Verilog快速入门题目收获——异步复位的串联T触发器(VL2)
2022-04-08 11:03:24掌握 异步复位和同步复位 掌握 T触发器 -
FPGA面试题目笔记(二)——同步异步D触发器、静动态时序分析、分频设计、Retiming
2022-05-13 09:15:57二选一多路器实现实现异或。LUT 。寄存器锁存器,为什么多用寄存器,行为级描述中锁存器如何产生。 D触发器实现2倍分频。 高阻态。`include和条件编译。行为级和RTL级。 -
SQL使用DOS命令建库。建表,添加约束,标量值函数,存储过程,触发器,游标
2017-11-08 17:32:23建表,添加约束,标量值函数,存储过程,触发器。 代码建库: 为了复习在SQL中使用DOS命令,开头便启用了高级选项,详细请看代码. [sql] view plain copy print? use master go...