Oracle应用实战八(完结)——存储过程、函数+对象曹组

游标

在写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自动地执行触发器中定义的语句序列。

1.触发器作用

l 数据确认

l 实施复杂的安全性检查

l 做审计,跟踪表上所做的数据操作等

l 数据的备份和同步

2.触发器的类型

语句级触发器 :在指定的操作语句操作之前或之后执行一次,不管这条语句影响 了多少行 。

行级触发器(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;

3.触发器实际应用

需求:使用序列,触发器来模拟mysql中自增效果

1. 创建序列

(1)建立表

复制代码 代码如下:

create table user  (       id   number(6) primary key,       name   varchar2(30) not null   )

(2)建立序列SEQUENCE

代码如下:

create sequence user_seq;

2.创建自增的触发器

分析:创建一个基于该表的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;

3.测试效果

insert into itcastuser(name) values('aa');commit;insert into itcastuser(name) values('bb');commit;

Java代码访问Oracle对象(掌握)

1.java连接oracle的jar包

可以在虚拟机中xp的oracle安装目录下找到jar包 :ojdbc14.jar

2.数据库连接字符串

String driver="oracle.jdbc.OracleDriver";

String url="jdbc:oracle:thin:@192.168.106.10:1521:orcl";

String username="scott";

String password="tiger";

3.实现过程与函数的调用

1)调用过程

a.过程定义
--统计年薪的过程

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;
b.过程调用
@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();

}

}

2.调用函数

1)函数定义
--统计年薪的函数

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;
2)函数调用
@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();

}

}

4.游标引用的java测试

1)定义过程,并返回引用型游标

--定义过程,输入部门编号,返回部门下所有员工信息

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

}

}



}

原文发布于微信公众号 - Java帮帮(javahelp)

原文发表时间:2017-03-29

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏Python

Django---ORM操作大全

前言 Django框架功能齐全自带数据库操作功能,本文主要介绍Django的ORM框架 到目前为止,当我们的程序涉及到数据库相关操作时,我们一般都会这么搞:...

95910
来自专栏JAVA同学会

solr的基本概念

  大家可以把solr搜索引擎看成一个数据库,不过是基于内存的。它可以存储信息,并且根据你的查询条件返回你想要的信息。

1592
来自专栏北京马哥教育

10分钟学会理解和解决MySQL乱码问题

本文将详细介绍MySQL乱码的成因和具体的解决方案。在阅读本文之前,强烈建议对字符集编码概念还比较模糊的同学 阅读下博主之前对相关概念的一篇科普:十分钟搞清字符...

3018
来自专栏java系列博客

oracle 中start with ... connect by prior 子句的用法

1782
来自专栏西安-晁州

mysql随笔

Mysql学习笔记 1、操作数据库 use dataBaseName  //使用数据库 show databases   //显示所有数据库 show tabl...

2010
来自专栏更流畅、简洁的软件开发方式

分页控件之分页算法 —— for SQL Server 版。

上两篇随笔: 我的分页控件(未完,待续)——控件件介绍及思路 我自己写的一个分页控件(源码和演示代码)PostBack分页版 for vs2003、SQL ...

2349
来自专栏张善友的专栏

LINQ to SQL集成到应用程序中需考虑的一些问题

1、LINQ to SQL集成到应用程序中需考虑的一个问题, 到底应该返回IQueryable<T>还是IQueryable? 或许这个列表还应该继续扩展为T,...

2086
来自专栏禹都一只猫博客

Python的flask:models.py来创建mysql数据库

1.1K9
来自专栏加米谷大数据

Hive的数据类型

本文介绍hive的数据类型,数据模型以及文件存储格式。这些知识大家可以类比关系数据库的相关知识。

1442
来自专栏前端儿

在PHP中使用MySQL Mysqli操作数据库 ,以及类操作方法

先来操作函数部分,普遍的MySQL 函数方法,但随着PHP5的发展,有些函数使用的要求加重了,有些则将废弃不用,有些则参数必填...

3603

扫码关注云+社区

领取腾讯云代金券