第10章 PL_SQL編程語言1

1、數(shù)據(jù)庫開發(fā)技術(shù)數(shù)據(jù)庫開發(fā)技術(shù)福建工程學(xué)院第10章 PL/SQL編程基礎(chǔ)1數(shù)據(jù)庫開發(fā)技術(shù)FJUT 本章要點本章要點編寫編寫PL/SQLPL/SQL匿名塊匿名塊聲明聲明PL/SQL PL/SQL 變量、常量、數(shù)據(jù)類型變量、常量、數(shù)據(jù)類型介紹賦值語句和算術(shù)運(yùn)算符介紹賦值語句和算術(shù)運(yùn)算符理解理解%TYPE%TYPE屬性屬性掌握各種變量的范圍和使用掌握各種變量的范圍和使用如何在如何在PL/SQLPL/SQL塊中編寫可執(zhí)行的代碼塊中編寫可執(zhí)行的代碼 掌握掌握DBMS_OUTPUTDBMS_OUTPUT包與輸入輸出包與輸入輸出嵌套塊應(yīng)遵循的規(guī)則嵌套塊應(yīng)遵循的規(guī)則 準(zhǔn)備編寫簡單的準(zhǔn)備編寫簡單的PL/SQLPL

2、/SQL塊塊數(shù)據(jù)庫開發(fā)技術(shù)FJUT什么是什么是PL/SQL PL/SQL(Procedural language/SQL)是過程語言是過程語言(Procedural Language)與結(jié)構(gòu)化查詢語言與結(jié)構(gòu)化查詢語言(SQL)結(jié)合而結(jié)合而成的編程語言。成的編程語言。 PL/SQL 是對是對 SQL 的擴(kuò)展的擴(kuò)展 PL/SQL支持現(xiàn)代軟件工程的很多重要特征，支持現(xiàn)代軟件工程的很多重要特征， 如如: 數(shù)據(jù)封裝、面向?qū)ο缶幊蹋〝?shù)據(jù)封裝、面向?qū)ο缶幊蹋∣OP)、錯誤（例外）處、錯誤（例外）處理和信息隱藏等。理和信息隱藏等。 PL/SQL是一種是一種“后臺技術(shù)后臺技術(shù)”，它的輸入輸出是面向，它的輸入輸出

3、是面向 數(shù)據(jù)庫的，主要用于編寫服務(wù)器端的程序。數(shù)據(jù)庫的，主要用于編寫服務(wù)器端的程序。數(shù)據(jù)庫開發(fā)技術(shù)FJUT什么是什么是PL/SQL 支持多種數(shù)據(jù)類型，如大對象和集合類型，可使用條件和支持多種數(shù)據(jù)類型，如大對象和集合類型，可使用條件和循環(huán)等控制結(jié)構(gòu)，給循環(huán)等控制結(jié)構(gòu)，給SQL語句的執(zhí)行添加程序邏輯。語句的執(zhí)行添加程序邏輯。 PL/SQL是是Oracle數(shù)據(jù)庫的核心編程語言，用于編數(shù)據(jù)庫的核心編程語言，用于編 寫存儲過程、函數(shù)、觸發(fā)器、包，面向?qū)ο髷?shù)據(jù)庫的寫存儲過程、函數(shù)、觸發(fā)器、包，面向?qū)ο髷?shù)據(jù)庫的 類的定義等等。類的定義等等。 與與 Oracle 服務(wù)器和服務(wù)器和 Oracle 工具緊密集成，

4、具備可移植工具緊密集成，具備可移植性、靈活性和安全性。性、靈活性和安全性。數(shù)據(jù)庫開發(fā)技術(shù)FJUTPL/SQL簡介簡介支持支持 SQL，在，在 PL/SQL 中可以使用：中可以使用：數(shù)據(jù)操縱命令數(shù)據(jù)操縱命令事務(wù)控制命令事務(wù)控制命令游標(biāo)控制游標(biāo)控制SQL 函數(shù)和函數(shù)和 SQL 運(yùn)算符運(yùn)算符可移植性，可運(yùn)行在任何操作系統(tǒng)和平臺上的可移植性，可運(yùn)行在任何操作系統(tǒng)和平臺上的Oralce 數(shù)據(jù)庫數(shù)據(jù)庫更佳的性能，更佳的性能，PL/SQL 經(jīng)過編譯執(zhí)行經(jīng)過編譯執(zhí)行安全性，可以通過存儲過程限制用戶對數(shù)據(jù)的訪問安全性，可以通過存儲過程限制用戶對數(shù)據(jù)的訪問用戶將整個語句塊發(fā)送給 OracleOracleProce

5、dureBegin ProcedureCall SQL Command End數(shù)據(jù)庫開發(fā)技術(shù)FJUTPL/SQL 簡介簡介PL/SQL 引擎駐留在引擎駐留在 Oracle 服務(wù)器中服務(wù)器中該引擎接受該引擎接受 PL/SQL 塊并對其進(jìn)行編譯執(zhí)行塊并對其進(jìn)行編譯執(zhí)行將PL/SQL 塊發(fā)送給 Oracle 服務(wù)器用戶用戶執(zhí)行過程語句引擎將 SQL 語句發(fā)送給SQL 語句執(zhí)行器Oracle 服務(wù)器PL/SQL引擎引擎SQL 語句執(zhí)行器過程語句過程語句執(zhí)行器執(zhí)行器執(zhí)行 SQL 語句將結(jié)果發(fā)送給用戶數(shù)據(jù)庫開發(fā)技術(shù)FJUTPL/SQL基本原理基本原理PL/SQL程序中可以使用大寫或小寫字母程序中可以使用大

