ORACLE深入淺出-中級篇ppt課件

1、ORACL深化淺出主講人：鄒振興.第一章 事務與鎖什么是事務？如何Oracle運用事務。在Java中運用事務。什么是鎖？演示更新喪失的問題。鎖的分類。阻塞與死鎖。.什么是事務什么是事務？事務是一種機制，這種機制能保證在事務內的DML(數據操作語句)做為一個不可分割的單元進展執(zhí)行。舉例闡明事務。演示sql servert2005事務處置。演示Oracle事務處置。事務四大特性(ACID)原子性(A) 一致性(C)隔離性(I) 永久性(D).什么是事務事務處置的兩種方式顯示事務：用戶經過編碼方式開啟事務隱式事務：數據庫默許以為一切操作都是事務比較sql server 2005與Oracle事務處置

2、方式的區(qū)別。.如何Oracle運用事務Oracel事務關鍵字Savepoint a ; 設置事務保管點Rollback to a ;回滾到事務保管點a,事務不終了Commit ;提交事務，事務終了Rollback; 回滾一切事務，事務終了講解Oracel程序員一天.在Java Jdbc中運用事務演示如何在Java Jdbc中如何運用事務驅動包位置：oracle安裝途徑-oracle-jdbc-lib-classes12.jar演示案例：DBUtil.java.什么是鎖生活中的鎖：為什么會有鎖的出現。鎖的作用就是維護私有的空間。Oracle中的鎖：演示Oracle中更新喪失的問題。講解此問題的兩

3、種思緒：一、在第一個用戶修正之前不允許其它用戶修正。二、在修正之前，查看此數據的版本號。.鎖的分類悲觀鎖定此鎖持悲觀態(tài)度，以為在用戶修正數據期間，一定會有用戶再對其修正。那么此鎖會在用戶事務內不斷堅持鎖定形狀，其它用戶無法進展修正。在查詢語句后接 for update nowait此可加鎖。查詢多少數據，鎖定多少數據。樂觀鎖定此鎖持樂觀態(tài)度，以為在用戶修正數據期間，其它用戶不會再對其修正，直到最后提交數據時才判別數據能否已更改。.鎖的分類樂觀鎖定的實現方式：添加標識列來區(qū)分能否已修正經過Oracle ORA_ROWSCN機制來實現樂觀鎖定.阻塞與死鎖假設他有兩個會話，每個會話都持有另一個會話想

4、要的資源，此時就會出現死鎖deadlock。例如，假設我的數據庫中有兩個表A 和B，每個表中都只需一行，就可以很容易地展現什么是死鎖。我要做的只是翻開兩個會話例如，兩個SQL*Plus 會話。在會話A 中更新表A，并在會話B 中更新表B。如今，假設我想在會話B 中更新表A，就會阻塞。會話A 曾經鎖定了這一行。這不是死鎖；只是阻塞而已。我還沒有遇到過死鎖，由于會話A 還有時機提交或回滾，這樣會話B 就能繼續(xù)。.阻塞與死鎖假設我再回到會話A，試圖更新表B，這就會導致一個死鎖。要在這兩個會話中選擇一個作為“犧牲品，讓它的語句回滾。例如，會話B 中對表A 的更新能夠回滾，得到以下錯誤：想要更新表B 的

5、會話A 還阻塞著，Oracle 不會回滾整個事務。只會回滾與死鎖有關的某條語句。會話B 依然鎖定著表B 中的行，而會話A 還在耐心地等待這一行可用。收到死鎖音訊后，會話B 必需決議將表B 上未執(zhí)行的任務提交還是回滾，或者繼續(xù)走另一條路，以后再提交。一旦這個會話執(zhí)行提交或回滾，另一個阻塞的會話就會繼續(xù)，好似什么也沒有發(fā)生過一樣。.總結開發(fā)多用戶、數據庫驅動的運用時，最大的難點之一是：一方面要力爭獲得最大限制的并發(fā)訪問，與此同時還要確保每個用戶能以一致的方式讀取和修正數據。為此就有了鎖定locking機制，這也是一切數據庫都具有的一個關鍵特性，Oracle 在這方面更是技高一籌。不過，Oracle

