sql - 如何编写一个 PL/SQL 代码，在插入之前检查 table_name.column 名称中是否存在值？

Question

insert into AIRPORT_DIM
select airportkey_Seq.nextval, AIRPORTID , CITY_NAME,NULL, NULL 
from TMP_AIRPORT;

如何将上述插入 SQL 代码转换为 pl/sql 代码，以检查新插入行的 AIRPORTID 是否已存在且在 airport_dim 表中具有不同的 city_name？

如果存在，那么做

insert into AIRPORT_DIM 
select airportkey_Seq.nextval, AIRPORTID ,NULL,CITY_NAME, sysdate
from TMP_airport;

别的

insert into airport_dim 
select airportkey_Seq.nextval, AIRPORTID , CITY_NAME,NULL, NULL
from TMP_airport;

以下是 AIRPORT_DIM 和 TMP_airport 表。

CREATE TABLE AIRPORT_DIM
(
AIRPORT_KEY NUMBER,
AIRPORT_CODE    VARCHAR2(8),
CITY_NAME_CURRENT   VARCHAR2(20),
CITY_NAME_NEW   VARCHAR2(20),
EFFECTIVE_DATE  TIMESTAMP(8)
)

CREATE TABLE TMP_AIRPORT
(
AIRPORTID   VARCHAR2(4),
CITY_NAME   VARCHAR2(20)
)

标签： sqloracleplsql

Answer 1

您可以按以下方式实现：

INSERT INTO airport_dim 
select airportkey_Seq.nextval
       , AIRPORTID 
       , CASE WHEN ad.AIRPORT_KEY is null then CITY_NAME else null end
       , CASE WHEN ad.AIRPORT_KEY is null then null else CITY_NAME end 
       , CASE WHEN ad.AIRPORT_KEY is null then null CITY_NAME sysdate end 
from TMP_airport ta
LEFT JOIN AIRPORT_DIM ad
  ON ta.AIRPORTID = ad.AIRPORT_CODE