PL SQL語言介紹詳細

第6章PL/SQL語言介紹01PL/SQL概述02PL/SQL字符集03

PL/SQL變量、常量和數(shù)據(jù)類型04

PL/SQL基本程序結(jié)構(gòu)和語句05

系統(tǒng)內(nèi)置函數(shù)06

函數(shù)07

游標(biāo)08

程序包的使用1

PL/SQL概述PL/SQL與SQL的不同Oracle數(shù)據(jù)庫客戶應(yīng)用SQLSQLSQLOracle數(shù)據(jù)庫客戶應(yīng)用SQLSQLSQLSQL執(zhí)行PL/SQL執(zhí)行2

PL/SQL字符集主要內(nèi)容

合法字符01

運算符02

其他符號03PL/SQL字符集合法字符在使用PL/SQL進行程序設(shè)計時，可以使用的有效字符包括以下3類。（1）所有的大寫和小寫英文字母。（2）數(shù)字0~9。（3）符號()、+、-、*、/、<、>、=、!、~、;、:、.、’、@、%、,、”、#、^、&、_、{、}、?、[、]。PL/SQL字符集1．算術(shù)運算符+（加）、-（減）、*（乘）、/（除）、**（指數(shù)）和||（連接字符）運算符PL/SQL字符集2．關(guān)系運算符（1）=（等于）、<>或!=（不等于）、<（小于）、>（大于）、>=（大于等于）、<=（小于等于）；（2）BETWEEN最小值A(chǔ)ND最大值（檢索兩值之間的內(nèi)容）；（3）IN（檢索匹配列表中的值）；（4）LIKE（檢索匹配字符樣式的數(shù)據(jù)）；（5）ISNULL/ISNOTNULL（判斷是否為null）。PL/SQL字符集3．邏輯運算符邏輯運算符用于對某個條件進行測試，運算結(jié)果為TRUE或FALSE。Oracle提供的邏輯運算符有：（1）AND（兩個表達式同時為真則結(jié)果為真）；（2）OR（只要有一個為真則結(jié)果為真）；（3）NOT（取相反的邏輯值）。PL/SQL字符集其他符號符號意義樣例()列表分隔('Json','king');語句結(jié)束select*fromemp;.項分離（在例子中分離account）select*fromaccount.table_name'字符串界定符'king':=賦值a:=a+1||并置Full_name:='Narth'||'Yebba'--注釋符--thisisacomment/*注釋界定符/*thisisacommenttoo*/3

PL/SQL變量、常量和數(shù)據(jù)類型主要內(nèi)容

定義變量和常量

基本數(shù)據(jù)類型

