---某同事想实现自存储过程实现一功能,其中表的条件不等于存储过程的参数,因为表中的列值可能为空,这儿有些小特殊,如
--不特殊处殊,要么多提取记录,要么未提取记录,要么少提取记录,测试如下供备记
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>
--不特殊处殊,要么多提取记录,要么未提取记录,要么少提取记录,测试如下供备记
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函数,转换处理
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/9240380/viewspace-753498/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/9240380/viewspace-753498/