实验7-存储过程应用初步

《数据库原理及应用》课程实验报告实验名称存储过程应用初步实验序号实验日期班级学号姓名院系计算机科学与信息工程学院专业计算机科学与技术指导教师成绩一、实验目的和要求1. 理解存储过程的作用；2．初步掌握存储过程的创建方法；3．初步掌握存储过程的执行方法。

4. 这个实验所涉及的容不属于《数据库原理及应用》课知识，是扩充知识，主要是为即将到来的课程设计做部分准备工作。

二、实验预习容1．上网查找资料---存储过程是数据库中的一个怎样的对象；主要应用在哪些方面；存储过程的类型等基本知识。

2．创建存储过程的方法，包括有参数和无参数的存储过程。

其中可能涉及到变量、系统函数SQL控制流语句等容。

三、实验项目摘要1．针对“图书-借阅”涉及的5表，自行设计3个存储过程（要有实际意义），执行它们，看结果是否是你所需要的；2．针对“供应”涉及的4表，设计3个存储过程（要有实际意义），执行它们，看结果是否是你所需要的。

说明：设计的存储过程不可以太过简单（如只有一个简单查询），要会设计带参数的存储过程。

同学们可以根据自己对知识的掌握情况，自行决定如何设计。

四、实验结果与分析1．（1）创建(无参数)查询‘文学’类图书中出版时间最早的最早的图书信息的存储过程并执行：1、书写T-SQL语句，创建过程一：CREATE PROCEDURE GUOCHENG_ONE AS/*新建过程一*/SELECT图书名,作者,FROM BOOKS,BOOKCATEGORYWHERE出版日期IN(SELECT MIN(出版日期)FROM BOOKSWHERE BOOKS.种类编号=BOOKCATEGORY.种类编号AND种类名称='文学')/*T-SQL语句*/GOII、执行过程一：EXECUTE GUOCHENG_ONE /*执行该存储过程功能*/(或者：EXEC GUOCHENG_ONE )（2）（单个参数）查询：设置一个存储过程GUOCHENG_TWO，通过指定可变的读者，显示其所借阅的图书的图书名：I、先以一个“具体的”书写T-SQL语句，检验语法错误；无误后以变量代替“具体的”，进而创建过程二：SELECT图书名FROM BOOKSWHERE图书编号IN(SELECT图书编号FROM BORROWWHERE读者编号IN(SELECT读者编号FROM READERSWHERE='于志强'))CREATE PROCEDURE GUOCHENG_TWO NAME varchar(10)AS/*新建过程二*/SELECT图书名FROM BOOKSWHERE图书编号IN (SELECT图书编号FROM BORROWWHERE读者编号IN (SELECT读者编号FROM READERSWHERE=NAME ))GO/*过程功能*/II、执行上述过程，即随意指定读者，查询其借阅图书的图书名：EXECUTE GUOCHENG_TWO NAME='明'/*执行该存储过程功能*/EXECUTE GUOCHENG_TWO NAME='明'/*执行该存储过程功能*/（3）设置（多个参数）插入一条记录的过程；完成后并执行该过程将：“读者编号：1243；：翟萌；种类编号：2；工作单位：交通大学；住址：桃苑小区3-415；联系：83839228；登记日期：2014-04-15；性别：男”这些容添加到到表READERS中：I、创建向表READER插入记录的过程如下：CREATE PROCEDURE GUOCHENG_THREEREADERS_NUMBER nchar(10),NAME varchar(20),RANGE_NUMBER int,COMPANY varchar(50), ADD varchar(100),TEL varchar(20),DATE datetime,SEX char(2)AS/*新建过程三*/INSERT INTO READERS VALUES(READERS_NUMBER,NAME,RANGE_NUMBER ,COMPANY,ADD,TEL,DATE,SEX)/*过程功能*/GOII、执行该过程，将记录插入到表中：EXECUTE GUOCHENG_THREEREADERS_NUMBER='1243',NAME='翟萌',RANGE_NUMBER='2',COMPANY='交通大学', ADD='桃苑小区-415',TEL='83839228',DATE='2014-04-15',SEX='男'/*执行该存储过程功能*/2．（1）创建带输出参数的存储过程，要求求出零件表P中所有蓝色零件重量的平均值,并将结果通过输出变量返回给用户：I、创建具有上述功能的带输出参数的存储过程：CREATE PROCEDURE GUOCHENG_FOUR PJZ INT OUTPUTAS/*创建过程四*/SELECT PJZ=AVG(WEIGHT)FROM零件表PWHERE COLOR ='蓝'/*过程功能*/GOII、执行该过程：DECLARE PJZ INTEXECUTE GUOCHENG_FOUR PJZ OUTPUTPRINT'零件表中所有蓝色零件的平均重量为'+STR(PJZ)GO（2）创建带输入输出参数的存储过程，要求求出“用户指定的供应商”提供给“用户指定的工程”的”所有零件重量”的平均值,并将结果通过输出变量返回给用户：I、创建具有上述功能的带输入输出参数的存储过程：CREATE PROCEDURE GUOCHENG_FIVE GYSN NCHAR(10),GCN NCHAR(10),PJZ INT OUTPUTAS/*创建过程四*/SELECT PJZ=AVG(WEIGHT)FROM零件表PWHERE PN IN(SELECT PNFROM供应关系表SPJWHERE SN IN(SELECT SNFROM供应关系表SPJWHERE SN=GYSN)AND JN IN(SELECT JNFROM供应关系表SPJWHERE JN=GCN))/*过程功能*/GOII、假设用户指定供应商为S4，工程为J4，执行上述过程：DECLARE PJZ INTEXECUTE GUOCHENG_FIVE 'S5','J4',PJZ OUTPUTPRINT'零件的平均值为'+STR(PJZ)GO（3）（无参数）更新“供应商表”用户指定值的供应表信息：I、创建具有上述功能的存储过程：CREATE PROCEDURE GUOCHENG_SIX GYSN NCHAR(10),GYSNAME NCHAR(10),CSM VARCHAR(50)AS/*创建过程六*/UPDA TE供应商表SSET SNAME=GYSNAME,CITY=CSMWHERE SN=GYSN /*过程功能*/GOII、指定S4，修改名称为“SN8”，城市为“土耳其”：附：之前实验报告修改实验五1.1）查询“计算机科学”这类图书中的定价最高的图书书名和定价；SELECT图书名,价格AS定价FROM Books,BookCategoryWHERE价格=(SELECT MAX(价格)FROM BooksWHERE Books.种类编号=BookCategory.种类编号AND种类名称='计算机科学')实验六3．导入“图书借阅”5表的关系图；导入供应商等四表的关系图。

