如何在 IF 语句中替换 SELECT 语句以使其正常工作

Question

我有一个简单的问题 - 为了举例起见，让我们有表格

城市（身份证，姓名）。

一个想法是，当我想添加新城市时，我首先确保它不在表中 CITY。

代码示例为：

IF cityName NOT IN (SELECT name FROM city) THEN   
  INSERT INTO City(ID, NAME) VALUES(100, cityName); 
ELSE        
  Raise namingError;   
END IF;

但是我不能在 if 语句中包含该子查询，那么我应该用什么替换它？我可以使用任何类型的列表或变量或技巧？

Answer 1

 IF NOT EXISTS(SELECT 1 FROM CITY WHERE NAME = <CITYNAME>) 
    INSERT INTO City(ID, NAME) VALUES(100, cityName);

或

 INSERT INTO City
  SELECT 100,'cityName'
   FROM dual 
         WHERE NOT EXISTS (SELECT 1
                 FROM CITY
                WHERE name = cityname
              )

我在这里阅读了第二个查询

我没有数据库可以尝试这个，但这应该可以工作

Answer 2

您可以使用合并命令执行插入到表中的操作。虽然合并命令用于在数据不存在时执行插入，或者在这种情况下执行更新（如果数据存在），因为您只有两个字段，它只会为您执行插入。

如果要从一个或多个表中获取数据并将它们合并为一个表，这将非常有用。

MERGE INTO city c
    USING (SELECT * FROM city_import ) h
    ON (c.id = h.id and c.city = h.city)
  WHEN MATCHED THEN
  WHEN NOT MATCHED THEN
    INSERT (id, city)
    VALUES (h.id, h.city);

http://www.oracle-base.com/articles/9i/merge-statement.php

Answer 3

如果是我，我可能会做类似的事情

DECLARE
  rowCity  CITY%ROWTYPE;
BEGIN
  SELECT * INTO rowCity FROM CITY c WHERE c.NAME = cityName;
  -- If we get here it means the city already exists; thus, we raise an exception
  RAISE namingError;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    -- cityName not found in CITY; therefore we insert the necessary row
    INSERT INTO City(ID, NAME) VALUES(100, cityName);
END;

分享和享受。

Answer 4

两个选项：

• 一个使用带有LEFT OUTER JOIN的INSERT INTO ... SELECT;和
• 另一种使用MERGE

SQL 小提琴

Oracle 11g R2 架构设置：

CREATE TABLE city (
  ID   NUMBER(2) PRIMARY KEY,
  NAME VARCHAR2(20)
);
INSERT INTO city
SELECT 1, 'City Name' FROM DUAL;
CREATE TABLE city_errors (
  ID    NUMBER(2),
  NAME  VARCHAR2(20),
  TS    TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  ERROR VARCHAR2(20)
);

查询 1：

DECLARE
  city_id   CITY.ID%TYPE   := 2;
  city_name CITY.NAME%TYPE := 'City Name';
  namingError EXCEPTION;
  PRAGMA EXCEPTION_INIT( namingError, -20001 );
BEGIN
  INSERT INTO city ( id, name )
  SELECT city_id,
         city_name
  FROM   DUAL d
         LEFT OUTER JOIN
         city c
         ON ( c.name = city_name )
  WHERE  c.id IS NULL;
  IF SQL%ROWCOUNT = 0 THEN
    RAISE namingError;
  END IF;
EXCEPTION
  WHEN DUP_VAL_ON_INDEX THEN
    -- Do something when duplicate ID found.
    INSERT INTO city_errors ( ID, NAME, ERROR ) VALUES ( city_id, city_name, 'Duplicate ID' );
  WHEN namingError THEN
    -- Do something when duplicate Name found.
    INSERT INTO city_errors ( ID, NAME, ERROR ) VALUES ( city_id, city_name, 'Duplicate Name' );
END;

结果：

查询 2：

DECLARE
  city_id   CITY.ID%TYPE   := 3;
  city_name CITY.NAME%TYPE := 'City Name';
  namingError EXCEPTION;
  PRAGMA EXCEPTION_INIT( namingError, -20001 );
BEGIN
  MERGE INTO city c
  USING ( SELECT city_id   AS id,
                 city_name AS name
          FROM DUAL ) d
  ON    ( c.Name = d.Name )
  WHEN NOT MATCHED THEN
    INSERT VALUES ( d.id, d.name );
  IF SQL%ROWCOUNT = 0 THEN
    RAISE namingError;
  END IF;
EXCEPTION
  WHEN DUP_VAL_ON_INDEX THEN
    -- Do something when duplicate ID found.
    INSERT INTO city_errors ( ID, NAME, ERROR ) VALUES ( city_id, city_name, 'Duplicate ID' );
  WHEN namingError THEN
    -- Do something when duplicate Name found.
    INSERT INTO city_errors ( ID, NAME, ERROR ) VALUES ( city_id, city_name, 'Duplicate Name' );
END;

结果：

查询 3：

SELECT * FROM City

结果：

| ID |      NAME |
|----|-----------|
|  1 | City Name |

查询 4：

SELECT * FROM City_Errors

结果：

| ID |      NAME |                             TS |          ERROR |
|----|-----------|--------------------------------|----------------|
|  2 | City Name | January, 02 2014 20:01:49+0000 | Duplicate Name |
|  3 | City Name | January, 02 2014 20:01:49+0000 | Duplicate Name |