oracle在立即执行中返回ID给出“缺失关键字”错误

Question

以下是甲骨文程序

create or replace 
PROCEDURE INSERT_COMMON(
ENTITY_NAME IN VARCHAR2  
, INSERT_QUERY IN varchar2
) 
AS
NEW_ID NUMBER;
BEGIN
    -- execute insert
    DBMS_OUTPUT.PUT_LINE('INSERT_QUERY: ' || INSERT_QUERY);
    -- execute IMMEDIATE INSERT_QUERY returning ID into NEW_ID;
    -- above gives me a syntax error so using as below
    execute IMMEDIATE INSERT_QUERY || ' returning ID into NEW_ID';
    DBMS_OUTPUT.PUT_LINE('NEW_ID: ' || NEW_ID);

END INSERT_COMMON;

和我正在使用的Pl/SQL

DECLARE
  ENTITY_NAME VARCHAR2(200);
  INSERT_QUERY VARCHAR2(200);
BEGIN
  ENTITY_NAME := 'company';
  INSERT_QUERY := 'INSERT INTO COMPANY (NAME) VALUES (''A  Company 2'')';

  INSERT_COMMON(ENTITY_NAME,INSERT_QUERY);
END;

这给了我以下错误

Error report:
ORA-00905: missing keyword
ORA-06512: at "SYSTEM.INSERT_COMMON", line 20
ORA-06512: at line 8
00905. 00000 -  "missing keyword"

然而，我已经测试了和后续的工作很好。

DECLARE
  NEW_ID NUMBER;
BEGIN

  INSERT INTO COMPANY (NAME) VALUES ('A  Company 2') returning ID into NEW_ID;

  DBMS_OUTPUT.PUT_LINE('NEW_ID: ' || NEW_ID);

END;

Answer 1

您需要在dynamic语句中指定要返回的绑定变量，但还需要将returning into <variable>子句添加到execute语句中。

我认为(未经测试，因为您没有提供用于设置表和相关触发器的语句)，下面应该对您的问题进行排序：

create or replace procedure insert_common (entity_name in varchar2,
                                           insert_query in varchar2)
as
  new_id number;
begin
  -- execute insert
  dbms_output.put_line ('INSERT_QUERY: ' || insert_query);

  -- above gives me a syntax error so using as below
  execute immediate insert_query || ' returning ID into :NEW_ID' returning into new_id;

  dbms_output.put_line ('NEW_ID: ' || new_id);
end insert_common;
/