PLSQL編程基礎(chǔ)

1、第第7章章 PL/SQL編程基礎(chǔ)編程基礎(chǔ)前面所學(xué)習(xí)的SQL是操作關(guān)系性數(shù)據(jù)庫的一種通用語言，但是SQL本身是一種非過程化的語言，即不用指明執(zhí)行的具體方法和途徑，而是簡單地調(diào)用相應(yīng)語句直接取得結(jié)果即可。所以SQL本身并不適合在復(fù)雜的業(yè)務(wù)流程下使用，為了解決這一問題，Oracle提供了PL/SQL編程，這是一種過程化編程語言，與JAVA語言一樣關(guān)注于處理細(xì)節(jié)，可以實(shí)現(xiàn)較為復(fù)雜的業(yè)務(wù)邏輯。7.1 PL/SQL簡介簡介PL/SQL語法結(jié)構(gòu)語法結(jié)構(gòu)DECLARE -聲明部分，例如：定義變量、常量、游標(biāo)BEGIN -程序編寫、SQL語句EXECEPTION -處理異常END;/聲明部分（聲明部分（DECL

2、ARE）：包含變量定義、用戶定義的PL/SQL類型、游標(biāo)、引用 的函數(shù)或過程。執(zhí)行部分（執(zhí)行部分（BEGIN）：包含變量賦值、對(duì)象初始化、條件結(jié)構(gòu)、迭代結(jié)構(gòu)、嵌套的PL/SQL塊，或是對(duì)局部或者存儲(chǔ)PL/SQL命名塊的調(diào)用。異常部分（異常部分（exception）：包含錯(cuò)誤處理語句，該語句可以像執(zhí)行部分一樣使用所有項(xiàng)。結(jié)束部分（結(jié)束部分（END）:程序執(zhí)行到END表示結(jié)束，分號(hào)用戶結(jié)束塊，正斜杠（/）執(zhí)行塊程序既沒有變量，也沒有流程控制（分支、循環(huán)）。PL/SQL與與SQL區(qū)別：區(qū)別：SQLPL/SQLPL/SQL是結(jié)構(gòu)化和過程化的結(jié)合體。每條SQL語句逐一發(fā)送給數(shù)據(jù)庫。一次性將多條SQL語言

3、一起發(fā)送給數(shù)據(jù)庫7.1 PL/SQL簡介簡介最簡單的PL/SQL塊可以不做任何事情，但是在PL/SQL編程過程中要求，執(zhí)行塊至少要有一條語句，即使這條語句只是編寫一個(gè)NULL也行。范例：范例：編寫不做任何工作的PL/SQL塊。BEGIN NULL;END;/范例：范例：編寫一個(gè)簡單的PL/SQL程序DECLAREv_num number;BEGIN v_num:=30; DBMS_output.put_line(v_num變量的內(nèi)容是：|v_num);END;7.1 PL/SQL簡介簡介范例：范例：編寫PL/SQL塊，輸入一個(gè)雇員編號(hào)，而后取得指定的雇員姓名。Declare v_eno num

4、ber; v_ename varchar2(10);BeginV_eno:=&empno; -由鍵盤輸入雇員編號(hào)Select ename into v_ename from emp where empno=v_eno;DBMS_OUTPUT.put_line(編號(hào)為:| v_eno |雇員的名字為：|v_ename);End;7.1 PL/SQL簡介簡介7.2 變量的聲明與賦值變量的聲明與賦值變量聲明并使用變量：變量聲明并使用變量：n 變量名稱可以有字母、數(shù)字、_、$、#等組成。n 所有變量名稱要求以字母開頭，不能是Oracle中的保留字n 變量的長度最多只能為30個(gè)字符n 變量不區(qū)分大小寫7

5、.2 變量的聲明與賦值變量的聲明與賦值聲明變量語法：聲明變量語法：變量名稱 CONSTANT 類型 not NULL :=value使用使用%TYPE聲明變量類型：聲明變量類型：在編寫PL/SQL程序時(shí)，如果希望某一個(gè)變量與指定數(shù)據(jù)表中某一列的類型一樣，則可以采用 “變量定義變量定義”表名稱表名稱.表名稱表名稱%TYPE的格式的格式7.2 變量的聲明與賦值變量的聲明與賦值Declare v_eno emp.empno%type; v_ename emp.ename%type;Begin DBMS_output.put_line(請(qǐng)輸入雇員編號(hào)：); v_eno:=&empno; select

6、ename into v_ename from emp where empno=v_eno; DBMS_output.put_line(編號(hào)為：|v_eno | 雇員的名字為：|v_ename);end;7.2 變量的聲明與賦值變量的聲明與賦值使用使用%ROWTYPE聲明變量類型聲明變量類型PL/SQL提供了一種%ROWTYPE，使用此標(biāo)記可以定義表中一行記錄的類型。當(dāng)用戶使用了“selectinto”將表中的一行記錄設(shè)置為ROWTYPE類型的變量中時(shí)，就可以利用“rowtype 變量.表字段”的方式取得表中每行對(duì)應(yīng)的列數(shù)據(jù)。7.2 變量的聲明與賦值變量的聲明與賦值Declare v_dept

