2、PLSQL編程基礎(chǔ)

1、PL/SQL編程基礎(chǔ)1目標(biāo)PL/SQL字符類型和詞匯單元PL/SQL變量PL/SQL保留字在PL/SQL中使用標(biāo)識(shí)符使用%TYPE屬性指定數(shù)據(jù)類型聲明和初始化變量變量的作用范圍2PL/SQL字符類型和詞匯單元 2-1字符類型PL/SQL引擎支持4種類型的字符：字母、數(shù)字、符號(hào)(*、+、-、=等)和空格。把這些類型的一個(gè)或者多個(gè)字符組合起來就會(huì)創(chuàng)建一個(gè)詞匯單元詞匯單元詞匯單元就是不同類型字符的組合PL/SQL詞匯單元分為如下5類:標(biāo)識(shí)符必須以字母開頭，最多包含30個(gè)字符保留字是PL/SQL的專用詞(例如BEGIN、END和SELECT等)3PL/SQL字符類型和詞匯單元 2-2操作符是對(duì)PL/S

2、QL有特殊含義的字符，例如，算術(shù)操作符和引號(hào)等字面值是不代表標(biāo)識(shí)符的值(字符、數(shù)值或者布爾 true/false)。如123、“Declaration of Independence”和FALSE等都是字面值的例子注釋可以是單行注釋(-)或者多行注釋(/* */)4PL/SQL變量 4-1變量可以被用來保存臨時(shí)值。變量的語法為: variable-name datatype optional default assignment變量也被稱為標(biāo)識(shí)符。變量必須以字母開頭，最大為30個(gè)字符長(zhǎng)。例如：v_student_idv_last_ name V_LAST_NAME apt_#注意：標(biāo)識(shí)符v_l

3、ast_ name和V_LAST_NAME被認(rèn)為是相同的，因?yàn)镻L/SQL不區(qū)分大小寫5PL/SQL變量 4-2以下是非法標(biāo)識(shí)符：X+Y ：包含一個(gè)加號(hào)，而加號(hào)是PL/SQL預(yù)留使用的1st_year ：以數(shù)字開頭student ID ：包含空格為了使用變量，必須在PL/SQL語句塊的聲明部分中聲明它。必須給變量起一個(gè)名稱，定義其數(shù)據(jù)類型。也可以給這個(gè)變量賦一個(gè)初始值。注意，如果沒有給變量賦初始值，則值為NULL。變量必須首先被聲明，然后才能被引用?？梢园炎兞吭O(shè)置為一個(gè)常量，這意味著這個(gè)變量的值不能被修改6PL/SQL變量 4-3示例1：set serveroutput on;DECLARE

4、v_name VARCHAR2(35); v_dob DATE; v_us_citizen BOOLEAN;BEGIN DBMS_OUTPUT.PUT_LINE (v_name|born on|v_dob);END;A）如果在SQL *Plus中運(yùn)行這個(gè)示例，會(huì)得到什么結(jié)果?只會(huì)得到輸出 born on。原因是v _name和v_dob變量值null，輸出顯示為空白B）當(dāng) v _name和v_dob變量值為null時(shí)，希望分別輸出No Name和01-Jan-1999 ，可以怎么做 ？7PL/SQL變量 4-4在DBMS_OUTPUT.PUT_LINE語句中，使用COALESCE (v_nam

5、e, No Name)替代v_name，使用COALESCE (v_dob，01-Jan-1999)替代v _dobCOALESCE函數(shù)會(huì)把表達(dá)式列表中每個(gè)表達(dá)式與NULL進(jìn)行比較，并返回第1個(gè)非NULL表達(dá)式的值。在此情況下，會(huì)比較v_name變量和 No Name字符串，并返回值 No Name。這是因?yàn)関_name變量尚未被初始化，因此其值還是NULL8PL/SQL保留字 2-1保留字(如BEGIN、END和SELECT等)是專用于PL/SQL的標(biāo)識(shí)符。不能把保留字用作變量名、字面值或者用戶定義異常的名稱示例2：set serveroutput on;DECLARE exception

6、VARCHAR2(15);BEGIN exception :=This is a test; DBMS_OUTPUT.PUT_LINE (exception);END;A)如果運(yùn)行這個(gè)PL/SQL語句塊，會(huì)發(fā)生什么事情呢?9PL/SQL保留字 2-2會(huì)出現(xiàn)一大堆錯(cuò)誤：ORA-06550: 第 3 行, 第 3 列: PLS-00103: 出現(xiàn)符號(hào) EXCEPTION在需要下列之一時(shí)： begin function pragma procedure subtype type current cursor delete exists prior符號(hào) begin在 EXCEPTION 繼續(xù)之前已插入