6、寫或小寫字母一個完整的語句可以占若干行，但關(guān)鍵字不能被分開寫在兩一個完整的語句可以占若干行，但關(guān)鍵字不能被分開寫在兩行。行。PL/SQL語句包括保留字，標(biāo)識符，定界符，字面值和注釋語句包括保留字，標(biāo)識符，定界符，字面值和注釋保留字保留字（關(guān)鍵字）：專門用途的單詞（關(guān)鍵字）：專門用途的單詞用戶定義標(biāo)識符：用來命名變量、常量、過程、函數(shù)、游用戶定義標(biāo)識符：用來命名變量、常量、過程、函數(shù)、游標(biāo)、表、記錄和異常。標(biāo)、表、記錄和異常。字面值：數(shù)字類型，字符類型和布爾類型字面值：數(shù)字類型，字符類型和布爾類型注釋：注釋：對每個代碼段的目的和用途的描述對每個代碼段的目的和用途的描述數(shù)據(jù)庫開發(fā)技術(shù)FJUTPL/

7、SQL 塊的語法和原則塊的語法和原則標(biāo)識符標(biāo)識符至多有至多有30個字符個字符允許使用字母允許使用字母,數(shù)字?jǐn)?shù)字,$,#,_不能是保留字，但若保留字用雙引號標(biāo)明，不能是保留字，但若保留字用雙引號標(biāo)明，即可作為標(biāo)識符（例如，即可作為標(biāo)識符（例如，SELECT)。必須以字母開頭必須以字母開頭不允許與數(shù)據(jù)庫中表的列名相同不允許與數(shù)據(jù)庫中表的列名相同保留字應(yīng)為大寫形式，以提高可讀性。保留字應(yīng)為大寫形式，以提高可讀性。數(shù)據(jù)庫開發(fā)技術(shù)FJUTPL/SQL 塊的語法和原則塊的語法和原則字面值字面值 字面值是精確的數(shù)值、字符、串或布爾值。字面值是精確的數(shù)值、字符、串或布爾值。 字符或日期型文字必須用單引號標(biāo)識。

