Oracle存储过程以及游标嵌套实际使用

Oracle数据库，存储过程以及游标嵌套实际使用

Oracle对存储过程嵌套游标的使用

这学期新开的Oracle的课程，但是无奈课本是很浅显的东西，内容比较少，以至于老师布置的作业有一些难度，需要参考很多资料才能解决

Oracle的游标

最开始先介绍一下书上对游标的解释 ： 由SELECT语句返回的完整行集称为结果集，使用SELECT语句进行查询时可以得到这个结果集，但有时用户需要对结果集中的某一行或部分行单独处理，这在SELECT的结果集中无法实现，游标(Cursor) 就是提供这种机制的对结果集的一种扩展，PL/SQL 通过游标提供了对一个结果集进行逐行处理的能力。
游标包括以下两部分的内容:

1. 游标结果集:定义游标的SELECT语句返回的结果集的集合；
2. 标当前行指针:指向该结果集中某一行的指针；

游标具有下列优点:

1. 允许定位在结果集的特定行；
2. 从结果集的当前位置检索一行或一部分行；
3. 支持对结果集中当前位置的行进行数据修改；
4. 为由其他用户对显示在结果集中的数据库数据所做的更改提供不同级别的可见性支持；

游标包括显式游标(Explicit Cursor)和隐式游标(Implicit Cursor),显式游标的操作要遵循声明游标、打开游标、读取数据和关闭游标等步骤，而使用隐式游标不需执行以上步骤，只需让PL/SQL处理游标并简单地编写SELECT语句

需要的表格

student表格

course表格

score表格

teacher表格

teaching表格

department表格

题目

1、用显示游标循环列出所有学生选修的全部课程的成绩，包括院系名称、学生姓名、性别、课程名、成绩。
2、创建函数，查询某门课程的平均分、最高分和最低分，分数保留小数点后一位。
3、创建一个存储过程，输入学生学号，显示出该学生所选修课程及成绩（按五级分制）。
4、创建触发器，当删除学生表中的某一学生信息时，判断在成绩表中该学生的平均成绩是否高于60，若高于60，则不能删除（要进行异常处理），否则允许删除，同时删除成绩表中该学生对应的选课信息。

解决如下：

作业需要的表格自行导入
使用的是sql developer
有人说student表里的birthday类型不是日期怎么办
将student用excel打开，然后左上角另存为文本格式txt的，再在Oracle里面导入
1、用显示游标循环列出所有学生选修的全部课程的成绩，包括院系名称、学生姓名、性别、课程名、成绩。
定义游标

DECLARE
p_dname char(22);
p_sname char(8);
p_ssex char(4);
p_cname char(18);
p_grade char(4);
CURSOR curstudent1
IS
SELECT yuanxi.dname,student.sname,student.ssex,course.cname,score1.grade
FROM student ,course ,yuanxi ,score1 
WHERE score1.sno=student.sno AND score1.cno=course.cno AND student.dept=yuanxi.dno
ORDER BY student.sname;
BEGIN
OPEN curstudent1;
FETCH curstudent1 INTO p_dname,p_sname,p_ssex,p_cname,p_grade;
WHILE curstudent1%FOUND LOOP
DBMS_OUTPUT.PUT_LINE('院系名称:'||p_dname||',学生姓名:'||p_sname||',性别:'||p_ssex||',课程名:'||p_cname||',成绩:'||p_grade);
FETCH curstudent1 INTO p_dname,p_sname,p_ssex,p_cname,p_grade;
END LOOP;
CLOSE curstudent1;
END;

这是没有按照学生名字排序的结果，上述的代码按照学生名字排序更加直观的的出结果

2、创建函数，查询某门课程的平均分、最高分和最低分，分数保留小数点后一位。
函数创建：

