oracle循环处理数据,Oracle循环处理数据

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;