1、第12章 Oracle編程基礎本章介紹Oracle編程基礎，主要有PL/SQL相關內容和Oracle的控制語句。PL/SQLProcedural Language/SQL是Oracle在SQL標準語言上進行擴展后形成的程序設計語言，是一種Oracle數據庫特有的、支持應用開發的語言。Oracle編程中也需要結合使用流程控制語句，用于控制PL/SQL語句、語句塊的執行過程。:12.1 PL/SQL簡介PL/SQL是Oracle公司開發的一種編程語言，它是對SQL的擴充。在PL/SQL程序中，可以定義變量、數據類型、函數以及過程，可以使用流控制語句，可以包含SQL語句，還可以進行錯誤處理。利用PL

2、/SQL程序，可以對數據庫進行復雜的訪問。:12.1.1 PL/SQL基本結構PL/SQL是一種結構化編程語言。程序的基本單元是塊，主要的塊形式有函數、過程和匿名塊。一個PL/SQL塊由以下三部分組成，其大體結構如圖所示。:12.1.1 PL/SQL基本結構【示例12-1】輸出語句“Hello,Life for fun, style for teens”。:12.1.2 PL/SQL注釋注釋增強了程序的可讀性，使得程序更易于理解。注釋在進行編譯時被PL/SQL編譯器忽略。注釋有單行注釋和多行注釋兩種，這與許多高級語言的注釋風格是一樣的。1.單行注釋單行注釋由兩個連字符（-）開場，一直到行尾回車

3、符標志著注釋的結束）。【示例12-2】對示例12-1進行單行注釋。:12.1.2 PL/SQL注釋2.多行注釋多行注釋由/*開頭，由*/結尾，這和C語言是一樣的。【示例12-3】多行注釋說明。:12.1.3 PL/SQL基本規則做任何事情都有規范，編程也一樣。在PL/SQL中，也需要遵守一些規范，這樣可以提高代碼的可讀性，提高工作效率。:12.1.3 PL/SQL基本規則1.PL/SQL中允許出現的字符集。字母，包括大寫和小寫。數字，即09。空格、回車符。數學符號，包括+、-、*、/、=。間隔符，包括()、?、!、;、:、,、#、$、%、&等。:12.1.3 PL/SQL基本規則2.P

4、L/SQL需要遵守的要求。標識符是不區分大小寫的。標識符中只允許出現字母、數字、下劃線，并且以字母開頭。語句以分號結束。字符型和日期型必須使用括號括起。語句的關鍵詞、標識符:12.1.3 PL/SQL基本規則3.PL/SQL中可以參考的規范。每行只寫一條語句。所有的過程名稱大寫。所有的變量以及自建的過程或游標、觸發器名稱都使用有意義的名稱命名。命名應該以“_的連接方式，也可以使用大小寫混合的方式。變量前最好加上前綴，以表示該變量的數據類型、作用范圍等。每個變量都應加上注釋。在重要的程序段都應加上注釋。這些只是基本的規則，可以提高代碼的可讀性。在日常中應該注意這些規范，形成良好的編程習慣。:12

5、.1.4 PL/SQL數據類型Oracle定義的數據類型很多，掌握這些簡單的數據類型有助于編寫一些復雜的程序。下面將對常用數據類型進行介紹。:12.1.4 PL/SQL數據類型1.數字類型數字類型變量存儲整數或者實數。它包含number、pls_integer和binary_integer3種基本類型。其中，number類型的變量可以存儲整數或浮點數，而pls_integer和binary_integer類型的變量只存儲整數。:12.1.4 PL/SQL數據類型2.字符類型字符類型變量用來存儲字符串或者字符數據。其類型包括varchar2、char、nchar、nvarchar2等。varch

6、ar2：可以存儲變長字符串。char類型表示定長字符串。nchar類型表示的是存儲Unicode字符集的定長字符集數據。nvarchar2類型表示的是存儲Unicode字符集的變長字符集數據。:12.1.4 PL/SQL數據類型3.日期類型日期類型是用來存放表示日期的數據的。日期類型有date、timestamp。date類型用來存儲日期和時間的。date類型的時間精確到秒。timestamp類型也是用來存儲日期和時間信息，包括世紀、年、月、天、小時、分鐘和秒。與date類型的區別就是在顯示日期和時間時更精確。timestamp類型可以精確到小數秒。使用timestamp類型存放日期還可以顯示

