1、第5講 ORACLE編程基礎(chǔ)（二）,主要內(nèi)容,PL/SQL簡介與運行環(huán)境,PL/SQL程序塊的組成,事務(wù)控制命令,PL/SQL流程控制,游標的使用,PL/SQL的異常處理,存儲過程、函數(shù)、觸發(fā)器,事務(wù)的概念,事務(wù)控制命令,5.4 事務(wù)控制命令,并發(fā)控制,事務(wù)的概念,當(dāng)用戶建立與數(shù)據(jù)庫的會話后，用戶就可以對數(shù)據(jù)庫進行操作，而用戶對數(shù)據(jù)庫的操作是一個個事務(wù)來進行的。 事務(wù)是指在相鄰兩條事務(wù)控制命令之間的一組sql語句，它是對數(shù)據(jù)庫操作的邏輯單元，并且可以避免不一致的數(shù)據(jù)。 事務(wù)的開始位置是從前一個事務(wù)結(jié)束以后執(zhí)行的第一條SQL語句，或者在連接到該數(shù)據(jù)庫以后所執(zhí)行的第一條SQL語句。 事務(wù)的結(jié)束是使

2、用COMMIT或ROLLBACK等顯式語句標識的 。,事務(wù)的控制語句,事務(wù)的控制命令有如下三種形式： 5.4.1 事務(wù)提交(COMMIT) 5.4.2 事務(wù)回退(ROLLBACK) 5.4.3 設(shè)置保存點命令(SAVEPOINT),5.4.1 事務(wù)提交(COMMIT),提交命令用于提交自上次提交以后對數(shù)據(jù)庫中數(shù)據(jù)所作的改動。提交命令會結(jié)束用戶的事務(wù)處理，可以使對數(shù)據(jù)的改變永久化。 在執(zhí)行提交命令之前，數(shù)據(jù)庫中的數(shù)據(jù)（永久存儲介質(zhì)上的數(shù)據(jù)）并沒有發(fā)生任何改變，用戶本人可以通過查詢命令查看對數(shù)據(jù)庫操作的結(jié)果，但是整個網(wǎng)絡(luò)上的其他用戶并沒有看到你對數(shù)據(jù)庫所作的改動。 無論事務(wù)處理的規(guī)模如何，提交的響

3、應(yīng)通常差別很小。因為在數(shù)據(jù)庫中進行提交之前，已經(jīng)完成了所有實際的工作，已經(jīng)修改了數(shù)據(jù)庫中的數(shù)據(jù)，完成了99.9%的工作。,5.4.1 事務(wù)提交(COMMIT),COMMIT命令的語法如下： COMMIT；,Oracle中設(shè)置了一個自動提交的開關(guān)，如果把它設(shè)置為ON,那么所有的改變數(shù)據(jù)庫中數(shù)據(jù)的語句都會立即生效，影響數(shù)據(jù)庫中的數(shù)據(jù)，缺省值為OFF。格式：SET AUTOCOMMIT ON|OFF。 用戶主動斷開與oracle的連接，這時用戶當(dāng)前的事務(wù)將被自動提交。,5.4.2 事務(wù)回退(ROLLBACK),在尚未對數(shù)據(jù)庫提交的時候，可以用事務(wù)回退命令ROLLBACK，將數(shù)據(jù)庫回退到上次COMMI

4、T后的狀態(tài)。 事務(wù)完成的所有工作都被取消，好像沒有執(zhí)行這些命令一樣。 -如果插入了一行，那么回滾就刪除它；如果更新了一行，回滾就將其更新到原來的樣子；如果刪除了一行，就重新插入它。 事務(wù)所需要執(zhí)行的所有加鎖處理都被釋放掉； 一旦事務(wù)已提交，就不能再使用事務(wù)回退命令進行回退了。 ROLLBACK語句的語法是： ROLLBACK;,5.4.2 事務(wù)回退(ROLLBACK),當(dāng)程序檢測出一個錯誤而不能繼續(xù)工作時，通常都使用顯式的ROLLBACK語句。 如果一個會話斷開與數(shù)據(jù)庫的連接，而沒有使用COMMIT或ROLLBACK終止當(dāng)前的事務(wù)時，那么該事務(wù)會自動被數(shù)據(jù)庫撤銷。 用戶進程被意外中止，這是用戶