实验七存储过程及应用1．实验目的1、理解存储过程的概念。

2、掌握存储过程的使用方法。

2．实验要求1．建立如下的存储过程（基于前面实验建立的表和插入的数据，并为每个存储过程设计返回的状态值）：（1）按要求设计完成如下功能的存储过程。

①查询平均分数在x到y范围内的学生信息。

说明：该存储过程有两个参数；要求查询的学生信息包括学号、姓名、院系名称和平均分数。

②更新操作，以学号、课程编号和考试成绩作参数更新指定学生和课程的考试成绩，并返回该学生的平均成绩。

(2)自行再分别设计一个完成查询和完成操作功能的存储过程（在实验报告中要准确描述功能需求）。

（3）在客户端以存储过程和输入SQL语句的方式分别执行相同的查询或操作，比较使用和不使用存储过程的区别。

3、实验过程（1）①查询平均分数在x到y范围内的学生信息create procedure cc711@x smallint,@y smallintasselect学生.学号,学生.姓名,院系.名称,学生.平均成绩from学生join院系on学生.院系=院系.编号where学生.平均成绩between@x and@yEXECUTE cc71160,90②更新操作，以学号、课程编号和考试成绩作参数更新指定学生和课程的考试成绩，并返回该学生的平均成绩create procedure mcc712@xh nchar(8),@kcbh nchar(8),@cj intasupdate选课set成绩=@cj where学号=@xh and课程编号=@kcbhdeclare@pjcj intselect@pjcj=AVG(成绩)from选课where学号=@xhreturn@pjcjdeclare@avg intexecute@avg=mcc7122,5,98print'更新后平均成绩：'+str(@avg,6)(2)自行再分别设计一个完成查询和完成操作功能的存储过程（在实验报告中要准确描述功能需求）。

一、实验背景随着数据库技术的不断发展，数据库管理系统（DBMS）的功能日益强大，存储过程作为一种重要的数据库对象，在数据库应用中扮演着越来越重要的角色。

存储过程能够封装复杂的业务逻辑，提高数据库性能，增强数据安全性。

本实验旨在通过实际操作，掌握存储过程的创建、执行和管理方法，提高数据库应用开发能力。

二、实验目的1. 理解存储过程的概念和作用。

2. 掌握存储过程的创建、执行和管理方法。

3. 学会使用存储过程优化数据库性能。

4. 提高数据安全性。

三、实验环境1. 操作系统：Windows 102. 数据库：MySQL 5.73. 编程语言：MySQL四、实验内容1. 创建存储过程2. 调用存储过程3. 管理存储过程4. 优化存储过程性能五、实验步骤1. 创建存储过程（1）创建一个名为“get_user_info”的存储过程，用于查询用户信息。

```sqlDELIMITER //CREATE PROCEDURE get_user_info(IN user_id INT)BEGINSELECT FROM users WHERE id = user_id;END //DELIMITER ;```（2）创建一个名为“update_user_info”的存储过程，用于更新用户信息。

```sqlDELIMITER //CREATE PROCEDURE update_user_info(IN user_id INT, IN username VARCHAR(50), IN email VARCHAR(100))BEGINUPDATE users SET username = username, email = email WHERE id = user_id;END //DELIMITER ;```2. 调用存储过程（1）调用“get_user_info”存储过程查询用户信息。