7、當前是上午還是下午。:12.1.4 PL/SQL數據類型4.布爾類型布爾類型中的唯一類型是boolean，主要用于控制程序流程。一個布爾類型變量的值可以是true、false和null。:12.1.4 PL/SQL數據類型5.type定義的數據類型上面介紹了幾種常用的數據類型，下面來介紹一下如何定義數據類型，它類似C語言中的結構類型。定義數據類型的語句格式如圖所示。:12.1.5 PL/SQL運算符運算符是一種符號，用來指定要在一個或者多個表達式中執行的操作。運算符一般也用在執行列、常量或變量之間的數學運算和比較操作中。而表達式則是標識符、值和運算符的組合。簡單的表達式可以是一個常量、函數、列

8、名、變量和子查詢。可以用運算符將兩個或更多的簡單表達式連接起來組成復雜的表達式。PL/SQL運算符包括：算數運算符、位運算符、比較運算符、邏輯運算符、賦值運算符、字符串連接符和一元運算符。:12.1.5 PL/SQL運算符1.算術運算符算術運算符用于執行數字型表達式的算術運算。在PL/SQL中支持的算術運算及其說明如下所示。+（加），加法或正號。-（減），減法或負號。*（乘），乘法運算。/（除），除法運算。:12.1.5 PL/SQL運算符2.比較運算符比較運算用來比較兩個表達式的大小。在PL/SQL中可以支持的比較運算符主要包括以下幾種。，大于。=，等于。=，大于或等于。=，小于或等于。（!

9、=），不等于:12.1.5 PL/SQL運算符3.邏輯運算符邏輯運算符用來對邏輯條件進行測試，以獲得其真實情況。它與比較運算符一樣，根據測試結果返回布爾值true、false、null。邏輯運算符有and、or、not、between、like等，具體的在查詢一章中已詳細講解。:12.1.5 PL/SQL運算符4.字符串連接符字符竄連接連接符“|”用于實現字符串之間的連接操作，如下圖。:12.1.5 PL/SQL運算符5.賦值運算符 在PL/SQL中，賦值運算符為“:=”，即將表達式的值賦給一個變量。【示例12-4】聲明一個變量，并為該變量賦值為“confidence”。:12.1.5 PL/

10、SQL運算符6.運算符的優先級Oracle中的運算符具有不同的優先級。同一個表達式中包含不同的運算符時，運算符的優先級決定了表達式的計算和比較操作的順序。Oracle中各種運算符的優先級的順序如下所示。括號，()正、負運算，+、-乘、除運算，*、/加、減、字符連接運算，+、-、|比較運算，=、，=，=，（!=）邏輯非運算，not邏輯與運算，andall、any、between、in、like、or、some等運算賦值運算，:=:12.1.5 PL/SQL運算符在進行數據查詢時，經常需要對查詢到的數據進行再次計算處理。PL/SQL中允許直接在select子句中使用計算列，計算列是通過某些列的數據

11、進行演算得來的結果。【示例12-5】結合order_items數據表，計算unit_price打9折之后的值。:12.1.6 PL/SQL表達式數據庫中經常使用表達式來計算結果，尤其在變量和常量的使用過程中。在前面已經接觸過表達式的使用，它和普通編程語言的表達式類似。本小節系統地介紹表達式的類型以及如何使用表達式。1.表達式簡介表達式不能獨立構成語句，表達式的結果是一個值。如果不給這個值安排一個存放的位置，則表達式本身毫無意義。通常，表達式作為賦值語句的一部分出現在賦值值運算符的右邊，或者作為函數的參數等。:12.1.6 PL/SQL表達式2.字符和數值表達式（1數值表達式是由數值類型的常量、

