【Oracle PL/SQL】Oracle PL/SQL之一 PL/SQL编程基礎

经378

SQL與PL/SQL塊的區別

使用SQL，每次的SQL應用都需要在網路上發送一次,使用PL/SQL塊則只需要在網路上發送一次,降低網路開銷
PL/SQL塊提供模組化的程序設計功能,以處理業務邏輯

PL/SQL編程工具
SQLPlus、iSQLPlus、PL/SQLDeveloper ......
　　如何查看一個命令的幫助，ex：helpset

PL/SQL編程基礎
PL/SQL塊結構
DECLARE
/*定義部份:定義常量、變數、複雜數據類型、遊標、例解.該部份可選*/
BEGIN
/*執行部份:PL/SQL語句和SQL語句.該部份必須有*/
EXCEPTION
/*例外處理部份:處理運行錯誤.該部份可選*/
END;/*塊結束標記,注意最後必須有一分號*/
如果要輸出結果,你需要如下設置:set serveroutput on

PL/SQL塊分類
匿名塊：不存于Oracle数据库里面,只可执行一次,下次执行需再编写
命名塊：存于Oracle数据库里面,下次可依塊名字進行調用.塊前用<<>>標記
子程序：過程、函數、包、觸發器(過程不需要返回值,但是函數一定要返回值)
注釋以–(單行)或/* */(多行)注記

PL/SQL變數定義
1、必須是字母開頭,PL/SQL對變數的大小寫不敏感.如果變數不是以字母開頭的必須使用雙引號引住.ex:"12法克魷"
2、變數可以是字母、數字、_、$、#
3、最大長度不可以超過30個字符
4、PL/SQL保留的關鍵字符不可以定義為變數如果變數是Oracle或PL/SQL關鍵字必須使用雙引號引住.ex:"update"
5、PL/SQL變數類型：Scalar、Composite、Reference、Largeobject(LOB)、Bind Variables(Non-PL/SQL variables)
6、每行只能夠定義一個標識符
syntax：
identifier[CONSTANT]datatype[NOT NULL][:=或者DEFAULTexpr]
變數名稱定義習慣：
　　

PL/SQL變數類型	變數習慣命名
Variable	v_variable_name
Constant	c_constant_name
Subprogram patameter	p_prameter_name
Bind(host) variable	b_bind_name
Cursor	cur_cursor_name
Record	rec_record_name
Type	type_name_type
Exception	e_exception_name
File handle	f_file_handle_name

ex：
DECLARE
v_hiredateDATE;
v_locationVARCHAR2(13)NOT NULL:=’CHINA’
v_company VARCHAR2(10)NOT NULLDEFALUT‘Oracle’
v_salaryCONSTANTNUMBER := 10000000--以後就不可以再對常量重新賦值了
如果一個VARCHAR變數裏面有引號怎麼轉意：v_company:= q’[terry’s zhanna]’; --用q’成對符
v_company :=‘terry’’s zhanna’;--用2個單引號做一個單引號
變數類型說明：
Scalar：標量(單一值,類部沒有其它結構ex:CHAR,VARCHAR2,NUMBER,BOOLEAN ,BINARY_INTEGER,BINARY_FLOAT...)
%TYPE：指定和資料庫某一列數據類型相同或和某一已經被聲明的變數類型相同
zxidzx_file.zx01%TYPE;
zxid_tempzxid%TYPE;
BOOLEAN：有三值TRUE/FALSE/NULL
字符和日期必須以成對的單引號’’引起來
Composite：複合類型(RECORD、COLLECTIONS(TABLE、VARRAY))
Reference：引用(REF CURSOR / REF object_type)
Large object：大類型,主要存儲BLOB、CLOB、BFILE等二進位數據
Bind Variables：環境變數,例如SQL與其它語言交互時使用的變數,例如C/JAVA變數
--在PL/SQL裏面使用SQL*Plus變數--PL/SQL引用SQL*Plus變數時必須先使用VARIABLE命令定義變數,如果要輸出則使用PRINT命令VARIABLE username VARCHAR2(16); --環境變數出了PL/SQL塊還可以繼續使用,使用時前面需要加冒號:BEGINSELECT zx02 INTO :username FROM zx_file WHERE zx01 = 'terry';END;/PRINT username;--在PL/SQL裏面使用Procedure Builder變數--PL/SQL引用Procedure Builder變數時必須先使用.CREATE命令定義變數,如果要輸出則使用包TEXT_IO命令.CREATE CHAR username LENGTH 16; --環境變數出了PL/SQL塊還可以繼續使用,使用時前面需要加冒號:BEGINSELECT zx02 INTO :username FROM zx_file WHERE zx01 = 'terry';END;/TEXT_IO.PUT_LINE(:username);--在PL/SQL裏面使用Pro*C/C++變數--PL/SQL引用Pro*C/C++變數時必須先定義宿主變數,如果要輸出則使用printf()語句char username[16];EXEC SQL EXECUTEBEGINSELECT zx02 INTO :username FROM zx_file WHERE zx01 = 'terry';END;END-EXEC;printf("%s\n",username);大多數SQL單行函數可以使用于PL/SQL編程中，DECORD/GROUP函數不可作用于PL/SQL
但是這些函數在PL/SQL中的SQL裏面依然是可以使用的
PL/SQL塊中只能夠直接嵌入DML語句及事務控制語句(COMMIT ROLLBACK SAVEPOINT)
而不能夠直接嵌入DDL語句(CREATE ALTER DROP)及DCL語句(GRANT REVOKE)
PL/SQL中的事務必須顯示的commit,否則事務不會自動提交！