create or replace FUNCTION funAvgMaxMin(p_cname IN char)
RETURN char
AS
result char(400);
p_avg number;
p_max number;
p_min number;
BEGIN
SELECT ROUND(AVG(grade),1) INTO p_avg
FROM COURSE,SCORE1
WHERE COURSE.cno=SCORE1.cno AND COURSE.cname=p_cname;
SELECT MAX(grade) INTO p_max
FROM COURSE,SCORE1
WHERE COURSE.cno=SCORE1.cno AND COURSE.cname=p_cname;
SELECT MIN(grade) INTO p_min
FROM COURSE,SCORE1
WHERE COURSE.cno=SCORE1.cno AND COURSE.cname=p_cname;
result :='该门课的平均分为：'||p_avg||',该门课的最高分为：'||p_max||',该门课的最低分为：'||p_min;
RETURN(result);
END;

调用过程：（funAvgMaxMin（里输入查询的课程名字））

ECLARE
v_result char(400);
BEGIN
v_result:= funAvgMaxMin('大学英语');
DBMS_OUTPUT.PUT_LINE(v_result);
END;

3、创建一个存储过程，输入学生学号，显示出该学生所选修课程及成绩（按五级分制）；
存储过程创建：
重点是嵌套的游标
在学习的过程书上也有说SELECT INTO 只能返回单行值，最初想用循环，但是写了一天一直没解决出来，于是去咨询老师，老师也说游标麻烦，用语句循环可以，但是一晚上依旧无果，无奈才考虑游标，发现游标并没有想象的那么麻烦。于是嵌入游标返回多行值。
如果有人能解决，希望也能参考一下。

create or replace PROCEDURE spCnameGrade(p_sno IN number)
AS
p_cname char(16);
p_grade number;
p_result char(8);
CURSOR curtest 
IS
SELECT cname,grade
FROM SCORE1,COURSE
WHERE SCORE1.sno=p_sno AND SCORE1.cno=course.cno;
BEGIN
OPEN curtest;
FETCH curtest INTO p_cname,p_grade;
WHILE curtest%FOUND LOOP
CASE
WHEN p_grade>=90 AND p_grade<=100 THEN P_result:='优秀';
WHEN p_grade>=80 AND p_grade<90 THEN P_result:='良好';
WHEN p_grade>=70 AND p_grade<80 THEN P_result:='中等';
WHEN p_grade>=60 AND p_grade<70 THEN P_result:='及格';
WHEN p_grade>=0 AND p_grade<60 THEN P_result:='不及格';
ELSE p_result :='NOTHING';
END CASE;
DBMS_OUTPUT.PUT_LINE('该学生所选课程是：'||p_cname||'  ,成绩是：'||p_grade||','||p_result);
FETCH curtest INTO p_cname,p_grade;
END LOOP;
CLOSE curtest;
END;

调用过程：

DECLARE
BEGIN
spCnameGrade('201301001');
END;

4、创建触发器，当删除学生表中的某一学生信息时，判断在成绩表中该学生的平均成绩是否高于60，若高于60，则不能删除（要进行异常处理），否则允许删除，同时删除成绩表中该学生对应的选课信息。
触发器创建：

create or replace TRIGGER trigDropscore
BEFORE DELETE ON student FOR EACH ROW
DECLARE
Coursegrade number;
pragma autonomous_transaction;
BEGIN
SELECT AVG(grade)into Coursegrade
FROM score1
WHERE sno IN
(
SELECT student.sno
FROM student
WHERE sname=:OLD.sname
);
IF coursegrade>60 THEN
RAISE_APPLICATION_ERROR(-20004,'该学生平均成绩大于60，不能删除');
ELSIF DELETING THEN
DELETE FROM score1
WHERE sno IN
(SELECT sno
FROM student
WHERE sname=:OLD.sname
);
END IF;
COMMIT;
END;

删除测试：

DELETE FROM STUDENT
WHERE sname='张磊';

如果第三题有人又不是游标的方法，还希望也能给我参考一下。