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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

PL/SQL表達(dá)式3.關(guān)系表達(dá)式和邏輯表達(dá)式（1）關(guān)系表達(dá)式是將字符或數(shù)值用關(guān)系運(yùn)算符連接起來。（2）邏輯表達(dá)式是由邏輯符號(hào)和常量或變量等組成的表達(dá)式。第28頁/共60頁12.1.7

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

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

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

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

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

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

else語句。第45頁/共60頁12.3.1流程控制——ifelse判斷3.ifelsif結(jié)構(gòu)該結(jié)構(gòu)是前面兩種使用方式的綜合，它可以提供多個(gè)if條件選擇，當(dāng)程序執(zhí)行到該結(jié)構(gòu)部分時(shí)，它會(huì)對(duì)每一個(gè)條件進(jìn)行判斷，一旦條件為真，程序會(huì)執(zhí)行相應(yīng)的語句，而后繼續(xù)判斷下一個(gè)條件，直到所有條件判斷完成。該結(jié)構(gòu)執(zhí)行完畢后，程序會(huì)繼續(xù)向后執(zhí)行?！臼纠?2-21】當(dāng)需要進(jìn)行判斷的情況多于一種時(shí)，可以利用elsif。第46頁/共60頁12.3.2流程控制——casewhen分支case語句與if語句類似，也是根據(jù)條件選擇對(duì)應(yīng)的語句執(zhí)行。但是case結(jié)構(gòu)用于多條件分支結(jié)構(gòu)，可以完成計(jì)算多個(gè)條件并為每個(gè)條件返回單個(gè)值?？梢允沽鞒炭刂聘忧逦?。case結(jié)構(gòu)的主要流程圖如圖所示。第47頁/共60頁12.3.2流程控制——casewhen分支1.簡(jiǎn)單case語句它給出一個(gè)表達(dá)式，并把表達(dá)式同提供的幾個(gè)可預(yù)見的結(jié)果做比較，若與可預(yù)見結(jié)果相匹配，則執(zhí)行對(duì)應(yīng)的語句序列，如圖所示。第48頁/共60頁12.3.2流程控制——casewhen分支2.搜索式case語句它會(huì)提供多個(gè)布爾表達(dá)式，然后選擇第一個(gè)為true表達(dá)式，執(zhí)行對(duì)應(yīng)的腳本，其語法結(jié)構(gòu)如圖所示。第49頁/共60頁12.3.2流程控制——casewhen分支【示例12-22】表customersnew中，列status有“gold”、“Silver”、“Platinum”三種情況，利用顧客編號(hào)，結(jié)合case語句，輸出對(duì)應(yīng)的的顧客status類型，“gold”對(duì)應(yīng)貴賓卡會(huì)員、“Silver”對(duì)應(yīng)銀卡會(huì)員、“Platinum”對(duì)應(yīng)金卡會(huì)員?！臼纠?2-23】使用搜索式case語句的方式進(jìn)行改寫上一個(gè)示例12-22?！臼纠?2-24】在表order_items中存儲(chǔ)了有關(guān)訂單產(chǎn)品的記錄信息。使用搜索式case語句的方式，在數(shù)據(jù)表order_items中，要求根據(jù)order_id得到的對(duì)應(yīng)的價(jià)格unit_price，判斷價(jià)格屬于哪個(gè)范圍并給出輸出提示。第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語句對(duì)數(shù)據(jù)進(jìn)行循環(huán)處理，利用該語句可以循環(huán)執(zhí)行指定的語句序列。其使用語法如圖所示。第52頁/共60頁12.4.1無條件循環(huán)無條件循環(huán)在循環(huán)開始時(shí)，不指定循環(huán)條件，但是必須在循環(huán)內(nèi)部指定跳出循環(huán)的條件，否則，該循環(huán)將一直執(zhí)行，造成死循環(huán)，loop循環(huán)語句的基本流程如圖所示。第53頁/共60頁12.4.1無條件循環(huán)【示例12-26】可以利用無條件循環(huán)輸出custom