8、字符或日期型文字必須用單引號標(biāo)識。 數(shù)字可以是簡單的值數(shù)字可以是簡單的值(如如, 32.5) ，也可是用科學(xué)計，也可是用科學(xué)計數(shù)法表示的數(shù)值數(shù)法表示的數(shù)值(如如, 2E5, 意義意義2*105= 200000) 。v_ename := Henderson;數(shù)據(jù)庫開發(fā)技術(shù)FJUT注釋注釋在單行注釋前加前綴雙短線在單行注釋前加前綴雙短線 (- -)。多行注釋放在多行注釋放在 /* 和和 */之間。之間。舉例：用月工資計算年工資舉例：用月工資計算年工資.SET SERVEROUTPUT ON DECLARE v_sal NUMBER(9,2);BEGIN /* Compute the annual

9、salary based on the monthly salary input from the user */ v_sal := &monthly_sal * 12; DBMS_OUTPUT.PUT_LINE(The annual salary is |TO_CHAR(v_sal);END; - This is the end of the block/ 數(shù)據(jù)庫開發(fā)技術(shù)FJUT在在PL/SQL中顯示輸出結(jié)果中顯示輸出結(jié)果vDBMS_OUTPUTDBMS_OUTPUT是是OracleOracle提供的包提供的包vDBMS_OUTPUTDBMS_OUTPUT包為包為PL/SQLPL/S

10、QL程序提供了對一個內(nèi)部緩沖區(qū)的輸程序提供了對一個內(nèi)部緩沖區(qū)的輸入輸出功能入輸出功能vDBMS_OUTPUTDBMS_OUTPUT包中的過程實現(xiàn)了兩個基本操作：包中的過程實現(xiàn)了兩個基本操作：GET GET 和和 PUTPUTvPUTPUT操作是將過程參數(shù)中的數(shù)據(jù)放入到一個內(nèi)部緩沖區(qū)中，操作是將過程參數(shù)中的數(shù)據(jù)放入到一個內(nèi)部緩沖區(qū)中，而而GETGET操作則是從這個緩沖區(qū)中讀出數(shù)據(jù)，并將其內(nèi)容作為操作則是從這個緩沖區(qū)中讀出數(shù)據(jù)，并將其內(nèi)容作為參數(shù)值返回參數(shù)值返回DBMS_OUTPUT包與輸入輸出包與輸入輸出數(shù)據(jù)庫開發(fā)技術(shù)FJUT在在PL/SQL中顯示輸出結(jié)果中顯示輸出結(jié)果v要使用該方法顯示輸出數(shù)據(jù)

11、，必須將環(huán)境變量作如下的設(shè)置： SET SERVEROUTPUT ON vDBMS_OUTPUT.PUT(字符串表達(dá)式)；用于輸出字符串，但不換行，括號中的參數(shù)是要輸出的字符串表達(dá)式。用于輸出字符串，但不換行，括號中的參數(shù)是要輸出的字符串表達(dá)式。vDBMS_OUTPUT.PUT_LINE(字符串表達(dá)式)；用于輸出一行字符串信息，并換行，括號中的參數(shù)是要輸出的字符串表用于輸出一行字符串信息，并換行，括號中的參數(shù)是要輸出的字符串表達(dá)式。達(dá)式。vDBMS_OUTPUT.NEW_LINE；用來輸出一個換行，沒有參數(shù)。用來輸出一個換行，沒有參數(shù)。 DBMS_OUTPUT包與輸入輸出包與輸入輸出數(shù)據(jù)庫開發(fā)

12、技術(shù)FJUT注釋、替換變量與注釋、替換變量與DBMS_OUTPUT包使用實例包使用實例SET SERVEROUTPUT ONDECLARE v_sal NUMBER(9,2);BEGIN /* SQL中的替換變量在中的替換變量在PL/SQL中仍然有效中仍然有效 */ v_sal:= &monthly_sal*12;-不需要在程序塊中聲明替換變量不需要在程序塊中聲明替換變量 DBMS_OUTPUT.PUT_LINE(The annual salary is | TO_CHAR(v_sal);END;/以下程序只能在以下程序只能在sql-plus環(huán)境中運(yùn)行環(huán)境中運(yùn)行數(shù)據(jù)庫開發(fā)技術(shù)FJUTP

13、L/SQL中的中的SQL函數(shù)函數(shù)過程語句中的有效函數(shù)過程語句中的有效函數(shù):單行數(shù)字單行數(shù)字 單行字符單行字符數(shù)據(jù)類型轉(zhuǎn)換數(shù)據(jù)類型轉(zhuǎn)換日期日期過程語句中的無效函數(shù)過程語句中的無效函數(shù):DECODE分組函數(shù)。分組函數(shù)僅在分組函數(shù)。分組函數(shù)僅在PL/SQL塊中的塊中的SQL語句中適用。語句中適用。與與 SQL中同中同 數(shù)據(jù)庫開發(fā)技術(shù)FJUTPL/SQL 函數(shù)函數(shù)PL/SQL 提供了大量的有效函數(shù)用來處理數(shù)據(jù)，這些函數(shù)提供了大量的有效函數(shù)用來處理數(shù)據(jù)，這些函數(shù)有以下幾類：有以下幾類：l 錯誤報告函數(shù)（錯誤報告函數(shù)（Error reporting）l 數(shù)字函數(shù)（數(shù)字函數(shù)（Number）l 字符函數(shù)（字符

14、函數(shù)（Character）l 轉(zhuǎn)換函數(shù)（轉(zhuǎn)換函數(shù)（Conversion）l 日期函數(shù)（日期函數(shù)（Date）l 雜項函數(shù)（雜項函數(shù)（Miscellaneous）數(shù)據(jù)庫開發(fā)技術(shù)FJUTPL/SQL 函數(shù)函數(shù)例：給一個公司創(chuàng)建一個通信地址.把員工名轉(zhuǎn)換成小寫形式.v_mailing_address := v_name|CHR(10)| v_address|CHR(10)|v_state| CHR(10)|v_zip;v_ename:= LOWER(v_ename);注解：注解：CHR 是一個SQL函數(shù)，把ASCII碼轉(zhuǎn)換成相應(yīng)的字符-。數(shù)據(jù)庫開發(fā)技術(shù)FJUT數(shù)據(jù)類型轉(zhuǎn)換函數(shù)數(shù)據(jù)類型轉(zhuǎn)換函數(shù)把數(shù)據(jù)轉(zhuǎn)換

15、成可比較的數(shù)據(jù)類型。把數(shù)據(jù)轉(zhuǎn)換成可比較的數(shù)據(jù)類型?；旌系臄?shù)據(jù)類型可能會出現(xiàn)錯誤，影響性能混合的數(shù)據(jù)類型可能會出現(xiàn)錯誤，影響性能轉(zhuǎn)換函數(shù)轉(zhuǎn)換函數(shù):TO_CHARTO_DATETO_NUMBERDECLARE v_date VARCHAR2(15);BEGIN SELECT TO_CHAR(hiredate, MON. DD, YYYY) INTO v_date FROM emp WHERE empno = 7839;END;數(shù)據(jù)庫開發(fā)技術(shù)FJUT數(shù)據(jù)類型轉(zhuǎn)換函數(shù)數(shù)據(jù)類型轉(zhuǎn)換函數(shù)l如果variable v_date 被聲明為DATE型，該語句就會產(chǎn)生一個編譯錯誤。v_date := January

16、 13, 1998; v_date := TO_DATE (January 13, 1998, Month DD, YYYY);l使用TO_DATE 轉(zhuǎn)換函數(shù)可改正錯誤。數(shù)據(jù)庫開發(fā)技術(shù)FJUTPL/SQL 塊結(jié)構(gòu)塊結(jié)構(gòu)DECLARE 可選可選變量、游標(biāo)、例外聲明變量、游標(biāo)、例外聲明用戶自定義的特殊類型用戶自定義的特殊類型BEGIN 必須必須SQL 語句語句PL/SQL 語句語句EXCEPTION 可選可選錯誤發(fā)生時的處理動作錯誤發(fā)生時的處理動作END; 必須必須DECLAREBEGINEXCEPTIONEND;PL/SQL是塊結(jié)構(gòu)化語言是塊結(jié)構(gòu)化語言,即程序能被分成若干個邏輯塊。愉中即程序能被

17、分成若干個邏輯塊。愉中對象有自己的作用域。對象有自己的作用域。一個一個PL/SQL塊有三個部分構(gòu)成：聲明（可選）塊有三個部分構(gòu)成：聲明（可選）,執(zhí)行執(zhí)行(必須必須) 和和 異常處理異常處理 (可選可選)。數(shù)據(jù)庫開發(fā)技術(shù)FJUT可選的可選的該部分給出當(dāng)在執(zhí)行部分發(fā)生錯誤該部分給出當(dāng)在執(zhí)行部分發(fā)生錯誤或出現(xiàn)異常情況或出現(xiàn)異常情況時的具體處理動作時的具體處理動作 ?？汕短讐K?？汕短讐K。異常處理異常處理部分部分必須的必須的 該部分包括該部分包括SQL語句（用于處理數(shù)語句（用于處理數(shù)據(jù)庫中的數(shù)據(jù)）及據(jù)庫中的數(shù)據(jù)）及PL/SQL語句（用語句（用于處理塊中的數(shù)據(jù)）。于處理塊中的數(shù)據(jù)）。可嵌套塊。可嵌套塊。執(zhí)

18、行部分執(zhí)行部分可選的可選的該部分包括所有在聲明、執(zhí)行部分該部分包括所有在聲明、執(zhí)行部分所用到的變量、常量、游標(biāo)以及用所用到的變量、常量、游標(biāo)以及用戶自定義的特殊類型。戶自定義的特殊類型。聲明部分聲明部分備注備注描述描述塊結(jié)構(gòu)塊結(jié)構(gòu)PL/SQL 塊結(jié)構(gòu)塊結(jié)構(gòu)數(shù)據(jù)庫開發(fā)技術(shù)FJUTPL/SQL 塊結(jié)構(gòu)塊結(jié)構(gòu)DECLARE v_variable VARCHAR2(5);BEGIN SELECT column_name INTO v_variable FROM table_name;EXCEPTION WHEN exception_name THEN .END;數(shù)據(jù)庫開發(fā)技術(shù)FJUTPL/SQL 塊結(jié)

19、構(gòu)塊結(jié)構(gòu)PL/SQL 塊是構(gòu)成塊是構(gòu)成 PL/SQL 程序的基本單元程序的基本單元將邏輯上相關(guān)的聲明和語句組合在一起將邏輯上相關(guān)的聲明和語句組合在一起 寫一個簡單的寫一個簡單的PL/SQL過程過程,輸出輸出Hello World!set serveroutput onbegin dbms_output.put_line(Hello World!);end;/當(dāng)該塊成功執(zhí)行，沒有任何未經(jīng)處理的錯誤和編譯錯誤時，將會顯示如下語當(dāng)該塊成功執(zhí)行，沒有任何未經(jīng)處理的錯誤和編譯錯誤時，將會顯示如下語句句 ：PL/SQL 過程已成功完成。過程已成功完成。PL/SQL procedure successful

20、ly completed 數(shù)據(jù)庫開發(fā)技術(shù)FJUT數(shù)據(jù)庫開發(fā)技術(shù)FJUT數(shù)據(jù)庫開發(fā)技術(shù)FJUT塊包括兩種類型：匿名塊和命名塊塊包括兩種類型：匿名塊和命名塊匿名塊匿名塊匿名塊是沒有被命名的塊。他們是在應(yīng)用程序即將執(zhí)行時匿名塊是沒有被命名的塊。他們是在應(yīng)用程序即將執(zhí)行時被聲明，在運(yùn)行期間被傳遞給被聲明，在運(yùn)行期間被傳遞給PL/SQL引擎執(zhí)行。引擎執(zhí)行。命名塊命名塊子程序是被命名的子程序是被命名的 PL/SQL 塊，能接收參數(shù)，能被調(diào)用塊，能接收參數(shù)，能被調(diào)用。子程序存儲在服務(wù)器或應(yīng)用程序?qū)又?。子程序存儲在服?wù)器或應(yīng)用程序?qū)又小Ｋ梢月暶鳛檫^程它可以聲明為過程(procedure)也可以為函數(shù)也可以為

