Oracle 存储过程之批量添加数据

3/3/2017来源:C/C++教程人气:3431

–vc_trade_id 复制交易方案id –vc_new_trade_id 新id –vc_scheme_date 系统逻辑时间 –l_trade_stage 交易方案轮次 –vc_trading_schemename 交易方案名称 –项目id create or replace PRocedure procedureName (vc_trade_ids in varchar2, vc_new_trade_ids in varchar2, vc_scheme_dates in varchar2, l_trade_stages in varchar2, vc_trading_schemenames in varchar2, vc_pro_id in varchar2) is

TYPE ref_cursor_type IS REF CURSOR; –动态关联结果集的临时对象 引用游标类型 dataCursor ref_cursor_type; –游标变量。 LOCAL_SQL varchar(8000); –存放临时sql –添加公允价值 临时存储变量,没有什么字段匹配排序存储值 fairValue varchar(200); fundId varchar(200); fundname varchar(200); tradingschemespv varchar(200); toumoney varchar(200); toucompanymoney varchar(200); tradeid varchar(200); chinafundid varchar(200); proforma varchar(200); gongyuncurrency varchar(200); –其他事项 atterName varchar(200); detailDescription varchar(200); begin –交易方案新增 insert into 交易方案 ( L_TRADE_ID ,L_PRO_ID ,L_FUND_ID ,L_TRADE_AMT ,L_STOCK_RATIO ,L_BEFORE_WORTH ,VC_BEFORE_CURRENCY ,L_TURN_WORTH ,VC_TURN_CURRENCY ,L_FINANCING_AMT ,VC_FINANCING_CURRENCY ,VC_BOARD_SEATS ,L_REMOVE_PERIOD ,VC_CONTRACT_SIGNING ,VC_IPO_PLAN ,VC_CLEAR_DATE ,VC_FIRST_BUY ,VC_FIRST_SUBSCRIBE ,VC_WARRANT_RIGHT ,VC_CO_SALE ,VC_OPPOSE_DILUTION ,vc_currency ,p_trade_description ,VC_SCHEME_NAME ,VC_SCHEME_DATA ,VC_IS_FINAL ,TB_SHARECLASS ,TB_AGELIMIT ,TB_REPORATE ,TB_REPURCHASEREMARK ,TB_LIQUIDATIONPREFERENCE ,TB_LIQUIDATIONREMARK ,TB_SHARESCASHPLAN ,TB_SHARESCASHREMARK ,TB_BSASSIGNEDPERSONNEL ,TB_BOARDOBSERVERSEAT ,TB_BOSASSIGNEDPERSONNEL ,TB_DIVIDENDPRIORITY ,TB_DIVIDENDPRIREMARK ,TB_SALEPRIORITY ,TB_SALEPRIREMARK ,TB_REGISTRATIONRIGHTS ,TB_YXGMQANDTSQ ,TB_ANTIDILUTION ,TB_ANTIDILUTION_remark ,TB_RESTRICTIVEREGULATIONS ,TB_RIGHTTOINFORMATION ,TB_SHARES ,TB_EXPPRICE ,TB_ORIGINALNOTEAMOUNT ,TB_EXPDATE ,TB_MATURITYDATE ,TB_INTERESTRATE ,Operationtime ,tb_Proportion ,vc_Due_date ,l_Price_per_share ,L_MY_TRADE_AMT ,l_number_Of_Shares ,L_Company_valuation_r ,L_Company_valuation_d ,L_BefCompany_valuation_r ,L_BefCompany_valuation_d ,vc_SHARECLASS_jw ,vc_New_old_shares ,VC_Stock_cash_plan ,VC_Stock_option ,VC_STOCT_START_TIME ,vc_stoct_end_time ,vc_Stock_option_remarks ,VC_Signed_right ,vc_Signed_right_remarks ,vc_protective_terms ,vc_pro_terms_Remarks ,VC_OP_DIL_Remarks ,VC_Val_adj ,VC_Val_adj_Remarks ,vc_Jurisdiction_law ,VC_Dispute_settlement ,L_total_number ,L_GP_PRICE ,L_rp_price , vc_manager_valuation ,vc_Loan_subject ,l_Loan_amount ,vc_Loan_term ,l_coupon_rate ,vc_interest_time ,vc_debt_to_equity ,VC_Con_price ,VC_Con_price_remarks ,vc_Debt_trigger_event ,vc_Debt_trigger_time ,vc_pe_breach_contract ,vc_Default_event ,vc_Protective_terms_dk ,vc_Pro_terms_remarks_dk ,vc_guarantee ,vc_guarantee_remarks ,vc_Guarantee_mode ,vc_Jurisdiction_law_dk ,vc_Dispute_settlement_dk ,vc_Other_spe_agreements ,vc_newOldSharesRemark ,l_numberOfShares ,l_price_Of_Shares ,l_amt_Of_Shares ,vc_firstSubscribeRemark ,vc_yxgmAndTsqRemark ,vc_RegistrationRightsRemark ,vc_rightToInformationRemark ,vc_restrictivereRemark ,vc_mirrorholding ,vc_mirrorholdingRemark ,L_INVESTMENT_AMOUNT ,L_NUMBEROFSHARESM ,L_NUMBEROFSHARESS ,VC_MTI_TRUST ,VC_NEWSCHEME_DATA ,VC_FUNDBODY1 ,VC_FAIRVALUE1 ,VC_FAIRVALUECURRENCY1 ,VC_FUNDBODY2 ,VC_FAIRVALUE2 ,VC_FAIRVALUECURRENCY2 ,VC_FUNDBODY3 ,VC_FAIRVALUE3 ,VC_FAIRVALUECURRENCY3 ,vc_maximumChargeLimit ,vc_maximumChargeLimitCurrency ,vc_exclusivePeriod ,vc_legalServiceFee ,vc_legalServiceFeeCurrency ,vc_financialServiceFee ,vc_financialServiceFeeCurrency ,vc_lawFirm ,vc_accountingFirm ,vc_boolBoardGuoqiao ,vc_boolBoardSPA ,vc_loanSigningTime ,l_spv_id ,VC_OTHERUSER ,L_MIMONEY ,VC_bridgeAcrossTime ,VC_bridgeSpvTime ,L_numberofSharesCompany ,L_beforeStockRatio ,vc_equalCurrency ,L_equalmyTradeAmt ,vc_signedCurrency ,vc_boardseats ,vc_thereObservers ,vc_director ,vc_directorRemark ,vc_islpac ,vc_lpacTime ,vc_isBoardbridge ,vc_isBoardbridgeTime ,vc_isFileBoardbridge ,vc_isFileBoardbridgeTime ,vc_loanAmountCurrency ,L_newStockRatio ,vc_companyvaluationrcurrency ,vc_companyvaluationdcurrency ,vc_befcpyvaluationrcurrency ,vc_befcpyvaluationdcurrency ,vc_founder_equity_staging ,l_trade_stage ,l_loan_balance ,vc_actual_payment_time ,l_interest ,vc_reimbursement_turn_time ,vc_is_board_seats ,vc_is_observe_the_seats ,vc_is_shareholders_seats ,vc_is_shareholders_veto ,vc_is_director_veto ,vc_trading_schemeName ,l_thigh_registered_capital ,l_financing_amount ,D_update_time ,update_time) select vc_new_trade_ids , vc_pro_id ,’-1’ ,ptb.l_trade_amt ,ptb.l_stock_ratio ,ptb.l_before_worth ,ptb.vc_before_currency ,ptb.l_turn_worth ,ptb.vc_turn_currency ,ptb.l_financing_amt ,ptb.vc_financing_currency ,ptb.vc_board_seats ,ptb.l_remove_period ,ptb.vc_contract_signing ,ptb.vc_ipo_plan ,ptb.vc_clear_date ,ptb.vc_first_buy ,ptb.vc_first_subscribe ,ptb.vc_warrant_right ,ptb.vc_co_sale ,ptb.vc_oppose_dilution ,ptb.vc_currency ,ptb.p_trade_description ,ptb.vc_scheme_name ,vc_scheme_dates ,ptb.vc_is_final ,ptb.tb_shareclass ,ptb.tb_agelimit ,ptb.tb_reporate ,ptb.tb_repurchaseremark ,ptb.tb_liquidationpreference ,ptb.tb_liquidationremark ,ptb.tb_sharescashplan ,ptb.tb_sharescashremark ,ptb.tb_bsassignedpersonnel ,ptb.tb_boardobserverseat ,ptb.tb_bosassignedpersonnel ,ptb.tb_dividendpriority ,ptb.tb_dividendpriremark ,ptb.tb_salepriority ,ptb.tb_salepriremark ,ptb.tb_registrationrights ,ptb.tb_yxgmqandtsq ,ptb.tb_antidilution ,ptb.tb_antidilution_remark ,ptb.tb_restrictiveregulations ,ptb.tb_righttoinformation ,ptb.tb_shares ,ptb.tb_expprice ,ptb.tb_originalnoteamount ,ptb.tb_expdate ,ptb.tb_maturitydate ,ptb.tb_interestrate ,sysdate ,ptb.tb_proportion ,ptb.vc_due_date ,ptb.l_price_per_share ,ptb.l_my_trade_amt ,ptb.l_number_of_shares ,ptb.l_company_valuation_r ,ptb.l_company_valuation_d ,ptb.l_befcompany_valuation_r ,ptb.l_befcompany_valuation_d ,ptb.vc_shareclass_jw ,ptb.vc_new_old_shares ,ptb.vc_stock_cash_plan ,ptb.vc_stock_option ,ptb.vc_stoct_start_time ,ptb.vc_stoct_end_time ,ptb.vc_stock_option_remarks ,ptb.vc_signed_right ,ptb.vc_signed_right_remarks ,ptb.vc_protective_terms ,ptb.vc_pro_terms_remarks ,ptb.vc_op_dil_remarks ,ptb.vc_val_adj ,ptb.vc_val_adj_remarks ,ptb.vc_jurisdiction_law ,to_number(ptb.vc_dispute_settlement) ,ptb.l_total_number ,ptb.l_gp_price ,ptb.l_rp_price ,ptb.vc_manager_valuation ,ptb.vc_loan_subject ,ptb.l_loan_amount ,ptb.vc_loan_term ,ptb.l_coupon_rate ,ptb.vc_interest_time ,ptb.vc_debt_to_equity ,ptb.vc_con_price ,ptb.vc_con_price_remarks ,ptb.vc_debt_trigger_event ,ptb.vc_debt_trigger_time ,ptb.vc_pe_breach_contract ,ptb.vc_default_event ,ptb.vc_protective_terms_dk ,ptb.vc_pro_terms_remarks_dk ,ptb.vc_guarantee ,ptb.vc_guarantee_remarks ,ptb.vc_guarantee_mode ,ptb.vc_jurisdiction_law_dk ,ptb.vc_dispute_settlement_dk ,ptb.vc_other_spe_agreements ,ptb.vc_newoldsharesremark ,ptb.l_numberofshares ,ptb.l_price_of_shares ,ptb.l_amt_of_shares ,ptb.vc_firstsubscriberemark ,ptb.vc_yxgmandtsqremark ,ptb.vc_registrationrightsremark ,ptb.vc_righttoinformationremark ,ptb.vc_restrictivereremark ,ptb.vc_mirrorholding ,to_number(ptb.vc_mirrorholdingremark) ,ptb.l_investment_amount ,ptb.l_numberofsharesm ,ptb.l_numberofsharess ,ptb.vc_mti_trust ,vc_scheme_dates ,ptb.vc_fundbody1 ,ptb.vc_fairvalue1 ,ptb.vc_fairvaluecurrency1 ,ptb.vc_fundbody2 ,ptb.vc_fairvalue2 ,ptb.vc_fairvaluecurrency2 ,ptb.vc_fundbody3 ,ptb.vc_fairvalue3 ,ptb.vc_fairvaluecurrency3 ,ptb.vc_maximumchargelimit ,ptb.vc_maximumchargelimitcurrency ,ptb.vc_exclusiveperiod ,ptb.vc_legalservicefee ,ptb.vc_legalservicefeecurrency ,ptb.vc_financialservicefee ,ptb.vc_financialservicefeecurrency ,ptb.vc_lawfirm ,ptb.vc_accountingfirm ,ptb.vc_boolboardguoqiao ,ptb.vc_boolboardspa ,ptb.vc_loansigningtime ,ptb.l_spv_id ,ptb.vc_otheruser ,ptb.l_mimoney ,ptb.vc_bridgeacrosstime ,ptb.vc_bridgespvtime ,ptb.l_numberofsharescompany ,ptb.l_beforestockratio ,ptb.vc_equalcurrency ,ptb.l_equalmytradeamt ,ptb.vc_signedcurrency ,ptb.vc_boardseats ,ptb.vc_thereobservers ,ptb.vc_director ,ptb.vc_directorremark ,ptb.vc_islpac ,ptb.vc_lpactime ,ptb.vc_isboardbridge ,ptb.vc_isboardbridgetime ,ptb.vc_isfileboardbridge ,ptb.vc_isfileboardbridgetime ,ptb.vc_loanamountcurrency ,ptb.l_newstockratio ,ptb.vc_companyvaluationrcurrency ,ptb.vc_companyvaluationdcurrency ,ptb.vc_befcpyvaluationrcurrency ,ptb.vc_befcpyvaluationdcurrency ,ptb.vc_founder_equity_staging ,l_trade_stages ,ptb.l_loan_balance ,ptb.vc_actual_payment_time ,ptb.l_interest ,ptb.vc_reimbursement_turn_time ,ptb.vc_is_board_seats ,ptb.vc_is_observe_the_seats ,ptb.vc_is_shareholders_seats ,ptb.vc_is_shareholders_veto ,ptb.vc_is_director_veto ,vc_trading_schemenames ,ptb.l_thigh_registered_capital ,ptb.l_financing_amount ,sysdate ,current_timestamp(6) from P_TRADE_BASE_INFO ptb where ptb.l_trade_id = vc_trade_ids;

