Oracle數(shù)據(jù)庫培訓(xùn)-PLSQL編程

PL/SQL編程 本章學(xué)習(xí)目標(biāo) 本章將講述 PL/SQL基礎(chǔ)語法，結(jié)構(gòu)和組件、以及如何設(shè)計(jì)并執(zhí)行一個(gè) PL/SQL程序。 第二章 PL/SQL編程 本章內(nèi)容安排 2.1 PL/SQL簡(jiǎn)介 2.2 PL/SQL語法 2.3 存儲(chǔ)過程 2.4 觸發(fā)器 2.5 游標(biāo) 2.6 包 2.7 函數(shù) 2.1 PL/SQL簡(jiǎn)介 2.1.1 PL/SQL特點(diǎn) 2.1.2 開發(fā)及運(yùn)行環(huán)境 1. PL/SQL的優(yōu)點(diǎn) （ 1） PL/SQL是一種高性能的基于事務(wù)處理的語言，能運(yùn)行在任何 Oracle環(huán)境中，支持所有數(shù)據(jù)處理命令。通過使用 PL/SQL程序單元處理 SQL的數(shù)據(jù)定義和數(shù)據(jù)控制元素。 （ 2） PL/SQL支持所有 SQL數(shù)據(jù)類型和所有 SQL函數(shù)，同時(shí)支持所有Oracle對(duì)象類型。 （ 3） PL/SQL塊可以被命名和存儲(chǔ)在 Oracle服務(wù)器中，同時(shí)也能被其他的PL/SQL程序或 SQL命令調(diào)用，任何客戶 /服務(wù)器工具都能訪問 PL/SQL程序，具有很好的可重用性。 （ 2）可以使用 Oracle數(shù)據(jù)工具管理存儲(chǔ)在服務(wù)器中的 PL/SQL程序的安全性?？梢允跈?quán)或撤銷數(shù)據(jù)庫其他用戶訪問 PL/SQL程序的能力。 （ 5） PL/SQL代碼可以使用任何 ASCII文本編輯器編寫，所以對(duì)任何Oracle能夠運(yùn)行的操作系統(tǒng)都是非常便利的。 2.1.1 PL/SQL特點(diǎn) 2. PL/SQL的缺點(diǎn) 對(duì)于 SQL， Oracle必須在同一時(shí)間處理每一條 SQL語句，在網(wǎng)絡(luò)環(huán)境下這就意味作每一個(gè)獨(dú)立的調(diào)用都必須被 oracle服務(wù)器處理，這就占用大量的服務(wù)器時(shí)間，同時(shí)導(dǎo)致網(wǎng)絡(luò)擁擠。而 PL/SQL是以整個(gè)語句塊發(fā)給服務(wù)器，這就降低了網(wǎng)絡(luò)擁擠。 服務(wù)器端 PL/SQL 不需要顯式的安裝或許可。它是 Oracle9i 數(shù)據(jù)庫的隱式部分，此處有相應(yīng)的文檔。 PL/SQL 編譯器和解釋器也嵌入到 Oracle Developer 中，為開發(fā)者在客戶端和服務(wù)器端提供一致的、可利用的開發(fā)模型。 2.1.2 開發(fā)及運(yùn)行環(huán)境 2.2.2 常量與變量 2.2.3 數(shù)據(jù)類型 2.2.4 運(yùn)算符 2.2.1 PL/SQL塊 2.2.5 流程控制 2.2.6 異常處理 2.2 PL/SQL語法 1塊結(jié)構(gòu) 2、塊的命名和匿名 3、塊的執(zhí)行 2.2.1 PL/SQL塊 1. 塊結(jié)構(gòu) PL/SQL是一種塊結(jié)構(gòu)的語言 ， 組成 PL/SQL程序的單元是邏輯塊 ，一個(gè) PL/SQL 程序包含了一個(gè)或多個(gè)邏輯塊 ， 每個(gè)塊都可以劃分為三個(gè)部分 。 （ 1） 塊的三個(gè)部分 聲明部分 （ Declaration section） 聲明部分包含了變量和常量的數(shù)據(jù)類型和初始值 。 這個(gè)部分是由關(guān)鍵字DECLARE開始 ， 如果不需要聲明變量或常量 ， 那么可以忽略這一部分 。 執(zhí)行部分 （ Executable section） 執(zhí)行部分是 PL/SQL塊中的指令部分 ， 由關(guān)鍵字 BEGIN開始 ， 所有的可執(zhí)行語句都放在這一部分 ， 其他的 PL/SQL塊也可以放在這一部分 。 異常處理部分 （ Exception section） 這一部分是可選的 ， 在這一部分中處理異?；蝈e(cuò)誤 ， 對(duì)異常處理的詳細(xì)討論在后面進(jìn)行 。 （ 2） PL/SQL塊語法 PL/SQL塊語法結(jié)構(gòu)如下： DECLARE declaration statements BEGIN executable statements EXCEPTION exception statements END 例： declare in_id number(2)；in_name varchar2(20)； begin in_id:=1；in_name:=James； update test set t_name=in_name where t_id=in_id； end； / PL/SQL塊中的每一條語句都必須以分號(hào)結(jié)束 ， SQL語句可以多行 ， 但分號(hào)表示該語句的結(jié)束 。 一行中可以有多條 SQL語句 ， 他們之間以分號(hào)分隔 。每一個(gè) PL/SQL塊由 BEGIN或 DECLARE開始 ， 以 END結(jié)束 。 注釋由 -標(biāo)示 。 2 塊的命名和匿名 PL/SQL程序塊可以是一個(gè)命名的程序塊也可以是一個(gè)匿名程序塊 ，匿名程序塊可以用在服務(wù)器端也可以用在客戶端 。 執(zhí)行部分包含了所有的語句和表達(dá)式 ， 執(zhí)行部分以關(guān)鍵字 BEGIN開始 ， 以關(guān)鍵字 EXCEPTION結(jié)束 ， 如果 EXCEPTION不存在 ， 那么將以關(guān)鍵字 END結(jié)束 。 分號(hào)分隔每一條語句 ， 使用賦值操作符 :=或 SELECT INTO或 FETCH INTO給每個(gè)變量賦值 ， 執(zhí)行部分的錯(cuò)誤將在異常處理部分解決 ， 在執(zhí)行部分中可以使用另一個(gè) PL/SQL程序塊 ， 這種程序塊被稱為嵌套塊 。 所有的 SQL數(shù)據(jù)操作語句都可以用于執(zhí)行部分 ， PL/SQL塊不能在屏幕上顯示 SELECT語句的輸出 。 SELECT語句必須包括一個(gè) INTO子串或者是游標(biāo)的一部分 ， 執(zhí)行部分使用的變量和常量必須首先在聲明部分聲明 ， 執(zhí)行部分必須至少包括一條可執(zhí)行語句 ， NULL是一條合法的可執(zhí)行語句 ， 事物控制語句 COMMIT和 ROLLBACK可以在執(zhí)行部分使用 ，數(shù)據(jù)定義語言 (Data Definition language)不能在執(zhí)行部分中使用 ， DDL語句與 EXECUTE IMMEDIATE一起使用或者是 DBMS_SQL調(diào)用 。 3 塊的執(zhí)行 SQL*PLUS中匿名的 PL/SQL塊的執(zhí)行是在 PL/SQL塊后輸入 /來執(zhí)行 。 命名的程序與匿名程序的執(zhí)行不同 ， 執(zhí)行命名的程序塊必須使用 execute關(guān)鍵字 :。 如果在另一個(gè)命名程序塊或匿名程序塊中執(zhí)行這個(gè)程序 ， 那么就不需要 EXECUTE關(guān)鍵字 。 1變量 2、常量 3、有效字符集 2.2.2 常量與變量 1、 變量 （ 1） 聲明變量 聲明變量的語句格式如下： Variable_name CONSTANT databyte NOT NULL:=|DEFAULT expression 注意： 可以在聲明變量的同時(shí)給變量強(qiáng)制性的加上 NOT NULL約束條件 ，此時(shí)變量在初始化時(shí)必須賦值 。 （ 2） 給變量賦值 給變量賦值有兩種方式： 直接給變量賦值 通過 SQL SELECT INTO 或 FETCH INTO給變量賦值 2、 常量 常量與變量相似 ， 但常量的值在程序內(nèi)部不能改變 ， 常量的值在定義時(shí)賦予 ， 聲明方式與變量相似 ， 但必須包括關(guān)鍵字 CONSTANT。 常量和變量都可被定義為 SQL和用戶定義的數(shù)據(jù)類型 。 為了減少這部分程序的修改 ， 編程時(shí)使用 %TYPE、%ROWTYPE方式聲明變量 ， 使變量聲明的類型與表中的保持同步 ， 隨表的變化而變化 ， 這樣的程序在一定程度上具有更強(qiáng)的通用性 。 %TYPE對(duì)應(yīng)列 ， %ROWTYPE對(duì)應(yīng)行 。 3、 有效字符集 （ 1） 所有的大寫和小寫英文字母； （ 2） 數(shù)字 0-9； （ 3） 符號(hào)： 0一 * ！ 一； :. ， “ “ ？ PL/SQL標(biāo)識(shí)符的最大長(zhǎng)度是 30個(gè)字符 ， 并且不區(qū)分字母的大小寫 。 但是適當(dāng)?shù)厥褂么笮?， 可以提高程序的可讀性 。 2.2.3 數(shù)據(jù)類型 例如：定義如下若干類型變量，常量。 DECLARE ORDER_NO NUMBER(3)； CUST_NAME VARCHAR2(20)； ORDER_DATE DATE； EMP_NO INTEGER:=25； PI CONSTANT NUMBER:=3.1216； BEGIN NULL； END； 2.2.4 運(yùn)算符 與其他程序設(shè)計(jì)語言相同， PL/SQL有一系列操作符。主要有： 算術(shù)操作符 + - * / * | 關(guān)系操作符 = ，方式 l數(shù)據(jù)類型 1， 參數(shù) 2， 方式 2數(shù)據(jù)類型 2， ) IS|AS is或 as完全等價(jià) BEGIN PL/SQL過程體 END過程名 2.3.1 存儲(chǔ)過程的創(chuàng)建 過程參數(shù)有以下三種類型： （ 1） in參數(shù)類型：表示輸入給過程的參數(shù)。 （ 2） out參數(shù)類型：表示參數(shù)在過程中將被賦值，可以傳給過程體的外部。 （ 3） in out參數(shù)類型：表示該類參數(shù)既可以向過程體傳值，也可以在過程體中賦值，以便向過程體外傳值。 存儲(chǔ)過程可以直接用 EXECUT命令調(diào)用或 PL/SQL程序塊內(nèi)部調(diào)用。 用 EXECUT命令調(diào)用存儲(chǔ)過程的格式如下： SQLexecute proc_name(par1,par2)； 2.3.2 存儲(chǔ)過程的調(diào)用 存儲(chǔ)過程也可以被另外的 PL/SQL塊調(diào)用 ， 調(diào)用的語句是： declare par1,par2； begin proc_name(par1,par2)； end； 在調(diào)用前要聲明變量 par1， par2 當(dāng)某個(gè)存儲(chǔ)過程不再需要時(shí)，應(yīng)將其從內(nèi)存中刪除，以釋放它占用的內(nèi)存資源。釋放過程的語句格式如下： SQLdrop procedure proc_name； proc_name為過程名。 2.3.3 存儲(chǔ)過程的釋放 編寫存儲(chǔ)過程，顯示所指定雇員名所在的部門名和位置。 CREATE OR REPLACE PROCEDURE DeptMesg(pename emp.ename%TYPE, pdname OUT dept.dname%TYPE,ploc OUT dept.loc%TYPE) AS BEGIN SELECT dname,loc INTO pdname,ploc FROM emp,dept WHERE emp.deptno=dept.deptno AND emp.ename=pename； END； / VARIABLE vdname VARCHAR2(12)； VARIABLE vloc VARCHAR2(13)； EXECUTE DeptMesg(SMITH,:vdname， :vloc)； PRINT vdname vloc； 2.3.4 實(shí)例 2.2.2 觸發(fā)器的類型 2.2.3 觸發(fā)器的創(chuàng)建 2.2.4 觸發(fā)器的修改和刪除 2.2.1 觸發(fā)器的設(shè)計(jì)規(guī)則 2.2 觸發(fā)器 2.2.5 實(shí)例 （ 1）作用范圍清晰 （ 2）不要讓觸發(fā)器去完成 Oracle后臺(tái)已經(jīng)能夠完成的功能 （ 3）限制觸發(fā)器代碼的行數(shù) （ 2）不要?jiǎng)?chuàng)建遞歸的觸發(fā)器 （ 5）觸發(fā)器僅在被觸發(fā)語句觸發(fā)時(shí)進(jìn)行集中的，全局的操作，同用戶和數(shù)據(jù)庫應(yīng)用無關(guān)。 2.2.1 觸發(fā)器的設(shè)計(jì)規(guī)則 可以創(chuàng)建被如下語句所觸發(fā)的觸發(fā)器： （ 1） DML語句（ DELETE， INSERT， UPDATE）； （ 2） DDL語句（ CREATE， ALTER， DROP）； （ 3）數(shù)據(jù)庫操作（ SERVERERROR， LOGON， LOGOFF，STARTUP， SHUTDOWN）。 2.2.2 觸發(fā)器的類型 1使用 CREATE TRIGGER語句創(chuàng)建觸發(fā)器 使用 CREATE TRIGGER語句創(chuàng)建觸發(fā)器的語句格式如下： CREATEOR REPLACE TRIGGER name BEFORE|AFTER event OR . ON table FOREACH ROW|STATEMENT WHEN(condition) plsql block|call procedures_statement 2.2.3 觸發(fā)器的創(chuàng)建 2 注意事項(xiàng) （ 1） 觸發(fā)器可以聲明為在對(duì)記錄進(jìn)行操作之前 ， 在之前 (檢查約束之前和 INSERT， UPDATE 或 DELETE 執(zhí)行前 )或之后 (在檢查約束之后和完成 INSERT， UPDATE 或 DELETE 操作 )觸發(fā) .。 （ 2） 一個(gè) FOR EACH ROW 執(zhí)行指定操作的觸發(fā)器為操作修改的每一行都調(diào)用一次 。 （ 3） SELECT 并不更改任何行 ， 因此不能創(chuàng)建 SELECT 觸發(fā)器 .這種場(chǎng)合下規(guī)則和視圖更適合 。 （ 2） 觸發(fā)器和某一指定的表格有關(guān) ， 當(dāng)該表格被刪除時(shí) ， 任何與該表有關(guān)的觸發(fā)器同樣會(huì)被刪除 。 （ 5） 在一個(gè)表上的每一個(gè)動(dòng)作只能有一個(gè)觸發(fā)器與之關(guān)聯(lián) 。 （ 6） 在一個(gè)單獨(dú)的表上 ， 最多只能創(chuàng)建三個(gè)觸發(fā)器與之關(guān)聯(lián) ， 一個(gè) INSERT觸發(fā)器 ， 一個(gè) DELETE觸發(fā)器和一個(gè) UPDATE觸發(fā)器 。 刪除觸發(fā)器的語句格式為： DROP TRIGGER name ON table； 一個(gè)觸發(fā)器由三部分組成 :觸發(fā)事件或語句、觸發(fā)限制和觸發(fā)器動(dòng)作。觸發(fā)事件或語句是指引起激發(fā)觸發(fā)器的 SQL語句，可為對(duì)一指定表的 INSERT、 UNPDATE或 DELETE語句。觸發(fā)限制是指定一個(gè)布爾表達(dá)式，當(dāng)觸發(fā)器激發(fā)時(shí)該布爾表達(dá)式是必須為真。觸發(fā)器作為過程，是 PL/SQL塊，當(dāng)觸發(fā)語句發(fā)出、觸發(fā)限制計(jì)算為真時(shí)該過程被執(zhí)行。 2.2.4 觸發(fā)器的修改和刪除 編寫一個(gè)數(shù)據(jù)庫觸發(fā)器，當(dāng)任何時(shí)候某個(gè)部門從 dept表中刪除時(shí)，該觸發(fā)器將從 emp表中刪除該部門的所有雇員。 CREATE OR REPLACE TRIGGER del_emp_deptno BEFORE DELETE ON dept FOR EACH ROW BEGIN DELETE FROM emp WHERE deptno=:OLD.deptno； END； 2.2.5 實(shí)例 2.5.2 隱式游標(biāo) 2.5.3 實(shí)例 2.5.1 顯式游標(biāo) 2.5 游標(biāo) Oracle游標(biāo)是一種用于輕松的處理多行數(shù)據(jù)的機(jī)制 ，沒有游標(biāo) ， Oracle開發(fā)人員必須單獨(dú)地 、 顯式地取回并管理游標(biāo)查詢選擇的每一條記錄 。 游標(biāo)的另一項(xiàng)功能是 ， 它包含一個(gè)跟蹤當(dāng)前訪問的記錄的指針 ， 這使程序能夠一次處理多條記錄 。 1聲明游標(biāo) 2、打開游標(biāo) 3、從游標(biāo)中取回?cái)?shù)據(jù) 2.5.1 顯式游標(biāo) 2、關(guān)閉游標(biāo) 1.