```sqlCALL get_user_info(1);```（2）调用“update_user_info”存储过程更新用户信息。

《数据库原理与应用》课程实验报告
学院：物光学院班级：07网工姓名：学号：
实验设备：计算机1台实验日期：年月日
实验项目名称存储过程
实验目的 1.掌握创建和使用存储过程。

2.掌握显示存储过程。

3.掌握删除存储过程。

实验要求：
1.熟悉查询分析器的操作，以及执行T-SQL语句的方法。

2.了解使用存储过程的优点。

实验内容(包括步骤)：
1.创建一个为worker表添加职工记录的存储过程Addworker。

2.创建一个存储过程Delworker删除worker表中指定职工号的记录。

3.显示存储过程delworker。

4.删除存储过程addworker和delworker。

调试与结果测试：
代码注释:
无
――――――――――――以下内容为教师填写―――――――――――――――
教师评阅：
成绩：
年月日。

GO
USEypp7
--声明四个变量，用于保存输入和输出参数
DECLARE@KECHENGMINGvarchar(20)
DECLARE@AVGCHENGJI1tinyint
DECLARE@MAXCHENGJI1tinyint
DECLARE@MINCHENGJI1tinyint
--为输入参数赋值
SELECT@KECHENGMING='高等数学'
--声明四个变量，用于保存输入和输出参数
DECLARE@KECHENGMINGvarchar(20)
DECLARE@AVGCHENGJI1tinyint
DECLARE@MAXCHENGJI1tinyint
DECLARE@MINCHENGJI1tinyint
--为输入参数赋值
SELECT@KECHENGMING='计算机基础'
1.使用if exists语句，如果存储过程“单科成绩分析”存在，就将其删除；
2.使用create proc语句创建存储过程；
3.定义所需要的输入参数和输出参数；
4.声明4个变量来保存输入和输出参数；
5.执行存储过程并显示结果。
同时，值得注意的是，在创建存储过程时，应该注意一些细节，如单词的拼写要准确无误，程序中用的是单引号而不是双引号等等。
--执行存储过程
EXEC单科成绩分析@KECHENGMING,
@AVGCHENGJI1OUTPUT,
@MAXCHENGJI1OUTPUT,
@MINCHENGJI1OUTPUT
--显示结果
SELECT@KECHENGMINGAS课程名,@AVGCHENGJI1AS平均成绩,@MAXCHENGJI1AS最高成绩,

实验七使用PL/SQL编写存储过程访问数据库一、实验目的1、熟悉存储过程编译过程、工作原理及其优点；2、熟悉使用存储过程来进行数据库应用程序的设计。

二、实验条件1、具有局域网的计算机机房一个；2、SQL SERVER2000或SQL SERVER2005软件。

三、实验内容对教材“学生-课程”数据库（student表，course表，sc表），编写存储过程，分别完成下面功能：create table Student(Sname char(12)primary key,Ssex char(2),Sage int,Sdept char(12));create table Course(Cname char(12)primary key,Cpno int,Ccredit int);create table SC(Cno char(12)primary key,Grade int);①建立存储过程完成统计“离散数学”的成绩分布情况，即按照各分数段统计人数；create procedure Proc_1asselect count(*),Cname,Grade from sc wherecname in(select Cname from Course where cname='db');exec Proc_1;②建立存储过程完成统计任意一门课的平均成绩；create procedure Proc_2asselect avg(grade),cname from sc where camme='db'exec Proc_2;③建立存储过程完成将学生选课成绩从百分制改为等级制（即A、B、C、D、E）；create or replace procedure change_critical()as declarechgrade char(1);currecord record;beginalter table SC add column(newgrade char(1));for currecord in select*from SC loopif currecord.grade<60then chgrade='E';else if currecord.grade<70then chgrade='D';else if currecord.grade<80then chgrade='C';else if currecord.grade<90then chgrade='B';else chgrade='A';end if;update SC set newgrade=chgradewhere sno=currecord.sno and cno=o;end loop;alter table SC drop column grade;alter table SC rename newgrade to grade;end;④建立存储过程完成对学生表的批量插入数据，插入数据的条数作为其中一个参数，另一个参数为学号起始学号，其它数据格式自行定义（如每次插入100条数据）；create procedure proc_4@count int,@startsno char(9)asdeclare@loop intset@loop=0while@loop<@countbegininsert into student(sno,sname,ssex,sage,sdept)values(@startsno,'zj','M',20,'IS')set@loop=@loop+1set@startsno=@startsno+1endexec proc_4100,'20130901';要求：提交源程序并标识必要的注释。

一、实验模块数据库原理与应用二、实验标题存储过程操作实验三、实验内容1. 实验目的（1）掌握存储过程的概念和作用。

（2）学会创建和使用存储过程。

（3）了解存储过程与触发器的区别。

2. 实验原理存储过程是一组为了完成特定功能的SQL语句集，存储在数据库中供应用程序调用。

