|
CREATE OR REPLACE PACKAGE MY_STEEL_SOFT
AS
--預存程序返回資料集的類型
TYPE ReturnDataSet IS REF CURSOR;
END MY_STEEL_SOFT;
create or replace function bb(beginDate in varchar2)
return My_steel_soft.ReturnDataSet
as
ACursor My_steel_soft.ReturnDataSet;
begin
open ACursor for select * from v_out_orderqry t
where t.dOrderDate>=beginDate;
--and t.dOrderDate< endDate;
--and t.sSalerGUID=orgguid
--or t.sBuyGUID=orgguid;
return ACursor;
end bb;
/**
* TestFun.java
* create on 2007-5-23
* Copyright 2010 MySteelSoft All Rights Reserved.
*/
package com.test;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
public class TestFun{
public static void main(String[] args)
{
Connection connection = null;
try{
Class.forName("oracle.jdbc.driver.OracleDriver");
}
catch(ClassNotFoundException e) {
System.out.println("資料庫驅動類沒找到");
}
try{
ResultSet rs = null;
String serverName = "192.168.18.21";
String portNumber = "1521";
String sid = "eTrade";
String url = "jdbc:oracle:thin:@" + serverName + ":" + portNumber + ":" + sid;
String username = "developer";
String password = "mysteelsoft";
connection = DriverManager.getConnection(url,username,password);
CallableStatement cstmt= null;;
cstmt = connection.prepareCall("{? = call bb(?)}");
//cstmt = connection.prepareCall("? = call eTrade.bb(?);");
cstmt.registerOutParameter(1,oracle.jdbc.OracleTypes.CURSOR);
cstmt.setString(2,"2006-12-12");
System.out.println(cstmt);
cstmt.execute();
rs = (ResultSet) cstmt.getObject(1);
if (rs != null) {
while (rs.next()) {
System.out.print(rs.getString(1)+" ");
System.out.print(rs.getString(2)+" ");
System.out.println(rs.getString(3));
}
}
cstmt.close();
connection.close();
}catch(SQLException e) {
e.printStackTrace();
}
}
}
/**
* TestFun.java
* create on 2007-5-23
* Copyright 2010 MySteelSoft All Rights Reserved.
*/
package com.test;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import javax.sql.rowset.CachedRowSet;
import com.sun.rowset.CachedRowSetImpl;
public class TestFun{
public static void main(String[] args)
{
Connection connection = null;
try{
Class.forName("oracle.jdbc.driver.OracleDriver");
}
catch(ClassNotFoundException e) {
System.out.println("資料庫驅動類沒找到");
}
try{
CachedRowSet crs = new CachedRowSetImpl();
ResultSet rs = null;
String serverName = "192.168.18.21";
String portNumber = "1521";
String sid = "eTrade";
String url = "jdbc:oracle:thin:@" + serverName + ":" + portNumber + ":" + sid;
String username = "developer";
String password = "mysteelsoft";
connection = DriverManager.getConnection(url,username,password);
CallableStatement cstmt= null;;
cstmt = connection.prepareCall("{? = call bb(?)}");
cstmt.registerOutParameter(1,oracle.jdbc.OracleTypes.CURSOR);
cstmt.setString(2,"2006-12-12");
cstmt.execute();
System.out.println(cstmt);
rs = (ResultSet) cstmt.getObject(1);
crs.populate(rs);
if (crs != null) {
while (crs.next()) {
System.out.print(crs.getString("SGUID")+" ");
System.out.print(crs.getString("SORDERID")+" ");
System.out.println(crs.getString("SSALERGUID"));
}
}
cstmt.close();
connection.close();
}catch(SQLException e) {
e.printStackTrace();
}
}
} |
