存储过程和触发器(数据库实验5)
实验5：存储过程和触发器
云南大学软件学院实验报告课程：数据库原理与实用技术实验学期：2014-2015学年第二学期任课教师：薛岗、朱艳萍专业：学号：姓名：成绩：实验5 存储过程和触发器一、实验目的（1）理解存储过程的概念、了解存储过程的类型（2）掌握创建存储过程的方法（3）掌握执行存储过程的方法（4）理解触发器的功能及工作原理。
（5）掌握创建、更改、删除触发器的方法。
二、实验内容1、使用不带参数的存储过程（1）创建一个存储过程，查询person表中所有不重复的职称。
（2）执行存储过程。
2、带输入参数的存储过程（1）创建一个存储过程，按照姓名查询person表中的员工信息。
（2）执行存储过程，查询名为黎明的员工数据3、带输入/输出参数的存储过程（1）创建一个存储过程，使其能够根据员工姓名，查询员工工资。
（考虑到员工不存在时给出提示信息）（2）执行存储过程4、使用触发器（1）创建一个触发器trig_update，返回对person进行更新操作后，被更新的记录条数（2）执行触发器（3）修改触发器trig_update，除返回被更新的记录条数外，再返回学生的所有基本信息5、使用触发器的两个特殊表：插入表（inserted）和删除表（deleted）。
（1）在person上创建触发器ins_del_sample，在插入、删除或更新操作后，分别从inserted表和deleted表中查询员工所有信息。
（请同学们在做删除操作时，注意备份）（2）执行触发器。
思考执行插入、删除和更新操作后返回的表有什么区别？6、使用系统存储过程查看触发器（与存储过程的使用类似）（1）显示触发器trig_update的一般信息（2）显示触发器trig_update的源代码（3）显示person上所有的依赖关系（4）显示触发器trig_update所引用的对象。
实验五 触发器及存储过程的使用
实验五触发器与存储过程
【实验目的】：①掌握触发器的使用
②掌握存储过程的使用
【实验内容】：相关命令写在作业本上。
1、建立银行业务数据库bankdb，其中，帐户信息表(bank)存放帐户的信息，交易信息表(transInfo)存放每次的交易信息。
（1）当向交易信息表(transInfo)中插入一条交易信息时，自动更新对应帐户的余额。
（2）当删除交易信息表时，要求自动备份被删除的数据到表backupTable中。
（3）跟踪用户的交易，交易金额超过20000元，则取消交易，并给出错误提示。
2、在学生成绩数据库中，利用存储过程，查询每门考试的平均分，若平均分大于85分，显示“优秀”，否则显示“较差”，并查询这门课中未通过考试的学生名单。
3、有程序员工资表prowage(id int,panme char(10),wage int)，其中id是程
wage是工资。
创建一个存储过程，对程序员的工资进行分析，如果有百分之五十的人薪水不到2000，给所有人加薪，每次加100，再进行分析，直到有一半以上的人大于2000元为止。
存储过程执行完后，最终加了多少钱？每个人的工资为多少？
思考：如何修改上题的命令创建存储过程，要求查询程序员平均工资在4500元，如果不到，则每个程序员每次加200元，直到所有程序员平均工资达到4500元为止。
【实验答案】：1、（1）命令为：
(2)命令为：
(3)命令为：
2、命令为：
3、命令为：。
实验5 存储过程,函数,触发器操作
实验5 存储过程、函数、触发器操作
已知一个已经建好的数据库factory,现在该数据库上存在三个表
1.职工表worker,其结构为 (职工号 numnber ,姓名 char(8), 性别 char(2) ,
出生日期 date, 党员否 char, 参加工作时间 date , 部门号 number ),其中职工号为主键
2.部门表 department 结构为 ( 部门号 number , 部门名称 varchar(20) ) ,
其中部门号为主键. 通常的部门信息有人事部,市场部,财务处等等
3.职工工资表 salary 其结构为 ( 职工号 number ,姓名 char(10) , 日期
date , 工资 number(10,2) ) . 其中职工号和日期为关键字
在以上的数据库上完成如下操作：
1.创建一个为worker表添加职工记录的存储过程addworker
2.创建一个为给定职工号,查询职工信息及部门名称的存储过程query_worker
3.创建一个为给定职工号,删除worker表中记录的存储过程delete_worker
4.显示存储过程
5.创建一个函数，完成给定职工号返回职工所在部门的名称的函数
6.创建一个函数，完成给定部门号返回该部门的最大年龄的函数
7.在表department上创建一个触发器deaprt_update，当更改部门号时同步更
改worker表中对应的部门号
8.在表worker上创建一个触发器worker_delete，当删除职工记录同步删除对
应职工的工资记录
9.删除两个刚刚建立触发器
以上内容请书写实验报告
余下时间请完成数据库的预备试验。
存储过程和触发器(实验报告)
WITH ENCRYPTION AS
SELECT*
FROM student_info
WHERE性别='男'
EXEC stu_en
DROP PROCEDURE stu_en
4．使用grade表。
（1）创建一个存储过程stu_g_r，当输入一个学生的学号时，通过返回输出参数获取该学生各门课程的平均成绩。
CREATE PROC stu_g_r @stu_no varchar(8)=NULL,
@stu_score real OUTPUT
AS
SELECT@stu_score=AVG(分数)
FROM grade
WHERE (学号=@stu_no)
（2）执行存储过程stu_g_r，输入学号0002。
DECLARE @score real
WHERE (a.姓名=@stu_name)
EXEC stu_g_p ‘刘卫平’
sp_helptext stu_g_p
3．使用student_info表。
（1）创建一个加密的存储过程stu_en，查询所有男学生的信息。
（2）执行存储过程stu_en，查看返回学生的情况。
（3）使用Transact-SQL语句DROP PROCEDURE删除存储过程stu_en。
（3）掌握通过SQL Server管理平台和Transact-SQL语句Alter procedure修改存储过程的方法；
（4）掌握通过SQL Server管理平台和Transact-SQL语句Drop procedure删除存储过程的方法；
（5）掌握通过SQL Server管理平台和Transact-SQL语句Create trigger创建触发器的方法和步骤；
实验五触发器与存储过程的设计-西安理工大学
实验五触发器与存储过程的设计-西安理工大学实验五触发器与存储过程的设计实验目的1、掌握创建存储过程的方法和步骤。
2、掌握存储过程的使用方法。
3、掌握创建触发器的方法和步骤。
4、掌握触发器的使用方法。
实验内容、要求以教学管理系统为例，有Student、SC和Course表，根据数据库的完整性要求，自己设计一个存储过程和触发器，当删除SC表的某条记录时，在屏幕上给出提示，当删除该条记录后，若没有学生选择此门课时，要求将Course表中对应的记录删除。
实验环境Microsoft Word, SQL Server 2000环境。
实验过程一、存储过程的设计1、有关概念存储过程是由SQL语句及控制流语句组成的集合。
调用一个存储过程，可以一次性地执行过程中的所有语句。
从这一点来说，它类似于程序。
存储过程由用户建立，它作为数据库的一个成分，存在于数据库中。
存储过程类似VFP中的过程（函数、子程序），它可以接受参数，也可以返回参数。
存储过程可以被客户端、其他存储过程或触发器调用。
以SP_为前缀的存储过程是SQL提供的系统存储过程; 以XP_为前缀的存储过程是扩展的存储过程; 关联到表上的存储过程称为触发式存储过程。
2、设计存储过程使用SQL语句创建存储过程（或使用企业管理器创建存储过程）。
3、执行存储过程对存储在服务器上的存储过程，使用EXECUTE命令执行它。
4、操作存储过程查看、修改、删除存储过程。
二、触发器的设计触发器是一种特殊的存储过程, 用它来控制关联的表。
1、设计一个触发器使用SQL语句创建触发器（使用企业管理器创建触发器）。
2、查看、修改和删除触发器实验六图书管理系统设计及SQL编程实验目的初步掌握数据库系统的开发过程实验内容1.根据数据库设计的基本思想，设计出图书管理系统的概念结构（用E-R图表示）。
2.根据E-R图进行数据库的逻辑设计。
3.在逻辑设计的基础上，进行数据库的物理设计。
4.给出各设计阶段的完整文档（E-R图、逻辑模型、数据字典）。
实验五触发器和存储过程
实验五：触发器和存储过程一、实验目的：理解触发器和存储过程的含义，掌握用SQL语句实现触发器和存储过程的编写，并初步掌握什么情况下使用事务。
二、预习要求：存储过程和触发器的相关概念，事务的相关概念，编写相应的SQL语句。
三、实验内容：（一）为S表的删除操作定义一个触发器，在删除一个供应商记录时，将这个供应商的所有供应情况从spj表中删除。
（二）有一个小型的图书管理数据库，包含的表为：bookstore（bookid，bookname，bookauthor，purchasedate，state）；--图书库存表borrowcard（cardid，ownername）；--借书证表borrowlog（cardid，bookid，borrowdate，returndate）；--借书记录表写一个存储过程，实现借书操作，要求有事务处理。
（1）读者借书，要先设置书籍不在库标志state（借出），然后增加借书记录，在同一事务中完成。
（2）要求在事务执行过程中引入错误触发事件，以此体会事务的错误保护机制和事务编程的作用。
（三）完成情况（附上设计的SQL语句）。
建立数据库：create database bookstoruse bookstorcreate table bookstore(bookid int,bookname char(20),bookauthor char(20),purchasedate char(20),stat char(6),primary key(bookid),Check(stat in('在库','不在库')),);create table borrowcard(cardid int primary key,ownername char(20),);create table borrowlog(cardid int,bookid int,borrowdate char(20),returndate char(20),primary key(cardid,bookid),foreign key(cardid)references borrowcard(cardid),foreign key(bookid)references bookstore(bookid),);存储过程：create procedure [dbo].[jieshu](@cardid int,@bookid int,@borrowdate char(20),@returndate char(20))asbegin transactioninsertinto borrowlogvalues(@cardid ,@bookid ,@borrowdate ,@returndate )if exists(select * from bookstore,borrowcard where bookid=@bookid and stat='在库'and cardid=@cardid)beginupdate bookstoreset stat='不在库'where bookid=@bookidcommit transactionendelsebeginif not exists(select*from bookstore where bookid=@bookid)print'不存在该书'if not exists(select*from borrowcard where cardid=@cardid)print'没有此用户'rollback transactionEnd查询语句：exec jieshu 1002062,1,'20121103','2012124'结果：触发器:create trigger storon borrowlogafter insertasif(new.borrowdate>new.returndate)print'失败，还书时间不应早于结束时间'beginrollback transactionend四、实验方法和步骤：（一）在查询分析器中编写实现上述功能的触发器，参考Create Trigger语法。
存储过程及触发器实验报告
数据库技术与应用实验报告七班级：机械因材学号： 16 姓名：高永吉一：实验名称：存储过程及触发器二，实验目的：⑴ 使用系统常用的存储过程；⑵ 掌握存储过程的创建及应用(3) 理解触发器的概念；(4) 掌握触发器的创建及应用。
三．实验内容、过程和结果：存储过程1创建一个存储过程，查看学号为1（根据实际情况取）的学生的信息，包括该学生的学号，班级编号，姓名。
（提示：查询涉及到表Student）2执行1中创建的存储过程。
3使用输入参数创建题1中的存储过程。
题1中所创建的存储过程只能学号为1的学生信息进行查看，要想对其他学生进行查看，需要进行参数传递。
4执行3中创建的存储过程，（1）按位置传递参数；（2）通过参数名传递参数；5触发器1）在课程表Course上创建一个触发器，该触发器被操作DELETE所触发，且要求触发触发器的DELETE语句在执行被取消。
2）在表Student中建立插入触发器, 插入一条记录时，若年龄>100或者年龄<=0,拒绝插入记录并显示：“年龄不符合规定，无法插入此记录！”；3）创建一个触发器，如果在Student表中添加或更改数据，向客户端显示一条消息“你正在插入或修改学生表的数据”，要求触发触发器的DELETE、UPDATE语句被执行。
4 ）为Course表创建一个名称为my_trig的触发器，当用户成功删除该表中的一条或多条记录时，触发器自动删除Student表中与之有关的记录。
5 ）使用系统存储过程查看创建的触发器。
图一：创建一个存储过程，查看Tno为1（根据实际情况取）的教师的信息，包括该教师的姓名，sal图二执行1中创建的存储过程。
图三使用输入参数创建题1中的存储过程。
图四执行3中创建的存储过程，（按位置传递参数）图五执行3中创建的存储过程通过参数名传递参数；图六使用系统存储过程查看3中创建的存储过程图七删除3中创建的存储过程。
图八在Teacher上创建一个触发器，该触发器被操作DELETE所触发，且要求触发触发器的DELETE语句在执行被取消。
实验五 存储过程和触发器的使用
实验五存储过程和触发器的使用【目的要求】1、了解存储过程的基本概念和类型。
2、了解创建存储过程的T-SQL语句的基本语法。
3、了解查看、执行、修改和删除存储过程的T-SQL命令的用法。
4、了解触发器的基本概念和类型。
5、了解创建触发器的T-SQL语句的基本语法。
6、了解查看、修改和删除存储过程的T-SQL命令的用法。
【实验内容】内容一：存储过程的使用一、数据需求分析存储过程是一种数据库对象，为了实现某个特定任务，将一组预编译的SQL语句以一个存储单元的形式存储在服务器上，供用户调用，自动完成需要预先执行的任务。
存储过程在第一次执行时进行编译，然后将编译好的代码保存在高速缓存中便于以后调用，提高了代码的执行效率。
二、内容要点分析1、SQL SERVER支持五种类型的存储过程：系统存储过程、本地存储过程、临时存储过程、远程存储过程和扩展存储过程。
其中，系统存储过程是由系统提供的存储过程，可以作为命令执行各种操作。
系统存储过程定义在系统数据库master中，其前缀是sp_。
本地存储过程是指在用户数据库中创建的存储过程，这种存储过程完成特定数据库操作任务，不能以sp_为前缀。
2、只能在当前数据库中创建存储过程。
3、创建存储过程时，应指定所有输入参数和向调用过程或批处理返回的输出参数、执行数据库操作的编程语句和返回至调用过程或批处理以表明成功或失败的状态值。
4、创建存储过程的T-SQL语句CREATE PROC[EDURE] 存储过程名称[{ @参数名称数据类型 }] [,…n][WITH{ RECOMPILE|ENCRYPTION }]ASSQL语句序列说明：（1）RECOMPILE表明每次运行该过程时，将其重新编译。
（2）ENCRYPTION表示 SQL SERVER 加密SYSCOMMENTS表中包含CREATE PROCEDURE语句文本的条目。
注：必须将CREATE PROCEDURE语句放在单个批处理中。
5 实验五 创建存储过程和触发器
实验五创建存储过程和触发器
一、实验目的
1．通过对常用系统存储过程的使用，了解存储过程的类型；通过创建和执行存储过程，了解存储过程的基本概念，掌握使用企业管理器及查询分析器执行T-SQL语句创建存储过程。
2．通过创建触发器，了解触发器的基本概念，理解触发器的功能，掌握使用企业管理器及查询分析器执行T-SQL语句创建触发器。
二、实验要求
1．实验前做好上机实验的准备，针对实验内容，认真复习与本次实验有关的知识，完成实验内容的预习准备工作；
2．能认真独立完成实验内容；
3．实验后做好实验总结，根据实验情况完成总结报告。
三、实验学时
2学时
四、实验内容
1、使用sp_helptext查看byroyalty存储过程的文本，该存储过程在数据库pubs中。
2、在实验二创建的factory数据库中执行以下操作：
（1）创建一个为worker表添加职工记录的存储过程addworker。
执行并验证存储过程的正确性。
最后删除该存储过程。
（2）在depart表上创建一个触发器depart_update，当更改部门号时同步更改worker 表中对应的部门号。
执行并验证触发器的正确性。
最后删除该触发器。
（3）在worker表上创建一个触发器worker_delete，当删除职工记录时同步删除salary表中对应职工的工资记录。
执行并验证触发器的正确性。
最后删除该触发器。
数据库原理实验指导书(含触发器及存储过程)范文
数据库原理实验指导实验 1 SQL Server2000管理工具的使用一、目的与要求1．掌握SQL Server服务器的安装2．掌握企业管理器的基本使用方法3．掌握查询分析器的基本使用方法4．掌握服务管理器的基本使用方法5．对数据库及其对象有一个基本了解6．掌握用企业管理器和查询分析器创建数据库，修改数据库和删除数据库的方法。
二、实验准备1．了解SQL Server各种版本安装的软、硬件要求2．了解SQL Server支持的身份验证模式3．了解SQL Server各组件的主要功能4．对数据库、表和数据库对象有一个基本了解5．了解在查询分析器中执行SQL语句的方法三、实验内容1．安装SQL Server 2000 根据软硬件环境，选择一个合适版本的SQL Server 2000。
2．利用企业管理器访问系统自带的pubs数据库。
（1）启动SQL Server服务管理器。
通过“开始=>程序=>Microsoft SQL Server=>服务管理器”打开“SQL Server服务管理器”，启动“SQL Server服务管理器”，并记录当前运行的服务器名。
图1.1 启动SQL Server服务管理器（2）启动企业管理器。
通过“开始=>程序=>Microsoft SQL Server=>企业管理器”打开“SQL Server Enterprise Manager”图1.2 启动企业管理器（3）在企业管理器的树形目录中展开数据库，找到pubs并展开，则列出该数据库的所有对象，如表、视图、存储过程、默认和规则等。
（4）选中“表”，将列出pubs数据库的所有表（包括系统表和用户表），在此以用户表publishers为例，选中该表，单击鼠标右键，弹出快捷菜单，执行“打开表—返回所有行”菜单项，打开该表，查看其内容。
（5）在表的尾部插入记录（9943，zhang，Beijing，null，china）和记录（1408，li，shanghai，null，china）。
实验5：存储过程、触发器和视图
实验5：存储过程、触发器和视图第五周实验可编程对象（视图、存储过程和触发器）一．实验目的1．了解视图、存储过程和触发器的基本概念和使用方法。
2．学会用两种方法创建和维护视图、存储过程和触发器等数据库对象：一是在SQL Server Management Studio通过可视化操作实现，一是在查询窗口执行相关T-SQL语句实现。
二．实验环境●SQL Server Management Studio●BookStore数据库提示1：到“课程辅助材料”中下载BookStore数据库，在SQL Server中附加。
三．实验内容说明：标记为▲的是必做题目，其他为选作题目。
首先需要附加BookStore数据库。
1▲．创建视图。
（1）创建视图V_BookSell，使其包含图书销量情况。
要求显示图书代码（BookCode）、图书名称（BookName）、作者（Author）、出版社名称（Publisher）以及数量（Amount）。
（2）创建视图V_CustomerBookOrderDetail。
要求显示订单号（OrderCode）、客户名（Name）、客户等级（VIPClass）、书名（BookName）、单价（Price）、数量（Amount）、折扣（Discount）以及总价（TotalPrice=Price*Amount*Discount）。
（3）创建视图V_CustomerVIPABTotalOrder，汇总客户订单信息。
使其包含用户等级为“A”和“B”、且不姓“郭”和“刘”的客户订单信息，要求显示客户姓名（Name）以及所订图书总金额，并按所订图书总金额降序排列。
2．创建存储过程（1）▲创建存储过程proc_SearchBook，查询指定书名的图书信息。
（2）创建存储过程proc_FuzzySearchBook，实现按书名（全名或部分书名）模糊查询图书信息。
（3）创建存储过程proc_SearchCustomerMoney，查询指定客户在某一年之前的购书总金额（已知客户号和年份，输出总金额）。
实验五 存储过程和触发器
实验五触发器、存储过程操作实验本实验需要2学时。
请大家先根据“触发器.doc”文档完成相关操作，再进行本次实验。
介绍完“存储过程”后，需上交本次实验报告。
一、实验目的（1）掌握SQL Server中的触发器的使用方法；（2）掌握存储过程的操作方法。
二、实验内容1. 创建、查看、修改和删除触发器。
2. 创建、查看、修改和删除存储过程。
三、实验方法1. 触发器的操作（1）建立触发器方法一：使用企业管理器首先，打开企业管理器，定位数据库并找到要创建触发器的表；然后，右击该表名，在弹出的快捷菜单中选择“设计表”，在打开的“设计表”窗口中单击按钮，打开如图1所示窗口。
图1 触发器属性窗口最后，在触发器属性窗口中输入触发器的内容，并单击“确定”按钮。
方法二：使用CREATE TRIGGER语句语法：CREATE TRIGGER <触发器名>ON { <表名> | <视图名>}{ FOR | INSTEAD OF | AFTER}{ [UPDATE] [,] [INSERT] [,] [DELETE]}AS<SQL 语句块>（2）查看触发器方法一：使用企业管理器方法二：使用T-SQL语句- sp_help <触发器名>- sp_helptext <触发器名>- sp_depends <触发器名>（3）修改触发器方法一：使用企业管理器方法二：使用ALTER TRIGGER语句语法：ALTER TRIGGER <触发器名>ON { <表名> | <视图名>}{ FOR | INSTEAD OF | AFTER}{ [UPDATE] [,] [INSERT] [,] [DELETE]} AS<SQL 语句块>（4）删除触发器方法一：使用企业管理器方法二：使用DROP TRIGGER语句语法为：DROP TRIGGER <触发器名>2. 存储过程的操作（1）建立存储过程方法一：使用建立存储过程向导方法二：使用企业管理器方法三：使用SQL语句（CREATE PROCEDURE）语法：CREATE PROC[EDURE] <存储过程名>[{@<参数名> <数据类型>}]AS {<ＳＱＬ语句> | <语句块> }（2）查看存储过程方法一：使用企业管理器方法二：使用SQL语句（系统存储过程）- sp_help <存储过程名>- sp_helptext <存储过程名>- sp_depends <存储过程名>（3）修改存储过程方法一：使用企业管理器方法二：使用SQL语句（ALTER PROCEDURE）语法为：ALTER PROC[EDURE] <存储过程名>[{@<参数名> <数据类型>}]AS {<ＳＱＬ语句>| <语句块> }（4）删除存储过程方法一：使用企业管理器方法二：使用DROP PROCEDURE语句语法为：DROP PROCEDURE <存储过程名>四、实验内容1、在学生表student上建立一个DELETE类型的触发器tr_delete，触发动作是显示信息“已删除学生表中的数据”。
实验报告5 数据库编程存储过程
实验5：数据库编程——存储过程一．实验目的通过本实验使学生掌握存储过程的基本概念和创建、执行、删除方法。
二．实验类型验证型三．实验学时2学时四．实验原理及知识点1．Transact-SQL编程2．存储过程的创建和执行3．存储过程的修改和删除五．实验环境1．硬件设备要求：PC及其联网环境；2．软件设备要求：Windows操作系统；MS SQL Server数据库管理系统。
六．实验内容及步骤利用存储过程实现下面的应用：从账户1转指定数额的款项到账户2中。
假设账户关系表为Account（Accountnum，total）存储过程create procedure pro_transfer(@inAccount int,@outAccount int,@amount float)asdeclare@totalDepositOut float,@totalDepositIn float,@inAccountnum int;beginselect@totalDepositOut=(select total from Account where accountnum =@outAccount);if (@totalDepositOut is null)beginrollback;return;endif (@totalDepositOut<@amount)beginrollback;return;endselect@inAccount=(select accountnum from Account where accountnum =@inAccount)if(@inAccountnum is null)beginrollback;return;endupdate Account set total=toal-@amountwhere accountnum=@outAccount;update Account set total=total+@amountwhere accountnum=@inAccount;commit;end七．实验总结通过这次实验学会写简单的存储过程，了解了在sql server 2008中的存储过程的基本语法，发现存储过程的语法和pascal有很大的相似之处，不过sql server 2008的语法和书上的语法有很大的不同，只能自己去网上找资料，不过这锻炼了我的自学能力，不过就写一个存储过程还是有很多不够，还是要勤加练习。
数据库实验-存储过程、触发器
group by Sno
order by sum(Grade)desc
exec total
drop proc total
执行结果：命令已成功完成。
如图：
(3)创建存储过程，查找某门课的最高分（带输入参数的存储过程）。
代码：
create proc grade1
@Cname char(20)
石家庄经济学院
实验报告
学院:信息工程学院
专业:网络工程
信息工程学院计算机实验中心制
学号
411109030110
姓名
马立杰
实验日期
2014.5.12
实验室
260
指导教师
张有华
设备编号
实验内容
实验6存储过程、触发器
一实验题目
1．存储过程的定义和使用
2．触发器的创建与使用
二实验目的
1．掌握存储过程的定义、执行和调用方法。
如图：
(5)创建存储过程，统计某门课选修的人数，将人数返回。（带返回值的存储过程）
代码：
create proc proc1
@Cno char(10),
@number int output
as
select @number=count(*)
from SC
where Cno=@Cno
group by Cno
where Sno=
(select top 1 Sno from SC
group by Sno
order by count(*) desc)
exec proc2
drop proc proc2
执行结果：命令已成功完成。
如图：
2．触发器的创建与使用
SQL数据库实验五 存储过程和触发器
实验五存储过程和触发器
一、实验目的：
了解存储过程和触发器的组成结构及作用
熟悉存储过程和触发器的创建及使用
三、实验内容及要求
1. 2.
3.
4.CREATE PROCEDURE Stu_proc1 @SCLASS INT
AS SELECT奖学金=奖学金*1.1 FROM Student where奖学金<@JXJ
7.CREATE PROCEDURE Stu_proc4
@banj INT,@AVG decimal OUTPUT,@RS INT OUTPUT
AS SELECT @AVG=AVG(grade),@RS=COUNT(Student.Sno)
9.CREATE PROCEDURE Stu_proc6 @KCM Co,Student.sname,Elective.Grade from ElectiveSTUDENT INNER JOIN Elective ON Student.Sno=Elective.Sno WHERE o=@KCM
FROM STUDENT INNER JOIN ELECTIVE ON STUDENT.SNO=ELECTIVE.SNO
WHERE class=@banj
8.CREATE PROCEDURE Stu_proc5 @XM CHAR
AS SELECT o,Elective.grade from Elective STUDENT INNER JOIN Elective ON tudent.Sno=Elective.Sno WHERE Student.sname=@XM
AS SELECT * FROM Student WHERE CLASS=@SCLASS
实验五 存储过程和触发器的定义和使用资料
实验五存储过程和触发器的定义和使用一、实验目的1、掌握局部变量、全局变量、流程控制语句的使用方法2、了解存储过程的类型和作用，并掌握使用对象资源管理器和Transact-SQL语句创建存储过程的方法及使用方法。
3、理解触发器的特点和作用，并掌握使用Transact-SQL语言创建触发器的方法二、实验内容1. 在学生成绩库中中有如下各表：学生表（Student）学号姓名性别出生日期专业所在系联系电话020101 杨颖0 1980-7-20 计算机应用计算机88297147 020102 方露露0 1981-1-15 信息管理计算机88297147 020103 俞奇军 1 1980-2-20 信息管理计算机88297151 020104 胡国强 1 1980-11-7 信息管理计算机88297151 020105 薛冰 1 1980-7-29 水利工程水利系88297152 020201 秦盈飞0 1981-3-10 电子商务经济系88297161 020202 董含静0 1980-9-25 电子商务经济系88297062 020203 陈伟 1 1980-8-7 电子商务经济系88297171 020204 陈新江 1 1980-7-20 房建水利系88297171 create database学生成绩数据库create table Student(学号Char(6)not null,姓名Char(8)not null,性别Bit not null,出生日期smalldatetime,专业Char(10),所在系Char(10),联系电话Char(11)null)课程表（Course）课程号课程名教师开课学期学时学分101 计算机原理陈红 2 45 3102 计算方法王颐 3 45 3103 操作系统徐格 2 60 4104 数据库原理及应用应对刚 3 75 5105 网络基础吴江江 4 45 3106 高等数学孙中文 1 90 6107 英语陈刚 1 90 6108 VB程序设计赵红韦 3 70 5create table Course(课程号Char(3)not null,课程名Char(20)not null,教师Char(10),开课学期Tinyint,学时Tinyint,学分Tinyint not null,)学生选课成绩表（SC）学号课程号成绩020101 101 85020101 102 87020101 103 88020102 101 58020102 102 63020104 107 76020202 103 55020202 107 80020203 103 57020204 103 71create table SC(学号Char(6)not null,课程号Char(3)not null,成绩Smallint,)对三个表格分别导入，截图如下：2、T-SQL语句中流程控制语句的使用（1）全局变量的使用。
数据库实验5 存储过程和触发器
实验五存储过程和触发器一、实验目的(1) 通过实践理解存储过程和触发器的概念、作用及优点；(2) 掌握存储过程的定义与调用，实现存储过程中带有不同参数的应用；(3) 掌握创建触发器。
二、实验原理1.存储过程一个被命名的存储在服务器上的T-SQL语句的集合，是封装重复性工作的一种方法。
（1）创建存储过程CREATE PROC[DURE]PROCDURE_NAME [{@PARAMENT DATA_TYPE}[VARYING][=DEFAULT][OUTPUT]] [, (1)AS SQL_STATEMENTPROCEDURE_NAME:新存储过程的名称，必须符合标识符规则且唯一。
@PARAMETER:过程中的参数。
可以声明一个或多个参数。
用户必须在执行过程时提供每个所声明参数的值（除非定义了该参数的默认值）。
使用 @ 符号作为第一个字符来指定参数名称。
参数名称须符合标识符规则。
每个过程的参数仅用于该过程本身；相同的参数名称可用在其它过程中。
默认情况下参数只能代替常量，不能代替表名、列名或其它数据库对象名称。
DATA_TYPE:参数的数据类型。
DEFAULT:参数的默认值。
如果定义了默认值，不必指定该参数的值即可执行过程。
默认值必须是常量或 NULL。
OUTPUT:表明参数是返回参数。
该选项的值可以返回给 EXEC[UTE]。
使用 OUTPUT 参数可将信息返回给调用过程。
（2）执行存储过程SQL SERVER系统中，可以使用EXECUTE语句执行存储过程。
EXECUTE语句也可以简写为EXEC。
如果将要执行的存储过程需要参数，那么应该在存储过程名称后面带上参数值。
[EXEC[UTE]]{[@RETURN_STATUS=]{PROCEDURE_NAME[;NUMBER]|@PROCEDURE_NAME_VAR}[@PARAMETER={VALUE|@VARIABLE[OUTPUT]|[DEFAULT]}[,…N](3) 删除存储过程使用DROP PROCEDURE语句可永久地删除存储过程。
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
数据库基础与实践实验报告实验五存储过程和触发器班级：惠普测试142学号：**********姓名：***日期：2016-11-141 实验目的：1)掌握SQL进行存储过程创建和调用的方法；2)掌握SQL进行触发器定义的方法，理解触发器的工作原理；3)掌握触发器禁用和重新启用的方法。
2 实验平台：操作系统：Windows xp。
实验环境：SQL Server 2000以上版本。
3 实验内容与步骤利用实验一创建的sch_id数据库完成下列实验内容。
1.创建存储过程JSXX_PROC，调用该存储过程时可显示各任课教师姓名及其所教课程名称。
存储过程定义代码：CREATE PROCEDURE JSXX_PROCASSELECT tn 教师姓名,cn 所教课程FROM T,TC,C WHERE T.tno=TC.tno AND o=o存储过程执行语句与执行结果截图：EXECUTE JSXX_PROC2.创建存储过程XM_PROC，该存储过程可根据输入参数（学生姓名）查询并显示该学生的学号、所学课程名称和成绩；如果没有该姓名学生，则提示“无该姓名的同学”。
存储过程定义代码：CREATE PROCEDURE XM_PROC @sname VARCHAR(100)ASBEGINIF EXISTS(SELECT NULL FROM S WHERE sn=@sname)SELECT S.sno 学号,cn 课程,score 成绩FROM S,SC,C WHERE o=o AND SC.sno=S.sno ANDS.sn=@snameELSEPRINT'无该姓名的同学。
'END运行截图：3.创建存储过程XBNL_PROC，该存储过程可根据输入参数（专业名词，默认值为计算机专业），统计并显示该专业各年龄段男、女生人数。
如果没有该专业，则显示“无此专业”。
存储过程定义代码：CREATE PROCEDURE XBNL_PROC@departName VARCHAR(30)='计算机',@begin INT,@end INTASDECLARE @numOfBoys INTDECLARE @numOfGirls INTDECLARE @d# VARCHAR(3)DECLARE @result VARCHAR(50)BEGINSELECT @d# = dno FROM D WHERE dn=@departNameIF @d# IS NOT NULLBEGINSELECT @numOfBoys =COUNT(sno)FROM S WHERE age BETWEEN @begin AND @end AND dno=@d# AND sex='男'SELECT@numOfGirls =COUNT(sno)FROM S WHERE age BETWEEN@begin AND@end AND dno=@d# AND sex='女'SET @result = @departName+'专业年龄在'+CAST(@begin AS VARCHAR(3))+'-'+CAST(@end AS VARCHAR(3))+'之间的男生有'+CAST(@numOfBoys AS VARCHAR(3))+'人，'+'女生有'+CAST(@numOfGirls AS VARCHAR(3))+'人'ENDELSESET @result='无此专业。
'PRINT @resultEND执行结果：4.执行XM_PROC存储过程，查询“贾慧”同学的学号、所学课程名称和成绩。
存储过程调用语句及其执行结果截图：EXEC XM_PROC'贾慧'5.如果学生表中无“贾慧”同学，则另查询一位学生表中出现过的学生姓名；如果学生表中有“贾慧”同学，则另查询一位学生表中没有的学生姓名。
存储过程调用语句及其执行结果截图：EXEC XM_PROC'张明'6.执行XBNL_PROC存储过程，查询计算机专业各年龄的男、女生人数。
（要求计算机专业必须有至少3个年龄段的男女学生）存储过程调用语句及其执行结果截图：EXEC XBNL_PROC@begin=21,@end=25 --默认：计算机EXEC XBNL_PROC'计算机',18,25EXEC XBNL_PROC'计算机',23,25EXEC XBNL_PROC'计算机',20,217.执行XBNL_PROC存储过程，输入一个专业表中没有的专业。
存储过程调用语句及其执行结果截图：EXEC XBNL_PROC'音乐',18,258.分别查看XBNL_PROC存储过程的一般信息。
执行语句及结果截图：EXEC sp_help XM_PROC9.删除XM_PROC存储过程。
执行语句及结果截图：DROP PROC XM_PROC10.创建班级表B(bno,bn,num)，三个属性分别表示班号，班名，人数；在学生S表中增加一个属性班号。
表创建语句：--创建班级表：CREATE TABLE B(bno VARCHAR(3)PRIMARY KEY,bn VARCHAR(20)UNIQUE NOT NULL,num INT)--S表添加班级属性：ALTER TABLE S ADD bno VARCHAR(3)执行结果截图：11.创建触发器T_insertS，当向S表添加记录时，验证学生的班级号是否出现在B表中，如果不是则撤销S表添加记录的操作；，如果学生的班号在班级表中则自动修改相应班级的人数字段值。
触发器创建语句：CREATE TRIGGER T_insertSON SFOR INSERTASBEGINDECLARE @bno VARCHAR(3)DECLARE @sno VARCHAR(10)SELECT @bno=bno FROM insertedSELECT @sno=sno FROM insertedIF EXISTS(SELECT NULL FROM B WHERE bno=@bno)BEGINUPDATE B SET num=num+1 WHERE bno=@bnoPRINT'添加成功！'ENDELSEBEGINDELETE FROM S WHERE sno=@snoPRINT'添加失败！'ENDEND向S表中插入一位学生，其班号不在B表中。
测试触发器的功能截图：插入记录前查询：SELECT*FROM S WHERE sn='翠翠'插入记录：INSERT INTO S VALUES('S19','翠翠','女',18,'D2',3)插入后查询：SELECT*FROM S WHERE sn='翠翠'向S表中插入一位学生，其班号在B表出现过。
测试触发器的功能截图：插入前查询：SELECT*FROM S WHERE sn='翠翠'插入记录：INSERT INTO S VALUES('S19','翠翠','女',18,'D2',1)插入后查询：SELECT*FROM S WHERE sn='翠翠'禁用T_insertS触发器语句；ALTER TABLE S DISABLE TRIGGER T_insertS向S表中插入一位学生，其班号不在B表中。
测试触发器的功能截图：INSERT INTO S VALUES('S20','翠花','女',18,'D2',3)SELECT*FROM S WHERE sn='翠花'启用T_insertS触发器语句；ALTER TABLE S ENABLE TRIGGER T_insertS向S表中插入一位学生，其班号不在B表中。
测试触发器的功能截图：INSERT INTO S VALUES('S21','翠花儿','女',19,'D2',3)SELECT*FROM S WHERE sn='翠花儿'12.创建触发器T_updateS，当修改S表一位同学的班级字段值时，验证学生的班级号是否出现在B表中，如果不是则撤销对S表中该条记录的修改操作，如果修改后的班号在班级表中则自动修改相应班级的人数字段值。
触发器创建语句：CREATE TRIGGER T_updateSON SFOR UPDATEASBEGINDECLARE @b# VARCHAR(3)DECLARE @b#old VARCHAR(3)DECLARE @numOld INTSELECT @b#=bno FROM insertedSELECT @b#old=bno FROM deletedSELECT @numOld=num FROM B WHERE bno=@b#oldIF EXISTS(SELECT NULL FROM B WHERE bno=@b#)BEGINIF (@numOld IS NOT NULL)BEGINUPDATE B SET num=num+1 WHERE bno=@b#UPDATE B SET num=num-1 WHERE bno=@b#oldENDELSEUPDATE B SET num=num+1 WHERE bno=@b#PRINT'更新成功!'ENDELSEBEGINUPDATE S SET bno=@b#old WHERE bno=@b#PRINT'更新失败!'ENDEND修改S表中一位学生的班级号，修改后的班号不在B表中。
测试触发器的功能截图：UPDATE S SET bno='88'WHERE sno='S1'修改S表中一位学生的班级号，修改后的班号在B表中。
测试触发器的功能截图：SELECT sno AS'学号',bno AS'班级编号'FROM S WHERE sno='S18'SELECT bno '班级编号',num AS'班级人数'FROM BUPDATE S SET bno='8'WHERE sno='S18'SELECT sno AS'学号',bno AS'班级编号'FROM S WHERE sno='S18'SELECT bno '班级编号',num AS'班级人数'FROM B4 深入思考与讨论1）请按自己的理解，说明一下触发器的工作原理。