Solution to pass varchar2 parameter null through struct in Oracle

I had trouble with my Oracle stored procedures for more than a month. Due to different versions, I spent a whole day today and finally solved them later in the afternoon, this problem accounts for 10% of the performance appraisal this month. It is very important to write down the question and give a brief explanation to everyone.

This is the case. Some time ago, a company's management information system was about to launch a press conference. In terms of its functions, the boss demanded that the multi-condition combined query function be added to the "sales statistics" section, this function is really good, but it is not easy to implement it in a simple way to flexibly combine various conditions of user input and filter database content. In this regard, I used Servlet for control conditions (J2EE-based MVC mode for development), and oracle stored procedures for dynamic SQL processing. I will not elaborate on the many judgments in it, the design idea is as follows:

At the time of development, oracle 10g xe was used for convenience. The official name is oracle 10g special edition, which was easy to use at first. It's okay to use pl SQL to write and test stored procedures. By the time it was about to be released on March 13, October 10, after importing the database to oracle10g of the Enterprise Edition from xe, the test found that after clicking on the page that was originally running normally, it would be white and nothing was found, check in the console of MyEclipse and report an error. The first check is that the dynamic SQL combination in the stored procedure is incorrect. The next day, after outputting the SQL statement that combines the stored procedure, it is found in if... the judgment in then was not done at all. At that time, I felt that the judgment was equal and I did not know it until today.

(1) Create an oracle array:
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) judge whether the strings are equal during oracle storage:
-- 7. Multi-condition combination query
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
)
Var_ SQL varchar2 (4000 );
Var_ I _paixu number (2): = 0;
-- 7.1 sales warehouse
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; -- controls the combination of conditions for a warehouse.
----------------------------------------------
-- 7.2 product type

----------------------------------------------
-- 7.3 Product NO.
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; -- controls the combination of item numbers
----------------------------------------------
Begin
Var_ SQL: = 'select s. sr_id as commodity id, mp. re_name as sales warehouse, cl. cc_name as item type, s. sr_number as product number, s. sr_name as product name, SC. cs_idername as Warehouse picking ticket number, SC. cs_zuser as salesman, SC. cs_fuser as works with sales staff, SC. cs_leixing as sales type, SC. cs_invoice as invoice number, SC. cs_man1 as customer name, ma. ar_name as customer region, s. sr_price1 as product price, SC. cs_price as product price, SC. cs_price-s. sr_price1 as profit, SC. cs_intime as sales time from s_storoom 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. query string of the sales warehouse combination
For I in 1 .. xskf_lst.count loop
-- Obtain the values of each choice Column
Var_choice1: = xskf_lst (I );
Var_hidden1: = xskf_lst (I). xhidden;
Var_value1: = xskf_lst (I). xvalue;
-- Add filter conditions
-- 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 '% door %' and s. sr_number like '% sp %' and s. sr_name like '% macro %' and SC. cs_idername like '% 2%' and (SC. cs_zuser like '% Zhu %' or SC. cs_fuser like '% Jiang %') and SC. cs_leixing like '% Not %' and SC. cs_invoice like '% 100' and SC. cs_man1 like '% section %' and ma. ar_name like '% Chuan %'
If 0 = var_ I _xskf1 then
-- First condition
If var_hidden1 = 'like' then
Var_ SQL _like1: = 'and (mp. re_name like ''%' | var_value1 | '% ''';
End if;
If var_hidden1 = 'not like' then
-- The first not like
Var_ SQL _notlike1: = 'and (mp. re_name not like ''%' | var_value1 | '% ''';
End if;
If var_hidden1 = '= 'then
-- First =
Var_ SQL _eq1: = 'and (mp. re_name = ''' | var_value1 | '''';
End if;
If var_hidden1 = '> 'then
-- First>
Var_ SQL _gt1: = 'and (mp. re_name> ''' | var_value1 | '''';
End if;
If var_hidden1 = '<'then
-- First <
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
-- More than one condition
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
-- The first not like
Var_ SQL _notlike1: = var_ SQL _notlike1 | 'or mp. re_name not like ''%' | var_value1 | '% ''';
End if;
If var_hidden1 = '= 'then
-- First =
Var_ SQL _eq1: = var_ SQL _eq1 | 'or mp. re_name = ''' | var_value1 | '''';
End if;
If var_hidden1 = '> 'then
-- First>
Var_ SQL _gt1: = var_ SQL _gt1 | 'or mp. re_name> ''' | var_value1 | '''';
End if;
If var_hidden1 = '<'then
-- First <
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 _lt1 | var_ SQL _gt1 | percent | var_ SQL _gteq1 | var_ SQL _uneq1 | var_ SQL _eq1;
Var_ I _xskf1: = var_ I _xskf1 + 1;
End loop;
If var_ I _xskf1> 0 then
-- If a filtering condition exists, add ')' to the end of the filtering condition.
Var_ SQL: = var_ SQL | ')';
End if;

1 2 Next Page