代码之家 › 专栏 › 技术社区 › 4est

查找不同Oracle类型的空值

nvl null plsql oracle sql

4est · 技术社区 · 7 年前

我正在收集两列(col&val)。第二个select col是其他列的参数,val是该列的值。

declare
  TYPE t_my_list is record(id varchar2(1000), col VARCHAR2(4000),val VARCHAR2(4000));
  TYPE list_3STR is table of t_my_list;    
  v_stmt    VARCHAR2(32000) := 'SELECT id, col, val FROM userA.tableA';
  v_lstmt   VARCHAR2(32000);
  v_ret     list_3STR := list_3STR();
  cDel   number;

begin
 EXECUTE IMMEDIATE v_stmt BULK COLLECT INTO v_ret;

  for i in v_ret.first..v_ret.last loop
   v_lstmt := 'SELECT count(*) FROM userB.tableB 
      WHERE NVL('||v_ret (i).col||', ''<null>'') in ('''||v_ret (i).val||''', ''<null>'') and idB = '''||v_ret (i).id||'''';

   EXECUTE IMMEDIATE v_lstmt INTO cDel;
    If cDel > 0 Then
    --some code
    cDel = 0;   
    end if;
  end loop;
end;

但是在我的select语句中,我可以有null,所以我使用的是NVL。另外,因为我可以有数字,所以我需要使用convert to_char('||v_ret (i).col||') . 另外,列的类型是数字、原始数据、日期等。。

我的问题是:

还有其他的可能性吗?
如果没有,是否有oracle默认转换器?(所有类型都需要Varchar2)

2 回复 | 直到 7 年前

Alex Poole 7 年前

您可以将代码更改为:

   v_lstmt := 'SELECT count(*) FROM userB.tableB WHERE id = '''||v_ret (i).id||''''
     || ' and ('||v_ret (i).col||' is null or '||v_ret (i).col||' = :val)';

   EXECUTE IMMEDIATE v_lstmt INTO cDel using v_ret (i).val;

检查列是否为空或匹配提供的 val ,并使用一个绑定变量提供要检查的值,以减少一点解析。

但是,这仍然依赖于隐式转换,因此,如果您在表中有一个日期值,例如,您将依赖于NLS设置来转换它以匹配目标表列类型。

你可以使用 all_tab_columns 视图以查找目标列的数据类型并对 瓦尔 在绑定之前。一个更复杂但可能更稳健的方法是 dbms_sql 对于内部动态SQL而不是 execute immediate .

外部查询似乎不需要是动态的,但是您可以:

declare
  v_lstmt   VARCHAR2(32000);
  cDel   number;
begin
  for rec in (SELECT id, col, val FROM tableA) loop
    v_lstmt := 'SELECT count(*) FROM tableB WHERE id = '''||rec.id||''''
      || ' and ('||rec.col||' is null or '||rec.col||' = :val)';


    dbms_output.put_line(v_lstmt);
    EXECUTE IMMEDIATE v_lstmt INTO cDel using rec.val;
    If cDel > 0 Then
      --some code
      cDel := 0;   
    end if;
  end loop;
end;
/

Goran Kutlaca 7 年前

如果我正确地理解了您的问题,您需要包括v_ret(I).col为空时的情况。

如果是,可以尝试在select语句中使用以下代码:

WHERE ('||v_ret (i).col||' is null OR '||v_ret (i).col||' = '||v_ret (i).val||')

而不是:

WHERE NVL('||v_ret (i).col||', ''<null>'') in ('''||v_ret (i).val||''', ''<null>'')

作为默认转换器,您可以尝试使用铸造功能: https://docs.oracle.com/en/database/oracle/oracle-database/12.2/sqlrf/CAST.html