PLSQL--清華大學(xué)計(jì)算中心ORACLE培訓(xùn)資料.pptVIP

Oracle9i PL/SQL程序設(shè)計(jì),Procedural Language,PL/SQL程序設(shè)計(jì),2,PL/SQL 概述,PL/SQL是Oracle對(duì)關(guān)系數(shù)據(jù)庫(kù)語(yǔ)言SQL的過(guò)程化擴(kuò)充,它將數(shù)據(jù)庫(kù)技術(shù)和過(guò)程化程序設(shè)計(jì)語(yǔ)言聯(lián)系起來(lái),是一種應(yīng)用開(kāi)發(fā)語(yǔ)言,可使用循環(huán),分支處理數(shù)據(jù),將SQL的數(shù)據(jù)操縱功能與過(guò)程化語(yǔ)言數(shù)據(jù)處理功能結(jié)合起來(lái). PL/SQL的使用,使SQL成為一種高級(jí)程序設(shè)計(jì)語(yǔ)言,支持高級(jí)語(yǔ)言的塊操作,條件判斷,循環(huán)語(yǔ)句,嵌套等,與數(shù)據(jù)庫(kù)核心的數(shù)據(jù)類(lèi)型集成,使SQL 的程序設(shè)計(jì)效率更高. PL/SQL是一種塊結(jié)構(gòu)語(yǔ)言,構(gòu)成PL/SQL的基本單位是邏輯塊,塊中可以嵌套子塊,以塊為單位向Oracle Server傳遞數(shù)據(jù)執(zhí)行,其塊結(jié)構(gòu)如下: DECLARE BEGIN EXCEPTION END;,說(shuō)明部分:說(shuō)明變量及常量,執(zhí)行部分:使用SQL的DML語(yǔ)言事務(wù)處理,例外處理:處理錯(cuò)誤及警告信息,邏 輯 塊 結(jié) 束,PL/SQL程序設(shè)計(jì),3,PL/SQL語(yǔ)言的特點(diǎn),PL/SQL中可以定義變量,變量有其作用范圍 PL/SQL是以塊的方式設(shè)計(jì),塊中可以嵌套子塊,子塊可以位于塊中任何部分 子塊可以是PL/SQL中的一條命令 PL/SQL中可以使用條件判斷,循環(huán),游標(biāo)等高級(jí)語(yǔ)言程序設(shè)計(jì)方法 模塊式的過(guò)程化語(yǔ)言,以塊為單位執(zhí)行 使用PL/SQL可以?xún)?yōu)化程序設(shè)計(jì),得到更好的性能 (例如,執(zhí)行 10個(gè)SQL語(yǔ)句,需要訪問(wèn)10次 Oracle核心,如果10個(gè)SQL組成一個(gè)PL/SQL程序,則只需訪問(wèn)一次Oracle 核心,將結(jié)果一次返回給用戶(hù),則程序執(zhí)行效率高,節(jié)省時(shí)間,降低通信量),PL/SQL程序設(shè)計(jì),4,減少對(duì)于Oracle核心的訪問(wèn),降低網(wǎng)絡(luò)負(fù)責(zé) 數(shù)據(jù)庫(kù)數(shù)據(jù)類(lèi)型集成 PL/SQL支持全部的SQL數(shù)據(jù)類(lèi)型,這些共享的數(shù)據(jù)類(lèi)型與SQL所提供的直接存取相結(jié)合,使PL/SQL與Oracle數(shù) 據(jù)字典結(jié)合成一體. PL/SQL與Oracle RDBMS集成在一起,可以使用PL/SQL 的集成數(shù)據(jù)類(lèi)型,使變量的數(shù)據(jù)類(lèi)型在數(shù)據(jù)庫(kù)的操作中實(shí) 時(shí)確定.,PL/SQL語(yǔ)言的特點(diǎn),PL/SQL程序設(shè)計(jì),5,PL/SQL與SQL語(yǔ)言結(jié)合,PL/SQL語(yǔ)句含有SQL的DML語(yǔ)句,檢索及事務(wù)處理 PL/SQL中可以使用的SQL語(yǔ)句如下: INSERT UPDATE DELETE SELECT INTO COMMIT ROLLBACK SAVEPOINT 支持 RDBMS 的所有數(shù)據(jù)類(lèi)型 對(duì)于SQL的數(shù)據(jù)類(lèi)型進(jìn)行了擴(kuò)充,增加了新的數(shù)據(jù)類(lèi)型,使 PL/SQL所支持的數(shù)據(jù)類(lèi)型更靈活. Oracle RDBMS 對(duì)于PL/SQL中引用的表名,列名進(jìn)行語(yǔ)句檢查 對(duì)于SQL語(yǔ)句的執(zhí)行結(jié)果,PL/SQL提供返回信息檢查,通過(guò) PL/SQL的例外處理,記錄SQL的執(zhí)行結(jié)果.,PL/SQL程序設(shè)計(jì),6,PL/SQL基礎(chǔ),變量的使用 和其他高級(jí)語(yǔ)言一樣,在PL/SQL中可以使用變 量 注釋 單行注釋: - 多行注釋: /* */ 數(shù)據(jù)類(lèi)型 日期型 Date 字符型 CHAR,VARCHAR2 數(shù)字型 NUMBER 布爾型 BOOLEAN(TRUE,FALSE,NULL),數(shù)據(jù)類(lèi)型,PL/SQL數(shù)據(jù)類(lèi)型擴(kuò)展,數(shù)字型 NUMBER 存儲(chǔ)整型及實(shí)型數(shù)據(jù)，可以定義精度和取值范圍 BINARY_INTEGER 可存儲(chǔ)帶符號(hào)整數(shù)，為整數(shù)計(jì)算優(yōu)化性能 DEC NUMBER的子類(lèi)型，存儲(chǔ)實(shí)型數(shù)據(jù) DOUBLE PRECISION NUMBER的子類(lèi)型，高精度實(shí)數(shù) INTEGER NUMBER的子類(lèi)型，整數(shù) INT NUMBER的子類(lèi)型，整數(shù),PL/SQL數(shù)據(jù)類(lèi)型擴(kuò)展,數(shù)字型 SMALLINT NUMBER的子類(lèi)型，取值范圍比INTEGER小 NUMERIC NUMBER的子類(lèi)型，與NUMBER等價(jià) REAL NUMBER的子類(lèi)型，存儲(chǔ)實(shí)型數(shù)據(jù),PL/SQL數(shù)據(jù)類(lèi)型擴(kuò)展,字符型 VARCHAR2 存放可變長(zhǎng)字符串，有最大長(zhǎng)度限制 CHAR 字符型，固定長(zhǎng)度 LONG 長(zhǎng)字符型 CLOB 大對(duì)象字符(Oracle8、Oracle8i) 日期型 Date 存儲(chǔ)日期數(shù)據(jù),PL/SQL數(shù)據(jù)類(lèi)型擴(kuò)展,布爾型 BOOLEAN 結(jié)果是TRUE或FALSE 二進(jìn)制數(shù)據(jù) RAW 存儲(chǔ)多媒體數(shù)據(jù)，如圖象、聲音、視頻等 BLOB 大對(duì)象如圖象、聲音、視頻等（Oracle8、Oracle8i）,PL/SQL程序設(shè)計(jì),11,PL/SQL基礎(chǔ),數(shù)據(jù)定義 標(biāo)示符 數(shù)據(jù)類(lèi)型 ; 標(biāo)示符 數(shù)據(jù)類(lèi)型 := PL/SQL表達(dá)式; 例如, v_salary number(7,2); v_ename varchar2(20) :=SMITH;,PL/SQL程序設(shè)計(jì),12,PL/SQL基礎(chǔ),變量賦值 變量名 := 值; v_salary := 890.76; v_date :=25-MAR-99; Available_now :=FALSE; %TYPE類(lèi)型 Credit Real(7,2); Debit Credit%TYPE; My_name Employee.ename%TYPE; %TYPE 屬性 + 不必知道My_name的真正數(shù)據(jù)類(lèi)型 + 當(dāng)數(shù)據(jù)庫(kù)中列ename列定義改變時(shí)，數(shù) 據(jù)庫(kù)運(yùn)行時(shí)自動(dòng)修改 %ROWTYPE: 行類(lèi)型,PL/SQL程序設(shè)計(jì),13,PL/SQL基礎(chǔ),PL/SQL 的記錄類(lèi)型 把邏輯相關(guān)的數(shù)據(jù)作為一個(gè)單元存儲(chǔ)起來(lái),在 Declare 段中定義record類(lèi)型數(shù)據(jù)，使某一 變量使用該record型數(shù)據(jù) 定義方法：TYPE r_record is RECORD ( v_name emp.ename%TYPE, v_job emp.job%TYPE, v_sal emp.sal%TYPE); 變量定義 r_emp r_record; 變量使用 SELECT ename,job,sal INTO r_emp FROM emp WHERE empno=7934; 則，r_emp.v_ename,r_emp.v_job,r_emp.v_sal 已有 值； 給變量賦值： r_employee r_record; r_employee.v_ename :=JACK; r_employee.v_job :=CLERK; r_employee.v_sal := 890.98;,PL/SQL程序設(shè)計(jì),PL/SQL程序設(shè)計(jì),14,PL/SQL基礎(chǔ),TABLE類(lèi)型數(shù)據(jù) PL/SQL中的表(table)類(lèi)型類(lèi)似于語(yǔ)言中的 結(jié)構(gòu)類(lèi)型數(shù)組 定義方法：TYPE table_emp IS TABLE OF emp.ename %TYPE INDEX BY BINARY_INTEGER; 一個(gè)PL/SQL表有兩個(gè)列，(key,value),key列類(lèi)型即是 BINARY_INTEGER,而value類(lèi)型則是所定義的數(shù)據(jù)類(lèi)型 Table類(lèi)型使用： 定義變量 my_name為 table_emp 類(lèi)型,則可以使用變量 my_name ,也可以在SQL語(yǔ)句中使用 table類(lèi)型變量 my_name table_emp; my_name(0) :=SCOTT; my_name(1) :=SMITH; my_name(2) :=SUSAN; SELECT ename INTO my_name(10) FROM emp WHERE empno = 7934;,PL/SQL程序設(shè)計(jì),15,在PL/SQL中使用SQL,INSERT語(yǔ)句的使用 Declare v_empno EMP.empno%TYPE :=1234; v_ename EMP.ename%TYPE :=SCOTT; v_job VARCHAR2(15) :=MANAGER; v_deptno EMP.deptno%TYPE :=20; v_sal NUMBER(7,2) :=890.50; Begin INSERT INTO emp(empno,ename,job,hiredate,sal,deptno) VALUES(v_empno,v_ename,v_job,SYSDATE,v_sal,v_deptno); END; /,注意:非空(NOT NULL) 必須有值,NOT NULL,PL/SQL程序設(shè)計(jì),16,PL/SQL中使用SQL,DELETE 語(yǔ)句的使用 Declare v_empno EMP.empno%TYPE :=1234; Begin DELETE FROM emp WHERE empno=v_empno; End; 事務(wù)處理語(yǔ)句的使用 在PL/SQL中可以使用SQL的 COMMIT,ROLLBACK及 SAVEPOINT語(yǔ)句 Declare v_empno EMP.empno%TYPE :=1234; Begin DELETE FROM emp WHERE empno=v_empno; COMMIT; End; /,PL/SQL程序設(shè)計(jì),條件控制語(yǔ)句,IF-THEN語(yǔ)句 IF 條件成立 THEN 可執(zhí)行語(yǔ)句; END IF;,條件控制語(yǔ)句,IF-THEN-ELSE語(yǔ)句 IF 條件成立 THEN 執(zhí)行語(yǔ)句1; ELSE 執(zhí)行語(yǔ)句2； END IF;,條件控制語(yǔ)句,IF-THEN-ELSIF語(yǔ)句 IF 條件1成立 THEN 執(zhí)行語(yǔ)句1; ELSIF 條件2成立 THEN 執(zhí)行語(yǔ)句2； ELSIF 條件3成立 THEN 執(zhí)行語(yǔ)句3; . END IF;,循環(huán)語(yǔ)句,基本循環(huán) LOOP 執(zhí)行語(yǔ)句； EXIT WHEN 條件成立； END LOOP;,循環(huán)語(yǔ)句,WHILE循環(huán) WHILE 條件成立 LOOP 執(zhí)行語(yǔ)句； END LOOP;,循環(huán)語(yǔ)句,FOR循環(huán) FOR 計(jì)數(shù)器 IN 低界高界 LOOP 執(zhí)行語(yǔ)句； END LOOP;,PL/SQL程序設(shè)計(jì),23,光標(biāo)(CURSOR)設(shè)計(jì),什么是光標(biāo) 光標(biāo)使用 光標(biāo)說(shuō)明 ：在Declare 段中命名光標(biāo)，將光標(biāo)與一查詢(xún)語(yǔ)句聯(lián)系起來(lái). 語(yǔ)法： CURSOR 光標(biāo)名(參數(shù)) IS SELECT 字句； 打開(kāi)光標(biāo) 獲取活動(dòng)集中的行 FETCH語(yǔ)句檢索活動(dòng)集中的行，每次一行，每執(zhí)行一次FECTCH，光標(biāo)前進(jìn)到活動(dòng)集中的下一行。 光標(biāo)下移 關(guān)閉光標(biāo),PL/SQL程序設(shè)計(jì),24,光標(biāo)屬性,光標(biāo)屬性 每一個(gè)光標(biāo)有四種屬性 %FOUND 查詢(xún)語(yǔ)句(FETCH語(yǔ)句)返回記錄 %NOTFOUND 查詢(xún)語(yǔ)句(FETCH語(yǔ)句)無(wú)返回記錄，用于循環(huán)退出條件 %ROWCOUNT FETCH已獲取的記錄數(shù) %ISOPEN 光標(biāo)已打開(kāi)標(biāo)記 if not C%ISOPEN then open C; end if;,Loop FETCH C INTO v_ename,v_sal; EXIT WHEN C%NOTFOUND; END LOOP;,屬性使用,PL/SQL程序設(shè)計(jì),25,隱式光標(biāo)處理,隱式光標(biāo)處理 隱式光標(biāo)是指在處理SQL時(shí),不需定義光標(biāo)，所使用的SQL語(yǔ)句包括：INSERT ,UPDATE,DELETE子句。 隱式光標(biāo)屬性 SQL%FOUND SQL%NOTFOUND 由執(zhí)行上述語(yǔ)句結(jié)果引起 SQL%ROWCOUNT 隱式光標(biāo)的使用 Begin update emp set sal=1200 where empno=1234; if SQL%NOTFOUND then insert into Errors(empno,sal) values(1234,1200); end if; End;,數(shù)據(jù)庫(kù)中沒(méi)有編碼為1234的職工,PL/SQL程序設(shè)計(jì),26,例外處理(EXCEPTION),在PL/SQL中,警告信息、出錯(cuò)信息、或返回信息統(tǒng)稱(chēng)為例外(Exception)。有兩中類(lèi)型的例外。 Oracle預(yù)定義的例外: 是由PL/SQL運(yùn)行過(guò)程中，系統(tǒng)自動(dòng)產(chǎn)生的信息。 用戶(hù)定義例外: 是用戶(hù)根據(jù)需要，自己定義使用的例外，執(zhí)行時(shí) 由用戶(hù)自己引起。 預(yù)定義的例外 CURSOR_ALREADY_OPEN VALUE_ERROR NO_DATA_FOUND INVALID_NUMBER TOO_MANY_ROWS ZERO_DIVIDE INVALID_CURSOR 用戶(hù)自定義例外 用戶(hù)定義的例外必須在DECLARE段中說(shuō)明，在 Begin段中用RAISE引起，在EXCEPTION段中使用。,PL/SQL程序設(shè)計(jì),27,例外處理(EXCEPTION),用戶(hù)定義例外的說(shuō)明：定義名為 out_of_range 的例外 Declare out_of_range EXCEPTION; 例外的使用 Declare Begin Exception When NO_DATA_FOUND THEN When CURSOR_ALREADY_OPEN THEN When TOO_MANY_ROWS THEN . When OTHERS THEN . END;,例 外 段,在Exception段中 對(duì)返回信息一一作出響應(yīng),PL/SQL程序設(shè)計(jì)