oracle 觸發器 和 常用內建程式包

-觸發器和常用內建程式包
--author:shine
--一.觸發器：
--1.觸發器組成：由觸發器語句，觸發器限制，觸發器操作三部分組成。[@more@]--exp:1.1.1
create or replace trigger tri_test1
before update or insert
on emp
for each row --以上是觸發器語句（即：什麼時機觸發）
when (new.sal > 5000) --觸發器限制(即：滿足什麼條件時執行觸發器操作,注意：只能一個觸發器只能有一個限制)
begin --以下是觸發器操作（即：滿足觸發器限制時執行什麼操作）
dbms_output.put_line('會不會多啦點');
:new.sal := 2000;
end;

--測試
update emp set sal = 6000 where empno=7369;

--2.觸發器型別：
--1)行級觸發器:(每一行觸發一次)
--exp:1.2.1：用觸發器做個自動增長列
create stu (stuno int ,stuname varchar2(10));
create sequence seq_stuno ;

create or replace trigger tri_test2
before insert or update on stu --before行級觸發器，在記錄進入之前觸發
for each row --行級觸發器標誌
declare
stu_no int;
begin
if updating then
raise_application_error(-20001,'此表不能更新！');
elsif inserting then
select seq_stuno.nextval into stu_no from dual;
:new.stuno := stu_no; --干預了插入的新值
end if;
end;

--測試
insert into stu(stuname)
select ename from emp;

--2)語句級觸發器：不論一句影響了多少行，每一句只觸發一次（即：以分號為標誌）
--exp:1.2.2
create or replace trigger tri_test3
after insert or update or delete on stu --after語句級觸發器，在記錄進入資料庫之後觸發
begin
if inserting then
dbms_output.put_line('進行了插入');
elsif updating then
dbms_output.put_line('進行了更新');
elsif deleting then
dbms_output.put_line('進行了刪除');
end if;
end;

--測試
delete from stu;

/*
小結一下：
a.為了不使觸發器內部發生混亂，觸發器禁止使用rollback,commit.
b.在使用觸發器時應該防止觸發器之間發生遞迴呼叫。
c.after和before觸發器雖然都能使用new和old，但是由於before觸發器是在對資料進行操作之前觸發，
所以它可以干預new值（如：exp1.2.1中），而after觸發器不能干預new值。
d.如果不說明是before，after觸發器，則預設為after觸發器.
*/

--3)instead of 觸發器
--a.instead of 觸發器 只能用在檢視上，不能用在表上。
--b.instead of 只能是行級，不能是語句級（即：要寫for each row）

--exp:1.2.3
drop view view_emp_dept;
create view view_emp_dept as
select empno,ename,b.* from emp a,dept b where a.deptno = b.deptno;

select * from view_emp_dept ;

create or replace trigger tri_view_emp
instead of update or insert on view_emp_dept
referencing new as n
for each row
declare
cursor emp_cur is select * from emp where empno = :n.empno;
cursor dept_cur is select * from dept where deptno = :n.deptno;
emp_row emp%rowtype;
dept_row dept%rowtype;
begin
open emp_cur;
open dept_cur;
fetch emp_cur into emp_row;
if emp_cur%notfound then
insert into emp(empno,ename,deptno) values(:n.empno,:n.ename,:n.deptno);
else
update emp set ename = :n.ename,deptno = :n.deptno where empno = :n.empno;
end if;

fetch dept_cur into dept_row;
if dept_cur%notfound then
insert into dept values(:n.deptno,:n.dname,:n.loc);
else
update dept set dname = :n.dname, loc = :n.loc where deptno = :n.deptno;
end if;
close emp_cur;
close dept_cur;
end;

--測試
insert into view_emp_dept
values(8888,'yaoyao',33,'yaoyao','wuhan');

--4）模式觸發器：
--針對的是DDL語句,如：drop,create,alter,grant,revoke.truncate，主要用來寫日誌
--exp:1.2.4
create or replace trigger tri_test4
before truncate or grant or drop or create or alter or revoke on schema
begin
dbms_output.put_line(ora_dict_obj_name||' '||ora_dict_obj_type||' '||sysdate||' '||ora_dict_obj_owner);
end;

--測試
grant select on emp to shine;
drop table emp;

--5）資料庫級觸發器：
--針對的是資料庫的例項的，如：啟動，關閉，登陸，登出等等,必需是sys/change_on_install as sysdba許可權
--exp:1.2.5
create or replace trigger tri_test5
after startup on
begin
dbms_output.put_line('資料庫啟動啦');
end;

--3.啟用觸發器和禁用觸發器
alter trigger tri_test4 disable;--禁用

alter trigger tri_test4 enable;--啟用

--4.刪除觸發器
drop trigger tri_test4;

--5.檢視觸發器
desc user_triggers;
select * from user_triggers;

