In application development, one or more selection box conditions are required. If the input value of the input box is sz or sz | nj | zj | nt, it will be processed in SQL. Select * fromtab_1wherecol_1sz; this is a single-sector input. Select * fromtab_1wherecol_1sz | nj; this is the input in the multiple selection box. Obviously, do not select multiple input values
In application development, one or more selection box conditions are required. If the input value of the input box is sz or sz | nj | zj | nt, it will be processed in SQL. Select * from tab_1 where col_1 = sz; this is a single sequence input. Select * from tab_1 where col_1 = sz | nj; this is the input from multiple selection boxes. Obviously, do not select multiple input values
In application development, one or more selection box conditions are required. If the input value of the input box is 'sz 'or 'sz | nj | zj | nt', it will be processed in SQL.
Select * from tab_1 where col_1 = 'sz '; this is a single sequence input.
Select * from tab_1 where col_1 = 'sz | nj '; this is the input of multiple selection boxes.
Obviously, results are not displayed if you select multiple input values.
-
How can this problem be solved?
There are methods to implement dynamic SQL, such as assembling such an SQL statement: select * from tab_1 where col_1 in ('sz ', 'nj ');
You can also insert the 'sz | nj 'split into a temporary table and associate it with the temporary table, for example, select * from tab_1 where col_1 in (select a from tt );
Temporary tables involve table creation and maintenance, as well as IO.
Recently, I came up with a method to return input strings as nested table types and use table function calls to achieve this type of requirement.
The function code is as follows:
1. create or replace function f_get_unitstring (p_str_all in varchar2,
2. p_str_gap in varchar2) 3. return t_ntb_allstring is 4. -- create or replace type t_ntb_allstring is table of varchar2 (20); 5. v_ntb_allstring t_ntb_allstring;
6.
7. str_unit varchar2 (20 );
8. str_char varchar2 (1 );
9.
10. I _str_length number;
11. I _str_index number;
12.
13. begin 14./* p_str_city: = 'nj ~ Wx ~ Sz ~ Cz ~ Zj ~ Nt ~ Yc ~ '; */15. -- p_str_all: = '1 | 2 | 3 |'; 16. -- p_str_gap: = '|'; 17.
18. v_ntb_allstring: = t_ntb_allstring ();
19.
20. I _str_length: = length (p_str_all );
21.
22. I _str_index: = 1;
23.
24. while (I _str_index <= I _str_length) loop
25. str_char: = substr (p_str_all, I _str_index, 1 );
26.
27. if (str_char = p_str_gap) then 28.
29. if (str_unit is not null) then 30. v_ntb_allstring.extend (1 );
31. v_ntb_allstring (v_ntb_allstring.count): = str_unit; 32. str_unit: = null; 33. end if; 34.
35. else 36. str_unit: = str_unit | str_char;
37.
38. if (I _str_index = I _str_length) then 39. v_ntb_allstring.extend (1 );
40. v_ntb_allstring (v_ntb_allstring.count): = str_unit; 41. str_unit: = ''; 42. end if; 43.
44. end if; 45.
46. I _str_index: = I _str_index + 1;
47. end loop; 48.
49. return (v_ntb_allstring); 50.end; test as follows:
1. SQL> select * from table (f_get_unitstring ('1aa | 2cc | 3bb ',' | '); 2.
3. COLUMN_VALUE
4. -------------------- 5.1aa
6.2cc
7.3bb
8.
9. SQL>
The above solution is only for parameters. Please contact us.
Add another method.
The pipelined function can also be used to meet this requirement. However, the performance advantages here will not be reflected. This method can be used if you happen to have a large data volume such as hundreds of millions of string splits.
The Code is as follows:
1. create or replace function f_get_unitstring (p_str_all in varchar2,
2. p_str_gap in varchar2) 3. return t_ntb_allstring 4. pipelined is 5. -- create or replace type t_ntb_allstring is table of varchar2 (20); 6. v_ntb_allstring t_ntb_allstring;
7. str_unit varchar2 (20 );
8. str_char varchar2 (1 );
9. I _str_length number;
10. I _str_index number;
11. begin 12. v_ntb_allstring: = t_ntb_allstring ();
13. I _str_length: = length (p_str_all );
14. I _str_index: = 1;
15. while (I _str_index <= I _str_length) loop
16. str_char: = substr (p_str_all, I _str_index, 1 );
17. if (str_char = p_str_gap) then 18. if (str_unit is not null) then 19. -- v_ntb_allstring.extend (1); 20. -- v_ntb_allstring (v_ntb_allstring.count): = str_unit; 21. pipe row (str_unit );
22. str_unit: = null; 23. end if; 24. else 25. str_unit: = str_unit | str_char;
26. if (str_unit is not null) then 27. -- v_ntb_allstring.extend (1); 28. -- v_ntb_allstring (v_ntb_allstring.count): = str_unit; 29. pipe row (str_unit );
30. str_unit: = null; 31. end if; 32. end if; 33. I _str_index: = I _str_index + 1;
34. end loop; 35. -- return (v_ntb_allstring); 36.end;
