教學(xué)大綱：
PLSQL編程：Hello World、程序結(jié)構(gòu)、變量、流程控制、游標。
存儲過程：概念、無參存儲、有參存儲（輸入、輸出）。
JAVA調(diào)用存儲存儲過程。
1.PLSQL編程
1.1.概念和目的
什么是PL/SQL？
PL/SQL（Procedure Language/SQL）
PLSQL是Oracle對sql語言的過程化擴展（類似于Basic）
指在SQL命令語言中增加了過程處理語句（如分支、循環(huán)等），使SQL語言具有過程處理能力。
1.2.程序結(jié)構(gòu)
通過Plsql Developer工具的Test Window 創(chuàng)建程序模板或者通過語句在SQL Window編寫
提示：PLSQL語言的大小寫是不區(qū)分的
PL/SQL可以分為三個部分：聲明部分、可執(zhí)行部分、異常處理部分。
1-- Created on 2020/11/16 by 32251
2declare
3-- 聲明變量、游標
4-- Local variables here
5iinteger;
6begin
7-- 執(zhí)行語句
8-- Test statements here
9--[異常處理] ?
10 end;
其中DECLARE部分用來聲明變量或游標（結(jié)果集類型變量），如果程序中無變量聲明可以省略
1.3.Hello World
1BEGIN
2
3--打印hello world
4
5Dbms_output.put_line('hello wolrd!');
6
7ENDS;
其中Dbms_output為oracke內(nèi)置程序包，相當(dāng)于Java中的System.out,而PUT_LINE（）是調(diào)用的方法，相當(dāng)于println（）方法
在sqlplus中也可以編寫運行PLSQL程序：
1SQL>BEGIN
22
33--打印hello world
44
55Dbms_output.put_line('hello world');
66
77END;
88/
9
10PL/SQL? 過程已成功完成。
執(zhí)行結(jié)束后并未顯示輸出的結(jié)果，默認情況下，輸出選項是關(guān)閉狀態(tài)的 我們需要開啟一下 set serveroutput on
SQLPLUS中執(zhí)行PLSQL程序需要在程序最后添加一個 / 標識程序的結(jié)束
1.4.變量
PLSQL編程中常見的變量分兩大類：
普通數(shù)據(jù)類型（char,varchar2,date,number,boolean,long)
特殊變量類型（引用型變量、記錄型變量）
聲明能量的方式為
1變量名 變量類型（變量長度）? ? 例如：v_namevarchar2(20);
1.4.1.普通變量
變量賦值的方式有兩種：
直接賦值語句? :=? 比如：v_name := 'zhangsan'
語句賦值，使用select...into...賦值：（語法 select 值 into 變量）
【示例】打印人員個人信息，包括? ? 姓名、薪水、地址
SQL>-- 打印人員個人信息，包括：姓名、薪水、地址
SQL>DECLARE
2-- 姓名
3v_namevarchar2(20) :='張三';
4-- 薪水
5v_salNUMBER;
6-- 地址
7v_addrVARCHAR(200);
8BEGIN
9-- 直接賦值
10v_sal :=580;
11-- 語句賦值
12SELECT'上海'intov_addrfromdual;
13Dbms_output.put_line('姓名：'||v_name||',薪水:'||v_sal||',地址:'||v_addr);
14end;
15/
姓名：張三,薪水:580,地址:上海
?
PL/SQL 過程已成功完成。
1.4..2.引用型變量
變量的類型和長度取決于表中字段的類型和長度
通過表名.列名%TYPE指定變量的類型和長度，例如：v_name emp.ename%TYPE;
【示例】查詢emp表中7839號員工的個人信息，打印姓名和薪水
1 DECLARE--查詢emp表中comm=1400.00的員工的個人信息，打印姓名和薪水
2--姓名
3V_NAME emp.ename%TYPE:='張三';--聲明變量直接賦值
4V_SAL emp.sal%TYPE;-- 薪水
5BEGIN
6selectename,salintov_name,v_salfromempwherecomm=1400.00;--查詢表中的姓名和薪水并賦值給變量
7Dbms_Output.put_line('姓名'||v_name||'薪水'||v_sal);-- 注意查詢的字段和賦值的變量的順序、個數(shù)、類型要一致 -- 打印變量
8end;
9/
引用型變量的好處：
使用普通變量定義方式，需要知道表中列的類型，而使用引用類型，不需要考慮列的類型，使用%TyPE是非常好的編程風(fēng)格，因為他使得PL/SQL更加靈活，更加適應(yīng)于對數(shù)據(jù)庫定義的更新。
1.4.3.記錄型變量
接受表中的一整行記錄，相當(dāng)于Java中的一個對象
語法：變量名稱? ? 表名%ROWTYPE，例如：v_emp emp%ROWTYPE;
【示例】
查詢并打印comm是1400.00的員工的姓名和薪水
1-- 查詢emp表中comm是1400.00的員工的個人信息，打印姓名和薪水
2declare
3-- 記錄型變量接受一行
4V_EMP emp%ROWTYPE;
5begin
6-- 記錄變量默認接受表中的一行數(shù)據(jù)，不能指定字段。
7select*intov_empfromempwherecomm=1400.00;
8-- 打印變量
9Dbms_Output.put_line('姓名：'||v_emp.ename||'薪水：'||v_emp.sal);
10
11end;
如果有一個表，有100個字段，那么你程序如果要使用這100個字段話，如果你使用引用型變量一個個聲明，會特別麻煩，記錄型變量可以方便的解決這個問題。
錯誤的使用：
記錄型變量只能存儲一個完整的行數(shù)據(jù)
2.返回的行太多了，記錄型變量也接受不了
1.5.流程控制
1.5.1.條件分支
語法：
1 begin
2
3
4IF條件1THEN執(zhí)行1
5
6ELSIF條件2THEN執(zhí)行2
7
8ELSE執(zhí)行3
9
10ENDIF；
11
12end;
注意關(guān)鍵字：ELSIF
【示例】判斷emp表中記錄是否超過20條，10~20之間，或者10條以下
1DECLARE
2-- 聲明變量接受emp表中的記錄數(shù)
3V_COUNTNUMBER;
4begin
5
6-- 查詢emp表中的記錄數(shù)賦值給變量
7
8SELECT? ? ?COUNT(1)? ? ?INTO? ? ?V_COUNT? ? ? FROM? ? ?EMP;
9
10-- 判斷打印
11
12IFV_COUNT >20THEN
13dbms_output.put_line('EMP表中的記錄數(shù)超過了20條為：'||V_COUNT||'條。');
14
15ELSIFV_COUNT >=10THEN
16dbms_output.put_line('EMP表中的記錄數(shù)在10~20條之間為：'||V_COUNT||'條。');
17
18ELSE
19dbms_output.put_line('EMP表中的記錄數(shù)10條以下為：'||V_COUNT||'條。');
20ENDIF;
21end;
1.5.2.循環(huán)
在ORACLE中有三種循環(huán)方式，這里我們不再展開，只介紹其中一種：loop循環(huán)
語法：
BEGIN
? LOOP
? EXIT WHEN 退出循環(huán)條件
? END LOOP;
END;
【示例】打印數(shù)字1-10
1declare
2-- 聲明循環(huán)變量
3v_numNUMBER:=1;
4begin
5LOOP
6exitwhenv_num >10;
7dbms_output.put_line(v_num);
8-- 循環(huán)變量的自增
9v_num := v_num +1;
10ENDLOOP;
11end;
2.游標
2.1.什么是游標
用于臨時存儲一個查詢返回的多行數(shù)據(jù)（結(jié)果集，類似于Java的Jdbc連接返回的ResultSet集合），通過遍歷游標，可以逐行訪問處理該結(jié)果集的數(shù)據(jù)。
游標的使用方式：聲明--->打開--->讀取--->關(guān)閉
2.2.語法
游標聲明：
CURSOR 游標名[(參數(shù)列表)] IS 查詢語句;
游標的打開：
OPEN 游標名；
游標的取值；
FETCH 游標名 INTO 變量列表；
游標的關(guān)閉；
CLOSE 游標名；
2.3.游標的屬性
游標的屬性返回值類型說明
%ROWCOUNT-----------整型----------獲得FETCH語句返回的數(shù)據(jù)整行
%FOUND------------布爾型---------------最近的FETCH語句返回一行數(shù)據(jù)則為真，否則為假
%NOTFOUND--------------布爾型-----------與%FOUND屬性返回值相反
%ISOPEN----------------布爾值-------------游標已經(jīng)打開時值為真，否則為假
其中%NOTFOUND是在游標中找不到元素的時候返回TRUE，通常用來判斷退出循環(huán)。
2.4.創(chuàng)建和使用
【示例】使用游標查詢emp表中所有員工的姓名和工資，并將其依次打印出來。
-- 使用游標查詢emp表中所有員工的姓名和工資，并將其依次打印出來。
DECLARE
-- 聲明游標
CURSORC_EMPISSELECTENAME,SALFROMEMP;
-- 聲明變量接受游標中的數(shù)據(jù)
V_ENAME EMP.ENAME%TYPE;
V_SAL EMP.SAL%TYPE;
BEGIN
-- 打開游標
OPENC_EMP;
-- 遍歷游標
LOOP
-- 獲取游標中的數(shù)據(jù)
FETCHC_EMPINTOV_ENAME,V_SAL;
-- 退出循環(huán)條件
EXITWHENC_EMP%NOTFOUND;
Dbms_Output.put_line('姓名：'||V_ENAME||'薪資：'||V_SAL);
ENDLOOP;
-- 關(guān)閉游標
CLOSEC_EMP;
END;
執(zhí)行結(jié)果：
2.5.帶參數(shù)的游標
【示例】使用游標查詢并打印出某部門的員工的姓名和薪資，部門編號為運行時手動輸入。
1declare-- 使用游標查詢并打印某部門的員工的姓名和薪資，部門編號為運行時手動輸入。
2-- 聲明游標傳遞參數(shù)
3CURSORC_EMP(V_EMPNO EMP.EMPNO%TYPE)IS
4SELECTENAME, SALFROMEMPWHEREEMPNO = V_EMPNO;
5
6-- 聲明變量用來接受游標中的元素
7V_ENAME EMP.ENAME%TYPE;
8
9
10V_SAL EMP.SAL%TYPE;
11
12
13begin
14
15
16-- 打開游標并傳遞參數(shù)
17OPENC_EMP(7839);
18
19-- 遍歷游標中的值
20LOOP
21
22-- 通過FETCH語句獲取游標中的值并賦值給變量
23FETCHC_EMPINTOV_ENAME, V_SAL;
24
25EXITWHENC_EMP%NOTFOUND;
26DBMS_OUTPUT.PUT_LINE('姓名：'||V_ENAME||'薪水'||V_SAL);
27
28ENDLOOP;
29
30end;
執(zhí)行結(jié)果：
注意：%NOTFOUND屬性默認值為FALSE，所以在循環(huán)中要注意判斷條件的位置，如果先判斷在FETCH會導(dǎo)致最后一條記錄的值被打印多次（多循環(huán)一次默認）；
3.存儲過程
3.1.概念作用
之前我們編寫的PLSQL程序可以進行表的操作、判斷、循環(huán)邏輯處理的工作，但無法重復(fù)調(diào)用?？梢岳斫庵暗拇a全部編寫在了main方法中，是匿名程序，JAVA可以通過封裝對象和方法來解決復(fù)用問題。PLSQL是將一個個PLSQL的業(yè)務(wù)處理過程存儲起來進行復(fù)用，這些被存儲起來的PLSQL程序稱之為存儲過程
存儲過程作用：
在開發(fā)程序中，為了一個特定的業(yè)務(wù)共嫩南瓜，會向數(shù)據(jù)庫進行多次連接關(guān)閉（連接和關(guān)閉時很耗費資源），需要對數(shù)據(jù)庫進行多次I/O讀寫，性能比較低。如果把這些業(yè)務(wù)放到PLSQL中，在應(yīng)用程序中只需要調(diào)用PLSQL就可以做到連接關(guān)閉一次數(shù)據(jù)庫就可以實現(xiàn)我們的業(yè)務(wù)，可以大大提高效率。
ORACLE官方給的建議：能夠讓數(shù)據(jù)庫操作的不要放在程序中。在數(shù)據(jù)庫中實現(xiàn)基本上不會出現(xiàn)錯誤，在程序中操作可能會存在錯誤。（如果在數(shù)據(jù)庫中操作數(shù)據(jù)，可以有一定的日志恢復(fù)等功能。）
3.2.語法
-- 這部分的as可以換成is，效果一樣
create? ? procedure? ? ?過程名? ? ? as
-- 沒有使用declare聲明變量，但是可以在begin上邊直接聲明變量
begin
-- 執(zhí)行部分
end[過程名];
根據(jù)參數(shù)的類型，我們將其分為3類講解：
|不帶參數(shù)
|帶輸入?yún)?shù)的
|但輸入輸出參數(shù)（返回值）的。
3.3.無參函數(shù)
3.3.1.創(chuàng)建存儲
通過Plsql Developer或者語句創(chuàng)建存儲過程：
【示例】通過調(diào)用存儲過程打印hello world
創(chuàng)建存儲過程：
1create? ? or? ?replace? ? procedure? ? ? ? ?p_hello? ? ? ?is? ? ? ? ? ? -- 通過調(diào)用存儲過程打印hello world
2-- 聲明變量
3begin
4
5
6dbms_output.put_line('hello 你還 ? nihai world!');
7
8
9endp_hello;
通過工具查看創(chuàng)建好的存儲過程：
3.3.2調(diào)用存儲過程
通過PLSQL程序調(diào)用（在新建文件夾處，重新打開一個test window）：
1begin
2-- 直接輸入調(diào)用存儲過程的名稱
3p_hello;
4
5end;
提示：SQLPLUS中顯示結(jié)果的前提是需要set serveroutpput on
注意：
第一個問題：is和as是可以互用的，用哪個都沒關(guān)系。
第二個問題：過程中沒有declare關(guān)鍵字，declare用在語句塊中。
3.4.帶輸入?yún)?shù)的存儲過程
【示例】查詢并打印某個員工（如7839號員工）的姓名和薪水--存儲過程：要求，調(diào)用的時候傳入員工編號，自動控制臺打印。
1createorreplaceprocedurep_querynameandsal(I_EMPNOINEMP.EMPNO%TYPE)is-- 查詢并打印某個員工（如7839號員工）的姓名和薪水--存儲過程：要求，調(diào)用的時候傳入員工編號，自動控制臺打印。
2-- 聲明變量
3v_ename emp.ename%TYPE;
4v_sal emp.sal%TYPE;
5begin
6
7-- 查詢emp表中某個員工的姓名和薪水并復(fù)制給變量
8-- 根據(jù)用戶傳遞的員工號查詢姓名和薪水
9selectename, salintov_ename, v_salfromempwhereempno=I_EMPNO;
10-- 打印結(jié)果
11dbms_output.put_line('姓名：'||v_ename||'，薪水：'||v_sal);
12
13
14endp_querynameandsal;
命令調(diào)用：
1SQL>execp_querynameandsal(7654)
2姓名：MARTIN，薪水：1250
3
4PL/SQL 過程已成功完成。
PLSQL程序調(diào)用：
? 1? begin
? 2
? 3
? 4 ?? p_querynameandsal(7654);
? 5
? 6
? 7? end;
執(zhí)行結(jié)果：
3.5.帶輸出參數(shù)的存儲過程
【示例】輸入員工號查詢某個員工（7654號員工）信息，要求，將薪水作為返回值輸出，給調(diào)用的程序使用。
1createorreplaceprocedurep_querysal_out(I_EMPNOINEMP.EMPNO%TYPE, o_saloutemp.sal%TYPE)is-- 查詢并打印某個員工（如7839號員工）的姓名和薪水--存儲過程：要求，將薪水作為返回值輸出，給調(diào)用的程序使用。
2
3begin
4
5
6selectsalintoo_salfromempwhereempno=I_EMPNO;
7
8
9endp_querysal_out;
PLSQL調(diào)用：
1declare
2-- 聲明變量接受存儲過程中的輸出參數(shù)
3v_sal emp.sal%TYPE;
4begin
5
6p_querysal_out(7654, v_sal);-- 注意參數(shù)的順序
7
8-- 打印返回值
9dbms_output.put_line('返回值為：'||v_sal);
10
11end;
注意：調(diào)用的時候，參數(shù)要與定義的參數(shù)的順序和類型一致。
3.7.JAVA程序調(diào)用存儲過程
需求：如果一條語句無法實現(xiàn)結(jié)果集，比如需要多表查詢，或者需要復(fù)雜邏輯查詢，我們可以選擇調(diào)用存儲查詢出你的結(jié)果。
