如果存在，如何使用-如果PL/SQL中不存在? [英] How to use if exists- if not exists in PL/SQL?

问题描述

我正在尝试将if存在语句从SQL Server转换为PL/SQL，但是出现错误.

I am trying to convert if exists statement from SQL-Server to PL/SQL but having an error.

我正在尝试检查 NAME_1 在我的 table_1 中是否不存在，如果它们不存在，那么我正在检查 COLUMN_NAME ='NAME_2'存在于我的 table_1 中(如果存在)将(NAME_1和NAME_2)(NAME_1)插入我的 table_2 .谢谢

I am trying to check if NAME_1 doesn't exist in my table_1, if they don't exist then I am checking if COLUMN_NAME='NAME_2' exist in my table_1, if it exist then insert (NAME_1 and NAME_2) into my table_2. Thanks

T-SQL(SQL服务器):

T-SQL (SQL-Server):

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):

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;        

错误

错误报告
ORA-06550:第11行，第27列:
PLS-00103:期望其中一个

Error report
ORA-06550: line 11, column 27:
PLS-00103: Encountered the symbol "INSERT" when expecting one of the

推荐答案

您的代码基本上是不错的，但是您必须像这样修改它:

Your code is mostly good, but you would have to modify it either like this:

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，第二个选择是通过当场执行 INSERT 来完全避免使用动态SQL(我更喜欢该选项).

The first option is using the correct Oracle spelling for string creations and dynamic SQL and the second option is avoiding dynamic SQL altogether by executing INSERT on the spot (the option I prefer).

您得到的错误是因为您没有将 INSERT 封装在字符串中.当我提到为字符串创建和动态SQL编写正确的Oracle拼写时，这就是我为第一个选择为您所做的更改.

EDIT : The error you got was because you did not encapsulate your INSERT inside a string. That is what I changed for you in my first option when I mentioned correct Oracle spelling for string creations and dynamic SQL.

希望我能帮上忙！

这篇关于如果存在，如何使用-如果PL/SQL中不存在?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！