Oracle通过struct传入varchar2参数为null的解决办法
Oracle通过struct传入varchar2参数为null的解决办法
困扰了自己一个多月的Oracle存储过程由于版本不同的问题，今天花了一整天的时间，终于在下午晚些的时候，解决了，这个问题在本月的绩效考核之中占10%呢，还是很重要的，既然解决了，写下，给大伙做个简单说明。
是这样的，前段时间，由于公司的一个管理信息系统要搞发布会，功能上，老大要求在“销售统计”这块能够加上多条件组合查询的功能，这个功能做好了，真的很好，但是要灵活组合用户的输入的各项条件，再进行数据库的内容筛选，也是不容易以一种简单的方法实现的。在这个上面，我使用了Servlet做控制条件(开发基于J2EE的MVC模式)，oracle存储过程来进行动态sql的处理，里面很多的判断，就不细说了，设计思想是这样的，下面上图：
在开始做开发的时间里，由于方便，使用的是oracle 10g xe，官方的称法叫做oracle 10g特别版，最开始使用就是它方便。再使用pl sql工具来写存储过程，编写、测试，都没问题。到了10月10日那天要发布的时候，从xe把数据库导入企业版的oracle10g中以后，测试发现原来运行正常的页面点击以后，就白了，什么内容都没有，到MyEclipse的console台里查看，报了错，第一眼看过去，应该是存储过程里的动态sql组合不对。第二天，把存储过程组合好的sql输出以后，发现在if...then中的判断根本没做，当时感觉就是判断相等的地方，没有判断到，直到今天都这样认为。
(1)创建oracle数组类型：
drop table choice;
create table choice(
xhidden varchar2(10),
xvalue varchar2(100)
);
drop type XSKF_CHOICE;
drop type XSKF_CHOICE_LST;
create or replace type XSKF_CHOICE as object (
xhidden varchar2(10),
xvalue varchar2(100)
);
create or replace type XSKF_CHOICE_LST as table of XSKF_CHOICE;
(2)在oracle的存储过程中判断字符串是否相等：
-- 7. 多条件组合查询
procedure proc_select_multi2(
xskf_lst in xskf_choice_lst , xskf_paixu in varchar2 ,
idStr in varchar2 , splx_paixu in varchar2 ,
spbh_lst in xskf_choice_lst , spbh_paixu in varchar2 ,
fromdate in varchar2 , todate in varchar2 ,
m2_cursor out xskf_cursor
) as
var_sql varchar2(4000);
var_i_paixu number(2) := 0;
-- 7.1 销售库房
var_choice1 xskf_choice;
var_sql_like1 varchar2(1000);
var_sql_notlike1 varchar2(1000);
var_sql_lt1 varchar2(1000);
var_sql_gt1 varchar2(1000);
var_sql_lteq1 varchar2(1000);
var_sql_gteq1 varchar2(1000);
var_sql_uneq1 varchar2(1000);
var_sql_eq1 varchar2(1000);
var_hidden1 varchar2(10);
var_value1 varchar2(100);
var_i_xskf1 number(2) := 0; -- 控制 商品库房 条件的组合
-- --------------------------------------------
-- 7.2 商品类型
-- --------------------------------------------
-- 7.3 商品编号
var_choice3 xskf_choice;
var_sql_like3 varchar2(1000);
var_sql_notlike3 varchar2(1000);
var_sql_lt3 varchar2(1000);
var_sql_gt3 varchar2(1000);
var_sql_lteq3 varchar2(1000);
var_sql_gteq3 varchar2(1000);
var_sql_uneq3 varchar2(1000);
var_sql_eq3 varchar2(1000);
var_hidden3 varchar2(10);
var_value3 varchar2(100);
var_i_xskf3 number(2) := 0; -- 控制 商品编号 条件的组合
-- --------------------------------------------
begin
var_sql := 'select s.sr_id as 商品id , mp.re_name as 销售库房 , cl.cc_name as 商品类型 , s.sr_number as 商品编号,s.sr_name as 商品名称, sc.cs_idername as 出库单号, sc.cs_zuser as 主销售员, sc.cs_fuser as 配合销售员, sc.cs_leixing as 销售类型 , sc.cs_invoice as 发票号码 , sc.cs_man1 as 客户名称, ma.ar_name as 客户区域 , s.sr_price1 as 商品进价, sc.cs_price as 商品售价,sc.cs_price - s.sr_price1 as 利润 , sc.cs_intime as 售出时间 from s_storeroom s join s_comclass cl on s.sr_ccid = cl.cc_id join s_comsale sc on s.sr_number = sc.cs_srid join mp_reta mp on mp.re_id = s.sr_reid join c_customerInf ci on ci.ci_id = sc.cs_manid join mp_Area ma on ma.ar_id = ci.ci_arid where 1=1';
-- 1. 销售库房 组合查询字符串
for i in 1..xskf_lst.count loop
--取得choice各列的值
var_choice1 := xskf_lst(i);
var_hidden1 := xskf_lst(i).xhidden;
var_value1 := xskf_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_xskf1 then
--第一个条件
if var_hidden1 = 'like' then
var_sql_like1 := ' and (mp.re_name like ''%'|| var_value1 ||'%''';
end if;
if var_hidden1 = 'not like' then
-- 第一个 not like
var_sql_notlike1 := ' and (mp.re_name not like ''%'|| var_value1 ||'%''';
end if;
if var_hidden1='=' then
-- 第一个 =
var_sql_eq1 := ' and (mp.re_name = '''|| var_value1 ||'''';
end if;
if var_hidden1 = '>' then
-- 第一个 >
var_sql_gt1 := ' and (mp.re_name > '''|| var_value1 ||'''';
end if;
if var_hidden1 = '<' then
-- 第一个 <
var_sql_lt1 := ' and (mp.re_name < '''|| var_value1 ||'''';
end if;
if var_hidden1 = '>=' then
var_sql_gteq1 := ' and (mp.re_name >= '''|| var_value1 ||'''';
end if;
if var_hidden1 = '<=' then
var_sql_lteq1 := ' and (mp.re_name <= '''|| var_value1 ||'''';
end if;
if var_hidden1 = '<>' then
var_sql_uneq1 := ' and (mp.re_name <> '''|| var_value1 ||'''';
end if;
else
-- 多于一个条件
if var_hidden1 = 'like' then
var_sql_like1 := var_sql_like1 || ' or mp.re_name like ''%'|| var_value1 ||'%''';
end if;
if var_hidden1 = 'not like' then
-- 第一个 not like
var_sql_notlike1 := var_sql_notlike1 || ' or mp.re_name not like ''%'|| var_value1 ||'%''';
end if;
if var_hidden1='=' then
-- 第一个 =
var_sql_eq1 := var_sql_eq1 || ' or mp.re_name = '''|| var_value1 ||'''';
end if;
if var_hidden1 = '>' then
-- 第一个 >
var_sql_gt1 := var_sql_gt1 || ' or mp.re_name > '''|| var_value1 ||'''';
end if;
if var_hidden1 = '<' then
-- 第一个 <
var_sql_lt1 := var_sql_lt1 || ' or mp.re_name < '''|| var_value1 ||'''';
end if;
if var_hidden1 = '>=' then
var_sql_gteq1 := var_sql_gteq1 || ' or mp.re_name >= '''|| var_value1 ||'''';
end if;
if var_hidden1 = '<=' then
var_sql_lteq1 := var_sql_lteq1 || ' or mp.re_name <= '''|| var_value1 ||'''';
end if;
if var_hidden1 = '<>' then
var_sql_uneq1 := var_sql_uneq1 || ' or mp.re_name <> '''|| var_value1 ||'''';
end if;
end if;
var_sql := var_sql || var_sql_like1 || var_sql_notlike1 || var_sql_lt1 || var_sql_gt1 || var_sql_lteq1 || var_sql_gteq1 || var_sql_uneq1 || var_sql_eq1;
var_i_xskf1 := var_i_xskf1 + 1;
end loop;
if var_i_xskf1 > 0 then
-- 存在筛选条件，在筛选条件最后添加 ' ) ' , 结束
var_sql := var_sql || ')';
end if;
|
评论暂时关闭