Oracle就业课第六课之游标和触发器
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\libmaven中注冊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就业课第六课之游标和触发器的全部內容,希望文章能夠幫你解決所遇到的問題。
- 上一篇: Oracle复习课
- 下一篇: Asp.Net就业课之三验证控件