Java调用Oracle存储过程示例

最新推荐文章于 2024-09-12 14:35:28 发布

BayardZhang

最新推荐文章于 2024-09-12 14:35:28 发布

阅读量691

点赞数

文章标签：存储 oracle java login string date

本文链接：https://blog.csdn.net/BayardZhang/article/details/5865250

版权

Oracle的存储过程见下面4种：

1、无返回值的存储过程
create or replace procedure sys_login_count is
var_flag_logindate date;
var_temp    varchar2(1000);
   var_max_date   date;
begin
      select t.flag_logindate into var_flag_logindate from sys_login_log_flag t;
      select max(t.logon_date) into var_max_date from sys_login_log t;
      if (var_flag_logindate is null or var_max_date>var_flag_logindate) then
         delete from sys_login_log_flag;
         commit;
         insert into sys_login_log_flag(flag_logindate)(select max(t.logon_date) from sys_login_log t);
         delete from sys_login_log_temp;
         commit;
         insert into sys_login_log_temp (portaluri,logindate,num) (select t.portaluri,t.logindate,t.num from view_login_dw_byday_table t);
         insert into sys_login_log_temp (portaluri,logindate,num) (select t.portaluri,t.logindate,t.num from (view_login_count_byday1@hndllink) t);

END IF;
commit;

      exception
         when others then
         rollback;
      var_temp := SQLERRM;
      dbms_output.put_line(var_temp);

end sys_login_count;
2、有返回值（但返回的不是列表结果）的存储过程
create or replace procedure procdure_return_nolist(portal in view_login_dw_day0.portaluri%type,
lgdate out view_login_dw_day0.logindate%type, counts out view_login_dw_day0.num%type ) is
--in 传入参数 out 传出参数
begin
select t.logindate,t.num
         into lgdate,counts
         from view_login_dw_day0 t where t.portaluri=portal ;
end procdure_return_nolist;
3、返回一个列表结果集
先创建PACKAGE
CREATE OR REPLACE PACKAGE login_count_package
AS
   TYPE proccursor IS REF CURSOR;
END login_count_package;

再创建存储过程
create or replace procedure sys_login_acount(proccursor out login_count_package.proccursor) is
begin
    --打开游标，查询返回结果集
      OPEN proccursor
         FOR
           select *
              from view_login_dw_sum ;
end sys_login_acount;
4、返回多个列表集
类似第3条再创建一个PACKAGE
CREATE OR REPLACE PACKAGE login_count_package2
AS
   TYPE proccursor2 IS REF CURSOR;
END login_count_package2;
利用以上创建的2个package

再创建存储过程
create or replace procedure login_acount_2_cursor(proccursor2 out login_count_package2.proccursor2,
proccursor out login_count_package.proccursor) is
begin
   --打开游标，查询返回结果集
      OPEN proccursor2
         FOR
           select *
              from view_login_dw_day0 ;
    --打开游标，查询返回结果集
      OPEN proccursor
         FOR
           select *
              from view_login_dw_day1 ;
end login_acount_2_cursor;

以下是调用Oracle4种存储过程对应的java代码

package demo;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;

/**
 * 测试Oracle 的 存储过程
 * @author 张贝
 * */
public class TestOracleProcedure {
	
//	 Oracle 驱动类
	private static String oracleDriver = "oracle.jdbc.driver.OracleDriver";
	// URL
	private static String oracleUrl = "jdbc:oracle:thin:@127.0.0.1:1521:orcl";