–公允价值添加 LOCAL_SQL := ‘select ffv1.l_fair_value ,ffv1.vc_fundid ,ffv1.vc_fundname ,ffv1.vc_tradingschemespv ,ffv1.vc_toumoney ,ffv1.vc_toucompanymoney ,ffv1.vc_tradeid ,ffv1.vc_chinafundid ,ffv1.vc_proforma ,ffv1.vc_gongyuncurrency from f_fair_value ffv1 where ffv1.vc_tradeid = ’ || vc_trade_ids; dbms_output.put_line(LOCAL_SQL); open dataCursor for LOCAL_SQL; loop fetch dataCursor into fairValue, fundId, fundname, tradingschemespv, toumoney, toucompanymoney, tradeid, chinafundid, proforma, gongyuncurrency; exit when dataCursor %notfound; insert into f_fair_value ffv (ffv.l_fair_value, ffv.vc_fundid, ffv.vc_fundname, ffv.vc_tradingschemespv, ffv.vc_toumoney, ffv.vc_toucompanymoney, ffv.vc_tradeid, ffv.vc_chinafundid, ffv.vc_proforma, ffv.vc_gongyuncurrency) select SEQ_FAIRVALUE.Nextval, fundId, fundname, tradingschemespv, toumoney, toucompanymoney, vc_new_trade_ids, chinafundid, proforma, gongyuncurrency from dual; end loop; close dataCursor;

–添加其他事项 LOCAL_SQL := ‘select tto1.vc_matter_name ,tto1.vc_detail_description from t_trade_other tto1 where tto1.l_trade_id =’ || vc_trade_ids; dbms_output.put_line(LOCAL_SQL); open dataCursor for LOCAL_SQL; loop fetch dataCursor into atterName, detailDescription; exit when dataCursor %notfound; insert into t_trade_other tto (tto.l_trade_other_id, tto.l_trade_id, tto.vc_matter_name, tto.vc_detail_description, tto.d_create_time, tto.d_update_time) select SEQ_TRADE_OTHER_ID.Nextval, vc_new_trade_ids, atterName, detailDescription, sysdate, sysdate from dual; end loop; close dataCursor;

end Name;