21、函數(shù)(function)，通常僅執(zhí)行一動作時定義為過程，計算值時定義為函數(shù)，通常僅執(zhí)行一動作時定義為過程，計算值時定義為函數(shù)。包。包(package)是由一組過程和函數(shù)組成。觸發(fā)器是由一組過程和函數(shù)組成。觸發(fā)器(trigger)是由是由DML語句以隱含方式調(diào)用的一個塊。語句以隱含方式調(diào)用的一個塊。附注：函數(shù)類似于過程，只是函數(shù)必須返回一個值。附注：函數(shù)類似于過程，只是函數(shù)必須返回一個值。 塊的類型塊的類型數(shù)據(jù)庫開發(fā)技術(shù)FJUT塊的類型塊的類型匿名塊匿名塊 過程過程 函數(shù)函數(shù)數(shù)據(jù)庫開發(fā)技術(shù)FJUT程序結(jié)構(gòu)程序結(jié)構(gòu)數(shù)據(jù)庫開發(fā)技術(shù)FJUTDeveloper/2000組件組件-例如，表單例如，表單PL

22、/SQL塊，與應(yīng)用程序有關(guān)；能塊，與應(yīng)用程序有關(guān)；能自動執(zhí)行。自動執(zhí)行。應(yīng)用程序應(yīng)用程序觸發(fā)器觸發(fā)器Oracle服務(wù)器服務(wù)器PL/SQL塊，與數(shù)據(jù)庫中的表有關(guān)塊，與數(shù)據(jù)庫中的表有關(guān)；由；由DML語句觸發(fā)，自動執(zhí)行。語句觸發(fā)，自動執(zhí)行。數(shù)據(jù)庫數(shù)據(jù)庫觸發(fā)器觸發(fā)器Oracle服務(wù)器和服務(wù)器和Developer/2000組件組件-例如，表單例如，表單有命名的有命名的PL/SQL模塊，是一組相模塊，是一組相關(guān)的過程、函數(shù)和標(biāo)識符。關(guān)的過程、函數(shù)和標(biāo)識符。包包Developer/2000組件組件-例如，表單例如，表單有命名的有命名的PL/SQL塊，存儲在塊，存儲在Developer/2000應(yīng)用程序中或被

23、應(yīng)用程序中或被庫共享；可通過名字被重復(fù)調(diào)用。庫共享；可通過名字被重復(fù)調(diào)用。應(yīng)用程序中的應(yīng)用程序中的過程過程/函數(shù)函數(shù)Oracle服務(wù)器服務(wù)器有命名的有命名的PL/SQL塊；存儲在塊；存儲在Oracle服務(wù)器中；能接受參數(shù)；可服務(wù)器中；能接受參數(shù)；可通過名字被重復(fù)調(diào)用。通過名字被重復(fù)調(diào)用。已存儲的已存儲的過程過程/函數(shù)函數(shù)所有所有PL/SQL環(huán)境環(huán)境未被命名的未被命名的PL/SQL塊；可嵌入到塊；可嵌入到應(yīng)用程序中亦可交互使用。應(yīng)用程序中亦可交互使用。匿名塊匿名塊適用性適用性描述描述程序結(jié)構(gòu)程序結(jié)構(gòu)數(shù)據(jù)庫開發(fā)技術(shù)FJUTTRUE變量類型 P20025-OCT-99Atlanta“Four sco

