实验三 PL SQL程序设计
一、实验目的
1. 掌握PL/SQL的基本语法结构,并学会编写简单程序;
2. 掌握PL/SQL的命名模块设计,包括函数,存储过程的创建与调用方法;
3. 了解学习使用游标、存储过程和触发器的创建与使用方法。
二、课程目标
(1)熟练掌握数据库程序设计的原理与方法,如存储过程、函数和触发器的作用及其创建和调用的方法,以此来解决工程应用中的复杂数据处理问题;
(2)学会使用不同的工具进行数据库管理、应用程序开发,利用相关技术解决实际工程应用中复杂的数据查询及处理问题。
三、实验任务
1. 学习数据库编程语言PL/SQL的基础知识;
2. 使用顺序结构,分支结构及循环结构编写简单的控制程序;
3. 练习存储过程和函数的创建和使用;
四、实验要求
1. 练习多种PL/SQL工具的使用;
2. 独立思考,根据实际情况选择过程或者函数;
3. 提交纸质实验报告和电子版实验报告。
五、实验设计(具体内容根据课程实验特点要求,如算法流程图、核心代码等)
1.在实验二所创建的选课表(SC)的基础上,创建存储过程,对成绩进行等级转换输出。输入一个学号和课程号(输入参数),根据学号和课程号查询该同学的成绩(Grade),输出不同的评语。评价标准按如下方式进行:条件查询:if-else /// case
Grade=100, 评语为“太牛了!”
90<=Grade<100, 评语为“优秀”
80<=Grade<90, 评语为“良好”
70<=Grade<80, 评语为“中等”
60<=Grade<70, 评语为“及格”
40<=Grade<60, 评语为“不及格”
Grade<40, 评语为“太渣了!”
如果Grade 为 NULL, 评语为“缺考”
2. 创建函数,输入参数n, 计算1!+2!+ 3!+ …+ n! 的值。
3. 创建存储过程,根据调用时提供的学生姓名查询该学生所修课程的课程信息,在过程体中将课程号、课程名和成绩输出到输出窗口,给出过程调用语句块。
4. 创建存储过程,统计指定学生学号的平均成绩和选课门数,将统计结果用输出参数传递给主程序,在SQL窗口中调用存储过程,输出过程的返回结果。(选作)
解答如下:
1.在实验二所创建的选课表(SC)的基础上,创建存储过程,对成绩进行等级转换输出。输入一个学号和课程号(输入参数),根据学号和课程号查询该同学的成绩(Grade),输出不同的评语。评价标准按如下方式进行:条件查询:if-else /// case
本人使用的case循环语句,if-else同理,注意输入参数为IN类型,而输出参数为OUT类型。
CREATE PROCEDURE get_grade (
IN s_sno CHAR ( 20 ),
IN s_cno CHAR ( 25 ))
BEGIN
DECLARE s_grade INT;
DECLARE Result VARCHAR(10);
SELECT grade INTO s_grade FROM sc
WHERE
sno = s_sno AND cno = s_cno;
CASE
WHEN s_grade = 100 THEN
SET Result = '太牛了!';
WHEN s_grade >= 90 && s_grade < 100 THEN SET Result = '优秀';
WHEN s_grade >= 80 && s_grade < 90 THEN SET Result = '良好';
WHEN s_grade >= 70 && s_grade < 80 THEN SET Result = '中等';
WHEN s_grade >= 60 && s_grade < 70 THEN SET Result = '及格';
WHEN s_grade >= 40 && s_grade < 60 THEN SET Result = '不及格';
WHEN s_grade < 40 THEN SET Result = '太渣了!';
WHEN s_grade IS NULL THEN SET Result = '缺考';
END CASE;
SELECT Result;
END
CALL get_grade('121004','1004');
2.创建函数,输入参数n, 计算1!+2!+ 3!+ …+ n! 的值。
注意:由于MySQL的安全设置,代码运行后可能会出现“This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)"的提示,这是因为MySQL默认的安全设置
要求存储过程和函数必须声明它们是确定性的(即给定相同的输入,它们将总是返回相同的结果
),为了解决这个问题,需要添加DETERMINISTIC关键字。
CREATE FUNCTION Ccompute(n INT ) RETURNS INT DETERMINISTIC
BEGIN
DECLARE sum INT DEFAULT 0;
DECLARE y INT DEFAULT 1;
DECLARE x INT DEFAULT 1;
WHILE x <= n DO
SET y := y * x;
SET sum := sum + y;
SET x := x + 1;
END WHILE;
RETURN sum;
END;
SELECT Ccompute(3);
3. 创建存储过程,根据调用时提供的学生姓名查询该学生所修课程的课程信息,在过程体中将课程号、课程名和成绩输出到输出窗口,给出过程调用语句块。
CREATE PROCEDURE get_cno_cname_grade (
IN stuname VARCHAR ( 20 )) BEGIN
DECLARE
course_id CHAR ( 20 );
DECLARE
course_name VARCHAR ( 20 );
DECLARE
course_grade INT;
SELECT
course.cno,
course.cname,
sc.grade
FROM
course,
sc
WHERE
course.cno = sc.cno
AND sc.sno =(
SELECT
sno
FROM
student
WHERE
sname = stuname
);
END;
CALL get_cno_cname_grade ( '李佳慧' );
4.创建存储过程,统计指定学生学号的平均成绩和选课门数,将统计结果用输出参数传递给主程序,在SQL窗口中调用存储过程,输出过程的返回结果。
CREATE PROCEDURE get_avg_count ( IN stu_sno CHAR ( 20 ), OUT stu_avg FLOAT, OUT stu_count INT ) BEGIN
SELECT
AVG( grade ),
COUNT( cno ) INTO stu_avg,
stu_count
FROM
SC
WHERE
sc.sno = stu_sno;
END;
CALL get_score_course ( '121002', @ss_a, @ss_b );
SELECT @ss_a,@ss_b;