它可以提高数据库性能，简化代码编写，提高安全性。

3. 实验步骤（1）创建数据库```sqlCREATE DATABASE IF NOT EXISTS experiment;USE experiment;```（2）创建表```sqlCREATE TABLE IF NOT EXISTS employee (id INT PRIMARY KEY AUTO_INCREMENT,name VARCHAR(50),age INT,department_id INT);CREATE TABLE IF NOT EXISTS department (id INT PRIMARY KEY AUTO_INCREMENT,name VARCHAR(50));```（3）插入数据```sqlINSERT INTO employee (name, age, department_id) VALUES ('张三', 25, 1),('李四', 30, 2),('王五', 28, 3);INSERT INTO department (name) VALUES ('技术部'),('业务部'),('售后部');```（4）创建存储过程```sqlDELIMITER //CREATE PROCEDURE get_department_name(IN emp_id INT, OUT dept_name VARCHAR(50))BEGINSELECT INTO dept_name FROM employee e INNER JOIN department d ON e.department_id = d.id WHERE e.id = emp_id;END //DELIMITER ;```（5）调用存储过程```sqlCALL get_department_name(1, @dept_name);SELECT @dept_name AS department_name;```（6）创建触发器```sqlDELIMITER //CREATE TRIGGER before_employee_insertBEFORE INSERT ON employeeFOR EACH ROWBEGINIF NEW.age < 20 THENSIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '年龄不能小于20岁'; END IF;END //DELIMITER ;```（7）尝试插入年龄小于20岁的数据```sqlINSERT INTO employee (name, age, department_id) VALUES ('赵六', 18, 1);```4. 实验结果与分析（1）成功创建存储过程和触发器。

一、实验目的1. 了解存储过程的基本概念和作用。

2. 掌握存储过程的创建、执行和修改方法。

3. 学会使用存储过程进行数据库操作，提高数据库操作效率。

二、实验环境1. 操作系统：Windows 102. 数据库管理系统：MySQL 5.73. 开发工具：MySQL Workbench三、实验内容1. 创建存储过程2. 执行存储过程3. 修改存储过程4. 删除存储过程四、实验步骤1. 创建存储过程（1）打开MySQL Workbench，连接到本地数据库。

（2）在查询窗口中输入以下SQL语句创建一个存储过程：DELIMITER //CREATE PROCEDURE SelectAllStudents()BEGINSELECT FROM students;END //DELIMITER ;（3）执行以上SQL语句，成功创建存储过程。

2. 执行存储过程（1）在查询窗口中输入以下SQL语句执行存储过程：CALL SelectAllStudents();（2）观察查询结果，确认存储过程执行成功。

3. 修改存储过程（1）打开查询窗口，输入以下SQL语句修改存储过程：DELIMITER //CREATE PROCEDURE SelectAllStudents()BEGINSELECT id, name, age FROM students;END //DELIMITER ;（2）执行以上SQL语句，成功修改存储过程。

4. 删除存储过程（1）在查询窗口中输入以下SQL语句删除存储过程：DROP PROCEDURE IF EXISTS SelectAllStudents;（2）执行以上SQL语句，成功删除存储过程。

五、实验总结1. 通过本次实验，我们了解了存储过程的基本概念和作用，学会了创建、执行、修改和删除存储过程的方法。

2. 存储过程可以提高数据库操作效率，降低代码重复性，提高代码可维护性。

3. 在实际开发过程中，合理使用存储过程可以简化数据库操作，提高应用程序的性能。

第1篇一、实验背景随着数据库技术的不断发展，存储过程在数据库管理中的应用越来越广泛。

存储过程是一组为了完成特定功能的SQL语句集合，它具有提高数据库性能、增强安全性、简化应用开发等优点。

为了更好地掌握存储过程的应用，我们进行了本次实验。

二、实验目的1. 理解存储过程的概念、特点和应用场景。

2. 掌握存储过程的创建、执行、修改和删除方法。

3. 学习使用存储过程实现常见的数据库操作，如数据插入、查询、更新和删除。

4. 熟悉存储过程中的流程控制语句、循环语句和游标操作。

三、实验环境1. 操作系统：Windows 102. 数据库：MySQL 5.73. 开发工具：MySQL Workbench四、实验内容1. 创建存储过程2. 执行存储过程3. 修改存储过程4. 删除存储过程5. 存储过程中的流程控制语句6. 存储过程中的循环语句7. 存储过程中的游标操作五、实验步骤1. 创建存储过程首先，我们创建一个简单的存储过程，用于查询特定部门的所有员工信息。

```sqlCREATE PROCEDURE GetEmployeeInfo(IN dept_id INT)BEGINSELECT FROM employees WHERE department_id = dept_id;END;```在此过程中，我们使用了`IN`参数，表示该参数在调用存储过程时传入。

2. 执行存储过程创建存储过程后，我们可以通过以下命令执行它：```sqlCALL GetEmployeeInfo(10);```这将查询部门ID为10的所有员工信息。

