主页 / coding / 问题 / 79332898
Accepted
hud
Asked: 2025-01-06 19:34:59 +0800 CST

Oracle 中数据未通过存储过程更新

  • 772

我已经编写了一个基于的存储过程SAP_ID，如果该数据已经存在，我将更新表中的数据，SAP_ID如果不存在，则我INSERT更新它。

这是我的疑问：

PROCEDURE INSERT_PF_EXCEL_DATA_IPCOLO (
        p_political_state_name         IN NVARCHAR2,
        p_political_state_code         IN NVARCHAR2,
        p_rfcdate                      IN NVARCHAR2,
        p_rfs_date                     IN NVARCHAR2,
        p_rfe1_date                    IN NVARCHAR2,
        p_site_drop_date               IN NVARCHAR2,
        p_ip_colo_siteid               IN NVARCHAR2,
        p_mw_installed                 IN NVARCHAR2,
        p_dg_nondg                     IN NVARCHAR2,
        p_eb_noneb                     IN NVARCHAR2,
        p_id_od_countchange            IN NVARCHAR2,
        p_id_od_changeddate            IN NVARCHAR2,
        p_rrh_countchange              IN NVARCHAR2,
        p_rrh_changeddate              IN NVARCHAR2,
        p_tenancy_countchange          IN NVARCHAR2,
        p_tenancy_changeddate          IN NVARCHAR2,
        p_sap_id                       IN NVARCHAR2,
        p_created_by                   IN NVARCHAR2,
        p_rfs_date_5g                  IN NVARCHAR2,
        p_drop_date_5g                 IN NVARCHAR2,
        p_olt_count                    IN NUMBER,
        p_olt_change_date              IN NVARCHAR2,
        p_diesel_downtime_minutes      IN NVARCHAR2,
        p_overall_infra_outage_minutes IN NVARCHAR2,
        p_diesel_downtime_min_my       IN NVARCHAR2,
        p_overall_infra_outage_min_my  IN NVARCHAR2,
        p_is5gpresent                  IN NVARCHAR2,
        p_is_site_dropped              IN NVARCHAR2,
        p_status                       OUT NVARCHAR2,
        p_message                      OUT NVARCHAR2
    ) AS
        t_cnt           NUMBER;
        vcnt_sapid      NUMBER;
        v_ipcolobilling VARCHAR2(25) := 'IpColoBilling';
    BEGIN

  --  DELETE FROM APP_LOG WHERE APP_NAME = 'IpColoBilling' AND TRUNC(LOG_TIME) = TRUNC(SYSDATE - 2);

        DELETE FROM app_log
        WHERE
                app_name = v_ipcolobilling
            AND ( log_time ) >= trunc(sysdate - 2)
            AND log_time < trunc(sysdate - 1);

        COMMIT;
        
        SELECT count(sap_id) INTO vcnt_sapid FROM temp_ipcolo_billing_mst
        WHERE
            sap_id = p_sap_id;-- AND CREATED_DATE = SYSDATE;


        IF vcnt_sapid > 0 THEN
            UPDATE temp_ipcolo_billing_mst
SET
    political_state_name = p_political_state_name,
    political_state_code = p_political_state_code,
    rfcdate = TO_DATE(p_rfcdate, 'DD-MM-YYYY'), -- Truncates to the day
    rfs_date = TO_DATE(p_rfs_date, 'DD-MM-YYYY'),-- TRUNC(), -- Removed invalid format 'dd-MM-yyyy'
    rfe1_date = TO_DATE(p_rfe1_date, 'DD-MM-YYYY'), -- Removed invalid format 'dd-MM-yyyy'
   site_drop_date = TO_DATE(p_site_drop_date,'DD-MM-YYYY'), -- Removed invalid format 'dd-MM-yyyy'
    ip_colo_siteid = p_ip_colo_siteid,
    mw_installed = p_mw_installed,
    dg_nondg = p_dg_nondg,
    eb_noneb = p_eb_noneb,
    id_od_countchange = p_id_od_countchange,
    id_od_changeddate = TO_DATE(p_id_od_changeddate,'DD-MM-YYYY'), -- Removed invalid format 'dd-MM-yyyy'
    rrh_countchange = p_rrh_countchange,
    rrh_changeddate = TO_DATE(p_rrh_changeddate,'DD-MM-YYYY'), -- Removed invalid format 'dd-MM-yyyy'
    tenancy_countchange = p_tenancy_countchange,
    tenancy_changeddate = TO_DATE(p_tenancy_changeddate,'DD-MM-YYYY'), -- Removed invalid format 'dd-MM-yyyy'
    created_by = p_created_by,
    rfs_date_5g = TO_DATE(p_rfs_date_5g,'DD-MM-YYYY'), -- Removed invalid format 'dd-MM-yyyy'
    drop_date_5g = TO_DATE(p_drop_date_5g,'DD-MM-YYYY'), -- Removed invalid format 'dd-MM-yyyy'
    olt_count = p_olt_count,
    olt_change_date = TO_DATE(p_olt_change_date,'DD-MM-YYYY'), -- Removed invalid format 'dd-MM-yyyy'
    diesel_downtime_minutes = p_diesel_downtime_minutes,
    overall_infra_outage_minutes = p_overall_infra_outage_minutes,
    diesel_downtime_min_my = p_diesel_downtime_min_my,
    overall_infra_outage_min_my = p_overall_infra_outage_min_my,
    is5gpresent = p_is5gpresent,
    is_site_dropped = p_is_site_dropped,
    LAST_UPDATED_DATE = SYSDATE
    