嵌套塊：在PL/SQL的BEGIN塊或EXCEPTION裏面可以繼續再嵌入PL/SQL子塊。塊可以以<<>>命名

PL/SQL遊標CURSOR動態編程
當查詢的結果有多條記錄時,此時不可以再使用SELECT... INTO VARIABLE,而要使用遊標編程迴圈處理多條記錄
遊標分為：隱式遊標和顯示遊標
　　常用隱式遊標屬性：SQL%FOUND、SQL%NOTFOUND、SQL%ROWCOUNT、SQL%ISOPEN
　　SQL%ISOPEN : 當在PL/SQL中執行SELECT INTO / INSERT / UPDATE / DELETE時Oracle會隱含地打開遊標並且執行完後又會隱含地關閉遊標
　　SQL%ROWCOUNT : SQL語句作用的總計行數
　　SQL%NOTFOUND : SQL執行是否有作用行判斷
　　SQL%FOUND : SQL執行是否有作用行判斷
　　注意遊標屬性的使用必須在事務commit之前使用，事務提交之後則遊標屬性值也會重置為初始狀態
BEGINDELETE FROM gem_file WHERE gem01 = '2BSB';DBMS_OUTPUT.put_line('deleted rows:'||SQL%ROWCOUNT);--沒有提交事務END;/select gem01,gem02 from ds6.gem_filewhere gem01 = '2BSB'-- 依然可以查到數據-- GEM01      GEM02-- 2BSB          TE-- 遊標屬性的使用位置BEGINDELETE FROM gem_file WHERE gem01 = '2BSB';COMMIT;DBMS_OUTPUT.put_line('deleted rows:'||SQL%ROWCOUNT);  --在COMMIT後面END;/-- 結果是-- deleted rows:0-- PL/SQL procedure successfully completed-- 可以看到,數據已經被刪除了,但是結果是SQL%ROWCOUNT=0,因此你應該將SQL%ROWCOUNT置於COMMIT之前使用！
條件分支及迴圈控制語句
IF
IF condition THEN--condtion可以是TRUE/FALSE/NULL三種情況
statements;
[ELSIF condition THEN
statements;]
[ELSE
statements;]
END IF;
condition可以是ANDORNOT邏輯運算,運算規則如下:
　　

AND	TRUE	FALSE	NULL		OR	TRUE	FALSE	NULL		NOT
TRUE	TRUE	FALSE	NULL		TRUE	TRUE	TRUE	TRUE		TRUE	FALSE
FALSE	FALSE	FALSE	FALSE		FALSE	TRUE	FALSE	NULL		FALSE	TRUE
NULL	NULL	FALSE	NULL		NULL	TRUE	NULL	NULL		NULL	NULL

