第6章PL/SQLPL/SQL概述01PL/SQL的組成PL/SQL的組成（1）數(shù)據(jù)定義語(yǔ)言（DDL）。用于執(zhí)行數(shù)據(jù)庫(kù)的任務(wù)，對(duì)數(shù)據(jù)庫(kù)及其中的各種對(duì)象進(jìn)行創(chuàng)建、刪除、修改等操作，其基本的DDL命令及功能如表。語(yǔ)

句功

能說(shuō)

明CREATE創(chuàng)建數(shù)據(jù)庫(kù)或數(shù)據(jù)庫(kù)對(duì)象不同數(shù)據(jù)庫(kù)對(duì)象，其CREATE語(yǔ)句的語(yǔ)法形式不同ALTER對(duì)數(shù)據(jù)庫(kù)或數(shù)據(jù)庫(kù)對(duì)象進(jìn)行修改不同數(shù)據(jù)庫(kù)對(duì)象，其ALTER語(yǔ)句的語(yǔ)法形式不同DROP刪除數(shù)據(jù)庫(kù)或數(shù)據(jù)庫(kù)對(duì)象不同數(shù)據(jù)庫(kù)對(duì)象，其DROP語(yǔ)句的語(yǔ)法形式不同（2）數(shù)據(jù)操縱語(yǔ)言（DML）。用于操縱數(shù)據(jù)庫(kù)中各種對(duì)象、檢索和修改數(shù)據(jù)。需要注意的是，使用DML語(yǔ)句對(duì)某個(gè)數(shù)據(jù)對(duì)象進(jìn)行操作時(shí)，要求必須擁有該對(duì)象的相應(yīng)操作權(quán)限或系統(tǒng)權(quán)限。DML包括的主要語(yǔ)句及功能如表。語(yǔ)

句功

能說(shuō)

明SELECT從表或視圖中檢索數(shù)據(jù)使用最頻繁的SQL語(yǔ)句之一INSERT將數(shù)據(jù)插入到表或視圖中

UPDATE修改表或視圖中的數(shù)據(jù)既可修改表或視圖的一行數(shù)據(jù)，也可修改一組或全部數(shù)據(jù)DELETE從表或視圖中刪除數(shù)據(jù)可根據(jù)條件刪除指定的數(shù)據(jù)PL/SQL的組成（3）數(shù)據(jù)控制語(yǔ)言（DCL）。用于安全管理，確定哪些用戶(hù)可以查看或修改數(shù)據(jù)庫(kù)中的數(shù)據(jù)。DCL包括的主要語(yǔ)句及功能如表。語(yǔ)

句功

能說(shuō)

明GRANT授予權(quán)限將語(yǔ)句許可或?qū)ο笤S可的權(quán)限授予其他用戶(hù)和角色REVOKE收回權(quán)限不影響該用戶(hù)或角色從其他角色中作為成員繼承許可權(quán)限PL/SQL的組成PL/SQL是面向過(guò)程語(yǔ)言與SQL語(yǔ)言的結(jié)合，它在SQL語(yǔ)言中擴(kuò)充了面向過(guò)程的程序結(jié)構(gòu)，如變量和類(lèi)型、控制語(yǔ)句、過(guò)程和函數(shù)、對(duì)象類(lèi)型和方法等，實(shí)現(xiàn)了將過(guò)程結(jié)構(gòu)與OracleSQL的無(wú)縫集成，從而為用戶(hù)提供了一種功能強(qiáng)大的結(jié)構(gòu)化程序設(shè)計(jì)語(yǔ)言。例如，要在數(shù)據(jù)庫(kù)中修改一個(gè)學(xué)生的記錄，如果沒(méi)有該學(xué)生的記錄，就要為該學(xué)生創(chuàng)建一個(gè)新的記錄。用PL/SQL編制的程序代碼如下：DECLARE xhvarchar2(6):='151302'; xmvarchar2(8):='張瓊丹'; zxfnumber(2):=45; /*定義變量類(lèi)型*/BEGIN UPDATExsb SET姓名=xm,總學(xué)分=zxf WHERE學(xué)號(hào)=xh; /*更新學(xué)生表*/ IFSQL%NOTFOUNDTHEN /*檢查記錄是否存在，如果不存在就插入記錄*/ INSERTINTOxsb(學(xué)號(hào),姓名,性別,出生時(shí)間,專(zhuān)業(yè),總學(xué)分) VALUES(xh,xm,'女',TO_DATE('19970516','YYYYMMDD'),'軟件工程',zxf); ENDIF;END;/PL/SQL的組成PL/SQL通過(guò)擴(kuò)展SQL功能可更加強(qiáng)大，同時(shí)使用也更加方便。用戶(hù)能夠使用PL/SQL更加靈活地操作數(shù)據(jù)，因?yàn)樗С炙械腟QL數(shù)據(jù)操作語(yǔ)句、事務(wù)控制語(yǔ)句、函數(shù)和操作符。PL/SQL同樣也支持動(dòng)態(tài)SQL，能夠動(dòng)態(tài)執(zhí)行SQL數(shù)據(jù)定義、數(shù)據(jù)控制和會(huì)話控制語(yǔ)句。使用PL/SQL主要有以下好處。（1）有利于客戶(hù)/服務(wù)器環(huán)境應(yīng)用的運(yùn)行。對(duì)于客戶(hù)/服務(wù)器環(huán)境來(lái)說(shuō)，真正的瓶頸在網(wǎng)絡(luò)上。無(wú)論網(wǎng)絡(luò)的傳輸速度有多快，只要客戶(hù)端與服務(wù)器進(jìn)行大量數(shù)據(jù)交換，應(yīng)用運(yùn)行的效率肯定會(huì)受到影響。如果使用PL/SQL進(jìn)行編程，將這種具有大量數(shù)據(jù)處理的應(yīng)用放在服務(wù)器端執(zhí)行，就可節(jié)省數(shù)據(jù)在網(wǎng)絡(luò)中的傳輸時(shí)間。（2）適合于客戶(hù)環(huán)境。PL/SQL分為數(shù)據(jù)庫(kù)PL/SQL和工具PL/SQL。對(duì)于客戶(hù)端來(lái)說(shuō)，PL/SQL可以嵌入到相應(yīng)的工具中，客戶(hù)端程序可以執(zhí)行本地包含PL/SQL的部分，也可以向服務(wù)器端發(fā)送SQL命令，或激活服務(wù)器端的PL/SQL程序運(yùn)行。02PL/SQL的特點(diǎn)PL/SQL的特點(diǎn)Oracle系統(tǒng)對(duì)PL/SQL進(jìn)行了擴(kuò)展，大大增強(qiáng)了SQL的功能，其主要體現(xiàn)在以下方面。（1）SQL和PL/SQL編譯器集成PL/SQL，支持SQL所有范圍的語(yǔ)法，如INSERT、UPDATE、DELETE等。（2）支持CASE語(yǔ)句和表達(dá)式。（3）繼承和動(dòng)態(tài)方法釋放。（4）類(lèi)型進(jìn)化。屬性和方法既可以添加到對(duì)象類(lèi)型中，也可以從對(duì)象類(lèi)型中刪除，不需要重新構(gòu)建類(lèi)型和響應(yīng)數(shù)據(jù)。（5）新的日期/時(shí)間類(lèi)型。新的數(shù)據(jù)類(lèi)型TIMESTAMP記錄包括秒的時(shí)間值，新的數(shù)據(jù)類(lèi)型TIMESTAMPWITHTIMEZONE和TIMESTAMPWITHLOCALTIMEZONE可以根據(jù)時(shí)區(qū)不同來(lái)糾正日期和時(shí)間值。（6）PL/SQL代碼的本地編譯。使用典型的C語(yǔ)言開(kāi)發(fā)工具，可將Oracle系統(tǒng)提供的和用戶(hù)編寫(xiě)的存儲(chǔ)過(guò)程編譯為本地執(zhí)行語(yǔ)句，從而提高性能。（7）增強(qiáng)了對(duì)國(guó)際化語(yǔ)言的支持。（8）表函數(shù)和游標(biāo)表達(dá)式。指可以像表一樣返回一個(gè)查詢(xún)結(jié)果集合。結(jié)果集合可以從一個(gè)函數(shù)傳遞給另一個(gè)函數(shù)。PL/SQL的特點(diǎn)（9）多層集合。用戶(hù)可以嵌套集合類(lèi)型，例如，創(chuàng)建PL/SQL的VARRAY表，并可以創(chuàng)建復(fù)雜的數(shù)據(jù)結(jié)構(gòu)。（10）可更好地對(duì)LOB類(lèi)型進(jìn)行集成。它可以像操作其他類(lèi)型一樣操作LOB類(lèi)型，并在CLOB類(lèi)型和NCLOB類(lèi)型上使用函數(shù)，將BLOB類(lèi)型作為RAW。（11）對(duì)批操作的增強(qiáng)。用戶(hù)可以使用本地動(dòng)態(tài)SQL執(zhí)行批操作（如批提取），同時(shí)也可以執(zhí)行批插入和更新操作。（12）MERGE語(yǔ)句。這是一個(gè)將插入和更新合并為單項(xiàng)操作的專(zhuān)用語(yǔ)句，主要用于數(shù)據(jù)倉(cāng)庫(kù)，執(zhí)行特定模式的插入和更新。PL/SQL的特點(diǎn)用戶(hù)可以使用SQL語(yǔ)句操作Oracle11g數(shù)據(jù)庫(kù)和用于處理數(shù)據(jù)的流程控制語(yǔ)句，而且可以聲明變量和常量、定義過(guò)程和函數(shù)、跟蹤運(yùn)行錯(cuò)誤。PL/SQL將SQL的數(shù)據(jù)操縱功能與過(guò)程語(yǔ)言的數(shù)據(jù)處理功能結(jié)合在一起。此外，PL/SQL還具有以下特性。（1）數(shù)據(jù)抽象。數(shù)據(jù)抽象可以從數(shù)據(jù)結(jié)構(gòu)中提取必要的屬性，忽略不必要的細(xì)節(jié)。一旦設(shè)計(jì)了數(shù)據(jù)結(jié)構(gòu)，就可以忽略其細(xì)節(jié)，從而設(shè)計(jì)操縱數(shù)據(jù)結(jié)構(gòu)的算法。（2）信息隱藏。使用信息隱藏功能，可以使用戶(hù)只能看到算法和數(shù)據(jù)結(jié)構(gòu)設(shè)計(jì)所給定層次上的信息。03PL/SQL的開(kāi)發(fā)和運(yùn)行環(huán)境PL/SQL的開(kāi)發(fā)和運(yùn)行環(huán)境PL/SQL的編譯和運(yùn)行系統(tǒng)是一項(xiàng)技術(shù)而不是一個(gè)獨(dú)立的產(chǎn)品，它能夠駐留在Oracle數(shù)據(jù)庫(kù)服務(wù)器和Oracle系統(tǒng)開(kāi)發(fā)工具兩個(gè)環(huán)境中，PL/SQL與Oracle系統(tǒng)的服務(wù)器捆綁在一起。在這兩個(gè)環(huán)境中，PL/SQL的引擎可接收任何PL/SQL塊和子程序作為輸入，引擎執(zhí)行過(guò)程語(yǔ)句將SQL語(yǔ)句發(fā)送給Oracle11g服務(wù)器的執(zhí)行器執(zhí)行。第6章PL/SQLPL/SQL字符集01合法字符合法字符在使用PL/SQL進(jìn)行程序設(shè)計(jì)時(shí)，可以使用的有效字符包括以下三類(lèi)。（1）所有的大寫(xiě)和小寫(xiě)英文字母。（2）數(shù)字0～9。（3）符號(hào)()、+、－、*、/、<、>、=、!、~、;、:、.、'、@、%、,、＂、#、＾、&、_、{、}、?、[、]。02運(yùn)