24、re and seven years agoour fathers brought forth uponthis continent, a new nation,conceived in LIBERTY, and dedicatedto the proposition that all menare created equal.”256120.08數(shù)據(jù)庫開發(fā)技術(shù)FJUTPL/SQl數(shù)據(jù)類型和操作符PL/SQL 支持的內(nèi)置數(shù)據(jù)類型支持的內(nèi)置數(shù)據(jù)類型數(shù)據(jù)類型數(shù)據(jù)類型引用類型%ROWTYPE標(biāo)量類型數(shù)字字符布爾型日期時間LOB類型BFILEBLOBCLOBNCLOB%TYPE提供某個變量或數(shù)據(jù)庫表列

25、的數(shù)據(jù)類型提供表示表中一行的記錄類型 復(fù)合類型數(shù)據(jù)庫開發(fā)技術(shù)FJUTPL/SQl數(shù)據(jù)類型和操作符數(shù)據(jù)類型和操作符指定數(shù)值的存儲格式指定數(shù)值的存儲格式BINARY_INTEGER(-231=1231-1)INTEGERINTSMALLINTPOSTIVE 0231 不能為空NATURAL 1231 不能為空PLS_INTEGERNUMBERDEC(定點數(shù))DECIMAL (定點數(shù))NUMERIC (定點數(shù))FLOAT (浮點數(shù))REAL (浮點數(shù))DOUBLE PRECISION (浮點數(shù))數(shù)據(jù)庫開發(fā)技術(shù)FJUTPL/SQl數(shù)據(jù)類型和操作符數(shù)據(jù)類型和操作符字符數(shù)據(jù)類型包括：字符數(shù)據(jù)類型包括：CH

26、ARVARCHAR2LONGRAWLONG RAWPL/SQL 的數(shù)據(jù)類型與的數(shù)據(jù)類型與 SQL數(shù)據(jù)類型的比較數(shù)據(jù)類型的比較數(shù)據(jù)類型數(shù)據(jù)類型SQL類型類型PL/SQL類型類型CHAR1.20001.32767LONG1.2GB1.32760LONG RAW1.2GB1.32760RAW1.20001.32767VARCHAR21.40001.32767數(shù)據(jù)庫開發(fā)技術(shù)FJUTPL/SQl數(shù)據(jù)類型和操作符數(shù)據(jù)類型和操作符用于引用數(shù)據(jù)庫列的數(shù)據(jù)類型，以及表示表中一行用于引用數(shù)據(jù)庫列的數(shù)據(jù)類型，以及表示表中一行的記錄類型的記錄類型屬性類型有兩種：屬性類型有兩種：%TYPE - 引用變量和數(shù)據(jù)庫列的數(shù)據(jù)

27、類型引用變量和數(shù)據(jù)庫列的數(shù)據(jù)類型%ROWTYPE - 提供表示表中一行的記錄類型提供表示表中一行的記錄類型使用屬性類型的優(yōu)點：使用屬性類型的優(yōu)點：不需要知道被引用的表列的具體類型不需要知道被引用的表列的具體類型如果被引用對象的數(shù)據(jù)類型發(fā)生改變，如果被引用對象的數(shù)據(jù)類型發(fā)生改變，PL/SQL 變量的數(shù)據(jù)類型也隨之改變變量的數(shù)據(jù)類型也隨之改變bh emp.empno%TYPE;emp_rec scott.emp%ROWTYPE;數(shù)據(jù)庫開發(fā)技術(shù)FJUT變量的使用變量的使用 PL/SQL 中可以聲明變量，繼而在中可以聲明變量，繼而在SQL中使用它們，中使用它們，也可在任一過程語句的表達(dá)式中使用。也可在

28、任一過程語句的表達(dá)式中使用。數(shù)據(jù)的臨時存儲數(shù)據(jù)的臨時存儲l在數(shù)據(jù)流過程執(zhí)行之前需輸入合法的數(shù)據(jù)，這些在數(shù)據(jù)流過程執(zhí)行之前需輸入合法的數(shù)據(jù)，這些數(shù)據(jù)將被暫時存放在一個或多個變量中。數(shù)據(jù)將被暫時存放在一個或多個變量中。對其值的操作對其值的操作l無須訪問數(shù)據(jù)庫，通過變量就可進(jìn)行計算和對其無須訪問數(shù)據(jù)庫，通過變量就可進(jìn)行計算和對其他數(shù)據(jù)操作。他數(shù)據(jù)操作。 重復(fù)使用重復(fù)使用l 變量一經(jīng)聲明，就可在應(yīng)用程序中重復(fù)使用。簡變量一經(jīng)聲明，就可在應(yīng)用程序中重復(fù)使用。簡單地通過引用，其他語句（包括其他的聲明語句單地通過引用，其他語句（包括其他的聲明語句）即可重復(fù)使用這些變量。）即可重復(fù)使用這些變量。易于維護(hù)易于維

29、護(hù) 數(shù)據(jù)庫開發(fā)技術(shù)FJUTPL/SQL變量定義變量定義PL/SQL 塊中可以使用變量和常量塊中可以使用變量和常量在聲明部分聲明，使用前必須先聲明在聲明部分聲明，使用前必須先聲明聲明時必須指定數(shù)據(jù)類型，每行聲明一個標(biāo)識符聲明時必須指定數(shù)據(jù)類型，每行聲明一個標(biāo)識符在可執(zhí)行部分的在可執(zhí)行部分的 SQL 語句和過程語句中使用語句和過程語句中使用聲明變量和常量的語法：聲明變量和常量的語法：給變量賦值有兩種方法：給變量賦值有兩種方法：使用賦值語句使用賦值語句 :=使用使用 SELECT INTO 語句語句數(shù)據(jù)庫開發(fā)技術(shù)FJUTPL/SQL中的變量的操作中的變量的操作在聲明部分聲明并初始化變量。在聲明部分聲

