PL/SQL(本篇文章,使用sqldeveloper工具)
A.游标编程
1.游标
oracle服务器处理客户端的SQL语句
或者批处理存储过程,触发器中的数据处理请求时
会在内存中开辟一个工作区,在其中存储处理的结果
游标,相当于指向该内存区的指针,可以逐一从内存区中获取记录
并赋给变量,交由程序进一步处理
游标由系统或用户以变量的形式定义
2.分类
a.隐式游标
在 PL/SQL 程序中执行DML SQL 语句时自动创建隐式游标,名字固定叫sql
b.显式游标
显式游标用于处理返回多行的查询
c.REF游标
REF 游标用于处理运行时才能确定的动态 SQL 查询的结果3.隐式游标
PL/SQL中使用DML(CRUD)语句时自动创建
名为sql,通过检查隐式游标的属性可以获得最近执行的DML语句信息
存放的是最新处理的一条SQL 语句所包含的数据
属性:
sql%FOUND – SQL 语句影响了一行或多行时为 TRUE
sql%NOTFOUND – SQL 语句没有影响任何行时为TRUE
sql%ROWCOUNT – SQL 语句影响的行数
练习
先执行这句话
-- 别忘了先执行这句,否则打印不出信息 set serveroutput on;
a.scott模式下把emp表中销售人员的工资上调20%,然后使用隐式游标输出上调工资的员工数量(权限不够,切换sys,给scott最高权限)
给最高权限
conn sys/123456 as sysdba; grant dba to scott;
-- 别忘了先执行这句,否则打印不出信息 set serveroutput on; begin -- 销售人员工资上调20% update emp set sal=sal*1.2 where job ='SALESMAN'; -- 判断是否有影响的行数 if sql%found then dbms_output.put_line(sql%rowcount||'人上调工资'); else dbms_output.put_line('没有人上调工资'); end if; end;
b.查询scott模式下编号为7369的员工信息,打印出来
declare vname emp.ename%type; -- 定义vname,类型为ename的类型 begin -- 查询结果放入vname select ename into vname from emp where empno=7369; -- 判断是否有结果 if sql%found then dbms_output.put_line(sql%rowcount); else dbms_output.put_line('没有找到数据'); end if; -- 异常 exception when too_many_rows then dbms_output.put_line('查找的行记录多余1行'); when no_data_found then dbms_output.put_line('未找到匹配的行'); end;
4.显式游标
用户声明和操作的一种游标,常操作select查询结果集
步骤:
a.声明游标,在declare部分
就是再定义一个游标名,以及与其相对应的SELECT 语句
语法
CURSOR cursor_name[(parameter[, parameter]…)]
[RETURN datatype]
IS
select_statement;
游标参数只能为输入参数,其格式为:
parameter_name [IN] datatype [{:= | DEFAULT} expression],在指定数据类型时,不能使用长度约束。如NUMBER(4),CHAR(10) 等都是错误的
[RETURN datatype]是可选的,表示游标返回数据的数据。如果选择,则应该严格与select_statement中的选择列表在次序和数据类型上匹配。一般是RECORD记录数据类型或带“%ROWTYPE”的数据
b.打开游标
游标声明完毕,必须打开才能使用
就是执行游标所对应的SELECT语句
将其查询结果放入工作区,并且指针指向工作区的首部
标识出游标结果集合
语法:
OPEN cursor_name[([parameter =>] value[, [parameter =>] value]…)];
c.读取游标
就是检索结果集合中的数据行,放入指定的输出变量中
打开一个游标后,就可以读取游标中的数据了
语法:
FETCH cursor_name INTO {variable_list | record_variable };
执行FETCH语句时,每次返回一个数据行
然后自动将游标移动指向下一个数据行
当检索到最后一行数据时,如果再次执行FETCH语句
将操作失败,并将游标属性%NOTFOUND置为TRUE
所以每次执行完FETCH语句后,检查游标属性%NOTFOUND
就可以判断FETCH语句是否执行成功并返回一个数据行
以便确定是否给对应的变量赋了值
d.关闭游标
当提取和处理完游标结果集合数据后,应及时关闭游标
以释放该游标所占用的系统资源,并使该游标的工作区变成无效
不能再使用FETCH 语句取其中数据
关闭后的游标可以使用OPEN 语句重新打开
语法:
CLOSE cursor_name;
备注:显式游标和隐式游标有相同的属性
练习
a.使用游标检索scott模式中emp表,查找编号为7369的雇员名称和职务
DECLARE -- 定义变量,类型为字段类型 vname EMP.ENAME%TYPE; vjob EMP.JOB%TYPE; -- 定义游标,存放查询的数据 CURSOR cur_emp is select ename,job from emp where empno=7369; begin -- 打开游标 open cur_emp; -- 读取游标,将游标所在的数据,放入定义的变量中 FETCH cur_emp INTO vname,vjob; -- 判断 if cur_emp%found then dbms_output.put_line(vname||'---'||vjob); else dbms_output.put_line('没有查到数据'); end if; -- 关闭游标 close cur_emp; end;
b.显式游标常和while循环语句结合使用,简化遍历操作
使用游标检索员工编号大于7900的员工信息
DECLARE -- 定义变量存储一行数据 row_emp emp%ROWTYPE; -- 定义游标 CURSOR cur_emp is select * from emp where empno>7900; BEGIN -- 打开游标 OPEN cur_emp; -- 读取游标 FETCH cur_emp into row_emp; -- 循环遍历 WHILE cur_emp%found loop dbms_output.put_line(row_emp.empno||'---'||row_emp.ename); -- 再次读取 FETCH cur_emp into row_emp; end loop; --关闭游标 close cur_emp; END;
c.使用for循环游标,再次简化操作
不再需要open fetch和close语句
不用%FOUND属性检测是否到最后一条记录
一切Oracle隐式的帮我们完成了
也不需要在declare部分声明临时变量
其中for后面的类型是一个自动的record类型
declare -- 定义游标 cursor cur_emp is select * from emp where empno>7900; begin -- for循环 for emp_row in cur_emp loop dbms_output.put_line(emp_row.ename); end loop; end;
5.REF 游标:又称游标变量
与游标不同的是,游标变量是动态的,而游标是静态的
游标只能与指定的查询相连,即固定指向一个查询的内存处理区域
而游标变量则可与不同的查询语句相连
它可以指向不同查询语句的内存处理区域
但不能同时指向多个内存处理区域,在某一时刻只能与一个查询语句相连
步骤:
a.定义一个REF CURSOU数据类型
b.声明一个该数据类型的游标变量
c.在打开的时候和一个SQL语句关联
d.获取记录,操作记录
e.关闭游标,完全释放资源
练习:
a.使用游标检索scott模式中emp表,查找编号为7369的雇员名称和职务
declare -- 定义一个数据类型 type cur_ref is ref cursor; -- 定义一个中间变量 temp varchar2(20); -- 定义一个游标变量 vcur cur_ref; begin -- 打开游标关联sql语句 open vcur for select ename from emp where empno>7900; -- 获取操作记录 fetch vcur into temp; -- do_while循环 loop dbms_output.put_line(temp); -- 再次获取 fetch vcur into temp; exit when vcur%notfound; end loop; -- 关闭游标 close vcur; end;
B.自定义异常
1.异常处理
pl/sql的异常处理在exception代码块中,按照产生方式分为:
预定义异常:系统提供的异常,直接使用
自定义异常:程序设计人员根据业务需要自定义的异常
2.预定义异常
3.自定义异常
可以分为错误编号异常和业务逻辑异常
a.错误编号异常
错误发生时候系统显示错误号和相关描述信息的异常
oralce系统发生错误时,会提供错误号和相关描述信息
但错误编号较为抽象,不易理解
可以在declare中声明exception类型的异常变量名
使用PRAGMA EXCEPTION_INIT给错误编号关联该异常变量
然后就像系统预定义异常一样处理了
编号异常是对系统错误号的包装,使用异常处理机制后
防止异常引发程序崩溃
练习
在emp表中插入编号为7369的员工后
引发ORA-00001错误号,同时程序崩溃
insert into emp(empno) values(7369);
错误编号后面的解释有些抽象,并且程序崩溃,我们自定义一下:
declare -- 定义异常变量 pk_excep exception; -- 关联异常变量名和错误号 pragma exception_init(pk_excep,-00001); begin -- 执行一个错误语句 insert into emp(empno) values(7369); exception when pk_excep then dbms_output.put_line('主键empno已存在'); end;
可以看出,程序正常执行完成
b.业务逻辑异常
违反业务逻辑时候需要显示触发的异常
系统自定义异常或错误编号异常,均由oracle系统判断
但是业务逻辑异常oracle系统无法知晓,所以需要使用raise语句触发
首先在decalre部分声明异常变量
然后在begin部分根据业务逻辑执行raise语句
然后在exception部分进行异常处理
练习
实际应用中dept中dname不能为空
insert into dept(deptno) values(11); select * from dept;
desc dept;
可以看出,表中并未规定dname不能为空
所以从语法上来讲,并没有异常
但是从业务逻辑来讲,dname不能为空,所以要自定义一个异常
declare null_excep exception; dept_row dept%rowtype; begin dept_row.deptno:=9; insert into dept values(dept_row.deptno,dept_row.dname,dept_row.loc); -- 如果dname为空 if dept_row.dname is null then raise null_excep; end if; exception when null_excep then dbms_output.put_line('dname不能为空'); -- 回滚,否则依然会执行语句 rollback; end;
查看表中数据