A simple implementation of Oracle Dynamic Cursor

Procedure mx_print_common (pd_id in mx_pd_syn.pd_id % type,
P_pd_mxb_id in mx_pd_mxb_syn.p_mxb_id % type,
P_dept_no in SC _mxk.dept_code % type,
P1 SC _bz_syn.bz_code % type,
P2 SC _cjjc_syn.cjjc_code % type,
P3 SC _mxk.warehouse_num % type)
Is
Sql2 varchar2 (500); -- store query statements
Sql3 varchar2 (500); -- store query Conditions
Str1 SC _print_syn.a % type; -- stored Workshop process
Str2 SC _print_syn. B % type; -- storage team (process, process) Process
S_ip SC _print_syn.ip % type;
Type cursor_type is ref cursor;
C1 cursor_type;
Type record_type is record (
Pbom_id SC _mxk.pbom_id % Type
);
R_c1 record_type;
/*

Note the two rows in red and the two rows in blue.

Two red rows define a cursor

The blue two rows define the data structure of the data to be returned in a cursor

*/

Cursor C2 (p_pbom_id SC _mxk.pbom_id % type) is
Select a. dd_count, B. gx_name, C. bz_name, D. cjjc_name
From SC _p_gx_syn A, SC _gx_syn B, SC _bz_syn C, SC _cjjc_syn d
Where pbom_id = p_pbom_id
And a. gx_code = B. gx_code (+) and B. dept_code = p_dept_no
And a. bz_code = C. bz_code (+) and B. dept_code = p_dept_no
And a. cjjc_code = D. cjjc_code (+) and B. dept_code = p_dept_no;

R_c2 C2 % rowtype;
Begin
S_ip: = sys_context ('userenv', 'IP _ address ');
Delete from SC _print_syn where IP = s_ip and p_id = pd_id;
Commit;
-- Start to construct the query statement below
Sql2: = 'select distinct A. pbom_id from SC _mxk ';
Sql3: = 'where a. p_id = '| pd_id |' and A. dept_code = ''' | p_dept_no | '''';

If p_pd_mxb_id> 0 then
Sql2: = sql3 | ', mxk C ';
Sql3: = sql3 | 'and C. m_mxb_id =' | p_pd_mxb_id | 'and A. mxb_id = C. mxb_id ';
End if;

If P1 is not null then
Sql2: = sql2 | ', SC _p_gx_syn B ';
Sql3: = sql3 | 'and A. pbom_id = B. pbom_id and B. bz_code = ''' | P1 | '''';
End if;
If P2 is not null then
Sql2: = sql2 | ', SC _p_gx_syn B ';
Sql3: = sql3 | 'and A. pbom_id = B. pbom_id and B. cjjc_code = ''' | P2 | '''';
End if;
If P3 is not null then
Sql3: = sql3 | 'and A. warehouse_num = ''' | P3 | '''';
End if;
Sql2: = sql2 | sql3;

-- Open the dynamic cursor, and then go down to make the same
Open C1 for sql2;
Loop
Fetch C1 into r_c1;
Exit when C1 % notfound;
Str1: = '';
Str2: = '';
-- Open the process table for processing
Open C2 (r_c1.pbom_id );
Loop
Fetch C2 into r_c2;
Exit when C2 % notfound; -- no record exited
If r_c2.cjjc_name is not null then
Str1: = str1 | to_char (r_c2.cjjc_name );
End if;
If r_c2.bz_name is not null then
Str2: = str2 | r_c2.bz_name | to_char (r_c2.dd_count );
Elsif r_c2.gx_name is not null then
Str2: = str2 | to_char (r_c2.gx_name) | to_char (r_c2.dd_count );
End if;


End loop;
Close C2;
Insert into SC _print_syn (a, B, IP, p_id, r_id)
Values (str1, str2, s_ip, pd_id, r_c1.pbom_id );
Commit;
End loop;
Close C1;
End mx_print_common;

Of course, there must be many implementation methods, and even implicit cursors can be used. However, the use of dynamic query statements in implicit cursors also takes some twists and turns.