Oracle資料庫基本操作五——預存程序與觸發器

標籤：end   update   lin   cot   trigger   sys   增加   enc   產生   

 

4． 預存程序與觸發器：

例7-1： (預存程序) 建立一個顯示學生總人數的預存程序。

set serveroutput oncreate or replace procedure student_countas p1 number(3);beginselect count(*) into p1 from student;dbms_output.put_line(‘學生總人數是：‘||p1);end;/execute student_count();

例7-2： (預存程序) 建立顯示學生資訊的預存程序STUDENT_LIST，並引用STU_COUNT預存程序。

set serveroutput on;create or replace procedure student_listascursor select_hand is/*定義遊標方便使用*/select sno,rtrim(sname) as sname,ssex,sage,sdept,sclass from student;begin    for i in  select_hand  loop    dbms_output.put_line(i.sno||‘ ‘||i.sname||‘ ‘||i.ssex||‘ ‘||i.sage||‘ ‘
　　　　||i.sdept||‘ ‘||i.sclass);    end loop;    STUDENT_COUNT();end;/execute student_list();

例7-3： (預存程序) 建立一個顯示學生平均成績的預存程序。

set serveroutput on;create or replace procedure student_avgs(no in student.sno%type)asavgs1 number(3,1);beginselect avg(score) into avgs1 from score group by sno having sno = no ;dbms_output.put_line(‘學號為：‘||no||‘ 的平均成績是：‘|| avgs1);end;/execute student_avgs(‘96002‘);

例7-4： (預存程序) 建立顯示所有學生平均成績的預存程序。

set serveroutput oncreate or replace procedure student_avgsascursor  calcu_avgs is select sno,avg(sno) as avgs from score group by sno;beginfor i in calcu_avgs loopdbms_output.put_line(‘學號為：‘||i.sno||‘ 的平均成績是：‘|| i.avgs);end loop;end;/execute student_avgs();

例7-5： (修改資料庫表) 在Student表中增加SAVG(N,6,2) 欄位。

alter table student add savg number(6,2);

例7-6： (預存程序) 建立預存程序，計算每個學生的平均成績儲存到學生表SAVG欄位中。

set serveroutput oncreate or replace procedure save_savgascursor calcu_avgs is select sno, avg(sno) as ag from score group by sno;beginfor i in calcu_avgs loopupdate student set savg = i.ag where sno = i.sno;end loop;end;/execute save_savg();

 

例7-7： (觸發器) 當更新學產生績表SCORE中的學產生績時，自動計算該學生的平均成績儲存到學生表SAVG欄位中。

create or replace trigger TSTU_UPAafter update or delete or insert on scorereferencing new as new old as old for each rowbeginif updating or inserting thenupdate student set savg=-1 where sno=:new.sno;end if;if deleting thenupdate student set savg=-1 where sno=:old.sno;end if;end;/create or replace trigger TSTU_UPSafter update or delete or insert on studentdeclaressno char(5);ssavg number(6,1);beginselect sno into ssno from student where savg=-1;select avg(score) into ssavg from score group by sno having sno=ssno;if updating or inserting thenupdate student set savg=ssavg where sno=ssno;end if;if deleting thenupdate student set savg=ssavg where sno=ssno;end if;end;

 

例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 screferencing new as new old as old for each rowdeclaretlog_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;beginselect max(logs.log_id) into tlog_id from logs;tlog_table := ‘sc‘;tlog_date := sysdate;tlog_user := user;if inserting thentlog_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;

 

Oracle資料庫基本操作五——預存程序與觸發器