dba經(jīng)典輔助學(xué)習(xí)13a過程sql語言-pl

1、過程語言 PL/SQL第一節(jié)PL/SQL簡介 第二節(jié)PL/SQL塊的組成第三節(jié)PL/SQL流程控制第四節(jié) PL/SQL的異常處理第五節(jié)PL/SQL示例過程語言 PL/SQL(一）第一節(jié)PL/SQL簡介 第二節(jié)PL/SQL塊的組成第三節(jié)PL/SQL流程控制第一節(jié)PL/SQL簡介SQL: 指的是標(biāo)準(zhǔn)結(jié)構(gòu)化查詢語言；PL/SQL語言: 是Oracle RDBMS的一個組成部分,其中PL指的是過程化語言（Procedure Language），PL/SQL語言是在SQL語言中結(jié)合了結(jié)構(gòu)化語言成分的準(zhǔn)第四代編程語言,是對SQL語言的擴(kuò)展。PL/SQL語句塊中可以嵌入SQL的數(shù)據(jù)操縱及數(shù)據(jù)查詢語句 使用P

2、L/SQL的目的：由于大多數(shù)PL/SQL是在服務(wù)器端運(yùn)行，這樣可減少由客戶端運(yùn)行程序時所需的網(wǎng)絡(luò)數(shù)據(jù)流量。 使用PL/SQL的地方： PL/SQL可以單獨(dú)進(jìn)行程序的編寫，完成一般的處理功能； 在高級語言中可嵌入PL/SQL塊； 在第3代語言(如C語言等)中可以嵌入PL/SQL塊；PL/SQL的優(yōu)點(diǎn)集成性應(yīng)用程序Oracle 服務(wù)器共享庫PL/SQL的優(yōu)點(diǎn)應(yīng)用其它 DBMS應(yīng)用Oracle PL/SQLSQLSQLSQLSQLSQLIF.THENSQLELSESQLEND IF;SQL提高性能PL/SQL 的體系結(jié)構(gòu)SQL 語句執(zhí)行器主程序或 ORACLE 工具匿名 PL/SQL 塊匿名 PL/

3、SQL 塊過程語句執(zhí)行器ORACLE 服務(wù)器PL/SQL 引擎過程語句SQL語句 PL/SQL 的體系結(jié)構(gòu)PL/SQL 引擎是主要組件，它駐留在 Oracle 服務(wù)器中PL/SQL 引擎處理 PL/SQL 塊并將其分離為 SQL 語句及過程語句將過程語句發(fā)送到過程語句執(zhí)行器以進(jìn)行處理將 SQL 語句發(fā)送到 SQL 語句執(zhí)行器以進(jìn)行處理過程語言 PL/SQL(一）第一節(jié)PL/SQL簡介 第二節(jié)PL/SQL塊的組成第三節(jié)PL/SQL流程控制第二節(jié)程序塊結(jié)構(gòu)語言PL/SQL的組成P L / S Q L塊的類型：命名塊和匿名塊。PL/SQL塊的組成PL/SQL語言以塊為單位，塊中可以嵌套子塊。 一個基

4、本的PL/SQL塊由3部分組成：定義部分（DECLARE），可執(zhí)行部分（BEGIN），異常處理部分（EXCEPTION）。 定義部分 執(zhí)行部分 異常處理部分PL/SQL塊PL/SQL 語句塊結(jié)構(gòu)DECLARE 可選項(xiàng)塊的定義部分，定義PL/SQL變量、游標(biāo)、用戶定義異常BEGIN 必須的是塊中最重要的部分，必須至少包含一條下列語句SQL語句PL/SQL 語句EXCEPTION 可選當(dāng)錯誤發(fā)生時的處理動作END; 必須有的DECLAREBEGINEXCEPTIONEND;示例DECLARE v_date_of_joining DATE;BEGIN SELECT hiredate INTO v_d

5、ate_of_joining FROM emp WHERE empno = 7698; EXCEPTION WHEN no_data_found THEN DBMS_OUTPUT.PUT_LINE (No data found);END;塊類型匿名 過程 函數(shù)DECLAREBEGIN -statementsEXCEPTIONEND;PROCEDURE nameISBEGIN -statementsEXCEPTIONEND;FUNCTION nameRETURN datatypeISBEGIN -statements RETURN value;EXCEPTIONEND;主要內(nèi)容PL/SQL塊的定

6、義部分PL/SQL塊中使用到的變量、常量、游標(biāo)和異常處理的名字都必須先定義后使用。并且必須定義在以DECLARE關(guān)鍵字開頭的定義部分。PL/SQL塊的可執(zhí)行部分該部分是PL/SQL塊的主體，包含該塊的可執(zhí)行語句。該部分定義了塊的功能，是必須的。 由關(guān)鍵字BEGIN開始，EXCEPTION或END結(jié)束。PL/SQL塊的異常處理部分該部分包含該塊的異常處理程序（錯誤處理程序）。當(dāng)該塊程序體中的某個語句出現(xiàn)異常（檢測到一個錯誤）時，他將程序控制轉(zhuǎn)到異常部分的相應(yīng)的異常處理程序中進(jìn)行進(jìn)一步的處理。該部分由關(guān)鍵字EXCEPTION開始，END關(guān)鍵字結(jié)束。 一、定義部分變量的定義：是一個存儲空間，是在程序

7、中取出數(shù)據(jù)或傳入數(shù)據(jù)給程序。變量的名稱：必須是合法的標(biāo)識符變量的作用:存放暫時的數(shù)據(jù)操縱存儲的值可重用簡化維護(hù)變量的名稱變量的名稱：必須是合法的標(biāo)識符標(biāo)識符：必須以字母開頭可以由一個或多個字母、數(shù)字、或特殊字符（$、_）組成長度不超過30個字符名內(nèi)不能有空格定義變量名不可以為保留字有意義PL/SQL變量的習(xí)慣格式V_VariableName 程序變量E_ExceptionName 用戶自定義異常T_TypeName 用戶自定義類型變量P_ParameterName 存儲過程或函數(shù)參數(shù)變量C_ConstantValue 用CONSTANT關(guān)鍵字限制的變量變量類型PL/SQL變量:標(biāo)量記錄參照變量

8、LOB(大對象)非PL/SQL變量:綁定變量在 PL/SQL中操縱變量在聲明部分聲明和創(chuàng)建變量.在可執(zhí)行部分為變量賦值.通過參數(shù)為 PL/SQL塊傳送值.通過輸出變量查看結(jié)果.標(biāo)量格式： CONSTANT NOT NULL DEFAULT|:= ;說明在PL/SQL中使用的變量、常量、游標(biāo)和異常處理的名字都必須先定義后使用。定義部分是包括在關(guān)鍵字DECLARE和BEGIN之間的部分，每條語句后用；結(jié)束。每行只能定義一個標(biāo)識符。如果加上關(guān)鍵字CONSTANT,則表示所定義的標(biāo)識符為一個常量，必須為它賦初值。標(biāo)識符的命名規(guī)則與SQL的規(guī)則基本相同，即每個標(biāo)識符必須以字母開頭，而且不分大小寫。如果定

9、義的標(biāo)識符不能為空，則必須加上關(guān)鍵字NOT NULL,并賦初值。為標(biāo)識符賦值時，使用賦值符號：=，默認(rèn)值為空。Oracle 中常用的基本數(shù)據(jù)類型數(shù)據(jù)類型子類型及備注標(biāo)量類型數(shù)值BINARY_INTEGERNATURAL,POSITIVENUMBERDEC,DECIMAL,DOUBLE PRECISION,PLOAT,INTEGER,INT,NUMERIC,REAL,SMALLINT 字符CHARCHARACTER,STRING/固定長度字符數(shù)據(jù)VARCHAR2VARCHAR/變長字符類型最大為32767，列值LONG可變長字符數(shù)據(jù)最大長度為32760LONG RAWRAW固定長度的二進(jìn)制數(shù)據(jù)R

10、AWID偽列，用于存儲行標(biāo)識符邏輯BOOLEANTRUE，F(xiàn)ALSE，NULL日期DATE4712年組合類型記錄RECORD可定義記錄里域的名字、類型、初始值表TABLE定義表的類型和索引類型常用的雜符號示例identifier CONSTANT datatype NOT NULL := | DEFAULT expr;Declare v_hiredateDATE DEFAULT SYSDATE; v_deptnoNUMBER(2) NOT NULL := 10; v_locationVARCHAR2(13) := Atlanta; mCONSTANT NUMBER := 1400; 聲明PL/

11、SQL 變量指導(dǎo)：要遵從命名習(xí)慣.以 NOT NULL和CONSTANT約束來初始化某些變量.使用賦值操作符(:=)或者DEFAULT保留字來初始化變量.每行只聲明一個變量是一個好習(xí)慣.為一個變量賦值v_ename := Maduro;v_hiredate := 31-DEC-98;語法例如：為一個新雇員設(shè)定一個雇傭日期. 把雇員名字設(shè)為Maduro. identifier := expr;變量初始化和關(guān)鍵字使用方法:賦值操作符 (:=)DEFAULT關(guān)鍵字NOT NULL 約束2、聲明一個變量，使它的類型與某個變量或數(shù)據(jù)庫基本表中某個列的數(shù)據(jù)類型一致。（不知道該變量或列的數(shù)據(jù)類型）可以使用

12、%TYPE 。DECLARETeacher_name CHAR（5）;Student_name Teacher_name%TYPE;BEGIN ENDDECLARE NO EMP.EMPNO%TYPE;BEGIN END記錄類型與記錄型變量記錄類型定義的一般格式：TYPE IS RECORD( NOT NULLDEFAULT|:= , )；記錄型變量 說明標(biāo)識符 是定義的記錄類型名;定義記錄型變量，定義方法與前面標(biāo)量型變量定義一樣。記錄類型變量的屬性引用方法是.引用。示例1、聲明記錄類型和記錄類型變量DECLARE TYPE student IS RECORD (id NUMBER(4) NO

13、T NULL default 0, name CHAR(10) , sex BOOLEAN NOT NULL default true, birthdate DATE, physics NUMBER(3), chemistry NUMBER(3);-下面定義一個student類型的變量 student1 student;BEGIN END2、聲明一個記錄型變量，使它的類型與某個基本表的數(shù)據(jù)結(jié)構(gòu)一致，可以使用%ROWTYPE的形式定義。DECLAREEmp_value EMP %ROWTYPE;BEGINEND引用方式： EMP_VALUE .empnoEMP_VALUE .ename綁定變量定

14、義方法：variable 變量名 變量類型引用方法： ：綁定變量名二、可執(zhí)行部分 組成語句：變量賦值語句；流程控制語句；數(shù)據(jù)查詢、數(shù)據(jù)操縱和事務(wù)控制語句；游標(biāo)語句；賦值語句賦值賦號為：=。格式： ：= ；示例賦值的幾種情況。1、標(biāo)量型變量賦值TEACHER_NAME := LIU;2、記錄類型變量的賦值STUDENT1.NAME := FAN;STUDENT1.SEX:=TRUE;STUDENT1.PHYSICS:=90;3、%ROWTYPE型變量的賦值EMP_VALUE.ENAME:=SMITH;E MP_VALUE.EMPNO:=7369;EMP_VALUE.DEPTNO:=20;4、用S

15、ELECT語句為%ROWTYPE型變量整個賦值SELECT * INTO EMP_VALUE FROM EMP WHERE ENAME:=SMITH;SQL語句的使用在可執(zhí)行部分，可以使用SQL語句，但是不是所有的SQL語句都可以使用?？梢允褂玫闹饕校?MIT,ROLLBACK等數(shù)據(jù)查詢、數(shù)據(jù)操縱或事務(wù)控制命令，不能使用CREATE,ALTER,DROP,GRANT,REVOKE等數(shù)據(jù)定義和數(shù)據(jù)控制命令。說明在PL/SQL中，SELECT語句必須與INTO子句相配合，在INTO子句后面跟需要賦值的變量。在使用SELECT INTO時，結(jié)果只能有一條，如果返回了多條數(shù)據(jù)或沒有數(shù)據(jù)，則將產(chǎn)生錯誤

16、。（對于多條記錄的遍歷，可以使用游標(biāo)）在PL/SQL中，SQL語句的語法和交互命令時是一樣的。示例1查詢EMP中EMPNO=7876的雇員的信息。SET SERVEROUTPUT ONDECLARE-定義一個記錄類型變量EMP_VALUE EMP%ROWTYPE;BEGINSELECT * INTO emp_valueFROM empWHERE empno=7876;-輸出變量的值DBMS_OUTPUT.PUT_LINE(emp_value.ename);END;/計(jì)算表EMP中所有雇員的平均工資。DECLAREavg_sal NUMBER(8,3);BEGIN-使用select語句進(jìn)行賦值S

17、ELECT avg(sal)INTO avg_salFROM emp;DBMS_OUTPUT.PUT_LINE(avg_sal);END;/示例2示例3刪除員工名字為KING的員工信息。DECLARE v_ename VARCHAR2(10) := KING;BEGIN DELETE FROM emp WHERE ename = v_ename;END;嵌入塊主塊子塊示例DECLARE a CHAR(15);BEGIN a := Hello World DECLARE b NUMBER(5,2); BEGIN b := 50; dbms_output.put_line(b); END; dbm

18、s_output.put_line(a);END;過程語言 PL/SQL(一）第一節(jié)PL/SQL簡介 第二節(jié)PL/SQL塊的組成第三節(jié)PL/SQL流程控制第三節(jié)PL/SQL的流程控制 條件控制循環(huán)控制跳轉(zhuǎn)控制4.3.1條件控制IF_THEN_ELSE語句語法格式：IF THENELSEEND IF;功能：如果條件成立，將執(zhí)行，否則執(zhí)行，執(zhí)行完后，轉(zhuǎn)到下一條語句執(zhí)行。說明：是一個布爾型變量或表達(dá)式，取值只能是TRUE，F(xiàn)ALSE，NULL。執(zhí)行流程語句組1語句組2條件truefalseIF_THEN_ELSIF語句語法格式：IF THENELSIF THENELSIF THENELSEEND I

19、F;功能：如果成立，將執(zhí)行；否則判斷，如果成立執(zhí)行；否則，判斷，如此循環(huán)，直到判斷，如果都不成立，則執(zhí)行執(zhí)行，執(zhí)行完后，轉(zhuǎn)到下一條語句執(zhí)行 。說明：ELSIF不要誤寫為ELSEIF。執(zhí)行流程語句組1語句組2條件1truefalse條件2條件nfalse語句組n語句組n+1falsetruetrue示例1根據(jù)表emp中DEPTNO字段的值，為姓名為SMITH的雇員修改工資；若部門號為10，則工資加100；若部門號為20，則工資加300；否則工資加400。 declareincrement emp.sal%type;fandept emp.deptno%type;beginselect deptn

20、o into fandept from emp where ename = SMITH;if fandept = 10 then increment :=200;elsif fandept = 20 thenincrement :=300;else increment :=400;end if;update emp set sal = sal +increment where ename = SMITH;commit;end;/示例2DECLARE v_empno emp.empno%TYPE := &p_empno; v_deptno emp.deptno%TYPE ;BEGIN SELEC

21、T deptno INTO v_deptno FROM emp WHERE empno = v_empno; IF v_deptno = 10 THEN UPDATE emp SET mgr = 7782 WHERE empno = v_empno; END IF;END;示例3ACCEPT p_empno PROMPT Please enter the employee number: DECLARE v_empno emp.empno%TYPE := &p_empno; v_job emp.job%TYPE ;BEGIN SELECT job INTO v_job FROM emp WHE

22、RE empno = v_empno; IF v_job = CLERK THEN UPDATE emp SET sal = sal + (sal * 0.5) WHERE empno = v_empno; ELSE UPDATE emp SET sal = sal + (sal * 0.1) WHERE empno = v_empno; END IF;END;示例4DECLARE v_empno emp.empno%TYPE := &p_empno; v_deptno emp.deptno%TYPE ; v_bonus number (5,2);BEGIN SELECT deptno INT

23、O v_deptno FROM emp WHERE empno = v_empno; IF v_deptno= 10 THEN v_bonus := 500; ELSIF v_deptno = 20 THEN v_bonus := 800; ELSE v_bonus := 1000; END IF;END;4.3.2循環(huán)控制語句基本循環(huán)FOR循環(huán)WHILE循環(huán)基本循環(huán)(直到型）當(dāng)型循環(huán)至少會執(zhí)行一次FOR循環(huán)語法格式：FOR IN REVERSE LOOPEND LOOP;說明：循環(huán)變量是控制循環(huán)的變量，它不需要顯式的在變量定義部分進(jìn)行定義。系統(tǒng)隱含地將它看成一個整型變量。系統(tǒng)默認(rèn)時，計(jì)數(shù)器從

24、下界往上界遞增計(jì)數(shù)，如果使用REVERSE關(guān)鍵字，則表示計(jì)數(shù)器從下界到上界遞減計(jì)數(shù)。循環(huán)變量只能在循環(huán)體中使用，不能在循環(huán)體外使用。示例DECLARE v_ordid item.ordid%type:=601; BEGIN FOR v_counter IN 1.10 LOOP INSERT I(NTO item (ordid,itemid) VALUES(v_ordid, v_counter); END LOOP;END;WHILE循環(huán)語法格式：WHILE LOOPEND LOOP;執(zhí)行過程：先判斷，如果條件為真，則執(zhí)行循環(huán)體，繼續(xù)循環(huán)，否則結(jié)束循環(huán)。說明：當(dāng)型循環(huán)的循環(huán)體可能一次也不執(zhí)行。示

25、例DECLARE v_ordid item.ordid%type:=601; v_counter number(2) :=1; BEGIN WHILE v_counter =10 LOOP INSERT I(NTO item (ordid,itemid) VALUES(v_ordid, v_counter); v_counter:=v_counter+1; END LOOP;END;4.3.2循環(huán)控制語句循環(huán)的嵌套三種基本的循環(huán)語句可以進(jìn)行嵌套。即一個循環(huán)可以作為一個普通的語句，完全地嵌套到另一個循環(huán)之中。需要注意的是，嵌套的時候，一個循環(huán)必須完整的嵌套在另一個循環(huán)里，否則是錯誤的。示例DEC

26、LAREt1 number(10):=0;t2 number(10):=0;t3 number(10):=0;j number(10):=0;beginwhile(j100) loopj:=j+1;for i in 1.500 loopt1:=dbms_random.value(0,10000);t2:=dbms_random.value(0,10000);t3:=dbms_random.value(0,10000);insert into tt values(t1,t2,t3);commit;end loop;end loop;end;4.3.3標(biāo)號和跳轉(zhuǎn)語句語句標(biāo)號的使用在循環(huán)中的語法格式

27、：標(biāo)號LOOPEXIT 標(biāo)號 WHEN ；END LOOP 標(biāo)號 ;GOTO語句語法格式：標(biāo)號GOTO 標(biāo)號；說明：塊內(nèi)可以跳轉(zhuǎn)，內(nèi)層塊可以跳到外層塊，但外層塊不能跳到內(nèi)層。IF語句不能跳入。不能從循環(huán)體外跳入循環(huán)體內(nèi)。不能從子程序外部跳到子程序中。由于goto語句的缺點(diǎn)，建議盡量少用甚至不用goto語句。 4.3.3標(biāo)號和跳轉(zhuǎn)語句示例 采用GOTO求1100的和。 variable summ numberdeclareI number(3):=100;begin:summ:=0;:summ:=:summ + i;I:= I - 1;if I0 then goto label;end if;e

28、nd;/print sum;過程語言 PL/SQL第一節(jié)PL/SQL簡介 第二節(jié)PL/SQL塊的組成第三節(jié)PL/SQL流程控制第四節(jié) PL/SQL的異常處理第五節(jié)PL/SQL示例過程語言 PL/SQL（二）第四節(jié) PL/SQL的異常處理第五節(jié)PL/SQL示例第四節(jié)PL/SQL中的異常處理異常是什么?PL/SQL 在執(zhí)行的過程當(dāng)中所產(chǎn)生的標(biāo)識符。它是怎樣產(chǎn)生的?出現(xiàn)Oracle 錯誤使用raise語句，來明確地產(chǎn)生.怎樣對它進(jìn)行處理?使用處理句柄來捕獲它.向調(diào)用時的環(huán)境進(jìn)行傳遞.PL/SQL中的異常處理預(yù)定義異常對于Oracle預(yù)定義的異常，當(dāng)預(yù)定義的情況發(fā)生時，系統(tǒng)將自動觸發(fā)。用戶自定義的異常

29、需要程序員自己定義代碼，對異常情況進(jìn)行處理。異常處理的一般格式：DECLARE ;BEGIN ;EXCEPTION WHEN 異常情況1 OR 異常情況2 THEN ; WHEN異常情況3 OR 異常情況4 THEN ； WHEN OTHERS THEN ；END;4.4.1預(yù)定義的異常處理 異常情況名錯誤代碼描述DUP_VAL_ON_INDEXORA-00001試圖更新或插入重復(fù)記錄INVALID_CURSORORA-01001非法游標(biāo)操作INVALID_NUMBERORA-01722字符串向數(shù)字轉(zhuǎn)換失敗NO_DATA_FOUNDORA-01403執(zhí)行的SELECT沒有查到數(shù)據(jù)TOO_MANY_ROWSORA-01427未使用游標(biāo)，SELECT語句返回了多行數(shù)據(jù)VALUE_ERRORORA-06502出現(xiàn)數(shù)字、數(shù)據(jù)轉(zhuǎn)換、字符串或限制型錯誤ZERO_DIVIDEORA-01476被零除示例1處理預(yù)定義異常的一個例子DECLAREcursor emp_cursor is select * from emp;record emp%rowtype;beginfetch emp_cursor into record;exceptionwhen INVALID_CURSOR thenif emp_cursor%isopen thennul