A stored procedure: dynamically execute an SQL statement with parameters: execute immediate

According to the customer's needs, we have added a statistical table to collect statistics and generate statistical data. We need to dynamically Execute SQL statements based on a basic table. the execute immediate command is used to dynamically generate and execute SQL statements during the storage process. I want to write a general Stored Procedure for statistics. It seems that it is not easy to use. The application of if statements is inevitable. haha.

It is hereby archived.

Create or replace procedure p_insert_xt_tj_mx (sdate in varchar2) is
-- Author: Li chunlei
-- Create time: 2005.8.4
-- Purpose: update the xt_tj_mx table
Sxh xt_tj.xh % type; -- number of the master table
Sdwmc xt_tj.dwmc % type;
SDW xt_tj.dw % type;
Sdwzd xt_tj.dwzd % type;
Stable xt_tj.hzbmc % type;
Ssjzd xt_tj.sjzd % type;
Ssqlstr varchar2 (300 );
Icount int;
Cursor cursort is
Select XH, dwmc, hzbmc, sjzd, dwzd, DW from xt_tj;
Begin
Open cursort;
Loop
Fetch cursort into sxh, sdwmc, stable, ssjzd, sdwzd, SDW;
Exit when cursort % notfound;
Ssqlstr: = 'select count (*) from '| stable | 'where to_char (' | ssjzd | ',' | '''yyymm') =: sdate and '| sdwzd |
'In (select dwxh from xt_dw connect by prior dwxh = dwfxh start with dwxh =: SDW )';
Execute immediate ssqlstr into icount using sdate, SDW;
Delete from xt_tj_mx where fxh = sxh and SJZ = sdate;
Insert into xt_tj_mx (XH, fxh, HZSM, SJZ) values (seq_xt_tj_mx.nextval, sxh, icount, sdate );
Commit;
End loop;
End p_insert_xt_tj_mx;

Today, a statistical item is added, and dynamic cursors and archives are used.

Create or replace procedure p_insert_xt_tj_mx (sdate in varchar2) is
-- Author: Li chunlei
-- Create time: 2005.8.8
-- Purpose: update the xt_tj_mx table
Sxh xt_tj.xh % type; -- number of the master table
Sdwmc xt_tj.dwmc % type;
SDW xt_tj.dw % type;
Sdwzd xt_tj.dwzd % type;
Stable xt_tj.hzbmc % type;
Ssjzd xt_tj.sjzd % type;
Ssqlstr varchar2 (300 );
Icount int;
Itemp int;
Syear varchar2 (4 );
Sqxflbh qx_qxjl.qxflbh % type;

Cursor cursort is
Select XH, dwmc, hzbmc, sjzd, dwzd, DW from xt_tj;

Type t_cursor is ref cursor;
Qxcursor t_cursor;

Begin
Dbms_output.put_line (sdate );
Syear: = substr (sdate, 1, 4 );
Open cursort;
Loop
Fetch cursort into sxh, sdwmc, stable, ssjzd, sdwzd, SDW;
Exit when cursort % notfound;
-- Count the number of job orders of each company:
If stable = 'rw _ gzrwd 'then
Ssqlstr: = 'select count (*) from '| stable | 'where to_char (' | ssjzd | ',' | '''yyymm') =: sdate and '| sdwzd |
'In (select dwxh from xt_dw connect by prior dwxh = dwfxh start with dwxh =: SDW )';
Execute immediate ssqlstr into icount using sdate, SDW;
Delete from xt_tj_rw_gzrwd where fxh = sxh and SJZ = sdate;
Insert into xt_tj_rw_gzrwd (XH, fxh, HZSM, SJZ) values (seq_xt_tj_mx.nextval, sxh, icount, sdate );
End if;
-- Count the number of defects in each company
If stable = 'qx _ qxjl 'then
Ssqlstr: = 'select count (*), T. qxflbh from '| stable |
'T where qxdjrdw in (select dwxh from xt_dw connect by prior dwxh = dwfxh start with dwxh =: SDW) and to_char (T. fxrq, ''yyyy') =: syear group by T. qxflbh ';
Open qxcursor for ssqlstr using SDW, syear;
Loop
Fetch qxcursor into icount, sqxflbh;
Exit when qxcursor % notfound;
Dbms_output.put_line ('dwbh = '| SDW | 'flbh =' | sqxflbh | ', icount =' | icount );
Select count (*) into itemp from xt_tj_qx_qxjl t where T. dwxh = SDW and T. qxflbh = sqxflbh and T. SJZ = syear;
If itemp = 0 then
Insert into xt_tj_qx_qxjl (XH, dwxh, qxflbh, SJZ, HZSM) values (seq_xt_tj_qx_qxjl.nextval, SDW, sqxflbh, syear, icount );
Else
Update xt_tj_qx_qxjl set HZSM = icount where dwxh = SDW and qxflbh = sqxflbh and SJZ = syear;
End if;
End loop;
Close qxcursor;
End if;
End loop;
Close cursort;
Commit;
End p_insert_xt_tj_mx;