PLSQL是ORACLE對(duì)標(biāo)準(zhǔn)數(shù)據(jù)庫語言的擴(kuò)展

{管理信息化ORACLE}PLSQL是ORACLE對(duì)標(biāo)準(zhǔn)數(shù)據(jù)庫語言的擴(kuò)展執(zhí)行部分(Executablesection)執(zhí)行部分是PL/SQLBEGIN的PL/SQL塊也可以放在這一部分。異常處理部分(Exceptionsection)這一部分是可選的，在這一部分中處理異?；蝈e(cuò)誤，對(duì)異常處理的詳細(xì)討論我們?cè)诤竺孢M(jìn)行。PL/SQL塊語法[DECLARE]---declarationstatementsBEGIN---executablestatements[EXCEPTION]---exceptionstatementsENDPL/SQL塊中的每一條語句都必須以分號(hào)結(jié)束，SQL語句可以使多行的，但分號(hào)表示該語句的結(jié)束。一行中可以有多條SQLPL/SQL塊由BEGIN或DECLAREEND結(jié)束。注釋由--標(biāo)示。PL/SQL塊的命名和匿名PL/SQL以用在客戶端。命名程序塊可以出現(xiàn)在其他PL/SQL行部分引用，也可以在異常處理部分引用。PL/SQL的PL/SQL程序塊。ORACLE提供了四種類型的可存儲(chǔ)的程序：.函數(shù).過程.包.觸發(fā)器函數(shù)函數(shù)是命名了的、存儲(chǔ)在數(shù)據(jù)庫中的PL/SQL程序塊。函數(shù)接受零個(gè)或多個(gè)輸入?yún)?shù)，有一個(gè)返回值，返回值的數(shù)據(jù)類型在創(chuàng)建函數(shù)時(shí)定義。定義函數(shù)的語法如下：FUNCTIONname[{parameter[,parameter,...])]RETURNdatatypesIS[localdeclarations]BEGINexecutestatements[EXCEPTIONexceptionhandlers]END[name]過程存儲(chǔ)過程是一個(gè)PL/SQL程序塊，接受零個(gè)或多個(gè)參數(shù)作為輸入(INPUT)或輸出(OUTPUT)、或既作輸入又作輸出(INOUT)SQLEXECUT命令或PL/SQL程序塊內(nèi)部調(diào)用，定義存儲(chǔ)過程的語法如下：PROCEDUREname[(parameter[,parameter,...])]IS[localdeclarations]BEGINexecutestatements[EXCEPTIONexceptionhandlers]END[name]包(package)包其實(shí)就是被組合在一起的相關(guān)對(duì)象的集合，當(dāng)包中任何函數(shù)或存儲(chǔ)過程被調(diào)用，包就被加載入內(nèi)存中，包中的任何函數(shù)或存儲(chǔ)過程的子程序訪問速度將大大加快。(body),規(guī)范描述變量、常量、游標(biāo)、和子程序，包體完全定義子程序和游標(biāo)。觸發(fā)器(trigger)變量和常量PL/SQL內(nèi)的東西是可以改變的。聲明變量變量一般都在PL/SQL塊的聲明部分聲明，PL/SQL是一種強(qiáng)壯的類型語言，這就是說在引用變量前必須首先聲明，要在執(zhí)行或異常處理部分使用變量，那么變量必須首先在聲明部分進(jìn)行聲明。聲明變量的語法如下：Variable_name[CONSTANT]databyte[NOTNULL][:=|DEFAULTexpression]注意:可以在聲明變量的同時(shí)給變量強(qiáng)制性的加上NOTNULL約束條件，此時(shí)變量在初始化時(shí)必須賦值。給變量賦值給變量賦值有兩種方式：.直接給變量賦值X:=200;Y=Y+(X*20);.通過SQLSELECTINTO或FETCHINTO給變量賦值SELECTSUM(SALARY),SUM(SALARY*0.1)INTOTOTAL_SALARY,TATAL_COMMISSIONFROMEMPLOYEEWHEREDEPT=10;常量常量與變量相似，但常量的值在程序內(nèi)部不能改變，常量的值在定義時(shí)賦予，，他的聲明方式與變量相似，但必須包括關(guān)鍵字CONSTANT。常量和變量都可被定義為SQL和用戶定義的數(shù)據(jù)類型。ZERO_VALUECONSTANTNUMBER:=0;這個(gè)語句定了一個(gè)名叫ZERO_VALUE、數(shù)據(jù)類型是NUMBER、值為0的常量。標(biāo)量(scalar)數(shù)據(jù)類型標(biāo)量(scalar)數(shù)據(jù)類型沒有內(nèi)部組件，他們大致可分為以下四類：.number.character.date/time.boolean表1顯示了數(shù)字?jǐn)?shù)據(jù)類型；表2顯示了字符數(shù)據(jù)類型；表3顯示了日期和布爾數(shù)據(jù)類型。表1ScalarTypes:NumericDatatypeRangeSubtypesdescriptionBINARY_INTEGER-214748-647NATURAL用于存儲(chǔ)單字節(jié)整數(shù)。NATURAL要求存儲(chǔ)長(zhǎng)度低于NUMBER值。NPOSITIVE用于限制范圍的子類型(SUBTYPE):POSITIVENNATURAL:用于非負(fù)數(shù)SIGNTYPEPOSITIVE:只用于正數(shù)NATURALN:只用于非負(fù)數(shù)和非NULL值POSITIVEN:只用于正數(shù)，不能用于NULL值SIGNTYPE:只有值:-1、0或1.NUMBER1.0E-130-9.99E125DECDECIMALnumber[（[,]）]。DOUBLE缺省的精度是38,scale是0.PRECISIONFLOATINTEGERICINTNUMERICREALSMALLINT與BINARY_INTEGERPLS_INTEGER提PLS_INTEGER-647-647供更好的性能。表2字符數(shù)據(jù)類型datatyperangsubtypedescription最大長(zhǎng)度32767CHARCHARACTER存儲(chǔ)定長(zhǎng)字符串，如果長(zhǎng)度沒有確定，缺省是1字節(jié)最大長(zhǎng)度647LONG存儲(chǔ)可變長(zhǎng)度字符串字節(jié)最大長(zhǎng)度32767用于存儲(chǔ)二進(jìn)制數(shù)據(jù)和字節(jié)字符串，當(dāng)在兩個(gè)數(shù)據(jù)庫之間進(jìn)行傳遞時(shí)，RAW字節(jié)RAW數(shù)據(jù)不在字符集之間進(jìn)行轉(zhuǎn)換。LONGRAW最大長(zhǎng)度647與LONG數(shù)據(jù)類型相似，同樣他也不能在字符集之間進(jìn)行轉(zhuǎn)換。與數(shù)據(jù)庫ROWIDROWID18個(gè)字節(jié)符看作數(shù)據(jù)庫中每一行的唯一鍵值。最大長(zhǎng)度32767與VARCHARVARCHARVARCHAR2STRINGVARCHAR字節(jié)相同表3DATE和BOOLEANdatatyperangedescriptionBOOLEANTRUE/FALSE存儲(chǔ)邏輯值TRUE或FALSE,無參數(shù)DATE014712BC存儲(chǔ)固定長(zhǎng)的日期和時(shí)間值，日期值中包含時(shí)間LOB數(shù)據(jù)類型LOB(大對(duì)象,Largeobject)LOB數(shù)據(jù)對(duì)象可以是二進(jìn)制數(shù)據(jù)也可以是字符數(shù)據(jù)，其最大長(zhǎng)度不超過4GLOBLONG只支持順序訪問方式。LOB"LOB定位符"(LOBlocator)存儲(chǔ)在原始的表PL/SQL中操作LOB數(shù)據(jù)對(duì)象使用ORACLE提供的包DBMS_數(shù)據(jù)類型可分為以下四類：.BFILE.BLOB.CLOB.NCLOB操作符與其他程序設(shè)計(jì)語言相同，PL/SQL有一系列操作符。操作符分為下面幾類：.算術(shù)操作符.關(guān)系操作符.比較操作符.邏輯操作符算術(shù)操作符如表4所示operatoroperation+加-減/除*乘**乘方關(guān)系操作符主要用于條件判斷語句或用于where子串中，關(guān)系操作符檢查條件和結(jié)果是否為true或false,表5是PL/SQL中的關(guān)系操作符operatoroperation<小于操作符<=小于或等于操作符>大于操作符>=大于或等于操作符=等于操作符!=不等于操作符<>不等于操作符:=賦值操作符表6顯示的是比較操作符operatoroperationISNULL如果操作數(shù)為NULL返回TRUELIKE比較字符串值BETWEEN驗(yàn)證值是否在范圍之內(nèi)IN驗(yàn)證操作數(shù)在設(shè)定的一系列值中表7.8顯示的是邏輯操作符operatoroperationAND兩個(gè)條件都必須滿足OR只要滿足兩個(gè)條件中的一個(gè)NOT取反執(zhí)行部分執(zhí)行部分包含了所有的語句和表達(dá)式,執(zhí)行部分以關(guān)鍵字BEGINEXCEPTIONEXCEPTIONEND:=或SELECTINTO或FETCHINTO給每個(gè)變量賦值，執(zhí)行部分的錯(cuò)誤將在異常處理部分解決，在執(zhí)行部分中可以使用另一個(gè)PL/SQL程序塊，這種程序塊被稱為嵌套塊所有的SQL數(shù)據(jù)操作語句都可以用于執(zhí)行部分，PL/SQL塊不能再屏幕上顯示SELECT語句的輸出。SELECT語句必須包括一個(gè)INTO子串或者是游標(biāo)的一部分，執(zhí)行部分使用的變量和常量必須首先在聲NULLCOMMIT和ROLLBACK可以在執(zhí)行部分使用，數(shù)據(jù)定義語言(DataDefinitionlanguage)不能在執(zhí)行部分中使用，DDL語句與EXECUTEIMMEDIATE一起使用或者是DBMS_SQL調(diào)用。執(zhí)行一個(gè)PL/SQL塊SQL*PLUS中匿名的PL/SQL塊的執(zhí)行是在PL/SQL塊后輸入/來執(zhí)行，如下面的例子所示：declarev_m_percentconstantnumber:=10;beginupdateempsetm=sal*v_m_percentwheredeptno=10;endSQL>/PL/SQLproceduresuccessfullypleted.SQL>命名的程序與匿名程序的執(zhí)行不同，執(zhí)行命名的程序塊必須使用execute關(guān)鍵字：createorreplaceprocedureupdate_mission(v_deptinnumber,v_perventinnumberdefault10)isbeginupdateempsetm=sal*v_percentwheredeptno=v_dept;endSQL>/ProcedurecreatedSQL>executeupdate_mission(10,15);PL/SQLproceduresuccessfullypleted.SQL>如果在另一個(gè)命名程序塊或匿名程序塊中執(zhí)行這個(gè)程序，那么就不需要EXECUTE關(guān)進(jìn)字。declarev_deptnumber;beginselecta.deptnointov_deptfromempawherejob='PRESIDENT'update_mission(v_dept);endSQL>/PL/SQLproceduresuccessfullypletedSQL>控制結(jié)構(gòu)控制結(jié)構(gòu)控制PL/SQL程序流程的代碼行,PL/SQL支持條件控制和循環(huán)控制結(jié)構(gòu)。語法和用途IF..THEN語法：IFconditionTHENStatements1;Statements2;....ENDIFIF語句判斷條件condition是否為TRUETHENcondition為false或NULL則跳過THEN到ENDIF之間的語句，執(zhí)行ENDIF后面的語句。IF..THEN...ELSE語法:IFconditionTHENStatements1;Statements2;....ELSEStatements1;Statements2;....ENDIF如果條件condition為TRUE,則執(zhí)行THEN到ELSE之間的語句，否則執(zhí)行ELSE到ENDIF之間的語句。IF可以嵌套，可以在IF或IF..ELSE語句中使用IF或IF..ELSE語句。if(a>b)and(a>c)theng:=a;elseg:=b;ifc>gtheng:=c;endifendifIF..THEN..ELSIF語法：IFcondition1THENstatement1;ELSIFcondition2THENstatement2;ELSIFcondition3THENstatement3;ELSEstatement4;ENDIF;statement5;如果條件condition1為TRUE則執(zhí)行statement1,然后執(zhí)行statement5,否則判斷condition2是否為TRUE,若為TRUE則執(zhí)行statement2,然后執(zhí)行statement5,對(duì)于condition3也是相同的，如果condition1，condition2，condition3都不成立，那么將執(zhí)行statement4,然后執(zhí)行statement5。循環(huán)控制循環(huán)控制的基本形式是LOOP語句，LOOP和ENDLOOP之間的語句將無限次的執(zhí)行。LOOP語句的語法如下：LOOPstatements;ENDLOOPLOOP和ENDLOOPLOOP語句時(shí)必須使用EXIT語句，強(qiáng)制循環(huán)結(jié)束，例如：X:=100;LOOPX:=X+10;IFX>1000THENEXIT;ENDIFENDLOOP;Y:=X;此時(shí)Y的值是1010.EXITWHEN語句將結(jié)束循環(huán)，如果條件為TRUE，則結(jié)束循環(huán)。X:=100;LOOPX:=X+10;EXITWHENX>1000;X:=X+10;ENDLOOP;Y:=X;WHILE..LOOPWHILE..LOOP有一個(gè)條件與循環(huán)相聯(lián)系，如果條件為TRUE,則執(zhí)行循環(huán)體內(nèi)的語句，如果結(jié)果為FALSE,則結(jié)束循環(huán)。X:=100;WHILEX<=1000LOOPX:=X+10;ENDLOOP;Y=X;FOR...LOOP語法：FORcounterIN[REVERSE]start_range....end_rangeLOOPstatements;ENDLOOP;LOOP和WHILE循環(huán)的循環(huán)次數(shù)都是不確定的，F(xiàn)OR循環(huán)的循環(huán)次數(shù)是固定的，counter是一個(gè)隱式聲start_range,第二個(gè)值是start_range+1,直到end_range,如果start_range等于end_range,那么循環(huán)將執(zhí)行一次。如果使用了REVERSE關(guān)鍵字，那么范圍將是一個(gè)降序。X:=100;FORv_counterin1..10loopx:=x+10;endloopy:=x;如果要退出for循環(huán)可以使用EXIT語句。標(biāo)簽用戶可以使用標(biāo)簽使程序獲得更好的可讀性。程序塊或循環(huán)都可以被標(biāo)記。標(biāo)簽的形式是<>。標(biāo)記程序塊<>[DECLARE].........BEGIN........[EXCEPTION].......ENDlabel_name標(biāo)記循環(huán)<>LOOP.........<>loop..........<>loop....EXITouter_loopWHENv_condition=0;endloopinnermost_loop;..........ENDLOOPinner_loop;ENDLOOPouter_loop;GOTO語句語法：GOTOLABEL;執(zhí)行GOTO語句時(shí)，控制會(huì)立即轉(zhuǎn)到由標(biāo)簽標(biāo)記的語句。PL/SQL中對(duì)GOTO語句有一些限制，對(duì)于塊、循環(huán)、IF語句而言，從外層跳轉(zhuǎn)到內(nèi)層是非法的。X：=100;FORV_COUNTERIN1..10LOOPIFV_COUNTER=4THENGOTOend_of_loopENDIFX:=X+10;<>NULLENDLOOPY:=X;注意：NULL是一個(gè)合法的可執(zhí)行語句。嵌套程序塊的內(nèi)部可以有另一個(gè)程序塊這種情況稱為嵌套。嵌套要注意的是變量，定義在最外部程序塊中的變量可以在所有子塊中使用，如果在子塊中定義了與外部程序塊變量相同的變量名，在執(zhí)行子塊時(shí)GOTO語句不能由父塊跳轉(zhuǎn)道子塊中，反之則是合法的?！禣UTERBLOCK》DECLAREA_NUMBERINTEGER；B_NUMBERINTEGER；BEGIN--A_NUMBERandB_NUMBERareavailablehere<>DECLAREC_NUMBERINTEGERB_NUMBERNUMBER(20)BEGINC_NUMBER:=A_NUMBER;C_NUMBER=OUTER_BLOCK.B_NUMBER;ENDSUB_BLOCK;ENDOUT_BLOCK;小結(jié)我們?cè)谶@篇文章中介紹了PL/SQL的基礎(chǔ)語法以及如何使用PL/SQL語言設(shè)計(jì)和運(yùn)行PL/SQL將PL/SQL程序整合到Oracle服務(wù)器中,雖然PL/SQL程序作為功能塊嵌入Oracle數(shù)據(jù)庫中，但PL/SQL與ORACLE數(shù)據(jù)庫的緊密結(jié)合使得越來越多的Oracle數(shù)據(jù)庫管理員和開發(fā)人員開始使用PL/SQL。全面探討PL/SQL的復(fù)合數(shù)據(jù)類型PL/SQL我們將討論記錄和集合的類型、怎樣定義和使用記錄和集合。PL/SQL記錄記錄是PL/SQL的一種復(fù)合數(shù)據(jù)結(jié)構(gòu)，scalar數(shù)據(jù)類型和其他數(shù)據(jù)類型只是簡(jiǎn)單的在包一級(jí)進(jìn)行預(yù)定義，但復(fù)合數(shù)據(jù)類型在使用前必須被定義，記錄之所以被稱為復(fù)合數(shù)據(jù)類型是因?yàn)樗捎蜻@種由數(shù)據(jù)scalarc行或記錄中的每一列或域都可以被引用或單獨(dú)賦值，也可以通過一個(gè)單獨(dú)的語句引用記錄所有的域。在存儲(chǔ)過程或函數(shù)中記錄也可能有參數(shù)。創(chuàng)建記錄在PL/SQL變量，然后才是使用該變量。隱式聲明是在基于表的結(jié)構(gòu)或查詢上使用%TYPE屬性，隱式聲明是一個(gè)更強(qiáng)有力的工具，這是因?yàn)檫@種數(shù)據(jù)變量是動(dòng)態(tài)創(chuàng)建的。顯式定義記錄顯式定義記錄是在PL/SQL程序塊中創(chuàng)建記錄變量之前在聲明部分定義。使用type命令定義記錄，然后在創(chuàng)建該記錄的變量。語法如下：TYPErecord_typeISRECORD(field_definition_list);field_definition_list是由逗號(hào)分隔的列表。域定義的語法如下：field_namedata_type_and_size[NOTNULL][{:=|DEFAULT}default_value]域名必須服從與表或列的命名規(guī)則相同的命名規(guī)則。下面我們看一個(gè)例子：DELCARETYPEstock_quote_recISRECORD(symbol%TYPE,bidNUMBER(10,4),askNUMBER(10,4),volumeNUMBERNOTNULL:=0,exchangeVARCHAR2(6)DEFAULT'NASDAQ');real_time_quotestock_quote_rec;variable域定義時(shí)的%TYPE屬性用于引用數(shù)據(jù)庫中的表或視圖的數(shù)據(jù)類型和大小,而在此之前程序不知道類型和大小。在上面的例子中記錄域在編譯時(shí)將被定義為與列SYMBOL相同的數(shù)據(jù)類型和大小,當(dāng)代碼中要使用來自數(shù)據(jù)庫中的數(shù)據(jù)時(shí)，在變量或域定義中最好使用%TYPE來定義。隱式定義記錄隱式定義記錄中，我們不用描述記錄的每一個(gè)域。這是因?yàn)槲覀儾恍枰x記錄的結(jié)構(gòu)，不需要使用TYPE%ROWTYPE與TYPE命令相同的是它是一種定義獲得數(shù)據(jù)庫數(shù)據(jù)記錄的好方法。DECLAREaccounter_infoaccounts%ROWTYPR;CURSORxactions_cur(acct_noINVARCHAR2)ISSELECTaction,timestamp,holdingFROMportfoliosWHEREaccount_nbr='acct_no';xaction_infoxactions_cur%ROWTYPE;variable有一些PL/SQL指令在使用隱式定義記錄時(shí)沒有使用%ROWTYPE屬性，比如游標(biāo)FOR循環(huán)或觸發(fā)器中的:old和:new記錄。DELCARECURSORxaction_curISSELECTaction,timeamp,holdingFROMportfoliosWHEREaccount_nbr='37';BEGINFORxaction_recinxactions_curLOOPIFxactions_='ORCL'THENnotify_shareholder;ENDIF;ENDLOOP;使用記錄用戶可以給記錄賦值、將值傳遞給其他程序。記錄作為一種復(fù)合數(shù)據(jù)結(jié)構(gòu)意味作他有兩個(gè)層次可用。selectinto或fetch將所有域的值賦給另一個(gè)記錄。在更低的層次，用戶可以處理記錄內(nèi)單獨(dú)的域，用戶可以給單獨(dú)的域賦值或者在單獨(dú)的域上運(yùn)行布爾表達(dá)式，也可以將一個(gè)或更多的域傳遞給另一個(gè)程序。引用記錄記錄由域組成，訪問記錄中的域使用點(diǎn)（.）符號(hào)。我們使用上面的例子看看DELCARETYPEstock_quote_recISRECORD(symbol%TYPE,bidNUMBER(10,4),askNUMBER(10,4),volumeNUMBERNOTNULL:=0,exchangeVARCHAR2(6)DEFAULT'NASDAQ');TYPEdetailed_quote_recISRECORD(quotestock_quote_rec,timestampdate,bid_sizeNUMBER,NUMBER,last_tickVARCHAR2(4));real_time_detaildetail_quote_rec;BEGINreal_time__size:=1000;real_time_.volume:=156700;log_quote(real_time_);給記錄賦值給記錄或記錄中的域賦值的方法有幾種，可以使用SELECTINTO或FETCH給整個(gè)記錄或單獨(dú)的域賦值，可以將整個(gè)記錄的值賦給其他記錄，也可以通過給每個(gè)域賦值來得到記錄，以下我們通過實(shí)例講解每一種賦值方法。1、使用SELECTINTO使用SELECTINTO給記錄賦值要將記錄或域放在INTO子串中，INTO子串中的變量與SELECT中列的位置相對(duì)應(yīng)。例：DECLAREstock_info1stocks%ROWTYPE;stock_info2stocks%ROWTYPE;BEGINSELECTsymbol,exchangeINTOstock_info1.symbol,stock_info1.exchangeFROMstocksWHEREsymbol='ORCL';SELECT*INTOstock_info2FROMstocksWHEREsymbol='ORCL';2、使用FETCH如果SQL語句返回多行數(shù)據(jù)或者希望使用帶參數(shù)的游標(biāo)，那么就要使用游標(biāo)，這種情況下使用FETCH語句代替INSTEADINTO是一個(gè)更簡(jiǎn)單、更有效率的方法，但在安全性較高的包中FETCH的語法如下：FETCHcursor_nameINTOvariable;我們改寫上面的例子：DECLARECURSORstock_cur(symbol_inVARCHAR2)ISSELECTsymbol,exchange,begin_dateFROMstockWHEREsymbol=UPPER(symbol_in);stock_infostock_cur%ROWTYPEBEGINOPENstock_cur('ORCL');FETCHstock_curINTOstock_info;使用賦值語句將整個(gè)記錄復(fù)制給另一個(gè)記錄是一項(xiàng)非常有用的技術(shù)，不過記錄必須精確地被聲明為相同的類型，不能是基于兩個(gè)不同的TYPE語句來獲得相同的結(jié)構(gòu)。例：DECLARETYPEstock_quote_recISRECORD(symbol%TYPE,bidNUMBER(10,4),asknumber(10,4),volumeNUMBER);TYPEstock_quote_tooISRECORD(symbol%TYPE,bidNUMBER(10,4),asknumber(10,4),volumeNUMBER);--這兩個(gè)記錄看上去是一樣的，但實(shí)際上是不一樣的stock_onestocks_quote_rec;stock_twostocks_quote_rec;--這兩個(gè)域有相同的數(shù)據(jù)類型和大小stock_alsostock_rec_too；--與stock_quote_rec是不同的數(shù)據(jù)類型BEGINstock_:='orcl';stock_:=;stock_two：=stock_one;--正確syock_also：=stock_one;--錯(cuò)誤，數(shù)據(jù)類型錯(cuò)誤stock_:=stock_;stock_:=stock_;記錄不能用于INSERT語句和將記錄直接用于比較，下面兩種情況是錯(cuò)誤的：INSERTINTOstocksVALUES(stock_record);和IFstock_rec1>stock_rec2THEN要特別注意考試中試題中有可能用%ROWTYPE用記錄排序的情況，ORACLE不支持記錄之間的直接比較。對(duì)于記錄比較，可以采用下面的兩個(gè)選擇：.設(shè)計(jì)一個(gè)函數(shù)，該函數(shù)返回scalar數(shù)據(jù)類型，使用這個(gè)函數(shù)比較記錄，如IFsort_rec(stock_one)>sort_rec(stock_two)THEN.可以使用數(shù)據(jù)庫對(duì)象，數(shù)據(jù)庫對(duì)象可以使用order或map方法定義，允許oracle對(duì)復(fù)合數(shù)據(jù)類型進(jìn)oracle手冊(cè)。PL/SQL集合ORACLE7.3及以前的版本中只有一種集合稱為PL/SQLINDEX_BYTYPE后才是創(chuàng)建和使用這種類型的變量。集合的類型PL/SQL有三種類型的集合.Index_by表.嵌套表.VARRAY這三種類型的集合之間由許多差異，包括數(shù)據(jù)綁定、稀疏性(sparsity)、數(shù)據(jù)庫中的存儲(chǔ)能力都不相VARRAYIndex_by和嵌套表則是Index_by表可以是稀疏的，但VARRAY類型的集合則是緊密的，它的下標(biāo)之間沒有間隔。Index_by表不能存儲(chǔ)在數(shù)據(jù)庫中，但嵌套表和VARRAY可以被存儲(chǔ)在數(shù)據(jù)庫中。雖然這三種類型的集合有很多不同之處，但他們也由很多相似的地方：.都是一維的類似數(shù)組的結(jié)構(gòu).都有內(nèi)建的方法.訪問由點(diǎn)分隔Index_by表Index_by表集合的定義語法如下：TYPEtype_nameISTABLEOFelement_type[NOTNULL]INDEXBYBINARY_INTERGET;這里面重要的關(guān)鍵字是INDEXBYBINARY_INTERGET，沒有這個(gè)關(guān)鍵字，那么集合將是一個(gè)嵌套表，element_type可以是任何合法的PL/SQL數(shù)據(jù)類型，包括：PLS/INTEGER、SIGNTYPE、和BOOLEAN。其Index_by一旦定義了index_by表，就可以向創(chuàng)建其他變量那樣創(chuàng)建index_by表的變量：DECLARETYPEsymbol_tab_typISTABLEOFVARCHAR2(5)INDEXBYBINARY_INTEGER;symbol_tabsymbol_tab_typ;BEGIN嵌套表嵌套表非常類似于Index_by表，創(chuàng)建的語法也非常相似。使用TYPE語句，只是沒有INDEXBYBINARY_INTEGER子串。TYPEtype_nameISTABLEOFelement_type[NOTNULL]NOTNULL選項(xiàng)要求集合所有的元素都要有值，element_type可以是一個(gè)記錄，但是這個(gè)記錄只能使用標(biāo)量數(shù)據(jù)類型字段以及只用于數(shù)據(jù)庫的數(shù)據(jù)類型（不能是PLS_INTEGER,BOOLEAN或SIGNTYPE）。嵌套表和VARRAYNULL,ORACLE稱這種整個(gè)集合為NULL的為"自動(dòng)設(shè)置為NULL(atomicallyNULL)"以區(qū)別元素為NULL為NULL時(shí)，即使不會(huì)產(chǎn)生異常，用戶也不能引用集合中的元素。用戶可以使用ISNULL操作符檢測(cè)集合是否為NULL。存儲(chǔ)在一個(gè)數(shù)據(jù)庫中的嵌套表并不與表中的其它數(shù)據(jù)存放在同一個(gè)數(shù)據(jù)塊中，它們實(shí)際上被存放在第orderby子句select套表也不保證元素的順序。由于集合數(shù)據(jù)是離線存儲(chǔ)的，對(duì)于大型集合嵌套表是一個(gè)不錯(cuò)的選擇。VARRAYVARRAY或數(shù)據(jù)變量都有元素的限制。想起他集合一樣VARRAY定義仍然使用TYPE語句，但關(guān)鍵字VARRAY或VARRYINGARRAY告訴ORACLE這是一個(gè)VARRAY集合。TYPEtype_nameIS[VARRAY|VARYINGARRAY](max_size)OFelement_type[NOTNULL]max_size是一個(gè)整數(shù)，用于標(biāo)示VARRAY集合擁有的最多元素?cái)?shù)目。VARRAY集合的元素?cái)?shù)量可以低于max_size,但不能超過max_size。element_type是一維元素的數(shù)據(jù)類型，如果element_type是記錄，那么這個(gè)記錄只能使用標(biāo)量數(shù)據(jù)字段（與嵌套標(biāo)相似）。NOTNULL子串表示集合中的每一個(gè)元素都必須有值。與嵌套表相似，VARRAY能夠自動(dòng)為NULL,可以使用ISNULL操作符進(jìn)行檢測(cè)。與嵌套表不同的是，當(dāng)VARRAY存儲(chǔ)在數(shù)據(jù)庫中時(shí)與表中的其他數(shù)據(jù)存放在同一個(gè)數(shù)據(jù)塊中。正象列的排序保存在表的SELECT*中一樣元素的順序保存在VARRAYVARRAY很適合于小型集合。使用集合象記錄一樣，集合可以在兩個(gè)層面上使用：.操作整個(gè)集合.訪問集合中的單個(gè)元素第一種情況使用集合名，第二種情況使用下標(biāo)：collection(subscript)index_by-647--647。嵌套表和VARRAY表示元素在集合中的位置，用戶很難靈活設(shè)計(jì)下標(biāo)，這是因?yàn)椋?嵌套表開始是緊密的（相對(duì)于疏松）.VARRAY始終保持緊密.這兩種集合的下標(biāo)都由1開始初始化、刪除、引用集合使用集合之前必須要初始化，對(duì)于Index_by表初始化是自動(dòng)進(jìn)行的，但是對(duì)于嵌套表和VARRAY就必須使用內(nèi)建的構(gòu)造函數(shù)。如果重新調(diào)用，嵌套表和VARRAY自動(dòng)置NULL，這不只是元素置NULL，而是整個(gè)集合置NULL簡(jiǎn)單的使用賦值操作符。Index_by集合初始化是最簡(jiǎn)單的，只要涉及其中的一個(gè)元素集合就被初始化了。例：DECLARETYPEsymbol_tab_typISTABLEOFVARCHAR2(5)INDEXBYBINARY_INTEGER;TYPEaccount_tab_typISTABLEOFaccount%ROWTYPEINDEXBYBINARY_INTEGER;symbol_tabsymbol_tab_typ;account_tabaccount_tab_typ;new_acct_tabaccount_tab_typ;BEGIN--初始化集合元素147和-3SELECT*INTOaccount_tab(147)FROMaccountsWHEREaccount_nbr=147;SELECT*INTOaccount_tab(-3)FROMaccountsWHEREaccount_nbr=3003;IFaccount_tab(147).balance<500THENchang_maintenance_fee(147);ENDIFnew_acct_tab:=account_tab;symbol_tab(1):="ORCL";symbol_tab(2):="CSCO";symbol_tab(3):="SUNM";publish_portfolio(symbol_tab);嵌套表和VARRAYNULL,那么對(duì)應(yīng)的元素就被初始化為NULL,么元素將保持null例：DECLARETYPEstock_listISTABLEOF%TYPE;TYPEtop10_listISVARRAY(10)OF%TYPE;biotech_stocksstock_list;tech_10top10_list;BEGIN--非法，集合未初始化。biotech_stocks(1):='AMGN';IFbiotech_stocksISNULLTHEN--初始化集合biotech_stocks：=('AMGN','BGEN',IMCL','GERN',CRA');ENDIF;tech_10:=top10_list('ORCL',CSCO','MSFT','INTC','SUNW','IBM',NULL,NULL);IFtech_10(7)ISNULLTHENtech_10(7):='CPQ';ENDtech_10(8):='DELL';BIOTECH_STOCKS初始化有5VARRAYtech_10集合最多能有10個(gè)元素，但構(gòu)造函數(shù)只創(chuàng)建了8個(gè)元素，其中還有兩個(gè)元素是NULL值，并程序中給他們賦值。初始化基于記錄的集合，就必須將記錄傳遞給構(gòu)造函數(shù)，注意不能只是簡(jiǎn)單的將記錄的域傳遞給構(gòu)造函數(shù)。例：DECLARETYPEstock_quote_recISRECORD(symbol%TYPE,bidNUMBER(10,4),askNUMBER(10,4),volumeNUMBERNOTNULL:=0);TYPEstock_tab_typISTABLEOFstock_quote_rec;quote_liststock_tab_typ;single_quotestock_quote_rec;BEGINsingle_:='OPCL';single_:=100;single_:=101;single_:=25000;--合法quote_list:=stock_tab_typ(single_quote);--不合法quote_list:=stock_tab_typ('CSCO',75,76,);DBMS_(quote_list(1).bid);集合的方法除了構(gòu)造函數(shù)外,集合還有很多內(nèi)建函數(shù)，這些函數(shù)稱為方法。調(diào)用方法的語法如下：下表中列出oracle中集合的方法方法描述使用限制COUNT返回集合中元素的個(gè)數(shù)DELETE刪除集合中所有元素DELETE()刪除元素下標(biāo)為x的元素，如果x為null,則集合保持不變對(duì)VARRAY非法DELETE(,)刪除元素下標(biāo)從X到Y(jié)的元素，如果X>Y集合保持不變對(duì)VARRAY非法EXIST()如果集合元素x已經(jīng)初始化，則返回TRUE,否則返回FALSEEXTEND在集合末尾添加一個(gè)元素對(duì)Index_by非法EXTEND()在集合末尾添加x個(gè)元素對(duì)Index_by非法EXTEND(,)在集合末尾添加元素n的x個(gè)副本對(duì)Index_by非法FIRST返回集合中的第一個(gè)元素的下標(biāo)號(hào)，對(duì)于VARRAY集合始終返回1。LAST返回集合中最后一個(gè)元素的下標(biāo)號(hào),對(duì)于VARRAY返回值始終等于COUNT.LIMIT返回VARRY集合的最大的元素個(gè)數(shù)，對(duì)于嵌套表和對(duì)于嵌套表和Index_by為nullIndex_by集合無用返回在元素x之后及緊挨著它的元素的值，如果該元素是最后一個(gè)元素，則返回NEXT()null.返回集合中在元素x之前緊挨著它的元素的值，如果該元素是第一個(gè)元素，則返回PRIOR()null。對(duì)于index_by不合TRIM從集合末端開始刪除一個(gè)元素法TRIM()從集合末端開始刪除x個(gè)元素對(duì)index_by不合法關(guān)于集合之間的比較集合不能直接用于比較，要比較兩個(gè)集合，可以設(shè)計(jì)一個(gè)函數(shù)，該函數(shù)返回一個(gè)標(biāo)量數(shù)據(jù)類型。IFstock_list1>stock_list2----非法IFsort_collection(stock_list1)>sort_collection(stock_list2)THEN--合法但可以比較在集合內(nèi)的兩個(gè)元素。PL/SQL單行函數(shù)和組函數(shù)詳解SQL中Oracle數(shù)都可被稱為SQL或PL/SQL語句，函數(shù)主要分為兩大類：?jiǎn)涡泻瘮?shù)組函數(shù)本文將討論如何利用單行函數(shù)以及使用規(guī)則。SQL中的單行函數(shù)SQL和PL/SQL行數(shù)據(jù)，因此這些都可被統(tǒng)稱為單行函數(shù)。這些函數(shù)均可用于SELECT,WHERE、ORDERBY等子句中，例如下面的例子中就包含了TO_CHAR,UPPER,SOUNDEX等單行函數(shù)。SELECTename,TO_CHAR(hiredate,'day,DD-Mon-YYYY')FROMempWhereUPPER(ename)Like'AL%'ORDERBYSOUNDEX(ename)單行函數(shù)也可以在其他語句中使用，如update的SET子句，INSERT的VALUES子句，DELET的WHERE子句,認(rèn)證考試特別注意在SELECT語句中使用這些函數(shù)，所以我們的注意力也集中在SELECT語句中。NULL和單行函數(shù)在如何理解NULL上開始是很困難的，就算是一個(gè)很有經(jīng)驗(yàn)的人依然對(duì)此感到困惑。NULL值表示一個(gè)NULLNULL值,這個(gè)規(guī)則也CONCAT,DECODE,DUMP,NVL,REPLACE在調(diào)用了NULL參數(shù)時(shí)能夠返回非NULL這些中NVL函數(shù)時(shí)最重要的，因?yàn)樗苤苯犹幚鞱ULL值，NVLNVL(x1,x2),x1和x2都式表達(dá)式，當(dāng)x1為null時(shí)返回X2,否則返回x1。下面我們看看emp數(shù)據(jù)表它包含了薪水、獎(jiǎng)金兩項(xiàng)，需要計(jì)算總的補(bǔ)償columnnameemp_idsalarybonuskeytypepknulls/uniquenn,unnfktabledatatypenumbernumbernumberlength11.211.2不是簡(jiǎn)單的將薪水和獎(jiǎng)金加起來就可以了，如果某一行是null值那么結(jié)果就將是null，比如下面的例子：updateempsetsalary=(salary+bonus)*1.1salary+null,那么就會(huì)得出錯(cuò)誤的結(jié)論，這個(gè)時(shí)候就要使用nvl函數(shù)來排除null值的影響。所以正確的語句是：updateempsetsalary=(salary+nvl(bonus,0)*1.1單行字符串函數(shù)單行字符串函數(shù)用于操作字符串?dāng)?shù)據(jù)，他們大多數(shù)有一個(gè)或多個(gè)參數(shù)，其中絕大多數(shù)返回字符串ASCII()c1是一字符串，返回c1第一個(gè)字母的ASCII碼，他的逆函數(shù)是CHR()SELECTASCII('A')BIG_A,ASCII('z')BIG_zFROMempBIG_ABIG_z65122CHR(＜i＞)[NCHAR_CS]i是一個(gè)數(shù)字，函數(shù)返回十進(jìn)制表示的字符selectCHR(65),CHR(122),CHR(223)FROMempCHR65CHR122CHR223AzBCONCAT(,)c1,c2c2連接到c1c1為null,將返回c2.如果c2為null,則返回c1，如果c1、c2都為null，則返回null。他和操作符||返回的結(jié)果相同selectconcat('slobo','Svoboda')usernamefromdualusernamesloboSyobodaINITCAP()c1符號(hào)限制。selectINITCAP('veni,vedi,vici')CeasarfromdualCeasarVeni,Vedi,ViciINSTR(,[,＜i＞[,]])c1,c2均為字符串，i,j為整數(shù)。函數(shù)返回c2在c1中第j次出現(xiàn)的位置，搜索從c1的第i個(gè)字符開0,如果i從左到右，i和j的缺省值為1.selectINSTR('Mississippi','i',3,3)fromdualINSTR('MISSISSIPPI','I',3,3)11selectINSTR('Mississippi','i',-2,3)fromdualINSTR('MISSISSIPPI','I',3,3)2INSTRB(,[,i[,j])與INSTR（）函數(shù)一樣，只是他返回的是字節(jié)，對(duì)于單字節(jié)INSTRB()等于INSTR()LENGTH()c1為字符串，返回c1的長(zhǎng)度，如果c1為null，那么將返回null值。selectLENGTH('IpsoFacto')ergofromdualergo10LENGTHb()與LENGTH()一樣，返回字節(jié)。lower()返回c的小寫字符，經(jīng)常出現(xiàn)在where子串中selectLOWER(colorname)fromitemdetailWHERELOWER(colorname)LIKE'%white%'COLORNAMEWinterwhiteLPAD(,＜i＞[,])c1,c2ic1的左側(cè)用c2字符串補(bǔ)足致長(zhǎng)度i,i小于c1的長(zhǎng)度，那么只返回i那么長(zhǎng)的c1字符，其他的將被截去。c2的缺省值為單空格，參見RPAD。selectLPAD(answer,7,'')padded,answerunpaddedfromquestion;PADDEDUNPADDEDYesYesNONOMaybemaybeLTRIM(,)把c1中最左邊的字符去掉，使其第一個(gè)字符不在c2中，如果沒有c2，那么c1就不會(huì)改變。selectLTRIM('Mississippi','Mis')fromdualLTRppiRPAD(,＜i＞[,])在c1的右側(cè)用c2字符串補(bǔ)足致長(zhǎng)度i,i小于c1i那么長(zhǎng)的c1字符，其他的將被截去。c2的缺省值為單空格,其他與LPAD相似RTRIM(,)把c1中最右邊的字符去掉，使其第后一個(gè)字符不在c2中，如果沒有c2，那么c1就不會(huì)改變。REPLACE(,[,])c1,c2,c3都是字符串，函數(shù)用c3代替出現(xiàn)在c1中的c2后返回。selectREPLACE('uptown','up','down')fromdualREPLACEdowntownSTBSTR(,＜i＞[,])c1i,jc1的第i位開始返回長(zhǎng)度為jj尾部。selectSUBSTR('Message',1,4)fromdualSUBSMessSUBSTRB(,＜i＞[,])與SUBSTR大致相同，只是I,J是以字節(jié)計(jì)算。SOUNDEX()返回與c1發(fā)音相似的詞selectSOUNDEX('dawes')DawesSOUNDEX('daws')Daws,SOUNDEX('dawson')fromdualDawesDawsDawsonD200D200D250TRANSLATE(,,)將c1中與c2相同的字符以c3代替selectTRANSLATE('fumble','uf','ar')testfromdualTEXTrambleTRIM([[]]fromc3)將c3串中的第一個(gè)，最后一個(gè)，或者都刪除。selectTRIM('spacepadded')trimfromdualTRIMspacepaddedUPPER()返回c1的大寫，常出現(xiàn)where子串中selectnamefromdualwhereUPPER(name)LIKE'KI%'NAMEKING單行數(shù)字函數(shù)單行數(shù)字函數(shù)操作數(shù)字?jǐn)?shù)據(jù)，執(zhí)行數(shù)學(xué)和算術(shù)運(yùn)算。所有函數(shù)都有數(shù)字參數(shù)并返回?cái)?shù)字值。所有三角函數(shù)的操作數(shù)和值都是弧度而不是角度，oracle沒有提供內(nèi)建的弧度和角度的轉(zhuǎn)換函數(shù)。ABS()返回n的絕對(duì)值A(chǔ)COS()反余玄函數(shù)，返回-1到1之間的數(shù)。n表示弧度selectACOS(-1)pi,ACOS(1)ZEROFROMdualPIZERO3.50ASIN()反正玄函數(shù)，返回-1到1，n表示弧度ATAN()反正切函數(shù)，返回n的反正切值，n表示弧度。CEIL()返回大于或等于n的最小整數(shù)。COS()返回n的余玄值，n為弧度COSH()返回n的雙曲余玄值，n為數(shù)字。selectCOSH(<1.4>)FROMdualCOSH(1.4)2.7EXP()返回e的n次冪，e=2.3.FLOOR()返回小于等于N的最大整數(shù)。LN()返回N的自然對(duì)數(shù)，N必須大于0LOG(,)返回以n1為底n2的對(duì)數(shù)MOD()返回n1除以n2的余數(shù)，POWER(,)返回n1的n2次方ROUND(,)返回舍入小數(shù)點(diǎn)右邊n2位的n1的值，n2的缺省值為0n2為負(fù)數(shù)就舍入到小數(shù)點(diǎn)左邊相應(yīng)的位上，n2必須是整數(shù)。selectROUND(12345,-2),ROUND(12345.54321,2)FROMdualROUND(12345,-2)ROUND(12345.54321,2)345.54SIGN()如果n為負(fù)數(shù)，返回-1,如果n為正數(shù)，返回1，如果n=0返回0.SIN（)返回n的正玄值,n為弧度。SINH()返回n的雙曲正玄值,n為弧度。SQRT()返回n的平方根,n為弧度TAN（)返回n的正切值,n為弧度TANH()返回n的雙曲正切值,n為弧度TRUNC(,)返回截尾到n2位小數(shù)的n1n2缺省設(shè)置為0n2為缺省設(shè)置時(shí)會(huì)將n1n2為負(fù)值，就截尾在小數(shù)點(diǎn)左邊相應(yīng)的位上。單行日期函數(shù)單行日期函數(shù)操作DATADATADATA數(shù)據(jù)類型的值。ADD_MONTHS(,＜i＞)返回日期d加上i個(gè)月后的結(jié)果。i可以使任意整數(shù)。如果i是一個(gè)小數(shù)，那么數(shù)據(jù)庫將隱式的他轉(zhuǎn)換成整數(shù)，將會(huì)截去小數(shù)點(diǎn)后面的部分。LAST_DAY()函數(shù)返回包含日期d的月份的最后一天MONTHS_BETWEEN(,)返回d1和d2之間月的數(shù)目,如果d1和d2一個(gè)整數(shù)，否則會(huì)返回的結(jié)果將包含一個(gè)分?jǐn)?shù)。NEW_TIME(,,)d1tz1中的日期和時(shí)間是dtz2tz1和tz2時(shí)字符串。NEXT_DAY(,)返回日期d后由dow給出的條件的第一天，dow使用當(dāng)前會(huì)話中給出的語言指定了一周中的某一天，

返回的時(shí)間分量與d的時(shí)間分量相同。selectNEXT_DAY('01-Jan-2000','Monday')"1stMonday",NEXT_DAY('01-Nov-2004','Tuesday')+7"2ndTuesday")fromdual;1stMonday2ndTuesday03-Jan-200009-Nov-2004ROUND([,])將日期d按照fmt指定的格式舍入，fmt為字符串。SYADATE函數(shù)沒有參數(shù)，返回當(dāng)前日期和時(shí)間。TRUNC([,])返回由fmt指定的單位的日期d.單行轉(zhuǎn)換函數(shù)單行轉(zhuǎn)換函數(shù)用于操作多數(shù)據(jù)類型，在數(shù)據(jù)類型之間進(jìn)行轉(zhuǎn)換。CHARTORWID()c使一個(gè)字符串，函數(shù)將c轉(zhuǎn)換為RWID數(shù)據(jù)類型。SELECTtest_idfromtest_casewhererowid=CHARTORWID('AAAA0SAACAAAALiAAA')CONVERT(,[,])cdsetssetc由sset字符集轉(zhuǎn)換為dsetsset的缺省設(shè)置為數(shù)據(jù)庫的字符集。HEXTORAW()x為16進(jìn)制的字符串，函數(shù)將16進(jìn)制的x轉(zhuǎn)換為RAW數(shù)據(jù)類型。RAWTOHEX()x是RAW數(shù)據(jù)類型字符串，函數(shù)將RAW數(shù)據(jù)類轉(zhuǎn)換為16進(jìn)制的數(shù)據(jù)類型。ROWIDTOCHAR()函數(shù)將ROWID數(shù)據(jù)類型轉(zhuǎn)換為CHAR數(shù)據(jù)類型。TO_CHAR([[,)x是一個(gè)data或number數(shù)據(jù)類型，函數(shù)將x轉(zhuǎn)換成fmt指定格式的char數(shù)據(jù)類型，如果x為日期nlsparm=NLS_DATE_LANGUAGE控制返回的月份和日份所使用的語言。如果x為數(shù)字nlsparm=NLS_NUMERIC_CHARACTERS用來指定小數(shù)位和千分位的分隔符，以及貨幣符號(hào)。NLS_NUMERIC_CHARACTERS="dg",NLS_CURRENCY="string"TO_DATE([,[,）c表示字符串，fmtfmt格式顯示的c,nlsparm表示使用的語言。函數(shù)將字符串c轉(zhuǎn)換成date數(shù)據(jù)類型。TO_MULTI_BYTE()c表示一個(gè)字符串，函數(shù)將c的擔(dān)子截字符轉(zhuǎn)換成多字節(jié)字符。TO_NUMBER([,[,)cfmt表示一個(gè)特殊格式的字符串，函數(shù)返回值按照fmtnlsparm表示語言，函數(shù)將返回c代表的數(shù)字。TO_SINGLE_BYTE()將字符串c中得多字節(jié)字符轉(zhuǎn)化成等價(jià)的單字節(jié)字符。該函數(shù)僅當(dāng)數(shù)據(jù)庫字符集同時(shí)包含單字節(jié)和多字節(jié)字符時(shí)才使用其它單行函數(shù)BFILENAME(,)dir是一個(gè)directory類型的對(duì)象，file為一文件名。函數(shù)返回一個(gè)空的BFILE位置值指示符，函數(shù)用于初始化BFILE變量或者是BFILE列。DECODE(,,[,,,[])x是一個(gè)表達(dá)式，m1是一個(gè)匹配表達(dá)式，x與m1比較，如果m1等于x，那么返回r1,否則,x與m2比較，依次類推m3,m4,m5....直到有返回結(jié)果。DUMP(,[,[,[,]]])x是一個(gè)表達(dá)式或字符，fmt表示8進(jìn)制、10進(jìn)制、16進(jìn)制、或則單字符。函數(shù)返回包含了有關(guān)x的內(nèi)部表示信息的VARCHAR2類型的值。如果指定了n1,n2那么從n1開始的長(zhǎng)度為n2的字節(jié)將被返回。EMPTY_BLOB()該函數(shù)沒有參數(shù)，函數(shù)返回一個(gè)空的BLOB位置指示符。函數(shù)用于初始化一個(gè)BLOB變量或BLOB列。EMPTY_CLOB()該函數(shù)沒有參數(shù)，函數(shù)返回一個(gè)空的CLOB位置指示符。函數(shù)用于初始化一個(gè)CLOB變量或CLOB列。GREATEST()exp_listvarchar2使用的比較是非填充空格類型的比較。LEAST()exp_listvarchar2用的比較是非填充空格類型的比較。UID該函數(shù)沒有參數(shù)，返回唯一標(biāo)示當(dāng)前數(shù)據(jù)庫用戶的整數(shù)。USER返回當(dāng)前用戶的用戶名USERENV()基于opt返回包含當(dāng)前會(huì)話信息。opt的可選值為：ISDBA會(huì)話中SYSDBA腳色響應(yīng)，返回TRUESESSIONID返回審計(jì)會(huì)話標(biāo)示符ENTRYID返回可用的審計(jì)項(xiàng)標(biāo)示符INSTANCE在會(huì)話連接后，返回實(shí)例標(biāo)示符。該值只用于運(yùn)行Parallel服務(wù)器并且有多個(gè)實(shí)例的情況下使用。LANGUAGE返回語言、地域、數(shù)據(jù)庫設(shè)置的字符集。LANG返回語言名稱的ISO縮寫。TERMINAL為當(dāng)前會(huì)話使用的終端或計(jì)算機(jī)返回操作系統(tǒng)的標(biāo)示符。VSIZE()x是一個(gè)表達(dá)式。返回x內(nèi)部表示的字節(jié)數(shù)。SQL中的組函數(shù)組函數(shù)也叫集合函數(shù)，返回基于多個(gè)行的單一結(jié)果，行的準(zhǔn)確數(shù)量無法確定，除非查詢被執(zhí)行并且所有的結(jié)果都被包含在內(nèi)。與單行函數(shù)不同的是，在解析時(shí)所有的行都是已知的。由于這種差別使組函數(shù)與單行函數(shù)有在要求和行為上有微小的差異.組（多行）函數(shù)oracleselect或select的having子句中使用，當(dāng)用于select子串時(shí)常常都和GROUPBY一起使用。AVG([{DISYINCT|ALL}])返回?cái)?shù)值的平均值。缺省設(shè)置為ALL.SELECTAVG(sal),AVG(ALLsal),AVG(DISTINCTsal)FROMAVG(SAL)AVG(ALLSAL)AVG(DISTINCTSAL)1877.77.16.071413COUNT({*|DISTINCT|ALL})返回查詢中行的數(shù)目，缺省設(shè)置是ALL,*表示返回所有的行。MAX([{DISTINCT|ALL}])xVARCHAR2X是一個(gè)DATAX是numericdistinct和all不起作用，應(yīng)為最大值與這兩種設(shè)置是相同的。MIN([{DISTINCT|ALL}])返回選擇列表項(xiàng)目的最小值。STDDEV([{DISTINCT|ALL}])返回選者的列表項(xiàng)目的標(biāo)準(zhǔn)差，所謂標(biāo)準(zhǔn)差是方差的平方根。SUM([{DISTINCT|ALL}])返回選擇列表項(xiàng)目的數(shù)值的總和。VARIANCE([{DISTINCT|ALL}])返回選擇列表項(xiàng)目的統(tǒng)計(jì)方差。用GROUPBY給數(shù)據(jù)分組正如題目暗示的那樣組函數(shù)就是操作那些已經(jīng)分好組的數(shù)據(jù)，我們告訴數(shù)據(jù)庫用GROUPBY怎樣給數(shù)據(jù)分組或者分類，當(dāng)我們?cè)赟ELECT語句的SELECT子句中使用組函數(shù)時(shí)，我們必須把為分組或非常數(shù)列放置在GROUPBYgroupbyselectstat,counter(*)zip_countfromzip_codesGROUPBYstate;STZIP_COUNT-----------AK360AL1212AR1309AZ768CA3982statezip_codes排序,可以用ORDERBY語句，ORDERBY子句可以使用列或組函數(shù)。selectstat,counter(*)zip_countfromzip_codesGROUPBYstateORDERBYCOUNT(*)DESC;STCOUNT(*)----------NY4312PA4297TX4123CA3982用HAVING子句限制分組數(shù)據(jù)現(xiàn)在你已經(jīng)知道了在查詢的SELECT語句和ORDERBY子句中使用主函數(shù)，組函數(shù)只能用于兩個(gè)子串中，組函數(shù)不能用于WHERE子串中，例如下面的查詢是錯(cuò)誤的：錯(cuò)誤SELECTsales_clerk,SUN(sale_amount)FROMgross_salesWHEREsales_dept='OUTSIDE'ANDSUM(sale_amount)>10000GROUPBYsales_clerk這個(gè)語句中數(shù)據(jù)庫不知道SUM()是什么，當(dāng)我們需要指示數(shù)據(jù)庫對(duì)行分組，然后限制分組后的行的輸出時(shí)，正確的方法是使用HAVING語句：SELECTsales_clerk,SUN(sale_amount)FROMgross_salesWHEREsales_dept='OUTSIDE'GROUPBYsales_clerkHAVINGSUM(sale_amount)>10000;嵌套函數(shù)函數(shù)可以嵌套。一個(gè)函數(shù)的輸出可以是另一個(gè)函數(shù)的輸入。操作數(shù)有一個(gè)可繼承的執(zhí)行過程。但函數(shù)DECODE這樣的能被用于邏輯判斷語句IF....THEN...ELSE的函數(shù)。嵌套函數(shù)可以包括在組函數(shù)中嵌套單行函數(shù)，或者組函數(shù)嵌套入單行函數(shù)或組函數(shù)中。比如下面的例子：SELECTdeptno,GREATEST(COUNT(DISTINCTjob),COUNT(DISTINCTmgr)cnt,COUNT(DISTINCTjob)jobs,COUNT(DISTINCTmgr)mgrsFROMempGROUPBYdeptno;DEPTNOCNTJOBSMGRS-----------------104422043430332Oracle數(shù)據(jù)庫數(shù)據(jù)對(duì)象分析（上）Oracle,理解和掌握Oracle數(shù)據(jù)庫對(duì)象是學(xué)習(xí)Oracle的捷徑。表和視圖OracleORACLE8ORACLE8i的功能更強(qiáng)大。視圖是一個(gè)或多個(gè)表中數(shù)據(jù)的邏輯表達(dá)式。本文我們將討論怎樣創(chuàng)建和管理簡(jiǎn)單的表和視圖。管理表表可以看作有行和列的電子數(shù)據(jù)表，表是關(guān)系數(shù)據(jù)庫中一種擁有數(shù)據(jù)的結(jié)構(gòu)。用CREATETABLE語句建立表，在建立表的同時(shí)，必須定義表名，列，以及列的數(shù)據(jù)類型和大小。例如：CREATETABLEproducts(PROD_IDNUMBER(4),PROD_NAMEVAECHAR2(20),STOCK_QTYNUMBER(5,3));這樣我們就建立了一個(gè)名為productsCREATETABLE時(shí)規(guī)定了列的數(shù)據(jù)類型和大小。在創(chuàng)建表的同時(shí)你可以規(guī)定表的完整性約束，也可以規(guī)定列的完整性約束，在列上普通的約束是NOTNULL,關(guān)于約束的討論我們?cè)谝院筮M(jìn)行。null時(shí)，oracle即認(rèn)為該值為缺省值。下列數(shù)據(jù)字典視圖提供表和表的列的信息：.DBA_TABLES.DBA_ALL_TABLES.USER_TABLES.USER_ALL_TABLES.ALL_TABLES.ALL_ALL_TABLES.DBA_TAB_COLUMNS.USER_TAB_COLUMNS.ALL_TAB_COLUMNS表的命名規(guī)則表名標(biāo)識(shí)一個(gè)表，所以應(yīng)盡可能在表名中描述表，oracle中表名或列名最長(zhǎng)可以達(dá)30個(gè)字符串。表名應(yīng)該以字母開始，可以在表名中包含數(shù)字、下劃線、#、$等。從其它表中建立表可以使用查詢從基于一個(gè)或多個(gè)表中建立表，表的列的數(shù)據(jù)類型和大小有查詢結(jié)果決定。建立這種形式的表的查詢可以選擇其他表中所有的列或者只選擇部分列。在CREATETABLE語句中使用關(guān)鍵字AS，例如:SQL>CREATETABLEempASSELECT*FROMemployeeTABLECREATEDSQL>CREATETABLEYASSELECT*FROMXWHEREno=2需要注意的是如果查詢涉及LONG數(shù)據(jù)類型，那么CREATETABLE....ASSELECT....將不會(huì)工作。更改表定義ORACLE使用ALTERTABLE語句來更改表的定義1、增加列語法：ALTERTABLE[schema.]table_nameADDcolumn_definition例：ALTERTABLEordersADDorder_dateDATE;TABLEALTER對(duì)于已經(jīng)存在的數(shù)據(jù)行，新列的值將是NULL.2、更改列語法：ALTERTABLE[schema.]table_nameMODIFYcolumn_namenew_attributes;例：ALTERTABLEordersMODITY(quantitynumber(10,3),statusvarchar2(15));這個(gè)例子中我們修改了表orders，將STATUS列的長(zhǎng)度增加到15,將QUANTITY列減小到10,3;修改列的規(guī)則如下：.可以增加字符串?dāng)?shù)據(jù)類型的列的長(zhǎng)度，數(shù)字?jǐn)?shù)據(jù)類型列的精度。.減少列的長(zhǎng)度時(shí)，該列應(yīng)該不包含任何值，所有數(shù)據(jù)行都為NULL..改變數(shù)據(jù)類型時(shí)，該列的值必須是NULL..對(duì)于十進(jìn)制數(shù)字，可以增加或減少但不能降低他的精度。3、刪除數(shù)據(jù)列優(yōu)化ORACLE數(shù)據(jù)庫，唯一的方法是刪除列，重新建立數(shù)據(jù)庫。在ORACLE8i中有很多方法刪除列，你可以刪除未用數(shù)據(jù)列或者可以標(biāo)示該列為未用數(shù)據(jù)列然后刪除。刪除數(shù)據(jù)列的語法是：ALTERTABLE[schema.]table_nameDROP{COLUMcolumn_names|(column_names)}[CASCADECONSTRAINS]要注意的是在刪除列時(shí)關(guān)于該列的索引和完整性約束也同時(shí)刪除。注意關(guān)鍵字CASCADECONSTRAINS，如果刪除的列是多列約束的一部分，那么這個(gè)約束條件相對(duì)于其他列也同時(shí)刪除。如果用戶擔(dān)心在大型數(shù)據(jù)庫中刪除列要花太多時(shí)間，可以先將他們標(biāo)記為未用數(shù)據(jù)列，標(biāo)記未用數(shù)據(jù)列的語法如下：ALTERTABLE[schema.]table_nameSETUNUSED{COLUMcolumn_names|(column_names)}[CASCADECONSTRAINS]這個(gè)語句將一個(gè)或多個(gè)數(shù)據(jù)列標(biāo)記為未用數(shù)據(jù)列，但并不刪除數(shù)據(jù)列中的數(shù)據(jù)，也不釋放占用的磁盤空間。但是，未用數(shù)據(jù)列在視圖和數(shù)據(jù)字典中并不顯示，并且該數(shù)據(jù)列的名稱將被刪除，新的數(shù)據(jù)列可以使用這個(gè)名稱?；谠摂?shù)據(jù)列的索引、約束，統(tǒng)計(jì)等都將被刪除。刪除未用數(shù)據(jù)列的語句是：ALTERTABLE[schema.]table_nameDROP{UNUSEDCOLUM|COLUMNCONTINUE}刪除表和更改表名刪除表非常簡(jiǎn)單，但它是一個(gè)不可逆轉(zhuǎn)的行為。語法：DROPTABLE[schema.]table_name[CASCADECONSTRAINTS]ORACLE不能刪除視圖，或其他程序單元，但oracle將標(biāo)示他們無效。如果刪除的表涉及引用主鍵或唯一關(guān)鍵字的完整性約束時(shí)，那么DROPTABLE語句就必須包含CASCADECONSTRAINTS子串。更改表名RENAMEORACLE轉(zhuǎn)移到新表中。ORACLE同時(shí)使所有基于舊表的數(shù)據(jù)庫對(duì)象，比如視圖、程序、函數(shù)等，為不合法。語法：RENAMEold_nameTOnew_name;例：SQL>RENAMEordersTOpurchase_orders;TABLERENAMED截短表TRUNCATE命令與DROP命令相似，但他不是刪除整個(gè)數(shù)據(jù)表，所以索引、完整性約束、觸發(fā)器、權(quán)限TRUNCATE語句中要包含REUSESTORAGE子串。TRUNCATE命令語法如下：TRUNCATE{TABLE|CLUSTER}[schema.]name{DROP|REUSESTORAGE}例：SQL>TRUNCATETABLEt1;TABLEtruncate.管理視圖視圖是一個(gè)或多個(gè)表中的數(shù)據(jù)的簡(jiǎn)化描述，用戶可以將視圖看成一個(gè)存儲(chǔ)查詢（storedquery)或一個(gè)虛擬表(virtualtable).查詢僅僅存儲(chǔ)在oracle數(shù)據(jù)字典中，實(shí)際的數(shù)據(jù)沒有存放在任何其它地方，視圖可以有與他所基于表的列名不同的列名。用戶可以建立限制其他用戶訪問的視圖。建立視圖CREATEVIEW命令創(chuàng)建視圖，定義視圖的查詢可以建立在一個(gè)或多個(gè)表，或其他視圖上。查詢不能有FORUPDATE子串，在早期的ORACLE8i版本中不支持ORDERBY子串，現(xiàn)在的版本中CREATEVIEW可以擁有ORDERBY子串。例：SQL>CREATEVIEWTOP_EMPASSELECTempnoEMPLOYEE_ID,enameEMPLOYEE_NAME,salaryFROMempWHEREsalary>2000用戶可以在創(chuàng)建視圖的同時(shí)更改列名，方法是在視圖名后立即加上要命名的列名。重新定義視圖需要包含ORREPLACE子串。SQL>CREATEVIEWTOP_EMP（EMPLOYEE_ID，EMPLOYEE_NAME，SALARY)ASSELECTempno,ename,salaryFROMempWHEREsalary>2000如果在創(chuàng)建的視圖包含錯(cuò)誤在正常情況下，視圖將不會(huì)被創(chuàng)建。但如果你需要?jiǎng)?chuàng)建一個(gè)帶錯(cuò)誤的視圖必須在CREATEVIEW語句中帶上FORCE選項(xiàng)。如：CREATEFORCEVIEWORDER_STATUSASSELECT*FROMPURCHASE_ORDERSWHERESTATUS='APPPOVE';SQL>/warning:Viewcreatewithpilationerrors這樣將創(chuàng)建了一個(gè)名為ORDER_STATUS化則可以重新編譯，其狀態(tài)也變成合法的。從視圖中獲得數(shù)據(jù)SQL函數(shù)，以及所有SELECT語句的字串。插入、更新、刪除數(shù)據(jù)用戶在一定的限制條件下可以通過視圖更新、插入、刪除數(shù)據(jù)。如果視圖連接多個(gè)表，那么在一個(gè)時(shí)間里只能更新一個(gè)表。所有的能被更新的列可以在數(shù)據(jù)字典USER_UPDATETABLE_COLUMNS中查到。用戶在CREATEVIEW中可以使用了WITHWITHREADONLY能進(jìn)行更新、插入、刪除操作。WITHCHECKOPTION表示可以進(jìn)行插入和更新操作，但應(yīng)該滿足WHERE子串的條件。這個(gè)條件就是創(chuàng)建視圖WHERE子句的條件，比如在上面的例子中用戶創(chuàng)建了一個(gè)視圖TOP_EMP,在這個(gè)視圖中用戶不能插入salary小于2000的數(shù)據(jù)行。刪除視圖刪除視圖使用DROPVIEW他涉及到該視圖的函數(shù)、視圖、程序等都將被視為非法。例：DROPVIEWTOP_EMP;Oracle數(shù)據(jù)庫數(shù)據(jù)對(duì)象分析（中）完整性約束完整性約束用于增強(qiáng)數(shù)據(jù)的完整性，Oracle提供了5種完整性約束：CheckNOTNULLUniquePrimaryForeignkeySQL或PL/SQL期間使用。用戶可以指明約束是啟用的還是禁用的，當(dāng)約束啟用時(shí)，他增強(qiáng)了數(shù)據(jù)的完整性，否則，則反之，但約束始終存在于數(shù)據(jù)字典中。禁用約束，使用ALTER語句ALTERTABLEtable_nameDISABLECONSTRAINTconstraint_name;或ALTERTABLEpoliciesDISABLECONSTRAINTchk_gender如果要重新啟用約束：ALTERTABLEpoliciesENABLECONSTRAINTchk_g