	private static String username = "portal1q";
	// database password
	private static String password = "portal1q";
	
	
//	 util method to get connection.
	public static Connection getConnection(String dataDriver,String url,String userName,String password) {
		Connection connection = null;
		try {
			Class.forName(dataDriver);
			connection = DriverManager.getConnection(url,userName,password);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return connection;
	}
	
	
//	 util method to close connection.
	public static void close(Connection con, Statement stm, ResultSet rs) {
		if (rs != null) {
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		
		if (stm != null) {
			try {
				stm.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		
		if (con != null) {
			try {
				con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	
		
	/**
	 * 调用无返回值的存储过程
	 * */
	public void testNoReturn(){
		CallableStatement callableStmt=null;
		
		Connection con=getConnection(oracleDriver,oracleUrl,username,password);
		try {
			callableStmt = con.prepareCall("call sys_login_count()");
			callableStmt.execute();
			System.out.println("Success");

		} catch (SQLException e) {
			e.printStackTrace();
			
		}finally{
			close(con,callableStmt,null);
		}

		
	}
	
	/**
	 * 调用返回 不是 结果集的存储过程
	 * */
	public void testReturnNoList(){
		CallableStatement callableStmt=null;
		ResultSet rs=null;
		
		Connection con=getConnection(oracleDriver,oracleUrl,username,password);
		try {
			callableStmt = con.prepareCall("call procdure_return_nolist(?,?,?)");
			callableStmt.setString(1, "/hepcoweb/appmanager/dj/portal");//传入参数
			callableStmt.registerOutParameter(2, Types.VARCHAR);//传出参数
			callableStmt.registerOutParameter(3, Types.NUMERIC);//传出参数
			callableStmt.execute();
			String date= callableStmt.getString(2);
			int num= callableStmt.getInt(3);
		
			System.out.println("date="+date+"  num="+num);

		} catch (SQLException e) {
			e.printStackTrace();
			
		}finally{
			close(con,callableStmt,rs);
		}
	}

	
	/**
	 * 调用返回 一个 结果集的存储过程
	 * */
	public void testReturnList(){
		CallableStatement callableStmt=null;
		ResultSet rs=null;
		
		Connection con=getConnection(oracleDriver,oracleUrl,username,password);
		try {
//			callableStmt = con.prepareCall("call sys_login_acount(?)");
			callableStmt = con.prepareCall("call sys_login_count(?)");
			//Oracle存储过程中要利用游标
			callableStmt.registerOutParameter(1, oracle.jdbc.driver.OracleTypes.CURSOR);
			callableStmt.execute();
			rs = (ResultSet) callableStmt.getObject(1);
			while (rs.next()){
				String department=rs.getString("department");
				int c=rs.getInt("c");
				int c0=rs.getInt("c0");
				int c1=rs.getInt("c1");
				int c2=rs.getInt("c2");
				System.out.println("portaluri="+department+"    c="+c+"  c0="+c0);
				
			}
			System.out.println("Success");

		} catch (SQLException e) {
			e.printStackTrace();
			
		}finally{
			close(con,callableStmt,rs);
		}
	}

	
	/**
	 * 调用返回  两个  结果集的存储过程
	 * */
	public void testReturn2List(){
		CallableStatement callableStmt=null;
		ResultSet rs=null;
		ResultSet rs2=null;
		
		Connection con=getConnection(oracleDriver,oracleUrl,username,password);
		try {
			callableStmt = con.prepareCall("call login_acount_2_cursor(?,?)");
//			callableStmt = con.prepareCall("call sys_login_count(?)");
			//Oracle存储过程中要利用游标
			callableStmt.registerOutParameter(1, oracle.jdbc.driver.OracleTypes.CURSOR);
			callableStmt.registerOutParameter(2, oracle.jdbc.driver.OracleTypes.CURSOR);
			callableStmt.execute();
			rs = (ResultSet) callableStmt.getObject(1);
			rs2 = (ResultSet) callableStmt.getObject(2);
			while (rs.next()){
				String portaluri=rs.getString("portaluri");
				String logindate=rs.getString("logindate");
				int num=rs.getInt("num");
				System.out.println("portaluri="+portaluri+"    logindate="+logindate+"  num="+num);
			}
			System.out.println("======================================");
			while (rs2.next()){
				String portaluri=rs2.getString("portaluri");
				String logindate=rs2.getString("logindate");
				int num=rs2.getInt("num");
				System.out.println("portaluri="+portaluri+"    logindate="+logindate+"  num="+num);
			}

		} catch (SQLException e) {
			e.printStackTrace();
			
		}finally{
			close(con,callableStmt,rs);
		}
	}

	/**
	 * @param args
	 */
	public static void main(String[] args) {
		TestOracleProcedure test=new TestOracleProcedure();
//		test.testNoReturn();调用无返回值的存储过程
		test.testReturnNoList();//调用返回 不是 结果集的存储过程
//		test.testReturnList();//调用返回 一个 结果集的存储过程
//		test.testReturn2List();//调用返回  两个  结果集的存储过程

	}

}