---某同事想实现自存储过程实现一功能,其中表的条件不等于存储过程的参数,因为表中的列值可能为空,这儿有些小特殊,如
--不特殊处殊,要么多提取记录,要么未提取记录,要么少提取记录,测试如下供备记
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博客

用oracle procedure存储过程实现自表(列存在null)查询不等于输入参数的记录

最新文章