算

符1．算術(shù)運(yùn)算符2．關(guān)系運(yùn)算符3．邏輯運(yùn)算符運(yùn)

算

符1．算術(shù)運(yùn)算符算術(shù)運(yùn)算符執(zhí)行算術(shù)運(yùn)算。算術(shù)運(yùn)算符有﹢（加）、﹣（減）、*（乘）、/（除）、**（指數(shù)）和‖（連接字符）。其中﹢和﹣也可用于對(duì)DATE（日期）數(shù)據(jù)類(lèi)型的值進(jìn)行運(yùn)算。【例6.1】

求學(xué)生的年齡。SELECTEXTRACT(YEARFROMSYSDATE)-EXTRACT(YEARFROM出生時(shí)間)AS年齡 FROMxsb;其中，SYSDATE是當(dāng)前系統(tǒng)時(shí)間；EXTRACT用于從日期類(lèi)型數(shù)據(jù)中抽出年、月、日的部分，YEAR即表示抽出的年份。執(zhí)行結(jié)果如圖。運(yùn)

算

符2．關(guān)系運(yùn)算符關(guān)系運(yùn)算符（比較運(yùn)算符）有以下五種：（1）（2）（3）（4）（5）﹦（等于）、<>或!=（不等于）、<（小于）、>（大于）、>=（大于或等于）、<=（小于或等于）；BETWEEN…AND…（檢索兩值之間的內(nèi)容）；IN（檢索匹配列表中的值）；LIKE（檢索匹配字符樣式的數(shù)據(jù)）；ISNULL（檢索空數(shù)據(jù)）。運(yùn)

算

符【例6.2】

關(guān)系運(yùn)算符的應(yīng)用。①查詢(xún)總學(xué)分在40～50分的學(xué)生學(xué)號(hào)、姓名和總學(xué)分。SELECT學(xué)號(hào),姓名,總學(xué)分 FROMxsb WHERE總學(xué)分BETWEEN40AND50;②使用>=和<=代替BETWEEN實(shí)現(xiàn)與上例相同的功能。SELECT學(xué)號(hào),姓名,總學(xué)分 FROMxsb WHERE總學(xué)分>=40AND總學(xué)分<=50;執(zhí)行結(jié)果如圖。運(yùn)

算

符3．邏輯運(yùn)算符邏輯運(yùn)算符用于對(duì)某個(gè)條件進(jìn)行測(cè)試，運(yùn)算結(jié)果為T(mén)RUE或FALSE。Oracle系統(tǒng)提供的邏輯運(yùn)算符如下。（1）AND（兩個(gè)表達(dá)式同時(shí)為真時(shí)，則結(jié)果為真）。（2）OR（只要有一個(gè)為真時(shí)，則結(jié)果為真）。（3）NOT（取相反的邏輯值）。【例6.3】

邏輯運(yùn)算符的應(yīng)用。①查詢(xún)總學(xué)分不在40～50分的學(xué)生學(xué)號(hào)、姓名和總學(xué)分。SELECT學(xué)號(hào),姓名,總學(xué)分 FROMxsb WHERE總學(xué)分NOTBETWEEN40AND50;執(zhí)行結(jié)果如圖。運(yùn)

算

符②查詢(xún)計(jì)算機(jī)專(zhuān)業(yè)男生和通信工程專(zhuān)業(yè)女生的基本情況。SELECT學(xué)號(hào),姓名,專(zhuān)業(yè),總學(xué)分 FROMxsb WHERE專(zhuān)業(yè)='計(jì)算機(jī)'AND性別='男'OR專(zhuān)業(yè)='通信工程'AND性別='女';執(zhí)行結(jié)果如圖。03其他常用符號(hào)

其他常用符號(hào)

PL/SQL為支持編程，還使用了其他一些符號(hào)。如表所示列出了部分符號(hào)，它們是使用PL/SQL時(shí)要必須了解的。符

號(hào)意

義樣

例()列表分隔('Jones','Rose','Owen');語(yǔ)句結(jié)束過(guò)程名(參數(shù)1,參數(shù)2);.項(xiàng)分離（在例子中分離賬戶(hù)與表名）Select*from賬戶(hù)名.表名'字符串界定符Ifvar1='a+1':=賦值a:=a+1||并置全名:='Narth'||''||'Yebba'--注釋符--thisisacomment/*與*/注釋定界符/*thistooisacomment*/第6章PL/SQLPL/SQL變量、常量和數(shù)據(jù)類(lèi)型01變

量1．變量的聲明2．變量的屬性3．變量的作用域變

量1．變量的聲明變量是在PL/SQL塊的聲明部分定義的。每個(gè)變量都有一個(gè)特定的類(lèi)型，變量的類(lèi)型定義了變量可以存放的信息類(lèi)別。PL/SQL

的變量可以與數(shù)據(jù)庫(kù)列具有同樣的類(lèi)型。此外，PL/SQL還支持用戶(hù)自定義的數(shù)據(jù)類(lèi)型，如記錄類(lèi)型、表類(lèi)型等。使用用戶(hù)自定義的數(shù)據(jù)類(lèi)型，可以讓用戶(hù)訂制程序中使用的數(shù)據(jù)類(lèi)型結(jié)構(gòu)。下面是一個(gè)用戶(hù)自定義記錄類(lèi)型的例子。DECLARETYPEt_xsRecord( xhchar(6), xmchar(8), xbchar(2), zychar(12));v_xst_xsRecord變

量變量名必須是一個(gè)合法的標(biāo)識(shí)符，其命名規(guī)則如下：（1）（2）（3）（4）變量必須以字母（A～Z）開(kāi)頭；變量后跟可選的一個(gè)或多個(gè)字母、數(shù)字或特殊字符$、#或_；變量長(zhǎng)度不超過(guò)30個(gè)字符；變量名中不能有空格。變

量如表所示給出的變量名實(shí)例并評(píng)價(jià)了其合法性。變

量

名是否合法原

因Name2合法

90ora不合法必須以字母開(kāi)頭p_count合法

xs-count不合法使用了不合法的特殊字符-kcmc不合法不能含有空格menoy￥不合法使用了不合法的字符￥在使用變量前，首先要聲明變量。變量定義的基本格式為：

變量名數(shù)據(jù)類(lèi)型[(寬度):=初始值];例如，定義一個(gè)長(zhǎng)度為10的變量count，其初始值為1，類(lèi)型為varchar2：countvarchar2(10):='1';變

量2．變量的屬性（1）%TYPE。%TYPE屬性提供了變量和數(shù)據(jù)庫(kù)列的數(shù)據(jù)類(lèi)型。在聲明一個(gè)包含數(shù)據(jù)庫(kù)值的變量時(shí)非常有用。例如，在XSB表中包含“學(xué)號(hào)”列，為了聲明一個(gè)變量my_xh與“學(xué)號(hào)”列具有相同的數(shù)據(jù)類(lèi)型，聲明時(shí)可使用點(diǎn)和%TYPE屬性，其格式為：my_xhXSB.學(xué)號(hào)%TYPE;使用%TYPE聲明具有以下兩個(gè)優(yōu)點(diǎn)：①不必知道“學(xué)號(hào)”列確切的數(shù)據(jù)類(lèi)型；②如果改變了“學(xué)號(hào)”列的數(shù)據(jù)定義，my_xh的數(shù)據(jù)類(lèi)型在運(yùn)行時(shí)也會(huì)自動(dòng)更改。變

