下载文档原格式
实训项⽬存储过程的创建和使⽤⽹络数据库实训报告⼀、实训⽬的和要求1、了解存储过程的作⽤；2、掌握创建、修改及删除存储过程的⽅法；3、掌握执⾏存储过程的⽅法。
⼆、实训所需仪器、设备硬件：计算机软件：操作系统Windows XP、SQL Server 2005三、实训内容（⼀）不带参数的存储过程的创建和修改1、在student数据库中创建⼀个名为myp1的存储过程，该存储过程的作⽤是显⽰t_student中的全部记录。
USE STUDENTIF EXISTS(SELECT name FROM sysobjectsWHERE name='mpy1'AND type='P')DROP PROCEDURE mpy1GOCREATE PROCEDURE myp1ASSelect*FROM T_STUDENTGO2、运⾏myp1，检查是否实现功能。
use studentexec myp13、修改myp1，使其功能为显⽰t_student中班级为05541班的学⽣记录，然后测试是否实现其功能。
set ANSI_NULLS ONset QUOTED_IDENTIFIER ONgoALTER PROCEDURE [dbo].[myp1]ASSelect*FROM T_STUDENTwhere left(s_number,5)='05541'use studentexec myp14、创建⼀个存储过程myp2，完成的功能是在表t_student、表t_course和表t_score 中查询以下字段：班级、学号、姓名、性别、课程名称、考试分数。
USE STUDENTIF EXISTS(SELECT name FROM sysobjectsWHERE name='myp2'AND type='P')DROP PROCEDURE myp2GOCREATE PROCEDURE myp2ASSelect班级=SUBSTRING(T_STUDENT.S_NUMBER,1,LEN(T_STUDENT.S_NUMBER)-2),学号=SUBSTRING(T_STUDENT.S_NUMBER,LEN(T_STUDENT.S_NUMBER)-1,2),S_NAME AS姓名,SEX AS性别,T_COURSE.C_NAME AS课程名称,t_SCORE.SCORE AS考试分数FROM T_STUDENT,T_COURSE,t_SCOREWHERE T_STUDENT.S_NUMBER=t_SCORE.S_NUMBERAND T_COURSE.C_NUMBER=t_SCORE.C_NUMBERGO（⼆）带输⼊参数的存储过程的创建1、创建⼀个带有⼀个输⼊参数的存储过程stu_info，该存储过程根据传⼊的学⽣编号，在t_student中查询此学⽣的信息。
存储过程与函数的构建与使用存储过程和函数是数据库中常用的两种程序化对象，它们都可以用来封装一定的复杂业务逻辑，在数据库中进行复用，提高数据库的性能和可维护性。
1. 存储过程的构建和使用存储过程是一种预编译的数据库对象，可以用来执行一些具体的操作。
在构建存储过程时，需要用到以下的语法结构：CREATE PROCEDURE procedure_name@parameter datatype(size) = default_value,ASBEGINSQL statementsEND1. 创建存储过程的语法是“CREATE PROCEDURE 存储过程名”。
其中，存储过程名是自己定义的，应该符合命名规范。
2. 存储过程可以包含输入输出参数，所以需要在存储过程中定义参数的数据类型和默认值。
3. SQL语句块始终包含在BEGIN和END语句之间，并以AS语句开头。
构建完存储过程后，就可以使用以下的语句来调用存储过程：EXEC procedure_name parameter1, parameter2, ...其中，parameter1、parameter2等是存储过程中定义的参数。
执行上述语句后，存储过程会按照自己的逻辑进行处理。
2. 函数的构建和使用函数是一种特殊的存储过程，它返回一个值，常用于数据处理过程中。
在构建函数时，需要用到以下的语法结构：CREATE FUNCTION function_name (@parameter datatype(size)) RETURNS datatype(size)ASBEGINSQL statementsEND1. 函数的创建语法是“CREATE FUNCTION 函数名”。
函数名应该符合命名规范。
2. 函数返回一个值，因此需要在函数中定义返回值的数据类型。
3. SQL语句块始终包含在BEGIN和END语句之间，并以AS语句开头。
构建完函数后，就可以使用以下的语句来调用存储过程：SELECT dbo.function_name(parameter)其中，parameter是函数中定义的参数。
实验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.删除两个刚刚建立触发器
以上内容请书写实验报告
余下时间请完成数据库的预备试验。
北京理工大学珠海学院实验报告ZHUHAI CAMPAUS OF BEIJING INSTITUTE OF TECHNOLOGY 班级学号姓名指导教师成绩实验题目使用T-SQL编写存储过程和函数实验时间一实验目的1. 理解存储过程的概念、使用方式；2. 熟悉使用T-SQL编写存储过程来进行数据库应用程序的设计；3. 掌握SQL Server中自定义函数的概念，熟悉自定义函数的类型；4. 能创建相关的自定义函数，解决T-SQL程序设计中的相关问题。
二实验工具SQL Server 2008利用SQL Server 2008 SSMS及其SQL新建查询编辑器，使用T-SQL编写存储过程和函数。
三实验内容和要求1.建立学生-课程数据库，其中包含学生表Student(Sno,Sname,Ssex,Sage,Sdept)、课程表：Course(Cno,Cname,Cpno,Ccredit)和学生选课表：SC(Sno,Cno,Grade)；编写相应的存储过程，完成下面的功能：（1）编写一个存储过程，可以查询指定系的学生的选课信息，列出学号、姓名、所在系、课程名和成绩等内容。
调用该存储过程，测试执行结果。
（2）编写一个存储过程，返回指定课程的平均分。
调用该存储过程，测试执行结果。
（3）编写一个存储过程，可以查询指定系指定成绩等级的学生的选课信息，列出学号、姓名、所在系、课程名和成绩等内容。
调用该存储过程，测试执行结果。
（成绩等级为优、良、中、及格、不及格，其中成绩在90分到100分之间为‘优’，在80分到89分之间为‘良’，在70分到79分之间为‘中’，在60分到69分之间为‘及格’，在0分到59分之间为‘不及格’。
）要求：提交创建存储过程的SQL脚本，并标注必要的注释。
保证程序能够正确编译和运行，并有相应的测试代码。
2. 自定义标量函数假设有一家生产的集装箱的公司，生产一些不同型号的集装箱，存储集装箱信息的表如下所示。
实验五存储过程和触发器的使用【目的要求】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语句放在单个批处理中。
实验五创建存储过程和触发器
一、实验目的
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、sch数据表的表结构如表7、1所示,sch表的数据如表7、2。
表7、1 sch 表结构字段名数据类型主键外键非空唯一自增id INT(10) 就是否就是就是否name V ARCHAR(50) 否否就是否否class V ARCHAR(50) 否否就是否否表7、2 sch 表的内容id name class1 李明C12 小梅C21)建表sch并插入数据。
2)创建一个存储函数,用来统计表sch中的记录数。
delimiter $$create PROCEDURE count_sch5(out size int)BEGINselect count(*) into size from sch;END$$delimiter;3)创建一个存储过程,通过调用存储函数的方法来获取表sch中的记录数与sch表中id的与。
delimiter $$create PROCEDURE count_sch2(out s_a int,out s_id int)BEGINselect count(*) into s_a from sch;select sum(id) as s_id from sch;END$$delimiter;2、创建一存储过程insert_student_condition_user,利用自定义错误触发条件定义,当插入学生的性别不就是“男”或“女”时结束存储过程,并提示“学生性别不正确”。
表7、3 student表结构字段名数据类型主键外键非空唯一自增sno Char(10) 就是否就是就是否sname VARCHAR(20) 否否就是否否ssex CHAR(2) 否否就是否否Sage smallint 否否就是否否Sdept VARCHAR(30) 否否就是否否enterdate datetime 否否就是否否delimiter $$create PROCEDURE insert_student_condition_user(in sno int,in sname VARCHAR(20),in ssex varchar(2),in sage int,in sdept varchar(30))BEGINDECLARE continue handler for sqlstate '42S02' select '学生性别不正确';if ssex='男' or ssex='女' THENinsert into student values(sno,sname,ssex,sage,sdept);end if;end$$ delimiter;call insert_student_condition_user(1,'pqw','她',20,'C1');call insert_student_condition_user(4,'pqw','1',20,'C1');drop PROCEDURE insert_student_condition_user;3、创建一存储过程update_student_borthyear,在学生表(表结构如表7、3)中添加字段”borthyear”(出生年份),在存储过程中利用游标,通过学生年龄计算出出生年份并修改表中对应字段。
存储过程、触发器和用户自定义函数实验实验内容一练习教材中存储过程、触发器和用户自定义函数的例子。
教材中的BookSales数据库，在群共享中，文件名为BookSales.bak。
实验内容二针对附件1中的教学活动数据库，完成下面的实验内容。
1、存储过程（1）创建一个存储过程，该存储过程统计“高等数学”的成绩分布情况，即按照各分数段统计人数。
执行存储过程：exec countpeople（2）创建一个存储过程，该存储过程有一个参数用来接收课程号，该存储过程统计给定课程的平均成绩。
执行存储过程：exec avg_score'C602'（3）创建一个存储过程，该存储过程将学生选课成绩从百分制改为等级制（即A、B、C、D、E）。
执行存储过程：exec alterscore（4）创建一个存储过程，该存储过程有一个参数用来接收学生姓名，该存储过程查询该学生的学号以及选修课程的门数。
执行存储过程：exec select_courses'李强'（5）创建一个存储过程，该存储过程有两个输入参数用来接收学号和课程号，一个输出参数用于获取相应学号和课程号对应的成绩。
执行存储过程:declare@score smallintexec select_socre'98601','C602',@score outputprint'成绩是'+convert(char(2),@score)+'分'2、触发器（1）为study表创建一个UPDATE触发器，当更新成绩时，要求更新后的成绩不能低于原来的成绩。
创建完触发器尝试进行更新数据：update study set score=60 where sno='98601'and cno='C601'执行完之后查询结果发现成绩仍然是90select score from study where sno='98601'and cno='C601'再更新一个高于90分的成绩则可以成功update study set score=91 where sno='98601'and cno='C601'select score from study where sno='98601'and cno='C601'（2）为study表创建一个DELETE触发器，要求一次只能从study表中删除一条记录。
存储过程的建立与使用存储过程的建立与使用一、实验目的理解存储过程的概念、作用、建立和调用方法。
二、实验原理使用CREA TE PROCEDURE语句创建存储过程，ALTER PROCEDURE语句修改存储过程，DROP PROCEDURE语句删除存储过程，存储过程有不带参数的、有带输入参数的、有带输出参数(output)的，还可以有带返回值的。
创建好的存储过程可以使用EXEC procedure_name语句执行。
三、实验设备安装有SQL SERVER 2000的计算机。
四、实验示例1、模糊查询create procedure proc_empname @E_name varchar(10)asselect a.emp_name,a.dept,b.tot_amtfrom employee a inner join sales bon a.emp_no=b.sale_idwhere a.emp_name like @E_namegoexec proc_empname '刘%'2、利用存储过程计算出’E0008’业务员的销售总金额。
create procedure proc_saletot @E_no char(5), @p_tot int outputasselect @p_tot=sum(tot_amt)from saleswhere sale_id=@E_nogodeclare @tot_amt intexec proc_saletot E0014, @tot_amt outputselect @tot_amt3、创建一带返回值的存储过程，返回某一部门的平均工资create proc proc_avg_salary @Dept char(4)asdeclare @avg_salary intselect @avg_salary=avg(salary)from employeewhere dept=@Deptreturn @avg_salarydeclare @avg intexec @avg=proc_avg_salary '人事'print '返回值='+cast(@avg as char(10))五、实验内容1、利用存储过程，给employee表添加一条业务部门员工的信息。
实验训练5：存储过程与函数的构建与使用
一、存储过程与函数的概念
存储过程和函数都是数据库中的可执行代码，可以被多次调用和重复
使用。
存储过程是一组预定义的SQL语句集合，可以在数据库中定义和存储。
而函数是一个独立的代码块，它接收输入参数并返回一个值。
二、存储过程的构建与使用
1. 创建存储过程
在MySQL中，创建存储过程需要使用CREATE PROCEDURE语句。
例如：
CREATE PROCEDURE myproc()
BEGIN
SELECT * FROM mytable;
END;
这个例子创建了一个名为myproc的存储过程，它会查询mytable表中的所有数据。
2. 调用存储过程
使用CALL语句可以调用已经创建好的存储过程。
例如：
CALL myproc();
这个语句会执行myproc存储过程中定义的SQL语句。
3. 存储过程参数
我们可以给存储过程添加参数来使其更加灵活。
例如：
CREATE PROCEDURE myproc(IN p1 INT, IN p2 VARCHAR(50)) BEGIN
SELECT * FROM mytable WHERE column1 = p1 AND column2 = p2;
END;
这个例子创建了一个带有两个输入参数p1和p2的存储过程，它会查询mytable表中column1等于p1并且column2等于p2的数据。
4. 存储过程变量
除了参数之外，存储过程还可以使用变量来存储中间结果。
例如：
CREATE PROCEDURE myproc(IN p1 INT)
BEGIN
DECLARE v1 INT;
SET v1 = p1 * 2;
SELECT * FROM mytable WHERE column1 = v1;
END;
这个例子创建了一个带有一个输入参数p1和一个变量v1的存储过程，它会将p1乘以2并将结果存储在v1变量中，然后查询mytable表中column1等于v1的数据。
5. 存储过程控制流
在存储过程中，我们可以使用控制流语句来实现条件分支和循环等功能。
例如：
CREATE PROCEDURE myproc(IN p1 INT)
BEGIN
IF p1 > 0 THEN
SELECT * FROM mytable WHERE column1 > p1;
ELSE
SELECT * FROM mytable WHERE column2 < -p1;
END IF;
END;
这个例子创建了一个带有一个输入参数p1的存储过程，它会根据p1的值选择不同的查询条件。
三、函数的构建与使用
函数是一种特殊的存储过程，它接收输入参数并返回一个值。
与普通存储过程不同，函数必须有返回值，并且不能修改数据库中的数据。
1. 创建函数
在MySQL中，创建函数需要使用CREATE FUNCTION语句。
例如：
CREATE FUNCTION myfunc(p INT) RETURNS INT
BEGIN
RETURN p * 2;
END;
这个例子创建了一个名为myfunc的函数，它接收一个整数参数p并
返回p乘以2的结果。
2. 调用函数
调用函数需要使用SELECT语句。
例如：
SELECT myfunc(3);
这个语句会调用myfunc函数，并返回3乘以2的结果6。
3. 函数参数
与存储过程类似，函数也可以接收输入参数。
例如：
CREATE FUNCTION myfunc(p1 INT, p2 VARCHAR(50)) RETURNS INT
BEGIN
DECLARE v1 INT;
SET v1 = LENGTH(p2);
RETURN p1 * v1;
END;
这个例子创建了一个带有两个输入参数p1和p2的函数，它会计算
p2字符串的长度并将结果乘以p1返回。
4. 函数控制流
与存储过程类似，我们也可以在函数中使用控制流语句来实现条件分支和循环等功能。
例如：
CREATE FUNCTION myfunc(p INT) RETURNS INT
BEGIN
IF p > 0 THEN
RETURN p * 2;
ELSE
RETURN -p * 3;
END IF;
END;
这个例子创建了一个带有一个输入参数p的函数，它会根据p的值选择不同的计算方式。
四、总结
存储过程和函数都是数据库中可执行代码的一种形式，可以被多次调用和重复使用。
存储过程是一组预定义的SQL语句集合，而函数是一
个独立的代码块。
在MySQL中，创建存储过程和函数需要使用CREATE语句，调用存储过程和函数需要使用CALL和SELECT语句。
存储过程和函数都可以接收输入参数并返回一个值，可以使用控制流语句实现条件分支和循环等功能。
