数据库存储过程示例
例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
/*打开游标SX便指向查询结果的第一行*/
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-4： (存储过程) 创建显示所有学生平均成绩的存储过程。
例7-5： (修改数据库表) 在Student表中增加SAVG(N,6,2) 字段。
例7-6： (存储过程) 创建存储过程，计算每个学生的平均成绩保存到学生表SAVG字段中。
例7-7： (触发器) 当更新学生成绩表SCORE中的学生成绩时，自动计算该学生的平均成绩保存到学生表SAVG字段中。
/*******************************************/
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 VALUES('95004','1', 92);
commit;
delete from SC where sno='95004';
commit;
例7-8： (触发器) 创建包含插入、删除、修改多种触发事件的触发器DML_LOG，对SCORE表的操作进行记录。用INSERTING、DELETING、UPDATING谓词来区别不同的DML操作。
先创建事件记录表LOGS，该表用来对操作进行记录。该表的字段含义解释如下：
LOG_ID：操作记录的编号，数值型，它是该表的主键，自动
增1，可由序列自动生成。
LOG_TABLE：进行操作的表名，字符型，非空，该表设计成可以由多个触发器共享使用。比如我们可以为Student表创建类似的触发器，同样将操作记录到该表。
LOG_DML：操作的动作，即INSERT、DELETE或UPDATE三种之一。
LOG_KEY_ID：操作时表的主键值，数值型。之所以记录表的主键，是因为主键是表的记录的惟一标识，可以识别是对哪一条记录进行了操作。对于Score表，主键是由SNO_CNO构成。
LOG_DATE：操作的日期，日期型，取当前的系统时间。
LOG_USER：操作者，字符型，取当时的操作者账户名。比如登录SCOTT账户进行操作，在该字段中，记录账户名为SCOTT。
/*******************************************/
set serveroutput on;
create table LOGS(
LOG_ID number primary key,
LOG_TABLE VARCHAR2(30),
LOG_DML char(10),
LOG_KEY_ID VARCHAR2(30),
LOG_DATE 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 VALUES(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 VARCHAR2(30),
LOG_DML char(10),
LOG_KEY_ID VARCHAR2(30),
LOG_DATE 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 VALUES(LOG_ID.nextval, TLOG_TABLE, TLOG_DML, TLOG_KEY_ID, TLOG_DATE, TLOG_USER);
end if;