量（2）%ROWTYPE。使用%ROWTYPE屬性聲明可以描述表的行數(shù)據(jù)記錄，對(duì)于用戶(hù)定義的記錄，必須聲明自己的域。記錄包含唯一的命名域，具有不同的數(shù)據(jù)類(lèi)型。DECLARETYPETimeRecISRECORD(HHnumber(2),MMnumber(2));TYPEMeetingTypISRECORD( Meeting_Datedate, Meeting_TimeTimeRec, Meeting_Addrvarchar2(20), Meeting_Purposevarchar2(50))變

量在PL/SQL中，記錄用于將數(shù)據(jù)分組，一個(gè)記錄包含幾個(gè)相關(guān)的域，這些域用于存儲(chǔ)數(shù)據(jù)。%ROWTYPE屬性提供了表示一個(gè)表中一行的記錄類(lèi)型。這個(gè)記錄可以存儲(chǔ)整個(gè)從表中所選，或者從游標(biāo)中提取的數(shù)據(jù)行。行中的列和記錄相對(duì)應(yīng)，具有相同的名稱(chēng)和數(shù)據(jù)類(lèi)型。例如，聲明一個(gè)記錄名為cj_rec，它與CJB表具有相同的名稱(chēng)和數(shù)據(jù)類(lèi)型，其格式為：DECLARE cj_recCJB%ROWTYPE;可以使用點(diǎn)引用域，例如：my_xh:=cj_rec.學(xué)號(hào);變

量如果聲明了游標(biāo)提取學(xué)號(hào)、課程號(hào)和成績(jī)列，則可以使用%ROWTYPE聲明一個(gè)記錄存儲(chǔ)相同的信息，其代碼為：DECLARE CURSORc1 IS SELECT學(xué)號(hào),課程號(hào),成績(jī) FROMcjb;cj_recc1%ROWTYPE;當(dāng)執(zhí)行下面的語(yǔ)句時(shí)：FETCHc1INTOcj_rec;在CJB表中學(xué)號(hào)列的值就賦予了cj_rec的學(xué)號(hào)域。變

量3．變量的作用域變量的作用域是指可以訪問(wèn)該變量的程序部分。對(duì)于PL/SQL的變量來(lái)說(shuō)，其作用域就是從變量的聲明到語(yǔ)句塊的結(jié)束。當(dāng)變量超出了作用域時(shí)，PL/SQL解析程序就會(huì)自動(dòng)釋放該變量的存儲(chǔ)空間。02常

量常

量常量指在程序運(yùn)行過(guò)程中值不變的量。常量的使用格式取決于值的數(shù)據(jù)類(lèi)型，其格式為：

常量名constant數(shù)據(jù)類(lèi)型:=值;例如，定義一個(gè)整型常量num，其值為4；定義一個(gè)字符串常量str，其值為“Helloworld!”。numconstantnumber(1):=4;strconstantchar:='Helloworld!';03常用數(shù)據(jù)類(lèi)型

1．VARCHAR數(shù)據(jù)類(lèi)型2．NUMBER數(shù)據(jù)類(lèi)型3．DATE數(shù)據(jù)類(lèi)型4．BOOLEAN數(shù)據(jù)類(lèi)型常用數(shù)據(jù)類(lèi)型

1．VARCHAR數(shù)據(jù)類(lèi)型Oracle

11g版本中VARCHAR與VARCHAR2的類(lèi)型含義完全相同，為可變長(zhǎng)的字符數(shù)據(jù)。在未來(lái)的版本中VARCHAR將會(huì)獨(dú)立成為一種新的數(shù)據(jù)類(lèi)型，不再受寬度的限制。語(yǔ)法格式為：var_fieldvarchar(n);其中，長(zhǎng)度值n是變量的最大長(zhǎng)度且必須是正整數(shù)，例如：var_fieldvarchar(11);在定義變量時(shí)，可以同時(shí)對(duì)其進(jìn)行初始化，例如：var_fieldvarchar(11):='Helloworld!';常用數(shù)據(jù)類(lèi)型

2．NUMBER數(shù)據(jù)類(lèi)型NUMBER數(shù)據(jù)類(lèi)型可用來(lái)表示所有的數(shù)值類(lèi)型，語(yǔ)法格式為：num_fieldNUMBER(precision,scale);其中，precision表示總的位數(shù)；scale表示小數(shù)的位數(shù)（默認(rèn)是0），如果實(shí)際數(shù)據(jù)超出設(shè)定精度則出現(xiàn)錯(cuò)誤。例如：num_fieldNUMBER(10,2);其中num_field是一個(gè)整數(shù)部分最多8位、小數(shù)部分最多2位的變量。常用數(shù)據(jù)類(lèi)型

3．DATE數(shù)據(jù)類(lèi)型DATE數(shù)據(jù)類(lèi)型是用來(lái)存放日期時(shí)間類(lèi)型的數(shù)據(jù)，用7字節(jié)分別描述年、月、日、時(shí)、分、秒，語(yǔ)法格式為：date_fieldDATE;DATE格式是由初始化參數(shù)NLS_DATE_FORMAT來(lái)設(shè)置的，其默認(rèn)格式為DD-MM月-YY，分別對(duì)應(yīng)日、月、年，如29-6月-15。4．BOOLEAN數(shù)據(jù)類(lèi)型BOOLEAN數(shù)據(jù)類(lèi)型（邏輯型）變量的值只有TRUE（真）或FALSE（假），一般用于判斷狀態(tài)，然后根據(jù)其值是“真”或“假”來(lái)決定程序執(zhí)行分支。關(guān)系表達(dá)式的值就是一個(gè)邏輯值。BOOLEAN數(shù)據(jù)類(lèi)型是PL/SQL所特有的，一般用于流程控制結(jié)構(gòu)而非表中的列。04對(duì)象類(lèi)型對(duì)象類(lèi)型要使用對(duì)象類(lèi)型首先應(yīng)定義該類(lèi)型，然后用這種類(lèi)型定義字段或變量。創(chuàng)建對(duì)象類(lèi)型使用CREATETYPE語(yǔ)句，其語(yǔ)法格式為：CREATE[ORREPLACE]TYPE用戶(hù)方案名.類(lèi)型名稱(chēng) [AUTHID{CURRENT_USER|DEFINER}]ASOBJECT( 屬性名1數(shù)據(jù)類(lèi)型, [屬性名2數(shù)據(jù)類(lèi)型,] … [屬性名n數(shù)據(jù)類(lèi)型] [方法名1] [方法名2] … [方法名n])相關(guān)參數(shù)說(shuō)明如下。（1）ORREPLACE選項(xiàng)：表示若已存在相同名稱(chēng)的類(lèi)型則不會(huì)報(bào)錯(cuò)，并將創(chuàng)建新的類(lèi)型。（2）AUTHID選項(xiàng)：表示將來(lái)執(zhí)行該方法時(shí)，必須使用在創(chuàng)建時(shí)定義的CURRENT_USER或DEFINER的權(quán)限集合。對(duì)象類(lèi)型【例6.4】

定義一個(gè)簡(jiǎn)單的對(duì)象類(lèi)型并使用它。先創(chuàng)建對(duì)象類(lèi)型TEST_OBJ：CREATEORREPLACETYPEtest_obj ASOBJECT ( item_idchar(6), pricenumber(10,2) );再創(chuàng)建一個(gè)表SELL，其中一列的數(shù)據(jù)類(lèi)型使用對(duì)象類(lèi)型TEST_OBJ：CREATETABLEsell( namenumber(2)NOTNULLPRIMARYKEY, infotest_obj);對(duì)象類(lèi)型向表中插入記錄，使用語(yǔ)句如下：INSERTINTOsell(name,info) VALUES(1,test_obj('002',23.5));執(zhí)行結(jié)果如圖。數(shù)據(jù)類(lèi)型轉(zhuǎn)換

PL/SQL可以進(jìn)行數(shù)據(jù)類(lèi)型之間的轉(zhuǎn)換，常見(jiàn)的轉(zhuǎn)換函數(shù)如下。（1）TO_CHAR：將NUMBER類(lèi)型和DATE類(lèi)型轉(zhuǎn)換成VARCHAR2類(lèi)型。（2）TO_DATE：將CHAR類(lèi)型轉(zhuǎn)換成DATE類(lèi)型。（3）TO_NUMBER：將CHAR類(lèi)型轉(zhuǎn)換成NUMBER類(lèi)型。數(shù)據(jù)類(lèi)型轉(zhuǎn)換

