獲取ORA-01422:呼叫程序時精確提取回傳的行數超過請求的行數-有解無憂

CREATE TABLE cmb_staging (
    e_id               NUMBER(10),
    e_name             VARCHAR2(30),
    e_loc              VARCHAR2(30),
    validation_status  VARCHAR2(30),
    validation_result  VARCHAR2(30)
);

insert into cmb_staging values(1,'A','AA',null,null);
insert into cmb_staging values(1,'B','BB',null,null);
insert into cmb_staging values(2,'C','CC',null,null);
insert into cmb_staging values(2,'D','DD',null,null);
insert into cmb_staging values(3,'A','AA',null,null);

CREATE TABLE cmb_target (
    e_id    NUMBER(10),
    e_name  VARCHAR2(30),
    e_loc   VARCHAR2(30)
);

CREATE TABLE cmb_reject (
    e_id               NUMBER(10),
    e_name             VARCHAR2(30),
    e_loc              VARCHAR2(30),
    validation_status  VARCHAR2(30),
    validation_result  VARCHAR2(30)
);

CREATE TABLE SUMMARY_TAB
   (    TOT_RECORDS NUMBER(10,0), 
    SUCCESS_RECORDS NUMBER(10,0), 
    FAILED_RECORDS NUMBER(10,0), 
    PROCESS_STATUS VARCHAR2(30)
   );

程式：

create or replace procedure sp_dup_rec(ov_err_msg OUT varchar2)
    is
      lv_succ_rec number(30);
      lv_fail_rec number(30);
      lv_count number(30);
    begin
      lv_succ_rec := 0;
      lv_fail_rec := 0;

      UPDATE cmb_staging
      SET   validation_status = 'Fail',
            validation_result    = CASE
                                WHEN e_id IS NULL
                                THEN 'Id is not present'
                                ELSE 'Id is longer than expected'
                                END
      WHERE e_id is null
      OR    LENGTH(e_id) > 4;

--If there are duplicates id then it should go into cmb_reject table
select e_id into lv_count from cmb_staging;
if lv_count < 1 then
      MERGE INTO cmb_target t
        USING (SELECT e_id,
             e_name,
             e_loc
      FROM   cmb_staging
      WHERE  validation_status IS NULL) S
        ON (t.e_id = S.e_id)

            WHEN MATCHED THEN UPDATE SET 
                t.e_name = s.e_name,
                t.e_loc = s.e_loc


            WHEN NOT MATCHED THEN INSERT (t.e_id,t.e_name,t.e_loc)
                VALUES (s.e_id,s.e_name,s.e_loc);

      lv_succ_rec := SQL%ROWCOUNT;
else
      insert into cmb_reject
      select s.*
      from   cmb_staging s
      WHERE  validation_status = 'Fail';

      lv_fail_rec := SQL%ROWCOUNT;
end if;

      dbms_output.put_line('Inserting into Summary table');
      insert into summary_tab(
        tot_records,
        success_records,
        failed_records
      ) values (
        lv_succ_rec   lv_fail_rec,
        lv_succ_rec,
        lv_fail_rec
      );

      COMMIT;
      ov_err_msg := 'Procedure completed succesfully';
    EXCEPTION
      WHEN OTHERS THEN
        ov_err_msg := 'Procedure end up with errors'|| sqlerrm;
        ROLLBACK;
    END sp_dup_rec;

呼叫程序：

set serveroutput on;
declare
    v_err_msg varchar2(100);
begin
    sp_dup_rec(v_err_msg);
    dbms_output.put_line(v_err_msg);
end;

嗨，團隊，我在呼叫程序時收到 ora-01422 錯誤。基本上，我想在 cmb_reject 選項卡中插入重復的記錄，因為合并陳述句將失敗，如果我只使用合并，我將收到 ora - 30296 錯誤。所以，我寫了 if 條件，其中它將獲取計數，如果計數更多，則將插入 cmb_reject 選項卡

uj5u.com熱心網友回復：

--If there are duplicates id then it should go into cmb_reject table
select e_id into lv_count from cmb_staging;

當您嘗試將所有 5 個e_id值插入單個變數時，這會引發TOO_MANY_ROWS例外并引發例外。（除了它不識別重復的事實。）

您可以在原始檔案中進行所有處理UPDATE（在這種情況下，將其轉換為MERGE陳述句），而不是嘗試將重復項識別為流程的一個單獨部分：

create or replace procedure sp_dup_rec(ov_err_msg OUT varchar2)
is
  lv_succ_rec number(30);
  lv_fail_rec number(30);
  lv_count number(30);
