Oracle使用一个插入查询在多个表中插入多行

wf82jlnq 于 2023-02-18 发布在 Oracle

关注(0)|答案(1)|浏览(137)

我写了一个查询，我想在2个表中插入多行。但它只在第一个表中插入了第一行。下面是我的尝试：
我的手术：

PROCEDURE NEW_USER_PRIVELEGE_HIERCHY
(
    IN_CREATED_BY               IN VARCHAR2,
    IN_MODIFIED_BY                         IN VARCHAR2,
    IN_USERNAME                            IN VARCHAR2
)
AS
    L_USER_ID INTEGER;
    L_PRIVILEGE_ID INTEGER;
    L_HIERCHY_ID INTEGER;
    N_USER_ID  INTEGER := hibernate_sequence.NEXTVAL;
BEGIN
    SELECT case 
         when exists( SELECT USER_ID FROM T_MT_USER WHERE USERNAME = IN_USERNAME )
         then 
              (SELECT USER_ID FROM T_MT_USER WHERE USERNAME = IN_USERNAME)
         else 0
       end  into L_USER_ID
      FROM dual;
      
    IF (L_USER_ID = 0) THEN
        INSERT INTO T_MT_USER
        (USER_ID, ACCOUNT_EXPIRED_FLG, ACCOUNT_LOCKED_FLG, created_by, CREATED_TS, ACTIVE_FLG, end_effective_dt, modified_by, MODIFIED_TS, "password", password_expired, start_effective_dt, username) 
        VALUES
        (N_USER_ID, 0, 0, IN_CREATED_BY, SYSTIMESTAMP, 1, NULL, IN_MODIFIED_BY, SYSTIMESTAMP, '$2a$10$3hoAeeVJ0AG.CQS2xX8rT.wUlt1rjoiYsHW0YflZEiQ5QYzG4HpYi', 0, SYSTIMESTAMP, IN_USERNAME);    -- for Synergy1!
        -- start inserting in privilege talbe 
        insert into T_MT_USER_PRIVILEGE (privilege_id,user_id, created_by, CREATED_TS,  modified_by,MODIFIED_TS ) values (8001,N_USER_ID,  IN_CREATED_BY, sysdate, IN_MODIFIED_BY, sysdate);
        insert into T_MT_USER_PRIVILEGE (privilege_id,user_id, created_by, CREATED_TS,  modified_by,MODIFIED_TS ) values (8003,N_USER_ID,  IN_CREATED_BY, sysdate, IN_MODIFIED_BY, sysdate);
        insert into T_MT_USER_PRIVILEGE (privilege_id,user_id, created_by, CREATED_TS,  modified_by,MODIFIED_TS ) values (8004,N_USER_ID,  IN_CREATED_BY, sysdate, IN_MODIFIED_BY, sysdate);
        insert into T_MT_USER_PRIVILEGE (privilege_id,user_id, created_by, CREATED_TS,  modified_by,MODIFIED_TS ) values (8007,N_USER_ID,  IN_CREATED_BY, sysdate, IN_MODIFIED_BY, sysdate);
        insert into T_MT_USER_PRIVILEGE (privilege_id,user_id, created_by, CREATED_TS,  modified_by,MODIFIED_TS ) values (8010,N_USER_ID,  IN_CREATED_BY, sysdate, IN_MODIFIED_BY, sysdate);
        -- start inserting in hierchy table
        insert into T_MT_USER_HIER_NODE (hier_node_id, user_id, created_by, CREATED_TS, modified_by, MODIFIED_TS ) values (101, N_USER_ID, IN_CREATED_BY, sysdate, IN_MODIFIED_BY, sysdate);
        insert into T_MT_USER_HIER_NODE (hier_node_id, user_id, created_by, CREATED_TS, modified_by, MODIFIED_TS ) values (102, N_USER_ID, IN_CREATED_BY, sysdate, IN_MODIFIED_BY, sysdate);    
        insert into T_MT_USER_HIER_NODE (hier_node_id, user_id, created_by, CREATED_TS, modified_by, MODIFIED_TS ) values (103, N_USER_ID, IN_CREATED_BY, sysdate, IN_MODIFIED_BY, sysdate);    
        insert into T_MT_USER_HIER_NODE (hier_node_id, user_id, created_by, CREATED_TS, modified_by, MODIFIED_TS ) values (5626, N_USER_ID, IN_CREATED_BY, sysdate, IN_MODIFIED_BY, sysdate);    
        insert into T_MT_USER_HIER_NODE (hier_node_id, user_id, created_by, CREATED_TS, modified_by, MODIFIED_TS ) values (6548, N_USER_ID, IN_CREATED_BY, sysdate, IN_MODIFIED_BY, sysdate);        
    END IF;
