準(zhǔn)備數(shù)據(jù)
create table EMP
(
NO NUMBER,
NAME VARCHAR2(50),
SALARY NUMBER,
ADDRESS VARCHAR2(200)
);
insert into EMP
select 1, '德瑪西亞', 1580, '上海市寶山區(qū)'
from dual;
insert into EMP
select 2, '諾克薩斯', 2580, '上海市徐匯區(qū)'
from dual;
insert into EMP
select 3, '艾歐尼亞', 3580, '上海市虹口區(qū)'
from dual;
insert into EMP
select 4, '峽谷之巔', 4580, '上海市閔行區(qū)'
from dual;
insert into EMP
select 5, '黑色玫瑰', 5580, '上海市靜安區(qū)'
from dual;
PLSQL編程
概念
PLSQL是Oracle對(duì)sql語(yǔ)言的過(guò)程化擴(kuò)展，指在SQL命令語(yǔ)言中增加了過(guò)程處理語(yǔ)句（如分支、循環(huán)等），使SQL語(yǔ)言具有過(guò)程處理能力。
程序結(jié)構(gòu)
PL/SQL可以分為三個(gè)部分：聲明部分、可執(zhí)行部分、異常處理部分。
其中DECLARE部分用來(lái)聲明變量或游標(biāo)（結(jié)果集類(lèi)型變量），如果程序中無(wú)變量聲明可以省略掉。
DECLARE
-- 聲明變量、游標(biāo)。
I INTEGER;
BEGIN
-- 執(zhí)行語(yǔ)句
-- 異常處理
END;
運(yùn)行程序
在DataGrip中運(yùn)行
DBMS_OUTPUT為Oracle內(nèi)置程序包
BEGIN
-- 打印hello world
DBMS_OUTPUT.PUT_LINE('hello world');
END;
DataGrip設(shè)置控制臺(tái)輸出打印內(nèi)容
雙擊Shift搜索“dbms”，將開(kāi)關(guān)打開(kāi)
在sqlplus中運(yùn)行
在sqlplus中執(zhí)行PLSQL程序需要在程序最后添加一個(gè)‘/’ 符號(hào)，以標(biāo)識(shí)程序的結(jié)束。
sqlplus # 使用賬號(hào)密碼登錄
BEGIN
-- 打印hello world
DBMS_OUTPUT.PUT_LINE('hello world');
END;
/
sqlplus設(shè)置控制臺(tái)輸出打印內(nèi)容
執(zhí)行結(jié)束后并未顯示輸出的結(jié)果，默認(rèn)情況下，輸出選項(xiàng)是關(guān)閉狀態(tài)的，我們需要開(kāi)啟一下。
set serveroutput on
變量
- 普通數(shù)據(jù)類(lèi)型（char,varchar2, date, number, boolean, long）
- 特殊變量類(lèi)型（引用型變量、記錄型變量）
聲明變量的方式為
變量名 變量類(lèi)型（變量長(zhǎng)度）
例如：v_name varchar2(20);
普通變量
- 直接賦值語(yǔ)句，使用:=賦值
變量 := 值
例如：v_name := ‘honey’
- 語(yǔ)句賦值，使用select … into … 賦值
select 值 into 變量
例如：select ‘honey’ into v_name
-- 打印人員個(gè)人信息，包括：姓名、薪水、地址
DECLARE
-- 姓名
V_NAME VARCHAR2(20) := 'honey'; -- 聲明變量直接賦值
-- 薪水
V_SALARY NUMBER;
-- 地址
V_ADDRESS VARCHAR2(200);
BEGIN
-- 在程序中直接賦值
V_SALARY := 1580;
-- 語(yǔ)句賦值
SELECT '上海市徐匯區(qū)' INTO V_ADDRESS FROM DUAL;
-- 打印變量
DBMS_OUTPUT.PUT_LINE('姓名：' || V_NAME || '，薪水：' || V_SALARY || '，地址：' || V_ADDRESS);
END;
引用型變量
變量的類(lèi)型和長(zhǎng)度取決于表中字段的類(lèi)型和長(zhǎng)度
通過(guò)表名.列名%TYPE指定變量的類(lèi)型和長(zhǎng)度
例如：v_name emp.ename%TYPE
-- 查詢(xún)emp表中2號(hào)員工的個(gè)人信息，并打印姓名和薪水
DECLARE
-- 姓名
V_NAME EMP.NAME%TYPE;
-- 薪水
V_SALARY EMP.SALARY%TYPE;
BEGIN
-- 查詢(xún)表中的姓名和薪水并賦值給變量
SELECT NAME, SALARY INTO V_NAME, V_SALARY FROM EMP WHERE NO = 2;
-- 打印變量
DBMS_OUTPUT.PUT_LINE('姓名：' || V_NAME || '，薪水：' || V_SALARY);
END;
引用型變量的好處：
使用普通變量定義方式，需要知道表中列的類(lèi)型，而使用引用類(lèi)型，不需要考慮列的類(lèi)型，使用%TYPE是非常好的編程風(fēng)格，因?yàn)樗沟肞L/SQL更加靈活，更加適應(yīng)于對(duì)數(shù)據(jù)庫(kù)定義的更新。
記錄型變量
接收表中的一整行記錄，相當(dāng)于Java中的一個(gè)對(duì)象。
變量名 表名%ROWTYPE
例如：v_emp emp%rowtype;
-- 查詢(xún)emp表中3號(hào)員工的個(gè)人信息，并打印姓名和薪水
DECLARE
-- 記錄型變量
V_EMP EMP%ROWTYPE;
BEGIN
-- 記錄型變量默認(rèn)接收表中的一行數(shù)據(jù)，不能指定字段。
SELECT * INTO V_EMP FROM EMP WHERE NO = 3;
-- 打印變量，通過(guò)變量名.屬性的方式獲取變量中的值
DBMS_OUTPUT.PUT_LINE('姓名：' || V_EMP.NAME || '，薪水：' || V_EMP.SALARY);
END;
流程控制
條件分支
BEGIN
IF 條件1 THEN 執(zhí)行1
ELSIF 條件2 THEN 執(zhí)行2
ELSE 執(zhí)行3
END IF;
END;
-- 判斷emp表中的記錄數(shù)所在的數(shù)值范圍
DECLARE
-- emp表中的記錄數(shù)
V_COUNT NUMBER;
BEGIN
-- 查詢(xún)emp表中的記錄數(shù)賦值給變量
SELECT COUNT(1) INTO V_COUNT FROM EMP;
-- 判斷打印
IF V_COUNT > 20 THEN
DBMS_OUTPUT.PUT_LINE('EMP表中的記錄數(shù)超過(guò)了20條為：' || V_COUNT || '條。');
ELSIF V_COUNT >= 10 THEN
DBMS_OUTPUT.PUT_LINE('EMP表中的記錄數(shù)在10~20條之間為：' || V_COUNT || '條。');
ELSE
DBMS_OUTPUT.PUT_LINE('EMP表中的記錄數(shù)在10條以下為：' || V_COUNT || '條。');
END IF;
END;
循環(huán)
BEGIN
LOOP
EXIT WHEN 退出循環(huán)條件
END LOOP;
END;
-- 打印1-10
DECLARE
-- 聲明循環(huán)變量并賦初值
V_NUM NUMBER := 1;
BEGIN
LOOP
EXIT WHEN V_NUM > 10;
DBMS_OUTPUT.PUT_LINE(V_NUM);
-- 循環(huán)變量自增
V_NUM := V_NUM + 1;
END LOOP;
END;
游標(biāo)
概念
用于臨時(shí)存儲(chǔ)一個(gè)查詢(xún)返回的多行數(shù)據(jù)（結(jié)果集），通過(guò)遍歷游標(biāo)，可以逐行訪問(wèn)處理該結(jié)果集的數(shù)據(jù)。
游標(biāo)的使用方式：聲明—>打開(kāi)—>讀取—>關(guān)閉
語(yǔ)法
游標(biāo)的聲明：
CURSOR 游標(biāo)名[(參數(shù)列表)] IS 查詢(xún)語(yǔ)句;
游標(biāo)的打開(kāi)：
OPEN 游標(biāo)名;
游標(biāo)的取值：
FETCH 游標(biāo)名 INTO 變量列表;
游標(biāo)的關(guān)閉：
CLOSE 游標(biāo)名;
游標(biāo)的屬性
| 游標(biāo)的屬性 | 返回值類(lèi)型 | 說(shuō)明 |
|---|---|---|
| %ROWCOUNT | 整型 | 獲得FETCH語(yǔ)句返回的數(shù)據(jù)行數(shù) |
| %FOUND | 布爾型 | 最近的FETCH語(yǔ)句返回一行數(shù)據(jù)則為真，否則為假 |
| %NOTFOUND | 布爾型 | 與%FOUND屬性返回值相反 |
| %ISOPEN | 布爾型 | 游標(biāo)已經(jīng)打開(kāi)時(shí)值為真，否則為假 |
其中%NOTFOUND是在游標(biāo)中找不到元素的時(shí)候返回TRUE，通常用來(lái)判斷退出循環(huán)。
創(chuàng)建與使用
-- 使用游標(biāo)查詢(xún)emp表中所有員工的姓名和工資，并將其依次打印出來(lái)
DECLARE
-- 聲明游標(biāo)
CURSOR C_EMP IS
SELECT NAME, SALARY
FROM EMP;
-- 聲明變量用來(lái)接收游標(biāo)中的元素
V_NAME EMP.NAME%TYPE;
V_SALARY EMP.SALARY%TYPE;
BEGIN
-- 打開(kāi)游標(biāo)
OPEN C_EMP;
-- 遍歷游標(biāo)中的值
LOOP
-- 通過(guò)FETCH語(yǔ)句獲取游標(biāo)中的值并賦值給變量
FETCH C_EMP
INTO V_NAME, V_SALARY;
-- 判斷是否有值，有值打印，沒(méi)有則退出循環(huán)
EXIT WHEN C_EMP%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('姓名：' || V_NAME || '，薪水：' || V_SALARY);
END LOOP;
-- 關(guān)閉游標(biāo)
CLOSE C_EMP;
END;
帶參數(shù)的游標(biāo)
-- 使用游標(biāo)查詢(xún)并打印4號(hào)員工的姓名和薪資
DECLARE
-- 聲明游標(biāo)傳遞參數(shù)
CURSOR C_EMP(V_NO EMP.NO%TYPE) IS
SELECT NAME, SALARY
FROM EMP
WHERE NO = V_NO;
-- 聲明變量用來(lái)接收游標(biāo)中的元素
V_NAME EMP.NAME%TYPE;
V_SALARY EMP.SALARY%TYPE;
BEGIN
-- 打開(kāi)游標(biāo)并傳遞參數(shù)
OPEN C_EMP(4);
-- 遍歷游標(biāo)中的值
LOOP
-- 通過(guò)FETCH語(yǔ)句獲取游標(biāo)中的值并賦值給變量
FETCH C_EMP
INTO V_NAME, V_SALARY;
-- 判斷是否有值，有值打印，沒(méi)有則退出循環(huán)
EXIT WHEN C_EMP%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('姓名：' || V_NAME || '，薪水：' || V_SALARY);
END LOOP;
-- 關(guān)閉游標(biāo)
CLOSE C_EMP;
END;
存儲(chǔ)過(guò)程
概念
上述PLSQL程序可以進(jìn)行表的操作、判斷、循環(huán)邏輯處理的工作，但無(wú)法重復(fù)調(diào)用。
將一個(gè)個(gè)PLSQL的業(yè)務(wù)處理過(guò)程存儲(chǔ)起來(lái)進(jìn)行復(fù)用，這些被存儲(chǔ)起來(lái)的PLSQL程序稱(chēng)之為存儲(chǔ)過(guò)程。
作用
- 在開(kāi)發(fā)程序中，為了一個(gè)特定的業(yè)務(wù)功能，會(huì)向數(shù)據(jù)庫(kù)進(jìn)行多次連接關(guān)閉（連接和關(guān)閉很耗費(fèi)資源），需要對(duì)數(shù)據(jù)庫(kù)進(jìn)行多次I/O讀寫(xiě)，性能比較低。如果把這些業(yè)務(wù)放到PLSQL中，在應(yīng)用程序中只需要調(diào)用PLSQL就可以做到連接關(guān)閉一次數(shù)據(jù)庫(kù)就可以實(shí)現(xiàn)我們的業(yè)務(wù)，可以大大提高效率；
- ORACLE官方給的建議：能夠讓數(shù)據(jù)庫(kù)操作的不要放在程序中。在數(shù)據(jù)庫(kù)中實(shí)現(xiàn)基本上不會(huì)出現(xiàn)錯(cuò)誤，在程序中操作可能會(huì)存在錯(cuò)誤（如果在數(shù)據(jù)庫(kù)中操作數(shù)據(jù)，可以有一定的日志恢復(fù)等功能）
語(yǔ)法
CREATE OR REPLACE PROCEDURE 過(guò)程名稱(chēng)[(參數(shù)列表)] IS
BEGIN
END [過(guò)程名稱(chēng)];
無(wú)參存儲(chǔ)過(guò)程
創(chuàng)建
-- 通過(guò)調(diào)用存儲(chǔ)過(guò)程打印hello world
CREATE OR REPLACE PROCEDURE P_HELLO IS
BEGIN
DBMS_OUTPUT.PUT_LINE('hello world');
END P_HELLO;
調(diào)用
在DataGrip中調(diào)用
BEGIN
-- 直接輸入調(diào)用存儲(chǔ)過(guò)程的名稱(chēng)
P_HELLO;
END;
在sqlplus中調(diào)用
set serveroutput on
BEGIN
-- 直接輸入調(diào)用存儲(chǔ)過(guò)程的名稱(chēng)
P_HELLO;
END;
/
-- 使用EXEC命令調(diào)用
exec P_HELLO;
有輸入?yún)?shù)的存儲(chǔ)過(guò)程
-- 查詢(xún)并打印某個(gè)員工（如5號(hào)員工）的姓名和薪水
CREATE OR REPLACE PROCEDURE P_QUERY(I_NO IN EMP.NO%TYPE) IS
-- 聲明變量接收查詢(xún)結(jié)果
V_NAME EMP.NAME%TYPE;
V_SALARY EMP.SALARY%TYPE;
BEGIN
-- 根據(jù)用戶(hù)傳遞的員工號(hào)查詢(xún)姓名和薪水
SELECT NAME, SALARY INTO V_NAME, V_SALARY FROM EMP WHERE NO = I_NO;
-- 打印結(jié)果
DBMS_OUTPUT.PUT_LINE('姓名：' || V_NAME || '，薪水：' || V_SALARY);
END P_QUERY;
BEGIN
P_QUERY(5);
END;
有輸出參數(shù)的存儲(chǔ)過(guò)程
-- 查詢(xún)并打印某個(gè)員工（如5號(hào)員工）的姓名和薪水
CREATE OR REPLACE PROCEDURE P_QUERY_OUT(
I_NO IN EMP.NO%TYPE,
O_NAME OUT EMP.NAME%TYPE,
O_SALARY OUT EMP.SALARY%TYPE
) IS
BEGIN
SELECT NAME, SALARY INTO O_NAME, O_SALARY FROM EMP WHERE NO = I_NO;
END P_QUERY_OUT;
文章來(lái)源：http://www.zghlxwxcb.cn/news/detail-796919.html
DECLARE
--聲明一個(gè)變量接受存儲(chǔ)過(guò)程的輸出參數(shù)
V_NAME EMP.NAME%TYPE;
V_SALARY EMP.SALARY%TYPE;
BEGIN
P_QUERY_OUT(5, V_NAME, V_SALARY);
DBMS_OUTPUT.PUT_LINE('姓名：' || V_NAME || '，薪水：' || V_SALARY);
END;
文章來(lái)源地址http://www.zghlxwxcb.cn/news/detail-796919.html
到了這里，關(guān)于Mr. Cappuccino的第69杯咖啡——Oracle之存儲(chǔ)過(guò)程的文章就介紹完了。如果您還想了解更多內(nèi)容，請(qǐng)?jiān)谟疑辖撬阉鱐OY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！
