数据库作业储存过程

1、实验11存储过程1 .实验目的(1) 掌握使用SQL Server管理平台和Transact-SQL语句创建存储过程、执行存储过程、修改存储过程、删除存储过程的用法。(2) 理解使用SQL Server管理平台和Transact-SQL语句查看存储过程定义、重命名存储过程的用法。2 .实验内容及步骤请先附加studentsdb数据库，然后完成以下实验。(1) CREATE PROCEDURE st_gAS BEGINSELECT x.学号,x.姓名,y.分数FROM student_info x ,grade y Where x. 学号二y.学号 END该程序完成的功能是 创建一个存储过程，储

2、存分 数。(2) 设计一个存储过程proc_grade完成这样的功能：查询grade表中课程编号为k002'的学号、分数信息，并使存储过程不能使用sp_helptext查看(即 加密)，请编写程序实现。答：create procedure proc_grade with encryption as beginselect grade .学号,grade .分数from gradewhere grade .课程编号='k002'end(3) 修改存储过程proc_grade ,查询grade表中课程编号为k001'的学号、分数信息，去掉proc_grade加密性，

3、使其在运行时重新编译。答：alter procedure proc_gradeasbeginselect grade . 学号 , grade . 分数from gradewhere grade . 课程编号='k001'end(4) 4)在 student_info 表中增加一列名为“手机号”， 数据类型 char(11) 。 设计一个存储过程proc_tel ， 查询 student_info 表中末尾数字为5的手机号码。答： alter table student_infoadd 手机号 char ( 11)create procedure proc_telasbegin

4、insert into student_info ( 学号 , 手机号 )values ( , )select 手机号from student_infowhere 手机号 like '%5'endexec proc_tel(5) 设计一个存储过程proc_avg ,输出grade表中每个学生的平均成绩。答： create procedure proc_avgasbeginselect AVG( grade . 分数 ) as 平均成绩, grade . 学号from gradegroup by grade . 学号endexec proc_avg(6) 6)设计一个存储过程pr

5、oc_list ，查询 grade 表中指定课程( 参数 cidchar(4) 的成绩排名前3 的学生成绩信息。编写程序并调用该存储过程。答： create procedure proc_listcid char(4)asbeginselect top 3 *from gradewhere grade . 课程编号=cidorder by grade . 分数 descendexec proc_list7) 设计一个存储过程proc_stu ，查询某门课程某个分数段的学生信息，显示学号、姓名、分数，要求课程名称和分数段均为输入参数，请编写程序并调用该存储过程。create procedure

6、proc_stu sclass nchar ( 10), score decimal (4, 1), score2 decimal ( 4, 1)asbeginselect a . 学号 , a. 姓名 , b. 分数from student_info a inner join grade b on a . 学号 =b. 学号 inner join curriculum c on b .课程编号=c. 课程编号where c . 课程名称=sclass and b . 分数>score and b . 分数<score2 endexec proc_stu ' ' ,

7、 , ( 自己输入参数)8) 设计一个存储过程proc_course ， 查询某门课程的平均成绩并将其输出给用户。课程名称为输入参数，平均成绩为输出参数，编写程序并调用该存储过程。答： create procedure proc_course cclass nchar ( 10),avgscore decimal ( 4, 1) outputasbeginselect avgscore =avg( grade . 分数 )from grade inner join curriculum on grade . 课程编号=curriculum . 课程编号where curriculum . 课程

8、名称=cclassenddeclare cclass nchar ( 10) exec proc_course ' ' , cclass outputselect cclass 平均成绩9) 设计一个存储过程，用于修改指定学生( 参数 sid char(4) 指定课程(参数为 cid char(4) 的分数 (score decimal(3,1) ，并输出该生的平均成绩avgs)，要求输出格式为： “该生的最新平均成绩为： ”。编写并调用该存储过程(修改学号0002'的学生的彳读课程K003'的成绩改为96)。答：create procedure proc_al

9、tersid char(4),cid char (4),score decimal (3, 1), avgs decimal ( 3, 1) outputas beginupdate gradeset grade . 分数 =scorewhere grade . 学号 =sid and grade . 课程编号=cidselect avgs=avg(grade . 分数 ) from gradewhere grade . 学号 =sid enddeclare avgs decimal ( 3, 1)exec proc_alter '0002' , 'K003'

10、, 96, avgs outputselect avgs 平均成绩(10)设计一个存储过程proc_credit 完成这样的功能：输入学号sid、 课程名称52m#数值，将查询curriculum、grade表，并从输出参数 scoecredit 获取该学生该课程的成绩和学分，如果分数大于等于60，则返回对应课程的学分，否则返回学分值0，请编写程序并调用该存储过程。答：create procedure proc_creditsid char(4),cnamenchar ( 10),credit int output , score decimal (4, 1) output asbeginselect score=grade . 分数 , credit =curriculum . 学分from grade inner join curriculum on grade . 课程编号=curriculum . 课程编号where grade . 学号 =sid and curriculum . 课程名称=cnameend declare credit int , score decimal ( 4, 1 ) exec proc_credit ' ' , ' ' ,credit output