精通Oracle核心技術和項目實戰(zhàn)之Oracle編程基礎

精通Oracle核心技術和項目實戰(zhàn)之Oracle編程基礎第1頁/共60頁第12章Oracle編程基礎本章介紹Oracle編程基礎，主要有PL/SQL相關內容和Oracle的控制語句。PL/SQL（Procedural

Language/SQL）是Oracle在SQL標準語言上進行擴展后形成的程序設計語言，是一種Oracle數據庫特有的、支持應用開發(fā)的語言。Oracle編程中也需要結合使用流程控制語句，用于控制PL/SQL語句、語句塊的執(zhí)行過程。第2頁/共60頁12.1PL/SQL簡介PL/SQL是Oracle公司開發(fā)的一種編程語言，它是對SQL的擴充。在PL/SQL程序中，可以定義變量、數據類型、函數以及過程，可以使用流控制語句，可以包含SQL語句，還可以進行錯誤處理。利用PL/SQL程序，可以對數據庫進行復雜的訪問。第3頁/共60頁12.1.1PL/SQL基本結構PL/SQL是一種結構化編程語言。程序的基本單元是塊，主要的塊形式有函數、過程和匿名塊。一個PL/SQL塊由以下三部分組成，其大體結構如圖所示。第4頁/共60頁12.1.1PL/SQL基本結構【示例12-1】輸出語句“Hello,Lifeforfun,styleforteens”。第5頁/共60頁12.1.2PL/SQL注釋注釋增強了程序的可讀性，使得程序更易于理解。注釋在進行編譯時被PL/SQL編譯器忽略。注釋有單行注釋和多行注釋兩種，這與許多高級語言的注釋風格是一樣的。1.單行注釋單行注釋由兩個連字符（-）開始，一直到行尾（回車符標志著注釋的結束）?！臼纠?2-2】對示例12-1進行單行注釋。第6頁/共60頁12.1.2PL/SQL注釋2.多行注釋多行注釋由/*開頭，由*/結尾，這和C語言是一樣的?！臼纠?2-3】多行注釋說明。第7頁/共60頁12.1.3PL/SQL基本規(guī)則做任何事情都有規(guī)范，編程也一樣。在PL/SQL中，也需要遵守一些規(guī)范，這樣可以提高代碼的可讀性，提高工作效率。第8頁/共60頁12.1.3PL/SQL基本規(guī)則1.PL/SQL中允許出現的字符集。字母，包括大寫和小寫。數字，即0~9?？崭?、回車符。數學符號，包括+、-、*、/、<、>、=。間隔符，包括()、{}、[]、?、!、;、:、,、"、@、#、$、%、^、&等。第9頁/共60頁12.1.3PL/SQL基本規(guī)則2.PL/SQL需要遵守的要求。標識符是不區(qū)分大小寫的。標識符中只允許出現字母、數字、下劃線，并且以字母開頭。語句以分號結束。字符型和日期型必須使用括號括起。語句的關鍵詞、標識符第10頁/共60頁12.1.3PL/SQL基本規(guī)則3.PL/SQL中可以參考的規(guī)范。每行只寫一條語句。所有的過程名稱大寫。所有的變量以及自建的過程或游標、觸發(fā)器名稱都使用有意義的名稱命名。命名應該以“_”的連接方式，也可以使用大小寫混合的方式。變量前最好加上前綴，以表示該變量的數據類型、作用范圍等。每個變量都應加上注釋。在重要的程序段都應加上注釋。這些只是基本的規(guī)則，可以提高代碼的可讀性。在日常中應該注意這些規(guī)范，形成良好的編程習慣。第11頁/共60頁12.1.4

PL/SQL數據類型Oracle定義的數據類型很多，掌握這些簡單的數據類型有助于編寫一些復雜的程序。下面將對常用數據類型進行介紹。第12頁/共60頁12.1.4

PL/SQL數據類型1.數字類型數字類型變量存儲整數或者實數。它包含number、pls_integer和binary_integer3種基本類型。其中，number類型的變量可以存儲整數或浮點數，而pls_integer和binary_integer類型的變量只存儲整數。第13頁/共60頁12.1.4

PL/SQL數據類型2.字符類型字符類型變量用來存儲字符串或者字符數據。其類型包括varchar2、char、nchar、nvarchar2等。varchar2：可以存儲變長字符串。char類型表示定長字符串。nchar類型表示的是存儲Unicode字符集的定長字符集數據。nvarchar2類型表示的是存儲Unicode字符集的變長字符集數據。第14頁/共60頁12.1.4

