最近在解决一个Java中SQL语句引用的过程中,发现一个比较难以解决的问题,需要对Oracle中的long类型进行条件分析,并加到where语句中,网上都说在数据库中最好不好使用long类型,但是目前也没办法修改数据库类型,只好想办法将long类型转换成char类型了,进过网上多番资料的查找终于找到了决绝的办法。

 

首先需要在Oracle中穿件函数LONG_TO_CHAR

--创建函数
/*
   in_rowid为行id,
   in_owner为数据库登陆的帐号名,
   in_table_name为数据库表名,
   in_column为数据库对应long类型的表字段名称
*/
CREATE OR REPLACE FUNCTION LONG_TO_CHAR( in_rowid rowid,in_owner
varchar,in_table_name varchar,in_column varchar2)
RETURN varchar AS
text_c1 varchar2(32767);
sql_cur varchar2(2000);
--
begin
  sql_cur := 'select '||in_column||' from
'||in_owner||'.'||in_table_name||' where rowid =
'||chr(39)||in_rowid||chr(39);
  dbms_output.put_line (sql_cur);
  execute immediate sql_cur into text_c1;

  text_c1 := substr(text_c1, 1, 4000);
  RETURN TEXT_C1; 
END;

接着做了一个小实验其中remark为long类型字段

--查询实现
select a.*
from (select requestid,long_to_char(rowid,'oa','workflow_requestlog','remark') as longchar
      from workflow_requestlog
      where requestid=155253) a
where a.longchar like '%654321 2012-06-25 11:55:41%'

 

--将remark(long类型)附加到case when 的判断中去
 select case when t3.longchar like '%654321 2012-06-25 11:55:41%'
         then null else t1.remark end as remark,
         t1.requestid,t1.workflowid,t1.nodeid,t1.logtype,t1.operatedate,t1.operatetime,
         t1.operator,t1.clientip,t1.operatortype,t1.destnodeid,t1.receivedPersons,
         t1.showorder,t1.agentorbyagentid,t1.agenttype,t1.LOGID,t1.annexdocids,t1.requestLogId,
         t1.operatorDept,t1.signdocids,t1.signworkflowids,
         t2.nodename
  from workflow_requestlog t1,workflow_nodebase t2,
      (select requestid,nodeid,long_to_char(rowid,'oa','workflow_requestlog','remark') as longchar
       from workflow_requestlog
       where requestid=155253) t3
  where t1.requestid=t3.requestid
    and t1.nodeid=t2.id
    and t3.nodeid=t2.id
  order by operatedate desc,operatetime desc

 

最后在java中实现


--原文件 645行-654行
 String sql = "select t1.*,t2.nodename from workflow_requestlog t1,workflow_nodebase t2 " +
                " where t1.requestid=" + requestid + " and t1.nodeid=t2.id and t1.logtype != '1' " +
                " and t1.nodeid in (" + thisviewLogIds + ")" ;
        if(!"".equals(signid1username)){
         sql+=" and t1.operator in (select id from hrmresource where lastname  like '%"+signid1username+"%')";
        }
        if(!"".equals(signid1userid)){
         sql+=" and t1.operator in ("+signid1userid+")";
        }        
        sql += " order by t1.operatedate "+orderby+",t1.operatetime "+orderby+",t1.logtype "+orderby;

 

--新改进的文件

--原文件 645起
 String sql = "select case when t3.longchar like '%654321 2012-06-25 11:55:41%' then null else t1.remark end as remark, " +
              "       t1.requestid,t1.workflowid,t1.nodeid,t1.logtype,t1.operatedate,t1.operatetime, " +
              "       t1.operator,t1.clientip,t1.operatortype,t1.destnodeid,t1.receivedPersons, " +
              "       t1.showorder,t1.agentorbyagentid,t1.agenttype,t1.LOGID,t1.annexdocids,t1.requestLogId, " +
              "       t1.operatorDept,t1.signdocids,t1.signworkflowids,t2.nodename " +
              "from workflow_requestlog t1,workflow_nodebase t2, " +
              "    (select requestid,nodeid,long_to_char(rowid,'oa','workflow_requestlog','remark') as longchar " +
              "     from workflow_requestlog " +
              "     where requestid=" + requestid + " ) t3 " +
              " where t1.requestid=t3.requestid and t1.nodeid=t2.id and t1.nodeid=t3.nodeid andt1.logtype != '1' " +
              " and t1.nodeid in (" + thisviewLogIds + ")" ;
        if(!"".equals(signid1username)){
         sql+=" and t1.operator in (select id from hrmresource where lastname  like '%"+signid1username+"%')";
        }
        if(!"".equals(signid1userid)){
         sql+=" and t1.operator in ("+signid1userid+")";
        }        
        sql += " order by t1.operatedate "+orderby+",t1.operatetime "+orderby+",t1.logtype "+orderby;

 

这样就能基本通过long_to_char函数将long类型的remark字段添加到case when的判断中去了。