如何在Oracle中编写一个简单的查询或PL/SQL代码，在处理另一个表之前检查order_id是否存在于一个表中?&l

Question

我希望填充一些数据表，通常我会运行一些插入脚本或通过csv文件上传。我的要求是，只要order_id在orders表内，数据只能填充在第二个表中。

据我所知，我可能需要写一些PL/SQL代码来检查order_id是否存在于订单表之前运行插入脚本，但不确定如何写这个。如果有人能帮我开始的话，我会很感激的。

这是订单表的创建语句:

CREATE TABLE ORDERS (
ORDER_ID NUMBER NOT NULL,
STATUS VARCHAR2(9) NOT NULL,
ORDER_DATE DATE NOT NULL,
PRIMARY KEY(ORDER_ID),
CONSTRAINT CHK_STATUS CHECK (STATUS = 'OPEN' OR STATUS = 'CLOSED')
);

第二个表的create语句是:

CREATE TABLE ORDER2
(
ORDER_ID NUMBER NOT NULL,
PRODUCT_ID NUMBER NOT NULL,
ORDER_DATE DATE NOT NULL,
PRIMARY KEY(PRODUCT_ID)
);

谢谢。

Answer 1

嗯，是的，你可以手动检查。正如HoneyBadger所评论的那样，exists是一种方法，例如:

SQL> insert into orders (order_id, status, order_date)
2    select 1, 'OPEN'  , trunc(sysdate - 2) from dual union all
3    select 2, 'CLOSED', trunc(sysdate - 1) from dual;
2 rows created.
SQL> select * from orders;
ORDER_ID STATUS ORDER_DATE
---------- ------ ----------
1 OPEN   03.06.2022
2 CLOSED 04.06.2022

让我们尝试将order_id = 1插入order2:

SQL> insert into order2 (product_id, order_id, order_date)
2    select 100, 1, trunc(sysdate - 2) from dual
3    where exists (select null
4                  from orders
5                  where order_id = 1);
1 row created.

成功，因为orders表中存在order_id = 1。那不存在的order_id = 3呢?

SQL> insert into order2 (product_id, order_id, order_date)
2    select 300, 3, trunc(sysdate) from dual
3    where exists (select null
4                  from orders
5                  where order_id = 3);
0 rows created.
SQL>

正确，没有插入任何内容。

---

但是，为什么不让数据库为你做呢?创建外键约束除非order_id存在于orders表中，否则不允许将任何行插入order2表:

SQL> create table orders (
2      order_id   number      constraint pk_ord primary key,
3      status     varchar2(6) constraint chk_ord_stat check (status in ('OPEN', 'CLOSED'))
4                             not null,
5      order_date date        not null
6  );
Table created.
SQL> create table order2 (
2      product_id number      constraint pk_ord2 primary key,
3      order_id   number      constraint fk_ord2_ord references orders (order_id)
4                             not null,
5      order_date date        not null
6  );
Table created.
SQL>

测试:

SQL> insert into order2 (product_id, order_id, order_date)
2    values (300, 3, trunc(sysdate));
insert into order2 (product_id, order_id, order_date)
*
ERROR at line 1:
ORA-02291: integrity constraint (SCOTT.FK_ORD2_ORD) violated - parent key not
found

SQL>

看到了

?Oracle不允许你这样做，你不需要检查任何东西。

---

另一方面，为什么是两个表?大多数列都是公共的(我假设它们也共享公共数据)，所以也许您可以将product_id添加到订单中(但是我不知道order_id和product_id是否构成主键):

SQL> create table orders (
2      order_id   number,
3      product_id number,
4      status     varchar2(6) constraint chk_ord_stat check (status in ('OPEN', 'CLOSED'))
5                             not null,
6      order_date date        not null,
7      --
8                             constraint pk_ord primary key (order_id, product_id)
9  );
Table created.
SQL>