EXCEPTION
    WHEN OTHERS THEN
        log_api.fatal(log_ctx,'Exception in NEW_USER_PRIVELEGE_HIERCHY');
        RAISE;
END NEW_USER_PRIVELEGE_HIERCHY;

调用我的过程：

execute USER_DEMO.NEW_USER('sbappi', 'sbappi', 'sumon8');

我也试过insert all，但是没有运气。

oracle

来源：https://stackoverflow.com/questions/36694052/oracle-insert-multiple-rows-in-multiple-tables-with-one-insert-query

1条答案

按热度按时间

5vf7fwbs1#

你可以尝试一件事，如果你试图测试从sql开发人员，你可以把dbms日志记录器，以获得插入计数和它的失败。希望这个片段有帮助。

PROCEDURE NEW_USER_PRIVELEGE_HIERCHY(
    IN_CREATED_BY  IN VARCHAR2,
    IN_MODIFIED_BY IN VARCHAR2,
    IN_USERNAME    IN VARCHAR2 )
AS
  L_USER_ID      INTEGER;
  L_PRIVILEGE_ID INTEGER;
  L_HIERCHY_ID   INTEGER;
  N_USER_ID      INTEGER := hibernate_sequence.NEXTVAL;
  lv_cnt PLS_INTEGER;
