請各路高手看過來，有關jboss4.0.1sp1下呼叫oracle9i函式,返回結果集操作遊標時的異常怪問題，急！(附java及oracle函式原始碼)

zmg229發表於2005-12-19
Oracle函式Java原始碼
本人在標題所示版本的jboss下，呼叫oracle 9i function時，在對結果集遊標操作時，獲取結果集的記錄條數時，遇到異常，資訊如下：
13:26:56,348 ERROR [CustomerHandle] [CInfo][3] error ocurred while getCustomerList.
13:26:56,364 ERROR [CustomerHandle] [CInfo][3] error message: 對只轉髮結果集的無效操作: last
13:26:56,442 INFO [STDOUT] java.sql.SQLException: 對只轉髮結果集的無效操作: last
13:26:56,474 INFO [STDOUT] at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:111)
13:26:56,474 INFO [STDOUT] at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:145)
13:26:56,474 INFO [STDOUT] at oracle.jdbc.driver.BaseResultSet.last(BaseResultSet.java:81)
13:26:56,474 INFO [STDOUT] at com.navitone.wap.util.Pager.init(Pager.java:370)
13:26:56,489 INFO [STDOUT] at com.navitone.wap.cinf.CustomerDAO.getCustomerList(CustomerDAO.java:80)
13:26:56,489 INFO [STDOUT] at com.navitone.wap.cinf.CustomerHandle.getCustomerList(CustomerHandle.java:46)
13:26:56,489 INFO [STDOUT] at org.apache.jsp.wap.jpln.cust_005flist_jsp._jspService(cust_005flist_jsp.java:110)
13:26:56,489 INFO [STDOUT] at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:94)
13:26:56,489 INFO [STDOUT] at javax.servlet.http.HttpServlet.service(HttpServlet.java:810)
13:26:56,489 INFO [STDOUT] at org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:324)
13:26:56,489 INFO [STDOUT] at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:292)
13:26:56,489 INFO [STDOUT] at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:236)
13:26:56,489 INFO [STDOUT] at javax.servlet.http.HttpServlet.service(HttpServlet.java:810)
13:26:56,489 INFO [STDOUT] at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:237)
13:26:56,489 INFO [STDOUT] at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:157)
13:26:56,489 INFO [STDOUT] at org.jboss.web.tomcat.filters.ReplyHeaderFilter.doFilter(ReplyHeaderFilter.java:75)
13:26:56,489 INFO [STDOUT] at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:186)
13:26:56,489 INFO [STDOUT] at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:157)
13:26:56,489 INFO [STDOUT] at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:214)
13:26:56,489 INFO [STDOUT] at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:104)
13:26:56,489 INFO [STDOUT] at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:520)
13:26:56,489 INFO [STDOUT] at org.apache.catalina.core.StandardContextValve.invokeInternal(StandardContextValve.java:198)
13:26:56,489 INFO [STDOUT] at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:152)
13:26:56,489 INFO [STDOUT] at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:104)
13:26:56,505 INFO [STDOUT] at org.jboss.web.tomcat.security.CustomPrincipalValve.invoke(CustomPrincipalValve.java:66)
13:26:56,505 INFO [STDOUT] at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:102)
13:26:56,505 INFO [STDOUT] at org.jboss.web.tomcat.security.SecurityAssociationValve.invoke(SecurityAssociationValve.java:150)
13:26:56,505 INFO [STDOUT] at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:102)
13:26:56,505 INFO [STDOUT] at org.jboss.web.tomcat.security.JaccContextValve.invoke(JaccContextValve.java:54)
13:26:56,505 INFO [STDOUT] at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:102)
13:26:56,505 INFO [STDOUT] at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:520)
13:26:56,505 INFO [STDOUT] at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:137)
13:26:56,505 INFO [STDOUT] at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:104)
13:26:56,505 INFO [STDOUT] at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:118)
13:26:56,505 INFO [STDOUT] at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:102)
13:26:56,505 INFO [STDOUT] at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:520)
13:26:56,505 INFO [STDOUT] at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
13:26:56,505 INFO [STDOUT] at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:104)
13:26:56,505 INFO [STDOUT] at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:520)
13:26:56,505 INFO [STDOUT] at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:929)
13:26:56,505 INFO [STDOUT] at org.apache.coyote.tomcat5.CoyoteAdapter.service(CoyoteAdapter.java:160)
13:26:56,505 INFO [STDOUT] at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:799)
13:26:56,505 INFO [STDOUT] at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.processConnection(Http11Protocol.java:705)
13:26:56,505 INFO [STDOUT] at org.apache.tomcat.util.net.TcpWorkerThread.runIt(PoolTcpEndpoint.java:577)
13:26:56,505 INFO [STDOUT] at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:683)
13:26:56,505 INFO [STDOUT] at java.lang.Thread.run(Thread.java:534)
13:26:56,521 ERROR [Engine] StandardWrapperValve[jsp]: Servlet.service() for servlet jsp threw exception
......
----------------------------------------------------------
以下是java方法：
public Collection getCustomerList(String simCardNo, int customerType,
String customerName, String customerCode, int findType,
int customerGroupID, Connection conn, Pager pager)
throws SQLException {
Collection collection = new ArrayList();
/* temporary CustomerData object */
CustomerData customerData = null;
CallableStatement call = null;
ResultSet rs = null;
int retVal = -1;

try {
call = conn.prepareCall("{? = call mrm_user_customers_find(?,?,?,"
+ " ?,?,?,?,?)}", ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_UPDATABLE);
// call = conn.prepareCall("{? = call mrm_user_customers_find(?,?,?,"
// + " ?,?,?,?,?)}", oracle.jdbc.OracleResultSet.TYPE_SCROLL_SENSITIVE,
// oracle.jdbc.OracleResultSet.CONCUR_UPDATABLE);

call.registerOutParameter(1, oracle.jdbc.OracleTypes.NUMBER);
call.registerOutParameter(9, oracle.jdbc.OracleTypes.CURSOR);

call.setString(2, simCardNo);
call.setInt(3, customerType);
call.setString(4, customerName);
call.setString(5, customerCode);
call.setInt(6, findType);
call.setInt(7, customerGroupID);
/* @SortType int = 0 -- 0: 不排序；1: 按時間升序；2：按時間降序； */
call.setInt(8, 2);

call.execute();

retVal = call.getInt(1);
// rs = ((OracleCallableStatement) call).getCursor(9);
rs = (ResultSet) call.getObject(9);

pager.init(rs);
while (pager.nextRow(rs)) {
customerData = new CustomerData();
customerData.setCustomer_id(rs.getInt("customer_id"));
customerData.setCustomer_type(rs.getInt("type"));
customerData.setName(rs.getString("name"));
customerData.setClassType(rs.getInt("class_type"));
customerData.setUserId(rs.getInt("user_id"));
collection.add(customerData);
}
} catch (SQLException se) {
throw se;
} catch (Exception e) {
e.printStackTrace();
} finally {
if (rs != null) {
rs.close();
}
if (call != null) {
call.close();
}
}

return collection;
}
......
public void init(ResultSet rs) throws SQLException {
// this.rowCount = rowCount;
if (!this.bIsPager)
return;

// 每頁最大記錄數預設是20
if (this.pageSize == 0)
this.pageSize = DEFAULT_PAGE_SIZE;

// 當前頁碼預設為1
if (this.showPage == 0)
this.showPage = 1;

if (rs.next()) {
// 得到總記錄數
rs.last();
this.rowCount = rs.getRow();
} else { //無記錄
this.bIsPager = false;
return;
}

// 計算總頁數
this.pageCount = (this.rowCount + this.pageSize - 1) / this.pageSize;
// 調整待顯示的頁碼
if (this.showPage > this.pageCount)
this.showPage = this.pageCount;

this.offSet = (this.showPage - 1) * this.pageSize;

rs.beforeFirst();

// for JDBC 1.x and up
for (int i = 0; i < this.offSet; i++)
rs.next();

// for JDBC 2.x and up
//rs.absolute(this.offSet);

}
......
-------------------------------------------------------------
以下是oracle 9i funtion 定義：
CREATE OR REPLACE FUNCTION mrm_user_customers_find (
p_Mobile varchar2, -- 手機號碼
p_CustomerType int := 0, -- 0: company_info; 1: contact_info, 2: all
p_CustomerName varchar2 :='', -- 通訊錄名
p_CustomerCode varchar2 :='', -- 通訊錄編號
p_FindType int := 1, -- 0: 精確匹配；1: 模糊查詢
p_CustomerGroupID int := -1, -- -1：在所有組範圍內查詢；其他值：在相應的customer_groups[id]內查詢
p_SortType int := 0, -- 0: 不排序；1: 按時間升序；2：按時間降序；
p_cursor OUT pkg_cursor.cur
)
RETURN NUMBER
AS
v_retcode int:=0;
v_UserID int:=0;
--v_ret int:=0;
/******************************************************************************
Name:
mrm_user_customers_find
Description :

Return value :
>=0 :success; <0 :error_code
NOTES:

Version :
for MRM V2.0.0

REVISIONS:
Ver Date Author Description
--------- ---------- --------------- ------------------------------------
1.0 2005-11-14 Song 1. Created this procedure.

******************************************************************************/
BEGIN
v_retcode:= 0;