5、當(dāng)前的事務(wù)被回滾。 用戶只有在必要時才可以進行回滾，它是開銷很大的操作。,5.4.3 設(shè)置保存點命令(SAVEPOINT),如果只需回退事務(wù)的一部分時，可以使用保存點，將整個事務(wù)劃分為若干部分，這樣就可以回退部分事務(wù)了。 保存點的設(shè)置和使用格式： SAVEPOINT ； 設(shè)置了保存點后就可以回退到指定的保存點了，語法格式如下： ROLLBACK TO ;,5.4.4 事務(wù)處理示例,例：在數(shù)據(jù)庫中有一個表，表名為score_table,表有五個域：學(xué)號、姓名、班級、數(shù)學(xué)、語文成績。我們用Oracle的SQL*Plus工具來完成3個事務(wù)。 SQL select * from score_table

6、; NO NAME CLASS MATHS CHINESE - - - - - 1 陳靜 3(1) 88 67 2 張華 3(1) 87 87 3 鄭軍 3(1) 81 99 4 鄧超 3(1) 93 91 SQL delete from score_table where name=陳靜; 已刪除 1 行。 SQL commit; 提交完成。,5.4.4 事務(wù)處理示例,SQL select * from score_table; NO NAME CLASS MATHS CHINESE - - - - - 2 張華 3(1) 87 87 3 鄭軍 3(1) 81 99 4 鄧超 3(1) 93

7、 91,5.4.4 事務(wù)處理示例,SQL delete from score_table; 已刪除3行。 SQL select * from score_table; 未選定行 SQL rollback; 回退已完成。,5.4.4 事務(wù)處理示例,SQL select * from score_table; NO NAME CLASS MATHS CHINESE - - - - - 2 張華 3(1) 87 87 3 鄭軍 3(1) 81 99 4 鄧超 3(1) 93 91,并發(fā)控制,Oracle是一個典型的多用戶數(shù)據(jù)庫系統(tǒng)，也就是說，多個用戶會同時建立與數(shù)據(jù)庫的會話。當(dāng)一個用戶建立與數(shù)據(jù)庫會

8、話，也就會有多個事務(wù)對數(shù)據(jù)庫進行操作，就會發(fā)生多個事務(wù)操作相互干擾的問題。因此，Oracle必須提供一種機制，以保證所有事務(wù)在執(zhí)行時都能夠得到正確的結(jié)果，這就是基于事務(wù)的并發(fā)控制。 為了維護事務(wù)的一致性，Oracle使用鎖機制防止其他用戶修改一個未完成事務(wù)中的數(shù)據(jù)。,鎖,鎖是由Oracle系統(tǒng)自動管理的，它對用戶而言是透明的。鎖的持續(xù)時間等于被提交事務(wù)的處理時間。 比如，當(dāng)用戶要更新數(shù)據(jù)表中的記錄時，Oracle會自動完成對該項資源的加鎖和解鎖。用戶也可以用lock table語句顯式對要訪問的資源加鎖。,鎖,Oracle中的鎖分為三類： DDL鎖：在使用create、truncate或者al

9、ter語句時，由Oracle自動對相關(guān)資源施加，以確保在語句執(zhí)行過程中，沒有其他事務(wù)訪問。 DML鎖：在事務(wù)處理的開始被施加，而且事務(wù)處理完成時被釋放。 內(nèi)部鎖：由Oracle來管理，以保護內(nèi)部數(shù)據(jù)庫結(jié)構(gòu)，例如數(shù)據(jù)文件等。,條件控制,循環(huán)控制,GOTO跳轉(zhuǎn)控制語句,5.5 PL/SQL 流程控制,循環(huán)的嵌套,一、IF_THEN_ELSE語句語法: IF condition THEN Statements 1; Statements 2; . ELSE Statements 1; Statements 2; . END IF,5.5.1 條件控制,一、IF_THEN_ELSE語句語法示例如下:

10、declare a number; b number; begin a:=3; b:=4; if ab then dbms_output.put_line(a|大于|b); else dbms_output.put_line(a|小于|b); end if; end;,5.5.1 條件控制,下面的例子是查詢是否有該學(xué)生的成績，沒有就添加進來，將所有科目成績都置為0。 DECLARE counter number(2); math_score number(3,0); chinese_score number(3,0); BEGIN SELECT count(*) INTO counter FR