特殊數(shù)據(jù)類型010203PL/SQL程序塊[DECLARE] --聲明部分，可選BEGIN --執(zhí)行部分，必須[EXCEPTION] --異常處理部分，可選END標(biāo)準(zhǔn)PL/SQL塊的語法格式如下：定義變量和常量1．定義變量變量是指其值在程序運行過程中可以改變的數(shù)據(jù)存儲結(jié)構(gòu)，定義變量必須的元素就是變量名和數(shù)據(jù)類型，另外還有可選擇的初始值，其標(biāo)準(zhǔn)語法格式如下：<變量名><數(shù)據(jù)類型>[(長度):=<初始值>];定義變量和常量2．定義常量常量是指其值在程序運行過程中不可改變的數(shù)據(jù)存儲結(jié)構(gòu)，定義常量必須的元素包括常量名、數(shù)據(jù)類型、常量值和constant關(guān)鍵字，其標(biāo)準(zhǔn)語法格式如下：<常量名>constant<數(shù)據(jù)類型>:=<常量值>;定義變量和常量3．變量初始化許多語言沒有規(guī)定未經(jīng)過初始化的變量中應(yīng)該存放什么內(nèi)容。因此在運行時，未初始化的變量就可能包含隨機的或者未知的取值。在一種語言中，運行使用未初始化變量并不是一種很好的編程風(fēng)格。一般而言，如果變量的取值可以被確定，那么最好為其初始化一個數(shù)值。但是，PL/SQL定義了一個未初始化變量應(yīng)該存放的內(nèi)容，被賦值為NULL。NULL意味著“未定義或未知的取值”。換句話講，NULL可以被默認地賦值給任何未經(jīng)過初始化的變量。這是PL/SQL的一個獨到之處。許多其他程序設(shè)計語言沒有定義未初始化變量的取值?；緮?shù)據(jù)類型數(shù)值類型主要包括NUMBER、PLS_INTEGER和BINARY_INTEGER三種基本類型。其中，NUMBER類型的變量可以存儲整數(shù)或浮點數(shù)；而BINARY_INTEGER或PLS_INTEGER類型的變量只存儲整數(shù)。1．?dāng)?shù)值類型NUMBER(m,n)小數(shù)位數(shù)總的位數(shù)NUMBER(m)總的位數(shù)number(6,2)基本數(shù)據(jù)類型字符類型主要包括VARCHAR2、CHAR、LONG、NCHAR和NVARCHAR2等。這些類型的變量用來存儲字符串或字符數(shù)據(jù)。2．字符類型VARCHAR2(m)m<32767CHAR(m)m<32767有默認值1基本數(shù)據(jù)類型日期類型只有一個種——即DATE類型，用來存儲日期和時間信息，DATE類型的存儲空間是7個字節(jié)，分別使用一個字節(jié)存儲世紀(jì)、年、月、天、小時、分鐘和秒。3．日期類型布爾類型也只有一種——即BOOLEAN，主要用于程序的流程控制和業(yè)務(wù)邏輯判斷，其變量值可以是TRUE、FALSE或NULL中的一種。4．布爾類型特殊數(shù)據(jù)類型使用%TYPE關(guān)鍵字可以聲明一個與指定列名稱相同的數(shù)據(jù)類型，它通常緊跟在指定列名的后面。1．%TYPE類型特殊數(shù)據(jù)類型單詞RECORD有“記錄”之意，因此RECORD類型也稱作“記錄類型”，使用該類型的變量可以存儲由多個列值組成的一行數(shù)據(jù)。在聲明記錄類型變量之前，首先需要定義記錄類型，然后才可以聲明記錄類型的變量。記錄類型是一種結(jié)構(gòu)化的數(shù)據(jù)類型，它使用type語句進行定義，在記錄類型的定義結(jié)構(gòu)中包含成員變量及其數(shù)據(jù)類型，其語法格式如下：typerecord_typeisrecord(var_member1data_type[notnull][:=default_value],…var_memberndata_type[notnull][:=default_value])2．RECORD類型typeemp_typeisrecord(var_enamevarchar2(20),var_jobvarchar2(20),var_salnumber);特殊數(shù)據(jù)類型%ROWTYPE類型的變量結(jié)合了“%TYPE類型”和“記錄類型”變量的優(yōu)點，它可以根據(jù)數(shù)據(jù)表中行的結(jié)構(gòu)定義一種特殊的數(shù)據(jù)類型，用來存儲從數(shù)據(jù)表中檢索到的一行數(shù)據(jù)。它的語法形式很簡單，如下所示。rowVar_nametable_name%rowtype;3．%ROWTYPE類型rowVar_empemp%rowtype4

PL/SQL基本程序結(jié)構(gòu)和語句主要內(nèi)容

PL/SQL程序塊

選擇語句

循環(huán)結(jié)構(gòu)010203

選擇和跳轉(zhuǎn)語句

異常

空操作和空值040506PL/SQL程序塊[DECLARE] --聲明部分，可選BEGIN --執(zhí)行部分，必須[EXCEPTION] --異常處理部分，可選END標(biāo)準(zhǔn)PL/SQL塊的語法格式如下：選擇語句if…then語句是選擇語句中最簡單的一種形式，它只做一種情況或條件判斷，其語法格式如下：if<condition_expression>thenplsql_sentenceendif;1．If...then語句判斷條件語句1true語句2false選擇語句if…then…elsif語句實現(xiàn)了多分支判斷選擇，它使程序的判斷選擇條件更加豐富，更加多樣化，該語句中的哪個判斷分支的表達式為true，那么程序就會執(zhí)行其下面對應(yīng)的PL/SQL語句，其語法格式如下：if<condition_expression1>thenplsql_sentence_1;elsif<condition_expression2>thenplsql_sentence_2;…elseplsql_sentence_n;endif;2．If...then...elsif語句判斷條件1語句1true語句3false判斷條件2false語句2true語句4選擇語句在編寫程序的過程中，if…then…else語句是最常用到的一種選擇語句，它可以實現(xiàn)判斷兩種情況，只要if后面的條件表達式為false，程序就會執(zhí)行else語句下面的PL/SQL語句。其語法格式如下：if<condition_expression>thenplsql_sentence1;elseplsql_sentence2;endif;3．If...then...else語句判斷條件語句1true語句2false語句2循環(huán)結(jié)構(gòu)loop