SELECT rv.user_id
INTO v_UserID
FROM device dv, rover rv
WHERE dv.simCardNO=p_Mobile
and dv.device_id=rv.device_id;

v_retcode := mrm_sys_customers_find (v_UserID,3,p_CustomerType,p_CustomerName,p_CustomerCode,
p_FindType, p_CustomerGroupID, p_SortType, p_cursor);

RETURN v_retcode;
EXCEPTION
WHEN OTHERS THEN
v_retcode:= ERRCODE;
RETURN v_retcode;
END mrm_user_customers_find;
/
--------------------------------------------------------------
CREATE OR REPLACE FUNCTION mrm_sys_customers_find(
p_ObjectID int,-- 若ObjectType=1, 為要列表的公司客戶的ID，對應clients[id]
-- 若ObjectType=2, 為要列表的使用者組的ID，對應users_group[id]
-- 若ObjectType=3, 為要列表的使用者的ID，對應users[id]
p_ObjectType int, -- 若為1，表示@ObjectID為ClientID，若為2，表示為GroupID，若為3，表示為UserID
p_CustomerType int := 0, -- 0: company_info; 1: contact_info, 2: all
p_CustomerName varchar2:='', -- 通訊錄名
p_CustomerCode varchar2:='', -- 通訊錄編號
p_FindType int := 1, -- 0: 精確匹配；1: 模糊查詢
p_CustomerGroupID int := -1, -- -1：在所有組範圍內查詢；其他值：在相應的customer_groups[id]內查詢
p_SortType int := 0, -- 0: 不排序；1: 按時間升序；2：按時間降序；
p_cursor OUT pkg_cursor.cur
)RETURN NUMBER
/******************************************************************************
Description :
透過給定的ObjectID及ObjectID型別(CustomerType)，查詢所有它所能訪問的通訊錄。
例如，當@ObjectType為3時，表示為UserID，則找出所有該UserID能訪問的通訊錄，並且名字為test。
雖然引數中帶有CustomerName和CustomerCode兩個查詢引數，但只有當他們不為空時才會做為查詢
條件。若都為空則返回所有記錄，與mrm_sys_customers_list返回的結果相同。
Return value :
=0 :success; <0 :error
Version :
for MRM V2.0.0
Modify log :
2005-11-14 張 建立
******************************************************************************/
AS
strCompanyFields varchar2(2000):=' ';
strContactFields varchar2(2000):=' ';
strCompanySql varchar2(10000):=' ';
strContactSql varchar2(2000):=' ';
strConditionSql varchar2(2000):=' ';
v_ClientID int:=0;
v_GroupID int:=0;
v_UserID int:=0;
v_UserType int:=-1;

