火龍果軟件PLSQL經(jīng)典教程

PL/SQL程序設(shè)計(jì)簡介什么是PL/SQLPL/SQL是ProcedureLanguage&StructuredQueryLanguage（過程化SQL語言）的縮寫PL/SQL是對(duì)SQL語言存儲(chǔ)過程語言的擴(kuò)展PL/SQL的優(yōu)點(diǎn)它最大的優(yōu)點(diǎn)是提供了一種服務(wù)器端存儲(chǔ)過程語言

，可以對(duì)數(shù)據(jù)進(jìn)行快速高效的處理。任何客戶/服務(wù)器工具都能訪問PL/SQL程序，具有很好的可重用性?？梢允褂肙RACLE數(shù)據(jù)工具管理存儲(chǔ)在服務(wù)器中的PL/SQL程序的安全性?？梢允跈?quán)或撤銷數(shù)據(jù)庫其他用戶訪問PL/SQL程序的能力。

對(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ò)擁擠。

PL/SQL塊結(jié)構(gòu)和組成元素PL/SQL程序由三個(gè)塊組成，即聲明部分、執(zhí)行部分、異常處理部分。PL/SQL塊PL/SQL塊結(jié)構(gòu)和組成元素PL/SQL塊的結(jié)構(gòu)如下：DECLARE/*聲明部分:在此聲明PL/SQL用到的變量,類型及游標(biāo)，以及局部的存儲(chǔ)過程和函數(shù)*/BEGIN/*執(zhí)行部分:過程及SQL語句,即程序的主要部分*/EXCEPTION/*執(zhí)行異常部分:錯(cuò)誤處理*/END;其中執(zhí)行部分是必須的。PL/SQL塊結(jié)構(gòu)和組成元素PL/SQL塊可以分成三類：匿名塊：動(dòng)態(tài)構(gòu)造，只能執(zhí)行一次子程序：存儲(chǔ)在數(shù)據(jù)庫中的存儲(chǔ)過程、函數(shù)及包等。當(dāng)在數(shù)據(jù)庫上建立好后可以在其它程序中調(diào)用它們觸發(fā)器：當(dāng)數(shù)據(jù)庫發(fā)生操作時(shí)，會(huì)觸發(fā)一些事件，從而自動(dòng)執(zhí)行相應(yīng)的程序PL/SQL中的標(biāo)識(shí)符PL/SQL程序設(shè)計(jì)中的標(biāo)識(shí)符定義與SQL的標(biāo)識(shí)符定義的要求相同標(biāo)識(shí)符名不能超過30字符第一個(gè)字符必須為字母不能用’-‘(減號(hào))不分大小寫不能是SQL保留字PL/SQL中的標(biāo)識(shí)符一般不要把變量名聲明與表中字段名完全一樣,如果這樣可能得到不正確的結(jié)果例：DECLAREEnamevarchar2(20):=’KING’;BEGIN DELETEFROMempWHEREename=ename;END;PL/SQL中的標(biāo)識(shí)符下面是建議使用的變量命名方法

標(biāo)識(shí)符命名原則例程序變量

V_nameV_id程序常量

C_constantC_student_name游標(biāo)變量

Name_cursorEmp_cursor異常標(biāo)識(shí)

E_nameE_too_many_rows表類型

Name_table_typeEmp_record_type表

Name_tableEmp記錄類型

Name_typeEmp_record參數(shù)P_nameP_id練習(xí)：給’KING’的工資漲10%PL/SQL中的變量類型類型說明CHAR(n)定長字符串,長度可選,缺省1。最大可達(dá)2000字節(jié)VARCHAR2(n)可變字符串,長度不能缺省,最大可達(dá)4000字節(jié)BINARY_INTEGER帶符號(hào)整數(shù),為整數(shù)計(jì)算優(yōu)化性能NUMBER(p,s)數(shù)值，p代表刻度，s代表精度LONG變長字符串DATE日期BOOLEAN布爾ROWID存放數(shù)據(jù)庫行號(hào)PL/SQL中聲明變量在語句塊的聲明部分對(duì)變量聲明，聲明一個(gè)變量的語法是：variable_name[constant]type[notnull][:=value]其中：

variable_name為變量名type為類型

value為變量的初值PL/SQL中聲明變量例：DECLARE V_DescriptionVARCHAR2(50); V_NumberNUMBER:=45; V_CounterBINARY_INTEGER:=0;注意：如果變量在聲明時(shí)使用了NOTNULL選項(xiàng)則必須為變量指定初值。如果變量在聲明時(shí)使用了CONSTANT選項(xiàng)則必須為變量指定初值， 并且該初值不能被改變。練習(xí)：查詢員工號(hào)為7788的員工姓名，并顯示在控制臺(tái)。

PL/SQL中的復(fù)合類型

