当前位置：Gxlcms >
数据库问题 >
zbb20170601 oracle PL/SQL 语句块 游标 自定义游标 异常处理EXCEPTION
zbb20170601 oracle PL/SQL 语句块 游标 自定义游标 异常处理EXCEPTION
时间：2021-07-01 10:21:17
帮助过：6人阅读
打开日志输出
set serverout
on
-- PL/SQL 语句块：
-- 声明部分
declare
v_i number;
v_sum number:
=0;
-- 执行部分
begin
v_i := 1;
-- 简单loop循环
loop
v_sum:=v_sum
+v_i;
v_i:=v_i
+1;
if v_i
>100 then
exit;
-- 跳出循环
end if;
end loop;
dbms_output.put_line(v_sum);
-- 异常处理部分
end;
-- 循环1 ：简单LOOP循环
loop
-- 循环体
end loop;
-- 退出循环：
if 退出条件
then
exit;
end if;
-- 简写的退出条件
exit when 退出条件；
--
declare
v_s number:
=1;
v_i number:
=1;
begin
loop
v_s:=v_s
*v_i;
v_i:=v_i
+1;
exit when v_i
>3;
end loop;
dbms_output.put_line(v_s);
end;
-- 注意：在sqlplus中要输出，需要打开输出标记：
-- SQL> set serveroutput on;
-- if语句
if 条件1
then
--条件1为true执行语句
elsif 条件2
then
--条件2为true执行语句
else
--条件1、条件2都为false时执行语句
end if;
-- 循环2：while循环
declare
v_i number:
=1;
v_sum number:
=0;
begin
while v_i
<=100 loop
v_sum:=v_sum
+v_i;
v_i:=v_i
+1;
end loop;
dbms_output.put_line(v_sum);
end;
-- 循环3：数值循环for
declare
v_sum number:
=0;
begin
for v_i
in 1..
100 loop
v_sum:=v_sum
+v_i;
end loop;
dbms_output.put_line(v_sum);
end;
-- in reverse 反向for循环
begin
for v_i
in REVERSE 1..
10 loop
dbms_output.put_line(v_i);
end loop;
end;
-- 查询
declare
v_eno number:
=7780;
v_ename varchar2(
20);
begin
select ename
into v_ename
from emp
where empno
=v_eno;
dbms_output.put_line(v_ename);
exception
when no_data_found
then
dbms_output.put_line(‘没找到数据异常：‘||sqlerrm);
when others
then-- others 相当于异常父类，可以捕获所有异常
dbms_output.put_line(
‘异常：‘||sqlerrm);
end;
-- 异常消息格式：
-- 异常类型（ORA运行异常 PLS编译异常） 异常编号（一个负数：-1 ~ -19999） 异常消息
-- 例如：ORA-01403: 未找到任何数据
-- 自定义异常：
-- my_exception_name EXCEPTION; -- 定义异常名称
-- PRAGMA EXCEPTION_INIT(my_exception_name,-2290); -- 为异常编号困绑这个名称
declare
my_exception_name EXCEPTION; -- 定义异常名称
PRAGMA EXCEPTION_INIT(my_exception_name,
-2291);
-- 为异常编号困绑这个名称
begin
insert into emp(empno,deptno)
values(
1000,
50);
exception when my_exception_name
then
dbms_output.put_line(sqlerrm);
end;
-- 手动抛出异常：raise_application_error(异常编号,‘异常消息‘);
declare
-- 嵌套语句块的外部声明部分
begin
-- 嵌套前
declare
-- 变量的声明周期：在其定义的begin end 中
v_sex
varchar2(
20);
begin
v_sex := ‘&输入性别‘;
if not (v_sex
= ‘男‘ or v_sex
=‘女‘)
then
-- 抛出异常
raise_application_error(
-20000,
‘性别只能是男或者女‘);
end if;
-- 正常输出语句
dbms_output.put_line(
‘性别：‘||v_sex);
exception when others
then
dbms_output.put_line(sqlerrm);
end;
-- 嵌套后
end;
-- 列引用类型：%type
declare
v_ename emp.ename%type;
v_sal emp.sal%type;
c_constant constant number:
=20;
-- 定义一个常量
begin
select ename,sal
into v_ename,v_sal
from emp
where empno
=&员工编号;
dbms_output.put_line(v_ename||‘ ‘||v_sal);
exception when others
then
dbms_output.put_line(sqlerrm);
end;
-- 行引用类型： %rowtype;
declare
v_row emp%rowtype;
begin
select * into v_row
from emp
where empno
=&eno;
dbms_output.put_line(v_row.ename||‘ ‘||v_row.empno
||‘ ‘||v_row.sal
||‘ ‘||v_row.deptno);
exception when others
then
dbms_output.put_line(sqlerrm);
end;
--------- 游标 ----------
declare
v_row emp%rowtype;
-- 1-定义游标
cursor cur_emp
is select * from emp
where deptno
=10;
begin
-- 2- 打开游标
open cur_emp;
loop
-- 3- 提取数据
fetch cur_emp
into v_row;
-- 提取不到数据时结束循环
exit when cur_emp
%notfound;
dbms_output.put_line(v_row.empno||‘ ‘||v_row.ename
||‘ ‘||v_row.deptno
||‘ ‘||v_row.sal);
end loop;
-- 4- 关闭游标
close cur_emp;
exception when others
then
dbms_output.put_line(sqlerrm);
end;
/**
游标属性
%found boolean 测试是否提取到数据
%notfound boolean 测试是否未提取到数据
%isopen boolean 测试是否打开
%rowcount number 测试当前操作的行数
*/
-- while使用游标
declare
cursor cur_emp
is select * from emp
where deptno
=&dno;
v_row emp%rowtype;
begin
if not cur_emp
%isopen
then
open cur_emp;
end if;
fetch cur_emp
into v_row;
while cur_emp
%found loop
dbms_output.put_line(cur_emp%rowcount||‘ ‘||v_row.empno
||‘ ‘||v_row.ename
||‘ ‘||v_row.deptno
||‘ ‘||v_row.sal);
fetch cur_emp
into v_row;
end loop;
if cur_emp
%isopen
then
close cur_emp;
end if;
exception when others
then
dbms_output.put_line(sqlerrm);
end;
-- 游标for循环：
-- 特点：帮我们做 open fetch close
declare
cursor cur_emp_dept
is
select e.empno,e.ename,e.deptno,d.dname,d.loc
from emp e
join dept d
on(e.deptno
=d.deptno);
-- 使用游标定义行引用类型
v_row cur_emp_dept
%rowtype;
begin
for v_row
in cur_emp_dept loop
dbms_output.put_line(v_row.empno||‘ ‘||v_row.ename
||‘ ‘||v_row.deptno
||‘ ‘||v_row.dname
||‘ ‘||v_row.loc);
end loop;
exception when others
then
dbms_output.put_line(sqlerrm);
end;
-- 使用游标for循环甚至可以不用定义游标
begin
for v_row
in (
select ename,sal
from emp
where deptno
=20) loop
dbms_output.put_line(v_row.ename||‘ ‘||v_row.sal);
end loop;
end;
-- 参数化游标
declare
-- 注意：形参禁止带精度，否则编译错误
cursor cur_emp(dno
number)
is select * from emp
where deptno
=dno;
v_row cur_emp%rowtype;
begin
-- 打开游标时指定参数
open cur_emp(
30);
fetch cur_emp
into v_row;
while cur_emp
%found loop
dbms_output.put_line(v_row.ename||‘ ‘||v_row.sal
||‘ ‘||v_row.deptno);
fetch cur_emp
into v_row;
end loop;
close cur_emp;
exception when others
then
dbms_output.put_line(sqlerrm);
end;
declare
-- 注意：形参禁止带精度，否则编译错误
cursor cur_emp(dno
number)
is select * from emp
where deptno
=dno;
begin
for v_row
in cur_emp(
20) loop
dbms_output.put_line(v_row.ename||‘ ‘||v_row.sal
||‘ ‘||v_row.deptno);
end loop;
exception when others
then
dbms_output.put_line(sqlerrm);
end;
-- 游标的意义：逐行判断复杂的条件，并更新
-- 更新10部门工资低于3000的人，工资上涨10%；
declare
cursor cur_emp
is
select * from emp
for update -- 锁定游标指定的结果集
nowait;
-- 指定非等待方式
begin
for v_row
in cur_emp loop
if(v_row.sal
<3000 and v_row.deptno
=10)
then
-- 条件： 游标指向的当前行
update emp
set sal
=sal
*1.1 where current of cur_emp;
end if;
end loop;
exception when others
then
dbms_output.put_line(sqlerrm);
end;
-- 涉及多表操作的更新游标
declare
cursor cur_emp
is
select e.
*,d.dname,d.loc
from emp e,dept d
where d.deptno
=e.deptno
for update -- 锁定游标指定的结果集
of e.deptno
-- 指定锁定emp表（结果集合使用emp的rowid）
nowait;
-- 指定非等待方式
begin
for v_row
in cur_emp loop
if(v_row.sal
<3000 and v_row.deptno
=10)
then
-- 条件： 游标指向的当前行
update emp
set sal
=sal
*1.1 where current of cur_emp;
end if;
end loop;
exception when others
then
dbms_output.put_line(sqlerrm);
end;
-- 游标变量
declare
-- 定义游标类型
type my_cursor_type
is ref
cursor;
-- 使用游标类型定义游标变量
my_cursor my_cursor_type;
v_row emp%rowtype;
-- 错误：不能使用普通的游标变量定义行引用类型
--v_row my_cursor%rowtype;
begin
-- 打开游标时赋予结果集
open my_cursor
for select * from emp;
loop
fetch my_cursor
into v_row;
exit when my_cursor
%notfound;
dbms_output.put_line(v_row.empno||‘ ‘||v_row.ename
||‘ ‘||v_row.deptno
||‘ ‘||v_row.sal);
end loop;
close my_cursor;
exception when others
then
dbms_output.put_line(sqlerrm);
end;
-- 定义一个带返回类型的游标类型，生成游标变量
declare
-- 定义带返回类型的游标类型
type my_cursor_type
is ref
cursor return emp
%rowtype;
-- 使用游标类型定义游标变量
my_cursor my_cursor_type;
--v_row emp%rowtype;
-- 正确：可以使用带返回类型的游标变量定义行引用类型
v_row my_cursor
%rowtype;
begin
-- 打开游标时赋予结果集
open my_cursor
for select * from emp;
loop
fetch my_cursor
into v_row;
exit when my_cursor
%notfound;
dbms_output.put_line(v_row.empno||‘ ‘||v_row.ename
||‘ ‘||v_row.deptno
||‘ ‘||v_row.sal);
end loop;
close my_cursor;
exception when others
then
dbms_output.put_line(sqlerrm);
end;
type cur is ref
cursor;
整体的意思是“创建一个类型变量cur，它引用游标”，除了cur外，其余全是关键字。
TYPE cur：定义类型变量
is ref
cursor：相当于数据类型，不过是引用游标的数据类型。
这种变量通常用于存储过程和函数返回结果集时使用，因为PL/SQL不允许存储过程或函数直接返回结果集，但可以返回类型变量，于是引用游标的类型变量作为输出参数或返回值就应运而生了
zbb20170601 oracle PL/SQL 语句块 游标 自定义游标 异常处理EXCEPTION
标签：div and 处理 ini 函数 返回值 fetch 定义类 关闭