6、 的這些特性的實現是Oracle所特有的，就像SQL Server 的實現只是SQL Server 特有的一樣，運用執(zhí)行數據處置時，要正確地運用這些機制，而這一點要由他運用的開發(fā)人員來保證。.第五章 運用PL/SQLPL/SQL簡介變量、常量數據類型流程控制異常處置.PL/SQL簡介PL/SQL是Oracle在sql規(guī)范上的加強，運用Oracle可以基于對象編程T-SQL是基于事務的構造化查詢言語兩者都是對SQL的擴展.PL/SQL塊簡介PL/SQL 塊是構成 PL/SQL 程序的根本組織單元，可以編寫存儲過程、函數、包、觸發(fā)器等。PL/SQL 分為三個部分：聲明部分、可執(zhí)行部分和異常處置部分

7、.PL/SQL塊簡介演示：PL/SQL打印Hello World。演示：如何在PL/SQL塊中向表中添加一條記錄。演示：如何在PL/SQL塊中修正表中數據。演示：查詢學員張秋麗的年齡。.PL/SQL塊 小結每一個PL/SQL塊將會自動開啟一個事務，在PL/SQL塊中必需終了事務，否那么，將會阻塞。PL/SQL塊每條語句后，必需帶上;，表示語句終了。PL/SQL塊中不能執(zhí)行select語句進展顯示。在命令行中執(zhí)行pl-sql塊需加/。在命令行中打印變量需set serverout on;.變量與常量在PL/SQL塊的聲明部分聲明變量或常量語法：declare sal number;pi cons

8、tant number := 3.14;賦值1、運用賦值運算符“:=2、運用 SELECT INTO 語句 3、接納用戶輸入.變量與常量例如Declaremyname varchar2(20) not null :=張三;myage number ;mypi constant number :=3.14;Beginmyage:=26;select ename into myname from emp whereempno=7369;-輸出End;.日期時間日期時間類型存儲日期和時間數據常用的兩種日期時間類型DATETIMESTAMPdeclare mydate date; mytime tim

9、estamp; begin select sysdate into mydate from dual; select sysdate into mytime from dual; dbms_output.put_line(mydate); dbms_output.put_line(mytime); end;.布爾類型boolean用于存儲邏輯值(TRUE、FALSE和NULL)不能向數據庫中插入BOOLEAN數據不能將列值保管到BOOLEAN變量中只能對BOOLEAN變量執(zhí)行邏輯操作.屬性類型用于援用數據庫列的數據類型，以及表示表中一行的記錄類型列類型%type，語法：表名.列名%type行類

10、型%rowtype，語法：表名%rowtype優(yōu)點不需求知道被援用的表列的詳細類型假設被援用對象的數據類型發(fā)生改動，PL/SQL 變量的數據類型也隨之改動.屬性類型例如問題1：假設如今不知道emp表Job列的類型，但要保管員工7369的崗位到變量中，如何定義？ declare myjob emp.Job%type;begin select Job into myjob from emp where empno=7369; dbms_output.put_line(myjob);end;.屬性類型例如問題2：假設要輸出emp表員工7369的一切信息，怎樣辦？定義8個列類型變量，依次輸出？decl

11、are myemp emp%rowtype;begin select * into myemp from emp where empno=7369; dbms_output.put_line(myemp.ename|myemp.job);end;.異常處置在運轉程序時出現的錯誤叫做異常發(fā)生異常后，語句將停頓執(zhí)行，控制權轉移到 PL/SQL 塊的異常處置部分異常有兩種類型：預定義異常 - 當 PL/SQL 程序違反 Oracle 規(guī)那么或超越系統(tǒng)限制時隱式引發(fā)用戶定義異常 - 用戶可以在 PL/SQL 塊的聲明部分定義異常，自定義的異常經過 RAISE 語句顯式引發(fā).預定義異常Others 處置