3. 修改存储过程如果需要修改存储过程，可以使用`ALTER PROCEDURE`语句。

例如，将查询条件修改为按姓名查询：```sqlALTER PROCEDURE GetEmployeeInfo(IN emp_name VARCHAR(50))BEGINSELECT FROM employees WHERE name = emp_name;END;```4. 删除存储过程删除存储过程可以使用`DROP PROCEDURE`语句。

The Description of toy: A giant Blue Whale w
The Price of toy : 8.99
cOrderNo cToyId siQty cGiftWrap cWrapperId vMessage mToyCost
-----------------------------------------------------------------------------
createPROCproc8(@echar(6))
AS
BEGIN
IF(SELECTmTotalCostFROMOrdersWHEREcOrderNo=@e)>60BEGIN
SELECT*
FROMOrdersWHEREcOrderNo=@e
RETURN0
END
ELSE
BEGIN
PRINT'No more than 60'
ifexists(select*fromToyswherecToyId=@toyid)
begin
select@toyName=vToyName,@TOyDescription=vToyDescription,
@toyrate=mToyRate
fromToyswherecToyId=@toyid
return0
wherecToyId=@toyid
end
else
print'No record for the given Toyid'
end
指导教师
日期
注：请用A4纸书写，不够另附纸。第页，共页
end
else
return1
end
createprocproc11@toyidchar(6)='000001',

实验7 存储过程一、实验目的与要求1.掌握交互式创建存储过程的方法。

2.掌握用T-SQL创建存储过程的方法。

3.掌握执行存储过程的方法。

4.掌握修改存储过程的方法,而且不改变权限。

5.掌握交互式删除存储过程的操作。

6.掌握用SQL删除存储过程的方法。

二、实验内容1.交互式创建并执行存储过程Pro_Qsinf：通过学生的学号来查询该学生的姓名、年龄和所在系。

默认学号是200215121。

2.用T-SQL创建并执行存储过程Pro_Qgrade：通过学生的姓名和课程名来查询该学生该课程的成绩。

3.交互式修改存储过程Pro_Qsinf。

4.用SQL修改存储过程Pro_Qsinf。

5.交互式删除存储过程Pro_Qsinf。

6.用SQL删除存储过程Pro_Qgrade。

三、实验步骤1. 交互式创建并执行一存储过程（1）创建存储过程Pro_Qsinf：通过学生的学号来查询该学生的姓名、年龄和所在系。

①打开创建存储过程窗口。

在“对象资源管理器”中，依次展开“数据库”->“学生-课程”->“可编程性”->“存储过程”节点，右击“存储过程”，选择“新建存储过程”命令，如图1所示。

打开存储过程编辑窗口，并包含模板语句，如图2所示。

②修改模板语句。

将模板语句修改为图3所示的SQL语句。

③语法检查。

单击工具栏中的分析按钮，在“结果”窗口中显示“命令已成功完成”。

④保存存储过程。

单击工具栏中的执行按钮，保存创建的存储过程。

⑤查看数据库学生-课程中的存储过程对象。

在对象资源管理器中，右击数据库学生-课程中的“存储过程”节点，选择“刷新”命令，展开“存储过程”节点，查看新建的存储过程Pro_Qsinf，如图4所示。

图1 选择“新建存储过程”命令图2 存储过程编辑窗口图3 创建存储过程Pro_Qsinf图4 数据库学生-课程中的存储过程（2）执行存储过程Pro_Qsinf。

查询并显示默认学号200215121和学号为200215122的学生姓名、年龄和所在系。

