【oracle学习】7.plsql光标和例外
来源:互联网 发布:网络电视看珠江台了 编辑:程序博客网 时间:2024/06/04 00:19
前言
我们以下的所有操作均在PL/Sql Developer工具上完成:
我们以下的表操作可能会基于以下两张表:
我们创建一个员工表和部门表:
员工信息表
SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
--------------- -------------------- ------------------ --------------- -------------- ----- ----- ---------------
1110 张三 主管 1110 12-3月 -14 5200 0 20
1111 李四 销售 1116 03-11月-15 3400 500 30
1112 王五 销售 1116 25-4月 -12 4400 800 30
1113 赵二 后勤 1110 30-5月 -11 3450 0 40
1114 李磊磊 会计 1110 22-12月-15 2500 0 50
1115 张少丽 销售 1110 11-3月 -16 2400 1400 30
1116 林建国 主管 1116 22-1月 -16 5700 0 20
1117 马富邦 后勤 1116 22-7月 -13 2800 0 40
1118 沈倩 会计 1116 06-5月 -10 2100 0 50
部门表
DEPTNO DNAME
-------- --------
20 管理部门
30 销售部门
40 后勤部门
50 金融部门
(1)光标
①光标基础
光标(Cursor)其实就等同于Java中的ResultSet,是一个结果集。
它用来存储一个查询返回的多行数据。
语法:
cursor 光标名 [(参数名 数据类型[,参数名 数据类型]...)]
is select 语句;
光标的属性:
%isopen 是否被打开
%rowcount 行数
%notfound 是否有值
例如:
cursor cl is select ename from emp;
cl中就有emp中的所有ename字段的数据。
打开光标: open c1;(打开光标执行查询)
取一行光标的值:fetch c1 into pjob;(取一行到变量中)
关闭光标:close c1;(关闭光标释放资源)
注意:
上面的pjob必须与emp表中的job列类型一致:
定义: pjob emp.empjob%type;
演示:
使用光标查询员工姓名和工资,并打印
不使用光标的话的方法为:
②示例操作
接下来我们来看一个例子:
我们这里要求给员工涨工资,主管1000元,会计800元,其它400元。
也就是根据员工的职位,执行相应的update语句来给其涨工资。
先不用PLSQL来写,我们用大家熟悉的Java来解决这个问题。
这里肯定是使用JDBC连接数据数,然后拿到Connection对象,请求数据库,
得到一个结果集,给结果集按照职位设置涨工资参数,示例伪代码如下:
使用PLSQL可以解决JDBC能解决的问题。
我们使用PLSQL来解决这个问题,结构和这个差不多,但是语法不一样。
但是我们把SQL语言的数据操纵能力与过程语言的数据处理能力结合起来,使
得PLSQL面向过程但又比过程语言简单、高效、灵活和实用。
我们使用PLSQL来实现这个例子:
首先注意,oracle默认一次性只能开启300个光标,我们可以通过
“show parameters cursor”指令来查看光标目前的设置信息:
使用Command窗口:
光标的开启数是可以调整的,但是现在我们的数据不到300,所以调整方法
等下一个总结我们再介绍。
编写PLSQL程序:
执行程序之前:
执行程序并commit之后,结果:
③带参数的光标
我们来查询某个部门的员工姓名
(2)例外
例外是程序设计语言提供的一种功能,用来增强程序的健壮性和容错性。
其实类似于Java的Exception。
Oracle的异常处理
A.系统定义的例外
--No_data_found(没有找到数据)
--Too_many_rows(select...into语句匹配多个行)
--Zero_Divide(被零除)
--Value_error(算术或转换错误)
--Timeout_on_resource(在等待资源时发生超时)
B.用户定义的例外
用户自己去定义的自定义例外。
例子:测试“被零除”的例外
结果:
用户自定义的例外,其实就是用户定义的一个变量
如
查询100号部门的员工姓名(100号部门是不存在的)
由于我们在close cemp;之前就raise了一个异常,oracle在抛出异常之后,就会
找没有关闭的光标,然后自动关闭掉。但有时候在复杂环境下,自动关闭往往是
不保险的,所以这里我们要自己手动关闭光标。
我们在exception的判断中,可以判断一下光标是否关闭,如果没有关闭就关闭它。
我们以下的所有操作均在PL/Sql Developer工具上完成:
我们以下的表操作可能会基于以下两张表:
我们创建一个员工表和部门表:
员工信息表
create table EMP( EMPNO NUMBER, ENAME VARCHAR2(10), JOB VARCHAR2(9), MGR NUMBER, HIREDATE DATE, SAL BINARY_DOUBLE, COMM BINARY_DOUBLE, DEPTNO NUMBER);其中job是职位,mgr是该员工的上司的id,sal是工资,comm是提成,deptno是所属部门。
SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
--------------- -------------------- ------------------ --------------- -------------- ----- ----- ---------------
1110 张三 主管 1110 12-3月 -14 5200 0 20
1111 李四 销售 1116 03-11月-15 3400 500 30
1112 王五 销售 1116 25-4月 -12 4400 800 30
1113 赵二 后勤 1110 30-5月 -11 3450 0 40
1114 李磊磊 会计 1110 22-12月-15 2500 0 50
1115 张少丽 销售 1110 11-3月 -16 2400 1400 30
1116 林建国 主管 1116 22-1月 -16 5700 0 20
1117 马富邦 后勤 1116 22-7月 -13 2800 0 40
1118 沈倩 会计 1116 06-5月 -10 2100 0 50
部门表
create table dept( DEPTNO NUMBER, DNAME VARCHAR2(50));SQL> select * from dept t;
DEPTNO DNAME
-------- --------
20 管理部门
30 销售部门
40 后勤部门
50 金融部门
(1)光标
①光标基础
光标(Cursor)其实就等同于Java中的ResultSet,是一个结果集。
它用来存储一个查询返回的多行数据。
语法:
cursor 光标名 [(参数名 数据类型[,参数名 数据类型]...)]
is select 语句;
光标的属性:
%isopen 是否被打开
%rowcount 行数
%notfound 是否有值
例如:
cursor cl is select ename from emp;
cl中就有emp中的所有ename字段的数据。
打开光标: open c1;(打开光标执行查询)
取一行光标的值:fetch c1 into pjob;(取一行到变量中)
关闭光标:close c1;(关闭光标释放资源)
注意:
上面的pjob必须与emp表中的job列类型一致:
定义: pjob emp.empjob%type;
演示:
使用光标查询员工姓名和工资,并打印
set serveroutput ondeclare --光标 cursor cemp is select ename,sal from emp; pename emp.ename%type; psal emp.sal%type;begin open cemp; loop --从集合中取值 fetch cemp into pename,psal; exit when cemp%notfound; dbms_output.put_line(pename||'的工资为'||psal); end loop; close cemp;end;/结果:
不使用光标的话的方法为:
declare employee emp%rowtype;begin for employee in (select * from emp) loop dbms_output.put_line(employee.ename||'的工资为'||employee.sal); end loop;end;/结果:
②示例操作
接下来我们来看一个例子:
我们这里要求给员工涨工资,主管1000元,会计800元,其它400元。
也就是根据员工的职位,执行相应的update语句来给其涨工资。
先不用PLSQL来写,我们用大家熟悉的Java来解决这个问题。
这里肯定是使用JDBC连接数据数,然后拿到Connection对象,请求数据库,
得到一个结果集,给结果集按照职位设置涨工资参数,示例伪代码如下:
ResultSet rs = "select empno,job from emp";while(rs.next()){ int eno = rs.getInt("empno"); String job = rs.getString("job"); if(job.equels("主管")){ update emp set sal=sal+1000 where empno=eno; } else if(job.equels("销售")){ update emp set sal=sal+800 where empno=eno; }else{ update emp set sal=sal+400 where empno=eno; }}
使用PLSQL可以解决JDBC能解决的问题。
我们使用PLSQL来解决这个问题,结构和这个差不多,但是语法不一样。
但是我们把SQL语言的数据操纵能力与过程语言的数据处理能力结合起来,使
得PLSQL面向过程但又比过程语言简单、高效、灵活和实用。
我们使用PLSQL来实现这个例子:
首先注意,oracle默认一次性只能开启300个光标,我们可以通过
“show parameters cursor”指令来查看光标目前的设置信息:
使用Command窗口:
光标的开启数是可以调整的,但是现在我们的数据不到300,所以调整方法
等下一个总结我们再介绍。
编写PLSQL程序:
declare --光标代表员工 cursor cemp is select empno,job from emp; pempno emp.empno%type; pjob emp.empjob%type;begin open cemp; loop fetch cemp into pempno,pjob; exit when cemp%notfound; --判断 if pjob='主管' then update emp set sal=sal+1000 where empno=pempno; elsif pjob='销售' then update emp set sal=sal+800 where empno=pempno; else update emp set sal=sal+400 where empno=pempno; end if; end loop; close cemp; dbms_output.put_line('完成');end;/
执行程序之前:
执行程序并commit之后,结果:
③带参数的光标
我们来查询某个部门的员工姓名
declare cursor cemp(pdno number) is select ename from emp where deptno=pdno; pename emp.ename%type;begin open cemp(20); loop fetch cemp into pename; exit when cemp%notfound; dbms_output.put_line(pename); end loop;end;/查询的是部门为20的所有员工的姓名,结果:
(2)例外
例外是程序设计语言提供的一种功能,用来增强程序的健壮性和容错性。
其实类似于Java的Exception。
Oracle的异常处理
A.系统定义的例外
--No_data_found(没有找到数据)
--Too_many_rows(select...into语句匹配多个行)
--Zero_Divide(被零除)
--Value_error(算术或转换错误)
--Timeout_on_resource(在等待资源时发生超时)
B.用户定义的例外
用户自己去定义的自定义例外。
例子:测试“被零除”的例外
declare pnum number;begin pnum:=1/0;exception when Zero_Divide then dbms_output.put_line('1:0不能做被除数!'); when Value_error then dbms_output.put_line('2:算术或转换错误!'); when others then dbms_output.put_line('3:其它错误!');end;/
结果:
用户自定义的例外,其实就是用户定义的一个变量
如
查询100号部门的员工姓名(100号部门是不存在的)
declare pename emp.ename%type; No_emp_found exception; --自定义例外 cursor cemp is select ename from emp where deptno=100;begin open cemp; fetch cemp into pename; if cemp%notfound then raise No_emp_found; --抛出自定义异常 end if; close cemp;exception when No_emp_found then dbms_output.put_line('1:没有找到员工!'); when others then dbms_output.put_line('2:其它错误!');end;/结果:
由于我们在close cemp;之前就raise了一个异常,oracle在抛出异常之后,就会
找没有关闭的光标,然后自动关闭掉。但有时候在复杂环境下,自动关闭往往是
不保险的,所以这里我们要自己手动关闭光标。
我们在exception的判断中,可以判断一下光标是否关闭,如果没有关闭就关闭它。
if cemp%isopen then close cemp;endif;转载请注明出处:http://blog.csdn.net/acmman/article/details/52413428
0 0
- 【oracle学习】7.plsql光标和例外
- PLSQL学习——例外
- oracle学习之例外
- Oracle 10g处理例外(即sql异常)学习二——自定义例外和非预定义例外
- PLSQL学习——光标
- Oracle例外表学习笔记
- PLSQL光标和包使用
- Oracle中PLSQL中一个例外的写法
- oracle----例外
- oracle光标的学习
- oracle学习之光标
- oracle plsql学习一
- oracle PLSQL 学习二
- oracle plsql 学习三
- oracle plsql 学习四
- oracle之plSql学习
- Oracle plsql学习笔记
- oracle学习PLSQL编程
- 内存泄漏的检测;leakCanary
- Java中List与Array的转换
- 上传图片并生成缩略图
- Ubuntu(14.04)Kerberos LDAP配置
- 关于checkbox的全选和反选实例
- 【oracle学习】7.plsql光标和例外
- lua 字符串匹配
- 一个漂亮的输出MySql数据库表结构的PHP页面
- React Router 使用教程
- 使用git建立本地仓储管理代码
- android开发中怎么在界面上实现曲线图
- 运维
- C# explicit关键字的作用
- 基于dubbo框架下的RPC通讯协议性能测试