PL SQL語言介紹詳細

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

PL/SQL變量、常量和數據類型04

PL/SQL基本程序結構和語句05

系統(tǒng)內置函數06

函數07

游標08

程序包的使用1

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

PL/SQL字符集主要內容

合法字符01

運算符02

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

PL/SQL變量、常量和數據類型主要內容

定義變量和常量

基本數據類型

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

PL/SQL基本程序結構和語句主要內容

PL/SQL程序塊

選擇語句

循環(huán)結構010203

選擇和跳轉語句

異常

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

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

exit

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

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

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

循環(huán)結束條件修改;endloop;2．while...loop語句循環(huán)結束？false其它語句true設置循環(huán)初始化條件循環(huán)體代碼修改循環(huán)結束條件循環(huán)結束？false其它語句true設置循環(huán)初始化條件循環(huán)體代碼修改循環(huán)結束條件循環(huán)結構for語句是一個可預置循環(huán)次數的循環(huán)控制語句，它有一個循環(huán)計數器，通常是一個整型變量，通過這個循環(huán)計數器來控制循環(huán)執(zhí)行的次數。該計數器可以從小到大進行記錄，也可以相反，從大到小進行記錄。另外，該計數器值的合法性由上限值和下限值控制，若計數器值在上限值和下限值的范圍內，則程序執(zhí)行循環(huán)；否則，終止循環(huán)，其語法格式如下。forvariable_counter_namein[reverse]lower_limit..upper_limitloopplsql_sentence;endloop;3．for語句選擇和跳轉語句在case關鍵字的后面有一個選擇器，它通常是一個變量，程序就從這個選擇器開始執(zhí)行，接下來是when子句，并且在when關鍵字的后面是一個表達式，程序將根據選擇器的值去匹配每個when子句中的表達式的值，從而實現執(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語句選擇和跳轉語句GOTO語句的語法是：GOTOlabel;這是個無條件轉向語句。當執(zhí)行GOTO語句時，控制程序會立即轉到由標簽標識的語句。2．goto語句異常異常處理語法：WHEN異常類型|用戶定義異常|異常代碼|OTHERSTHEN異常處理異常預定義異常用戶自定義異?？詹僮骱涂罩涤袝r，特別是使用IF邏輯時，用戶需要結束測試一個條件。當測試條件為TRUE時，什么工作都不做；而當測試值為FALSE時，則執(zhí)行某些操作。在PL/SQL中以下述方法處理：IFn<0THENNULLELSEDBMS_OUTPUT.PUT_LINE('正常');關鍵字NULL表示不執(zhí)行操作。5

系統(tǒng)內置函數主要內容

字符類函數

數字類函數

日期和時間類函數010203

轉換類函數

聚集類函數0405字符類函數

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

CONCAT（s1,s2）函數

INITCAP（s）函數

INSTR（s1,s2[,i][,j]）函數

LENGTH（s）函數

LOWER（s）函數和UPPER函數（s）

LTRIM（s1,s2）函數、RTRIM（s1,s2）函數和TRIM（s1,s2）函數

REPLACE（s1,s2[,s3]）函數

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

函數 主要內容

函數的創(chuàng)建與調用

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

游標 主要內容

顯式游標

隱式游標0102

使用游標變量

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

is

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

程序包的使用 主要內容

程序包的規(guī)范

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