实验7 存储过程和触发器的使用1.存储过程①创建存储过程，使用Employees表中的员工人数来初始化一个局部变量，并调用这个存储过程USE YGGLGOCREATE PROCEDURE TEST@NU MBER int OUTPUTASBEGINDECLARE@NU MBER2inTSET@NU MBER2=(SELECT COUNT(*)FROM Employees)SET@NU MBER1=@NU MBER2END②创建存储过程，比较两个员工的实际收入，若前者比后者高就输出0，否则输出1 BEGINDECLARE@SR1float,@SR2FLOATSELECT@SR1=InCome-OutCome FROM Salary WHERE EmployeeID=@ID1SELECT@SR2=InCome-OutCome FROM Salary WHERE EmployeeID=@ID2IF@ID1>@ID2SET@BJ=0ELSESET@BJ=1END③创建添加职员记录的存储过程EmployeeAddUSE YGGLGOCREATE PROCEDURE EmployeeAdd(@employeeid char(6),@name char(10),@education char(4),@birthday datetime, @woekyear tinyint,@sex bit,@address char(40),@phonenumber char(12),@departmentID char(3))ASBEGININSERT INTO EmployeesVALUES(@employeeid,@name,@education,@birthday,@woekyear,@sex,@address,@phonenumber,@departmentID)ENDRETURNGO④创建带有OUTPUT游标参数的存储过程，在Employees表中声明并打开游标USE YGGLGOCREATE PROCEDURE em_cursor@em_cursor cursor VARYING OUTPUTASBEGINSET@em_cursor=CURSOR FORWARD_ONLY STATICFORSELECT*FROM EmployeesOPEN@em_cursorENDGO⑤创建存储过程，使用游标确定一个员工的实际收入是否排在前三名，结果为1表示是，结果为0表示否REATE PROCEDURE TOP_THREE@EM_ID char(6),@OK bit OUTPUTASBEGINDECLARE@X_EM_ID char(6)DECLARE@ACT_IN int,@SEQ intDECLARE SALARY_DIS cursor FORSELECT EmployeeID,InCome_OutComeFROM SalaryORDER BY InCome_OutCome DESCSET@SEQ=0SET@OK=0OPEN SALARY_DISFETCH SALARY_DIS INTO@X_EM_ID,@ACT_INWHILE@SEQ<3 AND@OK=0BEGINSET@SEQ=@SEQ+1IF@X_EM_ID=@EM_IDSET@OK=1FETCH SALARY_DIS INTO@X_EM_ID,@ACT_INENDCLOSE SALARY_DISDEALLOCATE SALARY_DISEND2.触发器①向Employees表插入或修改一个记录时，通过触发器检查记录的DpartmentID值在Dpartments表中是否存在，若不存在，则取消插入或修改操作USE YGGLGOCREATE TRIGGER EmployeesIns ONFOR INSERT,UPDATEASBEGINIF((SELECT DepartmentID from inserted)NOT IN(SELECT DepartmentID FROM DepartmentS))ROLLBACKEND②修改Dpartments表“DpartmentID”字段值时，该字段在Employees表中对应的值也做相应修改USE YGGLUSE YGGLGOCREATE TRIGGER DepartmentUpdate ONFOR UPDATEASBEGINUPDATE EmployeesSET DepartmentID=(SELECT DepartmentID FROM insered)WHERE DepartmentID=(SELECT DepartmentID FROM deleted)ENDGO③删除Dpartment表中记录的同时删除该记录“DpartmentID”字段值在Employees表中对应的记录CREATE TRIGGER DepartmentsDelate ONFOR DELETEASBEGINDELETE FROM EmployeesWHERE DepartmentID=(SELECT DepartmentID FROM deleted)ENDGO④创建INSTEAD OF 触发器，当向Salary表中插入记录时，先检查EmployeeID列上的值在Employees中是否存在，如果在则执行插入操作，如果不存在，则提示“员工号不存在”CREATE TRIGGER EM_EXISTS ON SalaryINSTEAD OF INSERTASBEGINDECLARE@EmployeeID char(6)SELECT@EmployeeID=EmployeeIDFROM insertedIF(@EmployeeID IN(SELECT EmployeeID FROM Employee))INSERT INTO Salary SELECT*FROM insertedELSEPRINT'员工号不存在'END⑤创建DDL触发器，当删除YGGLXJ数据库的一个表时，提示“不能删除表”，并回滚删除表的操作USE YGGLGOCREATE TRIGGER table_deleteON DATABASEAFTER DROP_TABLEASPRINT'不能删除该表'ROLLBACK TRANSACTION。

实验七存储过程
实验7存储过程
1，实验要求和目的
1，掌握创建存储过程的基本方法
2。

实验完成后写一份实验报告。

要求:在实验报告的“实验源代码和结果”一节中填写T-SQL语句代码和执行结果
2，实验内容
1，根据要求创建存储过程:
(1)创建存储过程，用于执行后检索学生的基本信息(检索字段有:学号、姓名、系)
(2)创建一个存储过程，该过程可以在执行后检索数据库分数大于60分的所有学生的学号和姓名。

(3)创建一个存储过程，可以执行该过程来查询任何学生编号的学生姓名、性别、年龄和系。

(4)创建存储过程时，任何记录都可以在执行过程中插入到“学生基本信息表”(表名定制)中。

(5)创建一个可执行的存储过程以输出:参加任何课程的学生人数2。

存储过程修改(使用ALTER关键字):
(1)修改问题(2)中的存储过程，修改后的存储过程可以检索数据库分数大于80分的所有男生的学生编号和姓名
(2)修改问题(3)中存储过程，修改后的存储过程可以更新学习
的任何学生的姓名(注意:update关键字用于更新表中的数据。

具体格
式见教科书第25页的“3.3.2数据修改”。

)
(3)本节中问题(2)的存储过程。

修改后的存储过程可以更新任何课程中任何学生的分数。