此外，PL/SQL還會(huì)自動(dòng)地轉(zhuǎn)換各種類(lèi)型，其示例如下：DECLARE xhvarchar2(6);BEGIN SELECTMAX(學(xué)號(hào))INTOxhFROMxsb;END;在數(shù)據(jù)庫(kù)中，MAX（學(xué)號(hào)）是一個(gè)NUMBER類(lèi)型的字段，但是xh卻是varchar2(6)的變量，PL/SQL會(huì)自動(dòng)將數(shù)值類(lèi)型轉(zhuǎn)換成字符串類(lèi)型。雖然PL/SQL可以在某些類(lèi)型之間自動(dòng)轉(zhuǎn)換，但使用轉(zhuǎn)換函數(shù)可提高程序的可讀性。如上面的例子也可以使用TO_CHAR轉(zhuǎn)換函數(shù)寫(xiě)為：DECLARE xhvarchar2(6);BEGIN SELECTTO_CHAR(MAX(學(xué)號(hào)))INTOxhFROMxsb;END;第6章PL/SQLPL/SQL基本程序結(jié)構(gòu)和語(yǔ)句01PL/SQL程序塊PL/SQL程序塊聲明部分包含了變量和常量的數(shù)據(jù)類(lèi)型和初始值，由DECLARE關(guān)鍵字開(kāi)始。執(zhí)行部分是PL/SQL塊中的指令部分，由關(guān)鍵字BEGIN開(kāi)始，所有可執(zhí)行語(yǔ)句都放在這個(gè)部分。異常處理部分是可選的，在其中處理異常和錯(cuò)誤。程序塊最終由關(guān)鍵字END結(jié)束，PL/SQL塊的基本結(jié)構(gòu)如下：[DECLARE]--聲明部分BEGIN--執(zhí)行部分[EXCEPTION]--異常處理部分ENDPL/SQL程序塊PL/SQL程序塊的每一條語(yǔ)句都必須由分號(hào)結(jié)束，每一個(gè)PL/SQL語(yǔ)句塊由BEGIN或DECLARE開(kāi)始，以END結(jié)束。下面是一個(gè)PL/SQL程序塊的示例：SETSERVEROUTPUTON;DECLARE anumber:=1;BEGIN a:=a+5; DBMS_OUTPUT.PUT_LINE('和為：'||TO_CHAR(a));END;/執(zhí)行結(jié)果如圖。02條件結(jié)構(gòu)1．IF-THEN語(yǔ)句2．IF-THEN-ELSE語(yǔ)句3．IF-THEN-ELSIF-THEN-ELSE語(yǔ)句條件結(jié)構(gòu)1．IF-THEN語(yǔ)句語(yǔ)法格式為：IF條件表達(dá)式THEN /*條件表達(dá)式*/ PL/SQL語(yǔ)句; /*對(duì)應(yīng)流程圖A*/ENDIF;這個(gè)結(jié)構(gòu)用于測(cè)試一個(gè)簡(jiǎn)單的條件。如果條件表達(dá)式為T(mén)RUE，則執(zhí)行語(yǔ)句塊中的操作，如圖。條件結(jié)構(gòu)【例6.5】

查詢(xún)總學(xué)分大于50分的學(xué)生人數(shù)。DECLARE v_numnumber(3);BEGIN SELECTCOUNT(*)INTOv_num FROMxsb WHERE總學(xué)分>50; IFv_num<>0THEN DBMS_OUTPUT.PUT_LINE('總學(xué)分>50的人數(shù)為：'||TO_CHAR(v_num)); ENDIF;END;執(zhí)行結(jié)果為“總學(xué)分>50的人數(shù)為：3”。IF-THEN語(yǔ)句可以嵌套使用。條件結(jié)構(gòu)【例6.6】

判斷計(jì)算機(jī)系總學(xué)分大于40分的人數(shù)是否超過(guò)10人。DECLARE v_numnumber(3);BEGIN SELECTCOUNT(*)INTOv_num FROMxsb WHERE總學(xué)分>40AND專(zhuān)業(yè)='計(jì)算機(jī)'; IFv_num<>0THEN IFv_num>5THEN DBMS_OUTPUT.PUT_LINE('計(jì)算機(jī)系總學(xué)分>40的人數(shù)超過(guò)10人'); ENDIF; ENDIF;END;執(zhí)行結(jié)果為“計(jì)算機(jī)系總學(xué)分>40的人數(shù)超過(guò)10人”。條件結(jié)構(gòu)2．IF-THEN-ELSE語(yǔ)句語(yǔ)法格式為：IF條件表達(dá)式THEN /*條件表達(dá)式*/ PL/SQL語(yǔ)句1; /*對(duì)應(yīng)流程圖A*/ELSE PL/SQL語(yǔ)句2; /*對(duì)應(yīng)流程圖B*/ENDIF;這種結(jié)構(gòu)與IF語(yǔ)句非常相似，唯一不同的是在條件表達(dá)式為FALSE時(shí)，執(zhí)行跟在ELSE后的一條或多條語(yǔ)句。IF-THEN-ELSE語(yǔ)句的執(zhí)行流程如圖。條件結(jié)構(gòu)【例6.7】

如果“計(jì)算機(jī)基礎(chǔ)”課程的平均成績(jī)高于75分，則顯示“平均成績(jī)大于75”，否則顯示“平均成績(jī)小于75”。DECLARE v_avgnumber(4,2);BEGIN SELECTAVG(成績(jī))INTOv_avg FROMxsb,cjb,kcb WHERExsb.學(xué)號(hào)=cjb.學(xué)號(hào) ANDcjb.課程號(hào)=kcb.課程號(hào) ANDkcb.課程名='計(jì)算機(jī)基礎(chǔ)'; IFv_avg>75THEN DBMS_OUTPUT.PUT_LINE('平均成績(jī)大于75'); ELSE DBMS_OUTPUT.PUT_LINE('平均成績(jī)小于75'); ENDIF;END;執(zhí)行結(jié)果為“平均成績(jī)大于75”。IF-THEN-ELSE語(yǔ)句也可以嵌套。條件結(jié)構(gòu)3．IF-THEN-ELSIF-THEN-ELSE語(yǔ)句語(yǔ)法格式為：IF條件表達(dá)式1THEN PL/SQL語(yǔ)句1; /*對(duì)應(yīng)流程圖A*/ELSIF條件表達(dá)式2THEN PL/SQL語(yǔ)句2; /*對(duì)應(yīng)流程圖B*/ELSE PL/SQL語(yǔ)句3; /*對(duì)應(yīng)流程圖C*/ENDIF;說(shuō)明：如果IF后的條件表達(dá)式成立，就執(zhí)行THEN后的語(yǔ)句。判斷ELSIF后面的條件表達(dá)式為真時(shí)，就執(zhí)行第二個(gè)THEN后的語(yǔ)句，否則執(zhí)行ELSE后的語(yǔ)句。IF-THEN-ELSIF-THEN-ELSE語(yǔ)句的執(zhí)行流程如圖。條件結(jié)構(gòu)【例6.8】

求x2+4x+3=0的根。DECLARE anumber; bnumber; cnumber; x1number; x2number; dnumber;BEGINa:=1;b:=4;c:=3;d:=b*b-4*a*c;IFa=0THEN x1:=-c/b; DBMS_OUTPUT.PUT_LINE('只有一個(gè)平方根'||to_char(x1));ELSIFd<0THEN DBMS_OUTPUT.PUT_LINE('沒(méi)有算術(shù)平方根'); ELSE x1:=(-b+sqrt(d))/(2*a); x2:=(-b-sqrt(d))/(2*a); DBMS_OUTPUT.PUT_LINE('第一個(gè)平方根'||to_char(x1)); DBMS_OUTPUT.PUT_LINE('第二個(gè)平方根'||to_char(x2)); ENDIF;END;輸出結(jié)果：第一個(gè)平方根為-1第二個(gè)平方根為-303循環(huán)結(jié)構(gòu)1．LOOP-EXIT-END循環(huán)2．LOOP-EXIT-WHEN-END循環(huán)3．WHILE-LOOP-END循環(huán)4．FOR-IN-LOOP-END循環(huán)循環(huán)結(jié)構(gòu)1．LOOP-EXIT-END循環(huán)語(yǔ)法格式為：LOOP

循環(huán)體 /*執(zhí)行循環(huán)體*/ IF條件表達(dá)式THEN /*測(cè)試條件表達(dá)式是否符合退出條件*/ EXIT; /*滿(mǎn)足退出條件，退出循環(huán)*/ ENDIF;ENDLOOP;說(shuō)明：循環(huán)體是在循環(huán)體中需要完成的操作。如果條件表達(dá)式為T(mén)RUE則跳出循環(huán)，否則繼續(xù)循環(huán)操作，直到滿(mǎn)足條件表達(dá)式的條件跳出循環(huán)。LOOP-EXIT-END語(yǔ)句對(duì)應(yīng)的執(zhí)行流程如圖。循環(huán)結(jié)構(gòu)【例6.9】

用LOOP-EXIT-END循環(huán)求10的階乘。DECLARE nnumber:=1; count1number:=2;BEGIN LOOP n:=n*count1; count1:=count1+1; IFcount1>10THEN EXIT; ENDIF; ENDLOOP; DBMS_OUTPUT.PUT_LINE(to_char(n));END;輸出結(jié)果為：3628800。循環(huán)結(jié)構(gòu)2．LOOP-EXIT-WHEN-END循環(huán)除了退出條件檢測(cè)有所區(qū)別，此結(jié)構(gòu)與前一個(gè)循環(huán)結(jié)構(gòu)類(lèi)似，語(yǔ)法格式為：LOOP

循環(huán)體 /*執(zhí)行循環(huán)體*/ EXITWHEN條件表達(dá)式 /*測(cè)試是否符合退出條件*/ENDLOOP;【例6.10】

