郑州轻工业大学数据库实验三 PL/SQL 程序设计

实验三 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;