循環(huán)執(zhí)行的語句塊;

exit

when循環(huán)結(jié)束條件;

循環(huán)結(jié)束條件修改;endloop;1．loop語句while(循環(huán)結(jié)束條件)loop

循環(huán)執(zhí)行的語句塊;

循環(huán)結(jié)束條件修改;endloop;2．while...loop語句循環(huán)結(jié)束？false其它語句true設(shè)置循環(huán)初始化條件循環(huán)體代碼修改循環(huán)結(jié)束條件循環(huán)結(jié)束？false其它語句true設(shè)置循環(huán)初始化條件循環(huán)體代碼修改循環(huán)結(jié)束條件循環(huán)結(jié)構(gòu)for語句是一個可預(yù)置循環(huán)次數(shù)的循環(huán)控制語句，它有一個循環(huán)計數(shù)器，通常是一個整型變量，通過這個循環(huán)計數(shù)器來控制循環(huán)執(zhí)行的次數(shù)。該計數(shù)器可以從小到大進行記錄，也可以相反，從大到小進行記錄。另外，該計數(shù)器值的合法性由上限值和下限值控制，若計數(shù)器值在上限值和下限值的范圍內(nèi)，則程序執(zhí)行循環(huán)；否則，終止循環(huán)，其語法格式如下。forvariable_counter_namein[reverse]lower_limit..upper_limitloopplsql_sentence;endloop;3．for語句選擇和跳轉(zhuǎn)語句在case關(guān)鍵字的后面有一個選擇器，它通常是一個變量，程序就從這個選擇器開始執(zhí)行，接下來是when子句，并且在when關(guān)鍵字的后面是一個表達式，程序?qū)⒏鶕?jù)選擇器的值去匹配每個when子句中的表達式的值，從而實現(xiàn)執(zhí)行不同的PL/SQL語句，其語法格式如下：case<selector>when<expression_1>thenplsql_sentence_1;when<expression_2>thenplsql_sentence_2;

…when<expression_n>thenplsql_sentence_n;[elseplsql_sentence;]endcase;1．case語句選擇和跳轉(zhuǎn)語句GOTO語句的語法是：GOTOlabel;這是個無條件轉(zhuǎn)向語句。當(dāng)執(zhí)行GOTO語句時，控制程序會立即轉(zhuǎn)到由標(biāo)簽標(biāo)識的語句。2．goto語句異常異常處理語法：WHEN異常類型|用戶定義異常|異常代碼|OTHERSTHEN異常處理異常預(yù)定義異常用戶自定義異?？詹僮骱涂罩涤袝r，特別是使用IF邏輯時，用戶需要結(jié)束測試一個條件。當(dāng)測試條件為TRUE時，什么工作都不做；而當(dāng)測試值為FALSE時，則執(zhí)行某些操作。在PL/SQL中以下述方法處理：IFn<0THENNULLELSEDBMS_OUTPUT.PUT_LINE('正常');關(guān)鍵字NULL表示不執(zhí)行操作。5

系統(tǒng)內(nèi)置函數(shù)主要內(nèi)容

字符類函數(shù)

數(shù)字類函數(shù)

日期和時間類函數(shù)010203

轉(zhuǎn)換類函數(shù)

聚集類函數(shù)0405字符類函數(shù)

ASCII(c)函數(shù)和CHR(i)函數(shù)

CONCAT（s1,s2）函數(shù)

INITCAP（s）函數(shù)

INSTR（s1,s2[,i][,j]）函數(shù)

LENGTH（s）函數(shù)

LOWER（s）函數(shù)和UPPER函數(shù)（s）

LTRIM（s1,s2）函數(shù)、RTRIM（s1,s2）函數(shù)和TRIM（s1,s2）函數(shù)

REPLACE（s1,s2[,s3]）函數(shù)