7、row dept%rowtype; -裝載一行dept記錄 Begin select * into v_deptrow from dept where deptno=10; DBMS_output.put_line(部門編號(hào)：|v_deptrow.deptno | 名稱：|v_deptrow.dname |位置： | v_deptrow.loc);End;7.3 標(biāo)量類型標(biāo)量類型數(shù)值型標(biāo)量類型字符型日期型大對(duì)象布爾7.3 標(biāo)量類型標(biāo)量類型No.分分類類數(shù)據(jù)類型數(shù)據(jù)類型描述描述1數(shù)值型Number(數(shù)據(jù)總長度,小數(shù)位長度)Number是一種表示數(shù)字的數(shù)據(jù)類型?？陕暶鞅４鏀?shù)據(jù)類型的整數(shù)位和小數(shù)位的

8、精度，在數(shù)據(jù)庫中是以十進(jìn)制格式存儲(chǔ)，在計(jì)算時(shí)，系統(tǒng)將其變成二進(jìn)制數(shù)據(jù)運(yùn)算， 占32字節(jié)。BINARY_INTEGER不存儲(chǔ)在數(shù)據(jù)庫中，只能在PL/SQL中使用的帶符號(hào)整數(shù)，范圍為-231-231，如果發(fā)生溢出，則自動(dòng)變?yōu)閚umber型數(shù)據(jù)。23PLS_INTEFER有符號(hào)的整數(shù)，范圍為-231-231，可以直接進(jìn)行數(shù)學(xué)運(yùn)算，溢出則觸發(fā)異常，與Number相比，其占空間小，性能好4BINARY_FLOAT單精度32位浮點(diǎn)類型，占5個(gè)字節(jié)5BINARY_DOUBLE雙精度浮點(diǎn)數(shù)類型，占9個(gè)字節(jié)7.3 標(biāo)量類型標(biāo)量類型No.分分類類數(shù)據(jù)類型數(shù)據(jù)類型描述描述12字符型RAW保存固定長度的二進(jìn)制數(shù)據(jù)，最

9、多可以存放2000個(gè)字節(jié)的數(shù)據(jù)LONG RAW存儲(chǔ)二進(jìn)制數(shù)據(jù)（圖片、音樂等），最多可以存儲(chǔ)2G大小數(shù)據(jù)1314ROWID數(shù)據(jù)表中每行記錄的唯一物理地址標(biāo)示，只支持物理行ID，不支持邏輯行ID15UROWID支持物理行ID和邏輯行ID7.3 標(biāo)量類型標(biāo)量類型No.分分類類數(shù)據(jù)類型數(shù)據(jù)類型描述描述16日期型DATADATA是一個(gè)7字節(jié)的列，可以保存時(shí)期和時(shí)間，不包含毫秒TIMESTAMPDATA子類型，包含日期和時(shí)間，時(shí)間部分包含毫秒，有TIMESTAMP WITH TIME ZONE和TIMESTAMP WITH LOCAL TIME ZONE1718INTERVALDATA的子類型，用于管理時(shí)

10、間間隔，有INTERVAL DAY TO SECOND和INTERVAL YEAR TO MONTH兩種子類型7.3 標(biāo)量類型標(biāo)量類型No.分分類類數(shù)據(jù)類型數(shù)據(jù)類型描述描述19大對(duì)象CLOBCLOB數(shù)據(jù)類型代表字符型大對(duì)象，最大存儲(chǔ)4GB的字符串?dāng)?shù)據(jù)NCLOB存儲(chǔ)UNICODE編碼的大文本數(shù)據(jù)，最多可以存儲(chǔ)4GB的非結(jié)構(gòu)化的二進(jìn)制數(shù)據(jù)2021BLOBBLOB數(shù)據(jù)類型列可以包含最大4GB的非結(jié)構(gòu)化二進(jìn)制數(shù)據(jù)22BFILE包含存儲(chǔ)在外部文件系統(tǒng)上文件的索引，最大不超過4GB23布爾BOOLEAN布爾類型，可以設(shè)置的內(nèi)容為TRUE、FALSE、NULL7.4 程序結(jié)構(gòu)程序結(jié)構(gòu) PL/SQL程序與其他

