Oracle數據庫數據編程SQL＜3.1 PL/SQL 匿名塊及流程控制中的條件判斷、循環、異常處理和隨機函數應用＞

PL/SQL部分

在SQL的基礎上增加了一些過程化的控制語句。

過程化控制語句包括：類型定義、判斷、循環、游標、異常處理（例外處理）

PL/SQL匿名塊

一、匿名塊基本結構

1、匿名塊由三個部分組成：

2、注意事項：

二、不帶聲明的匿名塊示例

1. 簡單示例：打印

（1）點擊【執行】，然后查看【結果】

三、declare帶聲明部分的匿名塊

1、變量的聲明

2、變量的類型

（1）字符類型 char varchar/varchar2

（2）數值類型 number int

（3）日期類型 date

（4）布爾類型 boolean --值返回對錯空

（5）表名.列名%type--和表中的某一列類型相同

（6）表名%rowtype--和表的結構一致

（7）record--自定義列的類型

4、變量的賦值

（1）:=直接賦值

（2）變量的值可以賦給另一個變量

（3）變量可以二次賦值

（4）變量賦值可以用函數

（5）變量可以運算

（6）用鍵盤可以直接輸入賦值&

（7）用 select … into語句給變量賦值，值只返回一行，可以多列

（8）type 變量 is record();自定義變量

（9）returning…into 查詢DML的上一次操作賦值給變量

（10）變量聲明時可以聲明多個，只用其中的某幾個；

（11）變量聲明了，可以不用，但是使用的變量一定要聲明。

四、帶異常處理的匿名塊

五、匿名塊中begin部分的流程控制（條件判斷）

1. IF-THEN-ELSE 語句

（1）單分支

（2）雙分支

（3）多分支

（4）舉例

2、CASE-WHEN-THEN-ELSE語句

六、PL/SQL里的case when和SQL里的case when 的區別？

七、匿名塊中begin部分的循環（也屬于流程控制）

1、簡單循環（基本循環）

2、while循環

3、for循環

八、匿名塊中的異常處理

1. 預定義異常

2. 自定義異常

九、隨機函數的應用

1、dbms_random.value(小值，大值)

2、dbms_random.string(參數，長度)

3、在匿名塊中的應用

PL/SQL匿名塊

匿名塊是 Oracle PL/SQL 中最基本的代碼執行單元，它沒有名稱，不能被存儲或重復調用，通常用于一次性執行的任務或測試代碼片段。

一、匿名塊基本結構

1、匿名塊由三個部分組成：

[DECLARE-- 聲明部分（可選）變量、常量、游標、異常等的聲明]
BEGIN-- 執行部分（必需）PL/SQL 和 SQL 語句
[EXCEPTION-- 異常處理部分（可選）異常處理程序]
END;
/   命令窗口需要加 / 以示結束

2、注意事項：

（1）每一部分的語句都需要以；結尾，end后也必須加；

（2）習慣的加注釋

（3）賦值的時候加“:=”,判斷相等的時候“=”。

（4）命令窗口需要修改時，輸入ed或edit

（5）命令窗口開啟打印服務時輸入set serveroutput on

? ? ? ? ? ? ? ? ? ? ? ?關閉打印服務 set serveroutput off

declarev1 number;v2 number;
beginv1 := 1000;v2 := 2000;dbms_output.put_line(v1 || '+' || v2 || '=' || (v1 + v2));
end;

這里注意加號和等號的執行順序，加號是有執行優先級的，只有(v1+v2),這個語句才能正確執行

二、不帶聲明的匿名塊示例

1. 簡單示例：打印

BEGINDBMS_OUTPUT.PUT_LINE('Hello, 你是不是學廢了');--打印
END;
/dbms_output.put --不換行打印必須搭配換行打印使用***
dbms_output.put_line --換行打印

（1）點擊【執行】，然后查看【結果】

三、declare帶聲明部分的匿名塊

1、變量的聲明

變量名變量的類型長度 v_name varchar2（20）；

2、變量的類型

（1）字符類型 char varchar/varchar2

（2）數值類型 number int

（3）日期類型 date

DECLAREv_count     NUMBER := 0;v_name      VARCHAR2(100) := '張三';v_hiredate  DATE := SYSDATE;v_flag      BOOLEAN := TRUE;c_pi CONSTANT NUMBER := 3.14159;
BEGIN-- 執行代碼
END;
/

（4）布爾類型 boolean --值返回對錯空

（5）表名.列名%type--和表中的某一列類型相同

所引用的數據庫列的數據類型可以不必知道。
索引用的數據庫列的數據類型可以實時改變，即原表改變他會自動改變。
定義變量不能與表中列的名字相同

