oracle参数传nulL,Oracle通过struct传入varchar2参数为null的解决办法

Oracle通过struct传入varchar2参数为null的解决办法

[日期：2011-01-23]

来源：Linux社区

作者：k09800

[字体：大 中 小]

-- 3. 商品编号 组合查询字符串

for i in 1..spbh_lst.count loop

var_hidden3 := '';

--取得choice各列的值

var_choice3 := spbh_lst(i);

var_hidden3 := spbh_lst(i).xhidden;

var_value3 := spbh_lst(i).xvalue;

--添加筛选条件

--where 1=1 and cl.cc_id in (212,244,245,246,247,248,249,243,213,250,251,252,253,254,255,256,257,258,214,215,210) and mp.re_name like '%门%' and s.sr_number like '%sp%' and s.sr_name like '%宏%' and sc.cs_idername like '%2%' and (sc.cs_zuser like '%朱%' or sc.cs_fuser like '%江%') and sc.cs_leixing like '%不%' and sc.cs_invoice like '%3%' and sc.cs_man1 like '%科%' and ma.ar_name like '%川%'

if 0 = var_i_xskf3 then

--第一个条件

if var_hidden3 = 'like' then

var_sql_like3 := ' and (s.sr_number like ''%'|| var_value3 ||'%''';

end if;

if var_hidden3 = 'not like' then

-- 第一个 not like

var_sql_notlike3 := ' and (s.sr_number not like ''%'|| var_value3 ||'%''';

end if;

if var_hidden3='=' then

-- 第一个 =

var_sql_eq3 := ' and (s.sr_number = '''|| var_value3 ||'''';

end if;

if var_hidden3 = '>' then

-- 第一个 >

var_sql_gt3 := ' and (s.sr_number > '''|| var_value3 ||'''';

end if;

if var_hidden3 = '

-- 第一个 <

var_sql_lt3 := ' and (s.sr_number < '''|| var_value3 ||'''';

end if;

if var_hidden3 = '>=' then

var_sql_gteq3 := ' and (s.sr_number >= '''|| var_value3 ||'''';

end if;

if var_hidden3 = '<=' then

var_sql_lteq3 := ' and (s.sr_number <= '''|| var_value3 ||'''';

end if;

if var_hidden3 = '<>' then

var_sql_uneq3 := ' and (s.sr_number <> '''|| var_value3 ||'''';

end if;

else

-- 多于一个条件

if var_hidden3 = 'like' then

var_sql_like3 := var_sql_like3 || ' or s.sr_number like ''%'|| var_value3 ||'%''';

end if;

if var_hidden3 = 'not like' then

-- 第一个 not like

var_sql_notlike3 := var_sql_notlike3 || ' or s.sr_number not like ''%'|| var_value3 ||'%''';

end if;

if var_hidden3='=' then

-- 第一个 =

var_sql_eq3 := var_sql_eq3 || ' or s.sr_number = '''|| var_value3 ||'''';

end if;

if var_hidden3 = '>' then

-- 第一个 >

var_sql_gt3 := var_sql_gt3 || ' or s.sr_number > '''|| var_value3 ||'''';

end if;

if var_hidden3 = '

-- 第一个 <

var_sql_lt3 := var_sql_lt3 || ' or s.sr_number < '''|| var_value3 ||'''';

end if;

if var_hidden3 = '>=' then

var_sql_gteq3 := var_sql_gteq3 || ' or s.sr_number >= '''|| var_value3 ||'''';

end if;

if var_hidden3 = '<=' then

var_sql_lteq3 := var_sql_lteq3 || ' or s.sr_number <= '''|| var_value3 ||'''';

end if;

if var_hidden3 = '<>' then

var_sql_uneq3 := var_sql_uneq3 || ' or s.sr_number <> '''|| var_value3 ||'''';

end if;

end if;

var_sql := var_sql || var_sql_like3 || var_sql_notlike3 || var_sql_lt3 || var_sql_gt3 || var_sql_lteq3 || var_sql_gteq3 || var_sql_uneq3 || var_sql_eq3;

var_i_xskf3 := var_i_xskf3 + 1;

end loop;

if var_i_xskf3 > 0 then

-- 存在筛选条件，在筛选条件最后添加 ' ) ' , 结束

var_sql := var_sql || ')';

end if;

-- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

if fromdate is not null and todate is not null then

var_sql := var_sql || ' and sc.cs_intime <= to_date(''' || todate || ''',''yyyy-mm-dd hh24:mi:ss'') and to_date(''' || fromdate || ''',''yyyy-mm-dd hh24:mi:ss'') <= sc.cs_intime';

end if;

if idStr is not null then

var_sql := var_sql || ' and cl.cc_id in ('|| idStr ||')';

end if;

--order by desc , desc , desc , desc , desc , desc , desc , desc , desc , desc , desc;

--order by , , , , , , , , ,

-- 1. xskf_paixu 销售库房 mp.re_name

if xskf_paixu is not null and 0 = var_i_paixu then

var_sql := var_sql || ' order by mp.re_name '|| xskf_paixu ||'';

var_i_paixu := var_i_paixu + 1;

end if;

if xskf_paixu is not null and var_i_paixu > 0 then

var_sql := var_sql || ',mp.re_name '|| xskf_paixu ||'';

var_i_paixu := var_i_paixu + 1;

end if;

-- 2. splx_paixu 商品类型 cl.cc_name

if splx_paixu is not null and 0 = var_i_paixu then

var_sql := var_sql || ' order by cl.cc_name '|| splx_paixu ||'';

var_i_paixu := var_i_paixu + 1;

end if;

if splx_paixu is not null and var_i_paixu > 0 then

var_sql := var_sql || ',cl.cc_name '|| splx_paixu ||'';

var_i_paixu := var_i_paixu + 1;

end if;

-- 3. spbh_paixu 商品编号 s.sr_number

if spbh_paixu is not null and 0 = var_i_paixu then

var_sql := var_sql || ' order by s.sr_number '|| spbh_paixu ||'';

var_i_paixu := var_i_paixu + 1;

end if;

if spbh_paixu is not null and var_i_paixu > 0 then

var_sql := var_sql || ',s.sr_number '|| spbh_paixu ||'';

var_i_paixu := var_i_paixu + 1;

end if;

if var_sql is not null then

open m2_cursor for var_sql;

end if;

end proc_select_multi2;

开始做开发的时候使用Oracle 10g xe运行以上(1)(2)中的代码很正常，但当要正式发布时候，在oracle 10g 企业版中运行上述(1)(2)中的代码就有问题，经过仔细检查，发现通过java调用oracle的存储过程，其中传入参数为数组，则数组中的元素是字符串内容的，在oracle中接收的内容是null，其他的类型：如number、float都可以正常接收，自己在oracle的存储过程中输出的结果是null。原来，在定义数组类型的时候，要将varchar2换为nvarchar2就可以正常接收string类型的参数了。于是将(1)中的内容改为如下代码：

drop table choice;

create table choice(

xhidden nvarchar2(10),

xvalue nvarchar2(100)

);

drop type XSKF_CHOICE;

drop type XSKF_CHOICE_LST;

create or replace type XSKF_CHOICE as object (

xhidden nvarchar2(10),

xvalue nvarchar2(100)

);

create or replace type XSKF_CHOICE_LST as table of XSKF_CHOICE;

    再在oracle 10g 企业版中测试，正常了！心情一阵激动！