用oracle procedure儲存過程實現自表(列存在null)查詢不等於輸入引數的記錄

---某同事想實現自儲存過程實現一功能,其中表的條件不等於儲存過程的引數,因為表中的列值可能為空,這兒有些小特殊,如
--不特殊處殊,要麼多提取記錄,要麼未提取記錄,要麼少提取記錄,測試如下供備記
SQL> select * from t_comp;
 
A          B
---------- ----------
           string1
a          string2
c          string3
 
SQL> desc t_comp;
Name Type         Nullable Default Comments
---- ------------ -------- ------- --------
A    VARCHAR2(10) Y                        
B    VARCHAR2(10) Y                        
 
SQL>
SQL> create or replace procedure proc_not_like(in_a varchar2)
  2  as
  3  v_cnt pls_integer;
  4  begin
  5  select count(nvl(a,0)) into v_cnt from t_comp where dump(a)!=dump(in_a);
  6  dbms_output.put_line(v_cnt);
  7  end;
  8  /
 
Procedure created
 
SQL> exec proc_not_like(NULL);
 
2
 
PL/SQL procedure successfully completed
 
SQL> exec proc_not_like('A');
 
3
 
PL/SQL procedure successfully completed
 
SQL> exec proc_not_like('a');
 
2
 
PL/SQL procedure successfully completed
 
SQL> exec proc_not_like('b');
 
3
 
PL/SQL procedure successfully completed
 
SQL> exec proc_not_like('');
 
2
 
PL/SQL procedure successfully completed
 
SQL>

小結:1,採用dump函式,轉換處理