怎么在同一个表中复制多条RECORD？ insert into table1 （COLUMN1，COLUMN2，……） select COLUMN1+2，COLUMN2，…… from table1 where .... 解决方案 » 免费领取超大流量手机卡，每月29元包185G流量+100分钟通话, 中国电信官方发货 用PL/SQLDeclare/*在此申明你的表的字段，并附初值*/Begin While 你的主键<1000（就是你想复制的条数） Loop insert into table1 （COLUMN1，COLUMN2，……）; 主键加一； （COLUMN1，COLUMN2，……） End loop; end; 比如下面的代码，是将3个表插入大量的数据，且3个表是嵌套的declare IIDS_REFERENCE_NUM NUMBER := 1 ; VERSION NUMBER(5) := 1 ; SERVICER_NUM VARCHAR2(6) := 'AMEX' ; HKMC_REFERENCE_NUM VARCHAR2(15) := 'HK-MC-01' ; PROPOSED_SETTLEMENT_MONTH VARCHAR2(20) := '2001/12' ; BATCH_REMARKS VARCHAR2(200) := 'HELLO,HONGKONG' ; BACKEND_SYSTEM_CODE CHAR(1) := 'L' ; FILE_IMPORT_LOC VARCHAR2(255) := '/export/home/' ; TOTAL_NO_OF_RECORD NUMBER(8) := 19 ; SUBMISSION_DATE DATE := TO_DATE('1998/01/01','YYYY/MM/DD') ; CREATED_DATE DATE := TO_DATE('1998/01/01','YYYY/MM/DD') ; CREATED_BY NUMBER(5) := 70 ; LAST_UPDATE_DATE DATE := TO_DATE('1999/09/09','YYYY/MM/DD') ; LAST_UPDATE_BY NUMBER(5) := 1 ; BATCH_STATUS NUMBER(3) := 1 ; SS_MORTGAGE_NUM VARCHAR2(20) ; REC_VERSION NUMBER(5) ; DOWNPAYMENT_AMOUNT NUMBER(11,2) ; ORIGINAL_MORTGAGE_PRINCIPAL NUMBER(11,2) ; ORIGINAL_MATURITY_DATE DATE ; CURRENT_OPB NUMBER(15,2) ; ORIG_MORTGAGE_DRAWDOWN_DATE DATE ; CURRENT_MATURITY_DATE DATE ; PRODUCT_TYPE CHAR(2) ; GMR_SPREAD_FIXED_RATE NUMBER(7,4) ; GMR_STRUCTURE CHAR(2) ; NEW_GMR_EFFECTIVE_DATE DATE ; NEW_GMR_SPREAD NUMBER(7,4) ; CAP_OPTIONS CHAR(1) ; REPAYMENT_HOLIDAY NUMBER(5) ; INSTALMENT_AMOUNT NUMBER(7,2) ; ORIGINATION_DTI NUMBER(5,2) ; ORIGINATION_LTV NUMBER(5,2) ; NUM_PARTIAL_PREPAYMENT NUMBER(2) ; NUM_DELINQUENCY_31_60_DAYS NUMBER(2) ; NUM_DELINQUENCY_61_90_DAYS NUMBER(2) ; NUM_DELINQUENCY_91_120_DAYS NUMBER(2) ; NUM_DELINQUENCY_121DAYS_OR_ABV NUMBER(2) ; STAFF_LOAN_OPTIONS CHAR(6) ; FIXED_GMR NUMBER(7,4) ; SUBSIDY_CAP NUMBER(7,4) ; FIXED_PRINCIPAL NUMBER(11,2) ; PRIME_HIBOR_CAP1 CHAR(10) ; GMR_SPREAD_FIXED_RATE_CAP1 NUMBER(7,4) ; PRIME_HIBOR_CAP2 CHAR(10) ; GMR_SPREAD_FIXED_RATE_CAP2 NUMBER(7,4) ; MULTIPLE_CAP_OPTION CHAR(2) ; PROPERTY_ADDRESS VARCHAR2(255) ; ESTATE_CODE VARCHAR2(5) ; ESTATE VARCHAR2(40) ; DISTRICT_CODE VARCHAR2(30) ; LOCATION VARCHAR2(3) ; GROSS_AREA NUMBER(7) ; SALEABLE_AREA NUMBER(7) ; OWNERSHIP_TYPE CHAR(2) ; NUM_UNITS_IN_DEVELOPMENT NUMBER(7) ; AMENITIES CHAR(1) ; OCCUPATION_PERMIT_DATE DATE ; ORIGINAL_VALUATION NUMBER(15,2) ; ORIGINAL_VALUATION_DATE DATE ; APPRAISER_TYPE VARCHAR2(5) ; FULL_PREPAYMENT_PENALTY VARCHAR2(30) ; FULL_EFFECTIVE_DATE DATE ; PARTIAL_PREPAYMENT_PENALTY VARCHAR2(30) ; PARTIAL_EFFECTIVE_DATE DATE ; LOAN_REASONS CHAR(1) ; CASH_OUT_IND CHAR(1) ; CASH_OUT_AMOUNT NUMBER(11,2) ; CASH_OUT_DATE DATE ; FURTHER_ADVANCE_IND VARCHAR2(2) ; FA_MORTGAGE_LINKAGE VARCHAR2(20) ; FURTHER_ADVANCE_DRAWDOWN_DATE DATE ; FURTHER_ADVANCE_DATE_OPB NUMBER(11,2) ; PARTIAL_PREPAYMENT_AMOUNT NUMBER(11,2) ; PARTIAL_PREPAID_DATE DATE ; CASH_REBATE_IND CHAR(1) ; CASH_REBATE_AMOUNT NUMBER(11,2) ; CASH_REBATE_DATE DATE ; OCCUPANCY CHAR(1) ; RESIDENCE_LENGTH NUMBER(5) ; REMARKS VARCHAR2(100) ; NUM_OBLIGORS NUMBER(2) ; NUM_BORROWER_INCOME_TO_QUALIFY NUMBER(2) ; NUM_PEOPLE_IN_HOUSEHOLD NUMBER(2) ; RECORD_STATUS NUMBER(3) ; OBLIGOR_SEQ_NUM NUMBER(2) ; BMG_IND CHAR(3) ; AGE NUMBER(2) ; OCCUPATION CHAR(4) ; EMPLOYMENT_TYPE CHAR(1) ; CURRENT_JOB_YEARS NUMBER(2) ; EDUCATION_YEARS NUMBER(2) ; TOTAL_MONTHLY_INCOME NUMBER(7,2) ; SUFFICIENT_INCOME_PROOF CHAR(1) ; NON_HOUSING_DEBT_MTHLY_PAYMENT NUMBER(11,2) ; MARTIAL_STATUS CHAR(1) ; RELATIONSHIP_WITH_BORROWER CHAR(1) ; RESIDING_YEARS NUMBER(2) ; PREVIOUS_HOME_TYPE CHAR(1) ; OWN_OTHER_PROPERTY CHAR(1) ; LIQUID_ASSETS VARCHAR2(200) ; MIP_COVERED CHAR(1) ; PRIOR_HOMEOWNER CHAR(1) ; PRIOR_MORTG_PYMT_HIST_AVAIL CHAR(1) ; PRIOR_MORTG_PYMT_HIST_NUM_LATE NUMBER(2) ; PRIOR_MORTGAGE_DEFAULT CHAR(1) ; YRS_SINCE_PRIOR_MORTG_DEFAULT NUMBER(2) ; PRIOR_BANKRUPTCY CHAR(1) ; TIME_SINCE_BANKRUPTCY NUMBER(2) ; PRIOR_JUDGMENTS CHAR(1) ; WAGES_GARNISH CHAR(1) ; NUM_CREDIT_OPENED NUMBER(2) ; NUM_CREDIT_LESS_THAN_1_YEAR NUMBER(2) ; NUM_CREDIT_LESS_THAN_2_MONTHS NUMBER(2) ; NUM_CREDIT_WITH_BALANCES NUMBER(2) ; NUM_INSTALMENT_DRAFTS NUMBER(2) ; BANK_CREDIT_CARDS_MTHLY_DRAFTS NUMBER(11,2) ; STORE_CREDIT_CARDS_MTHLY_DRAFT NUMBER(11,2) ; INSTALMENT_MONTHLY_DRAFTS NUMBER(11,2) ; NUM_CRT_30DAYS_LATE_LST_12MTHS NUMBER(2) ; NUM_CRT_30DAYS_LATE_LST_24MTHS NUMBER(2) ; NUM_CRT_60DAYS_LATE_LST_12MTHS NUMBER(2) ; NUM_CRT_60DAYS_LATE_LST_24MTHS NUMBER(2) ; NUM_CREDIT_90_DAYS_LATE NUMBER(2) ; OBLG_REMARKS VARCHAR2(100) ; begin while iids_reference_num<=12 loop insert into pmd_batch values ( IIDS_REFERENCE_NUM , VERSION , SERVICER_NUM , HKMC_REFERENCE_NUM , PROPOSED_SETTLEMENT_MONTH , BATCH_REMARKS , BACKEND_SYSTEM_CODE , FILE_IMPORT_LOC , TOTAL_NO_OF_RECORD , SUBMISSION_DATE , CREATED_DATE , CREATED_BY , LAST_UPDATE_DATE , LAST_UPDATE_BY , BATCH_STATUS ); SS_MORTGAGE_NUM := 'SS-MO-01' ; REC_VERSION := 1 ; DOWNPAYMENT_AMOUNT := 123456789.12 ; ORIGINAL_MORTGAGE_PRINCIPAL := 433434343.34 ; ORIGINAL_MATURITY_DATE := TO_DATE('2001/01/02','YYYY/MM/DD') ; CURRENT_OPB := 1234567890987.56 ; ORIG_MORTGAGE_DRAWDOWN_DATE := TO_DATE('2001/01/03','YYYY/MM/DD') ; CURRENT_MATURITY_DATE := TO_DATE('2001/01/05','YYYY/MM/DD') ; PRODUCT_TYPE := 'PR' ; GMR_SPREAD_FIXED_RATE := 234.3456 ; GMR_STRUCTURE := 'NM' ; NEW_GMR_EFFECTIVE_DATE := TO_DATE('2001/01/11','YYYY/MM/DD') ; NEW_GMR_SPREAD := 234.4567 ; CAP_OPTIONS := 'C' ; REPAYMENT_HOLIDAY := 12 ; INSTALMENT_AMOUNT := 34567.67 ; ORIGINATION_DTI := 546.56 ; ORIGINATION_LTV := 564.56 ; NUM_PARTIAL_PREPAYMENT := 99 ; NUM_DELINQUENCY_31_60_DAYS := 32 ; NUM_DELINQUENCY_61_90_DAYS := 65 ; NUM_DELINQUENCY_91_120_DAYS := 87 ; NUM_DELINQUENCY_121DAYS_OR_ABV := 98 ; STAFF_LOAN_OPTIONS := 'NORMAL' ; FIXED_GMR := 342.2332 ; SUBSIDY_CAP := 233.2322 ; FIXED_PRINCIPAL := 323232123.23 ; PRIME_HIBOR_CAP1 := 'PR' ; GMR_SPREAD_FIXED_RATE_CAP1 := 454.4534 ; PRIME_HIBOR_CAP2 := 'HI' ; GMR_SPREAD_FIXED_RATE_CAP2 := 454.4343 ; MULTIPLE_CAP_OPTION := 'HI' ; PROPERTY_ADDRESS := 'HONGKONGHUANGHOUDADAO' ; ESTATE_CODE := 'HA001' ; ESTATE := 'CONNAUGHT GARDEN' ; DISTRICT_CODE := 'HA' ; LOCATION := 'wer' ; GROSS_AREA := 34567 ; SALEABLE_AREA := 234 ; OWNERSHIP_TYPE := 'JT' ; NUM_UNITS_IN_DEVELOPMENT := 32223 ; AMENITIES := 'A' ; OCCUPATION_PERMIT_DATE := TO_DATE('2001/03/04','YYYY/MM/DD') ; ORIGINAL_VALUATION := 23233323.23 ; ORIGINAL_VALUATION_DATE := TO_DATE('2001/03/04','YYYY/MM/DD') ; APPRAISER_TYPE := 'A' ; FULL_PREPAYMENT_PENALTY := 'FDFD' ; FULL_EFFECTIVE_DATE := TO_DATE('2001/04/05','YYYY/MM/DD') ; PARTIAL_PREPAYMENT_PENALTY := 'GFGFG' ; PARTIAL_EFFECTIVE_DATE := TO_DATE('2001/06/06','YYYY/MM/DD') ; LOAN_REASONS := 'P' ; CASH_OUT_IND := 'Y' ; CASH_OUT_AMOUNT := 34343.34 ; CASH_OUT_DATE := TO_DATE('2001/04/13','YYYY/MM/DD') ; FURTHER_ADVANCE_IND := 'FS' ; FA_MORTGAGE_LINKAGE := 'CHRIS TET IT' ; FURTHER_ADVANCE_DRAWDOWN_DATE := TO_DATE('2001/04/16','YYYY/MM/DD') ; FURTHER_ADVANCE_DATE_OPB := 43434.34 ; PARTIAL_PREPAYMENT_AMOUNT := 43433.43 ; PARTIAL_PREPAID_DATE := TO_DATE('2001/04/17','YYYY/MM/DD') ; CASH_REBATE_IND := 'Y' ; CASH_REBATE_AMOUNT := 343434.34 ; CASH_REBATE_DATE := TO_DATE('2001/06/14','YYYY/MM/DD') ; OCCUPANCY := 'P' ; RESIDENCE_LENGTH := 33223 ; REMARKS := 'HELLO,ANDY' ; NUM_OBLIGORS := 23 ; NUM_BORROWER_INCOME_TO_QUALIFY := 54 ; NUM_PEOPLE_IN_HOUSEHOLD := 12 ; RECORD_STATUS := 1 ; while substr(SS_MORTGAGE_NUM,-2,2)<=19 loop insert into pmd_record values ( IIDS_REFERENCE_NUM , SS_MORTGAGE_NUM , VERSION , REC_VERSION , HKMC_REFERENCE_NUM , DOWNPAYMENT_AMOUNT , ORIGINAL_MORTGAGE_PRINCIPAL , ORIGINAL_MATURITY_DATE , CURRENT_OPB , ORIG_MORTGAGE_DRAWDOWN_DATE , CURRENT_MATURITY_DATE , PRODUCT_TYPE , GMR_SPREAD_FIXED_RATE , GMR_STRUCTURE , NEW_GMR_EFFECTIVE_DATE , NEW_GMR_SPREAD , CAP_OPTIONS , REPAYMENT_HOLIDAY , INSTALMENT_AMOUNT , ORIGINATION_DTI , ORIGINATION_LTV , NUM_PARTIAL_PREPAYMENT , NUM_DELINQUENCY_31_60_DAYS , NUM_DELINQUENCY_61_90_DAYS , NUM_DELINQUENCY_91_120_DAYS , NUM_DELINQUENCY_121DAYS_OR_ABV , STAFF_LOAN_OPTIONS , FIXED_GMR , SUBSIDY_CAP , FIXED_PRINCIPAL , PRIME_HIBOR_CAP1 , GMR_SPREAD_FIXED_RATE_CAP1 , PRIME_HIBOR_CAP2 , GMR_SPREAD_FIXED_RATE_CAP2 , MULTIPLE_CAP_OPTION , PROPERTY_ADDRESS , ESTATE_CODE , ESTATE , DISTRICT_CODE , LOCATION , GROSS_AREA , SALEABLE_AREA , OWNERSHIP_TYPE , NUM_UNITS_IN_DEVELOPMENT , AMENITIES , OCCUPATION_PERMIT_DATE , ORIGINAL_VALUATION , ORIGINAL_VALUATION_DATE , APPRAISER_TYPE , FULL_PREPAYMENT_PENALTY , FULL_EFFECTIVE_DATE , PARTIAL_PREPAYMENT_PENALTY , PARTIAL_EFFECTIVE_DATE , LOAN_REASONS , CASH_OUT_IND , CASH_OUT_AMOUNT , CASH_OUT_DATE , FURTHER_ADVANCE_IND , FA_MORTGAGE_LINKAGE , FURTHER_ADVANCE_DRAWDOWN_DATE , FURTHER_ADVANCE_DATE_OPB , PARTIAL_PREPAYMENT_AMOUNT , PARTIAL_PREPAID_DATE , CASH_REBATE_IND , CASH_REBATE_AMOUNT , CASH_REBATE_DATE , OCCUPANCY , RESIDENCE_LENGTH , REMARKS , NUM_OBLIGORS , NUM_BORROWER_INCOME_TO_QUALIFY , NUM_PEOPLE_IN_HOUSEHOLD , CREATED_DATE , CREATED_BY , LAST_UPDATE_DATE , LAST_UPDATE_BY , RECORD_STATUS ); OBLIGOR_SEQ_NUM :=1 ; BMG_IND :='BMR' ; AGE :=26 ; OCCUPATION :='OCCP' ; EMPLOYMENT_TYPE :='E' ; CURRENT_JOB_YEARS :=10 ; EDUCATION_YEARS :=5 ; TOTAL_MONTHLY_INCOME :=344 ; SUFFICIENT_INCOME_PROOF :='Y' ; NON_HOUSING_DEBT_MTHLY_PAYMENT :=4534 ; MARTIAL_STATUS :='Y' ; RELATIONSHIP_WITH_BORROWER :='R' ; RESIDING_YEARS :=12 ; PREVIOUS_HOME_TYPE :='S' ; OWN_OTHER_PROPERTY :='O' ; LIQUID_ASSETS :='GFDF' ; MIP_COVERED :='M' ; PRIOR_HOMEOWNER :='Y' ; PRIOR_MORTG_PYMT_HIST_AVAIL :='N' ; PRIOR_MORTG_PYMT_HIST_NUM_LATE :=34 ; PRIOR_MORTGAGE_DEFAULT :='Y' ; YRS_SINCE_PRIOR_MORTG_DEFAULT :=54 ; PRIOR_BANKRUPTCY :='Y' ; TIME_SINCE_BANKRUPTCY :=45 ; PRIOR_JUDGMENTS :='N' ; WAGES_GARNISH :='N' ; NUM_CREDIT_OPENED :=45 ; NUM_CREDIT_LESS_THAN_1_YEAR :=23 ; NUM_CREDIT_LESS_THAN_2_MONTHS :=56 ; NUM_CREDIT_WITH_BALANCES :=65 ; NUM_INSTALMENT_DRAFTS :=78 ; BANK_CREDIT_CARDS_MTHLY_DRAFTS :=677.76 ; STORE_CREDIT_CARDS_MTHLY_DRAFT :=5454.54 ; INSTALMENT_MONTHLY_DRAFTS :=4545.54 ; NUM_CRT_30DAYS_LATE_LST_12MTHS :=54 ; NUM_CRT_30DAYS_LATE_LST_24MTHS :=76 ; NUM_CRT_60DAYS_LATE_LST_12MTHS :=56 ; NUM_CRT_60DAYS_LATE_LST_24MTHS :=87 ; NUM_CREDIT_90_DAYS_LATE :=76 ; OBLG_REMARKS :='HELLO,REMARK' ; LAST_UPDATE_BY :=1 ; while OBLIGOR_SEQ_NUM<=6 loop insert into pmd_obligor values ( IIDS_REFERENCE_NUM , SS_MORTGAGE_NUM , OBLIGOR_SEQ_NUM , VERSION , REC_VERSION , HKMC_REFERENCE_NUM , BMG_IND , AGE , OCCUPATION , EMPLOYMENT_TYPE , CURRENT_JOB_YEARS , EDUCATION_YEARS , TOTAL_MONTHLY_INCOME , SUFFICIENT_INCOME_PROOF , NON_HOUSING_DEBT_MTHLY_PAYMENT , MARTIAL_STATUS , RELATIONSHIP_WITH_BORROWER , RESIDING_YEARS , PREVIOUS_HOME_TYPE , OWN_OTHER_PROPERTY , LIQUID_ASSETS , MIP_COVERED , PRIOR_HOMEOWNER , PRIOR_MORTG_PYMT_HIST_AVAIL , PRIOR_MORTG_PYMT_HIST_NUM_LATE , PRIOR_MORTGAGE_DEFAULT , YRS_SINCE_PRIOR_MORTG_DEFAULT , PRIOR_BANKRUPTCY , TIME_SINCE_BANKRUPTCY , PRIOR_JUDGMENTS , WAGES_GARNISH , NUM_CREDIT_OPENED , NUM_CREDIT_LESS_THAN_1_YEAR , NUM_CREDIT_LESS_THAN_2_MONTHS , NUM_CREDIT_WITH_BALANCES , NUM_INSTALMENT_DRAFTS , BANK_CREDIT_CARDS_MTHLY_DRAFTS , STORE_CREDIT_CARDS_MTHLY_DRAFT , INSTALMENT_MONTHLY_DRAFTS , NUM_CRT_30DAYS_LATE_LST_12MTHS , NUM_CRT_30DAYS_LATE_LST_24MTHS , NUM_CRT_60DAYS_LATE_LST_12MTHS , NUM_CRT_60DAYS_LATE_LST_24MTHS , NUM_CREDIT_90_DAYS_LATE , OBLG_REMARKS , LAST_UPDATE_BY ); OBLIGOR_SEQ_NUM := OBLIGOR_SEQ_NUM +1 ; end loop; convert_str(SS_MORTGAGE_NUM) ; record_status := record_status+1; end loop; IIDS_REFERENCE_NUM := IIDS_REFERENCE_NUM +1; batch_status := batch_status +1; end loop; commit; end; 直接套用Insert into ..... select ......格式：INSERT INTO TABLE_NAME1 （COLUMN1，COLUMN2，……） SELECT COLOMN+2,COLUMN2，……FROM TABLE_NAME1 WHERE CONDTION KingSunSha(弱水三千),我用的是ORACLE8.0。请问循环语句应该怎么写呢？ nononono(null,null)，l_agang(阿刚)，谢谢；使用之后可以实现数据的复制。 老鸟们帮忙看下这个是什么错误 急急 等中 如何去掉重复值的问题 排序问题 小女子有急事相求。。。。。求各位师兄，师姐帮忙了！！！！ 坐等高手解决检索问题！ 请问如何用ADO备份数据库 我给30分 我想知道，在一个表被INSERT UPDATE DELETE的时候，还有那些表也一起更新了呢？ Sql Server 最在的容量多少？ 请问如何将一部机上的sql server数据库复制到另一部机上运行？ 专题请教：如何更改图片的大小、格式 再发，Win2k下为何不能安装SQL Server? 一个小问题，希望大家多多帮忙。
/*
在此申明你的表的字段，并附初值
*/Begin
While 你的主键<1000（就是你想复制的条数） Loop
insert into table1 （COLUMN1，COLUMN2，……）;
主键加一；
（COLUMN1，COLUMN2，……）
End loop;
end;
比如下面的代码，是将3个表插入大量的数据，且3个表是嵌套的declare
IIDS_REFERENCE_NUM NUMBER := 1 ;
VERSION NUMBER(5) := 1 ;
SERVICER_NUM VARCHAR2(6) := 'AMEX' ;
HKMC_REFERENCE_NUM VARCHAR2(15) := 'HK-MC-01' ;
PROPOSED_SETTLEMENT_MONTH VARCHAR2(20) := '2001/12' ;
BATCH_REMARKS VARCHAR2(200) := 'HELLO,HONGKONG' ;
BACKEND_SYSTEM_CODE CHAR(1) := 'L' ;
FILE_IMPORT_LOC VARCHAR2(255) := '/export/home/' ;
TOTAL_NO_OF_RECORD NUMBER(8) := 19 ;
SUBMISSION_DATE DATE := TO_DATE('1998/01/01','YYYY/MM/DD') ;
CREATED_DATE DATE := TO_DATE('1998/01/01','YYYY/MM/DD') ;
CREATED_BY NUMBER(5) := 70 ;
LAST_UPDATE_DATE DATE := TO_DATE('1999/09/09','YYYY/MM/DD') ;
LAST_UPDATE_BY NUMBER(5) := 1 ;
BATCH_STATUS NUMBER(3) := 1 ;
SS_MORTGAGE_NUM VARCHAR2(20) ;
REC_VERSION NUMBER(5) ;
DOWNPAYMENT_AMOUNT NUMBER(11,2) ;
ORIGINAL_MORTGAGE_PRINCIPAL NUMBER(11,2) ;
ORIGINAL_MATURITY_DATE DATE ;
CURRENT_OPB NUMBER(15,2) ;
ORIG_MORTGAGE_DRAWDOWN_DATE DATE ;
CURRENT_MATURITY_DATE DATE ;
PRODUCT_TYPE CHAR(2) ;
GMR_SPREAD_FIXED_RATE NUMBER(7,4) ;
GMR_STRUCTURE CHAR(2) ;
NEW_GMR_EFFECTIVE_DATE DATE ;
NEW_GMR_SPREAD NUMBER(7,4) ;
CAP_OPTIONS CHAR(1) ;
REPAYMENT_HOLIDAY NUMBER(5) ;
INSTALMENT_AMOUNT NUMBER(7,2) ;
ORIGINATION_DTI NUMBER(5,2) ;
ORIGINATION_LTV NUMBER(5,2) ;
NUM_PARTIAL_PREPAYMENT NUMBER(2) ;
NUM_DELINQUENCY_31_60_DAYS NUMBER(2) ;
NUM_DELINQUENCY_61_90_DAYS NUMBER(2) ;
NUM_DELINQUENCY_91_120_DAYS NUMBER(2) ;
NUM_DELINQUENCY_121DAYS_OR_ABV NUMBER(2) ;
STAFF_LOAN_OPTIONS CHAR(6) ;
FIXED_GMR NUMBER(7,4) ;
SUBSIDY_CAP NUMBER(7,4) ;
FIXED_PRINCIPAL NUMBER(11,2) ;
PRIME_HIBOR_CAP1 CHAR(10) ;
GMR_SPREAD_FIXED_RATE_CAP1 NUMBER(7,4) ;
PRIME_HIBOR_CAP2 CHAR(10) ;
GMR_SPREAD_FIXED_RATE_CAP2 NUMBER(7,4) ;
MULTIPLE_CAP_OPTION CHAR(2) ;
PROPERTY_ADDRESS VARCHAR2(255) ;
ESTATE_CODE VARCHAR2(5) ;
ESTATE VARCHAR2(40) ;
DISTRICT_CODE VARCHAR2(30) ;
LOCATION VARCHAR2(3) ;
GROSS_AREA NUMBER(7) ;
SALEABLE_AREA NUMBER(7) ;
OWNERSHIP_TYPE CHAR(2) ;
NUM_UNITS_IN_DEVELOPMENT NUMBER(7) ;
AMENITIES CHAR(1) ;
OCCUPATION_PERMIT_DATE DATE ;
ORIGINAL_VALUATION NUMBER(15,2) ;
ORIGINAL_VALUATION_DATE DATE ;
APPRAISER_TYPE VARCHAR2(5) ;
FULL_PREPAYMENT_PENALTY VARCHAR2(30) ;
FULL_EFFECTIVE_DATE DATE ;
PARTIAL_PREPAYMENT_PENALTY VARCHAR2(30) ;
PARTIAL_EFFECTIVE_DATE DATE ;
LOAN_REASONS CHAR(1) ;
CASH_OUT_IND CHAR(1) ;
CASH_OUT_AMOUNT NUMBER(11,2) ;
CASH_OUT_DATE DATE ;
FURTHER_ADVANCE_IND VARCHAR2(2) ;
FA_MORTGAGE_LINKAGE VARCHAR2(20) ;
FURTHER_ADVANCE_DRAWDOWN_DATE DATE ;
FURTHER_ADVANCE_DATE_OPB NUMBER(11,2) ;
PARTIAL_PREPAYMENT_AMOUNT NUMBER(11,2) ;
PARTIAL_PREPAID_DATE DATE ;
CASH_REBATE_IND CHAR(1) ;
CASH_REBATE_AMOUNT NUMBER(11,2) ;
CASH_REBATE_DATE DATE ;
OCCUPANCY CHAR(1) ;
RESIDENCE_LENGTH NUMBER(5) ;
REMARKS VARCHAR2(100) ;
NUM_OBLIGORS NUMBER(2) ;
NUM_BORROWER_INCOME_TO_QUALIFY NUMBER(2) ;
NUM_PEOPLE_IN_HOUSEHOLD NUMBER(2) ;
RECORD_STATUS NUMBER(3) ;
OBLIGOR_SEQ_NUM NUMBER(2) ;
BMG_IND CHAR(3) ;
AGE NUMBER(2) ;
OCCUPATION CHAR(4) ;
EMPLOYMENT_TYPE CHAR(1) ;
CURRENT_JOB_YEARS NUMBER(2) ;
EDUCATION_YEARS NUMBER(2) ;
TOTAL_MONTHLY_INCOME NUMBER(7,2) ;
SUFFICIENT_INCOME_PROOF CHAR(1) ;
NON_HOUSING_DEBT_MTHLY_PAYMENT NUMBER(11,2) ;
MARTIAL_STATUS CHAR(1) ;
RELATIONSHIP_WITH_BORROWER CHAR(1) ;
RESIDING_YEARS NUMBER(2) ;
PREVIOUS_HOME_TYPE CHAR(1) ;
OWN_OTHER_PROPERTY CHAR(1) ;
LIQUID_ASSETS VARCHAR2(200) ;
MIP_COVERED CHAR(1) ;
PRIOR_HOMEOWNER CHAR(1) ;
PRIOR_MORTG_PYMT_HIST_AVAIL CHAR(1) ;
PRIOR_MORTG_PYMT_HIST_NUM_LATE NUMBER(2) ;
PRIOR_MORTGAGE_DEFAULT CHAR(1) ;
YRS_SINCE_PRIOR_MORTG_DEFAULT NUMBER(2) ;
PRIOR_BANKRUPTCY CHAR(1) ;
TIME_SINCE_BANKRUPTCY NUMBER(2) ;
PRIOR_JUDGMENTS CHAR(1) ;
WAGES_GARNISH CHAR(1) ;
NUM_CREDIT_OPENED NUMBER(2) ;
NUM_CREDIT_LESS_THAN_1_YEAR NUMBER(2) ;
NUM_CREDIT_LESS_THAN_2_MONTHS NUMBER(2) ;
NUM_CREDIT_WITH_BALANCES NUMBER(2) ;
NUM_INSTALMENT_DRAFTS NUMBER(2) ;
BANK_CREDIT_CARDS_MTHLY_DRAFTS NUMBER(11,2) ;
STORE_CREDIT_CARDS_MTHLY_DRAFT NUMBER(11,2) ;
INSTALMENT_MONTHLY_DRAFTS NUMBER(11,2) ;
NUM_CRT_30DAYS_LATE_LST_12MTHS NUMBER(2) ;
NUM_CRT_30DAYS_LATE_LST_24MTHS NUMBER(2) ;
NUM_CRT_60DAYS_LATE_LST_12MTHS NUMBER(2) ;
NUM_CRT_60DAYS_LATE_LST_24MTHS NUMBER(2) ;
NUM_CREDIT_90_DAYS_LATE NUMBER(2) ;
OBLG_REMARKS VARCHAR2(100) ;
begin
while iids_reference_num<=12 loop
insert into pmd_batch values
(
IIDS_REFERENCE_NUM ,
VERSION ,
SERVICER_NUM ,
HKMC_REFERENCE_NUM ,
PROPOSED_SETTLEMENT_MONTH ,
BATCH_REMARKS ,
BACKEND_SYSTEM_CODE ,
FILE_IMPORT_LOC ,
TOTAL_NO_OF_RECORD ,
SUBMISSION_DATE ,
CREATED_DATE ,
CREATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATE_BY ,
BATCH_STATUS
);
SS_MORTGAGE_NUM := 'SS-MO-01' ;
REC_VERSION := 1 ;
DOWNPAYMENT_AMOUNT := 123456789.12 ;
ORIGINAL_MORTGAGE_PRINCIPAL := 433434343.34 ;
ORIGINAL_MATURITY_DATE := TO_DATE('2001/01/02','YYYY/MM/DD') ;
CURRENT_OPB := 1234567890987.56 ;
ORIG_MORTGAGE_DRAWDOWN_DATE := TO_DATE('2001/01/03','YYYY/MM/DD') ;
CURRENT_MATURITY_DATE := TO_DATE('2001/01/05','YYYY/MM/DD') ;
PRODUCT_TYPE := 'PR' ;
GMR_SPREAD_FIXED_RATE := 234.3456 ;
GMR_STRUCTURE := 'NM' ;
NEW_GMR_EFFECTIVE_DATE := TO_DATE('2001/01/11','YYYY/MM/DD') ;
NEW_GMR_SPREAD := 234.4567 ;
CAP_OPTIONS := 'C' ;
REPAYMENT_HOLIDAY := 12 ;
INSTALMENT_AMOUNT := 34567.67 ;
ORIGINATION_DTI := 546.56 ;
ORIGINATION_LTV := 564.56 ;
NUM_PARTIAL_PREPAYMENT := 99 ;
NUM_DELINQUENCY_31_60_DAYS := 32 ;
NUM_DELINQUENCY_61_90_DAYS := 65 ;
NUM_DELINQUENCY_91_120_DAYS := 87 ;
NUM_DELINQUENCY_121DAYS_OR_ABV := 98 ;
STAFF_LOAN_OPTIONS := 'NORMAL' ;
FIXED_GMR := 342.2332 ;
SUBSIDY_CAP := 233.2322 ;
FIXED_PRINCIPAL := 323232123.23 ;
PRIME_HIBOR_CAP1 := 'PR' ;
GMR_SPREAD_FIXED_RATE_CAP1 := 454.4534 ;
PRIME_HIBOR_CAP2 := 'HI' ;
GMR_SPREAD_FIXED_RATE_CAP2 := 454.4343 ;
MULTIPLE_CAP_OPTION := 'HI' ;
PROPERTY_ADDRESS := 'HONGKONGHUANGHOUDADAO' ;
ESTATE_CODE := 'HA001' ;
ESTATE := 'CONNAUGHT GARDEN' ;
DISTRICT_CODE := 'HA' ;
LOCATION := 'wer' ;
GROSS_AREA := 34567 ;
SALEABLE_AREA := 234 ;
OWNERSHIP_TYPE := 'JT' ;
NUM_UNITS_IN_DEVELOPMENT := 32223 ;
AMENITIES := 'A' ;
OCCUPATION_PERMIT_DATE := TO_DATE('2001/03/04','YYYY/MM/DD') ;
ORIGINAL_VALUATION := 23233323.23 ;
ORIGINAL_VALUATION_DATE := TO_DATE('2001/03/04','YYYY/MM/DD') ;
APPRAISER_TYPE := 'A' ;
FULL_PREPAYMENT_PENALTY := 'FDFD' ;
FULL_EFFECTIVE_DATE := TO_DATE('2001/04/05','YYYY/MM/DD') ;
PARTIAL_PREPAYMENT_PENALTY := 'GFGFG' ;
PARTIAL_EFFECTIVE_DATE := TO_DATE('2001/06/06','YYYY/MM/DD') ;
LOAN_REASONS := 'P' ;
CASH_OUT_IND := 'Y' ;
CASH_OUT_AMOUNT := 34343.34 ;
CASH_OUT_DATE := TO_DATE('2001/04/13','YYYY/MM/DD') ;
FURTHER_ADVANCE_IND := 'FS' ;
FA_MORTGAGE_LINKAGE := 'CHRIS TET IT' ;
FURTHER_ADVANCE_DRAWDOWN_DATE := TO_DATE('2001/04/16','YYYY/MM/DD') ;
FURTHER_ADVANCE_DATE_OPB := 43434.34 ;
PARTIAL_PREPAYMENT_AMOUNT := 43433.43 ;
PARTIAL_PREPAID_DATE := TO_DATE('2001/04/17','YYYY/MM/DD') ;
CASH_REBATE_IND := 'Y' ;
CASH_REBATE_AMOUNT := 343434.34 ;
CASH_REBATE_DATE := TO_DATE('2001/06/14','YYYY/MM/DD') ;
OCCUPANCY := 'P' ;
RESIDENCE_LENGTH := 33223 ;
REMARKS := 'HELLO,ANDY' ;
NUM_OBLIGORS := 23 ;
NUM_BORROWER_INCOME_TO_QUALIFY := 54 ;
NUM_PEOPLE_IN_HOUSEHOLD := 12 ;
RECORD_STATUS := 1 ;
while substr(SS_MORTGAGE_NUM,-2,2)<=19 loop
insert into pmd_record values
(
IIDS_REFERENCE_NUM ,
SS_MORTGAGE_NUM ,
VERSION ,
REC_VERSION ,
HKMC_REFERENCE_NUM ,
DOWNPAYMENT_AMOUNT ,
ORIGINAL_MORTGAGE_PRINCIPAL ,
ORIGINAL_MATURITY_DATE ,
CURRENT_OPB ,
ORIG_MORTGAGE_DRAWDOWN_DATE ,
CURRENT_MATURITY_DATE ,
PRODUCT_TYPE ,
GMR_SPREAD_FIXED_RATE ,
GMR_STRUCTURE ,
NEW_GMR_EFFECTIVE_DATE ,
NEW_GMR_SPREAD ,
CAP_OPTIONS ,
REPAYMENT_HOLIDAY ,
INSTALMENT_AMOUNT ,
ORIGINATION_DTI ,
ORIGINATION_LTV ,
NUM_PARTIAL_PREPAYMENT ,
NUM_DELINQUENCY_31_60_DAYS ,
NUM_DELINQUENCY_61_90_DAYS ,
NUM_DELINQUENCY_91_120_DAYS ,
NUM_DELINQUENCY_121DAYS_OR_ABV ,
STAFF_LOAN_OPTIONS ,
FIXED_GMR ,
SUBSIDY_CAP ,
FIXED_PRINCIPAL ,
PRIME_HIBOR_CAP1 ,
GMR_SPREAD_FIXED_RATE_CAP1 ,
PRIME_HIBOR_CAP2 ,
GMR_SPREAD_FIXED_RATE_CAP2 ,
MULTIPLE_CAP_OPTION ,
PROPERTY_ADDRESS ,
ESTATE_CODE ,
ESTATE ,
DISTRICT_CODE ,
LOCATION ,
GROSS_AREA ,
SALEABLE_AREA ,
OWNERSHIP_TYPE ,
NUM_UNITS_IN_DEVELOPMENT ,
AMENITIES ,
OCCUPATION_PERMIT_DATE ,
ORIGINAL_VALUATION ,
ORIGINAL_VALUATION_DATE ,
APPRAISER_TYPE ,
FULL_PREPAYMENT_PENALTY ,
FULL_EFFECTIVE_DATE ,
PARTIAL_PREPAYMENT_PENALTY ,
PARTIAL_EFFECTIVE_DATE ,
LOAN_REASONS ,
CASH_OUT_IND ,
CASH_OUT_AMOUNT ,
CASH_OUT_DATE ,
FURTHER_ADVANCE_IND ,
FA_MORTGAGE_LINKAGE ,
FURTHER_ADVANCE_DRAWDOWN_DATE ,
FURTHER_ADVANCE_DATE_OPB ,
PARTIAL_PREPAYMENT_AMOUNT ,
PARTIAL_PREPAID_DATE ,
CASH_REBATE_IND ,
CASH_REBATE_AMOUNT ,
CASH_REBATE_DATE ,
OCCUPANCY ,
RESIDENCE_LENGTH ,
REMARKS ,
NUM_OBLIGORS ,
NUM_BORROWER_INCOME_TO_QUALIFY ,
NUM_PEOPLE_IN_HOUSEHOLD ,
CREATED_DATE ,
CREATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATE_BY ,
RECORD_STATUS
);
OBLIGOR_SEQ_NUM :=1 ;
BMG_IND :='BMR' ;
AGE :=26 ;
OCCUPATION :='OCCP' ;
EMPLOYMENT_TYPE :='E' ;
CURRENT_JOB_YEARS :=10 ;
EDUCATION_YEARS :=5 ;
TOTAL_MONTHLY_INCOME :=344 ;
SUFFICIENT_INCOME_PROOF :='Y' ;
NON_HOUSING_DEBT_MTHLY_PAYMENT :=4534 ;
MARTIAL_STATUS :='Y' ;
RELATIONSHIP_WITH_BORROWER :='R' ;
RESIDING_YEARS :=12 ;
PREVIOUS_HOME_TYPE :='S' ;
OWN_OTHER_PROPERTY :='O' ;
LIQUID_ASSETS :='GFDF' ;
MIP_COVERED :='M' ;
PRIOR_HOMEOWNER :='Y' ;
PRIOR_MORTG_PYMT_HIST_AVAIL :='N' ;
PRIOR_MORTG_PYMT_HIST_NUM_LATE :=34 ;
PRIOR_MORTGAGE_DEFAULT :='Y' ;
YRS_SINCE_PRIOR_MORTG_DEFAULT :=54 ;
PRIOR_BANKRUPTCY :='Y' ;
TIME_SINCE_BANKRUPTCY :=45 ;
PRIOR_JUDGMENTS :='N' ;
WAGES_GARNISH :='N' ;
NUM_CREDIT_OPENED :=45 ;
NUM_CREDIT_LESS_THAN_1_YEAR :=23 ;
NUM_CREDIT_LESS_THAN_2_MONTHS :=56 ;
NUM_CREDIT_WITH_BALANCES :=65 ;
NUM_INSTALMENT_DRAFTS :=78 ;
BANK_CREDIT_CARDS_MTHLY_DRAFTS :=677.76 ;
STORE_CREDIT_CARDS_MTHLY_DRAFT :=5454.54 ;
INSTALMENT_MONTHLY_DRAFTS :=4545.54 ;
NUM_CRT_30DAYS_LATE_LST_12MTHS :=54 ;
NUM_CRT_30DAYS_LATE_LST_24MTHS :=76 ;
NUM_CRT_60DAYS_LATE_LST_12MTHS :=56 ;
NUM_CRT_60DAYS_LATE_LST_24MTHS :=87 ;
NUM_CREDIT_90_DAYS_LATE :=76 ;
OBLG_REMARKS :='HELLO,REMARK' ;
LAST_UPDATE_BY :=1 ;
while OBLIGOR_SEQ_NUM<=6 loop
insert into pmd_obligor values
(
IIDS_REFERENCE_NUM ,
SS_MORTGAGE_NUM ,
OBLIGOR_SEQ_NUM ,
VERSION ,
REC_VERSION ,
HKMC_REFERENCE_NUM ,
BMG_IND ,
AGE ,
OCCUPATION ,
EMPLOYMENT_TYPE ,
CURRENT_JOB_YEARS ,
EDUCATION_YEARS ,
TOTAL_MONTHLY_INCOME ,
SUFFICIENT_INCOME_PROOF ,
NON_HOUSING_DEBT_MTHLY_PAYMENT ,
MARTIAL_STATUS ,
RELATIONSHIP_WITH_BORROWER ,
RESIDING_YEARS ,
PREVIOUS_HOME_TYPE ,
OWN_OTHER_PROPERTY ,
LIQUID_ASSETS ,
MIP_COVERED ,
PRIOR_HOMEOWNER ,
PRIOR_MORTG_PYMT_HIST_AVAIL ,
PRIOR_MORTG_PYMT_HIST_NUM_LATE ,
PRIOR_MORTGAGE_DEFAULT ,
YRS_SINCE_PRIOR_MORTG_DEFAULT ,
PRIOR_BANKRUPTCY ,
TIME_SINCE_BANKRUPTCY ,
PRIOR_JUDGMENTS ,
WAGES_GARNISH ,
NUM_CREDIT_OPENED ,
NUM_CREDIT_LESS_THAN_1_YEAR ,
NUM_CREDIT_LESS_THAN_2_MONTHS ,
NUM_CREDIT_WITH_BALANCES ,
NUM_INSTALMENT_DRAFTS ,
BANK_CREDIT_CARDS_MTHLY_DRAFTS ,
STORE_CREDIT_CARDS_MTHLY_DRAFT ,
INSTALMENT_MONTHLY_DRAFTS ,
NUM_CRT_30DAYS_LATE_LST_12MTHS ,
NUM_CRT_30DAYS_LATE_LST_24MTHS ,
NUM_CRT_60DAYS_LATE_LST_12MTHS ,
NUM_CRT_60DAYS_LATE_LST_24MTHS ,
NUM_CREDIT_90_DAYS_LATE ,
OBLG_REMARKS ,
LAST_UPDATE_BY
);
OBLIGOR_SEQ_NUM := OBLIGOR_SEQ_NUM +1 ;
end loop;
convert_str(SS_MORTGAGE_NUM) ;
record_status := record_status+1;
end loop;
IIDS_REFERENCE_NUM := IIDS_REFERENCE_NUM +1;
batch_status := batch_status +1;
end loop;
commit;
end;
INSERT INTO TABLE_NAME1 （COLUMN1，COLUMN2，……） SELECT COLOMN+2,COLUMN2，……FROM TABLE_NAME1 WHERE CONDTION