30、明并初始化變量。在執(zhí)行部分為變量指派新值。在執(zhí)行部分為變量指派新值。通過參數(shù)把值傳遞給通過參數(shù)把值傳遞給PL/SQL塊。塊。通過輸出變量顯示結(jié)果。通過輸出變量顯示結(jié)果。作用：變量主要用于程序與數(shù)據(jù)庫之間的數(shù)據(jù)作用：變量主要用于程序與數(shù)據(jù)庫之間的數(shù)據(jù)交換以及保存程序的中間結(jié)果。交換以及保存程序的中間結(jié)果。數(shù)據(jù)庫開發(fā)技術(shù)FJUT變量的類型變量的類型PL/SQL變量變量標(biāo)量型標(biāo)量型復(fù)合型復(fù)合型引用型引用型LOB型型 (大型的對象大型的對象)外部變量外部變量綁定變量和主變量綁定變量和主變量主要的標(biāo)量數(shù)據(jù)類型： VARCHAR2 (最大長度) NUMBER (精度, 刻度) DATE CHAR (最大長

31、度) LONG LONG RAW BOOLEAN BINARY_INTEGER PLS_INTEGER數(shù)據(jù)庫開發(fā)技術(shù)FJUT聲明聲明 PL/SQL 變量變量identifier CONSTANT datatype NOT NULL := | DEFAULT expr;Declare v_hiredateDATE; v_deptnoNUMBER(2) NOT NULL := 10; v_locationVARCHAR2(13) := Atlanta; c_ commCONSTANT NUMBER := 1400; 數(shù)據(jù)庫開發(fā)技術(shù)FJUT聲明聲明 PL/SQL變量變量 規(guī)則規(guī)則 遵循命名的慣例。遵

32、循命名的慣例。 可以按習(xí)慣命名可以按習(xí)慣命名例如例如 v_name 代表一個變量和代表一個變量和 c_name代表一個常變量代表一個常變量, g_ 代表代表 global 變量。變量。 初始化初始化NOT NULL型變量。型變量。 用賦值操作符用賦值操作符(:=) 或用或用DEFAULT關(guān)鍵字給變量賦初始值。關(guān)鍵字給變量賦初始值。 每行至多聲明一個標(biāo)識符。每行至多聲明一個標(biāo)識符。 聲明常量時，必須在類型說明符之前標(biāo)明關(guān)鍵字聲明常量時，必須在類型說明符之前標(biāo)明關(guān)鍵字CONSTANT ，并給該常量賦初值。并給該常量賦初值。數(shù)據(jù)庫開發(fā)技術(shù)FJUT命名規(guī)則命名規(guī)則不同塊中的兩個變量可以同名。不同塊中的

33、兩個變量可以同名。變量的名字（標(biāo)識符）不能與塊中表的列同變量的名字（標(biāo)識符）不能與塊中表的列同名。名。DECLARE empnoNUMBER(4);BEGIN SELECT empno INTO empno FROMemp WHERE ename = SMITH;END;數(shù)據(jù)庫開發(fā)技術(shù)FJUT變量的賦值變量的賦值v_ename := Maduro;v_hiredate := 31-DEC-98;identifier := expr;數(shù)據(jù)庫開發(fā)技術(shù)FJUT變量的初始化和關(guān)鍵字變量的初始化和關(guān)鍵字使用使用:= （賦值操作符）（賦值操作符）DEFAULT（缺?。ㄈ笔。㎞OT NULL（非空）（非空