7、。ORA-06550: 第 5 行, 第 3 列: PLS-00103: 出現(xiàn)符號(hào) EXCEPTION在需要下列之一時(shí)： ( begin case declare exit for goto if loop mod null pragma raise return select update while with con10在PL/SQL中使用標(biāo)識(shí)符 4-1示例3：DECLARE v_var1 VARCHAR2(20); v_var2 VARCHAR2(6); v_var3 NUMBER(5,3);BEGIN v_var1 := string literal; v_var2 := 12.345

8、; v_var3 := 12.345; DBMS_OUTPUT.PUT_LINE (v_var1: |v_var1); DBMS_OUTPUT.PUT_LINE (v_var2: |v_var2); DBMS_OUTPUT.PUT_LINE (v_var3: |v_var3);END;當(dāng)運(yùn)行這個(gè)腳本時(shí)，會(huì)得到什么結(jié)果？11在PL/SQL中使用標(biāo)識(shí)符 4-2分析：在本例中，我們聲明和初始化了3個(gè)變量。給變量分配的值都是字面值。因?yàn)槭褂脝我?hào)括起來了，所以前2個(gè)值string literal和12.345都是字符串字面值。第3個(gè)值12.345是數(shù)字字面值。當(dāng)運(yùn)行這個(gè)腳本，會(huì)得到如下結(jié)果：v_var

9、1: string literalv_var2: 12.345v_var3: 12.345 PL/SQL procedure successfully completed12在PL/SQL中使用標(biāo)識(shí)符 4-3示例4：DECLARE v_var1 NUMBER(2) := 123; v_var2 NUMBER(3) := 123; v_var3 NUMBER(5,3) := 123456.123;BEGIN DBMS_OUTPUT.PUT_LINE (v_var1: |v_var1); DBMS_OUTPUT.PUT_LINE (v_var2: |v_var2); DBMS_OUTPUT.PUT

10、_LINE (v_var3: |v_var3);END;A)如果執(zhí)行這個(gè)PL/SQL語句塊，會(huì)得到什么結(jié)果?13在PL/SQL中使用標(biāo)識(shí)符 4-4在本例中，我們聲明和初始化了3個(gè)數(shù)字變量。第1個(gè)聲明和初始化語句(v_var1 NUMBER (2) : = 123)會(huì)報(bào)錯(cuò)，因?yàn)橹?23超出了指定的精度范圍。第2個(gè)聲明和初始化語句(v_var2 NUMBER (3) : = 123)不會(huì)報(bào)錯(cuò)，因?yàn)橹?23符合指定的精度要求。最后一個(gè)聲明和初始化語句(v_var3 NUMBER (5 , 3): = 123456 .1 23)會(huì)報(bào)錯(cuò)，因?yàn)橹?23456.123超出了指定精度范圍。因此，本例會(huì)得到如下

11、輸出: ORA-06502: PL/SQL: 數(shù)字或值錯(cuò)誤 : 數(shù)值精度太高ORA-06512: 在 line 314使用%TYPE屬性指定數(shù)據(jù)類型 2-1給變量指定的數(shù)據(jù)類型可以基于某種數(shù)據(jù)庫(kù)對(duì)象，這樣使得變量的數(shù)據(jù)類型依賴于底層對(duì)象的數(shù)據(jù)類型。應(yīng)該盡可能使用這種方式，這使得當(dāng)?shù)讓訉?duì)象的數(shù)據(jù)類型發(fā)生變化時(shí)，不需要更改自己的PL/SQL代碼。語法如下所示: variable_name type-attribute%TYPEtype-attribute是對(duì)數(shù)據(jù)庫(kù)列的直接引用示例5：set serveroutput on;DECLARE v_name student.first_name%type

12、; v_grade grade.numeric_grade%type;15使用%TYPE屬性指定數(shù)據(jù)類型 2-2BEGIN DBMS_OUTPUT.PUT_LINE (nvl(v_name,no name)| has grade of |nvl(v_grade,0);END;A)在本例中，聲明了什么？闡述所聲明變量的數(shù)據(jù)類型和值變量v _name聲明時(shí)所使用的數(shù)據(jù)類型與數(shù)據(jù)庫(kù)表STUDENT的first _name列的類型完全相同。換句話說，變量v_ name被定義為VARCHAR2 (25)。除此之外，聲明變量v_grade的數(shù)據(jù)類型與數(shù)據(jù)庫(kù)表GRADE的grade_numeric列的類型完

