如何使用oracle过程将数据从多个表插入到一个表中

Question

我有6个不同的表，它们彼此之间有依赖关系。我正在尝试将所有这6个表中选定的列插入到单个表中。

我必须传递一个参数，以便根据表的输出从一个表中选择数据。如果没有匹配的记录，则应传递null。

我正在尝试使用6游标编写一个过程，并将一个游标的值传递给其他游标。

有没有人能帮我实现这一点。

下面是我的表的简化版本

Table Name                         table type
 ==========                         ==========
 CRM_CLAIM_INT_DETAILS_VIEW         Linked_Table
 INTRFC_MOTOR_NOTIFICATION_VIEW     Linked_Table
 Intrfc_Motor_Office_Master         Linked_Table
 INTRFC_MOTOR_RISK_VIEW             Linked_Table
 Intrfc_Policy_view                 Linked_Table
 MO_CLAIM_MASTER                    Table

Selected Fields
===============
CRM_ACC_DATE_TIME,CRM_GEN_REP_NAME,CRM_SYSDATE
policy_number
Parent_office_name
CHASSIS_NO,ENGINE_NO,MAKE,MODEL,REGISTRATION_NO
AGENT_NAME,Branch_Office Description,Insured_Address,Insured_Name,PRODUCT_CODE,RISK_START_DATE,RISK_START_TIME
claim_no,DATE_OF_NOTIFICATION,notified_by,VEHICLE_TYPE

Condition
=========
mo_claim_master.crm_sl_no = CRM_CLAIM_INT_DETAILS_VIEW.crm_sl_no
INTRFC_MOTOR_NOTIFICATION_VIEW.claim_no = mo_claim_master.claim_no
intrfc_policy_view.branch_office_code = Intrfc_Motor_Office_Master.office_code
INTRFC_MOTOR_RISK_VIEW.refernce_num = intrfc_policy_view.num_reference_num
intrf_notification_view.refernce_num = intrfc_policy_view.num_reference_num
claim_ref_no=:claim_ref_no

插入到CFT中

_

表格

我已经为此创建了一个过程，但我仍然无法获得空值。这是获得这个输出pl的正确方法吗？建议我。

CREATE OR REPLACE
 PROCEDURE TEST(
     para_claim_ref_no IN VARCHAR2 )
 AS
   CURSOR c1
   IS
     SELECT claim_no,
       DATE_OF_NOTIFICATION,
       VEHICLE_TYPE,
       notified_by,
       notif_crm_sl_no
     FROM mo_claim_master
     WHERE claim_ref_no=para_claim_ref_no;
   t1 c1%rowtype;
   claim_num_var       VARCHAR2(50);
   notif_crm_sl_no_var NUMBER;
   CURSOR c2
   IS
     SELECT policy_num,
       reference_num
     FROM INTRFC_MOTOR_NOTIFICATION_VIEW
     WHERE claim_no=claim_num_var;
   t2 c2%rowtype;
   num_reference_num_var NUMBER;
   CURSOR c3
   IS
     SELECT BRANCH_OFFICE_CODE,
       Branch_Office_Desc,
       Insured_Name,
       Insured_Address,
       RISK_START_DATE,
       RISK_START_TIME,
       PRODUCT_CODE,
       AGENT_NAME
     FROM intrfc_policy_view
     WHERE num_reference_number=num_reference_num_var;
   t3 c3%rowtype;
   branch_office_code_var VARCHAR2(30);
   CURSOR c4
   IS
     SELECT OFFICE_CODE
     FROM INTRFC_MOTOR_OFFICE_MASTER
     WHERE office_code = branch_office_code_var;
   t4 c4%rowtype;
   CURSOR c5
   IS
     SELECT MAKE,
       MODEL,
       ENGINE_NO,
       CHASSIS_NO,
       REGISTRATION_NO
     FROM INTRFC_MOTOR_RISK_VIEW
     WHERE reference_num=num_reference_num_var;
   t5 c5%rowtype;
   CURSOR c6
   IS
     SELECT CRM_ACC_DATE_TIME,
       CRM_GEN_REP_NAME
     FROM CRM_CLAIM_INT_DETAILS_VIEW
     WHERE crm_slno=notif_crm_sl_no_var;
   t6 c6%rowtype;
 BEGIN
   OPEN c1;
   FETCH c1 INTO t1;
   CLOSE c1;
   claim_num_var       := t1.claim_no;
   notif_crm_sl_no_var := t1.notif_crm_sl_no;
   OPEN c2;
   FETCH c2 INTO t2;
   CLOSE c2;
   num_reference_num_var := t2.reference_num;
   OPEN c3;
   FETCH c3 INTO t3;
   CLOSE c3;
   branch_office_code_var := t3.BRANCH_OFFICE_CODE;
   OPEN c4;
   FETCH c4 INTO t4;
   CLOSE c4;
   OPEN c5;
   FETCH c5 INTO t5;
   CLOSE c5;
   OPEN c6;
   FETCH c6 INTO t6;
   CLOSE c6;
   dbms_output.put_line(t1.claim_no);
   dbms_output.put_line(t1.DATE_OF_NOTIFICATION);
   dbms_output.put_line(t1.VEHICLE_TYPE);
   dbms_output.put_line(t1.notified_by);
   dbms_output.put_line(t2.policy_num);
   dbms_output.put_line(t3.Branch_Office_Desc);
   dbms_output.put_line(t3.Insured_Name);
   dbms_output.put_line(t3.Insured_Address);
   dbms_output.put_line(t3.RISK_START_DATE);
   dbms_output.put_line(t3.RISK_START_TIME);
   dbms_output.put_line(t3.PRODUCT_CODE);
   dbms_output.put_line(t3.AGENT_NAME);
   dbms_output.put_line(t4.office_code);
   dbms_output.put_line(t5.MAKE);
   dbms_output.put_line(t5.MODEL);
   dbms_output.put_line(t5.ENGINE_NO);
   dbms_output.put_line(t5.CHASSIS_NO);
   dbms_output.put_line(t5.REGISTRATION_NO);
   dbms_output.put_line(t6.CRM_ACC_DATE_TIME);
   dbms_output.put_line(t6.CRM_GEN_REP_NAME);
 END TEST;

Answer 1

没有人发布答案，所以我在这里添加一个。

当您讨论多个游标时，听起来好像您希望循环遍历每个游标中的每一行，并在执行过程中插入和更新。这大概是最糟糕的解决方案了。你想要一个

..。

Tom Kyte在这里解释了这一点：

慢慢地慢慢地..。

我不打算在这里编写完整的insert语句，因为您有大量的表和列，但基本上是基于SELECT创建一个INSERT语句：

INSERT INTO CFT_Table ( ... column names ... )
SELECT ( ... column names ... )
  FROM MO_CLAIM_MASTER
  LEFT JOIN Intrfc_Policy_view ON ( ... join condition ... )
  LEFT JOIN INTRFC_MOTOR_RISK_VIEW ON (... join condition ... )

我使用左连接是因为你说你想要空值。我假设是MO

_

申请

_

MASTER对要插入的每一行都有一行。