34、）數(shù)據(jù)庫開發(fā)技術(shù)FJUT標(biāo)量類型標(biāo)量類型l僅有一個值l內(nèi)部沒有分量25-OCT-99Atlanta“Four score and seven years ago our fathers brought forth upon this continent, a new nation, conceived in LIBERTY, and dedicated to the proposition that all men are created equal.”TRUE256120.08數(shù)據(jù)庫開發(fā)技術(shù)FJUT基本標(biāo)量類型基本標(biāo)量類型VARCHAR2 (最大長度最大長度)NUMBER (精度精度, 刻度

35、刻度)DATECHAR (最大長度最大長度)LONGLONG RAWBOOLEANBINARY_INTEGERPLS_INTEGER數(shù)據(jù)庫開發(fā)技術(shù)FJUT數(shù)據(jù)類型數(shù)據(jù)類型描述描述VARCHAR2(最大長度最大長度）變長字符的基本類型，最大的存儲數(shù)據(jù)是變長字符的基本類型，最大的存儲數(shù)據(jù)是32767字節(jié)。字節(jié)。 VARCHAR2型的變量和常量沒有默認(rèn)的長度。型的變量和常量沒有默認(rèn)的長度。NUMBER (精度精度, 刻度刻度)整數(shù)和浮點數(shù)的基本類型。整數(shù)和浮點數(shù)的基本類型。DATE日期和時間的基本類型。日期和時間的基本類型。DATE類型來存儲包含從一天的午夜開始的類型來存儲包含從一天的午夜開始的定長

36、的時間。有效的時間范圍是從公元前定長的時間。有效的時間范圍是從公元前4712年年1月月1號開始到號開始到公元公元9999年年12月月31號。號。CHAR (最大長度最大長度)固定長度字符數(shù)據(jù)的基本類型，最大有固定長度字符數(shù)據(jù)的基本類型，最大有32767字節(jié)。如果沒有指定字節(jié)。如果沒有指定的最大值，缺省值為的最大值，缺省值為1。LONG變長字符串的基本類型，最大長度是變長字符串的基本類型，最大長度是32760字節(jié)。字節(jié)。LONG型的數(shù)據(jù)型的數(shù)據(jù)庫列的最大長度是庫列的最大長度是2147483647字節(jié)。字節(jié)。LONG RAW二進(jìn)制數(shù)據(jù)和字節(jié)字符串的基本類型，最大字節(jié)為二進(jìn)制數(shù)據(jù)和字節(jié)字符串的基本類

37、型，最大字節(jié)為32760。PL/SQL不能解釋不能解釋 LONG RAW 型的數(shù)據(jù)。型的數(shù)據(jù)。BOOLEAN存儲邏輯值存儲邏輯值TRUE、FALSE和和 NULL 的基本類型。的基本類型。BINARY_INTEGER介于介于-2147483647和和2147483647之間有符號整數(shù)的基本類型。之間有符號整數(shù)的基本類型。與與NUMBER相比，相比，BINARY_INTEGE占有較少的存儲空間占有較少的存儲空間PLS_INTEGER介于介于-2147483647和和2147483647之間有符號整數(shù)的基本類型。之間有符號整數(shù)的基本類型。與與NUMBER 和和 BINARY_INTEGER相比，相

38、比，PLS_INTEGER占有占有較少的存儲空間且運(yùn)行效率高。較少的存儲空間且運(yùn)行效率高。數(shù)據(jù)庫開發(fā)技術(shù)FJUT標(biāo)量型變量的聲明標(biāo)量型變量的聲明v_jobVARCHAR2(9);v_countBINARY_INTEGER := 0;v_total_salNUMBER(9,2) := 0;v_orderdateDATE := SYSDATE + 7;c_tax_rateCONSTANT NUMBER(3,2) := 8.25;v_validBOOLEAN NOT NULL := TRUE;舉例數(shù)據(jù)庫開發(fā)技術(shù)FJUT變量常量聲明、初始化和賦值變量常量聲明、初始化和賦值DECLARE V_A1 NU

39、MBER; V_A2 NUMBER NOT NULL :=2000; V_A3 CONSTANT NUMBER :=3000; V_A4 CONSTANT NUMBER DEFAULT 4000; V_A5 NUMBER; BEGIN V_A1:=1000; SELECT SAL INTO V_A5 FROM EMP WHERE EMPNO=7876; INSERT INTO T VALUES(V_A1); INSERT INTO T VALUES(V_A2); INSERT INTO T VALUES(V_A3); INSERT INTO T VALUES(V_A4); INSERT INT

40、O T VALUES(V_A5); END;/ 變量不可以與塊中涉及的列同名 變量聲明在DECLARE之后; 變量聲明時如果不賦值，其值為NULL; 變量聲明時如果設(shè)置為NOT NULL， 則一定要在聲明時使用“:=”或者 “DEFAULT”賦初值。 變量在程序體中可以用“:=”或者 “SELECT INTO”賦值 常量聲明時一定要賦值，在程序中只能 引用，不能修改數(shù)據(jù)庫開發(fā)技術(shù)FJUT數(shù)據(jù)庫開發(fā)技術(shù)FJUT數(shù)據(jù)庫開發(fā)技術(shù)FJUT%TYPE 的屬性的屬性聲明的變量對應(yīng)于聲明的變量對應(yīng)于: 數(shù)據(jù)庫的列數(shù)據(jù)庫的列其它先前聲明過的變量其它先前聲明過的變量在在 %TYPE前綴上前綴上:數(shù)據(jù)庫中表和列名

41、數(shù)據(jù)庫中表和列名先前聲明過的變量名先前聲明過的變量名優(yōu)點：優(yōu)點： 利于書寫和維護(hù)代碼利于書寫和維護(hù)代碼, 因為不必要因數(shù)據(jù)庫中列的修改而因為不必要因數(shù)據(jù)庫中列的修改而去修改去修改PL/SQL塊。塊。 能和表中列的數(shù)據(jù)類型保持一致能和表中列的數(shù)據(jù)類型保持一致 可以與先前聲明過的變量保持一致可以與先前聲明過的變量保持一致數(shù)據(jù)庫開發(fā)技術(shù)FJUT用用%TYPE聲明變量聲明變量舉例舉例. v_enameemp.ename%TYPE; v_balanceNUMBER(7,2); v_min_balancev_balance%TYPE := 10;.數(shù)據(jù)庫開發(fā)技術(shù)FJUT聲明布爾型變量聲明布爾型變量布爾型變

42、量僅有三個值布爾型變量僅有三個值TRUE、FALSE和和NULL.布爾型變量由邏輯運(yùn)算符布爾型變量由邏輯運(yùn)算符AND、 OR和和NOT連接連接.布爾型變量輸出三個值布爾型變量輸出三個值 TRUE、 FALSE或或 NULL.算術(shù)、字符和日期表達(dá)式可以返回一個布爾值。算術(shù)、字符和日期表達(dá)式可以返回一個布爾值。數(shù)據(jù)庫開發(fā)技術(shù)FJUT 復(fù)合類型復(fù)合類型類型類型PL/SQL 表表 (tables)PL/SQL 記錄記錄 (records)PL/SQL 可變數(shù)組可變數(shù)組 (varrays)數(shù)據(jù)庫開發(fā)技術(shù)FJUTLOB 型變量型變量數(shù)據(jù)庫開發(fā)技術(shù)FJUT鍵盤輸入鍵盤輸入(替換變量替換變量)鍵盤輸入是通過在

43、變量名前面加一個鍵盤輸入是通過在變量名前面加一個“&”符號實現(xiàn)符號實現(xiàn)的，如：的，如：&V_INPUT。如果此變量是數(shù)值型（如：。如果此變量是數(shù)值型（如：number，integer等），在提示輸入時直接輸入數(shù)等），在提示輸入時直接輸入數(shù)據(jù)；如果此變量是字符型（如：據(jù)；如果此變量是字符型（如：varchar2()等），等），有兩個辦法輸入：有兩個辦法輸入：如果書寫時是：如果書寫時是：&V_INPUT，那么輸入時要加單引，那么輸入時要加單引號號“”把字符串引起來；把字符串引起來；如果書寫時是：如果書寫時是：&V_INPUT，那么輸入時不要加，那么輸入時不要加單引號

44、，直接輸入字符串。單引號，直接輸入字符串。數(shù)據(jù)庫開發(fā)技術(shù)FJUT外部變量外部變量(綁定變量綁定變量)Server外部變量是在某一程序的主環(huán)境下聲明的一種變量，用來外部變量是在某一程序的主環(huán)境下聲明的一種變量，用來把運(yùn)行時的值（可以是數(shù)字、字符）傳入或傳出一個或把運(yùn)行時的值（可以是數(shù)字、字符）傳入或傳出一個或更多更多PL/SQL程序，在這個程序中可以像其他變量一樣程序，在這個程序中可以像其他變量一樣使用外部變量。在主環(huán)境或一個調(diào)用環(huán)境（可以是由過使用外部變量。在主環(huán)境或一個調(diào)用環(huán)境（可以是由過程、函數(shù)或包的程、函數(shù)或包的PL/SQL語句調(diào)用的）下，聲明外部變語句調(diào)用的）下，聲明外部變量，之后就可

45、引用這些外部變量了。量，之后就可引用這些外部變量了。數(shù)據(jù)庫開發(fā)技術(shù)FJUT外部變量外部變量(綁定變量綁定變量) 外部變量是在程序運(yùn)行的主環(huán)境中定義的變量外部變量是在程序運(yùn)行的主環(huán)境中定義的變量 在在PL/SQL中引用外部變量需要在外部變量前加中引用外部變量需要在外部變量前加”:” 在在PL/SQL中可以象使用自己的變量一樣使用外部變量。中可以象使用自己的變量一樣使用外部變量。 外部變量使得外部變量使得PL/SQL可以與主機(jī)環(huán)境之間傳遞數(shù)據(jù)可以與主機(jī)環(huán)境之間傳遞數(shù)據(jù) 應(yīng)盡量減少使用外部變量，因為他會影響性能。為了盡量減應(yīng)盡量減少使用外部變量，因為他會影響性能。為了盡量減少對主機(jī)的呼叫，可以將變量

46、的值賦給局部變量。少對主機(jī)的呼叫，可以將變量的值賦給局部變量。數(shù)據(jù)庫開發(fā)技術(shù)FJUT引用外部變量引用外部變量SQL VARIABLE emp_num NUMBER -定義定義SQL*PLUS中的變量中的變量SQL BEGIN 2 SELECT COUNT(*) INTO :emp_num 3 FROM emp; 4 END; 5 /PL/SQL 過程已成功完成。過程已成功完成。SQL PRINT emp_num -在在SQL*PLUS中顯示變量中顯示變量 EMP_NUM- 13在SQL*PLUS環(huán)境中：數(shù)據(jù)庫開發(fā)技術(shù)FJUT嵌套塊和變量的作用域嵌套塊和變量的作用域只要可執(zhí)行的語句能運(yùn)行，其所在

47、的塊就能只要可執(zhí)行的語句能運(yùn)行，其所在的塊就能被嵌套。被嵌套。一個嵌套塊可看作是一個語句。一個嵌套塊可看作是一個語句。異常處理部分可以包含若干嵌套塊。異常處理部分可以包含若干嵌套塊。一個對象的作用域是指所涉及到該對象的程一個對象的作用域是指所涉及到該對象的程序的區(qū)域。序的區(qū)域。數(shù)據(jù)庫開發(fā)技術(shù)FJUT嵌套塊和變量的作用域嵌套塊和變量的作用域一個標(biāo)識符只有在它的作用域中用一個不限定一個標(biāo)識符只有在它的作用域中用一個不限定的名字來引用時才是可見的的名字來引用時才是可見的:塊可以由內(nèi)向外查找標(biāo)識符。塊可以由內(nèi)向外查找標(biāo)識符。 (如在嵌套塊如在嵌套塊的父塊中的聲明部分進(jìn)行查找的父塊中的聲明部分進(jìn)行查找)

48、塊不可以由外向內(nèi)查找標(biāo)識符。塊不可以由外向內(nèi)查找標(biāo)識符。(如不會向如不會向內(nèi)在其子塊或其兄弟塊中查找）內(nèi)在其子塊或其兄弟塊中查找）附注附注: 可以使用塊標(biāo)簽作為前綴限定一個標(biāo)識符。數(shù)據(jù)庫開發(fā)技術(shù)FJUT嵌套塊和變量的作用域嵌套塊和變量的作用域. x BINARY_INTEGER;BEGIN . DECLARE y NUMBER; BEGIN . END; .END; x的范圍的范圍Y的范圍的范圍舉例舉例數(shù)據(jù)庫開發(fā)技術(shù)FJUT決定變量的作用域決定變量的作用域本課習(xí)題本課習(xí)題.DECLARE V_SALNUMBER(7,2) := 60000; V_COMMNUMBER(7,2) := V_SAL

49、 * .20; V_MESSAGEVARCHAR2(255) := eligible for commission;BEGIN . DECLARE V_SALNUMBER(7,2) := 50000; V_COMM NUMBER(7,2) := 0; V_TOTAL_COMPNUMBER(7,2) := V_SAL + V_COMM; BEGIN . V_MESSAGE := CLERK not|V_MESSAGE; END; V_MESSAGE := SALESMAN|V_MESSAGE;END;習(xí)題1. 子塊中的 V_MESSAGE.2. 主塊中的 V_TOTAL_COMP.3. 子塊中的 V_COMM .4. 主塊中的 V_COMM.5. 主塊中的V_MESSAGE .數(shù)據(jù)庫開發(fā)技術(shù)FJUTPL/SQL中的運(yùn)算符中的運(yùn)算符邏輯運(yùn)算符邏輯運(yùn)算符算術(shù)運(yùn)算符算術(shù)運(yùn)算符連接運(yùn)算符連接運(yùn)算符 控制運(yùn)算符次序的括號控制運(yùn)算符次序的括號求冪運(yùn)算符求冪運(yùn)算符 (*)與與SQL中同中同數(shù)據(jù)庫開發(fā)技術(shù)