13、全相同。這也就是說， v_grade變量被定義為NUMBER (3)。每個(gè)變量的值都是NULL16PL/SQL中使用的主要數(shù)據(jù)類型 6-1VARCHAR2(max_length)存儲(chǔ)變長(zhǎng)的字符數(shù)據(jù)參數(shù)是必須的，指定字符數(shù)據(jù)的最大長(zhǎng)度，最大32767字節(jié)不使用常量或者變量來指定最大長(zhǎng)度，必須使用數(shù)字型的字面值VARCHAR2數(shù)據(jù)庫(kù)列的最大寬度是4000字節(jié)CHAR(max_length)存儲(chǔ)定長(zhǎng)的字符數(shù)據(jù)（不足時(shí)使用空格填充）參數(shù)是可選的，指定字符數(shù)據(jù)的最大長(zhǎng)度，最大32767字節(jié)不使用常量或者變量來指定最大長(zhǎng)度，必須使用數(shù)字型的字面值。如果沒有指定最大長(zhǎng)度，則默認(rèn)為1CHAR數(shù)據(jù)庫(kù)列的最大寬度

14、是2000字節(jié)，默認(rèn)值是1字節(jié)17PL/SQL中使用的主要數(shù)據(jù)類型 6-2NUMBER(precision,scale)存儲(chǔ)任何大小的整數(shù)或者浮點(diǎn)數(shù)precision精度是指數(shù)字的總位數(shù)，不含小數(shù)點(diǎn)scale數(shù)值范圍決定是否進(jìn)行四舍五入NUMBER值的最大精度是38個(gè)十進(jìn)制位常量或者變量不能被用于指定精度和數(shù)值范圍，必須使用數(shù)字型的字面值數(shù)值范圍為0到127。當(dāng)sacle=0時(shí)（默認(rèn)值），表示只允許使用整數(shù)，在小數(shù)點(diǎn)后面第1位上進(jìn)行四舍五入，例如123.45變成123；當(dāng)sacle=2時(shí)，表示在小數(shù)點(diǎn)后面第3位上進(jìn)行四舍五入，例如3.456變成3.46數(shù)值范圍可以是負(fù)數(shù)。表示在小數(shù)點(diǎn)的前面進(jìn)行

15、四舍五入。當(dāng)scale= -3時(shí)，表示在小數(shù)點(diǎn)前面的第3位上進(jìn)行四舍五入，例如3 456變成3 000，3 556變成4 00018PL/SQL中使用的主要數(shù)據(jù)類型 6-3BINARY_INTEGER存儲(chǔ)正負(fù)整數(shù)與NUMBER類型比較，該類型的值被存儲(chǔ)為二進(jìn)制格式，占用空間更少計(jì)算速度更快可以存儲(chǔ) -2 147 483 747到2 147 483 747之間的任何整數(shù)值（幾十億）該數(shù)據(jù)類型主要用于索引PL/SQL表，不能在數(shù)據(jù)庫(kù)表中創(chuàng)建類型為BINARY_INTEGER的列BOOLEAN該類型只有3個(gè)值：TRUE、FALSE、NULL不能在數(shù)據(jù)庫(kù)表中創(chuàng)建類型為BOOLEAN的列19PL/SQL

16、中使用的主要數(shù)據(jù)類型 6-4DATE存儲(chǔ)定長(zhǎng)的日期值，7個(gè)字節(jié)DATE變量的有效日期在January 1 4712 BC和December 31 9999 AD的范圍內(nèi)當(dāng)在數(shù)據(jù)庫(kù)列中存儲(chǔ)時(shí)，包含從零時(shí)以來，以秒為單位的時(shí)間信息日期部分的默認(rèn)值是當(dāng)月的第1天；時(shí)間部分的默認(rèn)值是零時(shí)日期實(shí)際存儲(chǔ)為二進(jìn)制格式，并按照默認(rèn)格式進(jìn)行顯示TIMESTAMP該類型是date類型的擴(kuò)展，存儲(chǔ)定長(zhǎng)的日期值，精度到秒的小數(shù)位，最高為十進(jìn)制小數(shù)位的第9位(默認(rèn)是6)。例如，12-jan-2012 09.51.44.000000借助于WITH TIME ZONE或者WITH LOCAL TIME ZONE選項(xiàng)，能夠把

