时间戳

首页 > 解决方案 > 在 Oracle SQL 中将行可重复插入到表中

sql - 在 Oracle SQL 中将行可重复插入到表中

问题描述

我想要一个可以多次运行的脚本，而不是在第一次之后向数据库状态添加一行。它应该在 KPI_TYPE 表中插入包含两列的行，列是 KPI_TYPE_ID 和 NAME，如果它更新序列 KPI_TYPE_SEQ 会很好，但仅在第一次运行时。

我得到的第一个想法是：

merge into KPI_TYPE dest
using (select 26 as KPI_TYPE_ID, 'Web Service Availability' as NAME from dual) src
on (src.KPI_TYPE_ID = dest.KPI_TYPE_ID)
when matched then update set 
  dest.NAME    = src.NAME
when not matched then 
  insert (KPI_TYPE_ID, NAME)
  values (src.KPI_TYPE_ID, src.NAME);

这几乎实现了我想要的，但它不会更新序列。如果序列的更新仅在值为 26 时发生，那也很好。如果它发生在第二个 SQL 命令中就可以了。

还有一个触发器将序列附加到表中：

create or replace TRIGGER "SCHEMA"."B1_KPI_TYPE" 
  BEFORE INSERT ON KPI_TYPE
  FOR EACH ROW
DECLARE
BEGIN
  IF( :new.KPI_TYPE_ID IS NULL )
  THEN
    :new.KPI_TYPE_ID := KPI_TYPE_SEQ.nextval;
  END IF;
END;

之前的示例数据：

11  DB Connectivity
12  DB Health Check
13  SOAP Service Availability
14  FTP/SFTP Server Availability
18  Queue Check
19  Business Check
20  Engine Availability
21  Backlog Management
23  Composite KPI
24  DB Objects Check
25  SMSC Gateway Availability

前序：

CREATED 01.06.18
LAST_DDL_TIME   01.06.18
SEQUENCE_OWNER  SCHEMA
SEQUENCE_NAME   KPI_TYPE_SEQ
MIN_VALUE   1
MAX_VALUE   9999999999999999999999999999
INCREMENT_BY    1
CYCLE_FLAG  N
ORDER_FLAG  N
CACHE_SIZE  0
LAST_NUMBER 26

第一次运行脚本后：

11  DB Connectivity
12  DB Health Check
13  SOAP Service Availability
14  FTP/SFTP Server Availability
18  Queue Check
19  Business Check
20  Engine Availability
21  Backlog Management
23  Composite KPI
24  DB Objects Check
25  SMSC Gateway Availability
26  Web Service Availability

第一次运行后的序列：

CREATED 01.06.18
LAST_DDL_TIME   01.06.18
SEQUENCE_OWNER  SCHEMA
SEQUENCE_NAME   KPI_TYPE_SEQ
MIN_VALUE   1
MAX_VALUE   9999999999999999999999999999
INCREMENT_BY    1
CYCLE_FLAG  N
ORDER_FLAG  N
CACHE_SIZE  0
LAST_NUMBER 27

第二次脚本运行后：

11  DB Connectivity
12  DB Health Check
13  SOAP Service Availability
14  FTP/SFTP Server Availability
18  Queue Check
19  Business Check
20  Engine Availability
21  Backlog Management
23  Composite KPI
24  DB Objects Check
25  SMSC Gateway Availability
26  Web Service Availability (It is also ok if this gets updated to 26 New Name)

第二次运行后的序列：

CREATED 01.06.18
LAST_DDL_TIME   01.06.18
SEQUENCE_OWNER  SCHEMA
SEQUENCE_NAME   KPI_TYPE_SEQ
MIN_VALUE   1
MAX_VALUE   9999999999999999999999999999
INCREMENT_BY    1
CYCLE_FLAG  N
ORDER_FLAG  N
CACHE_SIZE  0
LAST_NUMBER 27

我现在正在尝试这个：

declare v_seqvariable number;
begin
    SELECT last_number INTO v_seqvariable 
      FROM all_sequences
     WHERE sequence_owner = 'SCHEMA'
       AND sequence_name = 'KPI_TYPE_SEQ';    

    if v_seqvariable = 26 then DBMS_OUTPUT.put_line('Is 26'); else DBMS_OUTPUT.put_line('Is not 26'); end if;
end;

但是，我没有得到任何输出。我必须冲洗它还是什么？

标签： sqloraclesequence

解决方案

The following code does to the sequence what I want:

SET SERVEROUTPUT ON;
declare 
v_seqvariable number;
v_result number;
begin
    SELECT last_number INTO v_seqvariable 
      FROM all_sequences
     WHERE sequence_owner = 'SCHEMA'
       AND sequence_name = 'KPI_TYPE_SEQ';    

    if v_seqvariable = 26 then DBMS_OUTPUT.put_line('Is 26'); select SCHEMA.KPI_TYPE_SEQ.nextval INTO v_result FROM DUAL; else DBMS_OUTPUT.put_line('Is not 26'); end if;
end;

Output is:

anonymer Block abgeschlossen
Is 26

anonymer Block abgeschlossen
Is not 26

Now what is left is that I only have to test the MERGE code.

推荐阅读