【Oracle】第五章遊標和觸發器，oracle第五章遊標

【Oracle】第五章遊標和觸發器，oracle第五章遊標

第五章遊標和觸發器

遊標：

隱式遊標：%FOUND, %NOTFOUND ,%ROWCOUNT

1．%FOUND 用法，只有在DML 語句影響一行或者多行時，%FOUND 屬性才返回 TRUE。下列樣本示範了 %FOUND 的用法：

begin

  update employees2 set first_name = 'scott' where employee_id = 2;

if SQL%found then

  dbms_output.put_line('資料已經更新');

       --  dbms_output.put_line('rowCount = '||mrowcount);

else

  dbms_output.put_line('資料沒有找到');

end if;

end;

/

以下代碼示範了建立了一個遊標，返回employees2 表中 salary 大於300000 的記錄，注意type 的使用：

    declare

    csalary employees2.salary%type;

    cursor emp2_cursor is select salary from employees2 where salary >300000;

begin

  open emp2_cursor ;

  loop

    fetch emp2_cursor into csalary;

    exit when emp2_cursor%notfound;

    dbms_output.put_line('csalary = '||csalary);

  end loop;

end;

/

以下代碼示範了建立了一個遊標，返回employees2 表中 division_id=’SAL’ 的記錄。

注意rowtype 的使用：

declare

 cursor employee2_cursor is select * from employees2 where division_id='SAL';

  myrecord employees2%rowtype;

begin

  open employee2_cursor;

  fetch employee2_cursor into myrecord;

  while employee2_cursor%found loop

    dbms_output.put_line('employee id ='||myrecord.employee_id);

    dbms_output.put_line('first Name  ='||myrecord.first_name);

    dbms_output.put_line('last name ='||myrecord.last_name);

    fetch employee2_cursor into myrecord;

end loop;

end;

/

以下代碼示範了帶參數的遊標，根據division id 查詢指定的記錄：

 declare

  myrecord employees2%rowtype;

  cursor emp_cursor(divisionid varchar2) is select * from employees2 where division_id =divisionid;

begin

  open emp_cursor('&divisionid');

--loop

  fetch emp_cursor into myrecord;

  while emp_cursor%found loop

 -- exit when emp_cursor%notfound;

  dbms_output.put_line('employee id = '||myrecord.employee_id);

  dbms_output.put_line('division id = ' ||myrecord.division_id);

  dbms_output.put_line('first name = ' ||myrecord.first_name);

  fetch emp_cursor into myrecord;

end loop;

close emp_cursor;

end;

/

以下代碼示範了在建立遊標時指定更新表中的欄位。更新 employees2 表中的 first_name 欄位：

set serveroutput on

 declare

 firstName varchar2(20);

 cursor employees2_cursor is select first_name from employees2 where employee_id=1 for update of 

 first_name;

 begin

   open employees2_cursor;

   loop

    fetch employees2_cursor into firstName;

    exit when employees2_cursor%notfound;

   update employees2

   set first_Name='jeff ' where current of employees2_cursor;

  end loop;

 close employees2_cursor;

 commit;

 end; /

觸發器：

觸發器是當特定事件出現時自動執行的預存程序

特定事件可以是執行更新的DML語句和DDL語句

觸發器不能被顯式調用

觸發器的功能：

自動產生資料

自訂複雜的安全許可權

提供審計和日誌記錄

啟用複雜的商務邏輯

觸發器的類型包括：行級觸發器，語句級觸發器、INSTEAD OF 觸發器、模式觸發器、資料庫級觸發器。

建立觸發器文法：

CREATE [OR REPLACE] TRIGGER trigger_name

AFTER | BEFORE | INSTEAD OF

[INSERT] [[OR] UPDATE [OF column_list]] 

[[OR] DELETE]

ON table_or_view_name

[REFERENCING {OLD [AS] old / NEW [AS] new}]

[FOR EACH ROW]

[WHEN (condition)]

pl/sql_block;

建立行級觸發器，以下代碼示範了插入或者修改 employees2 表中的first_name 如果等於 ‘scott’時觸發器就會執行：

create or replace trigger tri_employees2

 before insert or update of first_name

   on employees2

  referencing NEW as newdata OLD as olddata

 for each row

 when (newdata.first_name='scott')

  begin

        : newdata.salary :=20000;

     dbms_output.put_line('new.salary:' || :newdata.salary);

     dbms_output.put_line('old.salary:' || :olddata.salary);

  end;

執行以上觸發器：

insert into employees2 values(38,'SUP','WOR','scott','mp',50000);

或者：

update employees2 set salary=90000,first_name='scott' where employee_id=38;