MACRO_COMPANY_INFO int:=0;
MACRO_CONTACT_INFO int:=1;
MACRO_ALL_INFO int:=2;
MACRO_CLIENT_TYPE int:=1;
MACRO_GROUP_TYPE int:=2;
MACRO_USER_TYPE int:=3;
MACRO_GROUP_T int:=0;
MACRO_MEMBER_T int:=1;
BEGIN

if p_ObjectType = MACRO_USER_TYPE then
v_UserID := p_ObjectID;
select u.user_type,u.group_id,u.client_id
into v_UserType,v_GroupID,v_ClientID
from users u
where u.id=v_UserID;
elsif p_ObjectType = MACRO_GROUP_TYPE then
select client_id into v_ClientID
from user_groups
where id=p_ObjectID;

v_GroupID := p_ObjectID;
v_UserType := MACRO_GROUP_TYPE;
elsif p_ObjectType = MACRO_CLIENT_TYPE then
v_ClientID := p_ObjectID;
v_UserType := MACRO_CLIENT_TYPE;
else
return -10;
end if;


if len(p_CustomerName) > 0 then
if p_FindType = 1 then
strConditionSql := strConditionSql||' and ct.name like ''%'||p_CustomerName||'%''';
else
strConditionSql := strConditionSql||' and ct.name = '''||p_CustomerName||'''';
end if;
end if;

if len(p_CustomerCode) > 0 then
if p_FindType = 1 then
strConditionSql := strConditionSql||' and ct.customer_code like ''%'||p_CustomerCode||'%''';
else
strConditionSql := strConditionSql||' and ct.customer_code = '''||p_CustomerCode||'''';
end if;
end if;

if p_CustomerGroupID > -1 then
strConditionSql := strConditionSql||' and ct.customer_group_id = '||to_char(p_CustomerGroupID);
end if;

if p_CustomerType = MACRO_ALL_INFO then
strCompanyFields := '( select '||to_char(p_ObjectID)||' as object_id, ct.id as customer_id, ct.user_id, ct.xml_customer_id, ct.name, ct.type, ct.sub_id, ct.create_time, ct.descinfor,
ct.customer_code, ct.customer_group_id, ct.group_id, ct.client_id, ct.class_type,
geo.latitude, geo.longitude, geo.house_number, geo.street, geo.cross_street, geo.county, geo.city,
geo.state, geo.landmark, geo.create_time ,
info.telephone1, info.telephone2, info.fax, info.email, info.operation, info.tax_no, info.bank_no,
info.create_time, info.business_info, info.operation_type, info.ifchecked,
null as gender, null as age, null as favorite, null as home_tel, null as office_tel, null as responsibility ';

strContactFields := '( select '||to_char(p_ObjectID)||' as object_id, ct.id as customer_id, ct.user_id, ct.xml_customer_id, ct.name, ct.type, ct.sub_id, ct.create_time, ct.descinfor,
ct.customer_code, ct.customer_group_id, ct.group_id, ct.client_id, ct.class_type,
geo.latitude, geo.longitude, geo.house_number, geo.street, geo.cross_street, geo.county, geo.city,
geo.state, geo.landmark, geo.create_time,
null as telephone1, null as telephone2, null as fax, info.email, null as operation, null as tax_no, null as bank_no,
info.create_time, info.business_info, info.operation_type, null as ifchecked,
info.gender, info.age, info.favorite, info.home_tel, info.office_tel, info.responsibility ';
elsif p_CustomerType = MACRO_COMPANY_INFO then
strCompanyFields := '( select '||to_char(p_ObjectID)||' as object_id, ct.id as customer_id, ct.user_id, ct.xml_customer_id, ct.name, ct.type, ct.sub_id, ct.create_time, ct.descinfor,
ct.customer_code, ct.customer_group_id, ct.group_id, ct.client_id, ct.class_type,
geo.latitude, geo.longitude, geo.house_number, geo.street, geo.cross_street, geo.county, geo.city,
geo.state, geo.landmark, geo.create_time ,
info.telephone1, info.telephone2, info.fax, info.email, info.operation, info.tax_no, info.bank_no,
info.create_time, info.business_info, info.operation_type, info.ifchecked ';
elsif p_CustomerType = MACRO_CONTACT_INFO then
strContactFields := '( select '||to_char(p_ObjectID)||' as object_id, ct.id as customer_id, ct.user_id, ct.xml_customer_id, ct.name, ct.type, ct.sub_id, ct.create_time, ct.descinfor,
ct.customer_code, ct.customer_group_id, ct.group_id, ct.client_id, ct.class_type,
geo.latitude, geo.longitude, geo.house_number, geo.street, geo.cross_street, geo.county, geo.city,
geo.state, geo.landmark, geo.create_time,
info.email, info.create_time, info.business_info, info.operation_type, info.gender, info.age,
info.favorite, info.home_tel, info.office_tel, info.responsibility ';
end if;

if p_CustomerType = MACRO_COMPANY_INFO or p_CustomerType = MACRO_ALL_INFO then
strCompanySql := CONCAT(strCompanySql,strCompanyFields);

if v_UserType = MACRO_CLIENT_TYPE then
strCompanySql := strCompanySql
||' from customer ct, customer_geo_info geo, company_info info '
||' where geo.id=info.customer_geo_info_id '
||' and ct.sub_id=info.id '
||' and ct.type = 0 '
||' and ct.client_id='||to_char(v_ClientID)
|| strConditionSql
||' ) ';

elsif v_UserType = MACRO_GROUP_TYPE then
-- 屬於該GroupID組的所有CustomerID
strCompanySql := strCompanySql
||' from customer ct, customer_geo_info geo, company_info info '
||' where geo.id=info.customer_geo_info_id '
||' and ct.sub_id=info.id '
||' and ct.type = 0 '
||' and ct.client_id='||to_char(v_ClientID)
||' and ct.group_id='||to_char(v_GroupID)
|| strConditionSql
||' ) ';
-- 聯合對該GroupID授權的CustomerID組
strCompanySql := strCompanySql || ' UNION ';
strCompanySql := strCompanySql || strCompanyFields;
strCompanySql := strCompanySql || ' from customer ct, customer_geo_info geo, company_info info, customer_grant cgt ';
strCompanySql := strCompanySql || ' where geo.id=info.customer_geo_info_id '
||' and ct.sub_id=info.id '
||' and ct.type = 0 '
||' and ct.client_id='||to_char(v_ClientID)
||' and ct.customer_group_id = cgt.which_id '
||' and cgt.which_type = '||to_char(MACRO_GROUP_T)
||' and cgt.who_type = '||to_char(MACRO_GROUP_T)
||' and cgt.who_id = '||to_char(v_GroupID)
|| strConditionSql
||' ) ';
-- 聯合對該GroupID授權的單個CustomerID
strCompanySql := strCompanySql || ' UNION ';
strCompanySql := strCompanySql || strCompanyFields;
strCompanySql := strCompanySql || ' from customer ct, customer_geo_info geo, company_info info, customer_grant cgt';
strCompanySql := strCompanySql || ' where geo.id=info.customer_geo_info_id '
||' and ct.sub_id=info.id '
||' and ct.type = 0 '
||' and ct.client_id='||to_char(v_ClientID)
||' and ct.id = cgt.which_id '
||' and cgt.which_type = '||to_char(MACRO_MEMBER_T)
||' and cgt.who_type = '||to_char(MACRO_GROUP_T)
||' and cgt.who_id = '||to_char(v_GroupID)
|| strConditionSql
||' ) ';
-- 聯合對該GroupID組中成員授權的CustomerID組
strCompanySql := strCompanySql || ' UNION ';
strCompanySql := strCompanySql || strCompanyFields;
strCompanySql := strCompanySql || ' from customer ct, customer_geo_info geo, company_info info, customer_grant cgt';
strCompanySql := strCompanySql || ' where geo.id=info.customer_geo_info_id '
||' and ct.sub_id=info.id '
||' and ct.type = 0 '
||' and ct.client_id='||to_char(v_ClientID)
||' and cgt.which_id = ct.customer_group_id '
||' and cgt.which_type = '||to_char(MACRO_GROUP_T)
||' and cgt.who_type = '||to_char(MACRO_MEMBER_T)
||' and cgt.who_id = '||to_char(v_UserID)
|| strConditionSql
||' ) ';
-- 聯合對該GroupID組中成員授權的單個CustomerID
strCompanySql := strCompanySql || ' UNION ';
strCompanySql := strCompanySql || strCompanyFields;
strCompanySql := strCompanySql || ' from customer ct, customer_geo_info geo, company_info info, customer_grant cgt, users u ';
strCompanySql := strCompanySql || ' where geo.id=info.customer_geo_info_id '
||' and ct.sub_id=info.id '
||' and ct.type = 0 '
||' and ct.client_id='||to_char(v_ClientID)
||' and ct.id = cgt.which_id '
||' and cgt.which_type = '||to_char(MACRO_MEMBER_T)
||' and cgt.who_type = '||to_char(MACRO_MEMBER_T)
||' and cgt.who_id = u.id'
||' and u.group_id = '||to_char(v_GroupID)
||' and (u.id = '||to_char(v_UserID)||' or 0='||to_char(v_UserID)|| ')'
|| strConditionSql
||' ) ';

elsif v_UserType = MACRO_USER_TYPE then
-- 屬於該UserID組的所有CustomerID
strCompanySql := strCompanySql
||' from customer ct, customer_geo_info geo, company_info info, customer_groups cg '
||' where geo.id=info.customer_geo_info_id '
||' and ct.sub_id=info.id '
||' and ct.type = 0 '
||' and ct.client_id='||to_char(v_ClientID)
||' and ct.user_id='||to_char(v_UserID)
--||' and (ct.customer_group_id = cg.id or ct.customer_group_id = 0 )' ---預設組裡的客戶
--||' and (ct.customer_group_id = 0 or (ct.customer_group_id = cg.id and cg.user_id = ct.user_id'-- + CAST(@UserID as varchar(20)) --Add by hx: 增加對使用者許可權的判斷
||' and ( ct.customer_group_id = 0 or (ct.customer_group_id = cg.id and cg.user_id = ct.user_id) or (ct.customer_group_id IN (select cg.id from customer c, customer_groups cg, customer_grant cgt where cgt.which_id = c.id and cgt.which_type = 1 and c.customer_group_id = cg.id and cgt.who_type = 1 and cgt.who_id =' ||to_char(v_UserID)|| ' UNION select cg.id from customer c, customer_groups cg, customer_grant cgt where cgt.which_id = c.id and cgt.which_type = 1 and c.customer_group_id = cg.id and cgt.who_type = 0 and cgt.who_id ='||to_char(v_GroupId)||')'--Add by hx: 增加對使用者許可權的判斷
||'))'
|| strConditionSql
||' ) ';
-- 聯合對該UserID授權的CustomerID組
strCompanySql := strCompanySql || ' UNION ';
strCompanySql := strCompanySql || strCompanyFields;
strCompanySql := strCompanySql || ' from customer ct, customer_geo_info geo, company_info info, customer_grant cgt';
strCompanySql := strCompanySql || ' where geo.id=info.customer_geo_info_id '
||' and ct.sub_id=info.id '
||' and ct.type = 0 '
||' and ct.client_id='||to_char(v_ClientID)
||' and ct.customer_group_id = cgt.which_id '
||' and cgt.which_type = '||to_char(MACRO_GROUP_T)
||' and cgt.who_type = '||to_char(MACRO_MEMBER_T)
||' and cgt.who_id = '||to_char(v_UserID)
|| strConditionSql
||' ) ';
-- 聯合對該UserID授權的單個CustomerID
strCompanySql := strCompanySql || ' UNION ';
strCompanySql := strCompanySql || strCompanyFields;
strCompanySql := strCompanySql || ' from customer ct, customer_geo_info geo, company_info info, customer_grant cgt';
strCompanySql := strCompanySql || ' where geo.id=info.customer_geo_info_id '
||' and ct.sub_id=info.id '
||' and ct.type = 0 '

||' and ct.client_id='||to_char(v_ClientID)
||' and ct.id = cgt.which_id '
||' and cgt.which_type = '||to_char(MACRO_MEMBER_T)
||' and cgt.who_type = '||to_char(MACRO_MEMBER_T)
||' and cgt.who_id = '||to_char(v_UserID)
|| strConditionSql
||' ) ';
-- 聯合對該GroupID組中成員授權的CustomerID組
strCompanySql := strCompanySql || ' UNION ';
strCompanySql := strCompanySql || strCompanyFields;
strCompanySql := strCompanySql || ' from customer ct, customer_geo_info geo, company_info info, customer_grant cgt';
strCompanySql := strCompanySql || ' where geo.id=info.customer_geo_info_id '
||' and ct.sub_id=info.id '
||' and ct.type = 0 '
||' and ct.client_id='||to_char(v_ClientID)
||' and cgt.which_id = ct.customer_group_id '
||' and cgt.which_type = '||to_char(MACRO_GROUP_T)
||' and cgt.who_type = '||to_char(MACRO_GROUP_T)
||' and cgt.who_id = '||to_char(v_GroupID)
|| strConditionSql
||' ) ';
-- 聯合對該GroupID授權的單個CustomerID
strCompanySql := strCompanySql || ' UNION ';
strCompanySql := strCompanySql || strCompanyFields;
strCompanySql := strCompanySql || ' from customer ct, customer_geo_info geo, company_info info, customer_grant cgt';
strCompanySql := strCompanySql || ' where geo.id=info.customer_geo_info_id '
||' and ct.sub_id=info.id '
||' and ct.type = 0 '
||' and ct.client_id='||to_char(v_ClientID)
||' and ct.id = cgt.which_id '
||' and cgt.which_type = '||to_char(MACRO_MEMBER_T)
||' and cgt.who_type = '||to_char(MACRO_GROUP_T)
||' and cgt.who_id = '||to_char(v_GroupID)
|| strConditionSql
||' ) ';

end if;
end if;

if p_CustomerType = MACRO_CONTACT_INFO or p_CustomerType = MACRO_ALL_INFO
then
if p_ObjectType = MACRO_CLIENT_TYPE
then
strContactSql := strContactFields
|| ' from customer ct, customer_geo_info geo, users u, contact_info info'
|| ' where geo.id=info.customer_geo_info_id '
|| ' and ct.sub_id=info.id '
|| ' and ct.type = 1 '
|| ' and u.client_id= '||to_char(v_ClientID)
|| ' and u.id = ct.user_id'
|| strConditionSql
|| ' ) ';
elsif p_ObjectType = MACRO_GROUP_TYPE then
strContactSql := strContactFields
|| ' from customer ct, customer_geo_info geo, users u, contact_info info'
|| ' where geo.id=info.customer_geo_info_id '
|| ' and ct.sub_id=info.id '
|| ' and ct.type = 1 '
--|| ' and u.group_id='||to_char(GroupID)
|| ' and u.id = ct.user_id'
|| strConditionSql
|| ' ) ';

elsif p_ObjectType = MACRO_USER_TYPE then
strContactSql := strContactFields
|| ' from customer ct, customer_geo_info geo, users u, contact_info info'
|| ' where geo.id=info.customer_geo_info_id '
|| ' and ct.sub_id=info.id '
|| ' and ct.type = 1 '
|| ' and ct.user_id = u.id'
|| ' and u.id = '||to_char(v_UserID)
|| strConditionSql
|| ' ) ';
end if;
end if;


if p_CustomerType =MACRO_COMPANY_INFO
then
if p_SortType>0 then
strCompanySql := strCompanySql || ' order by 8/*ct.create_time*/';
end if;
if p_SortType=1 then
strCompanySql := strCompanySql || ' asc';
elsif p_SortType=2 then
strCompanySql := strCompanySql || ' desc';
end if;
open p_cursor for strCompanySql;
--print strCompanySql

elsif p_CustomerType = MACRO_CONTACT_INFO then
if p_SortType>0 then
strContactSql := strContactSql || ' order by 8/*ct.create_time*/';
end if;
if p_SortType=1 then
strContactSql := strContactSql || ' asc';
elsif p_SortType=2 then
strContactSql := strContactSql || ' desc';
end if;
open p_cursor for strContactSql;
else
if p_SortType>0 then
strContactSql := strContactSql || ' order by 8/*ct.create_time*/';
end if;
if p_SortType=1 then
strContactSql := strContactSql || ' asc';
elsif p_SortType=2 then
strContactSql := strContactSql || ' desc';
end if;
open p_cursor for strCompanySql || ' UNION ALL ' || strContactSql;
end if;
return 0;
EXCEPTION
WHEN OTHERS THEN
RETURN ERRCODE;
END mrm_sys_customers_find;
/

線上等 ，請高手不吝賜教......

相關文章