游标
在写java程序中有结果集的概念,那么在pl/sql中也会用到多条记录,这时候我们就要用到游标,游标可以存储查询返回的多条数据。
游标可以理解为是PL/SQL中的结果集,我们通过游标可以提取结果集中的每行记录。
在声明区声明游标,语法如下:
cursor 游标名称 is SQL语句; |
---|
使用游标语法
open 游标名称loop fetch 游标名称 into 变量 exit when 游标名称%notfoundend loop;close 游标名称 |
---|
范例1:使用游标方式输出emp表中的员工编号和姓名
declare cursor pc is select * from emp; pemp emp%rowtype;begin open pc; loop fetch pc into pemp; exit when pc%notfound; dbms_output.put_line(pemp.empno || ' ' || pemp.ename); end loop; close pc;end; |
---|
范例2:使用游标方式输出emp表中指定部门员工的编号和姓名。
declare cursor pc(dno emp.deptno%type) is select * from emp where deptno=dno; pemp emp%rowtype;begin open pc(20); loop fetch pc into pemp; exit when pc%notfound; dbms_output.put_line(pemp.empno || ' ' || pemp.ename); end loop; close pc;end; |
---|
异常
异常是程序设计语言提供的一种功能,用来增强程序的健壮性和容错性。
系统定义异常
命名的系统异常 | 产生原因 |
---|---|
ACCESS_INTO_NULL | 未定义对象 |
CASE_NOT_FOUND | CASE 中若未包含相应的 WHEN ,并且没有设置 ELSE 时 |
COLLECTION_IS_NULL | 集合元素未初始化 |
CURSER_ALREADY_OPEN | 游标已经打开 |
DUP_VAL_ON_INDEX | 唯一索引对应的列上有重复的值 |
INVALID_CURSOR | 在不合法的游标上进行操作 |
INVALID_NUMBER | 内嵌的 SQL 语句不能将字符转换为数字 |
NO_DATA_FOUND | 使用 select into 未返回行,或应用索引表未初始化的元素时 |
TOO_MANY_ROWS | 执行 select into 时,结果集超过一行 |
ZERO_DIVIDE | 除数为 0 |
SUBSCRIPT_BEYOND_COUNT | 元素下标超过嵌套表或 VARRAY 的最大值 |
SUBSCRIPT_OUTSIDE_LIMIT | 使用嵌套表或 VARRAY 时,将下标指定为负数 |
VALUE_ERROR | 赋值时,变量长度不足以容纳实际数据 |
LOGIN_DENIED | PL/SQL 应用程序连接到 oracle 数据库时,提供了不正确的用户名或密码 |
NOT_LOGGED_ON | PL/SQL 应用程序在没有连接 oralce 数据库的情况下访问数据 |
PROGRAM_ERROR | PL/SQL 内部问题,可能需要重装数据字典& pl./SQL 系统包 |
ROWTYPE_MISMATCH | 宿主游标变量与 PL/SQL 游标变量的返回类型不兼容 |
SELF_IS_NULL | 使用对象类型时,在 null 对象上调用对象方法 |
STORAGE_ERROR | 运行 PL/SQL 时,超出内存空间 |
SYS_INVALID_ID | 无效的 ROWID 字符串 |
TIMEOUT_ON_RESOURCE | Oracle 在等待资源时超时 |
范例1:写出被0除的异常的plsql程序
declare pnum number;begin pnum := 1 / 0;exception when zero_divide then dbms_output.put_line('被0除'); when value_error then dbms_output.put_line('数值转换错误'); when others then dbms_output.put_line('其他错误');end; |
---|
用户也可以自定义异常,在声明中来定义异常
DECLARE
My_job char(10);
v_sal emp.sal%type;
No_data exception;
cursor c1 is select distinct job from emp order by job;
如果遇到异常我们要抛出raise no_data;
范例2:添加工资等级数据时,如果losal大于hisal抛出异常“区间设置有误”
declare v_grade salgrade.grade%type:=7; v_losal salgrade.losal%type:=20001; v_hisal salgrade.hisal%type:=20000; v_sal_error exception;begin if v_losal>= v_hisal then raise v_sal_error; else insert into salgrade values (v_grade,v_losal,v_hisal); commit; end if;exception when v_sal_error then dbms_output.put_line('区间设置有误');end; |
---|
存储过程(重点)
存储过程(Stored Procedure)是在大型数据库系统中,一组为了完成特定功能的SQL 语句集,经编译后存储在数据库中,用户通过指定存储过程的名字并给出参数(如果该存储过程带有参数)来执行它。存储过程是数据库中的一个重要对象,任何一个设计良好的数据库应用程序都应该用到存储过程。
创建存储过程语法:
create [or replace] PROCEDURE 过程名[(参数名 in/out 数据类型)] AS | ISbegin PLSQL子程序体;End [过程名]; |
---|
范例1:给指定的员工涨100工资,并打印出涨前和涨后的工资
分析:我们需要使用带有参数的存储过程
create or replace procedure addSal1(eno in number) is pemp emp%rowtype;begin select * into pemp from emp where empno = eno; update emp set sal = sal + 100 where empno = eno; dbms_output.put_line('涨工资前' || pemp.sal || '涨工资后' || (pemp.sal + 100)); commit;end addSal1; |
---|
调用方式有两种
方式一:
call addsal1(7902); -- Call 存储过程名称 |
---|
方式二:
begin addsal1(7902); end; |
---|
存储函数(重点)
语法:
create or replace function 函数名(参数1 in 数据类型, 参数2 out 数据类型,…) return 数据类型 as|is 结果变量 数据类型;Begin Pl/sql程序体; return(结果变量);end[函数名]; |
---|
存储过程和存储函数的区别
一般来讲,过程和函数的区别在于函数可以有一个返回值;而过程没有返回值。
但过程和函数都可以通过out指定一个或多个输出参数。我们可以利用out参数,在过程和函数中实现返回多个值。
范例:使用存储函数来查询指定员工的年薪
create or replace function empincome(eno in emp.empno%type) return number is psal emp.sal%type; pcomm emp.comm%type;begin select t.sal into psal from emp t where t.empno = eno; return psal * 12 + nvl(pcomm, 0);end; |
---|
使用存储过程来替换上面的例子
create or replace procedure empincomep(eno in emp.empno%type, income out number) is psal emp.sal%type; pcomm emp.comm%type;begin select t.sal, t.comm into psal, pcomm from emp t where t.empno = eno; income := psal*12+nvl(pcomm,0);end empincomep; |
---|
调用:
declare income number;begin empincomep(7369, income); dbms_output.put_line(income);end; |
---|
触发器
数据库触发器是一个与表相关联的、存储的PL/SQL程序。每当一个特定的数据操作语句(Insert,update,delete)在指定的表上发出时,Oracle自动地执行触发器中定义的语句序列。
l 数据确认
l 实施复杂的安全性检查
l 做审计,跟踪表上所做的数据操作等
l 数据的备份和同步
语句级触发器 :在指定的操作语句操作之前或之后执行一次,不管这条语句影响 了多少行 。
行级触发器(FOR EACH ROW) :触发语句作用的每一条记录都被触发。在行级触 发器中使用old和new伪记录变量, 识别值的状态。
语法:
CREATE [or REPLACE] TRIGGER 触发器名 {BEFORE | AFTER} {DELETE | INSERT | UPDATE [OF 列名]} ON 表名 [FOR EACH ROW [WHEN(条件) ] ]declare ……begin PLSQL 块 End [触发器名]; |
---|
范例:添加员工后打印一句话“一个新员工添加成功”
create or replace trigger testTrigger after insert on person declare -- local variables herebegin dbms_output.put_line('一个员工被添加');end testTrigger; |
---|
范例:今天(2015-06-16)系统维护,不能添加员工数据
create or replace trigger tri_add_empbeforeinsert on empdeclare v_today varchar2(20);begin select to_char(sysdate,'yyyy-mm-dd') into v_today from dual; if v_today='2016-06-11' then raise_application_error(-20001,'今天系统维护,不能添加员工数据'); end if;end; |
---|
当执行添加时会报错
在触发器中触发语句与伪记录变量的值
触发语句 | :old | :new |
---|---|---|
Insert | 所有字段都是空(null) | 将要添加的数据 |
Update | 更新以前该行的值 | 更新后的值 |
delete | 删除以前该行的值 | 所有字段都是空(null) |
范例:判断员工涨工资之后的工资的值一定要大于涨工资之前的工资
create or replace trigger addsal4p before update of sal on emp for each rowbegin if :old.sal >= :new.sal then raise_application_error(-20002, '涨前的工资不能大于涨后的工资'); end if;end; |
---|
调用
update emp t set t.sal = t.sal - 1;
需求:使用序列,触发器来模拟mysql中自增效果
1. 创建序列
(1)建立表
复制代码 代码如下:
create table user ( id number(6) primary key, name varchar2(30) not null )
(2)建立序列SEQUENCE
代码如下:
create sequence user_seq;
分析:创建一个基于该表的before insert 触发器,在触发器中使用刚创建的SEQUENCE。
代码如下:
create or replace trigger user_trigger
before insert on user for each row
begin
select user_seq.nextval into:new.id from sys.dual ;
end;
insert into itcastuser(name) values('aa');commit;insert into itcastuser(name) values('bb');commit; |
---|
Java代码访问Oracle对象(掌握)
可以在虚拟机中xp的oracle安装目录下找到jar包 :ojdbc14.jar
String driver="oracle.jdbc.OracleDriver";
String url="jdbc:oracle:thin:@192.168.106.10:1521:orcl";
String username="scott";
String password="tiger";
--统计年薪的过程
create or replace procedure proc_countyearsal(eno in number,esal out number)
as
begin
select sal*12+nvl(comm,0) into esal from emp where empno=eno;
end;
--调用
declare
esal number;
begin
proc_countyearsal(7839,esal);
dbms_output.put_line(esal);
end;
@Test
public void testCallProcedure(){
try {
//加载数据库驱动
Class.forName("oracle.jdbc.driver.OracleDriver");
//获取数据库连接
Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@192.168.19.10:1521:orcl","scott","tiger");
//创建preperedStatement
CallableStatement cst = conn.prepareCall("{call pro_emp_totalsal(?,?)}");
cst.setInt(1, 7654);
cst.registerOutParameter(2, OracleTypes.NUMBER);
//执行语句
cst.execute();
//处理结果
System.out.println(cst.getObject(2));
//释放资源
cst.close();
conn.close();
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
}
--统计年薪的函数
create or replace function fun_countyearsal(eno in number)
return number
as
esal number:=0;
begin
select sal*12+nvl(comm,0) into esal from emp where empno=eno;
return esal;
end;
--调用
declare
esal number;
begin
esal:=fun_countyearsal(7839);
dbms_output.put_line(esal);
end;
@Test
public void testCallFunction(){
try {
//加载数据库驱动
Class.forName("oracle.jdbc.driver.OracleDriver");
//获取数据库连接
Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@192.168.19.10:1521:orcl","scott","tiger");
//创建preperedStatement
CallableStatement cst = conn.prepareCall("{?=call fun_emp_totalsal(?)}"); //大括号
cst.registerOutParameter(1, OracleTypes.NUMBER);
cst.setInt(2, 7654);
//执行语句
cst.execute();
//处理结果
System.out.println(cst.getObject(1));
//释放资源
cst.close();
conn.close();
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
}
--定义过程,输入部门编号,返回部门下所有员工信息
create or replace procedure proc_cursor_ref(dno in number,empList out sys_refcursor)
as
begin
open empList for select * from emp where deptno = dno;
end;
--pl/sql中调用
declare
mycursor_c sys_refcursor;
myempc emp%rowtype;
begin
proc_cursor_ref(20,mycursor_c);
loop
fetch mycursor_c into myempc;
exit when mycursor_c%notfound;
dbms_output.put_line(myempc.empno||','||myempc.ename);
end loop;
close mycursor_c;
end;
2)java代码调用游标类型的out参数
2. @Test
3. public void testCallProcedureOutCursot(){
4. try {
5. //加载数据库驱动
6. Class.forName("oracle.jdbc.driver.OracleDriver");
7. //获取数据库连接
8. Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@192.168.19.10:1521:orcl","scott","tiger");
9. //创建preperedStatement
10. CallableStatement cst = conn.prepareCall("{call pro_emp_list(?,?)}"); //大括号
11. cst.setInt(1, 30);
12. cst.registerOutParameter(2, OracleTypes.CURSOR);
13. //执行语句
14. cst.execute();
15. //处理结果
16. ResultSet rs = ((OracleCallableStatement)cst).getCursor(2);
17. while(rs.next()){
18. System.out.println(rs.getObject(1)+","+rs.getObject(2)+","+rs.getObject(3)+","+rs.getObject(4));
19. }
20. System.out.println();
21. //释放资源
22. cst.close();
23. conn.close();
24. } catch (ClassNotFoundException e) {
25. e.printStackTrace();
26. } catch (SQLException e) {
27. e.printStackTrace();
28. }
29. }
3)提取公用代码
package cn.itcast.oracletest;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import org.junit.Before;
import org.junit.Test;
import oracle.jdbc.OracleCallableStatement;
import oracle.jdbc.driver.OracleTypes;
import oracle.jdbc.oracore.OracleType;
/**
* @Title: OracleFirst.java
* @Package cn.itcast.oracletest
* @Description: 使用jabc代用oracle对象(表,存储过程,存储函数)
* @Company:www.itcast.cn
*
* @author itcast.DZ
* @date 2016年7月6日 上午11:59:01
*/
public class OracleSecond {
//数据库的连接
private Connection conn;
private PreparedStatement pst;
private ResultSet rs;
private CallableStatement cst;
@Before
public void init(){
try {
//加载数据库驱动
Class.forName("oracle.jdbc.driver.OracleDriver");
//获取数据库连接
conn = DriverManager.getConnection("jdbc:oracle:thin:@192.168.19.10:1521:orcl","scott","tiger");
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
}
/**
*
* @Description: 查询员工信息列名
* @return void
*/
@Test
public void testFindEmplist(){
try {
//创建preperedStatement
pst = conn.prepareStatement("select * from emp");
//执行语句
rs = pst.executeQuery();
//处理结果
while(rs.next()){
System.out.println("姓名:"+rs.getString("ename")+",工作"+rs.getObject(3));
}
//释放资源
closeAll(rs, pst, null, conn);
} catch (SQLException e) {
e.printStackTrace();
} finally{
}
}
//Test注解方法,不能有返回值,不能有参数,不能是private
/**
*
* @Description: 调用存储过程
* @return void
* {call <procedure-name>[(<arg1>,<arg2>, ...)]}
* create or replace procedure pro_emp_totalsal(eno in number,empsal out number)
as
begin
select sal*12+nvl(comm,0) into empsal from emp where empno=eno;
end;
*/
@Test
public void testCallProcedure(){
try {
//创建preperedStatement
cst = conn.prepareCall("{call pro_emp_totalsal(?,?)}");
cst.setInt(1, 7654);
cst.registerOutParameter(2, OracleTypes.NUMBER);
//执行语句
cst.execute();
//处理结果
System.out.println(cst.getObject(2));
//释放资源
closeAll(null, null, cst, conn);
} catch (SQLException e) {
e.printStackTrace();
}
}
/**
*
* @Description: Jdbc调用存储函数
* @return void
* {?= call <procedure-name>[(<arg1>,<arg2>, ...)]}
*create or replace function fun_emp_totalsal(eno in number) return number
as
totalsal number;
begin
select sal*12+nvl(comm,0) into totalsal from emp where empno=eno;
return totalsal;
end;
*/
@Test
public void testCallFunction(){
try {
//创建preperedStatement
cst = conn.prepareCall("{?=call fun_emp_totalsal(?)}"); //大括号
cst.registerOutParameter(1, OracleTypes.NUMBER);
cst.setInt(2, 7654);
//执行语句
cst.execute();
//处理结果
System.out.println(cst.getObject(1));
//释放资源
closeAll(null, null, cst, conn);
} catch (SQLException e) {
e.printStackTrace();
}
}
/**
*
* @Description: 调用存储过程(包含一个输出参数为游标类型)
* @return void
* create or replace procedure pro_emp_list(dno in number, emplist out sys_refcursor)
as
begin
open emplist for select * from emp where deptno = dno;
end;
*/
@Test
public void testCallProcedureOutCursot(){
try {
//创建preperedStatement
cst = conn.prepareCall("{call pro_emp_list(?,?)}"); //大括号
cst.setInt(1, 30);
cst.registerOutParameter(2, OracleTypes.CURSOR);
//执行语句
cst.execute();
//处理结果
rs = ((OracleCallableStatement)cst).getCursor(2);
while(rs.next()){
System.out.println(rs.getObject(1)+","+rs.getObject(2)+","+rs.getObject(3)+","+rs.getObject(4));
}
System.out.println();
//释放资源
closeAll(rs, null, cst, conn);
}catch (SQLException e) {
e.printStackTrace();
}
}
/**
*
* @Description: 释放资源
* @param rs
* @param pst
* @param cst
* @param conn
* @return void
*/
public void closeAll(ResultSet rs, PreparedStatement pst, CallableStatement cst, Connection conn){
try {
if(rs != null){
rs.close();
}
if(pst != null){
pst.close();
}
if(cst != null){
cst.close();
}
if(conn != null){
conn.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}