FOR get_rec_cur_ IN get_rec_ loop
INSERT INTO INFO_SERVICES_RPT(
result_key,
row_no,
parent_row_no,
s1,
s2,
s3,
s4,
s5,
s6,
s7,
s8,
s9,
s10,
s11,
s12,
s13,
s14,
d1,
d2,
d3,
d4,
n1,
n2,
n3,
n4,
n5,
n6
)values(
result_key_,
row_no_,
0,
get_rec_cur_.COMPANY_ID,
get_rec_cur_.BEGIN_QUERY_YEAR,
get_rec_cur_.BEGIN_QUERY_MONTH,
get_rec_cur_.ACCOUNT_PERIOD,
get_rec_cur_.END_QUERY_MONTH,
get_rec_cur_.END_QUERY_YEAR,
get_rec_cur_.VOUCHER_FLAG,
get_rec_cur_.COMPANY_NAME,
get_rec_cur_.S9,
get_rec_cur_.VOUCHER_NO,
get_rec_cur_.ACCOUNT_NO,
get_rec_cur_.ACCOUNT_NAME,
get_rec_cur_.ACCOUNT_CONTENT,
get_rec_cur_.DEBIT_CREDIT_FLAG,
get_rec_cur_.VOUCHER_DATE,
'',
'',
'',
get_rec_cur_.DEBIT_BALANC,
get_rec_cur_.CREDIT_BALANC,
get_rec_cur_.BALANCE,
'',
'',
'',
);
row_no_:=row_no_+1;
END loop;
INSERT INTO INFO_SERVICES_RPT(
result_key,
row_no,
parent_row_no,
s1,
s2,
s3,
s4,
s5,
s6,
s7,
s8,
s9,
s10,
s11,
s12,
s13,
s14,
d1,
d2,
d3,
d4,
n1,
n2,
n3,
n4,
n5,
n6
)values(
result_key_,
row_no_,
0,
get_rec_cur_.COMPANY_ID,
get_rec_cur_.BEGIN_QUERY_YEAR,
get_rec_cur_.BEGIN_QUERY_MONTH,
get_rec_cur_.ACCOUNT_PERIOD,
get_rec_cur_.END_QUERY_MONTH,
get_rec_cur_.END_QUERY_YEAR,
get_rec_cur_.VOUCHER_FLAG,
get_rec_cur_.COMPANY_NAME,
get_rec_cur_.S9,
get_rec_cur_.VOUCHER_NO,
get_rec_cur_.ACCOUNT_NO,
get_rec_cur_.ACCOUNT_NAME,
get_rec_cur_.ACCOUNT_CONTENT,
get_rec_cur_.DEBIT_CREDIT_FLAG,
get_rec_cur_.VOUCHER_DATE,
'',
'',
'',
get_rec_cur_.DEBIT_BALANC,
get_rec_cur_.CREDIT_BALANC,
get_rec_cur_.BALANCE,
'',
'',
'',
);
row_no_:=row_no_+1;
END loop;
不过这个看起来好别扭
select a.company,
a.accounting_year,
a.voucher_no,
a.accounting_period,
a.account,
a.account_desc,
sum(nvl(a.debet_amount,0)),
sum(nvl(a.credit_amount,0)),
a.amount,
sysdate
from vouch_row a
where a.accounting = ACCOUNT_
union
select b.company,
b.accounting_year,
b.voucher_no,
b.accounting_period,
b.account,
b.account_desc,
sum(nvl(b.debet_amount,0)),
sum(nvl(b.credit_amount,0)),
b.amount,
sysdate
from GEN_LED_VOUCH_ROW b
where b.accounting = ACCOUNT_; 程序是这样的
for循环的意思是:
当get_rec_cur_在get_rec_游标的范围内,循环进行插入操作。
for循环游标中的每一行进行数据插入也可以这样写
begin
For x In (select a.company,
a.accounting_year,
a.voucher_no,
a.accounting_period,
a.account,
a.account_desc,
sum(nvl(a.debet_amount,0)),
sum(nvl(a.credit_amount,0)),
a.amount,
sysdate
from vouch_row a
where a.accounting = ACCOUNT_
union
select b.company,
b.accounting_year,
b.voucher_no,
b.accounting_period,
b.account,
b.account_desc,
sum(nvl(b.debet_amount,0)),
sum(nvl(b.credit_amount,0)),
b.amount,
sysdate
from GEN_LED_VOUCH_ROW b
where b.accounting = ACCOUNT_)
loop
INSERT INTO INFO_SERVICES_RPT(
result_key,
row_no,
parent_row_no,
s1,
s2,
s3,
s4,
s5,
s6,
s7,
s8,
s9,
s10,
s11,
s12,
s13,
s14,
d1,
d2,
d3,
d4,
n1,
n2,
n3,
n4,
n5,
n6
)values(
result_key_,
row_no_,
0,
X.COMPANY_ID,
X.BEGIN_QUERY_YEAR,
X.BEGIN_QUERY_MONTH,
X.ACCOUNT_PERIOD,
X.END_QUERY_MONTH,
X.END_QUERY_YEAR,
X.VOUCHER_FLAG,
X.COMPANY_NAME,
X.S9,
X.VOUCHER_NO,
X.ACCOUNT_NO,
X.ACCOUNT_NAME,
X.ACCOUNT_CONTENT,
X.DEBIT_CREDIT_FLAG,
X.VOUCHER_DATE,
'',
'',
'',
X.DEBIT_BALANC,
X.CREDIT_BALANC,
X.BALANCE,
'',
'',
'',
);
row_no_:=row_no_+1;
end loop;
end;