• 执行此存储过程： EXEC p_StudentGrade1
Student ( Sno, Sname, Ssex, Sage, Sdept )
Course ( Cno, Cname, Credit, Semster )
示例 Sc ( Sno, Cno, Grade )
• 例2．带输入参数的存储过程。查询某个指定系学生的 考试情况，列出学生的姓名、所在系、课程名和考试成
Student ( Sno, Sname, Ssex, Sage, Sdept ) Course ( Cno, Cname, Credit, Semster )
参数传递方式 Sc ( Sno, Cno, Grade )
• 按参数位置传值
– 实参的排列顺序与创建存储过程时参数定义 的顺序一致。
EXEC p_StudentGrade3 '吴宾', '高等数学'
存储过程功能
• 接受输入参数并以输出参数的形式将多个值 返回给调用者。
• 包含执行数据库操作的语句。 • 将查询语句执行结果返回到客户端内存中。
存储过程优点
• 允许模块化程序设计
– 只需创建一次并存储在数据库中，就可以在应 用程序中反复调用该存储过程
• 改善性能
– 在创建存储过程时对代码进行分析和优化，并 在第一次执行时进行语法检查和编译，将编译 好的可执行代码存储在内存的一个专门缓冲区 中，以后再执行此存储过程时，只需直接执行 内存中的可执行代码即可。
2.修改存储过程
ALTER PROC [EDURE] 存储过程名
[ { @参数名 数据类型 } [ = default ]
[OUTPUT]
] [ , ... n ]

实验七存储过程与触发器（实验报告）一、目的1．掌握存储过程和触发器的基本概念和功能。

2．掌握创建、管理存储过程的方法。

3．掌握创建、管理触发器的方法。

二、实验内容（1）完成下列操作1．利用SQL Server Management Studio创建一个查询过程ProNum,查询每个班级中学生的人数，按班级号升序排列。

2．利用Transact-SQL语句创建一个带有参数的存储过程ProInsert,向score表插入一条选课记录，并查询该学生的姓名、选修的所有课程名称、平时成绩和期末成绩。

3．利用Transact-SQL语句创建一个存储过程ProAvg，查询指定班级指定课程的平均分。

班级号和课程名称由输入参数给定，计算出的平均分通过输出参数返回。

若该存储过程存在，则删除后重建。

4．利用SQL Server Management Studio创建一个AFTER触发器trigsex,当插入或修改student表中性别字段sex时，检查数据是否只为‘男’或‘女’。

5．利用Transact-SQL语句创建一个AFTER数据库trigforeign,当向score表中插入或修改记录时，如果插入或修改的数据与student表中数据部匹配，即没有对应的学号存在，则将此记录删除。

6．利用Transact-SQL语句创建一个AFTER触发器trigclassname，当向class表中插入或修改数据时，如果出现班级名称重复则回滚事务。

若该触发器存在，则删除后重建。

（2）完成实验报告三、实验环境SQL2005。

四、实验原理1．理解存储过程。

2．掌握触发器的使用。

五、实验报告将实验内容作为实验报告完成。

六、实验小结。

实验七存储过程与触发器实验七存储过程与触发器实验七存储过程与触发器一、建立一个名叫“proc_1”的存储过程，用作查阅学生表的所有信息。

然后调用该存储过程。

二、创建一个名为“proc_2”的存储过程，用于向学生表的所有字段添加一条记录，记录内容由调用时同意。

然后调用该存储过程。

三、创建一个名为“proc_3”的存储过程，用于删除学生表中指定学号的记录，具体学号由调用时同意。

然后调用该存储过程。

四、修改存储过程“proc_3”，用于查询不小于指定成绩的学生的基本信息，具体成绩由调用时同意。

五、选做题：建立一个名叫“proc_4”的存储过程，用作谋一个3十一位整数的反序数。

比如123的反序数为321。

createprocproc_4@aintasdeclare@bint,@cint,@tint,@sintbeginset@t=@a%10select@b=@a/10,@b=@b%10set@c=@a/100set@s=@t*100+@b*10+@cselect@sendgoexecproc_4123六、建立一个名叫“trig_1”的触发器，当向学生表中嵌入记录时，该触发器自动表明学生表的所有信息。

七、修正名叫“trig_1”触发器，当企图向学生表中嵌入、修正或删掉记录时，该触发器自动显示如下信息：“对不起，你无权进行更新操作！”。

八、修正“trig_1”的触发器，当向学生表中修正记录时，该触发器自动表明修正前(deleted)和修改后(inserted)的记录。

九、选做题：建立一个名叫“trig_4”的触发器，当向成绩表嵌入记录时，该触发器自动表明与该记录有关的学生的学号、姓名和班级。

（本题建议建立之前先推论该触发器与否存有）ifexists(selectnamefromsysobjectswherename='trig_4'andtype='tr')droptriggertri g_4gocreatetriggertrig_4on成绩表forinsertasselect学号,姓名,班级from学生表where学号in(select学号frominserted)goinsert成绩表values（“0012”，“02”，80）2、截取图片，填写如下实验过程记录表实验过程记录（特别注意：标示题号）1.2.3.4.。