17、TIMESTAMP綁定到特定時(shí)區(qū)20PL/SQL中使用的主要數(shù)據(jù)類型 6-5LONG存儲(chǔ)變長(zhǎng)的字符串類似于VARCHAR2類型，唯一的區(qū)別是long值的最大長(zhǎng)度為2GB不能從LONG列選擇大于4000字節(jié)的值填充到一個(gè)long變量可以在update、insert、select語句中引用LONG列，但是不能在表達(dá)式、SQL函數(shù)調(diào)用或者特定SQL子句（例如where、group by、connect by等）中引用LONG RAW存儲(chǔ)二進(jìn)制數(shù)據(jù)，最大不能超過2GBLOB（大對(duì)象）LOB的四種類型是BLOB、CLOB、NLOB、BFILE?？梢源鎯?chǔ)二進(jìn)制對(duì)象，例如圖像或者視頻文件，最大為4GBBFI

18、LE是存儲(chǔ)在數(shù)據(jù)庫(kù)之外的大型二進(jìn)制文件，最大為4GB21PL/SQL中使用的主要數(shù)據(jù)類型 6-6ROWID每個(gè)Oracle數(shù)據(jù)庫(kù)表都有一個(gè)ROWID偽列，存儲(chǔ)二進(jìn)制值Rowid值用于唯一的標(biāo)識(shí)每行數(shù)據(jù)，提供訪問特定數(shù)據(jù)行的最快捷方式當(dāng)檢索ROWID偽列的值并存入ROWID類型變量時(shí)，可以使用函數(shù)ROWIDTOCHAR，把二進(jìn)制值轉(zhuǎn)換為18字節(jié)的字符串22PL/SQL中使用的操作符算術(shù)運(yùn)算符(* , * , / , + , )比較運(yùn)算符(=, , !=, , =, LIKE, IN, BETWEEN, IS NULL, IS NOT NULL,NOT IN)邏輯運(yùn)算符(AND, OR,NOT)字

19、符串運(yùn)算符(|, LIKE)23聲明和初始化變量在PL/SQL中，變量必須被聲明，以便于被引用。這是在P L/SQL語句塊中最初的聲明部分所完成的每個(gè)聲明語句必須以分號(hào)結(jié)束可以使用賦值操作符（:=）對(duì)變量進(jìn)行賦值。如果把某變量聲明為常量，必須在聲明時(shí)進(jìn)行賦值，該變量的值會(huì)在整個(gè)語句塊中都保持不變24變量的作用域 4-1標(biāo)簽和嵌套語句塊標(biāo)簽可以添加到語句塊，以改進(jìn)代碼的可讀性，以及限定嵌套語句塊中存在相同名稱的元素。標(biāo)簽的名稱必須在可執(zhí)行代碼第1行(或者BEGIN或者DECLARE)的前面，如下所示:SET SERVEROUTPUT ONBEGIN DBMS_OUTPUT.PUT_LINE(Th

20、e procedure find_stu_num has been executed.);END find_stu_num;標(biāo)簽也可以出現(xiàn)在END的后面，目的是為了注釋25變量的作用域 4-2嵌套語句塊是完全位于其它語句塊中的語句塊，例如：BEGIN -outer block BEGIN -inner block END; -end of inner block END; -end of outer block 嵌套語句塊會(huì)對(duì)變量的作用域帶來影響26變量的作用域 4-3變量的作用域變量的作用域是可以訪問變量或者變量可見的程序塊范圍。通常，變量的作用域就是聲明該變量的當(dāng)前語句塊。在外部塊中聲明的變量其作用域就是整個(gè)外部塊（從BEGIN開始一直到END），在嵌套塊中聲明的變量其作用域就是整個(gè)嵌套塊（從BEGIN開始一直到END）。嵌套塊可以直接訪問外部塊中聲明的變量。但是當(dāng)在嵌套塊中聲明了一個(gè)和外部塊中同名的變量時(shí)，嵌套塊就不能直接訪問外部塊中的同名變量了，因?yàn)檫@時(shí)它看不見外部塊中的同名變量。只能通過標(biāo)簽名來引用外部同名變量27變量的作用域 4-4示