Oracle 10g中存储过程中使用游标
实例设计
1. 功能设计
某高校开发的研究生招生系统，要求设计PL/SQL程序对考生的成绩数据进行处理，处理的逻辑是根据每门专业课的最低分数线和总分的最低分数线自动将考生归类为录取考生、调剂考生和落选考生。
为此设计两个数据表，graduate数据表存放考生成绩，result数据表存放处理结果，PL/SQL程序完成的功能就是将graduate数据表中的数据逐行扫描，根据分数线进行判断，计算出各科总分，在result数据表中将标志字段自动添加上“录取”或“落选”。
--创建数据库表 --graduate
CREATE TABLE "GRADUATE" (
"BH" NUMBER(10) NOT NULL,
"XM" VARCHAR2(10) NOT NULL,
"LB" VARCHAR2(10) NOT NULL,
"YINGYU" NUMBER(4, 1) NOT NULL,
"ZHENGZHI" NUMBER(4, 1) NOT NULL,
"ZHUANYE1" NUMBER(4, 1) NOT NULL,
"ZHUANYE2" NUMBER(4, 1) NOT NULL,
"ZHUANYE3" NUMBER(4, 1) NOT NULL
)
--创建序列
create sequence my_suquence --序列名称
minvalue 0 --序列最小值
start with 201112081 --序列起始值
maxvalue 999999999 --序列最大值
increment by 1 --序列的增长值
nocache --不预先在内存中缓存
cycle; --达到最大值后再重新循环
--插入数据
insert into graduate values(my_suquence.nextval,'张三丰','硕士','56','56','67','78','79');
insert into graduate values(my_suquence.nextval,'张无极','硕士','34','56','56','48','79');
insert into graduate values(my_suquence.nextval,'张翠山','硕士','44','78','76','88','39');
insert into graduate values(my_suquence.nextval,'赵敏','硕士','67','56','69','73','70');
insert into graduate values(my_suquence.nextval,'周芷若','硕士','43','56','45','78','67');
insert into graduate values(my_suquence.nextval,'小昭','硕士','64','66','64','76','79');
--创建数据库表 result
CREATE TABLE "RESULT" (
"BH" NUMBER(10) NOT NULL,
"XM" VARCHAR2(10) NOT NULL,
"LB" VARCHAR2(10) NOT NULL,
"YINGYU" NUMBER(4, 1) NOT NULL,
"ZHENGZHI" NUMBER(4, 1) NOT NULL,
"ZHUANYE1" NUMBER(4, 1) NOT NULL,
"ZHUANYE2" NUMBER(4, 1) NOT NULL,
"ZHUANYE3" NUMBER(4, 1) NOT NULL,
"TOTALSCORE" NUMBER(5, 1) NOT NULL,
"FLAG" VARCHAR2(4) NOT NULL
)
select * from result;
--创建存储过程
create or replace procedure graduateprocess(
tempzhangzhi in graduate.zhengzhi%type,--将表里面的类型赋给该变量
tempyingyu in graduate.yingyu%type,
tempzhuanye1 in graduate.zhuanye1%type,
tempzhuanye2 in graduate.zhuanye2%type,
tempzhuanye3 in graduate.zhuanye3%type,
temptotalscore in result.totalscore%type)
as
--定义graduate为记录型变量，临时存放通过游标从graduate表中提取的记录
graduaterecord graduate%rowtype;
--定义graduatetotalscore为数值型变量，统计总分
graduatetotalscore result.totalscore%type;
--定义graduateflag 为字符型变量，根据结果放入落选或录取
graduateflag varchar2(4);
--定义游标graduatecursor,存放的是所有的graduate数据表中的记录
cursor graduatecursor is
select * from graduate;
--定义异常处理
errormessage exception;
begin
--打开游标
open graduatecursor;
if graduatecursor%not found then
--如果没有数据。抛出异常
raise errormessage;
end if;
--如果有数据，将数据循环出来
loop
fetch graduatecursor into graduaterecord;
--计算总分
graduatetotalscore:=graduaterecord.zhengzhi+graduaterecord.yingyu+graduaterecord.zhuanye1+graduaterecord.zhuanye2+graduaterecord.zhuanye3;
--比较判断条件，看是否将数据插入到result数据库中
if(graduaterecord.yingyu>=tempyingyu and
graduaterecord.zhengzhi>=tempzhengzhi and
graduaterecord.zhuanye1>=tempzhuanye1 and
graduaterecord.zhuanye2>=tempzhuanye2 and
graduaterecord.zhuanye3>=tempzhuanye3 and
graduaterecord.totalscore>=temptotalscore )
then
graduateflag:='录取'
else
graduateflag:='落选'
--退出if
end if;
--退出游标循环
exit when graduatecursor%notfound;
--向result数据库中插入数据
insert into result(BH,xm,lb,yingyu,zhengzhi,zhuanye1,zhuanye2,zhuanye3,totalscore,flag)
values(graduaterecord.BH,graduaterecord.XM,graduaterecord.LB,graduaterecord.YINGYU,graduaterecord.ZHENGZHI,
graduaterecord.ZHUANYE1,graduaterecord.ZHUANYE2,graduaterecord.ZHUANYE3,graduatetotalscore,graduateflag);
end loop;
--关闭游标
close graduatecursor;
commit;--提交结果
--处理异常
exception
when errormessage then
dbms_output.put_line('无法打开数据表');
/*程序执行结束"*/
end;
end graduateprocess;--存储过程结束