Declare
V_billno varchar2 (40);
V_x number;
V_j varchar2 (40);
V_begin varchar2 (40);
V_end varchar2 (40);
V_sql varchar2 (4000);
V_je number;
The begin
- cycle take temporary table
For rec in (select *
The from t_ct_xsrw) loop
V_billno:=f_get_serial (RSL, null);
- start insert
Insert into t_md_sale_task_resolve_h
(resolveno, compid busno task_year, sale_amount, gross_amount, lastmodify, lasttime, status, execdate, checker1,
Checkbit1 createuser, createtime, checkdate1, notes, checkbit2, checkbit3, checkbit4 checkbit5)
The select v_billno, 1, rec. Busno, 2021,
Rec. A1 + rec. A2 + rec. A3 + rec. A4 + rec. A5 + rec. A6 + rec. A7 + rec. A8 + rec. A9 + rec. + rec a10. A11 +
Rec. A12, 0, 168, sysdate, 0, null, null, 0, 168, sysdate, sysdate, 'import', 0, 0, 0, 0
The from dual;
Cycle started in December -
V_x:=0;
While v_x & lt; 11 loop=
V_x:=v_x + 1;
Special format - save variable
Select 'rec. A' | | to_char (v_x)
Into v_j
The from dual;
V_sql:='select' | | v_j | | 'from dual';
Dbms_output. Put_line (v_sql);
the execute immediate v_sql into v_je;
- the first day of this month,
SELECT TO_CHAR (TRUNC (to_date (' 2021 - '| | TO_CHAR (v_x,' 00 ') | | '15', '- DD YYYY - MM'), 'MM'), '- DD YYYY - MM)
Into v_begin
The FROM DUAL;
- the last day of this month the SELECT TO_CHAR (LAST_DAY (SYSDATE), '- DD YYYY - MM) "on the last day of this month FROM DUAL;
The SELECT TO_CHAR (LAST_DAY (to_date (' 2021 - '| | TO_CHAR (v_x,' 00 ') | | '15', '- DD YYYY - MM)),' - DD YYYY - MM)
Into v_end
The FROM DUAL;
Dbms_output. Put_line (v_j | | ', '| | rec. A1);
- schedule of insert
V_sql:="insert into t_md_sale_task_resolve_d (resolveno, rowno, task_year task_month, sale_amount, gross_amount, startdate, enddate) '| |
'select' ' '| | v_billno | |' ' ', '| | v_x | |', 2021, '| | v_x | |', round (' | | v_je | | ', 2) '| |
', 0, to_date (" '| | v_begin | |' ' ', '- dd yyyy - mm "), to_date ("' | | v_end | |
"', 'yyyy - mm - dd' ') from dual ';
- output variable v_sql
Dbms_output. Put_line (rec. A1);
Dbms_output. Put_line (v_sql);
The execute immediate v_sql;
commit;
End loop;
End loop;
end;
The first paragraph of the execute immediate v_sql into v_je error, ORA - 00904: "REC". "A1" : identifier is invalid
ORA - 06512: in line 35,
Don't understand why complains,
Execute statement: select a1 from t_ct_mdrw no problem;
For advice on how to work out great god, the application of the dynamic field
CodePudding user response:
Affiliated predicate sentence:- Create table
The create table T_CT_XSRW
(
Business agency VARCHAR2 (255),
A1 NUMBER,
A2 NUMBER,
A3 NUMBER,
A4 NUMBER,
A5 NUMBER,
A6 NUMBER,
A7 NUMBER,
A8 NUMBER,
A9 NUMBER,
A10 NUMBER,
A11 NUMBER,
A12 NUMBER
)
In tablespace H2_BUSI
Pctfree 10
Initrans 1
Maxtrans 255
Storage
(
Initial 64 k
Next 1 m
Minextents 1
The maxextents unlimited
);