aa_ VARCHAR2(30) := '11;12;13';
bb_ VARCHAR2(30) := '21;22;23';
cc_ VARCHAR2(30) := '31;32;33';
result_v NUMBER;
BEGIN
aa_ := REPLACE(aa_,';', '*');
bb_ := REPLACE(bb_,';', '*');
cc_ := REPLACE(cc_,';', '*');
EXECUTE IMMEDIATE 'select '||aa_ ||'*'||bb_||'*' ||cc_||' from dual' INTO result_v;
DBMS_OUTPUT.PUT_LINE(result_v);
END;
/
--------------------------------------------------------------------------------------------
SQL> DECLARE
aa_ varchar2 (10) := '11;12;13';
bb_ varchar2 (10) := '21;22;23';
cc_ varchar2 (10) := '31;32;33';
arr_aa_ OWA_UTIL.ident_arr;
arr_bb_ OWA_UTIL.ident_arr;
arr_cc_ OWA_UTIL.ident_arr;
lenarr OWA_UTIL.num_arr;
arrlen integer;
BEGIN
OWA_UTIL.comma_to_ident_arr (REPLACE (aa_, ';', ','), arr_aa_, lenarr, arrlen);
OWA_UTIL.comma_to_ident_arr (REPLACE (bb_, ';', ','), arr_bb_, lenarr, arrlen);
OWA_UTIL.comma_to_ident_arr (REPLACE (cc_, ';', ','), arr_cc_, lenarr, arrlen);
FOR i in 1 .. arrlen LOOP
dbms_output.put_line(arr_aa_(i)*arr_bb_(i)*arr_cc_(i));
END LOOP;
END;
/
7161
8448
9867
---------------------------------------------------------------------SQL> create or replace
2 type number_array is table of number;
3 /
Type created.
SQL> declare
2 aa_ varchar2(20) := '11;12;13';
3 bb_ varchar2(20) := '21;22;23';
4 cc_ varchar2(20) := '31;32;33';
5 aa_array number_array := number_array();
6 bb_array number_array := number_array();
7 cc_array number_array := number_array();
8 begin
9 execute immediate 'begin ' ||
10 ':1 := number_array(' || replace(aa_,';',',') || '); ' ||
11 ':2 := number_array(' || replace(bb_,';',',') || '); ' ||
12 ':3 := number_array(' || replace(cc_,';',',') || '); ' ||
13 'end;'
14 using out aa_array, out bb_array, out cc_array;
15 for i in 1..aa_array.count loop
16 dbms_output.put_line('aa_array(' || i || ') = ' || aa_array(i));
17 end loop;
18 for i in 1..aa_array.count loop
19 dbms_output.put_line('bb_array(' || i || ') = ' || bb_array(i));
20 end loop;
21 for i in 1..aa_array.count loop
22 dbms_output.put_line('cc_array(' || i || ') = ' || cc_array(i));
23 end loop;
24 end;
25 /
aa_array(1) = 11
aa_array(2) = 12
aa_array(3) = 13
bb_array(1) = 21
bb_array(2) = 22
bb_array(3) = 23
cc_array(1) = 31
cc_array(2) = 32
cc_array(3) = 33
No comments:
Post a Comment