用LOOP-EXIT-WHEN-END循環(huán)求10的階乘。DECLARE nnumber:=1; count1number:=2;BEGIN LOOP n:=n*count1; count1:=count1+1; EXITWHENcount1=11; ENDLOOP; DBMS_OUTPUT.PUT_LINE(to_char(n));END;循環(huán)結(jié)構(gòu)3．WHILE-LOOP-END循環(huán)語(yǔ)法格式為：WHILE條件表達(dá)式 /*測(cè)試是否符合退出條件*/ LOOP

循環(huán)體 /*執(zhí)行循環(huán)體*/ENDLOOP;此結(jié)構(gòu)在循環(huán)的WHILE部分測(cè)試退出條件，當(dāng)條件成立時(shí)執(zhí)行循環(huán)體，否則退出循環(huán)。這種循環(huán)結(jié)構(gòu)同上面兩種有所不同，它先測(cè)試條件，然后執(zhí)行循環(huán)體；而前兩種是先執(zhí)行了一次循環(huán)體內(nèi)的語(yǔ)句，然后再測(cè)試條件。此結(jié)構(gòu)的執(zhí)行流程如圖。循環(huán)結(jié)構(gòu)【例6.11】

用WHILE-LOOP-END循環(huán)求10的階乘。DECLARE nnumber:=1; count1number:=2;BEGIN WHILEcount1<=10 LOOP n:=n*count1; count1:=count1+1; ENDLOOP; DBMS_OUTPUT.PUT_LINE(to_char(n));END;循環(huán)結(jié)構(gòu)4．FOR-IN-LOOP-END循環(huán)語(yǔ)法格式為：FOR循環(huán)變量名IN變量初值..變量終值 /*定義跟蹤循環(huán)的變量*/ LOOP

循環(huán)體 /*執(zhí)行循環(huán)體*/ ENDLOOP;說(shuō)明：FOR關(guān)鍵字后面指定一個(gè)循環(huán)變量，IN確定循環(huán)變量的初值和終值，在循環(huán)變量的初值和終值之間是分隔符兩個(gè)點(diǎn)號(hào)“..”。如果循環(huán)變量的值小于終值，則運(yùn)行循環(huán)體內(nèi)的語(yǔ)句，否則跳出循環(huán)，執(zhí)行下面的語(yǔ)句。循環(huán)結(jié)構(gòu)【例6.12】

用FOR-IN-LOOP-END循環(huán)求10的階乘。DECLARE nNUMBER:=1; count1NUMBER;BEGIN FORcount1IN2..10 LOOP n:=n*count1; ENDLOOP; DBMS_OUTPUT.PUT_LINE(to_char(n));END;其中，變量count是控制循環(huán)次數(shù)的計(jì)數(shù)器，其初始值是2，終值是10，步長(zhǎng)為1。每循環(huán)一次，count會(huì)自動(dòng)累加1，直到count大于終值10（count=11）時(shí)跳出循環(huán)。04選擇和跳轉(zhuǎn)語(yǔ)句

1．CASE語(yǔ)句2．GOTO語(yǔ)句選擇和跳轉(zhuǎn)語(yǔ)句

1．CASE語(yǔ)句CASE語(yǔ)句使用簡(jiǎn)單的結(jié)構(gòu)對(duì)數(shù)值列表做出選擇。更為重要的是，它還可以用來(lái)設(shè)置變量的值。語(yǔ)法格式為：CASE變量名 WHEN值1THEN語(yǔ)句1 WHEN值2THEN語(yǔ)句2 … WHEN值nTHEN語(yǔ)句n [ELSE語(yǔ)句]ENDCASE;說(shuō)明：首先設(shè)定一個(gè)變量的值，然后順序比較WHEN關(guān)鍵字后面給出的值，若相等，則執(zhí)行THEN關(guān)鍵字后面的語(yǔ)句，并且停止CASE語(yǔ)句的執(zhí)行。選擇和跳轉(zhuǎn)語(yǔ)句

【例6.13】CASE語(yǔ)句的應(yīng)用。DECLARE v_kchchar(3); v_Resultvarchar2(16);BEGIN SELECT課程號(hào) INTOv_kch FROMkcb WHERE開(kāi)課學(xué)期=1; CASEv_kch /*判斷v_kch的值，并給出結(jié)果*/ WHEN'101'THENv_Result:='計(jì)算機(jī)基礎(chǔ)'; WHEN'102'THENv_Result:='程序設(shè)計(jì)與語(yǔ)言'; WHEN'206'THENv_Result:='離散數(shù)學(xué)'; WHEN'208'THENv_Result:='數(shù)據(jù)結(jié)構(gòu)'; ELSE v_Result:='Nothing'; ENDCASE; DBMS_OUTPUT.PUT_LINE(v_result);END;執(zhí)行結(jié)果為“計(jì)算機(jī)基礎(chǔ)”。選擇和跳轉(zhuǎn)語(yǔ)句

2．GOTO語(yǔ)句PL/SQL提供GOTO語(yǔ)句，實(shí)現(xiàn)將執(zhí)行流程轉(zhuǎn)移到標(biāo)號(hào)指定的位置，語(yǔ)法格式為：GOTO標(biāo)號(hào)GOTO關(guān)鍵字后面的語(yǔ)句標(biāo)號(hào)必須符合標(biāo)識(shí)符規(guī)則，定義形式如下：<<標(biāo)號(hào)>>語(yǔ)句使用GOTO語(yǔ)句可以控制程序代碼的執(zhí)行順序。選擇和跳轉(zhuǎn)語(yǔ)句

【例6.14】

設(shè)表temp(學(xué)號(hào)char(6),性別char(2),姓名char(8))，初始化temp表。DECLARE v_counterBINARY_INTEGER:=1; v_xhnumber(6);BEGIN v_xh:=150001; LOOP INSERTINTOtemp(學(xué)號(hào),性別) VALUES(to_char(v_xh),'男'); v_counter:=v_counter+1; v_xh:=v_xh+1; IFv_counter=10THEN GOTOloop_end; ENDIF; ENDLOOP;<<loop_end>>DBMS_OUTPUT.PUT_LINE('InitOk');END;執(zhí)行結(jié)果如圖。05異

常1．預(yù)定義異常2．用戶(hù)定義異常異

常1．預(yù)定義異常預(yù)定義異常是由運(yùn)行系統(tǒng)產(chǎn)生的。如出現(xiàn)被0除時(shí)，PL/SQL就會(huì)產(chǎn)生一個(gè)預(yù)定義的ZERO_DIVIDE異常。【例6.15】ZERO_DIVIDE異常。使用系統(tǒng)預(yù)定義的異常處理后，程序運(yùn)行時(shí)系統(tǒng)就不會(huì)提示出現(xiàn)錯(cuò)誤。DECLARE v_numbernumber(2):=10; v_zeronumber(2):=0; v_resultnumber(5);BEGIN v_result:=v_number/v_zero; /*用v_number除以v_zero，即10/0，從而產(chǎn)生除零錯(cuò)誤*/ EXCEPTION WHENZERO_DIVIDETHEN DBMS_OUTPUT.PUT_LINE('DIVIDEZERO');END;輸出結(jié)果為：DIVIDEZERO。異

常如果在當(dāng)前塊中沒(méi)有WHEN子句并且BEGIN/END塊是嵌套的，程序?qū)⒗^續(xù)在外部塊中尋找錯(cuò)誤處理柄，直到找到一個(gè)。當(dāng)錯(cuò)誤發(fā)生而在任何異常部分沒(méi)有與之聯(lián)系的錯(cuò)誤處理柄時(shí)，程序?qū)⒔K止。除了除零錯(cuò)誤，PL/SQL還有很多系統(tǒng)預(yù)定義異常，如表所示列出的常見(jiàn)異常，通過(guò)檢測(cè)這些異常，用戶(hù)可以查找到PL/SQL程序產(chǎn)生的錯(cuò)誤。異

常說(shuō)

明no_data_found如果一個(gè)select語(yǔ)句試圖基于其條件檢索數(shù)據(jù)，此異常表示不存在滿(mǎn)足條件的數(shù)據(jù)行too_many_rows由于隱式游標(biāo)每次只能檢索一行數(shù)據(jù)，使用隱式游標(biāo)時(shí)，這個(gè)異常可檢測(cè)到有多行數(shù)據(jù)存在dup_val_on_index如果某索引中已有某鍵列值，若還要在該索引中創(chuàng)建該鍵碼值的索引項(xiàng)時(shí)，出現(xiàn)此異常。例如，假設(shè)一個(gè)藥店收費(fèi)系統(tǒng)以發(fā)票號(hào)為鍵碼，當(dāng)某個(gè)應(yīng)用程序準(zhǔn)備創(chuàng)建一個(gè)重復(fù)的發(fā)票號(hào)時(shí)，就會(huì)產(chǎn)生此異常value_error此異常表示指定目標(biāo)域的長(zhǎng)度小于待放入其中的數(shù)據(jù)長(zhǎng)度。例如，將“ABCDEFGH”字符串放入定義為“varchar2(6)”的域時(shí)，就會(huì)產(chǎn)生此異常case_not_found在case語(yǔ)句中發(fā)現(xiàn)不匹配的when語(yǔ)句異

常【例6.16】