同一時間只能接受一個值

DECLAREv_emp_name employees.last_name%TYPE;v_emp_sal  employees.salary%TYPE;
BEGINSELECT last_name, salary INTO v_emp_name, v_emp_salFROM employeesWHERE employee_id = 100;DBMS_OUTPUT.PUT_LINE(v_emp_name || '的薪資是: ' || v_emp_sal);
END;
/

（6）表名%rowtype--和表的結構一致

DECLAREv_emp_record employees%ROWTYPE;
BEGINSELECT * INTO v_emp_recordFROM employeesWHERE employee_id = 100;DBMS_OUTPUT.PUT_LINE('員工信息: ' || v_emp_record.employee_id || ', ' ||v_emp_record.last_name || ', ' ||v_emp_record.salary);
END;
/

（7）record--自定義列的類型

4、變量的賦值

（1）:=直接賦值

declare
v_number number(4);
begin
v_number:=7788;
end;

（2）變量的值可以賦給另一個變量

declare
v_1 number(4):=7788;
v_2 number(4);
begin
v_2:=v_1;
end;

（3）變量可以二次賦值

declare
v1 number:=1;
begin
dbms_output.put_line('v1='||v1);--打印
v1:=2;
dbms_output.put_line('v1='||v1);
end;

（4）變量賦值可以用函數

declare
v1 varchar2(20):='SMITH';
begin
v1:=lower(v1);
dbms_output.put_line(v1);
end;

（5）變量可以運算

（6）用鍵盤可以直接輸入賦值&

declare
v1 number:=&abcd;
begin
dbms_output.put_line('v1='||v1);
end;

（7）用 select … into語句給變量賦值，值只返回一行，可以多列

declare
v_sal emp.sal%type;
begin
select sal into v_sal from emp where ename='SMITH';
dbms_output.put_line(v_sal);
end;

（8）type 變量 is record();自定義變量

declare
type t_emp is record(v_e emp.empno%type,v_h emp.hiredate%type,v_d emp.deptno%type);
b t_emp;
begin
select empno,hiredate,deptno into b
from emp where ename='KING';
dbms_output.put_line(b.v_e||chr(13)||b.v_h||chr(13)||b.v_d);
end;

（9）returning…into 查詢DML的上一次操作賦值給變量

declare
v_n emp1.ename%type;
v_sal emp1.sal%type;
begin
insert into emp1 (ename,sal) values('Eddie',10000)
returning ename,sal into v_n,v_sal;
dbms_output.put_line(v_n||','||v_sal);
end;

（10）變量聲明時可以聲明多個，只用其中的某幾個；

（11）變量聲明了，可以不用，但是使用的變量一定要聲明。

--注意切換到Hr用戶
DECLAREv_employee_name VARCHAR2(100);v_salary       NUMBER;
BEGINSELECT last_name, salary INTO v_employee_name, v_salaryFROM employeesWHERE employee_id = 100;DBMS_OUTPUT.PUT_LINE('員工姓名: ' || v_employee_name);DBMS_OUTPUT.PUT_LINE('員工薪資: ' || v_salary);
END;
/--注意切換到scott用戶
--刪除emp1的員工編號是 7782的員工，輸入【刪除】的ename和mgr
--更新emp1的名字是KING的員工，
--名字改成首字母大寫，輸入【更新】后的名字和工作
create table emp6 as select * from emp;
declarev_e   emp6.ename%type;v_mgr emp6.mgr%type;v_n   emp6.ename%type;v_job emp6.job%type;
begindelete from emp6 where empno = 7902; returning ename, mgr into v_e, v_mgr;update emp6set ename = lower(ename), job = initcap(job)where ename = 'KING'returning ename, job into v_n, v_job;dbms_output.put_line(v_e || '' || v_mgr || chr(13) || v_n || '' || v_job);
end;
/
select * from emp6

四、帶異常處理的匿名塊

DECLAREv_dept_name VARCHAR2(100);
BEGINSELECT department_name INTO v_dept_nameFROM departmentsWHERE department_id = 999;  -- 假設這個部門不存在DBMS_OUTPUT.PUT_LINE('部門名稱: ' || v_dept_name);
EXCEPTION --異常處理WHEN NO_DATA_FOUND THENDBMS_OUTPUT.PUT_LINE('錯誤: 未找到部門信息');WHEN OTHERS THENDBMS_OUTPUT.PUT_LINE('錯誤代碼: ' || SQLCODE);DBMS_OUTPUT.PUT_LINE('錯誤信息: ' || SQLERRM);
END;
/

五、匿名塊中begin部分的流程控制（條件判斷）

