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

--觸發器和常用內建程式包
--author:shine
--一.觸發器：
--1.觸發器組成：由觸發器語句，觸發器限制，觸發器操作三部分組成。
--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 table 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 database
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:用於將查詢結果轉換為xml格式。
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)在oracle目錄下搜尋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('----------------------');
 

Trackback: http://tb.blog.csdn.net/TrackBack.aspx?PostId=2165043