oracle 游標和觸發(fā)器

回顧

表空間

表空間：邏輯名詞：表、視圖、索引；拆分：段、區(qū) 、塊（最小的存儲單元）

物理文件的構成：

? 1、tcl文件

? 2、log文件

? 3、dbf文件（數(shù)據(jù)文件）

一個或者多個表空間----------->一個物理文件（dbf文件）

1、創(chuàng)建表空間和用戶

1、創(chuàng)建表空間（切換成管理員）

2、創(chuàng)建用戶 關聯(lián)表空間

3、授權

示例：

--表空間: 數(shù)據(jù)表空間 create tablespace tbs_331 datafile 'd:/oracledata/tbs_331.dbf' --數(shù)據(jù)文件 size 50m; --初始大小--臨時表空間 create temporary tablespace tbs_331_tmp tempfile 'd:/oracledata/tbs_331_tmp.dbf' size 20m;--創(chuàng)建用戶 create user u331 identified by 123456 default tablespace tbs_331 --默認表空間 temporary tablespace tbs_331_tmp; --默認臨時表空間--授權 --內置的角色： --connect: 連接，能夠登陸系統(tǒng)，具備最基本的權限；游客 --resource: 資源；查看數(shù)據(jù)庫對象，增刪改查數(shù)據(jù)；正式的用戶使用 --dba:管理員 grant connect,resource to u331;

2、導出（備份）和導入（還原）

2.1 需要準備虛擬目錄：

--創(chuàng)建一個虛擬目錄:映射到指定的物理路徑上 create directory dir331 as 'd:/oracledata';--授予u331用戶操作dir331的權限：read,write grant read,write on directory dir331 to u331;

2.2 導出：expdp

(數(shù)據(jù)泵程序)

注意：在cmd窗口執(zhí)行

expdp u331/123456 schemas=u331 dumpfile=u331.dmp logfile=u331.log directory=dir331

• u331/123456：用戶名和密碼
• schemas=u331:模式，默認情況下模式=用戶；
• dumpfile=u331.dmp：導出的文件名
• logfile=u331.log:日志文件
• directory=dir331：虛擬目錄：dir331；默認導出的數(shù)據(jù)文件都在虛擬目錄對應的物理路徑中；

導入：impdp

impdp u331/123456 schemas=u331 dumpfile=u331.dmp logfile=u331.log directory=dir331

實驗：

1、使用新創(chuàng)建的用戶登錄創(chuàng)建好的表空間，創(chuàng)建一個數(shù)據(jù)表并錄入一些數(shù)據(jù)供測試

2、導出

3、刪除表

4、再次導入，查看數(shù)據(jù)是否還原

3、刪除用戶和表空間：

-- 刪除用戶 drop user u331 cascade; --級聯(lián)刪除--刪除表空間 （包括內容和數(shù)據(jù)文件） drop tablespace tbs_331 including contents and datafiles; drop tablespace tbs_331_tmp including contents and datafiles;

如果忘掉管理員密碼：

sqlplus scott/tigerSQL*Plus: Release 11.1.0.6.0 - Production on 星期一 9月 21 15:30:44 2020Copyright (c) 1982, 2007, Oracle. All rights reserved.連接到: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing optionsSQL> conn / as sysdba; 已連接。 SQL> alter user sys identified by sys;

學習目標

1、游標

2、觸發(fā)器

學習內容

1、游標

游標：cursor ;指針；

以往對數(shù)據(jù)表中數(shù)據(jù)進行的各種數(shù)據(jù)操作，本質上都是對列進行操作的。無法對每一行數(shù)據(jù)進行處理。

游標：將查詢到的結果暫時的存放到上下文的空間中（內存區(qū)），可以對數(shù)據(jù)進行逐行定位操作。

游標的分類：

1、顯式游標

? 有名字，定義游標，打開游標…

2、隱式游標

? DML命令（insert,update,delete) 和select … into 命令

游標的屬性

用法：顯式游標名%屬性名 或者：SQL%屬性名

? %NOTFOUND:如果沒有讀取到數(shù)據(jù)，返回true。否則返回false

? %ROWCOUNT:實際讀取到的行數(shù)

? %ISOPEN:游標是否打開，true/false

? %FOUND:游標是否讀取到數(shù)據(jù) ；讀取到true;否則：false

顯式游標

? 1、聲明游標

? 2、打開游標

? 3、提取數(shù)據(jù)

? 4、關閉游標

declare--聲明游標cursor c1 is 查詢命令; begin--打開游標open c1;--提取數(shù)據(jù)fetch c1 into 變量名;--關閉游標close c1;end;

普通顯式游標

示例：