12、一切異常在用戶試圖將反復的值存儲在運用獨一索引的數據庫列中時出現DUP_VAL_ON_INDEX在將字符串轉換為數字時出現INVALID_NUMBER在表中不存在懇求的行時出現NO_DATA_FOUND在執(zhí)行SELECT INTO語句后前往多行時出現TOO_MANY_ROWS在以零作為除數時出現ZERO_DIVIDE.預定義異常declare myname emp.ename%type;begin select ename into myname from emp; Exception when too_many_rows then dbms_output.put_line(值過多);end;

13、.預定義異常declare myname emp.ename%type;begin select ename into myname from emp where empno=1; Exception when no_data_found then dbms_output.put_line(沒有數據);end;.用戶自定義異常 declare myException Exception; begin if . then raise myException; end if; Exception when myException then . end;.流程控制條件構造If語句Case語句循環(huán)構造

14、Loop循環(huán)While循環(huán)For循環(huán).條件構造1) if.then.end if;2) if.then.else if.end if .end if; 嵌套if3) if.then.elsif.then.else.end if; 多重if4)case.when.end case;編寫一個PL/SQL塊，用戶輸入一個員工名，假設該員工的工資低于2000，那么將該員工工資增長10%。.接納用戶輸入的兩個數，經過條件構造判別兩個數的關系declare num1 number; num2 number; begin num1:=&請輸入一個數; num2:=&num2;-接納用戶輸入&name if

15、num1num2 then dbms_output.put_line(大于); else dbms_output.put_line(等于); end if; end;.declare grade varchar2(10); begin grade:=&grade; case grade when A then dbms_output.put_line(優(yōu)秀); when B then dbms_output.put_line(良好); when C then dbms_output.put_line(普通); when D then dbms_output.put_line(差); else

16、dbms_output.put_line(沒這個); end case; end;.循環(huán)構造運用三種循環(huán)實現輸出100次我愛他.1)無條件無限循環(huán) -跳出循環(huán) exit,exit when. declare counter number:=0; begin loop counter:=counter+1; dbms_output.put_line(counter|：我愛他); -if counter=100 then - exit;-退出循環(huán) -end if; exit when counter=100; end loop; end;.2)有條件循環(huán) declare counter numbe

17、r:=0; begin while(counter100) loop counter:=counter+1; dbms_output.put_line(counter|:我愛他); end loop; end;.3)循環(huán)固定次數:數字區(qū)間：下限.上限 begin for num in 1.100 loop dbms_output.put_line(num|:我愛他); end loop; end;.動態(tài)SQL運用：1、DDL 語句命令和會話控制語句不能在 PL/SQL 中直接運用，但是可以經過動態(tài) SQL 來執(zhí)行2、在運轉時，根據需求動態(tài)構建SQL語句執(zhí)行.動態(tài)SQL動態(tài) SQL 是指在PL/

18、SQL程序執(zhí)行時生成的 SQL 語句編譯程序對動態(tài) SQL 不做處置，而是在程序運轉時動態(tài)構造語句、對語句進展語法分析并執(zhí)行執(zhí)行語法：execute immediate sql語句into 變量列表 -將查詢的值賦值給變量using 參數列表 -運用參數填補占位符:name.Eg：利用動態(tài)SQL建表begin execute immediate create table test ( tid number);end;問題：能否建表后執(zhí)行插入數據操作？.Eg：根據員工編號獲得員工姓名declare myname emp.ename%type;begin execute immediate sel