PL/SQL數據類型3.日期類型日期類型是用來存放表示日期的數據的。日期類型有date、timestamp。date類型用來存儲日期和時間的。date類型的時間精確到秒。timestamp類型也是用來存儲日期和時間信息，包括世紀、年、月、天、小時、分鐘和秒。與date類型的區(qū)別就是在顯示日期和時間時更精確。timestamp類型可以精確到小數秒。使用timestamp類型存放日期還可以顯示當前是上午還是下午。第15頁/共60頁12.1.4

PL/SQL數據類型4.布爾類型布爾類型中的唯一類型是boolean，主要用于控制程序流程。一個布爾類型變量的值可以是true、false和null。第16頁/共60頁12.1.4

PL/SQL數據類型5.type定義的數據類型上面介紹了幾種常用的數據類型，下面來介紹一下如何定義數據類型，它類似C語言中的結構類型。定義數據類型的語句格式如圖所示。第17頁/共60頁12.1.5

PL/SQL運算符運算符是一種符號，用來指定要在一個或者多個表達式中執(zhí)行的操作。運算符一般也用在執(zhí)行列、常量或變量之間的數學運算和比較操作中。而表達式則是標識符、值和運算符的組合。簡單的表達式可以是一個常量、函數、列名、變量和子查詢?？梢杂眠\算符將兩個或更多的簡單表達式連接起來組成復雜的表達式。PL/SQL運算符包括：算數運算符、位運算符、比較運算符、邏輯運算符、賦值運算符、字符串連接符和一元運算符。第18頁/共60頁12.1.5

PL/SQL運算符1.算術運算符算術運算符用于執(zhí)行數字型表達式的算術運算。在PL/SQL中支持的算術運算及其說明如下所示。+（加），加法或正號。-（減），減法或負號。*（乘），乘法運算。/（除），除法運算。第19頁/共60頁12.1.5

PL/SQL運算符2.比較運算符比較運算用來比較兩個表達式的大小。在PL/SQL中可以支持的比較運算符主要包括以下幾種。>，大于。=，等于。<，小于。>=，大于或等于。<=，小于或等于。<>（!=），不等于第20頁/共60頁12.1.5

PL/SQL運算符3.邏輯運算符邏輯運算符用來對邏輯條件進行測試，以獲得其真實情況。它與比較運算符一樣，根據測試結果返回布爾值true、false、null。邏輯運算符有and、or、not、between、like等，具體的在查詢一章中已詳細講解。第21頁/共60頁12.1.5

PL/SQL運算符4.字符串連接符字符竄連接連接符“||”用于實現字符串之間的連接操作，如圖所示。第22頁/共60頁12.1.5

PL/SQL運算符5.賦值運算符在PL/SQL中，賦值運算符為“:=”，即將表達式的值賦給一個變量?！臼纠?2-4】聲明一個變量，并為該變量賦值為“confidence”。第23頁/共60頁12.1.5

PL/SQL運算符6.運算符的優(yōu)先級Oracle中的運算符具有不同的優(yōu)先級。同一個表達式中包含不同的運算符時，運算符的優(yōu)先級決定了表達式的計算和比較操作的順序。Oracle中各種運算符的優(yōu)先級的順序如下所示。括號，()正、負運算，+、-乘、除運算，*、/加、減、字符連接運算，+、-、||比較運算，=、>，<，>=，<=，<>（!=）邏輯非運算，not邏輯與運算，andall、any、between、in、like、or、some等運算賦值運算，:=第24頁/共60頁12.1.5

PL/SQL運算符在進行數據查詢時，經常需要對查詢到的數據進行再次計算處理。PL/SQL中允許直接在select子句中使用計算列，計算列是通過某些列的數據進行演算得來的結果?！臼纠?2-5】結合order_items數據表，計算unit_price打9折之后的值。第25頁/共60頁12.1.6

PL/SQL表達式數據庫中經常使用表達式來計算結果，尤其在變量和常量的使用過程中。在前面已經接觸過表達式的使用，它和普通編程語言的表達式類似。本小節(jié)系統地介紹表達式的類型以及如何使用表達式。1.表達式簡介表達式不能獨立構成語句，表達式的結果是一個值。如果不給這個值安排一個存放的位置，則表達式本身毫無意義。通常，表達式作為賦值語句的一部分出現在賦值值運算符的右邊，或者作為函數的參數等。第26頁/共60頁12.1.6

PL/SQL表達式2.字符和數值表達式（1）數值表達式是由數值類型的常量、變量以及函數，由運算符連接而成。在PL/SQL中，可以使用的算數運算符有。加號+。減號-。乘號*。除號。（2）對于字符表達式，唯一的運算符就是“||”，它的作用是把幾個字符串連接在一起，如表達式：'wonderful'||'life'||'.'的值就相等于'wonderfullife'。第27頁/共60頁12.1.6