SUBSTR（s,i,[j]）函數(shù)數(shù)字類函數(shù)函數(shù)說明ABS(n)返回n的絕對值CEIL(n)返回大于或等于數(shù)值n的最小整數(shù)COS(n)返回n的余弦值，n為弧度EXP(n)返回e的n次冪，e=2.71828183FLOOR(n)返回小于或等于n的最大整數(shù)LOG(n1,n2)返回以n1為底n2的對數(shù)MOD(n1,n2)返回n1除以n2的余數(shù)POWER(n1,n2)返回n1的n2次方ROUND(n1,n2)返回舍入小數(shù)點右邊n2位的n1的值，n2的默認值為0，這會返回小數(shù)點最接近的整數(shù)。如果n2為負數(shù)，就舍入到小數(shù)點左邊相應(yīng)的位上，n2必須是整數(shù)SIGN(n)若n為負數(shù)，則返回-1，若n為正數(shù)，則返回1，若n=0，則返回0SIN(n)返回n的正弦值，n為弧度SQRT(n)返回n的平方根，n為弧度TRUNC(n1,n2)返回結(jié)尾到n2位小數(shù)的n1的值，n2默認設(shè)置為0，當(dāng)n2為默認設(shè)置時，會將n1截尾為整數(shù)，如果n2為負值，就截尾在小數(shù)點左邊相應(yīng)的位上日期和時間類函數(shù)函數(shù)說明ADD_MONTHS(d,i)返回日期d加上i個月之后的結(jié)果。其中，i為任意整數(shù)LAST_DAY(d)返回包含日期d月份的最后一天MONTHS_BETWEEN(d1,d2)返回d1和d2之間的數(shù)目，若d1和d2的日期都相同，或者都是該月的最后一天，則返回一個整數(shù)，否則返回的結(jié)果將包含一個小數(shù)NEW_TIME(d1,t1,t2)其中，d1是一個日期數(shù)據(jù)類型，當(dāng)時區(qū)t1中的日期和時間是d1時，返回時區(qū)t2中的日期和時間。t1和t2是字符串SYSDATE()返回系統(tǒng)當(dāng)前的日期轉(zhuǎn)換類函數(shù)函數(shù)說明TO_CHAR(x[,format])該函數(shù)實現(xiàn)將表達式轉(zhuǎn)換為字符串，format表示字符串格式TO_DATE(s[,format[lan]])該函數(shù)將字符串s轉(zhuǎn)換成date類型，format表示字符串格式，lan表示所使用的語言TO_NUMBER(s[,format[lan]])該函數(shù)將返回字符串s代表的數(shù)字，返回值按照format格式進行顯示，format表示字符串格式，lan表示所使用的語言聚集類函數(shù)函數(shù)說明AVG(x[DISTINCT|ALL])計算選擇列表項的平均值COUNT(x[DISTINCT|ALL])返回查詢結(jié)果中的記錄數(shù)MAX(x[DISTINCT|ALL])返回選擇列表項目中的最大數(shù)MIN(x[DISTINCT|ALL])返回選擇列表項目中的最小數(shù)SUM(x[DISTINCT|ALL])返回選擇列表項目的數(shù)值總和VARIANCE(x[DISTINCT|ALL])返回選擇列表項目的統(tǒng)計方差STDDEV(x[DISTINCT|ALL])返回選擇列表項目的標(biāo)準(zhǔn)偏差6

函數(shù) 主要內(nèi)容

函數(shù)的創(chuàng)建與調(diào)用