BEGIN
  SELECT COUNT(1)
  INTO L_USER_ID
  FROM T_MT_USER
  WHERE UPPER(USERNAME) = UPPER(IN_USERNAME);
  IF (L_USER_ID         = 0) THEN
    INSERT
    INTO T_MT_USER
      (
        USER_ID,
        ACCOUNT_EXPIRED_FLG,
        ACCOUNT_LOCKED_FLG,
        created_by,
        CREATED_TS,
        ACTIVE_FLG,
        end_effective_dt,
        modified_by,
        MODIFIED_TS,
        "password",
        password_expired,
        start_effective_dt,
        username
      )
      VALUES
      (
        N_USER_ID,
        0,
        0,
        IN_CREATED_BY,
        SYSTIMESTAMP,
        1,
        NULL,
        IN_MODIFIED_BY,
        SYSTIMESTAMP,
        '$2a$10$3hoAeeVJ0AG.CQS2xX8rT.wUlt1rjoiYsHW0YflZEiQ5QYzG4HpYi',
        0,
        SYSTIMESTAMP,
        IN_USERNAME
      ); -- for Synergy1!
    lv_cnt:=SQL%ROWCOUNT;
    dbms_output.put_line(lv_cnt||' T_MT_USER ');
    -- start inserting in privilege talbe
    INSERT
    INTO T_MT_USER_PRIVILEGE
      (
        privilege_id,
        user_id,
        created_by,
        CREATED_TS,
        modified_by,
        MODIFIED_TS
      )
      VALUES
      (
        8001,
        N_USER_ID,
        IN_CREATED_BY,
        sysdate,
        IN_MODIFIED_BY,
        sysdate
      );
    lv_cnt:=lv_cnt+SQL%ROWCOUNT;
    dbms_output.put_line(lv_cnt||' T_MT_USER_PRIVILEGE ');
    INSERT
    INTO T_MT_USER_PRIVILEGE
      (
        privilege_id,
        user_id,
        created_by,
        CREATED_TS,
        modified_by,
        MODIFIED_TS
      )
      VALUES
      (
        8003,
        N_USER_ID,
        IN_CREATED_BY,
        sysdate,
        IN_MODIFIED_BY,
        sysdate
      );
    lv_cnt:=lv_cnt+SQL%ROWCOUNT;
    dbms_output.put_line(lv_cnt||' T_MT_USER_PRIVILEGE ');
    INSERT
    INTO T_MT_USER_PRIVILEGE
      (
        privilege_id,
        user_id,
        created_by,
        CREATED_TS,
        modified_by,
        MODIFIED_TS
      )
      VALUES
      (
        8004,
        N_USER_ID,
        IN_CREATED_BY,
        sysdate,
        IN_MODIFIED_BY,
        sysdate
      );
    lv_cnt:=lv_cnt+SQL%ROWCOUNT;
    dbms_output.put_line(lv_cnt||' T_MT_USER_PRIVILEGE ');
    INSERT
    INTO T_MT_USER_PRIVILEGE
      (
        privilege_id,
        user_id,
        created_by,
        CREATED_TS,
        modified_by,
        MODIFIED_TS
      )
      VALUES
      (
        8007,
        N_USER_ID,
        IN_CREATED_BY,
        sysdate,
        IN_MODIFIED_BY,
        sysdate
      );
    lv_cnt:=lv_cnt+SQL%ROWCOUNT;
    dbms_output.put_line(lv_cnt||' T_MT_USER_PRIVILEGE ');
    INSERT
    INTO T_MT_USER_PRIVILEGE
      (
        privilege_id,
        user_id,
        created_by,
        CREATED_TS,
        modified_by,
        MODIFIED_TS
      )
      VALUES
      (
        8010,
        N_USER_ID,
        IN_CREATED_BY,
        sysdate,
        IN_MODIFIED_BY,
        sysdate
      );
    lv_cnt:=lv_cnt+SQL%ROWCOUNT;
    dbms_output.put_line(lv_cnt||' T_MT_USER_PRIVILEGE ');
    -- start inserting in hierchy table
    INSERT
    INTO T_MT_USER_HIER_NODE
      (
        hier_node_id,
        user_id,
        created_by,
        CREATED_TS,
        modified_by,
        MODIFIED_TS
      )
      VALUES
      (
        101,
        N_USER_ID,
        IN_CREATED_BY,
        sysdate,
        IN_MODIFIED_BY,
        sysdate
      );
    lv_cnt:=lv_cnt+SQL%ROWCOUNT;
    dbms_output.put_line(lv_cnt||' T_MT_USER_HIER_NODE ');
    INSERT
    INTO T_MT_USER_HIER_NODE
      (
        hier_node_id,
        user_id,
        created_by,
        CREATED_TS,
        modified_by,
        MODIFIED_TS
      )
      VALUES
      (
        102,
        N_USER_ID,
        IN_CREATED_BY,
        sysdate,
        IN_MODIFIED_BY,
        sysdate
      );
    lv_cnt:=lv_cnt+SQL%ROWCOUNT;
    dbms_output.put_line(lv_cnt||' T_MT_USER_HIER_NODE ');
    INSERT
    INTO T_MT_USER_HIER_NODE
      (
        hier_node_id,
        user_id,
        created_by,
        CREATED_TS,
        modified_by,
        MODIFIED_TS
      )
      VALUES
      (
        103,
        N_USER_ID,
        IN_CREATED_BY,
        sysdate,
        IN_MODIFIED_BY,
        sysdate
      );
    lv_cnt:=lv_cnt+SQL%ROWCOUNT;
    dbms_output.put_line(lv_cnt||' T_MT_USER_HIER_NODE ');
    INSERT
    INTO T_MT_USER_HIER_NODE
      (
        hier_node_id,
        user_id,
        created_by,
        CREATED_TS,
        modified_by,
        MODIFIED_TS
      )
      VALUES
      (
        5626,
        N_USER_ID,
        IN_CREATED_BY,
        sysdate,
        IN_MODIFIED_BY,
        sysdate
      );
    lv_cnt:=lv_cnt+SQL%ROWCOUNT;
    dbms_output.put_line(lv_cnt||' T_MT_USER_HIER_NODE ');
    INSERT
    INTO T_MT_USER_HIER_NODE
      (
        hier_node_id,
        user_id,
        created_by,
        CREATED_TS,
        modified_by,
        MODIFIED_TS
      )
      VALUES
      (
        6548,
        N_USER_ID,
        IN_CREATED_BY,
        sysdate,
        IN_MODIFIED_BY,
        sysdate
      );
    lv_cnt:=lv_cnt+SQL%ROWCOUNT;
    dbms_output.put_line(lv_cnt||' T_MT_USER_HIER_NODE ');
  END IF;
EXCEPTION
WHEN OTHERS THEN
  log_api.fatal(log_ctx,'Exception in NEW_USER_PRIVELEGE_HIERCHY');
  RAISE;
END NEW_USER_PRIVELEGE_HIERCHY;

赞(0）回复(0）举报 2023-02-18

我来回答

Oracle使用一个插入查询在多个表中插入多行

1条答案

相关问题

热门标签

最新问答