您好,登錄后才能下訂單哦!
小編給大家分享一下Oracle觸發(fā)器和游標(biāo)的示例分析,相信大部分人都還不怎么了解,因此分享這篇文章給大家參考一下,希望大家閱讀完這篇文章后大有收獲,下面讓我們一起去了解一下吧!
??創(chuàng)建一個dept_log數(shù)據(jù)表,并在其中定義兩個字段(operate_tag varchar2(10),operate_time date),分別用來存儲操作種類(插入,修改,刪除)信息和操作日期。然后一個關(guān)于dept表的語句級觸發(fā)器tri_dept,將用戶對 dept 表的操作信息保存到dept_tag表中。
當(dāng)任何時候從dept表中刪除某個部門時,該觸發(fā)器將從emp表中刪除該部門的所有雇員。
-- 創(chuàng)建表 create table dept_log( operate_tag varchar2(10), operate_time date ); create table dept( dname varchar2(20), dno number ); -- 創(chuàng)建觸發(fā)器 create or replace trigger tri_dept before insert or update or delete on dept declare v_tag varchar2(10); begin if inserting then v_tag:='插入'; elsif updating then v_tag:='修改'; elsif deleting then v_tag:='刪除'; end if; insert into dept_log values(v_tag, sysdate); end tri_dept; /
當(dāng)任何時候從dept表中刪除某個部門時,該觸發(fā)器將從emp表中刪除該部門的所有雇員。
注意:所有的以sysdba登錄的賬戶都不能創(chuàng)建觸發(fā)器,因此需要在創(chuàng)建的用戶下面創(chuàng)建觸發(fā)器
-- 創(chuàng)建部門表 create table dept( deptno number not null, dname varchar(20) not null ); -- 創(chuàng)建員工表 create table emp( emp_no number not null, emp_name varchar(20) not null, job varchar(20) not null, sal number not null, deptno number not null ); --插入數(shù)據(jù) insert into dept values(10, '部門1'); insert into dept values(20, '部門2'); insert into emp values(1001, '員工1', '工作1', 5000, 10); insert into emp values(1002, '員工2', '工作2', 7200, 10); insert into emp values(1003, '員工3', '工作3', 6000, 10); insert into emp values(1004, '員工4', '工作4', 5000, 20); insert into emp values(1005, '員工5', '工作5', 7000, 20);
-- 創(chuàng)建觸發(fā)器 create or replace trigger del_dept before delete on dept for each row begin delete from emp where deptno = :old.deptno; end; /
創(chuàng)建了一個TRIG_INSERT的觸發(fā)器,在向表account插入數(shù)據(jù)之后會向表myevent插入一組數(shù)據(jù)(表結(jié)構(gòu)就不創(chuàng)建了…)
create or replace trigger trig_insert after insert on account begin if inserting then insert into myevent values(1, 'after insert'); end if; end; /
以DBA 身份登錄數(shù)據(jù)庫,并創(chuàng)建一個名為db_log的數(shù)據(jù)表,用于記錄登錄用戶的用戶名和時間。
接著分別創(chuàng)建數(shù)據(jù)庫啟動和數(shù)據(jù)庫關(guān)閉觸發(fā)器,并向db_log數(shù)據(jù)表中插入記錄,存儲登錄用戶的用戶名和操作時間。
--創(chuàng)建表 create table db_log( name varchar2(20), rtime timestamp ); -- 創(chuàng)建觸發(fā)器,用于記錄用戶登錄 create or replace trigger trigger_startup after startup on database begin insert into db_log values('user', sysdate); end; / -- 創(chuàng)建觸發(fā)器,用于記錄用戶退出 create or replace trigger trigger_shutdown before shutdown on database begin insert into db_log values('xiuyan', sysdate); end; /
以下題目基于部門表和員工表:
-- 創(chuàng)建表 create table emp( empno number, ename varchar2(20), job varchar2(20), sal number, deptno number); create table dept( deptno number, dname varchar2(20), loc varchar2(20)); -- 插入數(shù)據(jù) insert into dept values(10,'account','new york'); insert into dept values(20,'salesman','chicago'); insert into dept values(30,'research','dallas'); insert into dept values(40,'operations','boston'); insert into emp values(1001,'mary','account',5000,10); insert into emp values(2001,'smith','salesman',6000,20); insert into emp values(3001,'kate','research',7000,30);
begin for emp_record in(select empno, ename, sal from emp where job='salesman') loop dbms_output.put('雇員編號:'||emp_record.empno); dbms_output.put('; 雇員名稱:'||emp_record.ename); dbms_output.put_line('; 雇員編號:'||emp_record.sal); end loop; end; /
把 emp 表中銷售員(即salesman)的工資上調(diào)20%,然后使用隱式游標(biāo) SQL 的 %ROWCOUNT 屬性輸出上調(diào)工資的員工數(shù)量。
begin update emp set sal=sal*(1+0.2) where job='salesman'; if sql%notfound then dbms_output.put_line('沒有雇員需要上調(diào)工資'); else dbms_output.put_line('有'|| sql%rowcount ||'個雇員需要上調(diào)工資'); end if; end; /
declare cursor cur_emp is select * from emp where deptno = 30; begin for emp_record in cur_emp loop dbms_output.put('雇員編號:'||emp_record.empno); dbms_output.put('; 雇員名稱:'||emp_record.ename); dbms_output.put_line('; 雇員職務(wù):'||emp_record.job); end loop; end; /
聲明一個游標(biāo),用于檢索指定員工編號的雇員信息,然后使用游標(biāo)的%FOUND屬性來判斷是否檢索到指定員工編號的雇員信息。
declare v_ename varchar2(50); v_job varchar2(50); cursor cur_emp is select ename, job from emp where empno = &empno; begin open cur_emp; fetch cur_emp into v_ename, v_job; if cur_emp%found then dbms_output.put('雇員編號:'||v_ename ||',職務(wù)是:'||v_job ); else dbms_output.put('無數(shù)據(jù)記錄'); end if; end; /
創(chuàng)建 fruit 表并插入數(shù)據(jù)
-- 創(chuàng)建水果表 create table fruit( f_id varchar2(10) not null, f_name varchar2(255) not null, f_price number (8,2) not null ); --插入數(shù)據(jù) insert into fruit values ('a1', 'apple',5.2); insert into fruit values ('b1','blackberry', 10.2); insert into fruit values ('bs1','orange', 11.2); insert into fruit values('bs2','melon',8.2); insert into fruit values ('t1','banana', 10.3); insert into fruit values ('t2','grape', 5.3); insert into fruit values ('o2','coconut', 9.2);
創(chuàng)建表fruitage,表fruitage和表fruit的字段一致,利用以下語句創(chuàng)建:
create table fruitage as select * from fruit where 2=3; -- 如果WHERE后面的條件為真,則復(fù)制表時把數(shù)據(jù)也一起復(fù)制。 -- 不加默認會復(fù)制數(shù)據(jù)。
創(chuàng)建游標(biāo),完成數(shù)據(jù)轉(zhuǎn)移,將fruit表中,單價大于10的記錄放到fruitage表中。
declare v_id fruit.f_id %TYPE; v_name fruit.f_name %TYPE; v_price fruit.f_price %TYPE; cursor frt_cur is select f_id, f_name, f_price from fruit where f_price>10; begin open frt_cur; loop fetch frt_cur into v_id, v_name, v_price; if frt_cur%found then insert into fruitage values(v_id, v_name, v_price); else dbms_output.put_line('已取出所有數(shù)據(jù),共有'||frt_cur%ROWCOUNT||'條記錄'); exit; end if; end loop; close frt_cur; end; /
以上是“Oracle觸發(fā)器和游標(biāo)的示例分析”這篇文章的所有內(nèi)容,感謝各位的閱讀!相信大家都有了一定的了解,希望分享的內(nèi)容對大家有所幫助,如果還想學(xué)習(xí)更多知識,歡迎關(guān)注億速云行業(yè)資訊頻道!
免責(zé)聲明:本站發(fā)布的內(nèi)容(圖片、視頻和文字)以原創(chuàng)、轉(zhuǎn)載和分享為主,文章觀點不代表本網(wǎng)站立場,如果涉及侵權(quán)請聯(lián)系站長郵箱:is@yisu.com進行舉報,并提供相關(guān)證據(jù),一經(jīng)查實,將立刻刪除涉嫌侵權(quán)內(nèi)容。