oracle过程查询学习笔记

orcale语句具有编程特性，它既可以完全支持一般的sql语句，又可以利用自身强大的特性对sql语句进行编程化操作，就像linux的sell语句一样进行批处理。

create table student(snum NUMBER(10) primary key,sname varchar2(10) not null,ssex varchar2(5) default '不详',sbirthday date default '');create table courses(cnum NUMBER(3) primary key,cname varchar2(20) not null);create table results(snum NUMBER(3),cnum NUMBER(3),grade NUMBER(3),rank varchar2(5));create table tempmsg(snum NUMBER(3),cnum NUMBER(3),grade NUMBER(3));create table grades(min_grade NUMBER(3),max_grade NUMBER(3),gradelin varchar2(10) default '',remark varchar2(1) default '',msg varchar2(10) default '');INSERT INTO student VALUES(101,'张三', '女', '07-5月-1990');INSERT INTO student VALUES(102,'李四', '男', '25-11月-1992');INSERT INTO student VALUES(103,'王五', '女', '12-8月-1991');INSERT INTO student VALUES(104,'赵六', '男', '08-10月-1991');INSERT INTO student VALUES(105,'周七', '男', '03-4月-1992');INSERT INTO student VALUES(106,'孙八', '男', '21-7月-1990');INSERT INTO student VALUES(107,'钱九', '男', '21-12月-1990');INSERT INTO student VALUES(108,'沈十', '女', '11-7月-1992');INSERT INTO COURSES VALUES (91, 'C语言');INSERT INTO COURSES VALUES (92, 'JAVA语言');INSERT INTO COURSES VALUES (93, 'Oracle编程');INSERT INTO COURSES VALUES (94, '语文');INSERT INTO RESULTS VALUES (101, 91, 85, '优');INSERT INTO RESULTS VALUES (103, 91, 71, '良');INSERT INTO RESULTS VALUES (104, 93, 82, '良');INSERT INTO RESULTS VALUES (105, 92, 95, '优');INSERT INTO RESULTS VALUES (107, 94, 90, '优');INSERT INTO RESULTS VALUES (108, 92, 67, '良');INSERT INTO TEMPMSG VALUES (102, 91, 58);INSERT INTO TEMPMSG VALUES (101, 94, 88);INSERT INTO TEMPMSG VALUES (104, 93, 0);INSERT INTO TEMPMSG VALUES (106, 92, 95);INSERT INTO TEMPMSG VALUES (112, 93, 76);INSERT INTO TEMPMSG VALUES (110, 91, 100);INSERT INTO TEMPMSG VALUES (103, 92, 55);INSERT INTO TEMPMSG VALUES (105, 91, 100);INSERT INTO TEMPMSG VALUES (107, 93, 89);insert into grades(min_grade,max_grade) values(0,59);insert into grades(min_grade,max_grade) values(60,79);insert into grades(min_grade,max_grade) values(70,89);insert into grades(min_grade,max_grade) values(90,100);Commit;

下面是一些操作的例子：

/**编写匿名块，打印成绩表中编号为 101的学生的编号，姓名，课程编号和分数。**/DECLARECURSOR MYCUR IS SELECT * FROM RESULTS;MYREC RESULTS%ROWTYPE;S_NAME STUDENT.SNAME%TYPE;BEGINOPEN MYCUR;FETCH MYCUR INTO MYREC;WHILE MYCUR%FOUND LOOPIF myrec.snum=101 THEN SELECT SNAME INTO S_NAME FROM STUDENT WHERE SNUM=myrec.snum;dbms_output.put_line(S_NAME||'****'||myrec.snum||'****'||myrec.cnum||'****'||myrec.grade);END IF;FETCH MYCUR INTO MYREC;END LOOP;END;

/**编写匿名块，遍历students表中所有人员信息，根据grades表中的分数分档，更新results表中学生的成绩档次(grade字段)，并对各自的成绩进行判断： **/# (1)如果学生成绩在A档，打印信息“xxx,你是好样的！”#（2）如果学生成绩在B档或者C档，打印“xxx,再接再厉！”# (3)其他的打印“xxx,你还差得远呢！”#（4）如果执行中出现异常，捕获异常并提示异常的系统信息；declarein_snum TEMPMSG.snum%type;in_sname student.sname%type;in_cname COURSES.cname%type;in_cnum COURSES.cnum%type;MSG VARCHAR2(2);s_grade TEMPMSG.grade%type;cursor mycur is select * from student WHERE SNUM IN(SELECT SNUM FROM TEMPMSG GROUP BY SNUM);myrec  student%rowtype;beginopen mycur;fetch mycur into myrec;while mycur%found LOOPin_snum:=myrec.snum;IN_SNAME:=MYREC.SNAME;select grade into s_grade from tempmsg where snum = in_snum;select cnum into in_cnum from tempmsg where snum = in_snum;select CNAME into in_cname from COURSES where cnum = in_cnum;IF S_GRADE<60 THEN DBMS_OUTPUT.PUT_LINE(IN_SNAME||'-的-'||in_cname||'-课考了-'||s_grade||'-分，成绩不行啊，无人关心！');END IF;IF S_GRADE BETWEEN 60 AND 79 THEN DBMS_OUTPUT.PUT_LINE(IN_SNAME||'-的-'||in_cname||'-课考了-'||s_grade||'-分,你还差得远呢！');END IF;IF S_GRADE BETWEEN 80 AND 89 THEN DBMS_OUTPUT.PUT_LINE(IN_SNAME||'-的-'||in_cname||'-课考了-'||s_grade||'-分,再接再厉！');END IF;IF S_GRADE BETWEEN 90 AND 100 THEN DBMS_OUTPUT.PUT_LINE(IN_SNAME||'-的-'||in_cname||'-课考了-'||s_grade||'-分,你是好样的！');END IF;fetch mycur into myrec;end loop; end;

#根据Students表判断临时表中人员信息是否存在，不存在的按如下格式打印： “编号为：xxx的学生，没有基本信息！”；DECLARENOTIN STUDENT.SNUM%TYPE;CURSOR MYCUR IS SELECT * FROM TEMPMSG WHERE SNUM NOT IN(SELECT DISTINCT SNUM FROM STUDENT);MYREC TEMPMSG%ROWTYPE;S_SNUM TEMPMSG.SNUM%TYPE;BEGIN OPEN MYCUR;FETCH MYCUR INTO MYREC;WHILE MYCUR%FOUND LOOPDBMS_OUTPUT.PUT_LINE('编号为：'||MYREC.SNUM||'的学生，没有基本信息！');FETCH MYCUR INTO MYREC;END LOOP;END;#判断考试成绩表中是否已存在对应的人员编号和课程编号，如果存在，更新分数；如果不存在，将最新信息插入考试成绩表；DECLARECURSOR MYCUR IS SELECT * FROM RESULTS;MYETC RESULTS%ROWTYPE;BEGINOPEN MYCUR;FETCH MYCUR INTO MYETC;WHILE MYCUR%FOUND LOOPDBMS_OUTPUT.PUT_LINE(MYETC.SNUM);END LOOP;END;

欢迎各位批评指教！