以下代碼針對資料完整性進行操作(刪除dept 表中的ID 同時把外鍵表employee表中對應的 deptid 資訊刪除)：

建立對應的表：

      create table dept(deptId number(10),deptName varchar2(20));

    insert into dept(deptid,deptname) values(1,'學術部')
insert into dept(deptid,deptname) values(2,'教質部')
insert into dept(deptid,deptname) values(3,'市場部')
insert into dept(deptid,deptname) values(4,'財務部')

create table emp(empid number(10),empName varchar2(20),deptid number(10))
insert into emp(empid,empName,deptid) values(1,'scott',1)
insert into emp(empid,empName,deptid) values(2,'jack',2)
insert into emp(empid,empName,deptid) values(3,'mike',3)
insert into emp(empid,empName,deptid) values(4,'jeff',4)
insert into emp(empid,empName,deptid) values(5,'vera',1)
insert into emp(empid,empName,deptid) values(6,'linda',2)
insert into emp(empid,empName,deptid) values(7,'Marho',3)
insert into emp(empid,empName,deptid) values(7,'dick',4)

  刪除操作：

         create or replace trigger del_deptid

 after delete on dept

 for each row

 begin

   delete from employee where deptid = :old.id;

 end del_deptid;

 /

執行以上觸發器(由於在dept 表中建立了觸發器，刪除dept 表對應ID 記錄同時刪除employee 表中對應的deptid 資訊)：

   delete from dept where id=1;  查看employee 表中的 deptid 記錄;

添加操作：

    create or replace trigger insert_dept

after insert on dept

for each row

begin

 insert into employee(id,name,deptid) values('6','chenmp',:new.id);

end;

/

   執行以上觸發器：

insert into dept values(6,'銷售部門');

  查看employee 表中的 deptid 記錄

修改操作(在dept 表中建立UPDATE 觸發器,修改DEPT 表ID 同時修改employee 表中的DEPTID)：

  create or replace trigger update_dept

after update on dept

for each row

  begin

   update employee set deptid = :new.id where deptid = :old.id;

end;

/

執行以上觸發器：

update dept set id=8 where id=1;

查看employee 表中的 deptid 記錄

以下代碼示範了行級觸發器：

建立表：

drop table rowtable;

      create  table rowtable (id number(8) , name varchar2(100));

建立序列

        create sequence rowtablesequence;

建立觸發器：

create or replace trigger set_sequence

before insert on rowtable

for each row

declare

    rsequence number(8);

begin

select rowtablesequence.nextval into rsequence from dual;

   :NEW.id :=rsequence;

end;

/

執行SQL語句：

     insert into rowtable values(232,'scott');

以下代碼示範了語句級觸發器:

建立表：

create table mylog(curr_user varchar2(100),curr_date date,opera varchar2(10));

建立觸發

create or replace trigger tri_mylog

after insert or delete or update on employees2

begin

if inserting then

insert into mylog values(user,sysdate,'insert');

elsif deleting then

insert into mylog values(user,sysdate,'delete');

else

insert into mylog values(user,sysdate,'update');

end if;

end;

/

模式觸發器：可以在模式級的操作上建立觸發器，如：create ,alter,drop,grant,revoke 和truncate 等 DDL語句：

以下樣本對使用者所刪除的所有對象進行日誌記錄。

1． 建立資料庫表：

  drop table dropped_obj;

     CREATE TABLE dropped_obj

(

  obj_name VARCHAR2(30),

  obj_type VARCHAR2(20),

  drop_date DATE

);

    2．建立觸發器：

CREATE OR REPLACE TRIGGER log_drop_obj

AFTER DROP ON SCHEMA

BEGIN

  INSERT INTO dropped_obj

  VALUES (ORA_DICT_OBJ_NAME, ORA_DICT_OBJ_TYPE, SYSDATE);

END;

/

   3．建立和刪除對象：

          建立對象：CREATE  TABLE  for_drop ( x CHAR );

          刪除對象：DROP  TABLE  for_drop;

4．查看日誌表中的資訊：

         SELECT  *  FROM  dropped_obj;

起用和禁用觸發器：

   以下代碼示範了禁用biu_emp_deptno 觸發器：

         ALTER TRIGGER biu_emp_deptno DISABLE;

   以下代碼示範了啟用biu_emp_deptno 觸發器：   

         ALTER TRIGGER biu_emp_deptno enable;

可以使用：

        Alter table table_name{enable | disable} all triggers;

禁用或者起用在特定表上建立的所有觸發器。

刪除觸發器：

        Drop trigger trigger_name;

查看觸發器資訊，可以使用user_trigers 資料字典視圖。

Desc user_triggers