19、ect ename from emp where empno=:no into myname using &no; dbms_output.put_line(myname);end;問題：假設Sql語句有多個條件怎樣寫？.總結1、:=賦值運算符2、|銜接操作符3、&name接受用戶輸入參數值4、:name 動態(tài)SQL中占位符，運用using填補.第六章 游標管理什么是游標顯示游標的運用游標類型的運用隱式游標的運用.什么是游標提問：請編寫pl-sql塊，打印出一切的參與考試的學員信息。分析問題：pl-sql塊中無法運用select語句，運用變量也無法保管多行數據。處理問題：要是Oracle提供類

20、似于Java集合的構培育能輕松處理問題。.顯示游標1顯式游標在 PL/SQL 塊的聲明部分定義查詢，該查詢可以前往多行顯式游標的操作過程：數據庫翻開游標30George344Roger245James1Stud_mrksStud_namestud_no提取行變量 封鎖游標 .顯示游標2定義：CURSOR IS FOR UPDATE | FOR UPDATE OF 字段 ;操作：OPEN ; -翻開游標FETCH INTO 變量1,變量2,變量3,.變量n;或者FETCH INTO 行對象; -取出游標當前位置的值 CLOSE ; -封鎖游標.顯示游標屬性%NOTFOUND -假設FETCH語句

21、失敗，那么該屬性為TRUE，否那么為FALSE;%FOUND -假設FETCH語句勝利，那么該屬性為TRUE，否那么為FALSE;%ROWCOUNT -前往游標當前的行數;%ISOPEN -假設游標是開的那么前往TRUE，否那么為FALSE;.輸出一切員工編號與姓名declare rowemp emp%rowtype;-行變量 cursor empcur is select * from emp;-定義游標begin open empcur;-翻開游標 loop fetch empcur into rowemp; dbms_output.put_line(rowemp.empno|rowemp

22、.ename); exit when empcur%notfound; end loop; close empcur;-封鎖游標end;.For循周游標循周游標用于簡化游標處置代碼自動翻開游標，提取記錄，封鎖游標當用戶需求從游標中提取一切記錄時運用循周游標的語法如下：FOR IN LOOPEND LOOP;是類型為%rowtype的記錄變量運用游標for循環(huán)的時候不能運用open語句，fetch語句和close語句，否那么會產生錯誤。 .輸出一切員工編號與姓名declare cursor empcur is select * from emp;-定義游標begin for rowemp in

23、empcur loop dbms_output.put_line(rowemp.empno|rowemp.ename); end loop;end;.帶參數的游標提高游標的靈敏性語法：CURSOR (參數列表) IS ;.根據部門編號輸出該部門員工姓名declare cursor empcur(dtno emp.deptno%type) is select * from emp where deptno=dtno;-定義游標beginfor rowemp in empcur(20) loopdbms_output.put_line(rowemp.ename);end loop;end;.游標加

24、鎖FOR UPDATE | FOR UPDATE OF 字段 即在程中有“UPDATE,“INSERT,“DELETE語句對數據庫操作時，游標自動給指定的表或者字段加鎖，防止同時有別的程序對指定的表或字段進展“UPDATE,“INSERT,“DELETE操作。在運用“DELETE,“UPDATE后還可以在程序中運用CURRENT OF 子句援用當前行進展更新操作。.運用游標更新工資，翻倍declarecursor mycursor is select sal from emp for update;begin for r in mycursor loopupdate emp set sal=s

25、al*2 where current of mycursor; end loop;end; .REF游標與游標變量REF 游標和游標變量用于處置運轉時動態(tài)執(zhí)行的 SQL 查詢創(chuàng)建游標變量需求兩個步驟：聲明 REF 游標類型聲明 REF 游標類型的變量用于聲明 REF 游標類型的語法為：TYPE IS REF CURSORRETURN ;翻開游標變量的語法如下： OPEN cursor_name FOR select_statement;.獲取部門10的工資declare Type salcursor is ref cursor;-聲明游標類型 salcurvar salcursor;-定義游標