11、編程語言一樣，也擁有自己的3中程序結(jié)構(gòu)，即順序結(jié)構(gòu)、分支結(jié)構(gòu)、循環(huán)結(jié)構(gòu)。開始語句1語句2語句n結(jié)束順序結(jié)構(gòu)判斷條件語句2語句n結(jié)束語句1分支結(jié)構(gòu)語句1語句2判斷條件循環(huán)結(jié)構(gòu)7.4.1 分支結(jié)構(gòu)分支結(jié)構(gòu)IF語句語句If語句IFelse語句IFelseifelse語句If判斷條件 Then 滿足條件執(zhí)行語句；End if;If判斷條件 then 滿足條件執(zhí)行語句；Else 不滿足條件執(zhí)行語句；End IF;If判斷條件1 then 滿足條件1執(zhí)行語句；Elseif 判斷條件2 then Else 所有條件不滿足執(zhí)行語句；END if;7.4.1 分支結(jié)構(gòu)分支結(jié)構(gòu)范例：范例：查詢emp表的工資。輸入

12、員工編號(hào)，根據(jù)編號(hào)查詢工資。如果工資高于3000元，則顯示高工資；如果工資大于2000元?jiǎng)t顯示中等工資；如果小于2000元，則顯示低工資。DECLAREv_empSalemp.sal%TYPE ;- 定義變量與emp.sal字段類型相同v_empNameemp.ename%TYPE ;- 定義變量與emp.ename字段類型相同v_enoemp.empno%TYPE ;- 定義變量與emp.empno字段類型相同BEGINv_eno := &inputEmpno;- 用戶輸入要查找的雇員編號(hào)- 根據(jù)輸入的雇員編號(hào)查找雇員姓名及工資SELECT ename,sal INTO v_empName,

13、v_empSal FROM emp WHERE empno=v_eno;IF v_empSal 3000 THEN- 判斷DBMS_OUTPUT.put_line(v_empName | 的工資屬于高工資！) ;ELSIF v_empSal 2000 THEN- 判斷DBMS_OUTPUT.put_line(v_empName | 的工資屬于中等工資！) ;ELSEDBMS_OUTPUT.put_line(v_empName | 的工資屬于低工資！) ;END IF;END;7.4.1 分支結(jié)構(gòu)分支結(jié)構(gòu)范例：范例：用戶輸入一個(gè)雇員編號(hào)，根據(jù)它所在的部門給上漲工資，規(guī)則： 10部門上漲10%，2

14、0上漲20%，30上漲30%； 但是要求最高不能超過5000，超過5000就停留在5000。7.4.1 分支結(jié)構(gòu)分支結(jié)構(gòu)7.4.1 分支結(jié)構(gòu)分支結(jié)構(gòu)DECLAREv_empSalemp.sal%TYPE ;- 定義變量與emp.sal字段類型相同v_dnoemp.deptno%TYPE ;- 定義變量與emp.deptno字段類型相同v_enoemp.empno%TYPE ;- 定義變量與emp.empno字段類型相同BEGINv_eno := &inputEmpno;- 用戶輸入要查找的雇員編號(hào)SELECT deptno,sal INTO v_dno, v_empSal FROM emp W

15、HERE empno = v_eno ;IF v_dno = 10 THENIF v_empSal * 1.1 5000 THENUPDATE emp SET sal=5000 WHERE empno=v_eno;ELSEUPDATE emp SET sal=sal*1.1 WHERE empno=v_eno;END IF;ELSIF v_dno = 20 THENIF v_empSal * 1.2 5000 THENUPDATE emp SET sal =5000 WHERE empno=v_eno;ELSEUPDATE emp SET sal=sal*1.2 WHERE empno=v_e

16、no;END IF;ELSIF v_dno = 30 THENIF v_empSal * 1.3 5000 THENUPDATE emp SET sal=5000 WHERE empno=v_eno;ELSEUPDATE emp SET sal=sal*1.3 WHERE empno=v_eno;END IF;ELSEnull;END IF;END;7.4.1 分支結(jié)構(gòu)分支結(jié)構(gòu)CASE語句語句Case語句是一種多條件的判斷語句，其功能與IFelseifelse類似Case語法：語法：Case變量 when 值|表達(dá)式 THEN 執(zhí)行語句塊；when 值|表達(dá)式 THEN 執(zhí)行語句塊；Else

17、條件都不滿足時(shí)執(zhí)行語塊；End case7.4.1 分支結(jié)構(gòu)分支結(jié)構(gòu)范例：范例：使用CASE進(jìn)行多條件判斷DECLAREv_salaryemp.sal%TYPE ;v_enoemp.empno%TYPE ;BEGINv_eno := &inputEmpno ;SELECT sal INTO v_salary FROM emp WHERE empno=v_eno ;CASEWHEN v_salary = 3000 THENDBMS_OUTPUT.put_line(雇員： | v_eno | 的收入為高工資。) ;WHEN v_salary = 2000 AND v_salary = 3 ;v_i := v_i + 1 ;END LOOP ;END ;7.4.2 循環(huán)結(jié)構(gòu)循環(huán)結(jié)構(gòu)范例：范例：使用WHILELOOP循環(huán)DECLAREv_i NUMBER := 1 ;- 定義一個(gè)變量，用于循環(huán)BEGINWHILE (v_i 10 AND v_data 0 THEN- 部門存在部門存在RAISE_APPLICATION