PL/SQL表達式3.關系表達式和邏輯表達式（1）關系表達式是將字符或數值用關系運算符連接起來。（2）邏輯表達式是由邏輯符號和常量或變量等組成的表達式。第28頁/共60頁12.1.7

PL/SQL中變量、常量的定義與使用在PL/SQL塊中可以定義變量、常量和數據類型，這使得PL/SQL塊對數據的處理更加靈活。變量、常量和類型的定義放在PL/SQL塊的變量聲明部分。第29頁/共60頁12.1.7

PL/SQL中變量、常量的定義與使用1.變量和常量在PL/SQL程序運行時，需要定義一些變量來存放一些數據。（1）定義常量定義常量的語句格式如圖所示。第30頁/共60頁12.1.7

PL/SQL中變量、常量的定義與使用（2）定義變量變量的定義有兩種格式，如圖所示?！臼纠?2-6】定義了一個有關住址的變量，它是變長字符型，最大長度為20個字符。第31頁/共60頁12.1.7

PL/SQL中變量、常量的定義與使用（3）變量初始化變量在定義時可以指定默認值或初始值，在PL/SQL塊的運行過程中還可以為其賦值?！臼纠?2-7】定義三個變量id、name、hiredate，演示變量的聲明、賦值和輸出操作?！臼纠?2-8】在上個示例中，已經了定義三個變量，id、name、hiredate，現在結合%type屬性方法，添加一個變量是leavedate，該變量的數據類型與hiredate相同。第32頁/共60頁12.2

PL/SQL中定義類型在前面簡單講解了type定義的數據類型?，F在講解常用的自定義類型：記錄類型和集合類型。它們都是復合數據類型。第33頁/共60頁12.2.1記錄類型記錄類型允許在一個類型中包含若干類型不同的字段，字段類型可以是基本數據類型，也可以是另一個復合數據類型。記錄類型的定義格式如圖所示。第34頁/共60頁12.2.1記錄類型【示例12-9】例如，要存儲顧客的信息，可以定義一個記錄類型，包括姓名、電話、地址、記錄日期等字段。【示例12-10】定義了兩個shopper類型的變量，分別表示兩個顧客?！臼纠?2-11】在定義上述類型和變量的基礎上，為記錄類型中的字段賦值，并輸出結構。【示例12-12】根據customers表的結構，可以定義記錄類型變量cus?！臼纠?2-13】根據記錄類型變量cus，為customer_id、cust_first_name，city這三個字段賦值，并輸出其字段值。第35頁/共60頁12.2.2集合類型記錄類型變量中包含若干類型不同的數據，而集合類型變量中包含多個相同類型的元素。要創(chuàng)建一個集合，先要定義一個集合類型，然后再定義該類型的集合變量。定義集合類型的語法如圖所示。第36頁/共60頁12.2.2集合類型【示例12-14】定義了一個集合類型，元素類型是整數。【示例12-15】定義兩個集合變量begins1和begins2，并分別調用構造函數進行初始化，begins1中包含三個元素，begins2是一個空集合，不包含任何元素。集合類型類似于面向對象技術中的類，除了可以使用構造函數外，還有一些方法可以用來對集合進行操作。第37頁/共60頁12.2.2集合類型集合的部分方法如表所示。方法功能extend(m,n)將集合的第n個元素追加到集合末尾共m次，如果不指定n，則追加m個空元素。若不指定m和n，則追加一個空元素count返回集合中當前包含的元素的個數first返回第一個元素的下標last返回最后一個元素的下標next(n)返回第n個元素之后的元素的下標prior(n)返回第n個元素之前的元素的下標第38頁/共60頁12.2.2集合類型【示例12-16】結合兩個集合變量begins1和begins2，演示集合的使用方法。除了上述定義集合類型的方法，還有一種方法，其語法結構如圖所示。第39頁/共60頁12.2.2集合類型【示例12-17】使用第二種定義集合類型方法，定義兩個集合變量begins3，并對該變量進行賦值。第40頁/共60頁12.3

