代码之家 › 专栏 › 技术社区 › Jack

Oracle PLS-00103错误。如何检查现有记录并根据该条件进行更新或插入?

ora-00900 upsert plsql oracle sql

Jack · 技术社区 · 16 年前

我需要检查表中是否存在来自SELECT语句的记录。如果该记录存在,则执行更新,否则在表上创建一个记录。我正在努力,但我正在 PLS-00103 错误。

这些是我在dbvisaulzier中运行代码时遇到的错误:

18:00:09  [DECLARE - 0 row(s), 0.000 secs]  [Error Code: 6550, SQL State: 65000]  ORA-06550: line 2, column 12:
PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following:

:= . ( @ % ; not null range default character
18:00:09  [BEGIN - 0 row(s), 0.000 secs]  [Error Code: 6550, SQL State: 65000]  
ORA-06550: line 2, column 97:
PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following:

;
18:00:09  [IF - 0 row(s), 0.000 secs]  [Error Code: 900, SQL State: 42000]  ORA-00900: invalid SQL statement
18:00:09  [ELSE - 0 row(s), 0.000 secs]  [Error Code: 900, SQL State: 42000]   
ORA-00900: invalid SQL statement
18:00:09  [END - 0 row(s), 0.000 secs]  [Error Code: 900, SQL State: 42000]  ORA-00900: invalid SQL statement
18:00:09  [END - 0 row(s), 0.000 secs]  [Error Code: 900, SQL State: 42000]  ORA-00900: invalid SQL statement
... 6 statement(s) executed, 0 row(s) affected, exec/fetch time: 0.000/0.000 sec  [0 successful, 0 warnings, 6 errors]

以下是我的代码:

DECLARE a NUMBER;

BEGIN

  SELECT 1 
    INTO a 
    FROM FREC_EMAIL t
   WHERE t.FranchiseNo = '208254846' 
     AND t.ReportID = 1 
     AND t.id = 165;

  IF a=1 THEN

       UPDATE FREC_EMAIL
          SET email = 'blah@foo.com'
        WHERE FranchiseNo = '208254846' 
          AND ReportID = 1 
          AND ID = 165;

    ELSE

        INSERT INTO FREC_EMAIL
          (FranchiseNo, Email, ReportID)
         VALUES
           ('208254846', 'blah@foo.com', 1);

    END IF;
END;

5 回复 | 直到 15 年前

APC 16 年前

我们应该尽可能使用SQL,并且避免使用PL/SQL,除非它是严格必要的。SQL语句执行速度更快,它们通常需要较少的类型,并且更容易纠正错误。

自从9i Oracle提供了merge,执行“upsert”语句的单个SQL语句。

MERGE INTO frec_email t
USING (SELECT  'blah@foo.com' as email
                ,  '208254846' as FranchiseNo
                , 1 as ReportID
                , 165 as ID 
       FROM dual ) s
ON (s.ID = t.ID)
WHEN MATCHED THEN
     UPDATE SET t.email = s.email
WHEN NOT MATCHED THEN
    INSERT (t.FranchiseNo, t.Email, t.ReportID)
    VALUES  (s.FranchiseNo, s.Email, s.ReportID)
/

Karl T. 16 年前

在pl/sql块中,可以这样做:

更新表集列=… 哪里。。。。。;

如果SQL%行数=0,则插入…… 如果结束;

Pop 16 年前

使用合并命令(也被一些人称为upsert)。Oracle的参考(带示例) here .

Ian Carpenter 16 年前

另一方面,如果你对甲骨文还不熟悉,那么花点时间去处理官方事务是值得的。 documentation . 虽然从哪里开始看起来很困难,但汤姆·基特的 Road Map 是获取必读书目列表的好地方。

祝你好运!

torzech 15 年前

我通常使用以下概念,我认为这比合并更易读:

BEGIN

  UPDATE FREC_EMAIL
     SET email = 'blah@foo.com'
   WHERE FranchiseNo = '208254846' 
     AND ReportID = 1 
     AND ID = 165;

  IF SQL%NOTFOUND THEN
    INSERT INTO FREC_EMAIL
          (FranchiseNo, Email, ReportID, ID)
      VALUES
          ('208254846', 'blah@foo.com', 1, 165);
  END IF;

END;