26、變量 mysal number;begin open salcurvar for select sal from myemp where deptno=10; loop fetch salcurvar into mysal; dbms_output.put_line(mysal); exit when salcurvar%notfound; end loop;end;.游標變量優(yōu)點游標變量的功能強大，可以簡化數據處置。游標變量的優(yōu)點有：可從不同的 SELECT 語句中提取結果集可以作為過程的參數進展傳送可以援用游標的一切屬性可以進展賦值運算.運用游標變量執(zhí)行動態(tài)Sqldeclare Type

27、salcursor is ref cursor;-聲明游標類型 salcurvar salcursor;-定義游標變量 mysal number;begin open salcurvar for select sal from myemp where deptno=:dno-動態(tài)SQL字符串 using &no;-填補占位符的參數 loop fetch salcurvar into mysal; dbms_output.put_line(mysal); exit when salcurvar%notfound; end loop;end;.隱式游標在PL/SQL中運用DML語句時自動創(chuàng)建隱式游

28、標隱式游標自動聲明、翻開和封鎖，其游標稱號為 SQL經過檢查隱式游標的屬性可以獲得最近執(zhí)行的DML 語句的信息.隱式游標屬性%NOTFOUND -假設DML語句沒有影響到任何行時，那么該屬性為TRUE，否那么為FALSE;%FOUND -假設DML語句影響到一行或多行時，那么該屬性為TRUE，否那么為FALSE;%ROWCOUNT -前往DML語句影響的行數;%ISOPEN - 游標能否翻開，隱式游標一直為FALSE 運用：游標稱號+屬性SQL%NOTFOUND.隱式游標屬性beginupdate emp set comm=100 where empno=7369;if SQL%Found=t

29、rue then dbms_output.put_line(更新勝利); dbms_output.put_line(受影響的行數|SQl%rowcount);end if;If SQL%NotFound=true then dbms_output.put_line(不存在);end if;end;.總結游標用于處置查詢結果集中的數據游標類型有：隱式游標、顯式游標和 REF 游標隱式游標由 PL/SQL 自動定義、翻開和封鎖顯式游標用于處置前往多行的查詢在聲明 REF 游標時，不需求將 SELECT 語句與 其關聯.第七章 子程序與程序包什么是子程序子程序一：存儲過程子程序二：函數子程序三：自主

30、事務處置組織子程序：程序包.子程序1命名的 PL/SQL 塊，編譯并存儲在數據庫中。子程序的各個部分：聲明部分可執(zhí)行部分異常處置部分(可選)子程序的分類：過程 執(zhí)行某些操作函數 執(zhí)行操作并前往值.子程序2子程序的優(yōu)點：模塊化將程序分解為邏輯模塊可重用性可以被恣意數目的程序調用可維護性簡化維護操作平安性經過設置權限，使數據更平安.子程序一：存儲過程過程是用于完成特定義務的子程序 例如：前往售票廳訊問關于車票的信息 排隊等候 在柜臺購買車票 .子程序一：存儲過程創(chuàng)建簡單過程create or replace procedure SayHi as begin dbms_output.put_line

31、(hi); end;.子程序一：存儲過程調用 -1、在命令窗口下 execute SayHi; -2、在SQL Window下 begin SayHi(); end;.子程序一：存儲過程帶參數過程,參數不用指定長度 create or replace procedure SayHi(str varchar2) as begin dbms_output.put_line(str); end;.子程序一：存儲過程根據員工編號查詢員工姓名，并打印，沒找到數據那么異常處置 create or replace procedure SearchName(emp_no number) is empname

32、varchar(20); begin select ename into empname from emp where empno=emp_no; dbms_output.put_line(empname); end;.子程序一：存儲過程過程參數的三種方式：IN用于接受調用程序的值默許的參數方式OUT用于向調用程序前往值 IN OUT用于接受調用程序的值，并向調用程序前往更新的值.子程序一：存儲過程根據員工編號前往員工姓名給另一程序 create or replace procedure SearchName(emp_no in number,empname out varchar2) is