WHERE

    sap_id = p_sap_id;
                
                p_message := 'SUCCESS';

            COMMIT;
        ELSE
            INSERT INTO temp_ipcolo_billing_mst (--TEMP_IPCOLO_BILLING_MST

                id,
                political_state_name,
                political_state_code,
                rfcdate,
                rfs_date,
                rfe1_date,
                site_drop_date,
                ip_colo_siteid,
                mw_installed,
                dg_nondg,
                eb_noneb,
                id_od_countchange,
                id_od_changeddate,
                rrh_countchange,
                rrh_changeddate,
                tenancy_countchange,
                tenancy_changeddate,
                sap_id,
                created_by,
                rfs_date_5g,
                drop_date_5g,
                olt_count,
                olt_change_date,
                diesel_downtime_minutes,
                overall_infra_outage_minutes,
                diesel_downtime_min_my,
                overall_infra_outage_min_my,
                is5gpresent,
                is_site_dropped
            ) VALUES (
                incr_id_ipcolo_temp.NEXTVAL,
                p_political_state_name,
                p_political_state_code,
                to_date(p_rfcdate, 'dd-MM-yyyy'),
                to_date(p_rfs_date, 'dd-MM-yyyy'),
                to_date(p_rfe1_date, 'dd-MM-yyyy'),
                to_date(p_site_drop_date, 'dd-MM-yyyy'),
                p_ip_colo_siteid,
                p_mw_installed,
                p_dg_nondg,
                p_eb_noneb,
                p_id_od_countchange,
                to_date(p_id_od_changeddate, 'dd-MM-yyyy'),
                p_rrh_countchange,
                to_date(p_rrh_changeddate, 'dd-MM-yyyy'),
                p_tenancy_countchange,
                to_date(p_tenancy_changeddate, 'dd-MM-yyyy'),
                p_sap_id,
                p_created_by,
                to_date(p_rfs_date_5g, 'dd-MM-yyyy'),
                to_date(p_drop_date_5g, 'dd-MM-yyyy'),
                p_olt_count,
                to_date(p_olt_change_date, 'dd-MM-yyyy'),
                p_diesel_downtime_minutes,
                p_overall_infra_outage_minutes,
                to_date(p_diesel_downtime_min_my, 'dd-MM-yyyy'), 
                to_date(p_overall_infra_outage_min_my, 'dd-MM-yyyy'),
                p_is5gpresent,
                p_is_site_dropped
            );

            p_message := 'SUCCESS';
        END IF;

    EXCEPTION
        WHEN OTHERS THEN
            p_status := sqlerrm;
            p_message := 'ERROR';
    END INSERT_PF_EXCEL_DATA_IPCOLO;

我哪里做错了？

oracle-database

1 个回答

  1. Best Answer

    您从未解释过您所说的您编写的程序不起作用是什么意思。您是如何检查的？您运行过它吗？使用哪些数据？您检查过OUT参数值吗？

    无论如何：不要检查行是否已经存在（或不存在）并编写单独的update和insert语句，merge而是考虑使用；它也被称为“upsert”，因为它结合了更新和插入，并且是为您描述的问题而设计的。

    这是一个简化的例子（我不想创建所有这些参数或表列），但是 - 它应该说明我想要表达的意思。

    样表及序列：

    SQL> create table temp_ipcolo_billing_mst
  2  (id                   number,
  3   political_state_name varchar2(10),
  4   political_state_code varchar2(10),
  5   rfcdate              date);