declare--聲明游標cursor c1 is select ename,sal from emp where deptno=10;--臨時變量vname emp.ename%type;vsal emp.sal%type; begin--打開游標open c1;--逐行提取數(shù)據(jù)loopfetch c1 into vname,vsal;-- 實際的業(yè)務...if vsal>3000 and vsal<=10000 thendbms_output.put_line('調薪:2000');elsedbms_output.put_line('調薪:1000');end if;exit when c1%notfound;end loop;--關閉游標close c1;end;

帶變量的游標

declare--聲明游標cursor c1(vno number) is select ename,sal from emp where deptno=vno;--臨時變量vname emp.ename%type;vsal emp.sal%type; begin--打開游標open c1(10);--逐行提取數(shù)據(jù)loopfetch c1 into vname,vsal;dbms_output.put_line(vname||':工資:'||vsal);-- 實際的業(yè)務...if vsal>3000 and vsal<=10000 thendbms_output.put_line('調薪:2000');elsedbms_output.put_line('調薪:1000');end if;exit when c1%notfound;end loop;--關閉游標close c1;end;

使用for循環(huán)簡化游標

--emp_row:行數(shù)據(jù) beginfor emp_row in (select ename,sal from emp where deptno=10) loopdbms_output.put_line(emp_row.ename||emp_row.sal);end loop;end;

游標類型的變量

? 說明：將游標當作數(shù)據(jù)類型，來聲明變量；比如：存儲過程，函數(shù)，將查詢的多條結果存入到游標中，返回供別的過程使用；

declare--游標類型type cur_type is ref cursor;--使用該游標類型 聲明一個變量cur1 cur_type;--行類型的變量用來存儲每次讀取到的數(shù)據(jù)emp_row emp%rowtype;beginopen cur1 for select * from emp; loopfetch cur1 into emp_row;dbms_output.put_line(emp_row.ename||emp_row.sal);exit when cur1%notfound;end loop;close cur1; end;

隱式游標

begindelete from emp where empno=621;if SQL%NOTFOUND thendbms_output.put_line('數(shù)據(jù)不存在');elsedbms_output.put_line('刪除成功'); end if; end;

游標測試

create or replace procedure test_cur5 as--聲明變量cursor c1 is select deptno,count(*) from emp group by deptno;vno number(3);vcount number(5); beginopen c1;loopfetch c1 into vno,vcount;dbms_output.put_line('部門：'||vno||'人數(shù)：'||vcount);exit when c1%notfound;end loop;close c1; end;

測試：

begintest_cur5; end;

2、觸發(fā)器

觸發(fā)器：

? 隱式（自動）執(zhí)行的存儲過程；當執(zhí)行：DDL（create,alter,drop)操作，DML(insert,update,delete)操作時，一系列的系統(tǒng)事件(實例加載，登錄、登出）時會激活相應類型的觸發(fā)器；

觸發(fā)器分類：

1、DDL觸發(fā)器

2、DML觸發(fā)器

3、系統(tǒng)觸發(fā)器

DML觸發(fā)器

創(chuàng)建觸發(fā)器：

create or replace trigger 名字 --trigger 觸發(fā)器 before or after or instead of --執(zhí)行時間 before：之前 after:之后 instead of :替代（替換） insert or update or delete --激活觸發(fā)器的操作（動作） on 表名 或者 視圖名 --觸發(fā)器操控的數(shù)據(jù)庫對象 for each row -- 行級觸發(fā)器 ：數(shù)據(jù)操作影響的每一行數(shù)據(jù)都會激活觸發(fā)器 begin--命令 如果出現(xiàn)錯誤：rasie_application_error(); --觸發(fā)器執(zhí)行過程中一旦發(fā)生錯誤，會導致原先的數(shù)據(jù)操作：一并回滾end; -- 觸發(fā)器中規(guī)定，最多不超過：32k的代碼；如果代碼過多可以封裝成過程，在觸發(fā)器中調用

例子：

1、不允許刪除員工的數(shù)據(jù)

create or replace trigger tg_test1 before delete on emp beginraise_application_error(-20001,'不允許刪除員工表的數(shù)據(jù)'); end;

測試：

delete from emp where empno=66;

觸發(fā)器中的內置的對象：

邏輯表：

• :old 舊表：用來存放刪除的數(shù)據(jù)；delete和update時會用到
• :new 新表：用來存放新的數(shù)據(jù)；insert和update時會用到

[外鏈圖片轉存失敗,源站可能有防盜鏈機制,建議將圖片保存下來直接上傳(img-oSAyVFAF-1601737304231)(6、Oracle游標和觸發(fā)器.assets/image-20200923151129100.png)]

1、不允許降薪

create or replace trigger tg_test2before update on empfor each row --行級begin-- 獲取原來的薪資 ，獲取修改后的薪資if :old.sal>:new.sal thenraise_application_error(-20002,'不允許降薪操作');end if;end;

觸發(fā)器中的：3個條件謂詞：

• ? updating: 判斷是否是修改操作
• ? deleting: 判斷是否是刪除操作
• ? inserting: 判斷是否是錄入操作

? 用來判斷當前是那種數(shù)據(jù)操作激活了觸發(fā)器；直接當作條件使用；

create or replace trigger tg_test3after insert or update or deleteon empbeginif updating thendbms_output.put_line('修改操作激活了觸發(fā)器');elsif inserting thendbms_output.put_line('錄入操作激活了觸發(fā)器');elsif deleting thendbms_output.put_line('刪除操作激活了觸發(fā)器');end if;end;

準備一個員工表相同結構的的歷史記錄表；

-- 復制表結構 create table emp_his as select * from emp where 1=2; --復制表 create table emp_his2 as select * from emp;

例子：刪除員工數(shù)據(jù)時，將刪除的數(shù)據(jù)插入到歷史表中

create or replace trigger tg_test4 before delete on emp for each row begininsert into emp_his(empno,ename,job,sal) values(:old.empno,:old.ename,:old.job,:old.sal); end;

替代觸發(fā)器只能作用于視圖上面：

什么是視圖（view):虛擬表，本質上是一個查詢命令；

