记一下较复杂的SQL

最近在应用中用到两张表：

XZ_VIEW_INDEX_SJFW :  主表

ACF_SYS_GWATTACH    :  正文/附件存储表

 

正文或附件都以Blob字段存于 正文/附件存储表 中，该表的FILE_ID字段与主表中的ZW（正文序号）或FJ（附件序号）相关联。但是主表中的ZW和FJ字段是VARCHAR型的，而 正文/附件存储表 中的FILE_ID字段是数值型的，并且，主表中的ZW，FJ两个字段有可能为空，也有可能是中文，所以不能直接使用关联来从三张表中查询记录。

现在要求就是用一个SQL查询数据，如果有ZW或FJ值，则需要从 正文/附件存储表 中把对应的正文或附件也取出来。

 

为了防止非数值型与数值型字段值进行比较导致出错，所以先写了个自定义函数用来判断一个值是否是数字：

CREATE OR REPLACE FUNCTION IsNum (p_in VARCHAR2) RETURN NUMBER AS
  val NUMBER;
BEGIN
  select NVL(length(translate(trim(p_in),' +-.0123456789',' ')),0) into val from dual;
  if val=0 then 
    return 1;
  else
    return 0;
  end if;
END IsNum;

 

然后费了九牛二虎之力拼凑出一个SQL，在这里记一下，防止忘记：

--发文随机抽样
select * from ( 
  select 
      A.MODULE_CODE,
      A.BIZ_CODE,
      A.OP_TYPE,
      A.LASTTIME,
      A.BT,
      A.WH,
      A.ZTC,
      A.CYCL,
      A.SJ,
      A.ZW, 
      A.FJ,
      decode(isNum(A.ZW), 1,  B.EXT_NAME, -1) "正文扩展名", 
      decode(isNum(A.ZW), 1,  B.FILE_NAME, -1) "正文文件名", 
      decode(isNum(A.ZW), 1,  B.FILE_CONTENT,  empty_blob()) "正文",
      decode(isNum(A.FJ), 1,  C.EXT_NAME, -1) "附件扩展名",
      decode(isNum(A.FJ), 1,  C.FILE_NAME, -1) "附件文件名", 
      decode(isNum(A.FJ), 1,  C.FILE_CONTENT, empty_blob()) "附件"
  from UA.XZ_VIEW_INDEX_SJFW A, UB.ACF_SYS_GWATTACH B, UB.ACF_SYS_GWATTACH C
    where B.FILE_ID(+)=decode(isNum(A.ZW), 1, A.ZW, null) 
      and C.FILE_ID(+)=decode(isNum(A.FJ), 1, A.FJ, null)
  order by dbms_random.value
) 
where rownum <= 10