1、PL/SQL程序設計程序設計什么是什么是PL/SQLl對SQL的擴充l過程化l后臺技術PL/SQL 程序結構程序結構Declarebeginexceptionend/變量類型變量類型v_ename char(10) ;married boolean :=true ;v_ename emp.ename %TYPE ;emp_rec emp % ROWTYPE ;變量賦值an_sal :=mon_sal *12 + nvl(comm,0) ;Select ename INTO v_ename from emp where eno= e01 ;程序控制結構程序控制結構IF e1 THENELSIF

2、e2 THENELSIF e3 THENELSEEND IF;WHILE eLOOPEND LOOP ;FOR i IN 1.10LOOPEND LOOP ;EXIT WHEN e ;例例 外外EXCEPTIONWHEN 例外名1 THEN WHEN 例外名2 THEN WHEN OTHERS THEN END ；系統預定義例外系統預定義例外l dup_val_on_indexl no_data_foundl too_many_rowsSelect * into 用戶自定義例外用戶自定義例外l例外名定義 ex1 exception ;l引起例外 raise ex1 ； 例例 一一用戶注冊檢查t

3、in :tout : userlog : Ud psmsgUserid passwd 例例 一一DECLARE tin_rec tin % rowtype ; v_passwd userlog.passwd % type ; errps EXCEPTION ;BEGIN select * into tin_rec from tin ; select passwd into v_passwd from userlog where userid = tin_rec.ud ;例例 一一 if tin_rec.ps = v_passwd then insert into tout values(log

4、in ok );else raise errps ;end if ;exception when errps then insert into tout values(password error； when no_data_found then insert into tout values(userid error；end;光光 標標lOracle 使用Private SQL Area的工作區執行SQL語句，并保存語句執行結果和相關的狀態信息。l光標是一個PL/SQL結構，利用光標可以命名這些工作區，并通過光標訪問工作區中的信息。隱式光標隱式光標隱式光標名：SQL訪問：SQLROWCOUN

5、TSQL%FOUNDSQL%NOTFOUND顯式光標顯式光標l定義 delcare cursor c1 is select l打開 open c1l讀 fetch c1 into 變量/記錄lclose c1顯式光標顯式光標讀取狀態信息c1%found c1%rowcount用光標定義記錄temp c1%rowtype例例 二二問題：為職工長工資。從最低工資長，工資總額限制在50萬元。Declare cursor c1 is select eno,sal from emp order by sal for update ; emp_num number :=0 ; s_sal number ;

6、 e_sal number ; e_eno char(3);例例 二二Begin open c1; select sum(sal) into s_sal from emp ; while s_sal500000 loop fetch c1 into e_eno,e_sal ; exit when c1%notfound ; update emp set sal=sal*1.1 where eno=e_eno ; s_sal:=s_sal + e_sal*0.1; emp_num:=emp_num+1 ; end loop ; where current of c1 ;例例 二二Close c1

7、;insert into msg values(emp_num , s_sal) ;commit ;end;光標光標for循環循環For r in c1loop r.salend loop;如何打開光標如何讀光標區如何判斷讀完如何關閉光標帶參數光標帶參數光標cursor c1 (depno char(3) is select * from emp where dno=depno ; open c1(v_depno) ; 例例 三三l例題： 收發室收文件。文件分類，每類中文件依收到日期先后順序編號。值班人員收到文件后，要在系統中登錄文件類別，編號，收到日期。 （注：一天中收到的文件可能不止一份）

8、l編一PL/SQL 程序，查詢每一類中最后一天收到的文件）l表clog(kno 類別 , pno 編號 , pdate 收發日期) l練習要點：光標FOR循環，帶參數的光標例例 三三Declare cursor c1 is select distinct kno from clog; cursor c2 (kindno char(3) is select * from clog where kno=kindno order by day desc ; i number ; l_d date ;begin例例 三三For r1 in c1loop i := 0 ; for r2 in c2(r1

9、.kno) loop i := i + 1 ; if i=1 then insert into tout values(r2.kno,r2.pno,r2.pdate); l_d:=r2.pdate; elsif r2.pdate = l_d then insert into tout values(r2.kno, r2.pno,r2.pdate); else exit; end if ; end loop;end loop ;記錄多域單行結構記錄多域單行結構Declaretype time_type is record (second integer(2) :=0 , minute integ

10、er(2) :=0, hour integer(2) :=0 ) ; time_rec time_type ;PL/SQL表表( (數組數組) )多行單列結構多行單列結構Declare type ename_type is table of char(10) index by binary_integer ;ename_array ename_type ;I binary_integer :=0 ;begin for rec in (select ename from emp) loop I:=I+1 ; ename_array(I):=rec.ename ;end loop ;PL/SQL表

11、表( (數組數組) )多行多列結構多行多列結構Declare Type t_studentTab is TABLE of students%ROWTYPEstudent_array t_studentTalbe ;I binary_integer :=0 ;begin for rec in (select * from students) loop I:=I+1 ; student_array(I):=rec ;end loop ;PL/SQL表表屬性屬性student_array.countstudent_array.delete(I);student_array.delete(I , j

12、);student_array.existsstudent_array.first student_array.laststudent_array.nextstudent_array.prior例外信息捕獲例外信息捕獲Exception when others then err_msg:=substr(sqlerrm,1,50); err_code:=sqlcode; insert into tout values(err_code,err_msg) ;end ;PL/SQL塊的嵌套及例外轉移塊的嵌套及例外轉移D01部門沒有職工屬正常情況 begin select eno into v_eno

13、 from emp where dno=d01 ; if sql%found then raise too_many_rows ; end if ;exception when no_data_found then null ;end ;PL/SQL塊中特殊的例外轉移塊中特殊的例外轉移Declare段發生例外V_Number NUMBER(3) :=ABC;立刻傳播到包含塊Exception段發生例外 Excepionwhen a thenraise b;when b then 立刻傳播到包含塊利用利用PL/SQL塊中特殊的例外轉移塊中特殊的例外轉移Declarea exception;Beg

14、inraise a;Exceptionwhen a theninsert into log_able.;raise;End;觸發器觸發器l存儲在數據庫里的帶名的PL/SQL塊，當表被修改時，如果滿足條件，自動執行l主要用處維護復雜的完整性約束審計修改表被修改時，給其他需運行的程序發信號觸發器觸發器創建語法創建語法Create or Replace trigger emp_checkbefore/after insert or update or delete for each rowon emp when beginend ;建立數據庫對象觸發器建立數據庫對象觸發器Create trigger

15、 emp_checkbefore/after/instead of insert or update or delete on empbeginif to_char(sysdate,dy)=星期日 then raise_application_error(-20000, today is holiday ) ;end if ;end ;/建立數據庫對象觸發器建立數據庫對象觸發器Insert into emp values(. );錯誤信息：錯誤位于第1行：ORA-20000: today is holidayORA-06512: 在 WXY.EMP_CHECK, line 3ORA-04088

16、: 在執行觸發子 WXY.EMP_CHECK 時出錯觸發器觸發器類型類型l語句級觸發器l行級觸發器（for each row）:old:newlInstead-of觸發器觸發器觸發器觸發順序觸發順序lBefore 語句級lBefore 行級lDMLlAfter 行級lAfter 語句級觸發器觸發器限制限制l不能有事務控制語句l調用的過程和函數也不能有事務控制語句l不能聲明long, long raw列l語句級觸發器不能使用:old, :newl行級觸發器存取變化表與限制表Create trigger emp_tbefore update of sal on empfor each rowwhe

17、n (old.dno is not null)begin if (:new.sal show errorTRIGGER T1出現錯誤：LINE/COL ERROR- -2/2 PL/SQL: SQL Statement ignored2/25 PLS-00201: 標識符 NEW.A 必須說明存儲過程存儲過程Create or replace procedure raise_salary (emp_id integer,increase real)is begin update emp set sal=sal+increase where eno=emp_id ;end ;存儲過程存儲過程創建

18、語法創建語法Create or replace procedure procedure_name (argument in|out|in out type, ) is|as 說明部分Begin 執行部分Exception 例外處理部分end procedure_name;函數函數Create or replace function raise_salary (emp_id integer,increase real)RETURN BOOLEANis begin update emp set sal=sal+increase where eno=emp_id ; RETURN TRUE;end

19、;函數函數創建語法創建語法Create or replace function function_name (argument in|out|in out type, )Return type is|as beginExceptionend function_name;子程序子程序參數模式參數模式三種模式IN傳入，只讀OUT傳出，只寫IN OUT傳入傳出，可讀寫子程序子程序參數限制參數限制1. OUT, IN OUT只能用變量調用p(in, in, out)p(a,b,c); -錯誤2. 實參限制p(v1 out varchar2, v2 out number, v3 out char)a1

20、varchar2(10); a2 number(8); a3 char(6);p(a1, a2, a3);3.特殊情況形參限制p (v1 out %type)子程序子程序參數標識參數標識1. 位置標識p(v1, v2, v3)p(a1, a2, a3)2. 帶名標識p(v1 = a1, v3 = a3, v2 = a3);3.參數缺省值p (v1 in varchar2, v2 in varchar2 v3 in varchar2 default aaaa)p(a1, a2, a3); p(a1, a2);子程序子程序權限權限1.執行權限子程序在其擁有者的權限下運行2

21、. 依賴對象的權限直接授予擁有者，不能通過角色授予3. 原因a. oracle早期聯編b.角色是會話級別的子程序子程序例外返回例外返回1.未處理例外子程序返回調用者或調用環境2. Out 及in out 型變量值 保留調用時的值子程序子程序調用調用l在SQL*Plus中 execute raise_salary(12,46.50)l在PL*SQL中 begin raise_salary(12,46.50)； end ;子程序子程序相關數據字典相關數據字典lUSER_OBJECTS所有對象，包含子程序lUSER_SOURCE存儲過程，函數，觸發器，包的源碼lUSER_ERRORS編譯過程中的錯誤

22、信息 包（包（package)l包是一個可以將相關對象存儲在一起的PL/SQL結構。l包提供了PL/SQL的全局變量l包中過程可重載l不同會話使用包的不同拷貝包包結構圖結構圖應用程序包包規范和主體規范和主體l包規范/接口(specification) 變量，常量，過程，函數，光標，例外 。 為PL/SQL程序提供了全局對象。l包主體(body)過程，函數的實現l包依賴性(user_dependencies)主體依賴接口，接口不依賴主體，減少重編譯例四例四包規范包規范CREATE OR REPLACE PACKAGE ClassPackage AS - Add a new student int

23、o the specified class. PROCEDURE AddStudent( p_StudentID IN students.id%TYPE, p_Department IN classes.department%TYPE, p_Course IN classes.course%TYPE); - Removes the specified student from the specified class. PROCEDURE RemoveStudent( p_StudentID IN students.id%TYPE, p_Department IN classes.departm

24、ent%TYPE, 包規范包規范 p_Course IN classes.course%TYPE); - Exception raised by RemoveStudent. e_StudentNotRegistered EXCEPTION; - Table type used to hold student info. TYPE t_StudentIDTable IS TABLE OF students.id%TYPE INDEX BY BINARY_INTEGER; - Returns a PL/SQL table containing the students currently in

25、the specified class. PROCEDURE ClassList( p_Department IN classes.department%TYPE,例四例四包規范包規范 p_Course IN classes.course%TYPE, p_IDs OUT t_StudentIDTable, p_NumStudents IN OUT BINARY_INTEGER);END ClassPackage;例四例四包體包體CREATE OR REPLACE PACKAGE BODY ClassPackage AS - Add a new student for the specified

26、 class. PROCEDURE AddStudent( p_StudentID IN students.id%TYPE, p_Department IN classes.department%TYPE, p_Course IN classes.course%TYPE) IS BEGIN INSERT INTO registered_students (student_id, department, course) VALUES (p_StudentID, p_Department, p_Course);例四例四包體包體 COMMIT; END AddStudent; - Removes t

27、he specified student from the specified class. PROCEDURE RemoveStudent( p_StudentID IN students.id%TYPE, p_Department IN classes.department%TYPE, p_Course IN classes.course%TYPE) IS BEGIN DELETE FROM registered_students WHERE student_id = p_StudentID and例四例四包體包體 department = p_Department AND course

28、= p_Course; IF SQL%NOTFOUND THEN RAISE e_StudentNotRegistered; END IF; COMMIT; END RemoveStudent; - Returns a PL/SQL table containing the students currently - in the specified class. 例四例四包體包體PROCEDURE ClassList( p_Department IN classes.department%TYPE, p_Course IN classes.course%TYPE, p_IDs OUT t_St

29、udentIDTable, p_NumStudents IN OUT BINARY_INTEGER) IS v_StudentID gistered_students.student_id%TYPE; - Local cursor to fetch the registered students. CURSOR c_RegisteredStudents IS SELECT student_id FROM registered_students WHERE department = p_Department AND course = p_Course;例四例四包體包體BEGIN /* p_Num

30、Students will be the table index. It will start at 0, and be incremented each time through the fetch loop. At the end of the loop, it will have the number of rows fetched, and therefore the number of rows returned in p_IDs. */ p_NumStudents := 0;例四例四包體包體 OPEN c_RegisteredStudents; LOOP FETCH c_Regis

31、teredStudents INTO v_StudentID; EXIT WHEN c_RegisteredStudents%NOTFOUND; p_NumStudents := p_NumStudents + 1; p_IDs(p_NumStudents) := v_StudentID; END LOOP; END ClassList;END ClassPackage;系統包系統包l位于$ORACLE_HOME/rdbms/admin/lDBMS_OUTPUTlDBMS_SQLlDBMS_PIPElDBMS_JOB在開發語言里使用在開發語言里使用Oracle常用開發語言（工具）OCI接口:

32、C COBOL FORTRAN, 嵌入SQL語句: Pro*C FORTRAN PASCALCOBOL PL/I Ada圖形化開發： Develop 2000, Delphi, PowerBuilder, C+ BuilderWeb 應用開發 PL/SQL Web工具包PHP, Perl, Java分布式數據庫功能分布式數據庫功能分布式分布式DB物理上分布在網絡不同節點上的數據，從邏輯上可看作是一個整體（DB）。每個節點具有場地自治跨節點的全局操作訪問遠程數據庫訪問遠程數據庫本地(北京)orcale1遠程(上海)oracle2ora1ora2數據庫鏈數據庫鏈S2$HOME/network/ad

33、min/tnsname.ora訪問遠程數據庫上的表訪問遠程數據庫上的表l create database link L1 connect to 用戶名 identified by 口令 using s2; l select * from depL1 ;l select ename,tel from emp, depL1 where emp.dno=dep.dno ;lupdate depL1 set tel=2233 ;建立數據庫別名建立數據庫別名lcreate synonym dep for depL1 ;lselect ename,tel from emp, dep where emp.d

34、no=dep.dno ;分片create view emp as select emp1.ename,emp2.sal from emp1,emp2 where emp1.eno=emp2.eno ; 分片分片 - 視圖視圖Create view emp as select * from emp1L1 union select * from emp2L2；水平分片垂直分片復制復制 快照快照Create snapshot emp refresh fast complete force start with sysdate next next_day(sysdate,Monday) as sele

35、ct * from empL1 ;觸發器觸發器 Create snapshot log on emp時間表示時間表示l下周的同一天sysdate + 7l每星期五上午9點next_day( trunc(sysdate) , 星期五 ) + 9/24l每一小時sysdate +1/24l每10秒sysdate + 10/(24*60*60)過程中的分布處理與遠程過程調用過程中的分布處理與遠程過程調用Create procedure raise_salary (emp_id integer,increase real)is begin update empL2 set sal=sal+increa

36、se where eno=emp_id ;end ;begin raise_salaryL1(12,46.50)；end ;并發控制并發控制事務的概念事務的概念l事務是一個操作序列，一個事務中的操作要么都做，要么都不做。事務是一個不可分割的工作單元（原子性）。l事務是并發控制的基本單位。l事務開始 事務結束（commit / rollback)lDDL語句 單語句事務lset autocommit on (sql*plus) 事務回滾人為回滾 系統回滾rollback ；savepoint arollback to a并發存取數據庫并發存取數據庫R：讀用戶 W：寫用戶 RRWWRWW - W1

37、010 8810 77寫丟失寫丟失防止寫丟失加鎖防止寫丟失加鎖l自動加行鎖 insert、update、delete l讀提前加鎖 select * For update of l鎖語句 LOCK l在事務中加的鎖在事務結束時自動打開。死鎖死鎖UPDATE empSET sal=sal+500WHERE ename=張珊;UPDATE empSET sal=sal+700WHERE ename=李禾;UPDATE empSET sal=sal+300WHERE ename=李禾;UPDATE empSET sal=sal+200WHERE ename=張珊;事務A事務B如何解決死鎖如何解決死鎖

38、lOracle系統在檢測到一個死鎖時，它會給引起死鎖的其中一個事務發出一個錯誤信息，然后回滾該事務的當前語句。并告之用戶應顯式地回滾他的事務，而其他用戶就會因獲得資源而完成事務，死鎖就被解開了。實現讀一致性只讀事務實現讀一致性只讀事務Set transaction read only 只讀事務commit 寫事務包過程：dbms_transaction.read_only只讀事務幾點注意只讀事務幾點注意SET TRANSACTION READ ONLY必須是事務的第一條語句。在只讀事務里，只允許用戶執行讀語句（SELECT），而不能執行寫語句。 COMMIT語句、BOLLBACK語句或一個DD

39、L語句都會結束只讀事務。在只讀事務中所有的查詢都是引用數據庫的一個只讀版本，即只讀事務開始那一時刻數據庫中的數據。也就是說，只讀事務開始之后，其他事務對數據庫的修改（而且是提交了的），在只讀事務中看不到。 只讀事務不影響其他事務的操作，其他事務依然可以對數據庫的數據進行查詢或更新。就是說，讀一致性并沒有以限制其他用戶的寫為代價。防止讀臟數據防止讀臟數據READ寫事務防止讀臟數據防止讀臟數據writeread并發控制演示并發控制演示5Select num .for update5Update num=3cummitUpdate num=1 ?33Update num=111Select num3

40、cummitSelect num1并發控制演示并發控制演示Set transaction read only5Select num 5Update num=3 Select num commit commit commit 353Select num Select num 1Udate num=1 Oracle 事務的種類事務的種類l 讀寫事務（缺省）set transaction read write ;l 只讀事務set transaction read only 查詢結果是事務開始時刻數據庫中的數據。l 離散事務推遲了許多耗時的修改并記錄SQL語句所做的修改的內部系統操作。延遲并集中事務

41、的修改，直到應用提交一個離散事務。減少事務開銷，會提高聯機事務處理的系統性能定義離散事物執行系統內部包過程execute dbms_transaction.begin_discrete_transaction事務設計事務設計事務設計的好壞直接影響數據庫的完整性及應用的性能（一）工作單元事務的含義是將一些緊密相關的SQL語句包含起來。完成一項任務，形成一個工作單元。一個事務不能包含多個工作單元，也不能是一個工作單元的部分工作。（1）事務包含多個工作單元 ？當一個事務含有多個工作單元時，數據庫必須長時間地維護代表事務的內部系統信息。這會降低系統性能。特別是當oracle同時承擔多個這樣的不良事務設

42、計時更糟。例 (事務含有3個獨立單元，不相干的事）insert into emp; insert into part; insert into orders;事務設計事務設計（2）一些不必要的小事務提交會降低服務器的性能。例：insert into dep ; commit ; insert into emp commit ; insert into emp. commit ;部分化的事務會影響數據庫數據的完整性如果輸入部分職工記錄就提交。另一用戶（會計）看部門有職工了，就去讀不完整的數據，根據不完整的職工清單做了工資報表。事務設計事務設計（二）約束檢查時間（1）在執行插入、修改、刪除記錄后立

43、即執行約束檢查（2）延遲到應用提交事務時再執行約束檢查特定的應用也許會更新許多表，在事務結束之前會暫時違反完整性約束延遲約束指定：lcreate table temptable (a number primary key deferrable initially deferred);lcreate table temptable (a number primary key deferrable);lset constraint all / 約束名 deferred / immediate 事務設計事務設計CreatedeferrableCreatedeferrableinitially def

44、erredCreateSet immeSet defSet immeSet defSet immeSet defDIIIIIDDI安全性安全性l用戶標識（帳號和密碼）l存取控制（授權）l審計密碼密碼l操作系統用戶密碼l數據庫用戶帳號和密碼修改密碼alter user student1 identified by abc;權限控制權限控制lGrant 系統權限系統權限 to 用戶名 ；l系統權限系統權限：create any table(可在任何模式中建表)alter any table(更改任何模式中的任何表、視圖)create table(在自己模式中建表）create sequence （

45、在自己模式中建序列）drop user （刪除用戶）lock any table(鎖任何模式中的表、視圖）select any table (查詢任何模式的表、視圖)權限控制權限控制lGrant 角色角色 to 用戶名 ；l角色角色是一組系統權限的集合，系統預定義了以下三種角色；RESOURCE角色create tablecreate sequencecreate triggercreate procedurecreate cluster權限控制權限控制CONNECT角色create tablecreate viewcreate synonymcreate sequencecreate database linkcreate clusterc