java程序调用存储过程

 

    PL/SQL子程序，很多情况下是给应用程序来调用的，所有我们要掌握使用其他编程语言来调用我们写好的存储过程。下面我们介绍下使用java调用Oracle的存储过程。

 

  准备代码:

package com.mscncn.plsql.util;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.SQLException;

public class DBUtil {

	static{

		try {

			Class.forName("oracle.jdbc.OracleDriver");

		} catch (ClassNotFoundException e) {

			e.printStackTrace();

		}

	}

	public static Connection getConntection(){

		Connection ct=null;

		try {

			ct = DriverManager.getConnection(

					"jdbc:oracle:thin:@192.168.0.25:1521:oracle",

					"scott", 

					"scott");

		} catch (SQLException e) {

			e.printStackTrace();

		}

		return ct;

	}

}

 

create or replace package pro_pk is

   type pager_cursor is ref cursor;

   procedure add_dept(dept_no in number,dept_name in varchar2,location in varchar2);

   procedure delete_dept(dept_no in number,num out number);

end pro_pk;

create or replace package body pro_pk is

   procedure add_dept(dept_no in number,dept_name in varchar2,location in varchar2)

     is

         exp_remaining exception;

         pragma exception_init(exp_remaining,-1);/*非预定义错误，-1是违反唯一约束*/

     begin 

       insert into dept values(dept_no,dept_name,location);

       if sql%found then /*隐式游标，sql*/

         return 1;

       else 

         return 0;

       end if;

     exception 

       when exp_remaining then

         dbms_output.put_line('违反唯一约束.');

     end add_dept;

       

     procedure delete_dept(dept_no in number,num out number)

       is

     begin 

         delete from dept where deptno=dept_no;

         if sql%found then 

           num:=1;

         else 

           num:=1;

         end if;

     end delete_dept;

end pro_pk;

 create or replace package pageUtil is

 

  type page_cursor is ref cursor;--定义一个游标类型

  

  procedure pager(

        tName in varchar2, --表名

        pageNum in number, --页数

        pageSize in number,--每页记录数

        totalRecord out number,--总记录数

        totalPage out number,--总页数

        p_cursor out page_cursor);

        

end pageUtil;

create or replace package body pageUtil is 

       

    procedure pager(

        tName in varchar2, --表名

        pageNum in number, --页数

        pageSize in number,--每页记录数

        totalRecord out number,--总记录数

        totalPage out number,--总页数

        p_cursor out page_cursor) is

        

        --定义sql语句字符串

        v_sql varchar2(1000);

        --分页开始位置，与结束位置

        v_begin number:=(pageNum-1)*pageSize+1;

        v_end number:=pageNum*pageSize;

    begin

      v_sql:='select * from ( select t.*,rownum rn from '

        ||tName||' t where rownum<='||v_end||') where rn>='||v_begin;

      --把游标和sql关联

      dbms_output.put_line(v_sql);

      open p_cursor for  v_sql;

      --计算totalRecord与totalPage

      v_sql:='select count(*) from '||tName;

      --

      execute immediate v_sql into totalRecord;

      if mod(totalRecord,pageSize)=0 then 

        totalPage:=totalRecord/pageSize;

      else

        totalPage:=totalRecord/pageSize+1;

      end if;

      --关闭游标,这儿需要注意，如果我们在java程序中使用cursor,那么就一定不能关闭cursor

      --否则关闭cursor后，java程序中返回的结果集就是null

      --close p_cursor;

    end pager;

end pageUtil;

1. java调用没有返回值的存储过程。

/**

	 * java调用没有返回值的存储过程

	 */

	@Test

	public void proNoReulstTest(){

		Connection ct=DBUtil.getConntection();

		try {

			CallableStatement cs=ct.prepareCall("{call pro_pk.add_dept(?,?,?)}");

			cs.setInt(1, 13);

			cs.setString(2, "java开发部");

			cs.setString(3, "中国信阳");

			cs.execute();

		} catch (SQLException e) {

			e.printStackTrace();

		}finally{

			try {

				ct.close();

			} catch (SQLException e) {

				e.printStackTrace();

			}

		}

	}

2. java程序调用有返回值的存储过程

/**

	 * java调用有返回值的存储过程(返回值类型为number)

	 */

	@Test

	public void proHasReulstTest(){

		Connection ct=DBUtil.getConntection();

		try {

			CallableStatement cs=ct.prepareCall("{call pro_pk.delete_dept(?,?)}");

			cs.setInt(1, 13);

			//注册第二个参数为存储过程的返回值

			cs.registerOutParameter(2, OracleType.STYLE_INT);

			cs.execute();

			//通过参数的索引，来获取存储过程的返回值，索引从1开始

			int num=cs.getInt(2);

			System.out.println(num==1?"删除成功":"删除失败");

		} catch (SQLException e) {

			e.printStackTrace();

		}finally{

			try {

				ct.close();

			} catch (SQLException e) {

				e.printStackTrace();

			}

		}

	}

3. java程序调用存储过程返回值为游标

/**

	 * 存储过程返回一个游标

	 */

	@Test

	public void proReturnCursorTest(){

		Connection ct=DBUtil.getConntection();

		try {

			CallableStatement cs=ct.prepareCall("{call pageUtil.pager(?,?,?,?,?,?)}");

			cs.setString(1, "emp");

			cs.setInt(2, 2);

			cs.setInt(3, 5);

			cs.registerOutParameter(4, OracleTypes.NUMBER);

			cs.registerOutParameter(5, OracleTypes.NUMBER);

			cs.registerOutParameter(6, OracleTypes.CURSOR);

			cs.execute();

			//通过参数的索引，来获取存储过程的返回值，索引从1开始

			int totalRecord=cs.getInt(4);

			int totalPage=cs.getInt(5);

			ResultSet rs=(ResultSet)cs.getObject(6);

			System.out.println("总记录数为:"+totalRecord+",总页数为:"+totalPage);

			while(rs.next()){

				System.out.println("雇员编号:"+rs.getInt("empno")+",雇员姓名:"+rs.getString("ename"));

			}

		} catch (SQLException e) {

			e.printStackTrace();

		}finally{

			try {

				ct.close();

			} catch (SQLException e) {

				e.printStackTrace();

			}

		}

	}