--二.常用內建程式包
--1.dbms_output:顧名思義管輸出的
--1)put 輸出到緩衝區（沒換行）
--2)putline 輸出一行（帶換行）
begin
dbms_output.put('ssssss'||'aaaa'); --若只寫這一句，是把‘c’輸入緩衝區，Output視窗看不見。
dbms_output.new_line();
end;

--2.dbms_lob:larger object,操作大物件的
--1)append,2)copy,3)erase,4)getlength,5)instr,6)read,7)substr,8)write,9)fileopen,10)filegetname,11)fileclose
--操作大物件已在"操作大物件"一文中講過了，這裡不再重複。

--3.dbms_xmlquery.getXML:用於將查詢結果轉換為格式。
declare
result1 clob;
xmlstr varchar(32767);
strline varchar(2000);
line_no number := 1;
begin
result1 := dbms_xmlquery.getXML('select * from dept');
xmlstr := substr(result1,1,32767);
loop
exit when xmlstr is null;
strline := substr(xmlstr,1,instr(xmlstr,chr(10))-1);
dbms_output.put_line(line_no||' : '||strline);
xmlstr := substr(xmlstr,instr(xmlstr,chr(10))+1);
line_no := line_no + 1;
end loop;
end;

--4.dbms_random:操作隨機數
--1）dbms_random.random生成8位的隨機整數.
declare
num number;
begin
for i in 1..10
loop
num := abs(dbms_random.random mod 100);
dbms_output.put_line(num);
end loop;
end;
--2)dbms_random.value生成指定範圍的隨機數
--一個大於m小於n的隨機數
begin
dbms_output.put_line(dbms_random.value(1,100));
end;

--一個大於m小於n的整數
begin
dbms_output.put_line(round(dbms_random.value(1,100),0));
end;

/*
3）dbms_randon.string（引數一，引數二）;
其中：
引數二是指生成隨機數的長度.
引數一有以下幾種形式：
1）'u' 生成的是大寫字母
2) 'l' 生成的是小寫字母
3）'a' 生成的是大小寫混合
4）'x' 生成的是數字和大寫字母混合
5）'p' 任何形式（連特殊符號都行）;
*/
begin
dbms_output.put_line(dbms_random.string('x',8));
end;

--5.utl_file：用於從pl/sql中讀寫作業系統文字檔案。
--由於版本原因 操作上有一點點不一樣
--若是9i的老版本採用方法一，若是10i則採用方法二
/*
方法一:
1)在目錄下搜尋init.ora檔案，加上UTL_FILE_DIR=D:myxml (後面的D:myxml是你放文字檔案的資料夾)
2)在sqlplus上以sys/change_on_install as sysdba登陸,然後輸入以下命令：
3)shutdown immediate;
4)startup mount;
5)alter system set UTL_FILE_DIR='D:myxml' scope=spfile;
6)shutdown immediate;
7)startup;
8)再show parameter utl檢驗一下，若utl_file_dir對應的value值是d:myxml，說明你的資料夾配好了，接下來就是
pl/sql程式碼了。如下：
*/
--write xml:把src（大物件）先放入緩衝當中，再放到檔案中
declare
src clob;
xmlfile utl_file.file_type;
length number;
buffer varchar2(16384);
begin
src := dbms_xmlquery.getXML('select * from dept');
length := dbms_lob.getlength(src);
dbms_lob.read(src,length,1,buffer);
xmlfile := utl_file.fopen('D:myxml','dept.xml','w');
utl_file.put(xmlfile,buffer);
utl_file.fclose(xmlfile);
end;

--read xml
declare
input_file utl_file.file_type;
input_buffer varchar2(2000);
begin
input_file:=utl_file.fopen('D:myxml','dept.xml','r');
loop
utl_file.get_line(input_file,input_buffer);
dbms_output.put_line(input_buffer);
end loop;
utl_file.fclose(input_file);
exception
when no_data_found then
dbms_output.put_line('----------------------');
end;

/*
方法二:
1)用system/manager 登陸
2）建立資料夾 create directory MY_XML as 'D:myxml';
3）授權 grant write,read on directory MY_XML to scott;
4)用scott登陸 conn scott/tiger;
*/
--write xml:把src（大物件）先放入緩衝當中，再放到檔案中
declare
src clob;
xmlfile utl_file.file_type;
length number;
buffer varchar2(16384);
begin
src := dbms_xmlquery.getXML('select * from dept');
length := dbms_lob.getlength(src);
dbms_lob.read(src,length,1,buffer);
xmlfile := utl_file.fopen(MY_XML,'dept.xml','w');--就這裡不同
utl_file.put(xmlfile,buffer);
utl_file.fclose(xmlfile);
end;

--read xml
declare
input_file utl_file.file_type;
input_buffer varchar2(2000);
begin
input_file:=utl_file.fopen(MY_XML,'dept.xml','r');--就這裡不同
loop
utl_file.get_line(input_file,input_buffer);
dbms_output.put_line(input_buffer);
end loop;
utl_file.fclose(input_file);
exception
when no_data_found then
dbms_output.put_line('----------------------');