精华内容
下载资源
问答
  • 存储过程和触发器实验(至少包括完整的调试通过的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的博客

    展开全文
  • 有关于数据库存储过程和触发器实验,大家可以看看....
  • 数据库实验存储过程触发器

    千次阅读 2020-04-16 16:48:10
    (1)了解存储过程的概念、优点 (2)熟练掌握创建存储过程的方法 (3)熟练掌握存储过程的调用方法 (4)了解触发器的概念、优点 (5)掌握触发器的方法步骤 (6)掌握触发器的使用 二、实验环境 PLSQL Developer...

    一、实验目的
    (1)了解存储过程的概念、优点
    (2)熟练掌握创建存储过程的方法
    (3)熟练掌握存储过程的调用方法
    (4)了解触发器的概念、优点
    (5)掌握触发器的方法和步骤
    (6)掌握触发器的使用
    二、实验环境
    PLSQL Developer 12
    Oracle Database 11 home
    三、实验步骤、出现的问题及解决方案
    实验步骤:
    1、建立存储过程完成图书管理系统中的借书功能,并调用该存储过程实现借书功能。
    功能要求:
     借书时要求输入借阅流水号,借书证号,图书编号。(即该存储过程有3个输入参数)
     借书时,借书日期为系统时间。
     图书的是否借出改为“是”。
     调用存储过程实现借书证号“20051001”借出图书编号为“1005050”的图书。
    创建储存过程:
    CREATE OR REPLACE PROCEDURE 借书(借阅流水号 VARCHAR,借书证号 VARCHAR,图书编号 VARCHAR)
    AS
    BEGIN
    INSERT INTO 借阅 VALUES(借书.借阅流水号,借书.借书证号,借书.图书编号,TO_DATE(TO_CHAR(SYSDATE,‘YYYY/MM/DD’),‘YYYY/MM/DD’),’’,’’,’’);
    UPDATE 图书 SET 是否借出=‘是’ WHERE 图书.图书编号=借书.图书编号;
    COMMIT;
    END;
    调用储存过程:
    CALL 借书(‘7’,‘20051001’,‘1005050’);
    调用前:
    在这里插入图片描述
    在这里插入图片描述
    调用后:
    在这里插入图片描述
    在这里插入图片描述
    2、建立存储过程完成图书管理系统中的预约功能。
     预约时要求输入预约流水号,借书证号,ISBN。(即该存储过程有3个输入参数)
     存储过程先检查输入的ISBN版本的图书是否都已借出,如果是则进行预约,否则提示“该书目有可借图书,请查找”。
     预约时间为系统时间。
     调用存储过程实现借书证号“20081237”预约ISBN为“9787508040110”的图书。
    创建储存过程:
    CREATE OR REPLACE PROCEDURE 预约图书(预约流水号 VARCHAR,借书证号 VARCHAR,ISBN VARCHAR)
    AS
    预约结果 INT;
    BEGIN
    SELECT COUNT(*) INTO 预约结果
    FROM 图书
    WHERE 图书.ISBN=预约图书.ISBN AND 图书.是否借出=‘否’;
    IF 预约结果!=0
    THEN
    DBMS_OUTPUT.PUT_LINE(‘该书目有可借图书,请查找’);
    ELSE
    INSERT INTO 预约 VALUES(预约图书.预约流水号,预约图书.借书证号,预约图书.ISBN,TO_DATE(TO_CHAR(SYSDATE,‘YYYY/MM/DD’),‘YYYY/MM/DD’));
    COMMIT;
    END IF;
    END;
    调用储存过程:
    CALL 预约图书(‘2’,‘20081237’,‘9787508040110’);
    调用前:
    在这里插入图片描述
    调用后:
    在这里插入图片描述
    3、建立存储过程完成图书管理系统中的还书功能。
     还书时要求输入借书证号,图书编号,罚款分类号(即该存储过程有3个输入参数)。
     还书日期为系统时间。
     图书的是否借出改为‘否’。
     调用存储过程实现借书证号“20051001”归还图书编号为“1005050”的图书。
    创建储存过程:
    CREATE OR REPLACE PROCEDURE 还书(借书证号 VARCHAR,图书编号 VARCHAR,罚款分类号 VARCHAR)
    AS
    BEGIN
    UPDATE 借阅
    SET 借阅.归还日期=TO_DATE(TO_CHAR(SYSDATE,‘YYYY/MM/DD’),‘YYYY/MM/DD’),借阅.罚款分类号=还书.罚款分类号
    WHERE 借阅.借书证号=还书.借书证号 AND 借阅.图书编号=还书.图书编号;
    UPDATE 图书 SET 是否借出=‘否’ WHERE 图书.图书编号=还书.图书编号;
    COMMIT;
    END;
    调用储存过程:
    CALL 还书(‘20051001’,‘1005050’,‘1’)
    调用前:
    在这里插入图片描述
    在这里插入图片描述
    调用后:
    在这里插入图片描述
    在这里插入图片描述
    4、通过序列和触发器实现借阅表中借阅流水号字段的自动递增。
    创建序列:
    CREATE SEQUENCE 借阅流水号序列 START WITH 8;–创建序列,并且从8开始
    创建触发器:
    CREATE OR REPLACE TRIGGER 自动递增
    BEFORE INSERT ON 借阅
    FOR EACH ROW
    BEGIN
    SELECT 借阅流水号序列.NEXTVAL INTO :NEW.借阅流水号 FROM DUAL;
    END;
    5、修改借书功能的存储过程。
    该存储过程要求:
    (1)借书时输入借书证号,图书编号。(即该函数有2个输入参数)
    (2)借书时,借书日期为系统时间。
    *该存储过程主体部分只有insert into语句。
    修改储存过程:
    CREATE OR REPLACE PROCEDURE 借书(借书证号 VARCHAR,图书编号 VARCHAR)
    AS
    BEGIN
    INSERT INTO 借阅 VALUES(’’,借书.借书证号,借书.图书编号,TO_DATE(TO_CHAR(SYSDATE,‘YYYY/MM/DD’),‘YYYY/MM/DD’),’’,’’,’’);
    COMMIT;
    END;
    6、建立与借书存储过程相对应的触发器,当借阅表中加入借阅信息时,该触发器触发,自动修改所借图书的是否借出改为‘是’。
    创建触发器:
    CREATE OR REPLACE TRIGGER 自动是否借出
    AFTER INSERT ON 借阅
    FOR EACH ROW
    BEGIN
    UPDATE 图书 SET 是否借出=‘是’ WHERE 图书.图书编号=:NEW.图书编号;
    END;
    调用修改后的借书储存过程以及激活触发器:
    CALL 借书(‘20051001’,‘1005050’);
    CALL 借书(‘20051001’,‘2001231’);
    调用前:
    在这里插入图片描述
    在这里插入图片描述
    调用后:
    在这里插入图片描述在这里插入图片描述
    出现的问题及解决方案:
    1、PLSQL创建储存过程编译出错不会给出错误提示,导致调用时提示储存过程处于无效状态。解决方案:使用SQLPLUS,不过SQLPLUS只会提示编译错误,不会提示具体原因,还可以使用Navicat工具,Navicat会给出更加详细的错误原因,仅供参考。
    2、创建储存过程时,设置变量参数类型时,指定了字符长度导致创建失败。解决方案:直接设置变量数据类型,不设置其字符长度。
    3、使用TO_DATE(SYSDATE,‘YYYY/MM/DD’)获取当前日期作为借阅日期导致调用借书储存过程失败,提示参数类型错误。解决方案:因为TO_DATE()函数是将字符类型转换成日期类型,而SYSDATE本来就是日期类型,所以导致调用失败,使用TO_DATE(TO_CHAR(SYSDATE,‘YYYY/MM/DD’),‘YYYY/MM/DD’)将SYSDATE转换成字符类型再转换成日期类型。
    4、使用DBMS_OUTPUT.PUT_LINE()函数输出提示,没有反应。解决方案:在SQLPLUS中需要先使用SET SERVEROUTPUT ON;打开输出模式才能看见输出,而在PLSQL中输出的内容在另一个Output窗口中,而不是没有反应。
    5、创建自动递增借阅流水号的触发器时,使用NEW关键字改变借阅流水号,导致创建触发器失败,解决方案:使用NEW关键字时,需要在前面加一个“:”号,如“:NEW.借阅流水号”。
    6、调用修改后的借书储存过程时,发送错误,提示违反唯一约束条件以及COMMIT;不能再触发器中使用。解决方案:删除在触发器中的COMMIT;,然后删除序列“借阅流水号序列”,重新创建序列“借阅流水号序列”,并且设置初始值为8,因为借阅表中已经有借阅流水号1到7的数据了,然后创建序列时未指定初始值,序列默认从1开始,导致违反唯一约束条件,从而导致调用储存过程失败。
    四、实验心得体会
    通过本次实验,学会了储存过程以及触发器和序列的使用方法,对存储过程有了一个直观的认识,对触发器的工作原理和作用有了更加深刻的认识,使用触发器可以在修改数据前后规范数据,使数据规范化和标准化。使用储存过程将一系列的相关联的数据库操作封装成一个储存过程,使数据库操作更加简便,数据修改更加规范,数据库设计更加严谨。

    展开全文
  • MYSQL数据库实验(存储过程触发器)

    千次阅读 2018-12-29 11:47:19
    个人配置说明:5.7.24 MySQL Community Server (GPL),环境CentOS 7 1.MYSQL 不支持语句触发... 实验: 数据库存储和触发器实验 一、实验目的 理解,实现并逐渐熟悉存储过程的使用 存储过程(Stored Procedure)是...

    个人配置说明:5.7.24 MySQL Community Server (GPL),环境CentOS 7

    1.MYSQL 不支持语句触发(for each statement),只支持行触发(for each row,新旧数据通过关键字new和old区别)

    		实验:  数据库存储和触发器实验 
    

    一、实验目的

    1. 理解,实现并逐渐熟悉存储过程的使用
      存储过程(Stored Procedure)是指一组可编程的函数,是为了完成特定功能的SQL语句集,经编译创建并保存在数据库中,用户可通过指定存储过程的名字并给定参数(需要时)来调用执行。使用它的目的主要是它不用像SQL语句一样解释执行,而是相当于二进制文件直接运行即可,提高了效率.
    2. 掌握触发器的定义和使用
      触发器是与表有关的数据库对象,在满足定义条件时触发,并执行触发器中定义的语句集合。这在实际使用过程中非常实用.作为一名合格的数据库管理人员,是必须要掌握的.这节实验的目的也在于此.

    二、实验环境
    MYSQL

    三、实验前准备
    (1)准备电脑,课本(数据库系统概论第五版)
    (2)了解与存储过程有关的命令
    (3)了解与触发器有关的命令

    四、实验内容与步骤

    1. 理解,实现并逐渐熟悉存储过程的使用

    ①创建对应的数据表,并实现例8.9(从账户1转制定数额的款到账户2中,假设数据表是Acount(num,tatal))
    Procedure:程序
    1. SQL语句

    use tempdb ; 
    create table  Account (AccountId int primary key ,Total double ); 
    insert into  Account values(1,10000); 
    insert into  Account values(2,20000); 
    insert into  Account values(3,30000); 
    insert into  Account values(4,40000); 
    delimiter // 
    create procedure zhuan(inAccountId int,outAccountId int ,amount double)
           begin  
           declare output double ;  
           declare input double; 
               select total into output from Account where AccountId = outAccountId; 
               select total into input from Account where AccountId = inAccountId; 
               update Account set total=total-amount  where AccountId = outAccountId  ; 
               update Account set total=total+amount  where AccountId =  inAccountId; 
          end // 
    delimiter ;
    

    初始状态:
    在这里插入图片描述

    执行 call zhuan(1 2 4000);
    2. 执行结果

    在这里插入图片描述

    1. 掌握触发器的定义和使用
      ① 实现例5.23(定义一个before行级触发器,为教师表Teacher定义完整性规则"教师的工资不得低于4000元,如果低于4000元,则自动的改为4000元")
      ① 先查看有没有定义这样的触发器

       show triggers\G;
      

    在这里插入图片描述

    ②定义这样的触发器

    drop table  if exists Teacher ; 
    create table Teacher(TID int  auto_increment primary  key ,
    name varchar(24) not null ,
    money int unsigned ,
    time timestamp default current_timestamp ); 
    
    delimiter $$ 
    CREATE TRIGGER tr1 BEFORE INSERT ON Teacher  
    FOR EACH ROW  
       BEGIN  
           IF (NEW.name='professor' and NEW.money < 4000) THEN  
               SET NEW.money = 4000;  
           end if;   
       end $$ 
    delimiter ;
    

    在这里插入图片描述
    ③测试 ( 看Teacher表里面有什么,并且插入,进行测试)

    Select * from Teacher ;
    insert into Teacher  (name,money) values("professor",1000); 
    insert into Teacher  (name,money) values("lecturer",3000); 
    insert into Teacher  (name,money) values("professor",0); 
    insert into Teacher  (name,money) values("lecturer",3000);
    

    在这里插入图片描述

    途中还用了truncate语句
    ④删除该触发器

    Drop trigger tri ;
    show triggers \G;
    

    在这里插入图片描述

    五、评价分析及心得体会
    通过本次实验我掌握了数据库中存储过程与触发器的使用,做了大量的练习,也明白在构建大型系统时尽量少使用触发器(不到萬不得已的情况下尽量不要使用! 另外,能用存储过程代替的触发器就用存储过程代替.)对数据库的认识也有了很大的进步,同时也掌握了这些操作。希望再接再厉,继续努力!!!

    tips :

    1.mysql清空表中的数据:https://blog.csdn.net/chenshun123/article/details/79676446

    truncate table table_name;
    

    2.chrome 篇

    1. chrome 打开不小心刚刚关闭的网页:ctrl+shift+T
    2. 新打开一个标签:ctrl+T
    3. 切换标签:ctrl+tab 或者是 ctrl+pg(up/down)

    3.vim 篇

    在这里插入图片描述

    1. vim 多行编辑:

       `CTRL+v` 进入“可视 块”模式,选取-> `I` (大写i )->写入->保存即可。
      
    2. vim 多行删除:

       `CTRL+v `进入“可视 块”模式,选取-> `d` 删除。
      
    展开全文
  • 实验内容及程序代码 1sch数据表的表结构如表71所示,sch表的数据如表72 表71 sch 表结构 字段名 数据类型 主键 外键 非空 唯一 自增 id INT(10) 就是 否 就是 就是 否 n ame VARCHAR(50) 否 否 就是 否 否 class ...
  • 一、实验目的 1.了解存储过程的概念、优点 2.熟练掌握创建存储过程的方法 3.熟练掌握存储过程的调用方法 4.了解触发器的概念、优点 5.掌握触发器的方法步骤 6.掌握触发器的使用
  • 4. 掌握存储过程的管理维护。 5. 理解触发器的用途、类型工作原理。 6. 掌握利用T-SQL语句创建维护触发器的方法。 7. 掌握利用SQL Server Management Studio创建、维护触发器的方法。 二、 实验内容(实验...
  • 实验存储过程和触发器

    千次阅读 2018-12-16 14:02:47
    实验存储过程和触发器 一.实验目的: 1.掌握存储过程的创建及执行 2.掌握触发器的创建及测试 二.实验内容:(所有题写到实验报告中) 1 存储过程的创建及执行 1) ①在stuinfo数据库中创建一个存储过程p1,...

    实验七 存储过程和触发器

    一.实验目的:

    1.掌握存储过程的创建及执行

    2.掌握触发器的创建及测试

    二.实验内容:(所有题写到实验报告中)

    1 存储过程的创建及执行

    1)

    ①在stuinfo数据库中创建一个存储过程p1,查询指定学号的学生的姓名和平均分,要求姓名和平均分使用返回参数。

    ②执行该存储过程查询学号为101的学生的姓名和平均分。

    create proc p1 
    @num char(5)
    as
    select student.sname, AVG(degree) as 平均分 
    from score, student 
    where  student.sno = @num and student.sno = score.sno group by student.sno, student.sname
    
    exec p1 '103'
    # 由于在后续实验中将学号为 '101' 学生删除了,此处及后续截图均为查询 '103'
    --- 此答案错误,见下图后面的修改!
    


    修改: 未经测试!题干要求返回结果,因此原答案不对,改为以下:

    create proc p1 
    @num char(5),
    @name char(5) output,
    @davg float output
    as
    select @name = student.sname, @davg = AVG(degree) from score, student 
    where  student.sno = @num and student.sno = score.sno group by student.sno, student.sname
    
    declare @name char(5)
    declare @davg float
    
    exec p1 '103', @name output, @davg output
    print '姓名    平均分'
    print @name + '  ' + cast(@davg as char(5))
    

    2)

    ①在stuinfo数据库中创建存储过程P2,根据指定的学号和课程号判断成绩等级(A、B、C、D、E)(如果degree>=90则为A,依次类推,不及格为E)。

    ②执行该存储过程查看学号为101、课程号为3-105的成绩等级。

    create proc p2
    @sno char(5), @cno char(6)
    as
    select 成绩等级 = 
    case
    when degree >= 90 then 'A'
    when degree >= 80 and degree < 90 then 'B'
    when degree >= 70 and degree < 80 then 'C'
    when degree >= 60 and degree < 70 then 'D'
    else 'E'
    end
    from score where cno = @cno and sno = @sno 
    
    exec p2 '103', '3-105'
    

    3)

    ①在stuinfo数据库中创建存储过程P3,检查指定学号的学生是否有选课。有就输出其姓名、课程名和成绩,没有就输出“该生无选课”。

    ②执行该存储过程分别查看学号为101和888学生的选课情况。

    create proc p3
    @sno char(5)
    as 
    if (exists (select * from score where sno = @sno))
        select sname, cname, degree from course, student, score where student.sno = @sno and score.sno = student.sno and score.cno = course.cno 
    else
        print '该生无选课'
    
    exec p3 '103'
    exec p3 '888'
    


    4)

    ①在OrderManagement数据库中创建存储过程P4,查询指定客户号的订单中有多少种器件。

    ②执行该存储过程查看客户号是C0001的客户所订购的器件种类的数量。

    use OrderManagement
    create proc p4
    @cusnum char(6)
    as
    select count(器件号) as 器件种类 from order_detail, order_list where order_list.客户号 = @cusnum and order_detail.订单号 = order_list.订单号
    
    exec p4 'C10001'
    

    5)

    ①在OrderManagement数据库中创建存储过程P5,查询指定年份的销售总额。

    ②执行该存储过程查看2001年的销售总额。要求执行完存储过程后按如下格式输出数据:

      2001年的销售总额:
      ----------------------------
      ******元
    
    create proc p5
    @year int
    as
    declare @total int
    set @total = (select sum(单价 * 数量) from order_detail, order_list where year(order_list.订购日期) = @year and order_detail.订单号 =  order_list.订单号)
    print cast(@year as varchar) + '年的销售总额:'
    print '----------------'
    print @total
    
    exec p5 '2001'
    

    6)

    ①在OrderManagement数据库中创建存储过程P6,修改指定订单号和器件号的单价。

    ②执行该存储过程将订单号为OR-01C、器件号为P1001的单价修改为1000。

    create proc p6
    @ordernum char(6), @machinenum char(5), @newprice int
    as
    update order_detail set 单价 = @newprice where 订单号 = @ordernum and 器件号 = @machinenum
    
    exec p6 'OR-01C','P1001','1000'
    select * from order_detial where 订单号 ='OR-01C' and  器件号 = 'P1001'
    

    7)

    ①在OrderManagement数据库中创建存储过程P7,查询订单中至少订购了“CPU P4 1.4G”和“内存”这两种器件的订单号。

    ②执行该存储过程。

    create proc p7
    as
    select a.订单号 from 
    (select  订单号 from order_detail where 器件名 = '内存') as a,
    (select  订单号 from order_detail where 器件名 = 'CPU P4 1.4G') as b
    where a.订单号 = b.订单号
    
    exec p7
    


    2. 触发器的创建及测试

    1)

    ①在student表上创建触发器t1,在用户插入、修改和删除记录时,都会自动显示表中的内容。

    ② 测试:对student表分别用insert、update和delete语句进行测试。

    use stuinfo
    create trigger t1
    on student
    after insert,update,delete
    as
    select * from student 
    
    insert into student (sno, sname) values ('0242', 'zzz')
    update student set ssex = '男' where sno = '0242'
    delete from student where sno = '0242'
    

    2)

    ①建立一个触发器t2,当向student表中插入数据时,如果姓名不重复则插入,如果出现姓名重复的情况,则提示错误(raiserror(‘姓名重复,不能插入’,16,1))并回滚该事务(即取消插入的行)。

    ② 测试:对student表分别用insert语句插入一条姓名重复的记录和姓名不重复的记录进行测试。

    create trigger t2
    on student
    after insert
    as
    declare @sname char(10)
    declare @num int
    select @sname = sname from inserted
    select @num = COUNT(*) from student where sname = @sname
    if(@num != 1)
    begin
    raiserror('姓名重复,不能插入',16,1);
    rollback tran;
    end
    
    insert into student (sno, sname) values ('0243', '李军')
    

    3)

    ①建立一个触发器t3,当向student表中插入数据时,如果出现性别不正确的情况,不回滚该事务,只提示错误消息。

    ② 测试:对student表分别用insert语句插入一条性别正确的和性别不正确的记录进行测试。

    create trigger t3
    on student
    after insert
    as
    declare @ssex char(2)
    select @ssex = ssex from inserted
    if(@ssex != '男' and @ssex != '女')
    raiserror('Error: 性别不符合规定哦~',16,10)
    
    insert into student (sno, sname, ssex) values ('04257', 'zzz', '5')
    

    4)

    ①一个修改触发器t4,该触发器防止用户修改表student的学号。

    ② 测试:对student表分使用update语句修改学号进行测试。

    create trigger t4
    on student
    after update
    as
    declare @sno_before char(5)
    declare @sno_after char(5)
    select @sno_before = sno from deleted
    select @sno_after = sno from inserted
    if(@sno_before !=  @sno_after)
    begin
    raiserror('学号不能更改',16,1);
    rollback tran;
    end
    
    update student set sno = '103' where sno = '101'
    

    5)

    ①建立一个触发器t5,将student表中所有修改时改前的记录及修改日期保存到ss表中作为历史记录。

    ② 测试: 使用命令查询student表中的信息,再使用命令将student表中的1031班改为1032班,然后使用命令查询ss中的记录检查触发器t2是否正确。

    create trigger t5
    on student
    after update
    as
    declare @no char(5)
    declare @name char(10)
    declare @sex char(2)
    declare @birthday datetime
    declare @class char(10)
    declare @now datetime
    select @no = sno, @name = sname, @sex = ssex, @birthday = sbirthday, @class = sclass from deleted
    set @now = GETDATE()
    if OBJECT_ID(N'ss',N'U') is null
    begin
    create table ss(
    sno char(5) not null,
    sct datetime not null,
    sname char(10),
    ssex char(2),
    sbirthday datetime,
    sclass char(10),
    primary key(sno, sct)
    )
    end
    insert into ss values (@no, @now, @name, @sex, @birthday, @class)
    
    update student set ssex = '男' where sno = '107'
    select * from ss
    

    6)

    ①创建触发器t6,当删除student表中的某个学生记录时,应该也同时删除score表中该生的选课记录。

    ②测试:在student表中添加一条学号为222的学生记录,在score表中添加该学号三条选课记录,然后删除学生表中的该学号的学生记录,检查score表中学号为222的记录是否还存在,从而测试该触发器是否正确。

    create trigger t6
    on student
    after delete
    as
    declare @sno char(5)
    select @sno = sno from deleted
    delete from score where sno = @sno
    
    insert into student (sno, sname) values ('222', 'sdust')
    insert into score values ('222', '3-105', 100)
    insert into score values ('222', '6-166', 100)
    insert into score values ('222', '3-245', 100)
    select * from score where sno = '222'  -- 察看
    delete from student where sno = '222'
    select * from score where sno = '222'  -- 察看
    


    7)

    ①创建触发器t7,当修改student表中的某个学生学号时,同时也修改score表中该生的学号。

    ②测试:使用命令分别查询student表和score表中学号为的学生信息,再使用命令将student表中101的学号的改为999,然后使用命令再分别查询student表和score表中学号为101和的999的记录。

    -- 执行这条的时候需要将  t4 禁用
    disable trigger t4 on student -- 禁用
    
    create trigger t7
    on student
    after update
    as
    declare @sno_before char(5)
    declare @sno_after char(5)
    select @sno_before = sno from deleted
    select @sno_after = sno from inserted
    update score set sno = @sno_after where sno = @sno_before
    
    update student set sno = '999' where sno = '101'
    select * from student where sno = '101'
    select * from score where sno = '101'
    select * from student where sno = '999'
    select * from score where sno = '999'
    

    展开全文
  • 实验触发器和存储过程 实验目的: (1)掌握某主流DBMS支持的SQL编程语言编程规范,规范设计存储过程; (2)能够理解不同类型触发器的作用执行原理,验证触发器的有效性; (3)培养学生的系统思维,提升解决...
  • 数据库实验——触发器存储过程 掌握某主流DBMS的SQL编程语言,在前面创建的数据库基础上,定义BEFORE(for)触发器和AFTER触发器;掌握数据库存储过程定义、存储过程运行,存储过程更名,存储过程删除,存储过程...
  • 数据库实验六:数据可存储过程和触发器定义和使用相关实验试题及代码
  • 计算机科学系实验报告(首页) ...触发器和存储过程 教导教师 索剑 姓名 张钦颖 学号 1414080901218 组号   日期 2016年5月10日
  • 实验四 存储过程触发器与索引 一、实验目的 1.熟悉大型数据库实验环境,以MS SQL SERVER为例; 2.掌握视图; 3.掌握存储过程触发器; 4.掌握MS SQL SERVER的导入导出; 5.掌握MS SQL SERVER的索引。 二、实验...
  • 存储过程触发器和用户自定义函数实验 实验内容一 练习教材中存储过程触发器和用户自定义函数的例子。教材中的BookSales数据库,在群共享中,文件名为BookSales.bak。 实验内容二 针对附件1中的教学活动数据库,...
  • 掌握创建视图、存储过程和触发器对象的使用方法; 1.创建一个只选修8号课程的学生情况的视图信息 create view stu_8 as select student.sno,student.sname,sage,ssex from student,sc where student.sno=sc.sno and ...
  • 1.在数据库HrSystem 中创建存储过程avg_wage,用于求所有员工的平均工资,并通过输出参数返回该平均工资。要求在创建存储过程之前首先判断该存储过程是否已经存在,如果存在,则将其删除。 方法: USE...
  • 实验目的】 1、  ...SERVER高级设计存储过程和触发器的原理; 5、  可以简单的设计存储过程和触发器;   【实验原理】 1、  存储过程基本原理: 1.1系统存储过程 系统存储过程...

空空如也

空空如也

1 2 3 4 5 ... 8
收藏数 145
精华内容 58
关键字:

数据库实验存储过程和触发器