PL/SQL中如何使用ifexists-ifnotexists？

我正在尝试将 ifexists 语句从 SQL-Server 转换为 PL/SQL，但出现错误。

我正在尝试检查是否NAME_1我的中不存在table_1，如果它们不存在，那么我正在检查是否COLUMN_NAME='NAME_2'存在于我的table_1，如果存在则insert (NAME_1 and NAME_2)进入我的table_2。 谢谢

T-SQL（SQL 服务器）：

IF NOT (EXISTS (SELECT * from table_name_1 where name='NAME_1'))
BEGIN
    IF (EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'table_1' AND COLUMN_NAME='NAME_2'))

        EXEC('insert into table_name_1 values (''value1'', (select max(column) from table_2), 20)');
    ELSE
        EXEC('insert into table_name_1 values (''value1'', (select max(column) from table_2))');
END

PL/SQL（Oracle）：

DECLARE
l_count NUMBER;
l_count_2 NUMBER;
BEGIN
select count(*) into l_count from table_1 where name='NAME_1';
IF l_count = 0  then
    BEGIN 
        select count(*) into l_count_2 FROM dba_tab_cols  WHERE TABLE_NAME = 'table_1' AND COLUMN_NAME='NAME_2';

        IF l_count_2 > 0 THEN        
          sql_cnt :=  INSERT INTO table_1  VALUES ('value1', "select max(column) from table_2" , '20' );          
        ELSE             
            sql_cnt:= INSERT INTO table_1  VALUES ('value1', "select max(column) from table_2" );
        END IF;                    
       BEGIN
         EXECUTE IMMEDIATE sql_cnt ;
       END;
    END;
END IF;       
END;        

ERROR

错误报告
ORA-06550: 第 11 行，第 27 列：
PLS-00103：当期望其中之一时遇到符号“INSERT”

您的代码大部分都很好，但是您必须像这样修改它：

DECLARE
l_count NUMBER;
l_count_2 NUMBER;
BEGIN
select count(*) into l_count from table_1 where name='NAME_1';
IF l_count = 0  then
    BEGIN 
        select count(*) into l_count_2 FROM dba_tab_cols  WHERE TABLE_NAME = 'table_1' AND COLUMN_NAME='NAME_2';

        IF l_count_2 > 0 THEN        
          sql_cnt :=  'INSERT INTO table_1 (xycolumn1, xycolumn2, xycolumn3) VALUES (''value1'', ''select max(column) from table_2'', ''20'')';          
        ELSE             
           sql_cnt := 'INSERT INTO table_1 (xycolumn1, xycolumn2) VALUES (''value1'', ''select max(column) from table_2'')';
        END IF;                    
       BEGIN
         EXECUTE IMMEDIATE sql_cnt ;
       END;
    END;
END IF;       
END;      

或者像这样：

DECLARE
l_count NUMBER;
l_count_2 NUMBER;
BEGIN
select count(*) into l_count from table_1 where name='NAME_1';
IF l_count = 0  then
    BEGIN 
        select count(*) into l_count_2 FROM dba_tab_cols  WHERE TABLE_NAME = 'table_1' AND COLUMN_NAME='NAME_2';

        IF l_count_2 > 0 THEN        
          INSERT INTO table_1 (xycolumn1, xycolumn2, xycolumn3) VALUES ('value1', 'select max(column) from table_2', '20' );          
        ELSE             
          INSERT INTO table_1 (xycolumn1, xycolumn2) VALUES ('value1', 'select max(column) from table_2');
        END IF;   
    END;
END IF;       
END;      

第一个选项是使用正确的 Oracle 拼写来创建字符串和动态 SQL，第二个选项是通过执行来完全避免动态 SQLINSERT现场（我更喜欢的选择）。

EDIT :你得到的错误是因为你没有封装你的INSERT在一个字符串里面。这就是我在第一个选项中提到的为您所做的更改correct Oracle spelling for string creations and dynamic SQL.

我希望我有所帮助！