oracle 游标简写,zbb20170601 oracle PL/SQL 语句块 游标 自定义游标 异常处理EXCEPTION...

--打开日志输出

set serverout on

--PL/SQL 语句块：

--声明部分

declarev_inumber;

v_sumnumber:=0;--执行部分

beginv_i := 1;--简单loop循环

loop

v_sum:=v_sum+v_i;

v_i:=v_i+1;if v_i>100 then

exit;--跳出循环

end if;endloop;

dbms_output.put_line(v_sum);--异常处理部分

end;--循环1 ：简单LOOP循环

loop--循环体

endloop;--退出循环：

if 退出条件 then

exit;end if;--简写的退出条件

exit when退出条件；--

declarev_snumber:=1;

v_inumber:=1;beginloop

v_s:=v_s*v_i;

v_i:=v_i+1;exit when v_i>3;endloop;

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循环

declarev_inumber:=1;

v_sumnumber:=0;begin

while v_i<=100loop

v_sum:=v_sum+v_i;

v_i:=v_i+1;endloop;

dbms_output.put_line(v_sum);end;--循环3：数值循环for

declarev_sumnumber:=0;begin

for v_i in 1..100loop

v_sum:=v_sum+v_i;endloop;

dbms_output.put_line(v_sum);end;--in reverse 反向for循环

begin

for v_i in REVERSE 1..10loop

dbms_output.put_line(v_i);endloop;end;--查询

declarev_enonumber:=7780;

v_enamevarchar2(20);begin

select ename into v_ename from emp where empno=v_eno;

dbms_output.put_line(v_ename);

exceptionwhen no_data_found thendbms_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); -- 为异常编号困绑这个名称

declaremy_exception_name EXCEPTION;--定义异常名称

PRAGMA EXCEPTION_INIT(my_exception_name,-2291); --为异常编号困绑这个名称

begin

insert into emp(empno,deptno) values(1000,50);

exceptionwhen my_exception_name thendbms_output.put_line(sqlerrm);end;--手动抛出异常：raise_application_error(异常编号,‘异常消息‘);

declare

--嵌套语句块的外部声明部分

begin

--嵌套前

declare

--变量的声明周期：在其定义的begin end 中

v_sex varchar2(20);beginv_sex := ‘&输入性别‘;if not (v_sex = ‘男‘ or v_sex=‘女‘) then

--抛出异常

raise_application_error(-20000,‘性别只能是男或者女‘);end if;--正常输出语句

dbms_output.put_line(‘性别：‘||v_sex);

exceptionwhen others thendbms_output.put_line(sqlerrm);end;--嵌套后

end;--列引用类型：%type

declarev_ename emp.ename%type;

v_sal emp.sal%type;

c_constant constantnumber:=20;--定义一个常量

begin

select ename,sal into v_ename,v_sal from emp where empno=&员工编号;

dbms_output.put_line(v_ename||‘ ‘||v_sal);

exceptionwhen others thendbms_output.put_line(sqlerrm);end;--行引用类型： %rowtype;

declarev_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);

exceptionwhen others thendbms_output.put_line(sqlerrm);end;--------- 游标 ----------

declarev_row emp%rowtype;--1-定义游标

cursor cur_emp is select * from emp where deptno=10;begin

--2- 打开游标

opencur_emp;

loop--3- 提取数据

fetch cur_emp intov_row;--提取不到数据时结束循环

exit when cur_emp%notfound;

dbms_output.put_line(v_row.empno||‘ ‘||v_row.ename||‘ ‘||v_row.deptno||‘ ‘||v_row.sal);endloop;--4- 关闭游标

closecur_emp;

exceptionwhen others thendbms_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

opencur_emp;end if;fetch cur_emp intov_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 intov_row;endloop;if cur_emp%isopen then

closecur_emp;end if;

exceptionwhen others thendbms_output.put_line(sqlerrm);end;--游标for循环：--特点：帮我们做 open fetch close

declare

cursor cur_emp_dept is

selecte.empno,e.ename,e.deptno,d.dname,d.locfrom emp e join dept d on(e.deptno=d.deptno);--使用游标定义行引用类型

v_row cur_emp_dept%rowtype;begin

for v_row incur_emp_dept loop

dbms_output.put_line(v_row.empno||‘ ‘||v_row.ename||‘ ‘||v_row.deptno||‘ ‘||v_row.dname||‘ ‘||v_row.loc);endloop;

exceptionwhen others thendbms_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);endloop;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 intov_row;while cur_emp%found loop

dbms_output.put_line(v_row.ename||‘ ‘||v_row.sal||‘ ‘||v_row.deptno);fetch cur_emp intov_row;endloop;closecur_emp;

exceptionwhen others thendbms_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);endloop;

exceptionwhen others thendbms_output.put_line(sqlerrm);end;--游标的意义：逐行判断复杂的条件，并更新

--更新10部门工资低于3000的人，工资上涨10%；

declare

cursor cur_emp is

select * fromempfor update --锁定游标指定的结果集

nowait; --指定非等待方式

begin

for v_row incur_emp loopif(v_row.sal<3000 and v_row.deptno=10) then

--条件： 游标指向的当前行

update emp set sal=sal*1.1 where current ofcur_emp;end if;endloop;

exceptionwhen others thendbms_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.deptnofor update --锁定游标指定的结果集

of e.deptno --指定锁定emp表(结果集合使用emp的rowid)

nowait; --指定非等待方式

begin

for v_row incur_emp loopif(v_row.sal<3000 and v_row.deptno=10) then

--条件： 游标指向的当前行

update emp set sal=sal*1.1 where current ofcur_emp;end if;endloop;

exceptionwhen others thendbms_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 * fromemp;

loopfetch my_cursor intov_row;exit when my_cursor%notfound;

dbms_output.put_line(v_row.empno||‘ ‘||v_row.ename||‘ ‘||v_row.deptno||‘ ‘||v_row.sal);endloop;closemy_cursor;

exceptionwhen others thendbms_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 * fromemp;

loopfetch my_cursor intov_row;exit when my_cursor%notfound;

dbms_output.put_line(v_row.empno||‘ ‘||v_row.ename||‘ ‘||v_row.deptno||‘ ‘||v_row.sal);endloop;closemy_cursor;

exceptionwhen others thendbms_output.put_line(sqlerrm);end;

type curis ref cursor;

整体的意思是“创建一个类型变量cur，它引用游标”，除了cur外，其余全是关键字。

TYPE cur：定义类型变量is ref cursor：相当于数据类型，不过是引用游标的数据类型。

这种变量通常用于存储过程和函数返回结果集时使用，因为PL/SQL不允许存储过程或函数直接返回结果集，但可以返回类型变量，于是引用游标的类型变量作为输出参数或返回值就应运而生了