本文最后一次修改日期：2013-07-01
在Fusion Order Demo的StandaloneExamples中的AdvancedViewObjectsExamples中的ViewObjectOnRefCursor，介绍了如何使用ResultSet查询结果集实现View Object。
其中，Ref Cursor是使用PL-SQL实现的。
关于Fusion Order Demo，请参考《发布与运行 Oracle Fusion Order Demo》。
关于VO中的重要方法的介绍，请参考《VO高级使用技巧之一：可重写的重要的方法》。
1. PL-SQL代码
REM For security reasons, the FOD user is not given create procedure privileges
REM To run this script, issue the following statement as system:
REM grant create procedure to fod;
REM To back out this change, issue the following as FOD:
REM drop package RefCursorExample;
REM And run the following as system:
REM revoke create procedure from fod;
CREATE OR REPLACE PACKAGE RefCursorExample IS
TYPE ref_cursor IS REF CURSOR;
FUNCTION get_orders_for_customer(p_email VARCHAR2) RETURN ref_cursor;
FUNCTION count_orders_for_customer(p_email VARCHAR2) RETURN NUMBER;
END RefCursorExample;
.
/
show errors
CREATE OR REPLACE PACKAGE BODY RefCursorExample IS
FUNCTION get_orders_for_customer(p_email VARCHAR2) RETURN ref_cursor IS
the_cursor ref_cursor;
BEGIN
OPEN the_cursor FOR
SELECT o.order_id, o.order_status_code, o.order_total
FROM orders o, persons p
WHERE o.customer_id = p.person_id
AND p.email = p_email;
RETURN the_cursor;
END get_orders_for_customer;
FUNCTION count_orders_for_customer(p_email VARCHAR2) RETURN NUMBER IS
the_count NUMBER;
BEGIN
SELECT COUNT(*)
INTO the_count
FROM orders o, persons p
WHERE o.customer_id = p.person_id
AND p.email = p_email;
RETURN the_count;
END count_orders_for_customer;
END RefCursorExample;
.
/
show errors
package devguide.advanced.refcursor;
import java.math.BigDecimal;
import java.sql.CallableStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.sql.Timestamp;
import oracle.jbo.JboException;
import oracle.jbo.domain.Date;
import oracle.jbo.domain.Number;
import oracle.jbo.server.SQLBuilder;
import oracle.jbo.server.ViewObjectImpl;
import oracle.jbo.server.ViewRowImpl;
import oracle.jbo.server.ViewRowSetImpl;
import oracle.jdbc.OracleTypes;
// ---------------------------------------------------------------------
// --- File generated by Oracle ADF Business Components Design Time.
// --- Custom code may be added to this class.
// --- Warning: Do not modify method signatures of generated methods.
// ---------------------------------------------------------------------
public class OrdersForCustomerImpl extends ViewObjectImpl {
/**This is the default constructor (do not remove).
*/
public OrdersForCustomerImpl() {
}
/**
* Overridden framework method.
*
* Executed when the framework needs to issue the database query for
* the query collection based on this view object. One view object
* can produce many related result sets, each potentially the result
* of different bind variable values. If the rowset in query is involved
* in a framework-coordinated master/detail viewlink, then the params array
* will contain one or more framework-supplied bind parameters. If there
* are any user-supplied bind parameter values, they will *PRECEED* the
* framework-supplied bind variable values in the params array, and the
* number of user parameters will be indicated by the value of the
* numUserParams argument.
*/
protected void executeQueryForCollection(Object qc, Object[] params, int numUserParams) {
storeNewResultSet(qc, retrieveRefCursor(qc, params));
super.executeQueryForCollection(qc, params, numUserParams);
}
/**
* Overridden framework method.
*
* Wipe out all traces of a built-in query for this VO
*/
protected void create() {
getViewDef().setQuery(null);
getViewDef().setSelectClause(null);
setQuery(null);
}
/**
* Overridden framework method.
*
* The role of this method is to "fetch", populate, and return a single row
* from the datasource by calling createNewRowForCollection() and populating
* its attributes using populateAttributeForRow().
*/
protected ViewRowImpl createRowFromResultSet(Object qc, ResultSet rs) {
/*
* We ignore the JDBC ResultSet passed by the framework (null anyway) and
* use the resultset that we've stored in the query-collection-private
* user data storage
*/
rs = getResultSet(qc);
/*
* Create a new row to populate
*/
ViewRowImpl r = createNewRowForCollection(qc);
try {
/*
* Populate new row by attribute slot number for current row in Result Set
*/
populateAttributeForRow(r, 0, rs.getLong(1));
populateAttributeForRow(r, 1, rs.getString(2));
populateAttributeForRow(r, 2, rs.getString(3));
} catch (SQLException s) {
throw new JboException(s);
}
return r;
}
/**
* Overridden framework method.
*
* Return true if the datasource has at least one more record to fetch.
*/
protected boolean hasNextForCollection(Object qc) {
ResultSet rs = getResultSet(qc);
boolean nextOne = false;
try {
nextOne = rs.next();
/*
* When were at the end of the result set, mark the query collection
* as "FetchComplete".
*/
if (!nextOne) {
setFetchCompleteForCollection(qc, true);
/*
* Close the result set, we're done with it
*/
rs.close();
}
} catch (SQLException s) {
throw new JboException(s);
}
return nextOne;
}
/**
* Overridden framework method.
*
* The framework gives us a chance to clean up any resources related
* to the datasource when a query collection is done being used.
*/
protected void releaseUserDataForCollection(Object qc, Object rs) {
/*
* Ignore the ResultSet passed in since we've created our own.
* Fetch the ResultSet from the User-Data context instead
*/
ResultSet userDataRS = getResultSet(qc);
if (userDataRS != null) {
try {
userDataRS.close();
} catch (SQLException s) {
/* Ignore */
}
}
super.releaseUserDataForCollection(qc, rs);
}
/**
* Overridden framework method
*
* Return the number of rows that would be returned by executing
* the query implied by the datasource. This gives the developer a
* chance to perform a fast count of the rows that would be retrieved
* if all rows were fetched from the database. In the default implementation
* the framework will perform a SELECT COUNT(*) FROM (...) wrapper query
* to let the database return the count. This count might only be an estimate
* depending on how resource-intensive it would be to actually count the rows.
*/
public long getQueryHitCount(ViewRowSetImpl viewRowSet) {
Long result =
(Long)callStoredFunction(NUMBER, "RefCursorExample.count_orders_for_customer(?)", viewRowSet.getParameters(true));
return result.longValue();
}
// ------------- PRIVATE METHODS ----------------
/**
* Return a JDBC ResultSet representing the REF CURSOR return
* value from our stored package function.
*/
private ResultSet retrieveRefCursor(Object qc, Object[] params) {
ResultSet rs =
(ResultSet)callStoredFunction(OracleTypes.CURSOR, "RefCursorExample.get_orders_for_customer(?)", new Object[] { getNamedBindParamValue("CustEmail",
params) });
return rs;
}
private Object getNamedBindParamValue(String varName, Object[] params) {
Object result = null;
if (getBindingStyle() == SQLBuilder.BINDING_STYLE_ORACLE_NAME) {
if (params != null) {
for (Object param : params) {
Object[] nameValue = (Object[])param;
String name = (String)nameValue[0];
if (name.equals(varName)) {
return (String)nameValue[1];
}
}
}
}
throw new JboException("No bind variable named '" + varName + "'");
}
/**
* Store a new result set in the query-collection-private user-data context
*/
private void storeNewResultSet(Object qc, ResultSet rs) {
ResultSet existingRs = getResultSet(qc);
// If this query collection is getting reused, close out any previous rowset
if (existingRs != null) {
try {
existingRs.close();
} catch (SQLException s) {
}
}
setUserDataForCollection(qc, rs);
hasNextForCollection(qc); // Prime the pump with the first row.
}
/**
* Retrieve the result set wrapper from the query-collection user-data
*/
private ResultSet getResultSet(Object qc) {
return (ResultSet)getUserDataForCollection(qc);
}
/**
* Return either null or a new oracle.jbo.domain.Date
*/
private static Date nullOrNewDate(Timestamp t) {
return t != null ? new Date(t) : null;
}
/**
* Return either null or a new oracle.jbo.domain.Number
*/
private static Number nullOrNewNumber(BigDecimal b) {
try {
return b != null ? new Number(b) : null;
} catch (SQLException s) {
}
return null;
}
//----------------[ Begin Helper Code ]------------------------------
public static int NUMBER = Types.NUMERIC;
public static int DATE = Types.DATE;
public static int VARCHAR2 = Types.VARCHAR;
/**
* Simplifies calling a stored function with bind variables
*
* You can use the NUMBER, DATE, and VARCHAR2 constants in this
* class to indicate the function return type for these three common types,
* otherwise use one of the JDBC types in the java.sql.Types class.
*
* NOTE: If you want to invoke a stored procedure without any bind variables
* ==== then you can just use the basic getDBTransaction().executeCommand()
*
* @param sqlReturnType JDBC datatype constant of function return value
* @param stmt stored function statement
* @param bindVars Object array of parameters
* @return function return value as an Object
*/
protected Object callStoredFunction(int sqlReturnType, String stmt, Object[] bindVars) {
CallableStatement st = null;
try {
st = getDBTransaction().createCallableStatement("begin ? := " + stmt + "; end;", 0);
st.registerOutParameter(1, sqlReturnType);
if (bindVars != null) {
for (int z = 0; z < bindVars.length; z++) {
st.setObject(z + 2, bindVars[z]);
}
}
st.executeUpdate();
return st.getObject(1);
} catch (SQLException e) {
throw new JboException(e);
}
}
/**getEstimatedRowCount - overridden for custom java data source support.
*/
public long getEstimatedRowCount() {
long value = super.getEstimatedRowCount();
return value;
}
/**Gets the bind variable value for CustEmail
*/
public String getCustEmail() {
return (String)getNamedWhereClauseParam("CustEmail");
}
/**Sets <code>value</code> for bind variable Email
*/
public void setCustEmail(String value) {
setNamedWhereClauseParam("CustEmail", value);
}
}
3. 运行
（1）以system用户连接数据库，然后执行：grant create procedure to fod;
（2）以fod用户连接数据库，然后执行数据库脚本：CreateRefCursorPackage.sql
（3）运行AM
说明：本例还是一个典型的使用ResultSet实现VO的例子，你只需要修改私有方法retrieveRefCursor返回你的ResultSet就可以了。
参考文献：
2. http://codingwithpassion.blogspot.jp/2010/10/oracle-adf-advanced-techniques-custom.html
3. http://adfpractice-fedor.blogspot.jp/2011/01/adf-bc-programmatically-populated-vo.html
4. http://adfpractice-fedor.blogspot.jp/2010/12/adf-bc-plsql-procedure-params.html
