declare
--声明变量
i number;
s number;
managementBodyId number;
tenantId number;
arRptFieldRuleId number;
settleMainCode varchar2(128);
arRptFieldCfgId number;
pri number;
begin
-- into语句给变量赋值
select count(1) into s from stc.STC_AR_RPT_FIELD where ENABLED_FLAG = 'Y' and CONFIG_FILE_TYPE = 'EXCEL' and OPERATION_TYPE = 'DOMAIR' and REPORT_TYPE = 'BILL_DETAIL' and COLUMN_NAME = '税费';
--循环
for i in 1..s loop
--给变量赋值
select MANAGEMENT_BODY_ID,TENANT_ID,AR_RPT_FIELD_RULE_ID,SETTLE_MAIN_CODE,AR_RPT_FIELD_CFG_ID,PRI
into managementBodyId,tenantId,arRptFieldRuleId,settleMainCode,arRptFieldCfgId,pri from
(select rownum r,MANAGEMENT_BODY_ID,TENANT_ID,AR_RPT_FIELD_RULE_ID,SETTLE_MAIN_CODE,AR_RPT_FIELD_CFG_ID,PRI
from stc.STC_AR_RPT_FIELD
where
ENABLED_FLAG = 'Y' and CONFIG_FILE_TYPE = 'EXCEL'
and OPERATION_TYPE = 'DOMAIR' and
REPORT_TYPE = 'BILL_DETAIL' and COLUMN_NAME = '税费'
and ROWNUM <= i) where r>i-1;
INSERT INTO STC.STC_AR_RPT_FIELD (LAST_UPDATE_LOGIN, LAST_UPDATED_BY, LAST_UPDATE_DATE, CREATED_BY, CREATION_DATE, ENABLED_FLAG, MEMO, RULE, TAG, MANAGEMENT_BODY_ID, TENANT_ID, AR_RPT_FIELD_ID, AR_RPT_FIELD_RULE_ID, SETTLE_MAIN_CODE, AR_RPT_FIELD_CFG_ID, REPORT_TYPE, OPERATION_TYPE, CONFIG_FILE_TYPE, FIELD_TYPE, RPT_FIELD_NAME, FROM_BEAN_FIELD, FROM_BEAN_FIELD_TYPE, SUMMARY_FLAG, FROM_BEAN, FROM_HQL, BIND_FIELD, COLUMN_WIDTH, COLUMN_NAME, PRI)
VALUES (-1, -1, sysdate, -1, sysdate, 'Y', null, null, null, managementBodyId, tenantId, STC.STC_AR_RPT_FIELD_S.nextval, arRptFieldRuleId, settleMainCode, arRptFieldCfgId, 'BILL_DETAIL', 'DOMAIR', 'EXCEL', '2', '机建费', ':airportPrice', 'Double', 'Y', 'com.zte.settle.home.data.entity.StcArOrder', null, ':airportPrice', 14, '机建费', pri );
INSERT INTO STC.STC_AR_RPT_FIELD (LAST_UPDATE_LOGIN, LAST_UPDATED_BY, LAST_UPDATE_DATE, CREATED_BY, CREATION_DATE, ENABLED_FLAG, MEMO, RULE, TAG, MANAGEMENT_BODY_ID, TENANT_ID, AR_RPT_FIELD_ID, AR_RPT_FIELD_RULE_ID, SETTLE_MAIN_CODE, AR_RPT_FIELD_CFG_ID, REPORT_TYPE, OPERATION_TYPE, CONFIG_FILE_TYPE, FIELD_TYPE, RPT_FIELD_NAME, FROM_BEAN_FIELD, FROM_BEAN_FIELD_TYPE, SUMMARY_FLAG, FROM_BEAN, FROM_HQL, BIND_FIELD, COLUMN_WIDTH, COLUMN_NAME, PRI)
VALUES (-1, -1, sysdate, -1, sysdate, 'Y', null, null, null, managementBodyId, tenantId, STC.STC_AR_RPT_FIELD_S.nextval, arRptFieldRuleId, settleMainCode, arRptFieldCfgId, 'BILL_DETAIL', 'DOMAIR', 'EXCEL', '2', '燃油费', ':fuelPrice', 'Double', 'Y', 'com.zte.settle.home.data.entity.StcArOrder', null, ':fuelPrice', 14, '燃油费', pri );
end loop;
--提交
commit;
end;
Oracle循环处理数据
于 2019-05-09 20:58:05 首次发布