1. IF-THEN-ELSE 語句

（1）單分支

if 條件 then 要執行的語句 end if；

（2）雙分支

if 條件 then 要執行的語句 else 要執行的語句 end if；

（3）多分支

if 條件1 then 要執行的語句
elsif 條件2 then 要執行的語句
elsif 條件3 then 要執行的語句
...
else 要執行的語句
end if；

（4）舉例

DECLAREv_score NUMBER := 85;
BEGINIF v_score >= 90 THENDBMS_OUTPUT.PUT_LINE('優秀');ELSIF v_score >= 80 THENDBMS_OUTPUT.PUT_LINE('良好');ELSIF v_score >= 60 THENDBMS_OUTPUT.PUT_LINE('及格');ELSEDBMS_OUTPUT.PUT_LINE('不及格');END IF;
END;
/--從鍵盤輸入一個員工編號，如果他的工作是MANAGER打印'他是經理'；
--如果他的工作是CLERK打印'他是職員'；
--如果他的工作是SALESMAN打印'他是銷售'；
--如果他的工作是ANALYST打印'他是分析員'；
--否則打印'他是老板'。
declarev_job emp.job%type;
beginselect job into v_job from emp where empno = &empno;if v_job = 'MANAGER' thendbms_output.put_line('他是經理');elsif v_job = 'CLERK' thendbms_output.put_line('他是職員');elsif v_job = 'SALESMAN' thendbms_output.put_line('他是銷售');elsif v_job = 'ANALYST' thendbms_output.put_line('他是分析員');elsedbms_output.put_line('他是老板');end if;
end;
/

2、CASE-WHEN-THEN-ELSE語句

--語法
case when 條件1 then 要執行的語句;when 條件2 then 要執行的語句;……else 要執行的語句;
end case;--舉例
DECLAREv_grade CHAR(1) := 'B';
BEGINCASE v_gradeWHEN 'A' THEN DBMS_OUTPUT.PUT_LINE('優秀');WHEN 'B' THEN DBMS_OUTPUT.PUT_LINE('良好');WHEN 'C' THEN DBMS_OUTPUT.PUT_LINE('及格');ELSE DBMS_OUTPUT.PUT_LINE('未知等級');END CASE;
END;
/--練習
--從鍵盤上輸入一個員工編號，如果他的名字的首字母是A、B、C則打印1
--如果他的名字的首字母是D、E、F則打印2
--如果他的名字的首字母是G、H、I則打印3
--否則打印4；
declarev_e char;
beginselect substr(ename, 1, 1) into v_e from emp where empno = &empno;casewhen v_e in ('A', 'B', 'C') then--首字母是A、B、Cdbms_output.put_line(1);--打印1when v_e in ('D', 'E', 'F') then--首字母是D、E、Fdbms_output.put_line(2);--打印2when v_e in ('G', 'H', 'I') then--首字母是G、H、Idbms_output.put_line(3);--打印3elsedbms_output.put_line(4);--打印4end case;
end;

六、PL/SQL里的case when和SQL里的case when 的區別？

（1）sql里的case when 是一個值，pl/sql 里相當于if的作用；

（2）sql里case when 內部不能加分號，pl/sql里每一個條件后都要加分號；

（3）pl/sql里case when 后可以加DML；

（4）sql里case when 以end結尾，pl/sql里以end case 結尾；

七、匿名塊中begin部分的循環（也屬于流程控制）

循環：簡單循環、while循環、for循環

1、簡單循環（基本循環）

【1】語法:loop要執行的語句;exit when 退出的條件;end loop;進入循環不需要條件。注意：必須設置自增變量來控制循環以免出現死循環。【2】舉例DECLAREv_counter NUMBER := 1;BEGINLOOPDBMS_OUTPUT.PUT_LINE('當前計數: ' || v_counter);v_counter := v_counter + 1;EXIT WHEN v_counter > 5;END LOOP;END;/----------------------------------------------------------declarei number := 1;beginloopdbms_output.put_line(i);i := i + 1;exit when i > 10;end loop;end;/【3】練習
（1）用循環打印 A、B、C、D、E、F……Z。declarei number := ascii('A') - 1;beginloopi := i + 1;dbms_output.put(chr(i)||' ');--不換行打印exit when i > ascii('M') - 1;end loop;dbms_output.put_line('');--和一個換行打印搭配才能打印出東西i:=ascii('M');loopi := i + 1;dbms_output.put(chr(i));--不換行打印exit when i > ascii('Z') - 1;end loop;dbms_output.put_line('');--和一個換行打印搭配才能打印出東西end;/（2）123 456 789 declarei1 number := 1;i2 number := 1;j  varchar(2) := ' ';k  number := 0;beginloopk := k + 1;loopdbms_output.put(i1);i1 := i1 + 1;i2 := 3 * k;exit when i1 > i2;end loop;dbms_output.put(j);exit when k > 9;end loop;dbms_output.put_line('');end;/輸出-----------1 2 3  4 5 6  7 8 9  ---------------
（3）循環打印1-10的和,顯示1+2+3+4+5+6+7+8+9+10=declarev_sum number := 0;v_num number := 1;beginloopdbms_output.put(v_num || '+');v_sum := v_num + v_sum;v_num := v_num + 1;exit when v_num > 9;end loop;dbms_output.put_line(10 || '=' || (v_sum + v_num));end;/
（4）只打印1-10的和declarev_sum number := 0;v_num number := 1;beginloopv_sum := v_num + v_sum;v_num := v_num + 1;exit when v_num > 9;end loop;dbms_output.put_line(v_sum + v_num);end;/