轉(zhuǎn)換的錯(cuò)誤處理。DECLARE v_numbernumber(5); v_resultchar(5):='2w';BEGIN v_number:=to_number(v_result); EXCEPTION WHENVALUE_ERRORTHEN DBMS_OUTPUT.PUT_LINE('CONVERTTYPEERROR');END;輸出結(jié)果為：CONVERTTYPEERROR。異

常【例6.17】

聯(lián)合的錯(cuò)誤處理。DECLARE v_resultXSB.姓名%TYPE;BEGIN SELECT姓名INTOv_result FROMxsb WHERE姓名='王林'; DBMS_OUTPUT.PUT_LINE('Thestudentnameis'||v_result); EXCEPTION WHENTOO_MANY_ROWSTHEN DBMS_OUTPUT.PUT_LINE('TherehasTOO_MANY_ROWSerror'); WHENNO_DATA_FOUNDTHEN DBMS_OUTPUT.PUT_LINE('TherehasNO_DATA_FOUNDerror');END;輸出結(jié)果為：TherehasTOO_MANY_ROWSerror。異

常2．用戶(hù)定義異常用戶(hù)可以通過(guò)自定義異常來(lái)處理發(fā)生的錯(cuò)誤，語(yǔ)法格式為：EXCEPTION WHEN異常名THEN

語(yǔ)句塊1; WHENTHEN

語(yǔ)句塊2; [WHENOTHERSTHEN

語(yǔ)句塊3;]END;每個(gè)異常處理部分都是由WHEN子句和相應(yīng)的執(zhí)行語(yǔ)句組成的。通過(guò)下例可以清楚地了解異常處理的執(zhí)行過(guò)程。異

常【例6.18】

自定義異常的處理。DECLARE e_overnumberEXCEPTION; /*定義異常處理變量*/ v_xs_numbernumber(9); v_max_xs_numbernumber(9):=20;BEGIN SELECTCOUNT(*)INTOv_xs_number FROMxsb; IFv_max_xs_number<v_xs_numberTHEN RAISEe_overnumber; /*使用RAISE語(yǔ)句調(diào)用異常*/ ENDIF; EXCEPTION WHENe_overnumberTHEN DBMS_OUTPUT.PUT_LINE('CurrentXsNumberis:'||v_xs_number|| 'maxallowedis:'||v_max_xs_number); END;執(zhí)行結(jié)果為：CurrentXsNumberis:24maxallowedis:20。異

常不僅可以同時(shí)使用多個(gè)WHEN子句捕捉幾個(gè)異常情況，還可以結(jié)合系統(tǒng)預(yù)定義的異常處理來(lái)操作。此外，單個(gè)WHEN子句允許處理多個(gè)異常，也就是說(shuō)如下的形式是合法的。EXCEPTION WHEN異常1OR異常3THEN … /*出現(xiàn)異常1或者異常3執(zhí)行某些語(yǔ)句*/ WHEN異常2THEN … /*出現(xiàn)異常2執(zhí)行某些語(yǔ)句*/END;在上例中，出現(xiàn)異常1或異常3時(shí)使用相同的處理方式，這是可以的。但是，同一個(gè)異常不允許多個(gè)WHEN子句來(lái)處理，如下的形式是不合法的：EXCEPTION WHEN異常1 … /*出現(xiàn)異常1執(zhí)行某些語(yǔ)句*/ WHEN異常2THEN … /*出現(xiàn)異常2執(zhí)行某些語(yǔ)句*/ WHEN異常1OR異常3THEN … /*出現(xiàn)異常1或者異常3執(zhí)行某些語(yǔ)句*/END;異

常【例6.19】

使用OTHERS處理異常。DECLARE v_resultnumber;BEGIN SELECT姓名INTOv_result FROMxsb WHERE學(xué)號(hào)='150010'; DBMS_OUTPUT.PUT_LINE('Thestudentnameis'||v_result); EXCEPTION WHENTOO_MANY_ROWSTHEN DBMS_OUTPUT.PUT_LINE('TherehasTOO_MANY_ROWSerror'); WHENOTHERSTHEN DBMS_OUTPUT.PUT_LINE('Unkownerror');END;其實(shí)，OTHERS異常處理可以借助兩個(gè)函數(shù)來(lái)說(shuō)明捕捉到的異常類(lèi)型，這兩個(gè)函數(shù)為SQLCODE和SQLERRM，其中SQLCODE函數(shù)是用來(lái)說(shuō)明當(dāng)前錯(cuò)誤代碼的，如果是用戶(hù)自定義的異常，則返回“1”。SQLERRM函數(shù)返回的是當(dāng)前錯(cuò)誤的信息。為了說(shuō)明這兩個(gè)函數(shù)的使用，可以將上例中的WHENOHTERS子句的執(zhí)行語(yǔ)句換成如下語(yǔ)句：DBMS_OUTPUT.PUT_LINE('TheSQLCODEis:'||SQLCODE);DBMS_OUTPUT.PUT_LINE('TheSQLERRMis:'||SQLERRM);第6章PL/SQL系統(tǒng)內(nèi)置函數(shù)系統(tǒng)內(nèi)置函數(shù)1．?dāng)?shù)學(xué)運(yùn)算函數(shù)數(shù)學(xué)運(yùn)算函數(shù)可對(duì)Oracle系統(tǒng)提供的數(shù)值數(shù)據(jù)進(jìn)行數(shù)學(xué)運(yùn)算并返回運(yùn)算結(jié)果。常用的數(shù)學(xué)運(yùn)算函數(shù)如表6.7所示。函

數(shù)名說(shuō)

明Abs(數(shù)值)返回參數(shù)數(shù)值的絕對(duì)值，結(jié)果恒為正Ceil(數(shù)值)返回大于或等于參數(shù)數(shù)值最接近的整數(shù)Cos(數(shù)值)返回參數(shù)數(shù)值的余弦值Floor(數(shù)值)返回等于或小于參數(shù)的最大整數(shù)Mod(被除數(shù),除數(shù))返回兩數(shù)相除的余數(shù)。如果除數(shù)等于0，則返回被除數(shù)Power(數(shù)值,n)返回指定數(shù)值的n次冪Round(數(shù)值,n)結(jié)果近似到數(shù)值小數(shù)點(diǎn)右側(cè)的n位Sign(數(shù)值)返回值指出參數(shù)值是正還是負(fù)。若參數(shù)大于0則返回1；若小于0則返回-1；若等于0則返回0Sqrt(數(shù)值)返回參數(shù)數(shù)值的平方根Trunc(數(shù)值,n)返回舍入到指定的n位的參數(shù)數(shù)值。如果n為正，就截取到小數(shù)右側(cè)的該數(shù)值處；如果n為負(fù)，就截取到小數(shù)點(diǎn)左側(cè)的該數(shù)值處；如果沒(méi)有指定n就假定為0，截取到小數(shù)點(diǎn)處系統(tǒng)內(nèi)置函數(shù)（1）ABS函數(shù)語(yǔ)法格式為：ABS(數(shù)值)功能：返回給定數(shù)值的絕對(duì)值，參數(shù)為數(shù)值型表達(dá)式。【例6.20】

顯示ABS函數(shù)對(duì)兩個(gè)不同數(shù)值的效果。SELECTABS(-0.8)FROMdual;SELECTABS(0.8)FROMdual;得出的結(jié)果都為0.8。說(shuō)明：Oracle11g數(shù)據(jù)庫(kù)中的dual表是虛擬的一個(gè)表，它有一行一列，所有者為sys用戶(hù)，可供數(shù)據(jù)庫(kù)中的所有用戶(hù)使用。系統(tǒng)內(nèi)置函數(shù)（2）ROUND函數(shù)語(yǔ)法格式為：ROUND(數(shù)值,n)功能：求一個(gè)數(shù)值的近似值，結(jié)果近似到小數(shù)點(diǎn)右側(cè)的n位。【例6.21】

求幾個(gè)數(shù)值的近似值。SELECTROUND(3.678,2)FROMdual;結(jié)果為：3.68。SELECTROUND(3.3243,3)FROMdual;結(jié)果為：3.324。系統(tǒng)內(nèi)置函數(shù)2．字符串函數(shù)字符串函數(shù)用于對(duì)字符串進(jìn)行處理。一些常用的字符串函數(shù)如表。函

數(shù)

名返回值說(shuō)明Length(值)返回字符串、數(shù)字或表達(dá)式的長(zhǎng)度Lower(字符串)把給定字符串中的字符變成小寫(xiě)Upper(字符串)把給定字符串中的字符變成大寫(xiě)Lpad(字符串,長(zhǎng)度[,填充字符串])在字符串左側(cè)，使用指定的填充字符串進(jìn)行填充直到指定的長(zhǎng)度，若未指定填充字符串，則默認(rèn)為空格Rpad(字符串,長(zhǎng)度[,填充字符串])在字符串右側(cè)，使用指定的填充字符串進(jìn)行填充直到指定的長(zhǎng)度，若未指定填充字符串，則默認(rèn)為空格Ltrim(字符串,[,匹配字符串])從字符串左側(cè)刪除匹配字符串中出現(xiàn)的任何字符，直到匹配字符串中沒(méi)有字符為止Rtrim(字符串,[,匹配字符串])從字符串右側(cè)刪除匹配字符串中出現(xiàn)的任何字符，直到匹配字符串中沒(méi)有字符為止字符串1‖字符串2合并兩個(gè)字符串Initcap(字符串)將每個(gè)字符串的首字母大寫(xiě)Instr(源字符串,目標(biāo)字符串[,起始位置[,匹配次數(shù)]])判斷目標(biāo)字符串是否存在于源字符串中，并根據(jù)匹配次數(shù)顯示目標(biāo)字符串的位置，返回?cái)?shù)值Replace(源字符串,目標(biāo)字符串,替代字符串)在源字符串中查找目標(biāo)字符串，并用替代字符串來(lái)替換所有的目標(biāo)字符串Soundex(字符串)查找與字符串發(fā)音相似的單詞，該單詞的首字母要與字符串的首字母相同Subs(字符串,開(kāi)始位置[,刪除字符的個(gè)數(shù)])在字符串中刪除從指定位置開(kāi)始的指定個(gè)數(shù)字符。若未指定個(gè)數(shù)，則刪除從開(kāi)始位置的所有字符系統(tǒng)內(nèi)置函數(shù)（1）LENTH函數(shù)語(yǔ)法格式為：LENGTH(值)功能：返回參數(shù)值的長(zhǎng)度，返回值為整數(shù)。參數(shù)值可以是字符串、數(shù)字或者表達(dá)式。（2）LOWER函數(shù)語(yǔ)法格式為：LOWER(字符串)功能：將給定字符串的字符變?yōu)樾?xiě)。【例6.22】