Table created.

SQL> create sequence incr_id_ipcolo_temp;

Sequence created.

    程序；它删除了代码的日志记录P_SAP_ID部分 - 如果需要，请将其重新包含。基本上，它通过检查目标表中是否存在行来执行与您的过程相同的工作：

    • 如果存在，merge将更新该行
    • 如果不是，它会插入一个新行

    它的代码如下：

    SQL> create or replace procedure insert_pf_excel_data_ipcolo
  2    (p_sap_id               in number,
  3     p_political_state_name in nvarchar2,
  4     p_political_state_code in nvarchar2,
  5     p_rfcdate              in nvarchar2,
  6     p_message             out nvarchar2)
  7  as
  8  begin
  9      merge into temp_ipcolo_billing_mst a
 10      using (select p_sap_id               as sap_id,
 11                    p_political_state_name as political_state_name,
 12                    p_political_state_code as political_state_code,
 13                    to_date(p_rfcdate, 'dd-mm-yyyy')   as rfcdate
 14            from dual
 15           ) b
 16        on (b.sap_id = a.id)
 17      when matched then update set
 18        a.political_state_name = b.political_state_name,
 19        a.political_state_code = b.political_state_code,
 20        a.rfcdate   = b.rfcdate
 21      when not matched then insert
 22        (id,
 23         political_state_name,
 24         political_state_code,
 25         rfcdate)
 26        values
 27        (incr_id_ipcolo_temp.nextval,
 28         b.political_state_name,
 29         b.political_state_code,
 30         b.rfcdate
 31        );
 32
 33    p_message := 'SUCCESS';
 34  exception
 35    when others then
 36      p_message := 'ERROR: ' || sqlerrm;
 37  end;
 38  /

Procedure created.

    我们来测试一下：第一个例子运行正常并插入一行：

    SQL> set serveroutput on
SQL> declare
  2    l_msg varchar2(200);
  3  begin
  4    insert_pf_excel_data_ipcolo
  5      (p_sap_id               => 1,
  6       p_political_state_name => 'Pol. name',
  7       p_political_state_code => 'PNC',
  8       p_rfcdate              => '06-01-2025',
  9       p_message              => l_msg);
 10    dbms_output.put_line(l_msg);
 11  end;
 12  /
SUCCESS

PL/SQL procedure successfully completed.

SQL> select * from temp_ipcolo_billing_mst;

        ID POLITICAL_ POLITICAL_ RFCDATE
---------- ---------- ---------- ---------
         1 Pol. name  PNC        06-JAN-25

    此示例由于日期值无效而失败（并返回错误消息）：

    SQL> declare
  2    l_msg varchar2(200);
  3  begin
  4    insert_pf_excel_data_ipcolo
  5      (p_sap_id               => 1,
  6       p_political_state_name => 'Pol. name',
  7       p_political_state_code => 'PNC',
  8       p_rfcdate              => '99-01-2025',
  9       p_message              => l_msg);
 10    dbms_output.put_line(l_msg);
 11  end;
 12  /
ERROR: ORA-01847: day of month must be between 1 and last day of month

PL/SQL procedure successfully completed.

    最后的示例更新现有行：

    SQL> declare
  2    l_msg varchar2(200);
  3  begin
  4    insert_pf_excel_data_ipcolo
  5      (p_sap_id               => 1,
  6       p_political_state_name => 'Pol. nameA',
  7       p_political_state_code => 'PNC_A',
  8       p_rfcdate              => '25-08-2025',
  9       p_message              => l_msg);
 10    dbms_output.put_line(l_msg);
 11  end;
 12  /
SUCCESS

PL/SQL procedure successfully completed.

SQL> select * from temp_ipcolo_billing_mst;

        ID POLITICAL_ POLITICAL_ RFCDATE
---------- ---------- ---------- ---------
         1 Pol. nameA PNC_A      25-AUG-25

SQL>

    所以，是的 - 它有效。仔细看看它 - 如果你发现它有用 - 重写你的代码，以便它与我的示例程序执行相同的操作。

    • 2

相关问题