begin
  MERGE INTO cmb_staging dst
  USING (
    SELECT ROWID AS rid,
           CASE
           WHEN e_id IS NULL
           THEN 'Id is not present'
           WHEN LENGTH(e_id) > 4
           THEN 'Id is longer than expected'
           WHEN num_e_id > 1
           THEN 'Duplicate IDs'
           END AS failure_reason
    FROM   (
      SELECT e_id,
             COUNT(*) OVER (PARTITION BY e_id) AS num_e_id
      FROM   cmb_staging
    )
    WHERE  e_id IS NULL
    OR     LENGTH(e_id) > 4
    OR     num_e_id > 1
  ) src
  ON (src.rid = dst.ROWID)
  WHEN MATCHED THEN
    UPDATE
    SET validation_status = 'Fail',
        validation_result = failure_reason;

  MERGE INTO cmb_target t
  USING (
    SELECT e_id,
           e_name,
           e_loc
    FROM   cmb_staging
    WHERE  validation_status IS NULL
  ) S
  ON (t.e_id = S.e_id)
  WHEN MATCHED THEN
    UPDATE
    SET t.e_name = s.e_name,
        t.e_loc = s.e_loc
  WHEN NOT MATCHED THEN
    INSERT (t.e_id,t.e_name,t.e_loc)
    VALUES (s.e_id,s.e_name,s.e_loc);

  lv_succ_rec := SQL%ROWCOUNT;

  insert into cmb_reject
  select s.*
  from   cmb_staging s
  WHERE  validation_status = 'Fail';

  lv_fail_rec := SQL%ROWCOUNT;

  dbms_output.put_line('Inserting into Summary table');
  insert into summary_tab(
    tot_records,
    success_records,
    failed_records
  ) values (
    lv_succ_rec   lv_fail_rec,
    lv_succ_rec,
    lv_fail_rec
  );

  COMMIT;
  ov_err_msg := 'Procedure completed succesfully';
EXCEPTION
  WHEN OTHERS THEN
    ov_err_msg := 'Procedure end up with errors'|| sqlerrm;
    ROLLBACK;
END sp_dup_rec;
/

db<>在這里擺弄

uj5u.com熱心網友回復：

錯誤堆疊應該告訴您拋出錯誤的行。我的猜測是這條線是

select e_id 
  into lv_count 
  from cmb_staging;

因為那個查詢沒有意義。如果中有多行cmb_staging，查詢將拋出 ORA-01422 錯誤，因為您不能將多行放入標量變數中。您至少希望您的查詢執行count. 并且很可能帶有某種謂詞。就像是

select count(*)
  into lv_count 
  from cmb_staging;

會避免錯誤。但是，鑒于您說“它應該進入 cmb_reject 表”的評論，這可能沒有意義，這意味著有一個單一的物件。lv_count > 0每當有任何行時都會導致此更改，cmb_staging并且您似乎不太可能希望始終拒絕行。

我粗略的猜測是，當您說“重復”時，您的真正意思是“具有相同 e_id 的兩行”。如果這是對的

select e_id, count(*)
  from cmb_staging
 group by e_id
having count(*) > 1

將顯示重復e_id值。那么你可以

  insert into cmb_reject
  select s.*
  from   cmb_staging s
  where  e_id in (select s2.e_id
                    from cmb_staging s2
                   group by s2.e_id
                  having count(*) > 1);

如果您并不真正關心性能（我根據您提出的問題假設您只是在學習 PL/SQL 并且您沒有嘗試管理多 TB 資料倉庫負載）并且只是想要回圈處理記錄

for stg in (select s.*,
                   count(*) over (partition by s.e_id) cnt
              from cmb_staging s)
loop
  if( stg.cnt > 1 )
  then
    insert into cmb_reject( e_id, e_name, e_loc )
      values( stg.e_id, stg.e_name, stg.e_loc );
    l_fail_cnt := l_fail_cnt   1;
  else 
    merge into cmb_target tgt
      using( select stg.e_id e_id,
                    stg.e_name e_name,
                    stg.e_loc e_loc
               from dual ) src
         on( src.e_id = tgt.e_id )
       when not matched 
       then 
         insert( e_id, e_name, e_loc )
           values( src.e_id, src.e_name, src.e_loc )
       when matched
       then
         update set e_name = src.e_name,
                    e_loc  = src.e_loc;
      
    l_success_cnt := l_success_cnt   1;
  end if;
end loop;

uj5u.com熱心網友回復：

這是個問題：

--If there are duplicates id then it should go into cmb_reject table
select e_id into lv_count from cmb_staging;

由于沒有WHERE子句會限制回傳的行數，如果cmb_staging包含 2（或更多）行，查詢將失敗，因為您不能將那么多行放入標量lv_count變數中。

看起來你真的想說

select count(*) into lv_count from cmb_staging;

正如下一行所說

if lv_count < 1 then

轉載請註明出處，本文鏈接：https://www.uj5u.com/net/356373.html

標籤：甲骨文 plsql

上一篇：用管道分隔和逗號分隔將字串轉換為表

下一篇：我不知道為什么pl/sql不使用專案的值，sameone可以幫我嗎？