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;