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

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