数据库考试必备

例7-2：(存储过程) 创建显示学生信息的存储过程STUDENT_LIST，并引用STU_COUNT存储过程。

set serveroutput on;
create or replace procedure student_list
As
sno char(5);
sname char(10);
sdept char(2);
sclass char(2);
ssex char(4);
sage number(2);
CURSOR SX is
SELECT Sno, Sname,Sdept, Sclass,Ssex, Sage FROM Student;
Begin
loop
FETCH sx INTO sno , sname , sdept, sclass, ssex, sage;
exit when sx%notfound;
dbms_output.put_line
(Sno||' '||Sname||' '||Sdept||' '|| Sclass||' '||Ssex||' '|| Sage);
end loop;
end;
/
execute student_list;
drop procedure student_list;
例7-3：(存储过程) 创建一个显示学生平均成绩的存储过程。

set serveroutput on;
create or replace procedure student_avg_score
as
CURSOR SX is /*定义游标*/
SELECT Sno, avg(score) as y FROM Sc group by sno;
begin
for x in sx loop
dbms_output.put_line(x.Sno||' '||x.y);
end loop;
end;
例7-7：(触发器) 当更新学生成绩表SCORE中的学生成绩时，自动计算该学生的平均成绩保存到学生表SA VG字段中。

set serveroutput on;
create or replace trigger sc_update_row after insert or delete or update on sc
referencing new as new old as old for each row declare
ssno char(5);
ssavg number(6,2);
begin
if deleting then
update student set savg=-1 where sno=:OLD.sno;
end if;
if updating or inserting then
update student set savg=-1 where sno=:new.sno;
end if;
end;
create or replace trigger sc_update after insert or delete or update on sc
declare
ssno char(5);
S number;
ssavg number(6,2);
begin
SELECT Sno into ssno FROM student where savg=-1;
SELECT COUNT(*) into s FROM Sc where sno=ssno;
if (s >0) THEN
SELECT avg(score) into ssavg FROM Sc
group by sno
having sno=ssno;
update student set savg=ssavg where sno=ssno;
end if;
end;
/
INSERT INTO SC V ALUES('95004','1', 92); commit;
delete from SC where sno='95004';
commit;
例7-8：(触发器) 创建包含插入、删除、修改多种触发事件的触发器DML_LOG，对SCORE表的操作进行记录。

用INSERTING、DELETING、
UPDA TING谓词来区别不同的DML操作。

先创建事件记录表LOGS，该表用来对操作进行记录。

该表的字段含义解释如下：
LOG_ID：操作记录的编号，数值型，它是该表的主键，自动增1，可由序列自动生成。

LOG_TABLE：进行操作的表名，字符型，非空，该表设计成可以由多个触发器共享使用。

比如我们可以为Student表创建类似的触发器，同样将操作记录到该表。

set serveroutput on;
create table LOGS(
LOG_ID number primary key,
LOG_TABLE V ARCHAR2(30),
LOG_DML char(10),
LOG_KEY_ID V ARCHAR2(30),
LOG_DA TE DATE,
LOG_USER char(30) );
create sequence LOG_ID;
create or replace trigger sc_LOGS after insert or delete or update on sc
referencing new as new old as old for each row declare
TLOG_ID LOGS.LOG_ID%type;
TLOG_TABLE LOGS.LOG_TABLE%type;
TLOG_DML LOGS.LOG_DML%type;
TLOG_KEY_ID
LOGS.LOG_KEY_ID%type;
TLOG_DATE LOGS.LOG_DATE%type;
TLOG_USER LOGS.LOG_USER%type; begin
SELECT MAX(LOGS.LOG_ID) INTO TLOG_ID FROM LOGS;
TLOG_TABLE := 'SC';
TLOG_DATE := SYSDATE;
TLOG_USER := USER;
if inserting then
TLOG_DML := 'insert';
TLOG_KEY_ID:=:OLD.ROWID;
INSERT INTO LOGS V ALUES(LOG_ID.nextval, TLOG_TABLE, TLOG_DML, TLOG_KEY_ID, TLOG_DATE, TLOG_USER);
end if;
.......................
end;
或
set serveroutput on;
create table LOGS(
LOG_ID number primary key,
LOG_TABLE V ARCHAR2(30),
LOG_DML char(10),
LOG_KEY_ID V ARCHAR2(30),
LOG_DA TE DATE,
LOG_USER char(30) );
create sequence LOG_ID;
create or replace trigger sc_LOGS after insert or delete or update on sc
referencing new as new old as old for each row declare
TLOG_ID LOGS.LOG_ID%type;
TLOG_TABLE LOGS.LOG_TABLE%type;
TLOG_DML LOGS.LOG_DML%type;
TLOG_KEY_ID
LOGS.LOG_KEY_ID%type;
TLOG_DATE LOGS.LOG_DATE%type;
TLOG_USER LOGS.LOG_USER%type; begin
SELECT MAX(LOGS.LOG_ID) INTO TLOG_ID FROM LOGS;
TLOG_TABLE := 'SC';
TLOG_DATE := SYSDATE;
TLOG_USER := USER;
if inserting then
TLOG_DML := 'insert';
TLOG_KEY_ID:=:OLD.ROWID;
INSERT INTO LOGS V ALUES(LOG_ID.nextval, TLOG_TABLE, TLOG_DML, TLOG_KEY_ID, TLOG_DATE, TLOG_USER);
end if;。