遍历字符串时如何插入临时表-Oracle-PL/SQL [英] How to insert into temp table when looping through a string - Oracle - PL/SQL

问题描述

CREATE GLOBAL TEMPORARY TABLE tt_temptable(
    RowNums   NUMBER(3,0),
    procNums  NUMBER(18,0)
) ON COMMIT PRESERVE ROWS;

  inputString  VARCHAR2 ;

  inputString := '12,13,14,15'


SELECT   REGEXP_SUBSTR (inputString,'[^,]+',1,LEVEL) ProcNums
FROM dual CONNECT BY  REGEXP_SUBSTR (inputString,'[^,]+',1,LEVEL) IS NOT NULL;

  INSERT INTO tt_temptable( 
    SELECT identity(3) RowNums,procNums
      FROM 

);

想在临时表中插入12，13，14，15和3个长度的标识，因此在临时表中总共有4行

Want to insert 12 , 13, 14 , 15 and identity of 3 length in the temptable so total 4 rows in temptable

推荐答案

如果使用Oracle 12c，则可以在表定义中通过GENERATED ALWAYS AS IDENTITY定义IDENTITY列，并遵循以下方法:

If you use Oracle 12c, then you may define an IDENTITY column through GENERATED ALWAYS AS IDENTITY in your table definition and follow the way below :

SQL> CREATE GLOBAL TEMPORARY TABLE tt_temptable(
  2        RowNums NUMBER(3,0) GENERATED ALWAYS AS IDENTITY,
  3        procNums  NUMBER(18,0)
  4    ) ON COMMIT PRESERVE ROWS;

Table created

SQL> 
SQL> DECLARE
  2    inputString  VARCHAR2(50) := '12,13,14,15';
  3  BEGIN
  4      INSERT INTO tt_temptable(procNums)
  5         SELECT REGEXP_SUBSTR (inputString,'[^,]+',1,LEVEL) ProcNums
  6           FROM dual
  7        CONNECT BY  REGEXP_SUBSTR (inputString,'[^,]+',1,LEVEL) IS NOT NULL;
  8  END;
  9  /

PL/SQL procedure successfully completed

SQL> SELECT * FROM tt_temptable;

ROWNUMS            PROCNUMS
------- -------------------
      1                  12
      2                  13
      3                  14
      4                  15

要重置IDENTITY列(RowNums)，请使用:

To reset the IDENTITY column (RowNums), use :

SQL> ALTER TABLE tt_temptable MODIFY( RowNums Generated as Identity (START WITH 1));

每当释放表上的共享锁时.

这篇关于遍历字符串时如何插入临时表-Oracle-PL/SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！