UPSERT进入表与动态表名 [英] UPSERT into table with dynamic table name

问题描述

• 数据提升为〜1行/秒


• 表名称是DYNAMIC，使用传递给它的ObjectID参数生成

以下步骤：
ORA-00942：表或视图不存在

 创建或替换过程
程序SPINSERTDATA
（
 pObjectID IN RAW，
 pDateTime IN TIMESTAMP，
 pValue IN BINARY_DOUBLE，
）
 AS 
 BEGIN 
声明
 vQueryInsert VARCHAR2（1000）; 
 vQueryUpdate VARCHAR2（1000）; 
 vTableName VARCHAR2（30）; 
开始
 vTableName：= FGETTABLENAME（POBJECTID => pObjectID）; 
 vQueryUpdate：='UPDATE'|| vTableName || 'SET'VALUE=：1'; 
 vQueryInsert：='INSERT INTO'|| vTableName || '（DTTIME，VALUE）VALUES（：1，：2）'; 
 
 EXECUTE IMMEDIATE vQueryInsert使用pDateTime，pValue; 
 EXCEPTION 
 WHEN DUP_VAL_ON_INDEX THEN 
 EXECUTE IMMEDIATE vQueryUpdate使用pValue; 
结束; 
 ENDSPINSERTDATA; 
  

• 显然MERGE不起作用，TableName不能动态
  ???


• 我是一个新手，我的第三个月的编码，我通过
  STACKOVERFLOW& Googled了3天了，尝试各种有趣的&
  绝望的解决方案...即使是一个非常相关的链接，如果你发现一个
  将诚实赞赏。

解决方案

MERGE与Native动态SQL（EXECUTE IMMEDIATE）完美结合：

 创建表so_test （pk number not null主键，值varchar2（20））; 
 
 insert into so_test（pk，value）values（1，'one'）; 
 
声明
 l_SQL varchar2（4000）; 
 l_tablename varchar2（4000）default'so_test'; 
 begin 
 l_SQL：='merge into'|| l_tablename || 'target'|| 
'using（select 1 pk，'eins''from dual union all 
 select 2 pk，''zwei''from from）source 
 on（target.pk = source .pk）
匹配然后
更新set target.value = source.value 
当不匹配然后
插入值（source.pk，source.value）
 ; 
 dbms_output.put_line（l_sql）; 
执行立即l_SQL; 
结束
  

您可以在使用MERGE时发布您收到的错误消息吗？

Any better method to UPSERT into a table, provided :

• Data upsert at ~1 row/second
• Table Name is DYNAMIC, generated using ObjectID parameter passed to it

THE FOLLOWING PROCEDURE THROWS : "ORA-00942: table or view does not exist"

CREATE OR REPLACE PROCEDURE
PROCEDURE "SPINSERTDATA"
(
  pObjectID IN RAW,
  pDateTime IN TIMESTAMP,
  pValue IN BINARY_DOUBLE,
)
AS
BEGIN
  Declare
    vQueryInsert VARCHAR2(1000);
    vQueryUpdate VARCHAR2(1000);
    vTableName VARCHAR2(30);
  Begin      
      vTableName := FGETTABLENAME(POBJECTID => pObjectID);
      vQueryUpdate := 'UPDATE '      || vTableName || ' SET "VALUE" = :1';
      vQueryInsert := 'INSERT INTO ' || vTableName || ' ("DTTIME", "VALUE") VALUES (:1, :2)';

      EXECUTE IMMEDIATE vQueryInsert USING pDateTime, pValue;
        EXCEPTION
          WHEN DUP_VAL_ON_INDEX THEN 
            EXECUTE IMMEDIATE vQueryUpdate USING pValue;
  End;
END "SPINSERTDATA";

• Apparently MERGE does not work as the TableName Cannot be dynamic ???
• I'm a newbie, my third month of coding, I scourged through STACKOVERFLOW & Googled for 3 days now, trying all sorts of funny & desperate solutions ... Even a very relevant link if you found one would be honestly appreciated.

解决方案

MERGE works perfectly fine with Native dynamic SQL (EXECUTE IMMEDIATE):

create table so_test(pk number not null primary key, value varchar2(20));

insert into so_test(pk, value) values(1, 'one');

declare
  l_SQL varchar2(4000);
  l_tablename varchar2(4000) default 'so_test';
begin
  l_SQL := 'merge into ' || l_tablename || ' target' ||
    ' using (select 1 pk, ''eins'' value from dual union all
             select 2 pk, ''zwei'' value from dual) source
      on (target.pk = source.pk)
      when matched then 
        update set target.value = source.value
      when not matched then
        insert values(source.pk, source.value)      
  ';
  dbms_output.put_line(l_sql);
  execute immediate l_SQL;
end; 

Could you please post the error message you get when using MERGE?

这篇关于UPSERT进入表与动态表名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！