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的学生的编号，姓名，课程编号和分数。**/
DECLARE
CURSOR MYCUR IS SELECT * FROM RESULTS;
MYREC RESULTS%ROWTYPE;
S_NAME STUDENT.SNAME%TYPE;
BEGIN
	OPEN MYCUR;
	FETCH MYCUR INTO MYREC;
	WHILE MYCUR%FOUND LOOP
	IF 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）如果执行中出现异常，捕获异常并提示异常的系统信息；
declare
	in_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;
begin
open mycur;
	fetch mycur into myrec;
	while mycur%found LOOP
	in_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的学生，没有基本信息！”；
DECLARE
NOTIN 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 LOOP
	DBMS_OUTPUT.PUT_LINE('编号为：'||MYREC.SNUM||'的学生，没有基本信息！');
	FETCH MYCUR INTO MYREC;
	END LOOP;
END;
#判断考试成绩表中是否已存在对应的人员编号和课程编号，如果存在，更新分数；如果不存在，将最新信息插入考试成绩表；
DECLARE
CURSOR MYCUR IS SELECT * FROM RESULTS;
MYETC RESULTS%ROWTYPE;
BEGIN
	OPEN MYCUR;
	FETCH MYCUR INTO MYETC;
	WHILE MYCUR%FOUND LOOP
		DBMS_OUTPUT.PUT_LINE(MYETC.SNUM);
	END LOOP;
END;

欢迎各位批评指教！