oracle 如何在存储过程中检查表中的值是否存在于另一个表中

eqqqjvef 于 2023-08-04 发布在 Oracle

关注(0)|答案(1)|浏览(280)

你好，我正在检查一个表中的数据是否存在于另一个表中，这是我的代码
我尝试了2种方法，但都不起作用idk为什么方法1

create or replace procedure proc1
As
Begin
if not exists(select * from T2 where c=(select * from (select * from T1 order by c) where rownum=1)) then
insert into T2
select * from (select * from T1 order by c) where rownum=1
end if;
End;

字符串
方法2

create or replace procedure proc1
As
Begin
declare var integer;
select count(*) into var from (select * from T1
intersect
select * from T2)
group by c
if var<2 then
insert into T2
select * from (select * from T1 order by c) where rownum=1
end if;
end;

型

oracle

来源：https://stackoverflow.com/questions/76644544/how-to-check-if-value-in-table-exists-in-another-table-in-stored-procedures-orac

1条答案

按热度按时间

1cosmwyk1#

您不需要PL/SQL，可以使用单个SQL merge语句：

create or replace procedure proc1
As
Begin
  MERGE INTO t2
  USING (
    SELECT a, b, c, d, e
    FROM   t1
    ORDER BY c
    FETCH FIRST ROW ONLY
  ) t1
  ON (t1.c = t2.c)
  WHEN NOT MATCHED THEN
    INSERT (a, b, c, d, e)
    VALUES (T1.a, T1.b, T1.c, T1.d, T1.e);
End;
/

字符串
或者INSERT与NOT EXISTS和相关子查询：

create or replace procedure proc1
As
Begin
  INSERT INTO t2
  SELECT *
  FROM   (
    SELECT *
    FROM   t1
    ORDER BY c
    FETCH FIRST ROW ONLY
  ) t1
  WHERE NOT EXISTS ( SELECT 1
                     FROM   t2
                     WHERE  t1.c = t2.c );
End;
/

型
fiddle

赞(0）回复(0）举报 2023-08-04

我来回答

oracle 如何在存储过程中检查表中的值是否存在于另一个表中

1条答案

相关问题

热门标签

最新问答