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;
欢迎各位批评指教!