12、變量以及函數，由運算符連接而成。在PL/SQL中，可以使用的算數運算符有。加號+。減號-。乘號*。除號。（2對于字符表達式，唯一的運算符就是“|”，它的作用是把幾個字符串連接在一起，如表達式：wonderful |life|.的值就相等于wonderfullife。:12.1.6 PL/SQL表達式3.關系表達式和邏輯表達式（1關系表達式是將字符或數值用關系運算符連接起來。（2邏輯表達式是由邏輯符號和常量或變量等組成的表達式。:12.1.7 PL/SQL中變量、常量的定義與使用在PL/SQL塊中可以定義變量、常量和數據類型，這使得PL/SQL塊對數據的處理更加靈活。變量、常量和類型的定義放在P

13、L/SQL塊的變量聲明部分。:12.1.7 PL/SQL中變量、常量的定義與使用1.變量和常量在PL/SQL程序運行時，需要定義一些變量來存放一些數據。（1定義常量定義常量的語句格式如圖所示。:12.1.7 PL/SQL中變量、常量的定義與使用（2定義變量變量的定義有兩種格式，如下圖。【示例12-6】定義了一個有關住址的變量，它是變長字符型，最大長度為20個字符。:12.1.7 PL/SQL中變量、常量的定義與使用（3變量初始化變量在定義時可以指定默認值或初始值，在PL/SQL塊的運行過程中還可以為其賦值。【示例12-7】定義三個變量id、name、hiredate，演示變量的聲明、賦值和輸出

14、操作。【示例12-8】在上個示例中，已經了定義三個變量，id、name、hiredate，現在結合%type屬性方法，添加一個變量是leavedate，該變量的數據類型與hiredate相同。:12.2 PL/SQL中定義類型在前面簡單講解了type定義的數據類型。現在講解常用的自定義類型：記錄類型和集合類型。它們都是復合數據類型。:12.2.1 記錄類型記錄類型允許在一個類型中包含若干類型不同的字段，字段類型可以是基本數據類型，也可以是另一個復合數據類型。記錄類型的定義格式如圖所示。:12.2.1 記錄類型【示例12-9】例如，要存儲顧客的信息，可以定義一個記錄類型，包括姓名、地址、記錄日期

15、等字段。【示例12-10】定義了兩個shopper類型的變量，分別表示兩個顧客。【示例12-11】在定義上述類型和變量的基礎上，為記錄類型中的字段賦值，并輸出結構。【示例12-12】根據customers表的結構，可以定義記錄類型變量cus。【示例12-13】根據記錄類型變量cus，為customer_id、cust_first_name，city這三個字段賦值，并輸出其字段值。:12.2.2 集合類型記錄類型變量中包含若干類型不同的數據，而集合類型變量中包含多個相同類型的元素。要創建一個集合，先要定義一個集合類型，然后再定義該類型的集合變量。定義集合類型的語法如圖所示。:12.2.2 集合類

16、型【示例12-14】定義了一個集合類型，元素類型是整數。【示例12-15】定義兩個集合變量begins1和begins2，并分別調用構造函數進行初始化，begins1中包含三個元素，begins2是一個空集合，不包含任何元素。集合類型類似于面向對象技術中的類，除了可以使用構造函數外，還有一些方法可以用來對集合進行操作。:12.2.2 集合類型集合的部分方法如表所示。方法方法功能功能extend(m,n)將集合的第將集合的第n個元素追加到集合末尾共個元素追加到集合末尾共m次，次，如果不指定如果不指定n，則追加，則追加m個空元素。若不指個空元素。若不指定定m和和n，則追加一個空元素，則追加一個空元

17、素count返回集合中當前包含的元素的個數返回集合中當前包含的元素的個數first返回第一個元素的下標返回第一個元素的下標last返回最后一個元素的下標返回最后一個元素的下標next(n)返回第返回第n個元素之后的元素的下標個元素之后的元素的下標prior(n)返回第返回第n個元素之前的元素的下標個元素之前的元素的下標:12.2.2 集合類型【示例12-16】結合兩個集合變量begins1和begins2，演示集合的使用方法。除了上述定義集合類型的方法，還有一種方法，其語法結構如圖所示。:12.2.2 集合類型【示例12-17】使用第二種定義集合類型方法，定義兩個集合變量begins3，并對該

