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

第12章Oracle編程基礎(chǔ)本章介紹Oracle編程基礎(chǔ)，主要有PL/SQL相關(guān)內(nèi)容和Oracle的控制語句。PL/SQL（Procedural

Language/SQL）是Oracle在SQL標(biāo)準(zhǔn)語言上進行擴展后形成的程序設(shè)計語言，是一種Oracle數(shù)據(jù)庫特有的、支持應(yīng)用開發(fā)的語言。Oracle編程中也需要結(jié)合使用流程控制語句，用于控制PL/SQL語句、語句塊的執(zhí)行過程。12.1PL/SQL簡介PL/SQL是Oracle公司開發(fā)發(fā)的一種種編程語語言，它它是對SQL的擴充。。在PL/SQL程序中，，可以定定義變量量、數(shù)據(jù)據(jù)類型、、函數(shù)以以及過程程，可以以使用流流控制語語句，可可以包含含SQL語句，還還可以進進行錯誤誤處理。。利用PL/SQL程序，可可以對數(shù)數(shù)據(jù)庫進進行復(fù)雜雜的訪問問。12.1.1PL/SQL基本結(jié)構(gòu)構(gòu)PL/SQL是一種結(jié)結(jié)構(gòu)化編編程語言言。程序序的基本本單元是是塊，主主要的塊塊形式有有函數(shù)、、過程和和匿名塊塊。一個個PL/SQL塊由以下下三部分分組成，，其大體體結(jié)構(gòu)如如圖所示示。12.1.1PL/SQL基本結(jié)構(gòu)構(gòu)【示例12-1】輸出語句句“Hello,Lifeforfun,,styleforteens”。12.1.2PL/SQL注釋注釋增強強了程序序的可讀讀性，使使得程序序更易于于理解。。注釋在在進行編編譯時被被PL/SQL編譯器忽忽略。注注釋有單單行注釋釋和多行行注釋兩兩種，這這與許多多高級語語言的注注釋風(fēng)格格是一樣樣的。1.單行注釋釋單行注釋釋由兩個個連字符符（-）開始，，一直到到行尾（（回車符符標(biāo)志著著注釋的的結(jié)束））?！臼纠?2-2】對示例12-1進行單行行注釋。。12.1.2PL/SQL注釋2.多行注釋釋多行注釋釋由/*開頭，由由*/結(jié)尾，這這和C語言是一一樣的。?！臼纠?2-3】多行注釋釋說明。。12.1.3PL/SQL基本規(guī)則則做任何事事情都有有規(guī)范，，編程也也一樣。。在PL/SQL中，也需需要遵守守一些規(guī)規(guī)范，這這樣可以以提高代代碼的可可讀性，，提高工工作效率率。12.1.3PL/SQL基本規(guī)則則1.PL/SQL中允許出出現(xiàn)的字字符集。。字母，包包括大寫寫和小寫寫。數(shù)字，即即0~9。空格、回回車符。。數(shù)學(xué)符號號，包括括+、-、*、/、<、>、=。間隔符，，包括()、{}、[]、?、!、;、:、,、"、@、#、$、%、^、&等。12.1.3PL/SQL基本規(guī)則則2.PL/SQL需要遵守守的要求求。標(biāo)識符是是不區(qū)分分大小寫寫的。標(biāo)識符中中只允許許出現(xiàn)字字母、數(shù)數(shù)字、下下劃線，，并且以以字母開開頭。語句以分分號結(jié)束束。字符型和和日期型型必須使使用括號號括起。。語句的關(guān)關(guān)鍵詞、、標(biāo)識符符12.1.3PL/SQL基本規(guī)則則3.PL/SQL中可以參參考的規(guī)規(guī)范。每行只寫寫一條語語句。所有的過過程名稱稱大寫。。所有的變變量以及及自建的的過程或或游標(biāo)、、觸發(fā)器器名稱都都使用有有意義的的名稱命命名。命名應(yīng)該該以“_”的連接接方式，，也可以以使用大大小寫混混合的方方式。變量前最最好加上上前綴，，以表示示該變量量的數(shù)據(jù)據(jù)類型、、作用范范圍等。。每個變量量都應(yīng)加加上注釋釋。在重要的的程序段段都應(yīng)加加上注釋釋。這些只是是基本的的規(guī)則，，可以提提高代碼碼的可讀讀性。在在日常中中應(yīng)該注注意這些些規(guī)范，，形成良良好的編編程習(xí)慣慣。12.1.4PL/SQL數(shù)據(jù)類型型Oracle定義的數(shù)數(shù)據(jù)類型型很多，，掌握這這些簡單單的數(shù)據(jù)據(jù)類型有有助于編編寫一些些復(fù)雜的的程序。。下面將將對常用用數(shù)據(jù)類類型進行行介紹。。12.1.4PL/SQL數(shù)據(jù)類型型1.數(shù)字類型型數(shù)字類型型變量存存儲整數(shù)數(shù)或者實實數(shù)。它它包含number、pls__integer和binary__integer3種基本類類型。其其中，number類型的變變量可以以存儲整整數(shù)或浮浮點數(shù)，，而pls__integer和binary__integer類型的變變量只存存儲整數(shù)數(shù)。12.1.4PL/SQL數(shù)據(jù)類型型2.字符類型型字符類型型變量用用來存儲儲字符串串或者字字符數(shù)據(jù)據(jù)。其類類型包括括varchar2、char、nchar、nvarchar2等。varchar2：可以存存儲變長長字符串串。char類型表示示定長字字符串。。nchar類型表示示的是存存儲Unicode字符集的的定長字字符集數(shù)數(shù)據(jù)。nvarchar2類型表示示的是存存儲Unicode字符集的的變長字字符集數(shù)數(shù)據(jù)。12.1.4PL/SQL數(shù)據(jù)類型型3.日期類型型日期類型型是用來來存放表表示日期期的數(shù)據(jù)據(jù)的。日日期類型型有date、timestamp。date類型用來來存儲日日期和時時間的。。date類型的時時間精確確到秒。。timestamp類型也是是用來存存儲日期期和時間間信息，，包括世世紀(jì)、年年、月、、天、小小時、分分鐘和秒秒。與date類型的區(qū)區(qū)別就是是在顯示示日期和和時間時時更精確確。timestamp類型可以以精確到到小數(shù)秒秒。使用用timestamp類型存放放日期還還可以顯顯示當(dāng)前前是上午午還是下下午。12.1.4PL/SQL數(shù)據(jù)類型型4.布爾類型型布爾類型型中的唯唯一類型型是boolean，主要用用于控制制程序流流程。一一個布爾爾類型變變量的值值可以是是true、false和null。12.1.4PL/SQL數(shù)據(jù)類型型5.type定義的數(shù)數(shù)據(jù)類型型上面介紹紹了幾種種常用的的數(shù)據(jù)類類型，下下面來介介紹一下下如何定定義數(shù)據(jù)據(jù)類型，，它類似似C語言中的的結(jié)構(gòu)類類型。定定義數(shù)據(jù)據(jù)類型的的語句格格式如圖圖所示。。12.1.5PL/SQL運算符運算符是是一種符符號，用用來指定定要在一一個或者者多個表表達式中中執(zhí)行的的操作。。運算符符一般也也用在執(zhí)執(zhí)行列、、常量或或變量之之間的數(shù)數(shù)學(xué)運算算和比較較操作中中。而表表達式則則是標(biāo)識識符、值值和運算算符的組組合。簡簡單的表表達式可可以是一一個常量量、函數(shù)數(shù)、列名名、變量量和子查查詢?？煽梢杂眠\運算符將將兩個或或更多的的簡單表表達式連連接起來來組成復(fù)復(fù)雜的表表達式。。PL/SQL運算符包包括：算算數(shù)運算算符、位位運算符符、比較較運算符符、邏輯輯運算符符、賦值值運算符符、字符符串連接接符和一一元運算算符。12.1.5PL/SQL運算符1.算術(shù)運算算符算術(shù)運算算符用于于執(zhí)行數(shù)數(shù)字型表表達式的的算術(shù)運運算。在在PL/SQL中支持的的算術(shù)運運算及其其說明如如下所示示。+（加），，加法或或正號。。-（減），，減法或或負號。。*（乘）），乘法法運算。。/（除），，除法運運算。12.1.5PL/SQL運算符2.比較運算算符比較運算算用來比比較兩個個表達式式的大小小。在PL/SQL中可以支支持的比比較運算算符主要要包括以以下幾種種。>，大于。。=，等于。。<，小于。。>=，大于或或等于。。<=，小于或或等于。。<>（!=），不等等于12.1.5PL/SQL運算符3.邏輯運算算符邏輯運算算符用來來對邏輯輯條件進進行測試試，以獲獲得其真真實情況況。它與與比較運運算符一一樣，根根據(jù)測試試結(jié)果返返回布爾爾值true、false、null。邏輯運運算符有有and、or、not、between、like等，具體體的在查查詢一章章中已詳詳細講解解。12.1.5PL/SQL運算符4.字符串連連接符字符竄連連接連接接符“||”用于實實現(xiàn)字符符串之間間的連接接操作，，如圖所所示。12.1.5PL/SQL運算符5.賦值運算算符在PL/SQL中，賦值值運算符符為“:=”，即將將表達式式的值賦賦給一個個變量。。【示例12-4】聲明一個個變量，，并為該該變量賦賦值為““confidence”。12.1.5PL/SQL運算符6.運算符的的優(yōu)先級級Oracle中的運算算符具有有不同的的優(yōu)先級級。同一一個表達達式中包包含不同同的運算算符時，，運算符符的優(yōu)先先級決定定了表達達式的計計算和比比較操作作的順序序。Oracle中各種運運算符的的優(yōu)先級級的順序序如下所所示。括號，()正、負運運算，+、-乘、除運運算，**、/加、減、、字符連連接運算算，+、-、||比較運算算，=、>，<，>=，<=，<>（!=）邏輯非運運算，not邏輯與運運算，andall、any、between、in、like、or、some等運算賦值運算算，:=12.1.5PL/SQL運算符在進行數(shù)數(shù)據(jù)查詢詢時，經(jīng)經(jīng)常需要要對查詢詢到的數(shù)數(shù)據(jù)進行行再次計計算處理理。PL/SQL中允許直直接在select子句中使使用計算算列，計計算列是是通過某某些列的的數(shù)據(jù)進進行演算算得來的的結(jié)果。?！臼纠?2-5】結(jié)合order_items數(shù)據(jù)表，，計算unit_price打9折之后的的值。12.1.6PL/SQL表達式數(shù)據(jù)庫中中經(jīng)常使使用表達達式來計計算結(jié)果果，尤其其在變量量和常量量的使用用過程中中。在前前面已經(jīng)經(jīng)接觸過過表達式式的使用用，它和和普通編編程語言言的表達達式類似似。本小小節(jié)系統(tǒng)統(tǒng)地介紹紹表達式式的類型型以及如如何使用用表達式式。1.表達式簡簡介表達式不不能獨立立構(gòu)成語語句，表表達式的的結(jié)果是是一個值值。如果果不給這這個值安安排一個個存放的的位置，，則表達達式本身身毫無意意義。通通常，表表達式作作為賦值值語句的的一部分分出現(xiàn)在在賦值值值運算符符的右邊邊，或者者作為函函數(shù)的參參數(shù)等。。12.1.6PL/SQL表達式2.字符和數(shù)數(shù)值表達達式（1）數(shù)值表表達式是是由數(shù)值值類型的的常量、、變量以以及函數(shù)數(shù)，由運運算符連連接而成成。在PL/SQL中，可以以使用的的算數(shù)運運算符有有。加號+。減號-。乘號*。。除號。（2）對于字字符表達達式，唯唯一的運運算符就就是“||”，它的的作用是是把幾個個字符串串連接在在一起，，如表達達式：'wonderful''||''life'|||'..'的值就相相等于'wonderfullife'。12.1.6PL/SQL表達式3.關(guān)系表達達式和邏邏輯表達達式（1）關(guān)系表表達式是是將字符符或數(shù)值值用關(guān)系系運算符符連接起起來。（2）邏輯表表達式是是由邏輯輯符號和和常量或或變量等等組成的的表達式式。12.1.7PL/SQL中變量、、常量的的定義與與使用在PL/SQL塊中可以以定義變變量、常常量和數(shù)數(shù)據(jù)類型型，這使使得PL/SQL塊對數(shù)據(jù)據(jù)的處理理更加靈靈活。變變量、常常量和類類型的定定義放在在PL/SQL塊的變量量聲明部部分。12.1.7PL/SQL中變量、、常量的的定義與與使用1.變量和常常量在PL/SQL程序運行行時，需需要定義義一些變變量來存存放一些些數(shù)據(jù)。。（1）定義常常量定義常量量的語句句格式如如圖所示示。12.1.7PL/SQL中變量、、常量的的定義與與使用（2）定義變變量變量的定定義有兩兩種格式式，如圖圖所示。?！臼纠?2-6】定義了一一個有關(guān)關(guān)住址的的變量，，它是變變長字符符型，最最大長度度為20個字符。。12.1.7PL/SQL中變量、、常量的的定義與與使用（3）變量初初始化變量在定定義時可可以指定定默認值值或初始始值，在在PL/SQL塊的運行行過程中中還可以以為其賦賦值。【示例12-7】定義三個個變量id、name、hiredate，演示變變量的聲聲明、賦賦值和輸輸出操作作?！臼纠?2-8】在上個示示例中，，已經(jīng)了了定義三三個變量量，id、name、hiredate，現(xiàn)在結(jié)結(jié)合%type屬性方法法，添加加一個變變量是leavedate，該變量量的數(shù)據(jù)據(jù)類型與與hiredate相同。12.2PL/SQL中定義類類型在前面簡簡單講解解了type定義的數(shù)數(shù)據(jù)類型型?，F(xiàn)在在講解常常用的自自定義類類型：記記錄類型型和集合合類型。。它們都都是復(fù)合合數(shù)據(jù)類類型。12.2.1記錄類型型記錄類型型允許在在一個類類型中包包含若干干類型不不同的字字段，字字段類型型可以是是基本數(shù)數(shù)據(jù)類型型，也可可以是另另一個復(fù)復(fù)合數(shù)據(jù)據(jù)類型。。記錄類類型的定定義格式式如圖所所示。12.2.1記錄類型型【示例12-9】例如，要要存儲顧顧客的信信息，可可以定義義一個記記錄類型型，包括括姓名、、電話、、地址、、記錄日日期等字字段?！臼纠?2-10】定義了兩兩個shopper類型的變變量，分分別表示示兩個顧顧客?！臼纠?2-11】在定義上上述類型型和變量量的基礎(chǔ)礎(chǔ)上，為為記錄類類型中的的字段賦賦值，并并輸出結(jié)結(jié)構(gòu)?！臼纠?2-12】根據(jù)customers表的結(jié)構(gòu)構(gòu)，可以以定義記記錄類型型變量cus。【示例12-13】根據(jù)記錄錄類型變變量cus，為customer_id、cust_first_name，city這三個字字段賦值值，并輸輸出其字字段值。。12.2.2集合類型型記錄類型型變量中中包含若若干類型型不同的的數(shù)據(jù)，，而集合合類型變變量中包包含多個個相同類類型的元元素。要要創(chuàng)建一一個集合合，先要要定義一一個集合合類型，，然后再再定義該該類型的的集合變變量。定定義集合合類型的的語法如如圖所示示。12.2.2集合類型型【示例12-14】定義了一一個集合合類型，，元素類類型是整整數(shù)?！臼纠?2-15】定義兩個個集合變變量begins1和begins2，并分別別調(diào)用構(gòu)構(gòu)造函數(shù)數(shù)進行初初始化，，begins1中包含三三個元素素，begins2是一個空空集合，，不包含含任何元元素。集合類型型類似于于面向?qū)ο蠹夹g(shù)術(shù)中的類類，除了了可以使使用構(gòu)造造函數(shù)外外，還有有一些方方法可以以用來對對集合進進行操作作。12.2.2集合類型型集合的部部分方法法如表所所示。方法功能extend(m,n)將集合的第n個元素追加到集合末尾共m次，如果不指定n，則追加m個空元素。若不指定m和n，則追加一個空元素count返回集合中當(dāng)前包含的元素的個數(shù)first返回第一個元素的下標(biāo)last返回最后一個元素的下標(biāo)next(n)返回第n個元素之后的元素的下標(biāo)prior(n)返回第n個元素之前的元素的下標(biāo)12.2.2集合類型型【示例12-16】結(jié)合兩個個集合變變量begins1和begins2，演示集集合的使使用方法法。除了上述述定義集集合類型型的方法法，還有有一種方方法，其其語法結(jié)結(jié)構(gòu)如圖圖所示。。12.2.2集合類型型【示例12-17】使用第二二種定義義集合類類型方法法，定義義兩個集集合變量量begins3，并對該該變量進進行賦值值。12.3Oracle中的條件件控制語語句在Oracle數(shù)據(jù)庫中中，PL/SQL提供了豐豐富的流流控制語語句，用用來對程程序的執(zhí)執(zhí)行流程程進行控控制。通通過控制制語句，，我們可可以編寫寫更復(fù)雜雜的PL/SQL塊。流控控制語句句分為兩兩類，即即條件判判斷語句句和循環(huán)環(huán)語句。。所謂條條件語句句，就是是指程序序根據(jù)具具體條件件表達式式來執(zhí)行行一組命命令的結(jié)結(jié)構(gòu)。Oracle中的條件件語句包包括ifelse和casewhen兩種形式式。12.3.1流程控制制——ifelse判斷if語句是一一種條件件判斷語語句，它它根據(jù)條條件判斷斷的結(jié)果果執(zhí)行不不同的分分支語句句。if語句的語語法結(jié)構(gòu)構(gòu)如圖所所示。12.3.1流程控制制——ifelse判斷三種if語句的流流程圖如如圖所示示?！臼纠?2-18】在表customersnew中存儲了了有關(guān)顧顧客信息息的記錄錄。12.3.1流程控制制——ifelse判斷1.if....結(jié)構(gòu)這是if語句中最最簡單的的結(jié)構(gòu)方方式，它它只有一一個if語句，如如果給定定的表達達式不成成立，那那么將繼繼續(xù)向下下執(zhí)行。?！臼纠?2-19】現(xiàn)欲統(tǒng)計計表customersnew中，列status是“gold”的顧客客信息，，要求輸輸出統(tǒng)計計結(jié)果，，如果存存在貴賓賓顧客，，則輸出出“本公公司有擁擁有貴賓賓會員卡卡的顧客客”，結(jié)結(jié)合使用用if....then語句。12.3.1流程控制制——ifelse判斷2.ifelse結(jié)構(gòu)該類型的的表達式式不是選選A就是選B。該結(jié)構(gòu)構(gòu)表示要要么執(zhí)行行if后面的語語句，要要么執(zhí)行行else后面的語語句，是是二選一一的模式式。該結(jié)結(jié)構(gòu)執(zhí)行行完畢后后，程序序會繼續(xù)續(xù)向后執(zhí)執(zhí)行。【示例12-20】參考上一一個示例例，現(xiàn)欲欲統(tǒng)計表表customersnew中，列status是“gold”的顧客客信息，，要求輸輸出統(tǒng)計計結(jié)果，，如果存存在貴賓賓顧客，，則輸出出“本公公司有擁擁有貴賓賓會員卡卡的顧客客”；如如果不存存在，則則輸出““本公司司沒人擁擁有貴賓賓會員卡卡”，結(jié)結(jié)合使用用if....else語句。12.3.1流程控制制——ifelse判斷3.ifelsif結(jié)構(gòu)該結(jié)構(gòu)是是前面兩兩種使用用方式的的綜合，，它可以以提供多多個if條件選擇擇，當(dāng)程程序執(zhí)行行到該結(jié)結(jié)構(gòu)部分分時，它它會對每每一個條條件進行行判斷，，一旦條條件為真真，程序序會執(zhí)行行相應(yīng)的的語句，，而后繼繼續(xù)判斷斷下一個個條件，，直到所所有條件件判斷完完成。該該結(jié)構(gòu)執(zhí)執(zhí)行完畢畢后，程程序會繼繼續(xù)向后后執(zhí)行。?！臼纠?2-21】當(dāng)需要進進行判斷斷的情況況多于一一種時，，可以利利用elsif。12.3.2流程控制制——casewhen分支case語句與if語句類似似，也是是根據(jù)條條件選擇擇對應(yīng)的的語句執(zhí)執(zhí)行。但但是case結(jié)構(gòu)用于于多條件件分支結(jié)結(jié)構(gòu)，可可以完成成計算多多個條件件并為每每個條件件返回單單個值。?？梢允故沽鞒炭乜刂聘蛹忧逦?。。case結(jié)構(gòu)的主主要流程程圖如圖圖所示。。12.3.2流程控制制——casewhen分支1.簡單case語句它給出一一個表達達式，并并把表達達式同提提供的幾幾個可預(yù)預(yù)見的結(jié)結(jié)果做比比較，若若與可預(yù)預(yù)見結(jié)果果相匹配配，則執(zhí)執(zhí)行對應(yīng)應(yīng)的語句句序列，，如圖所所示。12.3.2流程控制制——casewhen分支2.搜索式case語句它會提供供多個布布爾表達達式，然然后選擇擇第一個個為true表達式，，執(zhí)行對對應(yīng)的腳腳本，其其語法結(jié)結(jié)構(gòu)如圖圖所示。。12.3.2流程控制制——casewhen分支【示例12-22】表customersnew中，列status有“gold”、“Silver”、“Platinum”三種情情況，利利用顧客客編號，，結(jié)合case語句，輸輸出對應(yīng)應(yīng)的的顧顧客status類型，““gold”對應(yīng)貴貴賓卡會會員、““Silver”對應(yīng)銀銀卡會員員、“Platinum”對應(yīng)金金卡會員員。【示例12-23】使用搜索索式case語句的方方式進行行改寫上上一個示示例12-22?！臼纠?2-24】在表order_items中存儲了了有關(guān)訂訂單產(chǎn)品品的記錄錄信息。。使用搜搜索式case語句的方方式，在在數(shù)據(jù)表表order_items中，要求求根據(jù)order_id得到的對對應(yīng)的價價格unit_price，判斷價價格屬于于哪個范范圍并給給出輸出出提示。。12.4Oracle中的循環(huán)環(huán)語句所謂循環(huán)環(huán)語句，，即指程程序按照照指定的的邏輯條條件循環(huán)環(huán)執(zhí)行一一組命令令的語句句。Oracle中的循環(huán)環(huán)語句包包括三種種方式：：無條件件循環(huán)、、while循環(huán)和for循環(huán)。12.4.1無條件循循環(huán)Oracle中，可以以使用loop語句對數(shù)數(shù)據(jù)進行行循環(huán)處處理，利利用該語語句可以以循環(huán)執(zhí)執(zhí)行指定定的語句句序列。。其使用用語法如如圖所示示。12.4.1無條件循循環(huán)無條件循循環(huán)在循循環(huán)開始始時，不不指定循循環(huán)條件件，但是是必須在在循環(huán)內(nèi)內(nèi)部指定定跳出循循環(huán)的條條件，否否則，該該循環(huán)將將一直執(zhí)執(zhí)行，造造成死循循環(huán)，loop循環(huán)語句句的基本本流程如如圖所示示。12.4.1無條件循循環(huán)【示例12-26】可以利用用無條件件循環(huán)輸輸出customersnew表中，顧顧客編號號在101~~112范圍之間間的所有有顧客姓姓名。【示例12-27】可以利用用exitwhen形式跳出出loop循環(huán)。12.4.2while循環(huán)while循環(huán)與基基本的loop循環(huán)比較較的話，，它可以以結(jié)束l