記錄類型：記錄類型是把邏輯相關(guān)的數(shù)據(jù)作為一個(gè)單元存儲(chǔ)起來定義記錄類型語法如下：TYPErecord_typeISRECORD(Field1type1[NOTNULL][:=exp1],Field2type2[NOTNULL][:=exp2],......Fieldntypen[NOTNULL][:=expn]);

練習(xí)1：創(chuàng)建一個(gè)圖書記錄類型（書號(hào)，書名），顯示結(jié)果為： 書號(hào)：1100書名：VB.Net練習(xí)2：顯示7788號(hào)員工的員工號(hào)，姓名，工資

PL/SQL中的復(fù)合類型

%TYPE:定義一個(gè)變量，其數(shù)據(jù)類型與已經(jīng)定義的某個(gè)數(shù)據(jù)變量的類型相同，或者與數(shù)據(jù)庫表的某個(gè)列的數(shù)據(jù)類型相同 ，這時(shí)可以使用%TYPE

例：DECLARE--用%TYPE類型定義與表相配的字段

TYPEt_RecordISRECORD(T_noemp.empno%TYPE,T_nameemp.ename%TYPE,T_salemp.sal%TYPE);--聲明接收數(shù)據(jù)的變量

v_empt_Record;

PL/SQL中的復(fù)合類型

%ROWTYPE:返回一個(gè)記錄類型,其數(shù)據(jù)類型和數(shù)據(jù)庫表的數(shù)據(jù)結(jié)構(gòu)相 一致。

例：DECLARE v_empnoemp.empno%TYPE:=&no;recemp%ROWTYPE;BEGINSELECT*INTOrecFROMempWHEREempno=v_empno;DBMS_OUTPUT.PUT_LINE('姓名:'||rec.ename||'工資:'||rec.sal||'工作時(shí)間:'||rec.hiredate);END;PL/SQL表

PL/SQL表或者稱為索引表(index-table)，是可以在PL/SQL程序中引用、能夠模仿數(shù)組的非永久表。用戶可以定義一個(gè)表類型，然后聲明這種類型的變量。接下來，用戶就可以將記錄添加到用戶的PL/SQL表中，并且采用與引用數(shù)組元素大體相同的方法引用他們

表包括兩個(gè)基本成分： 數(shù)據(jù)處理類型為BINARY_INTEGER主鍵

標(biāo)量或記錄數(shù)據(jù)類型的列

PL/SQL表

定義一個(gè)表：TYPEtype_nameISTABLEOF {column_type|variable%TYPE|table.column%TYPE}[NOTNULL]|table%ROWTYPE INDEXBYBINARY_INTEGER;

例： DECLARE TYPEdept_table_typeISTABLEOF dept%ROWTYPEINDEXBYBINARY_INTEGER; my_dname_tabledept_table_type;

引用表元素

例：DECLARE typedept_table_typeistableof dept%ROWTYPEindexbybinary_integer; my_dname_tabledept_table_type;intnumber(3);BEGIN int:=-10; my_dname_table(int).dname:='searching'; dbms_output.put_line(my_dname_table(int).dname); int:=0; my_dname_table(int).dname:='market'; dbms_output.put_line(my_dname_table(int).dname); int:=10; my_dname_table(int).dname:='finance'; dbms_output.put_line(my_dname_table(int).dname);END;PL/SQL表

注意：

Index-by表中的元素不一定要按任何特定的順序排序用于Index-by表的關(guān)鍵字沒有必要是順序的

關(guān)鍵字唯一允許的類型是BINARY_INTERGER練習(xí)：定義數(shù)組元素為部門表的記錄的數(shù)組，保存10號(hào)，20號(hào)部門的信息并顯示。PL/SQL中的可變數(shù)組

一般格式為：TYPEtype_nameISVARRAY(maximum_size)OFelement_type[NOTNULL]

其中：type_name是新可變長數(shù)組類型的類型名。 maximum_size是一個(gè)指定可變數(shù)組中元素最大數(shù)目的整數(shù)。element_type是一個(gè)PL/SQL標(biāo)量、記錄或?qū)ο箢愋汀? NOTNULL子串表示集合中的每一個(gè)元素都必須有值。

一般格式為：DECLARE typenumberlistisvarray(10)ofnumber(5);typerecordlistisvarray(5)ofdept%rowtype;

引用數(shù)組元素

