快速學(xué)習(xí)Oracle觸發(fā)器和游標(biāo)
更新時(shí)間：2021年06月30日 11:59:34 作者：Xiu Yan
oracle觸發(fā)器和游標(biāo)是面試中常問到的知識點(diǎn)，今天就抽空通過實(shí)例代碼給大家介紹Oracle觸發(fā)器和游標(biāo)的示例代碼，需要的朋友參考下吧
觸發(fā)器：
1、 創(chuàng)建一個(gè)用于記錄用戶操作的觸發(fā)器
創(chuàng)建一個(gè)dept_log數(shù)據(jù)表，并在其中定義兩個(gè)字段（operate_tag varchar2(10),operate_time date），分別用來存儲操作種類（插入，修改，刪除）信息和操作日期。然后一個(gè)關(guān)于dept表的語句級觸發(fā)器tri_dept，將用戶對 dept 表的操作信息保存到dept_tag表中。
當(dāng)任何時(shí)候從dept表中刪除某個(gè)部門時(shí)，該觸發(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; /
2、創(chuàng)建一個(gè)當(dāng)刪除部門時(shí)，刪除該部門下的所有雇員的觸發(fā)器
當(dāng)任何時(shí)候從dept表中刪除某個(gè)部門時(shí)，該觸發(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; /
3、創(chuàng)建一個(gè)在account表插入記錄之后，更新myevent數(shù)據(jù)表的觸發(fā)器
創(chuàng)建了一個(gè)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; /
4、創(chuàng)建一個(gè)用于記錄登錄 DBA 身份用戶的用戶名和時(shí)間的觸發(fā)器
以DBA 身份登錄數(shù)據(jù)庫，并創(chuàng)建一個(gè)名為db_log的數(shù)據(jù)表，用于記錄登錄用戶的用戶名和時(shí)間。
接著分別創(chuàng)建數(shù)據(jù)庫啟動和數(shù)據(jù)庫關(guān)閉觸發(fā)器，并向db_log數(shù)據(jù)表中插入記錄，存儲登錄用戶的用戶名和操作時(shí)間。
--創(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;
/
游標(biāo)：
以下題目基于部門表和員工表：
-- 創(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);
1、使用隱式游標(biāo)和 for 語句檢索出職務(wù)是銷售員(salesman)的雇員信息并輸出
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;
/
2、員工工資上調(diào)20%，使用隱式游標(biāo)輸出上調(diào)工資的員工數(shù)量
把 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 ||'個(gè)雇員需要上調(diào)工資');
end if;
end;
/
3、用顯示游標(biāo)和for語句檢索出部門編號是30的雇員信息并輸出
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;
/
4、聲明游標(biāo)檢索員工信息，并使用%FOUND屬性來判斷
聲明一個(gè)游標(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;
/
5、創(chuàng)建游標(biāo)完成數(shù)據(jù)轉(zhuǎn)移，將fruit表中單價(jià)大于10的記錄放到fruitage表中
創(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í)把數(shù)據(jù)也一起復(fù)制。 -- 不加默認(rèn)會復(fù)制數(shù)據(jù)。
創(chuàng)建游標(biāo)，完成數(shù)據(jù)轉(zhuǎn)移，將fruit表中，單價(jià)大于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;
/
到此這篇關(guān)于Oracle觸發(fā)器和游標(biāo)練習(xí)題的文章就介紹到這了,更多相關(guān)Oracle觸發(fā)器和游標(biāo)內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
您可能感興趣的文章:
相關(guān)文章
本文主要介紹了如何用Mimer Validator檢查SQL查詢的技巧方法和步驟。2009-04-04
使用工具 plsqldev將Excel導(dǎo)入Oracle數(shù)據(jù)庫
這篇文章主要介紹了使用工具 plsqldev將Excel導(dǎo)入Oracle數(shù)據(jù)庫,需要的朋友可以參考下2014-08-08
Oracle數(shù)據(jù)庫自定義類型type的用法詳解
這篇文章主要介紹了Oracle數(shù)據(jù)庫自定義類型type的用法詳解,Oracle?數(shù)據(jù)庫的概念和其它數(shù)據(jù)庫不一樣，這里的數(shù)據(jù)庫是一個(gè)操作系統(tǒng)只有一個(gè)庫,可以看作是?Oracle?就只有一個(gè)大數(shù)據(jù)庫,需要的朋友可以參考下2023-07-07
And,Where使用提示以及用+進(jìn)行左關(guān)聯(lián)的提示及注意事項(xiàng)
先左關(guān)聯(lián)后在過濾假如關(guān)聯(lián)的結(jié)果里面B.b3=null那么你在where后面在加B.b3=2那么結(jié)果中B.b3肯定是沒有null的情況的,也就是說用+進(jìn)行左關(guān)聯(lián)沒有用leftjoin靈活待后續(xù)看是否有什么好的解決方案2013-02-02
Linux環(huán)境下Oracle數(shù)據(jù)庫重啟詳細(xì)步驟
這篇文章主要給大家介紹了關(guān)于Linux環(huán)境下Oracle數(shù)據(jù)庫重啟的詳細(xì)步驟,oracle在linux下重啟,有多種方式可選擇,文中通過代碼介紹的非常詳細(xì),需要的朋友可以參考下2024-06-06
Oracle數(shù)據(jù)庫密碼文件的使用與維護(hù)
Oracle數(shù)據(jù)庫密碼文件的使用與維護(hù)...2007-03-03
Oracle進(jìn)行數(shù)據(jù)庫升級和降級的操作代碼
數(shù)據(jù)庫升級是一個(gè)復(fù)雜的過程,涉及到備份現(xiàn)有數(shù)據(jù)、安裝新版本的數(shù)據(jù)庫軟件、遷移數(shù)據(jù)和應(yīng)用程序的兼容性測試等步驟,數(shù)據(jù)庫降級通常比升級更具挑戰(zhàn)性,所以本文給大家介紹了Oracle進(jìn)行數(shù)據(jù)庫升級和降級的操作,需要的朋友可以參考下2024-09-09
Oracle生成不重復(fù)票號與LPAD,RPAD與NEXTVAL函數(shù)解析
這篇文章主要介紹了Oracle生成不重復(fù)票號與LPAD,RPAD與NEXTVAL函數(shù)解析，小編覺得還是挺不錯(cuò)的，這里分享給大家，供需要的朋友參考。2017-10-10
這篇文章主要介紹了Oracle連接配置解讀，文中通過示例代碼介紹的非常詳細(xì)，對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2019-09-09
修改表結(jié)構(gòu)在某些時(shí)候還是需要用到的，下面為大家介紹下使用腳本來修改表結(jié)構(gòu)，感興趣的朋友不要錯(cuò)過2013-12-12