1、授予該用戶創(chuàng)建視圖的權限：

grant create view to scott;

2、創(chuàng)建視圖

? 將一個復雜的查詢命令存儲起來，以供重復使用；

創(chuàng)建視圖 create view v_selectemp as select dt.deptno,dt.dname,count(e.empno) vcount from dept dt,emp e where dt.deptno=e.deptno group by dt.deptno,dt.dname; --測試 select * from v_selectemp where vcount>6 order by vcount ;

視圖的作用：簡化查詢操作，不允許：insert,update,delete

例子：

-- 測試錄入 insert into v_selectemp(deptno,dname,vcount) values(30,'帥哥部',30);create or replace trigger tg_test5 instead of insert on v_selectemp for each row --替換錄入操作 begindelete from emp where deptno=:new.deptno; --換成了刪除操作 end;

應用場景：

1、限制數(shù)據(jù)的修改規(guī)則

2、實現(xiàn)自動記錄歷史記錄

3、模擬自增列

create or replace trigger tg_test6 before insert on emp for each row beginselect 序列.nextval into :new.empno from dual; end;

Java中jdbc調用oracle存儲過程：

獲取驅動包：

D:\app\MrLang\product\11.1.0\db_1\jdbc\lib

maven中注冊jar包:

mvn install:install-file -DgroupId=com.oracle -DartifactId=ojdbc6 -Dversion=11.1.0.6.0 -Dpackaging=jar -Dfile=驅動包路徑

引入依賴項：

<dependencies><dependency><groupId>com.oracle</groupId><artifactId>ojdbc6</artifactId><version>11.1.0.6.0</version></dependency> </dependencies>

測試調用過程：

public class Test1 {static final String DRIVER = "oracle.jdbc.driver.OracleDriver";static final String URL = "jdbc:oracle:thin:@localhost:1521:orcl";static final String USERNAME = "scott";static final String PWD = "tiger";public static void main(String[] args) {Connection connection=null;//操作過程CallableStatement statement=null;try {Class.forName(DRIVER);connection = DriverManager.getConnection(URL, USERNAME, PWD); // 與編譯命令：指定要調用的過程，statement=connection.prepareCall("{call sp_zhuanzhang3(?,?,?,?)}"); // 綁定參數(shù) // 輸入?yún)?shù)：傳參statement.setInt(1,2);statement.setInt(2,1000); // 輸出參數(shù)：只需要指定對應的數(shù)據(jù)類型;注冊輸出參數(shù)statement.registerOutParameter(3,OracleTypes.NUMBER);statement.registerOutParameter(4,OracleTypes.NUMBER); // 執(zhí)行命令statement.execute(); // 執(zhí)行后：可以獲取到輸出參數(shù)的值double b1= statement.getDouble(3);double b2=statement.getDouble(4);System.out.println("轉賬后的余額:"+b1+":"+b2);} catch (ClassNotFoundException e) {e.printStackTrace();} catch (SQLException e) {e.printStackTrace();}finally {if(statement!=null) {try {statement.close();} catch (SQLException e) {e.printStackTrace();}}if(connection!=null){try {connection.close();} catch (SQLException e) {e.printStackTrace();}}}} }

總結

tStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}finally {
if(statement!=null) {
try {
statement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(connection!=null){
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
}

# 總結

總結

以上是生活随笔為你收集整理的Oracle就业课第六课之游标和触发器的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內容還不錯，歡迎將生活随笔推薦給好友。