18、變量進行賦值。:12.3 Oracle中的條件控制語句在Oracle數據庫中，PL/SQL提供了豐富的流控制語句，用來對程序的執行流程進行控制。通過控制語句，我們可以編寫更復雜的PL/SQL塊。流控制語句分為兩類，即條件判斷語句和循環語句。所謂條件語句，就是指程序根據具體條件表達式來執行一組命令的結構。Oracle中的條件語句包括if else和case when兩種形式。:12.3.1 流程控制if else判斷if語句是一種條件判斷語句，它根據條件判斷的結果執行不同的分支語句。if語句的語法結構如圖所示。:12.3.1 流程控制if else判斷三種if語句的流程圖如圖所示。【示例12-1

19、8】在表customersnew中存儲了有關顧客信息的記錄。:12.3.1 流程控制if else判斷1.if.構造這是if語句中最簡單的結構方式，它只有一個if語句，如果給定的表達式不成立，那么將繼續向下執行。【示例12-19】現欲統計表customersnew中，列status是“gold的顧客信息，要求輸出統計結果，如果存在貴賓顧客，則輸出“本公司有擁有貴賓會員卡的顧客”，結合使用if.then語句。:12.3.1 流程控制if else判斷2.if else結構該類型的表達式不是選A就是選B。該結構表示要么執行if后面的語句，要么執行else后面的語句，是二選一的模式。該結構執行完畢后

20、，程序會繼續向后執行。【示例12-20】參考上一個示例，現欲統計表customersnew中，列status是“gold的顧客信息，要求輸出統計結果，如果存在貴賓顧客，則輸出“本公司有擁有貴賓會員卡的顧客”；如果不存在，則輸出“本公司沒人擁有貴賓會員卡”，結合使用if. else語句。:12.3.1 流程控制if else判斷3.if elsif結構該結構是前面兩種使用方式的綜合，它可以提供多個if條件選擇，當程序執行到該結構部分時，它會對每一個條件進行判斷，一旦條件為真，程序會執行相應的語句，而后繼續判斷下一個條件，直到所有條件判斷完成。該結構執行完畢后，程序會繼續向后執行。【示例12-21

21、】當需要進行判斷的情況多于一種時，可以利用elsif。:12.3.2 流程控制case when分支case語句與if語句類似，也是根據條件選擇對應的語句執行。但是case結構用于多條件分支結構，可以完成計算多個條件并為每個條件返回單個值。可以使流程控制更加清晰。case結構的主要流程圖如圖所示。:12.3.2 流程控制case when分支1.簡單case語句它給出一個表達式，并把表達式同提供的幾個可預見的結果做比較，若與可預見結果相匹配，則執行對應的語句序列，如下圖。:12.3.2 流程控制case when分支2.搜索式case語句它會提供多個布爾表達式，然后選擇第一個為true表達式，

22、執行對應的腳本，其語法結構如圖所示。:12.3.2 流程控制case when分支【示例12-22】表customersnew中，列status有“gold”、“Silver”、“Platinum三種情況，利用顧客編號，結合case語句，輸出對應的的顧客status類型，“gold對應貴賓卡會員、“Silver對應銀卡會員、“Platinum對應金卡會員。【示例12-23】使用搜索式case語句的方式進行改寫上一個示例12-22。【示例12-24】在表order_items中存儲了有關訂單產品的記錄信息。使用搜索式case語句的方式，在數據表order_items中，要求根據order_id得

23、到的對應的價格unit_price，判斷價格屬于哪個范圍并給出輸出提示。:12.4 Oracle中的循環語句所謂循環語句，即指程序按照指定的邏輯條件循環執行一組命令的語句。Oracle中的循環語句包括三種方式：無條件循環、while循環和for循環。:12.4.1 無條件循環Oracle中，可以使用loop語句對數據進行循環處理，利用該語句可以循環執行指定的語句序列。其使用語法如圖所示。:12.4.1 無條件循環無條件循環在循環開始時，不指定循環條件，但是必須在循環內部指定跳出循環的條件，否則，該循環將一直執行，造成死循環，loop循環語句的基本流程如圖所示。:12.4.1 無條件循環【示例12-26】可以利用無條件循環輸出customersnew表中，顧客編號在101112范圍之間的所有顧客姓名。【示例12-27】可以利用exit when形式跳出loop循環。:12.4.2 while循環while循環與基本的l