2、while循環

【1】語法
while 條件 
loop 要執行的語句
{exit when 條件}
end loop;
滿足while后的條件才進入循環，不滿足不進入循環
注意：必須要設置自增變量控制循環，以免出現死循環。/*###################################################*/
【2】舉例
DECLAREv_counter NUMBER := 1;
BEGINWHILE v_counter <= 5 LOOPDBMS_OUTPUT.PUT_LINE('當前計數: ' || v_counter);v_counter := v_counter + 1;END LOOP;
END;
/
---------------------------------------------------------
declarei number := 0;
beginwhile i < 10loopi := i + 1;dbms_output.put_line(i);exit when i=5;--中途退出end loop;
end;/*###################################################*/
【3】練習
（1）用while循環打印A、B、C、D、E、F
declarei number := ascii('A') - 1;
beginwhile i < ascii('Z') loopdbms_output.put_line(chr(i + 1));i := i + 1;end loop;
end;
/（2）循環打印1-10的和
declarev_sum number := 0;v_num number := 1;
beginwhile v_num <=10 loopv_sum := v_num + v_sum;dbms_output.put_line(v_sum);v_num := v_num + 1;end loop;
end;
/（3）循環打印10-1
declarev1 number := 10;
beginwhile v1 > 0 loopdbms_output.put_line(v1);v1 := v1 - 1;end loop;
end;
/
===================================
declarev1 number := 10;
beginwhile v1 > 0 loopdbms_output.put(v1||' ');v1 := v1 - 1;end loop;dbms_output.put_line('');
end;
/（4）用while循環寫九九乘法表*******
DECLAREx INT := 1;y INT;
BEGINWHILE x <= 9 LOOPy := 1;WHILE y <= x LOOPdbms_output.put(y || ' * '|| x || ' = ' || x*y || ' ');y := y + 1;END LOOP;x := x + 1;dbms_output.put_line(''); END LOOP;
END;
/

3、for循環