11、OM score_table WHERE name=陳靜; IF counter0 THEN SELECT maths,chinese INTO math_score,chinese_score FROM score_table WHERE name=陳靜; DBMS_OUTPUT.PUT_LINE(math_score); DBMS_OUTPUT.PUT_LINE (chinese_score); ELSE INSERT INTO score_table VALUES(1,陳靜, 3(1), 0,0); END IF; END;,5.5.1 條件控制,IF 可以嵌套，可以在IF 或IF .E

12、LSE語句中使用IF或IF.ELSE語句。 程序中的DBMS_OUTPUT是Oracle內(nèi)置包，該包允許使用SQL*Plus和SQLPlus工作單從PL/SQL中顯示輸出信息。 DBMS_OUTPUT.PUT_LINE允許顯示一行文本 ，該過程接受要接受一個變量，該變量可以是字符串類型、數(shù)值類型或者日期類型。 它在調(diào)試程序中非常有用，在使用之前需要在SQLPlus中設(shè)置，用SET命令將SERVEROUTPUT置為ON即可。,5.5.1 條件控制,二、IF_THEN_ELSIF語句 IF condition1 THEN statement1; ELSIF condition2 THEN stat

13、ement2; ELSIF condition3 THEN statement3; ELSE statement4; END IF; statement5;,5.5.1 條件控制,下面的示例判斷某一年是否為閏年，閏年判斷條件：年號能被4整除但是不能被100整除，或者能被400整除。 declare year_date number; leap boolean; begin year_date:=2008; if mod(year_date,4)0 then leap:=false; elsif mod(year_date,100)0 then leap:=true; elsif mod(yea

14、r_date,400)0 then leap:=false; else leap:=true; end if; if leap then dbms_output.put_line(year_date|是閏年); else dbms_output.put_line(year_date|是平年); end if; end;,5.5.1 條件控制,下面的例子演示了判斷某同學(xué)在表記錄中是否存在，如果存在，再根據(jù)該同學(xué)的成績增加學(xué)費或者減免學(xué)費，例子中IF_THEN_ELSIF語句嵌套在IF_THEN_ELSE語句中.,5.5.1 條件控制,DECLARE counter number(2); math

15、_score number(3,0); chinese_score number(3,0); BEGIN SELECT count(*) INTO counter FROM score_table WHERE name=鄧超; IF counter0 THEN SELECT maths,chinese INTO math_score,chinese_score FROM score_table WHERE name=鄧超; IF math_score95 AND chinese_score95 THEN UPDATE student_fee SET allfee=allfee-100 WHER

16、E name=鄧超; ELSIF math_score90 AND chinese_score90 THEN UPDATE student_fee SET allfee=allfee-50 WHERE name=鄧超; ELSIF math_score60 OR chinese_score60 THEN UPDATE student_fee SET allfee=allfee+100 WHERE name=鄧超; ELSE DBMS_OUTPUT.PUT_LINE(math_score); DBMS_OUTPUT.PUT_LINE (chinese_score); END IF; ELSE D

17、BMS_OUTPUT.PUT_LINE(沒有這位同學(xué)); END IF; COMMIT; END;,三、Case表達式 Case語句的基本格式如下： Case 變量 WHEN 表達式1 then值1 WHEN 表達式2 then值2 WHEN 表達式3 then值3 WHEN 表達式4 then值4 ELSE 值5 END; Case語句的功能：首先設(shè)定變量的值作為條件，然后順序檢查表達式，一旦從中找到與條件匹配的表達式值，就停止case語句的處理。,5.5.1 條件控制,三、CASE語句 set serveroutput on declare i number:=1; begin case

18、i when 0 then dbms_output.put_line(i is zero); when 1 then dbms_output.put_line(i is one); else dbms_output.put_line(i is more than one); end case; end;,5.5.1 條件控制,執(zhí)行上面的程序塊時，每次循環(huán)，執(zhí)行的都是查找與i匹配的when常量的相關(guān)語句。所有其他語句都會被跳過。,5.5.1 條件控制,（1）直到型循環(huán) （ loopexitend循環(huán)控制語句） 語法格式： LOOP EXIT WHEN ； END LOOP; 先執(zhí)行循環(huán)體，然后判

19、斷，如果條件為真，則結(jié)束循環(huán)，否則繼續(xù)循環(huán)。直到型循環(huán)的循環(huán)體至少執(zhí)行1次。,5.5.2 循環(huán)控制,示例用直到型循環(huán)求1100所有整數(shù)的和。 variable sum number DECLARE I number(3):=100; BEGIN :sum:=0; LOOP :sum:=:sum+I; I:=I-1; EXIT WHEN I = 0; END LOOP; END; / print sum; 全局變量的引用時，必須加上:。,5.5.2 循環(huán)控制,（2）FOR循環(huán) FOR.LOOP語法： FOR counter IN REVERSE start_range.end_range LOO