condtion中的ANDOR NOT 運算同其它語言一樣也具有短路顯現！
CASE
CASEselector
WHENexpression1 THEN result1
WHENexpression2 THEN result2
....
WHENexpressionN THEN resultN
[ELSE resultN+1]
END / END CASE; --CASE表達式時用END;CASE語句時必須用ENDCASE;
LOOP：不exit會死循環。語法如下：
LOOP
statement1; --無論是否滿足條件,至少會執行一次
....
EXIT [WHEN condtion]; --一定要寫EXIT,否則會陷入死循環
END LOOP;
WHILELOOP：0此或多次，不滿足condition才退出循環。語法如下：
WHILEconditionLOOP
....
END LOOP;
FORLOOP：語法如下
FORcounterIN[REVERSE]-- counter變數不需要顯示的定義
lower_bound .. upper_boundLOOP-- lower_bound與upper_bound必須是數字,FOR步長一定是1
... –counter沒有顯示定義時則只能夠在FOR體內使用
ENDLOOP;
CONTINUE：忽略當次循環直接進行下一次循環
CONTINUE和EXIT後面可以帶標籤

複合數據類型RECORDS及COLLECTIONS:
RECORDS和COLLECTIONS的區別,如下圖所示

RECORD
聲明：1、直接定義：TYPEtype_name IS RECORD
(field_declaration[,field_declaration]...);
  identifier type_name;
2、使用ROWTYPE：variabletable%ROWTYPE
3、使用顯示遊標：CURSORxx_cur IS SELECT * FROM table_file;
xx_cur_recxx_cur%ROWTYPE;
RECORD中的field_declaration定義
field_name{field_type | variable%TYPE | table.column%TYPE | table%ROWTYPE}
判斷一個RECORD是否為空,不能夠直接判斷RECORDIS NULL,而應該判斷RECORD中的每一個域ISNULL
比較兩個RECORD是否相等,不能夠直接比較RECORD1= RECORD2 ? 而需要判斷RECORD中的每一個域是否都相等
只要兩個RECORD各域及域數量兼容就可以相互賦值
你可以這樣給RECORD變數賦值：RECORD_VARIABLE:= NULL
使用RECORD做UPDATE時ROW關鍵字的應用

COLLECTION
為例處理單列數據開發人員可以使用標量變數,處理多列數據可以使用PL/SQL記錄
而如果處理單列多行則需要使用PL/SQL集合變數(PL/SQL表 嵌套表 變長陣列)
COLLECTION之Associative Arrays(index by tables)關聯陣列(及PL/SQL表或稱索引表)
TYEP type_name IS TABLE OF
{column_type | variable%TYPE | table.column%TYPE | table%ROWTYPE | INDEX BY PLS_INTEGER |BINARY_INTEGER | VARCHAR2}
identifier type_name;
關聯陣列只能夠存於PL/SQL中,而不是資料庫中
它有兩列：Key+Value,且Key與Value一一對應.
Key可以是數值或字符或字符串,數值可以是負數且可以不連續,Value可以是其它標量或複合類型
　　

Key	Value
1	Terry
2	Zhanna

操縱關聯陣列的方法
EXISTS、COUNT、FIRST、LAST、PRIOR、NEXT、DELETE、LIMIT、EXTEND、TRIM
Key是數值BINARY_INTEGER或PLS_INTEGER：
Key是字符或字符串VARCHAR2：
注意：當Key是字符串時，如果字符串的長度過長(>1000)則index效率較數值時會越來越差