实验7-存储过程应用初步《数据库原理及应用》课程实验报告GOII、执行过程一：EXECUTE GUOCHENG_ONE /*执行该存储过程功能*/(或者：EXEC GUOCHENG_ONE )（2）（单个参数）查询：设置一个存储过程GUOCHENG_TWO，通过指定可变的读者姓名，显示其所借阅的图书的图书名：I、先以一个“具体的姓名”书写T-SQL语句，检验语法错误；无误后以变量代替“具体的姓名”，进而创建过程二：①SELECT图书名FROM BOOKSWHERE图书编号IN(SELECT图书编号FROM BORROWWHERE读者编号IN(SELECT读者编号FROM READERSWHERE姓名='于志强'))②CREATE PROCEDURE GUOCHENG_TWO @NAME varchar(10)AS/*新建过程二*/SELECT图书名FROM BOOKSWHERE图书编号IN (SELECT图书编号FROM BORROWWHERE读者编号IN (SELECT读者编号FROM READERSWHERE姓名=@NAME ))GO/*过程功能*/II、执行上述过程，即随意指定读者姓名，查询其借阅图书的图书名：EXECUTE GUOCHENG_TWO @NAME='李明'/*执行该存储过程功能*/EXECUTE GUOCHENG_TWO @NAME='李明'/*执行该存储过程功能*/（3）设置（多个参数）插入一条记录的过程；完成后并执行该过程将：“读者编号：1243；姓名：翟萌；种类编号：2；工作单位：西安交通大学；住址：桃苑小区3-415；联系电话：83839228；登记日期：2014-04-15；性别：男”这些内容添加到到表READERS中：I、创建向表READER插入记录的过程如下：CREATE PROCEDURE GUOCHENG_THREE@READERS_NUMBER nchar(10),@NAME varchar(20),@RANGE_NUMBER int,@COMPANY varchar(50),@ADD varchar(100),@TEL varchar(20),@DATE datetime,@SEX char(2)AS/*新建过程三*/INSERT INTO READERSVALUES(@READERS_NUMBER,@NAME,@RANGE_NUMBER ,@COMPANY, @ADD,@TEL,@DATE,@SEX)/*过程功能*/GOII、执行该过程，将记录插入到表中：EXECUTE GUOCHENG_THREE@READERS_NUMBER='1243',@NAME='翟萌',@RANGE_NUMBER='2',@COMPANY='西安交通大学',@ADD='桃苑小区-415',@TEL='83839228',@DATE='2014-04-15',@SEX='男'/*执行该存储过程功能*/2．（1）创建带输出参数的存储过程，要求求出零件表P中所有蓝色零件重量的平均值,并将结果通过输出变量返回给用户：I、创建具有上述功能的带输出参数的存储过程：CREATE PROCEDURE GUOCHENG_FOUR @PJZ INT OUTPUTAS/*创建过程四*/SELECT @PJZ=AVG(WEIGHT)FROM零件表PWHERE COLOR ='蓝'/*过程功能*/GOII、执行该过程：DECLARE @PJZ INTEXECUTE GUOCHENG_FOUR @PJZ OUTPUTPRINT'零件表中所有蓝色零件的平均重量为'+STR(@PJZ)GO（2）创建带输入输出参数的存储过程，要求求出“用户指定的供应商”提供给“用户指定的工程”的”所有零件重量”的平均值,并将结果通过输出变量返回给用户：I、创建具有上述功能的带输入输出参数的存储过程：CREATE PROCEDURE GUOCHENG_FIVE @GYSN NCHAR(10),@GCN NCHAR(10),@PJZ INT OUTPUTAS/*创建过程四*/SELECT @PJZ=AVG(WEIGHT)FROM零件表PWHERE PN IN(SELECT PNFROM供应关系表SPJWHERE SN IN(SELECT SNFROM供应关系表SPJWHERE SN=@GYSN)AND JN IN(SELECT JNFROM供应关系表SPJWHERE JN=@GCN))/*过程功能*/GOII、假设用户指定供应商为S4，工程为J4，执行上述过程：DECLARE @PJZ INTEXECUTE GUOCHENG_FIVE 'S5','J4',@PJZ OUTPUTPRINT'零件的平均值为'+STR(@PJZ)GO（3）（无参数）更新“供应商表”用户指定值的供应表信息：I、创建具有上述功能的存储过程：CREATE PROCEDURE GUOCHENG_SIX @GYSN NCHAR(10),@GYSNAME NCHAR(10),@CSM VARCHAR(50)AS/*创建过程六*/UPDATE供应商表SSET SNAME=@GYSNAME,CITY=@CSMWHERE SN=@GYSN /*过程功能*/GOII、指定S4，修改名称为“SN8”，城市为“土耳其”：附：之前实验报告修改实验五1.1）查询“计算机科学”这类图书中的定价最高的图书书名和定价；SELECT图书名,价格AS定价FROM Books,BookCategoryWHERE价格=(SELECT MAX(价格)FROM BooksWHERE Books.种类编号=BookCategory.种类编号AND种类名称='计算机科学')实验六3．导入“图书借阅”5张表的关系图；导入供应商等四张表的关系图。