例：DECLARE typestringsisvarray(5)ofvarchar2(10);--Declareavarraywithfourelement v_liststrings:=strings('scott','peter','smith','tom');intnumber;BEGIN int:=1;dbms_output.put_line(v_list(int)); v_list(int):='urman';dbms_output.put_line(v_list(int)); int:=3;dbms_output.put_line(v_list(int)); v_list(int):='jackson'; dbms_output.put_line(v_list(int));v_list.extend;v_list(5):='oracle';dbms_output.put_line(v_list(5));END;PL/SQL表和可變數(shù)組的區(qū)別VARRAY集合中的元素的數(shù)量是有限的，Index_by則是沒有限制的。Index_by表的下標(biāo)可以有間隔，VARRAY集合的下標(biāo)之間沒有間隔。Index_by表不能存儲(chǔ)在數(shù)據(jù)庫中，但嵌套表和VARRAY可以被存儲(chǔ)在數(shù)據(jù)庫中。Index_by表初始化是自動(dòng)進(jìn)行的，VARRAY就必須使用內(nèi)建的構(gòu)造函數(shù)，構(gòu)造函數(shù)和集合的名字相同。

表和數(shù)組屬性

屬性返回類型描述有效范圍Exists(n)boolean指定索引為n的元素在集合中是否存在表、可變數(shù)組countnumber返回集合中元素的數(shù)目表、可變數(shù)組limitnumber返回集合中最大元素?cái)?shù)可變數(shù)組First&lastBinary_integer返回第一個(gè)（最后一個(gè)）元素的索引表、可變數(shù)組Next&priorBinary_integer返回當(dāng)前元素的下一個(gè)（前一個(gè)）元素的索引表、可變數(shù)組extendN/A向集合末尾添加一個(gè)元素可變數(shù)組trimN/A從集合的最后刪除一個(gè)元素可變數(shù)組Delete(n)N/A刪除元素下標(biāo)為n的元素

表

表和數(shù)組屬性

例：DECLARE typestrings_tableistableofvarchar2(10)indexbybinary_integer; stringsstrings_table; intnumber;BEGIN int:=1;strings(int):='element1';ifstrings.exists(int)thendbms_output.put_line(strings(int));elsedbms_output.put_line('nodata!');return;endif;

表和數(shù)組屬性

例： strings(2):='element2'; strings(3):='element3';strings(4):='element4'; strings(5):='element5';strings(6):='element6';dbms_output.put_line(strings.count);dbms_output.put_line(strings.first);dbms_output.put_line(strings.last);dbms_output.put_line(strings.next(2));dbms_output.put_line(strings.prior(4));strings.delete(1,3);--dbms_output.put_line(strings(2));END;PL/SQL運(yùn)算符和表達(dá)式

關(guān)系運(yùn)算符

運(yùn)算符意義=等于<>,!=,~=,^=不等于<小于>大于<=小于等于>=大于等于PL/SQL運(yùn)算符和表達(dá)式

一般運(yùn)算符運(yùn)算符意義+加號(hào)-減號(hào)*乘號(hào)/除號(hào):=賦值號(hào)||連接符..范圍運(yùn)算符PL/SQL運(yùn)算符和表達(dá)式

邏輯運(yùn)算符運(yùn)算符意義ISNULL空值BETWEENAND介于兩者之間IN在一個(gè)值列表之中AND邏輯與

OR邏輯或

NOT取返,如ISNOTNULL,NOTIN

PL/SQL中的變量賦值在PL/SQL編程中，變量賦值是一個(gè)值得注意的地方，它的語法如下：

variable:=expression;variable是一個(gè)PL/SQL變量,expression是一個(gè)PL/SQL表達(dá)式.BOOLEAN型變量賦值：

布爾值只有TRUE,FALSE及NULL三個(gè)值?？罩导訑?shù)字仍是空值：NULL+<數(shù)字>=NULL?？罩导樱ㄟB接）字符，結(jié)果為字符：NULL||<字符串>=<字符串>PL/SQL中的變量賦值例：DECLARE doneBOOLEAN; /*thefollowingstatementsarelegal:*/BEGINdone:=FALSE;WHILENOTdoneLOOP Null;ENDLOOP;END;PL/SQL中的變量作用范圍及可見性

PL/SQL的變量作用范圍特點(diǎn)是：變量的作用范圍是在所引用的程序單元（塊、子程序、包）內(nèi)。即從聲明變量開始到該塊的結(jié)束一個(gè)變量（標(biāo)識(shí)）只能在所引用的塊內(nèi)是可見的當(dāng)一個(gè)變量超出了作用范圍，PL/SQL引擎就釋放用來存放該變量的空間（因?yàn)樗赡懿挥昧耍┰谧訅K中重新定義該變量后，它的作用僅在該塊內(nèi)PL/SQL中的變量作用范圍及可見性

例：

DECLARE v_NumberNUMBER(3,2); BEGIN DECLARE v_CharacterVARCHAR2(10); BEGIN ………… END; END;v_Number的作用域v_Character的作用域PL/SQL中的注釋在PL/SQL里，可以使用兩種符號(hào)來寫注釋：PL/SQL允許用雙–來寫注釋，它的作用范圍是只能在一行有效。

例： V_SalNUMBER(12,2);--工資變量使用/**/來加一行或多行注釋。 例：/***********************************************//*文件名：department_salary.sql*//**********