DECLARE
qty_on_hand NUMBER(5);
BEGIN
SELECT quantity INTO qty_on_hand
FROM Products
WHERE product = '玩具熊';
IF qty_on_hand > 0 THEN
UPDATE Products SET quantity = quantity + 1
WHERE product = '玩具熊';
INSERT INTO purchase_record
VALUES ('已購買玩具熊', SYSDATE);
END IF;
COMMIT;
EXCEPTION /* 異常處理語句 */
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('出錯:'|| SQLERRM);
END;
3、PL/SQL變量
聲明變量文法：
-- 聲明變量文法結構
DECLARE variable_name [CONSTANT] type [NOT NULL] [:=value];
說明：
使用 DECLARE 關鍵字進行聲明
variable_name： 表示變量名稱
CONSTANT：表示是否是常量
type：表示變量的資料類型
NOT NULL ：是否為空
:=value
：初始化變量
[ ]方括号表示可選項
變量命名規則：
變量名首字母必須是英文字母
其後可以是字母、數字或者特殊符号$、#和下劃線
變量名長度不超過30個字元
變量名中不能有空格
不能是SQL保留字
Oracle資料庫-03 PL/SQL程式設計、遊标、存儲過程
PL/SQL支援的内置資料類型如圖：
Oracle資料庫-03 PL/SQL程式設計、遊标、存儲過程
4、表達式和運算符
（1）表達式的分類
數值型
字元型
日期型
布爾型
（2）運算符分類
算術運算符
關系運算符
邏輯運算符
其他運算符
5、變量的指派及注釋的使用
/*
|| 在注釋開始的首行隻放斜線星号，标志注釋開始，
|| 然後注釋塊的每一行以雙垂直線開頭，突出注釋後面
|| 的内容是注釋部分，最後，将星号斜線單置于一行
*/
DECLARE
v_ename VARCHAR2(20);
v_rate NUMBER(7,2);
c_rate_incr CONSTANT NUMBER(7,2):=1.10;
BEGIN
--方法一：通過SELECT INTO給變量指派
SELECT ename, sal* c_rate_incr
INTO v_ename, v_rate
FROM employee
WHERE empno='7788';
--方法二：通過指派操作符“:=”給變量指派
v_ename:='SCOTT';
END;
6、控制語句
（1）條件控制
IF語句
-- 例1
DECLARE
v_str varchar2(10):='b';
BEGIN
IF(v_str='a') THEN dbms_output.put_line('if');
ELSIF(v_str='b') THEN dbms_output.put_line('elsif');
ELSE dbms_output.put_line('else');
END IF;
END;
-- 例2
DECLARE
i constant number:=5;
BEGIN
IF(i=4) THEN null; --如果什麼都不做的話，那麼補null來保證語句的完整性
ELSIF(i=4) THEN dbms_output.put_line(i);
ELSE dbms_output.put_line('else');
END IF;
END;
-- 例3：驗證如果不給字元變量指派時預設是空字元串還是為空
DECLARE
i varchar2(10); --字元類型
BEGIN
IF(i='') THEN dbms_output.put_line('空字元串');
ELSIF(i is null) THEN dbms_output.put_line('null');
ELSIF(i='a') THEN dbms_output.put_line('a');
ELSE dbms_output.put_line('b');
END IF;
END;
-- 例4
DECLARE
i number; -- 數值類型
BEGIN
IF(i='') THEN dbms_output.put_line('空字元串');
ELSIF(i is null) THEN dbms_output.put_line('null');
ELSIF(i='a') THEN dbms_output.put_line('a');
ELSE dbms_output.put_line('b');
END IF;
END;
CASE語句
-- 例1：CASE語句
--方法一
DECLARE
grade char:='A';
remark varchar2(20);
BEGIN
CASE grade
WHEN 'A' THEN remark:='is Excellent';
WHEN 'B' THEN remark:='is Good';
WHEN 'C' THEN remark:='is Normal';
WHEN 'D' THEN remark:='is Bad';
ELSE remark:='big Problem';
END CASE;
dbms_output.put_line(remark);
END;
--例2：CASE語句
--方法二
DECLARE
grade NUMBER:=70;
remark varchar2(20);
BEGIN
CASE
WHEN grade=100 THEN remark:='is Excellent';
WHEN grade>=80 THEN remark:='is Good';
WHEN grade>=60 THEN remark:='is Normal';
WHEN grade>=50 THEN remark:='is Bad';
ELSE remark:='big Problem';
END CASE;
dbms_output.put_line(remark);
END;
（2）循環控制
LOOP循環
- 例1：簡單循環loop
declare
i number:=0;
begin
loop
i:=i+1; --循環為i+1
dbms_output.put_line('i='||i); --輸出i的值
if(i=30) then exit; --i的值到30，則退出
end if;
end loop;
end;
-- 例2：簡單循環loop
declare
i number:=0;
begin
loop
i:=i+1;
dbms_output.put_line('i='||i);
exit when (i=30); --使用when關鍵字
end loop;
end;
-- 例3：嵌套循環
declare
i number:=0;
b number:=0;
begin
loop
i:=i+1;
dbms_output.put_line('i>>>>>>='||i);
exit when i=5;
b:=0;
--嵌套循環
loop
b:=b+1;
dbms_output.put_line('b='||b);
exit when b=2;
end loop;
end loop;
end;
WHILE循環
-- while循環
declare
a constant number:=1;
b constant number:=10;
i number:=0;
begin
while (b>a) loop
i:=i+1;
dbms_output.put_line('i='||i);
exit when i=5;
end loop;
end;
FOR 循環
-- 例1：for循環
declare
a constant number:=4;
b constant number:=10;
begin
for i in a ..b loop
dbms_output.put_line('i：'||i);
end loop;
end;
-- 例2：for循環
declare
a constant number:=1;
b constant number:=10;
begin
for i in reverse a ..b loop --使用reverse關鍵字實作倒序周遊
dbms_output.put_line('i+>>>>>'||i);
end loop;
end;
--預定義異常
BEGIN
--過程及SQL語句;
EXCEPTION
WHEN --異常名稱THEN
-- 過程及SQL語句;
WHEN OTHERS THEN
--過程及SQL語句;
END;
預定義異常分類如圖：
Oracle資料庫-03 PL/SQL程式設計、遊标、存儲過程
預定義異常範例：
declare
v_ename char(1);
begin
select ename into v_ename
from emp
where empno=7788;
dbms_output.put_line('員工姓名為：'||v_ename);
Exception
when no_data_found then --處理沒有資料的異常
dbms_output.put_line('沒有該員工');
when value_error then
dbms_output.put_line('該員工名稱過長');
when others then
dbms_output.put_line('異常編碼：'||sqlcode||'，異常資訊：'||sqlerrm);
end;
PL/SQL中的使用者自定義異常處理：
使用 RAISE 關鍵字引發異常
使用 EXCEPTION 關鍵字處理異常
Oracle資料庫-03 PL/SQL程式設計、遊标、存儲過程
使用者自定義異常範例：
-- 示範自定義異常
declare
v_eno Scott.emp.empno%type:=7839;
v_deptno Scott.emp.deptno%type;
v_sal Scott.emp.sal%type;
e_money_big exception;
begin
--根據員工編号查詢員工所屬部門和工資
select deptno,sal into v_deptno,v_sal
from Scott.emp
where empno=v_eno;
--判斷，如果部門編号為10
if v_deptno=10 then
--判斷工資是否低于10000
if v_sal<10000 then
update Scott.emp set sal=10000 where empno=v_eno;
commit;
else
raise e_money_big;
end if;
end if;
--異常處理
exception
when e_money_big then
dbms_output.put_line('工資不低于10000元');
when others then
dbms_output.put_line('異常編碼：'||sqlcode||'，異常資訊：'||sqlerrm);
end;
declare
--聲明遊标 查詢所有員工，将結果集存放到cursor_emp變量中
cursor cursor_emp is select * from Scott.emp;
--定義變量，類型為cursor_emp遊标中的資料的行類型
v_emp cursor_emp%rowtype;
begin
--判斷遊标是否打開，如果沒有則打開
if cursor_emp%isopen=false then
open cursor_emp; --打開遊标
end if;
--提取遊标中的資料
loop
--逐行提取遊标中每行的資料到v_emp變量中
fetch cursor_emp into v_emp;
--判斷如果沒有資料則退出
exit when cursor_emp%notfound;
--輸出目前資料
dbms_output.put_line('員工編号：'||v_emp.empno||'員工姓名：'||v_emp.ename||'工資：'||v_emp.sal);
end loop;
--關閉遊标
close cursor_emp;
end;
（5）允許使用遊标删除或更新活動集中的行
聲明遊标時必須使用select …for update語句
CURSOR <cursor_name> IS SELECT statement FOR UPDATE;
使用以下文法更新行
UPDATE <table_name> SET <set_clause> WHERE CURRENT OF <cursor_name>
使用以下文法删除行
DELETE FROM <table_name> WHERE CURRENT OF <cursor_name>
遊标範例： 使用foreach提取遊标的資料到變量中 自動打開關閉遊标
declare
--聲明遊标
cursor cursor_emp1
is select * from Scott.emp for update;
begin
--提取遊标中的資料
for v_emp in cursor_emp1 loop
delete Scott.emp where current of cursor_emp1;
--輸出目前資料
dbms_output.put_line('員工編号：'||v_emp.empno||'員工姓名：'||v_emp.ename||'工資：'||v_emp.sal);
end loop;
end;
-- 例1：建立一個根據empno查詢指定使用者資訊的存儲過程
create or replace procedure pro_getEmpByNo(
v_empno number,
v_emp out scott.emp%rowtype
)
as
begin
select * into v_emp from scott.emp where empno=v_empno;
end;
-- 例2：建立擷取所有員工的薪水的存儲過程
create or replace procedure get_sals(
--存放所有員工薪水的遊标；注意此處使用OUT關鍵字，該參數作為輸出參數
cur_salary OUT SYS_REFCURSOR,
on_Flag OUT number, --執行狀态
os_Msg OUT VARCHAR2 --提示資訊
)
as
begin
open cur_salary for
select empno,sal from Scott.emp;
on_Flag:=1;
os_Msg:='成功';
exception
when others then
on_Flag:=-1;
os_Msg:='其他錯誤，與管理者聯系。';
end;
（2）調用存儲過程
文法：
EXEC[UTE] procedure_name (param_list);
說明：
使用EXEC或 EXECUTE關鍵字調用
procedure_name ：表示調用的存儲過程的名稱
param_list：參數清單
執行存儲過程範例：
-- 調用例2中的get_sals存儲過程
declare
v_empno scott.emp.empno%type;
v_sal scott.emp.sal%type;
emp_salary1 SYS_REFCURSOR;
on_Flag1 number(1); --執行狀态
os_Msg1 VARCHAR2(200); --提示資訊
begin
--執行存儲過程
get_sals(emp_salary1,on_Flag1,os_Msg1);
if on_flag=1 then
loop --由于存儲過程中已經打開遊标，是以不可以使用foreach
fetch emp_salary into v_empno,v_sal;
exit when emp_salary%notfound;
dbms_output.put_line(v_empno||'的薪水是' ||v_sal);
end loop;
else
dbms_output.put_line(os_Msg);
end if;
if emp_salary%ISOPEN THEN
close emp_salary;
end if;
end;
補充：參數傳遞的三種方式
按位置傳遞參數
-- 按位置傳遞參數
declare
v_emp scott.emp%rowtype;
begin
pro_showAllEmp(7788,v_emp);
dbms_output.put_line(v_emp.ename);
end;
-- 将權限授予指定使用者
SQL> GRANT EXECUTE ON find_emp TO MARTIN;
--将權限授予所有使用者
SQL> GRANT EXECUTE ON swap TO PUBLIC;
--撤銷權限
revoke execute on 存儲過程名 to 使用者名/public;