【1】語法
for 變量 in {reverse}小值..大值loop要執行的語句;{exit when 條件;end loop;
每循環一次，循環變量自動+1;
使用關鍵字 reverse 循環變量自動-1;
跟在in reverse 后面的數字必須是從小到大的順序，而且必須是整數/*###################################################*/
【2】舉例
BEGINFOR i IN 1..5 LOOPDBMS_OUTPUT.PUT_LINE('當前計數: ' || i);END LOOP;-- 反向循環FOR i IN REVERSE 1..5 LOOPDBMS_OUTPUT.PUT_LINE('反向計數: ' || i);END LOOP;
END;
/
-----------------------------------------------------
beginfor i in reverse 1 .. 10 loopdbms_output.put_line(i);end loop;
end;
//*###################################################*/
【3】練習
（1）打印A-Z
beginfor i in ascii('A') .. ascii('Z') loopdbms_output.put(chr(i));end loop;dbms_output.put_line('');
end;
/
-----------------------------------------------------
（2）打印20以內的奇數
方法1：
declarej number;
beginfor i in 1..10 loopj:=2*i-1;dbms_output.put_line(j);end loop;
end;
方法2：
beginfor i in 1 .. 20 loopif mod(i, 2) = 1 thendbms_output.put_line(i);end if;end loop;
end;
/
-----------------------------------------------------
（3）打印1-20的和
declaren number := 0;
beginfor i in 1 .. 20 loopn := n + i;end loop;dbms_output.put_line(n);
end;
/
-----------------------------------------------------
（4）打印1-20以內奇數的乘積
declaren number := 1;j number;
beginfor i in 1 .. 10 loopj := 2 * i - 1;n := n * j;end loop;dbms_output.put_line(n);
end;
/
-----------------------------------------------------
（5）打印九九乘法表
beginfor i in 1 .. 9 loop--行數for j in 1 .. i loop--列數dbms_output.put(j || '×' || i || '=' || i * j || ' ');end loop;dbms_output.put_line('');end loop;
end;
/
---------------------------------
beginfor i in 1..9 loopfor j in 1..i loop----第一次循環是 1----1----第二次循環是 1----2----第三次循環是 1----3dbms_output.put(j||'*'||i||'='||i*j||' ');end loop;dbms_output.put_line('');end loop;
end;
/
-----------------------------------------------------
（6）打印金字塔
beginfor i in 1 .. 9 loopfor j in  1 .. (9-i) loopdbms_output.put(' ');end loop;for  k in 1..(2*i-1) loopdbms_output.put('*');end loop;dbms_output.put_line('');end loop;
end;
/
-----------------------------------------------------
（7）打印直角
beginfor i in  reverse 1 .. 9 loopfor j in  1 .. i loopdbms_output.put('*');end loop;dbms_output.put_line('');end loop;
end;

八、匿名塊中的異常處理

1. 預定義異常

BEGIN-- 可能出錯的代碼UPDATE employees SET salary = salary * 1.1 WHERE employee_id = 9999;IF SQL%NOTFOUND THENDBMS_OUTPUT.PUT_LINE('未更新任何行');END IF;
EXCEPTIONWHEN NO_DATA_FOUND THENDBMS_OUTPUT.PUT_LINE('未找到數據');WHEN TOO_MANY_ROWS THENDBMS_OUTPUT.PUT_LINE('返回多行數據');WHEN OTHERS THENDBMS_OUTPUT.PUT_LINE('錯誤代碼: ' || SQLCODE);DBMS_OUTPUT.PUT_LINE('錯誤信息: ' || SQLERRM);
END;
/

2. 自定義異常

DECLAREe_invalid_dept EXCEPTION;v_dept_id NUMBER := 99;
BEGIN-- 檢查部門是否存在IF v_dept_id NOT BETWEEN 10 AND 100 THENRAISE e_invalid_dept;END IF;-- 正常處理DBMS_OUTPUT.PUT_LINE('部門ID有效');
EXCEPTIONWHEN e_invalid_dept THENDBMS_OUTPUT.PUT_LINE('錯誤: 無效的部門ID ' || v_dept_id);WHEN OTHERS THENDBMS_OUTPUT.PUT_LINE('其他錯誤');
END;
/

九、隨機函數的應用

1、dbms_random.value(小值，大值)

dbms_random.value(小值，大值)
返回的是小數（小值到大值之間的）
在-2^31-2^31之間
select dbms_random.value(-1,10) from dual

2、dbms_random.string(參數，長度)

dbms_random.string(參數，長度)
u/U 大寫字母
l/L 小寫字母
a/A 字母
x/X 大寫字母和數字
p/P 任意可打印字符
select dbms_random.string('p',5) from dual

3、在匿名塊中的應用

【1】
--生成一個0-10的隨機整數，如果大于5則輸出‘大’，
--如果小于5則輸出‘小’，如果等于5輸出‘巧了’。
declaren number;
beginn := trunc(dbms_random.value(0, 10));if n > 5 thendbms_output.put_line(n || '大');elsif n < 5 thendbms_output.put_line(n || '小');elsedbms_output.put_line(n || '巧了');end if;
end;
/【2】
--連選十次
declaren number;
beginfor i in 1 .. 10 loopn := trunc(dbms_random.value(0, 100));if n > 5 thendbms_output.put(n || '普通' || ' ');elsif n < 5 thendbms_output.put(n || '稀有' || ' ');elsedbms_output.put(n || '傳說' || ' ');end if;end loop;dbms_output.put_line('');
end;
/【3】
--聲明一個變量要求和hr 用戶的employees的hire_date的類型一致
--把2003年入職，員工編號是141的first_name,last_name 輸出。
declare
v_h hr.employees.hire_date%type;
v_f hr.employees.first_name%type;
v_l hr.employees.last_name%type;
beginselect first_name,last_name into v_f,v_lfrom hr.employees e where e.employee_id=141 and to_char(e.hire_date,'yyyy')=2003;dbms_output.put_line(v_f||' '||v_l);end;
/【4】
--打印1*2*3*...*20的式子和結果
declaren number := 1;
beginfor i in 1 .. 20 loopn := n * i;if i < 20 thendbms_output.put(i || '×');elsedbms_output.put(i);end if;end loop;dbms_output.put_line('=' || n);
end;