20、Pstatements; END LOOP; LOOP和WHILE循環(huán)的循環(huán)次數(shù)都是不確定的，F(xiàn)OR循環(huán)的循環(huán)次數(shù)是固定的，counter是一個隱式聲明的變量，初始值是start_range，第二個值是start_range+1，直到end_range，如果start_range等于end _range，那么循環(huán)將執(zhí)行一次。如果使用了REVERSE關(guān)鍵字，那么范圍將是一個降序。,5.5.2 循環(huán)控制,例：從鍵盤接受一個整數(shù)，計算它的階乘，并在屏幕上打印出來。 variable fac number DECLARE NUM NUMBER(3):=5; BEGIN :fac:=1; IF NUM0

21、 THEN FOR I in 1.NUM LOOP :fac:=:fac * I; END LOOP; END IF; END; / PRINT fac; 如果要退出for循環(huán)可以使用EXIT語句。,5.5.2 循環(huán)控制,set serveroutput on begin for i in 0.5 loop case i when 0 then dbms_output.put_line(i is zero); when 1 then dbms_output.put_line(i is one); when 2 then dbms_output.put_line(i is two); when

22、3 then dbms_output.put_line(i is three); when 4 then dbms_output.put_line(i is four); else dbms_output.put_line(i is five); end case; end loop; end;,5.5.2 循環(huán)控制,使用reverse關(guān)鍵字 例：輸出20到1之間能被3整除的數(shù)。 begin for i in reverse 1 . 20 loop if mod(i,3)=0 then dbms_output.put_line(i); end if; end loop; end; /,5.5.

23、2 循環(huán)控制,嵌套的for循環(huán) 例：打印一個由星號“*”組成的三角形。 begin for x in 1 . 5 loop for y in 1.2*x-1 loop dbms_output.put (*); end loop; dbms_output.put_line( ); end loop; end; /,5.5.2 循環(huán)控制,（3）WHILE循環(huán) WHILE循環(huán)的語法是： WHILE condition LOOP Sequence_of_statement END LOOP; WHILE.LOOP中condition條件與循環(huán)相聯(lián)系，如果條件為TRUE,則執(zhí)行循環(huán)體內(nèi)的語句，如果結(jié)果為

24、FALSE,則結(jié)束循環(huán)，當(dāng)前循環(huán)的循環(huán)體可能一次也不執(zhí)行。,5.5.2 循環(huán)控制,示例用WHILE循環(huán)求1100所有整數(shù)的和 DECLARE summ number :=0; i number(3):=100; BEGIN WHILE i0 LOOP summ:=summ+i; i:=i - 1; END LOOP; DBMS_OUTPUT.PUT_LINE(summ); END;,5.5.2 循環(huán)控制,循環(huán)結(jié)構(gòu)有三種類型： 簡單循環(huán)結(jié)構(gòu)：一直運行，直到顯式地結(jié)束循環(huán)為止。 for循環(huán)：重復(fù)運行指定的次數(shù)。 while循環(huán)：一直運行，直到滿足指定的條件為止。 練習(xí)：編寫循環(huán)結(jié)構(gòu)，當(dāng)計數(shù)器cou

25、nter的值大于20時，循環(huán)結(jié)束。 1.使用ifthen條件判斷的簡單循環(huán)結(jié)構(gòu)。 2.使用exit when條件判斷的簡單循環(huán)結(jié)構(gòu)。 3.使用for循環(huán)結(jié)構(gòu)。 4.使用while循環(huán)結(jié)構(gòu)。,5.5.2 循環(huán)控制,1.使用ifthen條件判斷的簡單循環(huán)結(jié)構(gòu)。 counter:=0; loop counter:=counter+1; if counter0 then exit; end loop;,5.5.2 循環(huán)控制,程序塊的內(nèi)部可以有另一個程序塊這種情況稱為嵌套。 嵌套要注意的是變量，定義在最外部程序塊中的變量可以在所有子塊中使用，如果在子塊中定義了與外部程序塊變量相同的變量名，在執(zhí)行子塊時將使用子塊中定義的變量。子塊中定義的變量不能被父塊引用。同樣GOTO語句不能由父塊