快速學習Oracle觸發器和遊標

Posted on by

觸發器：

1、 創建一個用於記錄用戶操作的觸發器

  創建一個dept_log數據表，並在其中定義兩個字段（operate_tag varchar2(10),operate_time date），分別用來存儲操作種類（插入，修改，刪除）信息和操作日期。然後一個關於dept表的語句級觸發器tri_dept，將用戶對 dept 表的操作信息保存到dept_tag表中。

當任何時候從dept表中刪除某個部門時，該觸發器將從emp表中刪除該部門的所有雇員。

-- 創建表
create table dept_log(
	operate_tag varchar2(10),
	operate_time date
);
create table dept(
	dname varchar2(20),
	dno number
);

-- 創建觸發器
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、創建一個當刪除部門時，刪除該部門下的所有雇員的觸發器

當任何時候從dept表中刪除某個部門時，該觸發器將從emp表中刪除該部門的所有雇員。

註意：所有的以sysdba登錄的賬戶都不能創建觸發器，因此需要在創建的用戶下面創建觸發器

-- 創建部門表
create table dept(
    deptno number not null,
    dname varchar(20) not null
);

-- 創建員工表
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
);

--插入數據
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);
-- 創建觸發器
create or replace trigger del_dept
before delete on dept
for each row
begin
	delete from emp where deptno = :old.deptno;
end;
/

在這裡插入圖片描述

3、創建一個在account表插入記錄之後，更新myevent數據表的觸發器

創建瞭一個TRIG_INSERT的觸發器，在向表account插入數據之後會向表myevent插入一組數據（表結構就不創建瞭…）

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、創建一個用於記錄登錄 DBA 身份用戶的用戶名和時間的觸發器

以DBA 身份登錄數據庫，並創建一個名為db_log的數據表，用於記錄登錄用戶的用戶名和時間。
接著分別創建數據庫啟動和數據庫關閉觸發器，並向db_log數據表中插入記錄，存儲登錄用戶的用戶名和操作時間。

--創建表
create table db_log(
	name varchar2(20),
	rtime timestamp
);

-- 創建觸發器，用於記錄用戶登錄
create or replace trigger trigger_startup
after startup
on database
begin
	insert into db_log values('user', sysdate);
end;
/

-- 創建觸發器，用於記錄用戶退出
create or replace trigger trigger_shutdown
before shutdown
on database
begin
	insert into db_log values('xiuyan', sysdate);
end;
/

遊標：

以下題目基於部門表和員工表：

-- 創建表
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));
	
-- 插入數據
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、使用隱式遊標和 for 語句檢索出職務是銷售員(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、員工工資上調20%，使用隱式遊標輸出上調工資的員工數量

把 emp 表中銷售員（即salesman）的工資上調20%，然後使用隱式遊標 SQL 的 %ROWCOUNT 屬性輸出上調工資的員工數量。

begin
	update emp set sal=sal*(1+0.2) where job='salesman';
	if sql%notfound then
		dbms_output.put_line('沒有雇員需要上調工資');
	else
		dbms_output.put_line('有'|| sql%rowcount ||'個雇員需要上調工資');
	end if;
end;
/

在這裡插入圖片描述

3、用顯示遊標和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('; 雇員職務：'||emp_record.job);
	end loop;
end;
/

在這裡插入圖片描述

4、聲明遊標檢索員工信息，並使用%FOUND屬性來判斷

聲明一個遊標，用於檢索指定員工編號的雇員信息，然後使用遊標的%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 ||',職務是：'||v_job );
	else
		dbms_output.put('無數據記錄');
	end if;
end;
/

在這裡插入圖片描述

5、創建遊標完成數據轉移，將fruit表中單價大於10的記錄放到fruitage表中

創建 fruit 表並插入數據

-- 創建水果表
create table fruit(
	f_id    varchar2(10)    not null,
	f_name  varchar2(255)  	not null,
	f_price  number (8,2)  	not null
);

--插入數據
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);

創建表fruitage，表fruitage和表fruit的字段一致，利用以下語句創建：

create table fruitage as select * from fruit where 2=3;
-- 如果WHERE後面的條件為真，則復制表時把數據也一起復制。
-- 不加默認會復制數據。

創建遊標，完成數據轉移，將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('已取出所有數據，共有'||frt_cur%ROWCOUNT||'條記錄');
			exit;
			end if;
	end loop;
	close frt_cur;
end;
/

在這裡插入圖片描述

到此這篇關於Oracle觸發器和遊標練習題的文章就介紹到這瞭,更多相關Oracle觸發器和遊標內容請搜索WalkonNet以前的文章或繼續瀏覽下面的相關文章希望大傢以後多多支持WalkonNet！

推薦閱讀：