轉(zhuǎn)換字符的大小寫(xiě)。SELECTLOWER('hello')FROMdual;SELECTLOWER('Hello')FROMdual;SELECTLOWER('HELLO')FROMdual;結(jié)果都為：hello。系統(tǒng)內(nèi)置函數(shù)（3）REPLACE函數(shù)語(yǔ)法格式為：Replace(源字符串,目標(biāo)字符串,替代字符串)功能：把源字符串中目標(biāo)字符串用替代字符串代替。【例6.23】

字符替換。SELECTREPLACE('Helloworld','world','baby')FROMdual;結(jié)果為：Hellobaby。系統(tǒng)內(nèi)置函數(shù)3．統(tǒng)計(jì)函數(shù)Oracle11g數(shù)據(jù)庫(kù)提供了豐富的統(tǒng)計(jì)函數(shù)用于處理數(shù)值型數(shù)據(jù)，如表6.9所示列出的常用統(tǒng)計(jì)函數(shù)。函

數(shù)

名返回值說(shuō)明Avg([distinct]列名)求列名中所有值的平均值，若使用DISTINCT選項(xiàng)，則只能使用不同的非空數(shù)值Count([distinct]值表達(dá)式)統(tǒng)計(jì)選擇行的數(shù)目，并忽略參數(shù)值中的空值。若使用distinct選項(xiàng)，則只統(tǒng)計(jì)不同的非空數(shù)值。參數(shù)值可以是字段名，也可以是表達(dá)式Max(value)從選定的value中選取數(shù)值/字符的最大值，忽略空值Min(value)從選定的value中選取數(shù)值/字符的最小值，忽略空值Stddev(value)返回所選擇的value的標(biāo)準(zhǔn)偏差Sum(value)返回value的和。value可以是字段名，也可以是表達(dá)式Variance([distinct]value)返回所選行的所有數(shù)值的方差，忽略value的空值系統(tǒng)內(nèi)置函數(shù)（1）AVG函數(shù)語(yǔ)法格式為：AVG([DISTINCT]列名)功能：求所有數(shù)值型列中所有值的平均值，若使用DISTINCT關(guān)鍵字，則只能使用不能非空的數(shù)值。【例6.24】

求“計(jì)算機(jī)基礎(chǔ)”課的平均成績(jī)。SELECTAVG(成績(jī)) FROMcjb WHERE課程號(hào)='101';執(zhí)行結(jié)果為：78.65。系統(tǒng)內(nèi)置函數(shù)（2）COUNT函數(shù)語(yǔ)法格式為：COUNT([DISTINCT]值)功能：統(tǒng)計(jì)選擇行的數(shù)目，并忽略參數(shù)值中的空值。若使用DISTINCT選項(xiàng)，則只統(tǒng)計(jì)不同的非空數(shù)值。參數(shù)值可以是字段名，也可以是表達(dá)式。【例6.25】

求XSB表的學(xué)生總數(shù)。SELECTCOUNT(*) FROMxsb;執(zhí)行結(jié)果為：24。系統(tǒng)內(nèi)置函數(shù)4．日期函數(shù)Oracle11g數(shù)據(jù)庫(kù)提供了豐富的日期函數(shù)用來(lái)處理日期型數(shù)據(jù)，如表所示的常用日期函數(shù)。函

數(shù)

名返回值說(shuō)明Add_months(日期值,月份數(shù))把一些月份加到日期上，并返回結(jié)果Last_day(日期值)返回指定日期所在月份的最后一天Months_between(日期值1,日期值)返回日期值1減去日期值2得到的月數(shù)New_time(當(dāng)前日期,當(dāng)前時(shí)區(qū),指定時(shí)區(qū))根據(jù)當(dāng)前日期和當(dāng)前時(shí)區(qū)，返回在指定時(shí)區(qū)中的日期。其中，當(dāng)前時(shí)區(qū)和指定時(shí)區(qū)的值為時(shí)區(qū)的3個(gè)字母縮寫(xiě)Next_day(日期值,'day')給出指定日期后的day所在的日期；day是全拼的星期名稱(chēng)Round(日期值,'format')把日期值四舍五入到由format指定的格式To_char(日期值,'format')將日期型數(shù)據(jù)轉(zhuǎn)換成以format指定形式的字符型數(shù)據(jù)To_date(字符串,'format')將字符串轉(zhuǎn)換成以format指定形式的日期型數(shù)據(jù)返回Trunc(日期值,'format')把任何日期的時(shí)間設(shè)置為00:00:00系統(tǒng)內(nèi)置函數(shù)（1）LAST_DAY函數(shù)語(yǔ)法格式為：LAST_DAY(日期值)功能：求指定日期所在月份的最后一天。【例6.26】

查詢(xún)本月的最后一天。SELECTLAST_DAY(SYSDATE)FROMdual;其中，SYSDATE也是日期函數(shù)，可返回當(dāng)前系統(tǒng)的日期。系統(tǒng)內(nèi)置函數(shù)（2）MONTHS_BETWEEN函數(shù)語(yǔ)法格式為：MONTHS_BETWEEN(日期值1,日期值2)功能：返回日期值1減去日期值2得到的月數(shù)。如果日期值1比日期值2要早，則函數(shù)將返回一個(gè)負(fù)數(shù)。【例6.27】

求兩個(gè)日期間相隔的月數(shù)。SELECTMONTHS_BETWEEN('2016-01-25','2016-03-25')FROMdual;執(zhí)行結(jié)果如圖。第6章PL/SQL用戶(hù)定義函數(shù)用戶(hù)定義函數(shù)用戶(hù)定義函數(shù)是存儲(chǔ)在數(shù)據(jù)庫(kù)中的代碼塊，可以把值返回到調(diào)用程序。調(diào)用時(shí)如同系統(tǒng)函數(shù)一樣，如max(value)函數(shù)，其中value為參數(shù)。函數(shù)的參數(shù)有如下3種模式。（1）IN模式：表示該參數(shù)是輸入給函數(shù)的參數(shù)。（2）OUT模式：表示該參數(shù)在函數(shù)中被賦值，并可以傳給函數(shù)調(diào)用程序。（3）INOUT模式：表示該參數(shù)既可以傳值也可以被賦值。01創(chuàng)建函數(shù)

1．以界面方式創(chuàng)建函數(shù)2．以命令方式創(chuàng)建函數(shù)創(chuàng)建函數(shù)

1．以界面方式創(chuàng)建函數(shù)右擊myorcl連接的“函數(shù)”節(jié)點(diǎn)，選擇“新建函數(shù)”選項(xiàng)，彈出“創(chuàng)建PL/SQL函數(shù)”對(duì)話框。在“名稱(chēng)”欄中輸入函數(shù)的名稱(chēng)，在“參數(shù)”選項(xiàng)頁(yè)的第一行選擇返回值的類(lèi)型，單擊按鈕增加一個(gè)參數(shù)，設(shè)置參數(shù)名稱(chēng)、類(lèi)型和模式，設(shè)置完成后單擊“確定”按鈕。在打開(kāi)的主界面“COUNT_NUM”窗口中完成函數(shù)的編寫(xiě)工作，完成后單擊“編譯以進(jìn)行調(diào)試”按鈕完成函數(shù)的創(chuàng)建，整個(gè)過(guò)程的操作步驟如圖。創(chuàng)建函數(shù)

2．以命令方式創(chuàng)建函數(shù)在Oracle11g數(shù)據(jù)庫(kù)中，創(chuàng)建用戶(hù)定義函數(shù)使用CREATEFUNCTION語(yǔ)句，其語(yǔ)法格式為：CREATE[ORREPLACE]FUNCTION函數(shù)名 /*函數(shù)名稱(chēng)*/(

參數(shù)名1,參數(shù)類(lèi)型數(shù)據(jù)類(lèi)型, /*參數(shù)定義部分*/

參數(shù)名2,參數(shù)類(lèi)型數(shù)據(jù)類(lèi)型,

參數(shù)名3,參數(shù)類(lèi)型數(shù)據(jù)類(lèi)型, …) RETURN返回值類(lèi)型 /*定義返回值類(lèi)型*/ {IS|AS} [聲明變量] BEGIN