Oracle中的條件控制語句在Oracle數據庫中，PL/SQL提供了豐富的流控制語句，用來對程序的執(zhí)行流程進行控制。通過控制語句，我們可以編寫更復雜的PL/SQL塊。流控制語句分為兩類，即條件判斷語句和循環(huán)語句。所謂條件語句，就是指程序根據具體條件表達式來執(zhí)行一組命令的結構。Oracle中的條件語句包括ifelse和casewhen兩種形式。第41頁/共60頁12.3.1流程控制——ifelse判斷if語句是一種條件判斷語句，它根據條件判斷的結果執(zhí)行不同的分支語句。if語句的語法結構如圖所示。第42頁/共60頁12.3.1流程控制——ifelse判斷三種if語句的流程圖如圖所示。【示例12-18】在表customersnew中存儲了有關顧客信息的記錄。第43頁/共60頁12.3.1流程控制——ifelse判斷1.if...結構這是if語句中最簡單的結構方式，它只有一個if語句，如果給定的表達式不成立，那么將繼續(xù)向下執(zhí)行。【示例12-19】現欲統計表customersnew中，列status是“gold”的顧客信息，要求輸出統計結果，如果存在貴賓顧客，則輸出“本公司有擁有貴賓會員卡的顧客”，結合使用if...then語句。第44頁/共60頁12.3.1流程控制——ifelse判斷2.ifelse結構該類型的表達式不是選A就是選B。該結構表示要么執(zhí)行if后面的語句，要么執(zhí)行else后面的語句，是二選一的模式。該結構執(zhí)行完畢后，程序會繼續(xù)向后執(zhí)行?！臼纠?2-20】參考上一個示例，現欲統計表customersnew中，列status是“gold”的顧客信息，要求輸出統計結果，如果存在貴賓顧客，則輸出“本公司有擁有貴賓會員卡的顧客”；如果不存在，則輸出“本公司沒人擁有貴賓會員卡”，結合使用if...

else語句。第45頁/共60頁12.3.1流程控制——ifelse判斷3.ifelsif結構該結構是前面兩種使用方式的綜合，它可以提供多個if條件選擇，當程序執(zhí)行到該結構部分時，它會對每一個條件進行判斷，一旦條件為真，程序會執(zhí)行相應的語句，而后繼續(xù)判斷下一個條件，直到所有條件判斷完成。該結構執(zhí)行完畢后，程序會繼續(xù)向后執(zhí)行。【示例12-21】當需要進行判斷的情況多于一種時，可以利用elsif。第46頁/共60頁12.3.2流程控制——casewhen分支case語句與if語句類似，也是根據條件選擇對應的語句執(zhí)行。但是case結構用于多條件分支結構，可以完成計算多個條件并為每個條件返回單個值。可以使流程控制更加清晰。case結構的主要流程圖如圖所示。第47頁/共60頁12.3.2流程控制——casewhen分支1.簡單case語句它給出一個表達式，并把表達式同提供的幾個可預見的結果做比較，若與可預見結果相匹配，則執(zhí)行對應的語句序列，如圖所示。第48頁/共60頁12.3.2流程控制——casewhen分支2.搜索式case語句它會提供多個布爾表達式，然后選擇第一個為true表達式，執(zhí)行對應的腳本，其語法結構如圖所示。第49頁/共60頁12.3.2流程控制——casewhen分支【示例12-22】表customersnew中，列status有“gold”、“Silver”、“Platinum”三種情況，利用顧客編號，結合case語句，輸出對應的的顧客status類型，“gold”對應貴賓卡會員、“Silver”對應銀卡會員、“Platinum”對應金卡會員。【示例12-23】使用搜索式case語句的方式進行改寫上一個示例12-22?！臼纠?2-24】在表order_items中存儲了有關訂單產品的記錄信息。使用搜索式case語句的方式，在數據表order_items中，要求根據order_id得到的對應的價格unit_price，判斷價格屬于哪個范圍并給出輸出提示。第50頁/共60頁12.4

Oracle中的循環(huán)語句所謂循環(huán)語句，即指程序按照指定的邏輯條件循環(huán)執(zhí)行一組命令的語句。Oracle中的循環(huán)語句包括三種方式：無條件循環(huán)、while循環(huán)和for循環(huán)。第51頁/共60頁12.4.1無條件循環(huán)Oracle中，可以使用loop語句對數據進行循環(huán)處理，利用該語句可以循環(huán)執(zhí)行指定的語句序列。其使用語法如圖所示。第52頁/共60頁12.4.1無條件循環(huán)無條件循環(huán)在循環(huán)開始時，不指定循環(huán)條件，但是必須在循環(huán)內部指定跳出循環(huán)的條件，否則，該循環(huán)將一直執(zhí)行，造成死循環(huán)，loop循環(huán)語句的基本流程如圖所示。第53頁/共60頁12.4.1無條件循環(huán)【示例12-26】可以利用無條件循環(huán)輸出custom