COLLECTION之Nested Table嵌套表
嵌套表類似于高級語言中的陣列.不同的是高級語言的下標不能為負值,高級語言的元素個數是有限制的,
而嵌套表的元素個數是沒有限制的.
嵌套表和PL/SQL表類似,但嵌套表可以作為表列的數據類型,而PL/SQL表不能作為表列的數據類型
當在表列中使用嵌套表時,必須首先使用CREATE TYPE 語句建立嵌套表類型
--嵌套表定於語法TYPE type_name IS TABLE OF element_type;Identifier type_name--當PL/SQL塊中使用嵌套表元素時,必須首先使用其構造方法初始化嵌套表SET SERVEROUTPUT ONDECLARE TYPE zx_table_type IS TABLE OF zx_file.zx02%TYPE;zx_table zx_table_type;BEGINzx_table := zx_table_type('諸葛錢好');SELECT zx02 INTO zx_table(1) FROM zx_fileWHERE zx01 = &zx01;DBMS_OUTPUT.put_line(zx_table(1));END;/--嵌套表不僅可以在PL/SQL塊中使用,還可以作為表列的數據類型使用--但如果在表列中使用嵌套表類型,必須首先使用CREATE TYOE命令建立嵌套表類型--另外當使用嵌套表類型作為表列的數據類型時,必須要為嵌套表列指定專門的存儲表CREATE TYPE zx02_type IS TABLE OF zx_file.zx02%TYPE; --首先建立嵌套表類型/CREATE TABLE temp_zx_file(zx01  zx_file.zx01%TYPE,zx03  zx_file.zx03%TYPE,zx02  zx02_type                                   --再在temp_zx_file中使用嵌套表)NESTED TABLE zx02 STORE AS zx02_type;--如果為嵌套表插入數據須用嵌套表的構造方法--如果檢索潛逃表數據須先聲明該嵌套表的一變數用於存儲嵌套表的檢索結果--如果循環檢索嵌套表的信息,循環次數可以用COUNT屬性COLLECTION之Varray(變長陣列)
--VARRY定義語法TYPE type_name IS VARRAY(size_limit) OF element_type [NOT NULL];Identifier type_name;-VARRY不僅可以在PL/SQL塊中使用,還可以作為表列的數據類型使用--但如果在表列中使用VARRY類型,必須首先使用CREATE TYOE命令建立VARRY類型--另外當使用VARRY類型作為表列的數據類型時,必須要為VARRY表列指定專門的存儲表CREATE TYPE zx02_type IS VARRAY(16) zx_file.zx02%TYPE; --首先建立VARRY類型/CREATE TABLE temp_zx_file(zx01  zx_file.zx01%TYPE,zx03  zx_file.zx03%TYPE,zx02  zx02_type                                   --再在temp_zx_file中使用VARRY);--區別與嵌套表:嵌套表元素個數沒有限制,而VARRAY列的元素個數是有限制的嵌套表和變長陣列在使用之前必須先初始化,而索引表不需要,可以直接引用！

　　操縱關聯陣列的方法
　　EXISTS、COUNT、FIRST、LAST、PRIOR、NEXT、DELETE、LIMIT、EXTEND、TRIM方法介紹：
　　EXISTS()：確定集合元素是否存在,存在返回TRUE,不存在返回FALSE
　　COUNT：屬性,返回集合元素的個數,如果集合沒有元素則返回NULL
　　LIMIT：屬性,返回集合可以容納的最大元素個數.
　　對於索引表和嵌套表因為沒有最大的限制所以返回NULL,VARRAY返回其定義的最大個數值
　　FIRST/LAST：屬性,返回集合元素的第一個或最後一個元素所在的下標值
　　
COLLECTION之多級集合:在集合複合類型中再嵌入集合複合類型


顯示遊標的使用
Implicit cursors：隱式遊標,被PL/SQL聲明及管理,所有的DML語句都會產生隱式遊標
Explicit cursors：顯示遊標,在程序中被顯示的聲明及使用
CURSOR cursor_name IS
select statement;
OPEN cursor_name;
FETCH cursor_name ...
定義顯示遊標時指定相應的SELECT語句這種顯示遊標為靜態遊標
如果不指定SELECT語句而是在OPEN時才指定SELECT語句則為動態遊標

Explicit CURSOR FOR LOOP：顯示遊標的循環
FORrecord_nameINcursor_nameLOOP
statement1;
statement2;
....
END LOOP
顯示遊標循環應用時,顯示遊標將被隱式地open、Fetch、exit、close.循環變數record_name也將被隱式地聲明
顯示遊標循環還可以簡化去掉遊標的聲明部份
顯示遊標屬性：%ISOPEN、%NOTFOUND、%FOUND、%ROWCOUNT

帶參數的遊標使用
CURSOR cursor_name [(parameter_name datatype,...)]
IS select_statement;
OPEN cursor_name(parameter_value,...)

使用遊標fetch記錄再進行update
WHERE CURRENT OF cursor_name

異常處理
　　嵌入SELECT INTO語句需注意NO_DATA_FOUND異常和TOO_MANY_ROWS異常
　　WHERE子句需注意 變數名不能與列名相同,否則也會觸發TOO_MANY_ROWS異常