函數(shù)體; /*函數(shù)體部分*/ [RETURN(返回表達(dá)式);] /*返回語(yǔ)句*/ END[函數(shù)名];創(chuàng)建函數(shù)

相關(guān)參數(shù)說(shuō)明如下。（1）函數(shù)名：用戶(hù)定義函數(shù)的名稱(chēng)必須符合標(biāo)識(shí)符的規(guī)則，對(duì)其所有者來(lái)說(shuō)，該名稱(chēng)在數(shù)據(jù)庫(kù)中是唯一的。（2）參數(shù)類(lèi)型：參數(shù)類(lèi)型可以是IN模式、OUT模式或INOUT模式，默認(rèn)為IN模式。（3）數(shù)據(jù)類(lèi)型：這里定義參數(shù)的數(shù)據(jù)類(lèi)型時(shí)不需要指定數(shù)據(jù)類(lèi)型的長(zhǎng)度。（4）RETURN選項(xiàng)：在函數(shù)參數(shù)定義部分后面的RETURN選項(xiàng)中，可以指定函數(shù)返回值的數(shù)據(jù)類(lèi)型。（5）函數(shù)體：函數(shù)體部分由PL/SQL的語(yǔ)句構(gòu)成，是實(shí)現(xiàn)函數(shù)功能的主要部分。（6）RETURN語(yǔ)句：在函數(shù)體最后使用一條RETURN語(yǔ)句，將返回表達(dá)式的值返回給函數(shù)調(diào)用程序。創(chuàng)建函數(shù)

下面給出一個(gè)函數(shù)，并說(shuō)明函數(shù)的3種參數(shù)的合法性。CREATEORREPLACEFUNCTION函數(shù)名稱(chēng)( in_pmtINchar, out_pmtOUTchar, in_out_pmtINOUTchar) RETURNchar AS return_charchar; BEGIN

函數(shù)語(yǔ)句序列 RETURN(return_char); END[函數(shù)名稱(chēng)];創(chuàng)建函數(shù)

函數(shù)語(yǔ)句序列及其可能出現(xiàn)的情況如下。①in_pmt:='hello';該語(yǔ)句是錯(cuò)誤的，因?yàn)镮N類(lèi)型參數(shù)只能作為形參來(lái)傳遞值，不能在函數(shù)體中賦值。②return_char:=in_pmt;該語(yǔ)句語(yǔ)法正確。因?yàn)镮N類(lèi)型參數(shù)本身就是用來(lái)傳遞值的，而return_char則是作為返回值變量，通過(guò)IN類(lèi)型參數(shù)in_pmt給return_char賦值。③out_pmt:='hello';該語(yǔ)句正確。因?yàn)閛ut_pmt作為OUT類(lèi)型參數(shù)，在函數(shù)體內(nèi)被賦值是允許的。④return_char:=out_pmt;該語(yǔ)句不正確。因?yàn)镺UT類(lèi)型參數(shù)不能傳遞值。⑤in_out_pmt:='world';該語(yǔ)句正確。因?yàn)镮NOUT類(lèi)型參數(shù)可以在函數(shù)體中被賦值。⑥r(nóng)eturn_char:=in_out_pmt;該語(yǔ)句正確。因?yàn)镮NOUT類(lèi)型參數(shù)既能傳遞值，也可以賦值。創(chuàng)建函數(shù)

【例6.28】

計(jì)算某門(mén)課程全體學(xué)生的平均成績(jī)。CREATEORREPLACEFUNCTIONaverage(cnumINchar) RETURNnumberAS avgernumber; /*定義返回值變量*/BEGIN SELECTAVG(成績(jī))INTOavger FROMCJB WHERE課程號(hào)=cnum GROUPBY課程號(hào); RETURN(avger);END;創(chuàng)建函數(shù)

【例6.29】

創(chuàng)建一個(gè)統(tǒng)計(jì)數(shù)據(jù)庫(kù)中不同性別人數(shù)的函數(shù)。CREATEORREPLACEFUNCTIONcount_num(in_sexINchar) RETURNnumberAS out_numnumber; /*定義返回值變量*/BEGIN IFin_sex='男'THEN SELECTCOUNT(性別)INTOout_num FROMxsb WHERE性別='男'; ELSE SELECTCOUNT(性別)INTOout_num FROMxsb WHERE性別='女'; ENDIF; RETURN(out_num); /*返回語(yǔ)句*/ENDcount_num; /*函數(shù)結(jié)束*/02調(diào)用函數(shù)調(diào)用函數(shù)無(wú)論是命令行還是程序語(yǔ)句，都可以通過(guò)名稱(chēng)直接在表達(dá)式中調(diào)用函數(shù)，語(yǔ)法格式為：

變量名:=函數(shù)名[(實(shí)參1,實(shí)參2,…)]【例6.30】

用函數(shù)count_num統(tǒng)計(jì)XSB表中有多少女學(xué)生。DECLARE girl_numnumber;BEGIN girl_num:=count_num('女'); DBMS_OUTPUT.PUT_LINE(TO_CHAR(girl_num));END;輸出結(jié)果為：8。03刪除函數(shù)

刪除函數(shù)

用DROPFUNCTION語(yǔ)句可以刪除用戶(hù)定義的函數(shù)，語(yǔ)法格式為：DROPFUNCTION[用戶(hù)方案名.]函數(shù)名例如，要把函數(shù)count_num刪除，只需執(zhí)行如下語(yǔ)句：DROPFUNCTIONcount_num;第6章PL/SQL游

標(biāo)01顯式游標(biāo)1．聲明游標(biāo)2．打開(kāi)游標(biāo)3．讀取數(shù)據(jù)4．關(guān)閉游標(biāo)5．注意事項(xiàng)顯式游標(biāo)1．聲明游標(biāo)顯式游標(biāo)是作為聲明段的一部分進(jìn)行定義的，定義方法如下：DECLARECURSOR游標(biāo)名 IS SELECT語(yǔ)句其中，游標(biāo)名是與某個(gè)查詢(xún)結(jié)果集聯(lián)系的符號(hào)名，要遵循Oracle系統(tǒng)變量定義的規(guī)則。SELECT語(yǔ)句可查詢(xún)產(chǎn)生與所聲明游標(biāo)相關(guān)聯(lián)的結(jié)果集。例如：DECLARECURSORXS_CUR IS SELECT學(xué)號(hào),姓名,總學(xué)分 FROMxsb WHERE專(zhuān)業(yè)='計(jì)算機(jī)';顯式游標(biāo)2．打開(kāi)游標(biāo)聲明后，要使用游標(biāo)就必須先打開(kāi)它。使用OPEN語(yǔ)句打開(kāi)游標(biāo)，其格式為：

OPEN游標(biāo)名打開(kāi)游標(biāo)后，可以使用系統(tǒng)變量%ROWCOUNT返回最近一次提取到數(shù)據(jù)行的序列號(hào)。在打開(kāi)游標(biāo)之后，且提取數(shù)據(jù)之前可訪問(wèn)%ROWCOUNT值，并返回0。【例6.31】

定義游標(biāo)XS_CUR，然后打開(kāi)游標(biāo)，輸出當(dāng)前行的序列號(hào)。DECLARECURSORxs_cur IS SELECT學(xué)號(hào),姓名,總學(xué)分 FROMxsb; BEGIN OPENxs_cur; DBMS_OUTPUT.PUT_LINE(xs_cur%ROWCOUNT); END;輸出結(jié)果為：0。顯式游標(biāo)3．讀取數(shù)據(jù)游標(biāo)打開(kāi)后，就可以使用FETCH語(yǔ)句從中讀取數(shù)據(jù)。FETCH語(yǔ)句的格式為：FETCH游標(biāo)名[INTO變量名,…n]其中，INTO子句將讀取的數(shù)據(jù)存放到指定的變量中。【例6.32】

將計(jì)算機(jī)專(zhuān)業(yè)每個(gè)學(xué)生的“學(xué)號(hào)”與“總學(xué)分”相加后的值輸出。DECLARE v_xhchar(6); v_zxfnumber(2); CURSORxs_cur IS SELECT學(xué)號(hào),總學(xué)分 FROMxsb WHERE專(zhuān)業(yè)='計(jì)算機(jī)'; BEGIN OPENxs_cur; FETCHxs_curINTOv_xh,v_zxf; WHILExs_cur%FOUND LOOP DBMS_OUTPUT.PUT_LINE(v_xh+v_zxf); FETCHxs_curINTOv_xh,v_zxf; ENDLOOP; CLOSEXS_CUR; END;輸出結(jié)果如圖。顯式游標(biāo)4．關(guān)閉游標(biāo)游標(biāo)使用完以后要及時(shí)關(guān)閉。關(guān)閉游標(biāo)可使用CLOSE語(yǔ)句，其格式為：CLOSE游標(biāo)名;如關(guān)閉上例中的游標(biāo)：CLOSEXS_CUR;關(guān)閉游標(biāo)即關(guān)閉SELECT操作，釋放所占的內(nèi)存區(qū)。顯式游標(biāo)5．注意事項(xiàng)（1）用%FOUND或%NOTFOUND檢驗(yàn)游標(biāo)操作成功與否。%FOUND屬性表示當(dāng)前游標(biāo)是否指向有效的一行，如果游標(biāo)能按照其選擇條件從數(shù)據(jù)庫(kù)中成功查詢(xún)出一行數(shù)據(jù)，則返回TRUE表示成功，否則返回