33、begin select ename into empname from emp where empno=emp_no; end;.子程序一：存儲過程declare emp_no number; empname varchar2(20); begin emp_no:=&no; SearchName(emp_no,empname); dbms_output.put_line(empname); end;.子程序一：存儲過程交換兩個數 create or replace procedure Swap(num1 in out number,num2 in out number) is temp nu

34、mber; begin temp:=num1; num1:=num2; num2:=temp; end;.子程序一：存儲過程declare num1 number:=10; num2 number:=20; begin dbms_output.put_line(num1=|num1); dbms_output.put_line(num2=|num2); Swap(num1,num2); dbms_output.put_line(調用過程后); dbms_output.put_line(num1=|num1); dbms_output.put_line(num2=|num2); end;.子程序

35、一：存儲過程-前往游標的存儲過程create or replace procedure getAllEmp(returnCursor out sys_refcursor) asbegin open returnCursor for select * from emp;end;-運用存儲過程前往的游標declare type classcursor is ref cursor; mycursor classcursor; var_emp emp%rowtype;begin getAllEmp(returnCursor = mycursor); loop fetch mycursor into v

36、ar_emp; dbms_output.put_line(var_emp.ename); exit when mycursor%notfound; end loop;end;.子程序一：存儲過程創(chuàng)建過程的語法：create or replace procedure 過程稱號(參數列表) is|as 變量 begin 可執(zhí)行部分; Exception end; .子程序一：存儲過程運用Java程序調用幾種存儲過程。Test.java.子程序二：函數(function)函數是可以前往值的命名的 PL/SQL 子程序。參數與前往值類型不用指定長度.子程序二：函數簡單函數create or repla

37、ce function FSayHi return varchar2 is begin return Hi; end;.子程序二：函數調用函數1、結合查詢 select FSayHi from dual; 2、前往值給變量 declare str varchar2(20); begin str:=FSayHi; dbms_output.put_line(str); end;.子程序二：函數帶參函數create or replace function FSayHi(str varchar2) return varchar2 is begin return str; end; select FSa

38、yHi(大家好) from dual;.子程序二：函數-經過員工編號前往員工姓名 create or replace function FSearchName(emp_no number) return varchar2 is empname varchar2(20); begin select ename into empname from emp where empno=emp_no; return empname; end; select FSearchName(7369) from dual;.子程序二：函數創(chuàng)建函數語法 create or replace function 函數稱號(

39、參數列表) return is | as begin 可執(zhí)行部分; Exception end; .子程序二：函數刪除過程與函數drop procedure 過程名;drop function 函數名;授予其他用戶執(zhí)行子程序的權限grant execute on 子程序名 to 用戶名;.過程與函數比較過 程函 數作為 PL/SQL 語句執(zhí)行作為表達式的一部分調用在規(guī)格說明中不包含 RETURN 子句必須在規(guī)格說明中包含 RETURN 子句不返回任何值必須返回單個值可以包含 RETURN 語句，但是與函數不同，它不能用于返回值必須包含至少一條 RETURN 語句.子程序三：自主事務處置自主事務

40、處置主事務處置啟動獨立事務處置然后主事務處置被暫停自主事務處置子程序內的 SQL 操作然后終止自主事務處置恢復主事務處置PRAGMA AUTONOMOUS_TRANSACTION 用于標志子程序為自主事務處置.子程序三：自主事務處置自主事務處置測試：創(chuàng)建過程1，執(zhí)行刪除員工7369操作，并回滾創(chuàng)建過程2，執(zhí)行刪除員工7499操作，并調用過程1創(chuàng)建程序調用過程2問哪個刪除操作會回滾？.子程序三：自主事務處置create or replace proceduretestp1 is -pragma autonomous_transaction;-標識子程序為自主事務處置begin delete from myemp where empno=736