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();//调用返回 两个 结果集的存储过程 } }