函數(shù)的刪除0102函數(shù)的創(chuàng)建與調(diào)用函數(shù)的創(chuàng)建語法與存儲過程比較類似，它也是一種存儲在數(shù)據(jù)庫中的命名程序塊，函數(shù)可以接受零或多個輸入?yún)?shù)，并且函數(shù)必須有返回值（而這一點存儲過程是沒有的），其定義語法格式如下：create[orreplace]functionfun_name[(parameter1[,parameter2]…)returndata_typeis[inner_variable]beginplsql_sentence;[exception][dowith_sentences;]end[fun_name];1．創(chuàng)建函數(shù)return函數(shù)的創(chuàng)建與調(diào)用由于函數(shù)有返回值，所以在調(diào)用函數(shù)時，必須使用一個變量來保存函數(shù)的返回值，這樣函數(shù)和這個變量就組成了一個賦值表達式。2．調(diào)用函數(shù)函數(shù)的刪除刪除函數(shù)的操作比較簡單，使用dropfunction命令，其后面跟著要刪除的函數(shù)名稱，其語法格式如下：dropfunctionfun_name;7

游標(biāo) 主要內(nèi)容

顯式游標(biāo)

隱式游標(biāo)0102

使用游標(biāo)變量

使用游標(biāo)表達式0304顯式游標(biāo)顯示游標(biāo)是由用戶聲明和操作的一種游標(biāo)，通常用于操作查詢結(jié)果集（即由SELECT語句返回的查詢結(jié)果），使用它處理數(shù)據(jù)的步驟包括：聲明游標(biāo)、打開游標(biāo)、讀取游標(biāo)和關(guān)閉游標(biāo)4個步驟。其中讀取游標(biāo)可能是個反復(fù)操作的步驟，因為游標(biāo)每次只能讀取一行數(shù)據(jù)，所以對于多條記錄，需要反復(fù)讀取，直到游標(biāo)讀取不到數(shù)據(jù)為止，其操作過程如圖所示。顯式游標(biāo)declarecursorcur_name

is

select_sentence;聲明游標(biāo)語法：顯式游標(biāo)opencur_name打開游標(biāo)語法：fetchcur_nameinto{variable，...n};讀取游標(biāo)語法：顯式游標(biāo)聲明一個檢索emp表中雇員信息的游標(biāo)，然后打開游標(biāo)，并指定檢索職務(wù)是“MANAGER”的雇員信息，接著使用fetch…into語句和while循環(huán)讀取游標(biāo)中的所有雇員信息，最后輸出讀取的雇員信息。顯式游標(biāo)closecur_name;關(guān)閉游標(biāo)語法：隱式游標(biāo)在執(zhí)行一個SQL語句時，Oracle會自動創(chuàng)建一個隱式游標(biāo)。這個游標(biāo)是內(nèi)存中處理該語句的工作區(qū)域。隱式游標(biāo)主要是處理數(shù)據(jù)操縱語句（如，UPDATE、DELETE語句）的執(zhí)行結(jié)果，當(dāng)然特殊情況下，也可以處理SELECT語句的查詢結(jié)果。由于隱式游標(biāo)也有屬性，當(dāng)使用隱式游標(biāo)的屬性時，需要在屬性前面加上隱式游標(biāo)的默認名稱——SQL。把emp表中銷售員（即SALESMAN）的工資上調(diào)20%，然后使用隱式游標(biāo)sql的%rowcount屬性輸出上調(diào)工資的員工數(shù)量。使用游標(biāo)變量如同常量和變量的區(qū)別一樣，前面所講的游標(biāo)都是與一個SQL語句相關(guān)聯(lián)，并且在編譯該塊的時候此語句已經(jīng)是可知的，是靜態(tài)的，而游標(biāo)變量可以在運行時與不同的語句關(guān)聯(lián)，是動態(tài)的。游標(biāo)變量被用于處理多行的查詢結(jié)果集。在同一個PL\SQL塊中，游標(biāo)變量不同于特定的查詢綁定，而是在打開游標(biāo)時才確定所對應(yīng)的查詢。因此，游標(biāo)變量可以一次對應(yīng)多個查詢。使用游標(biāo)變量之前，必須先聲明，然后在運行時必須為其分配存儲空間，因為游標(biāo)變量是REF類型的變量，類似于高級語句中的指針。使用游標(biāo)表達式一個游標(biāo)表達式返回一個嵌套游標(biāo)，結(jié)果集中的每一行都包含值加上子查詢生成的游標(biāo)。然而，單個查詢能夠從多個表中提取相關(guān)的值。可以使用嵌套循環(huán)處理這個結(jié)果集，首先提取結(jié)果集的行，然后是這些行中的嵌套游標(biāo)。PL/SQL支持使用游標(biāo)表達式的查詢作為游標(biāo)聲明的一個部分。語法格式如下：CURSOR(subquery)嵌套游標(biāo)在包含的行從父游標(biāo)中提取的時候打開。只有在下列情形下，嵌套游標(biāo)才被關(guān)閉。（1）嵌套游標(biāo)被用戶顯示關(guān)閉。（2）父游標(biāo)被重新執(zhí)行。（3）父游標(biāo)被關(guān)閉。（4）父游標(biāo)被取消。（5）在提取父游標(biāo)的一行時出現(xiàn)錯誤。8

程序包的使用 主要內(nèi)容

程序包的規(guī)范

程序包的主體0102程序包的包頭“規(guī)范”用于規(guī)定在