plsql - 插入过程 - 检查主表中是否存在外键
问题描述
我有以下过程(insert_mapping):
create or replace procedure p_insert_mapping
(header_id in number,
position in number,
xml_mapping in varchar2,
id out number,
result_code out number
)
is
l_id number;
begin
-- check for errors
if header_id not in (select log_push_readouts_headers.id from log_push_readouts_headers) then
result_code := 9302;
raise_application_error(-RESULT_CODE, 'Foreign key constraint violated for headers');
end if;
-- if there are no errors, do insert
if result_code is null then
-- fetch sequence number
id := mapping_seq.nextval;
insert into log_push_readouts_mappings
(id, position, xml_mapping)
values
(id, position, xml_mapping);
end if;
commit;
end;
在以下行中:
if header_id not in (select log_push_readouts_headers.id from log_push_readouts_headers) then
我需要检查主表中是否存在外键。
怎么做?
有人可以给我一个关于如何检查外键是否在具有主键的表中的示例吗?
我收到以下错误:PROCEDURE 的编译错误
AMM_MDM.P_INSERT_MAPPING
Error: PLS-00405: subquery not allowed in this context
Line: 12
Text: if header_id not in (select log_push_readouts_headers.id from log_push_readouts_headers) then
Error: PL/SQL: Statement ignored
Line: 12
Text: if header_id not in (select log_push_readouts_headers.id from log_push_readouts_headers) then
解决方案
关于编译错误,请参考 Using a subquery within PLSQL conditional logic;错误 PLS-00405
为了检查表列的外键约束,我们可以从 all_constrainsts 查询,其中可以检查 CONSTRAINT_TYPE
文档参考:https ://docs.oracle.com/cd/B14117_01/server.101/b10755/statviews_1037.htm#i1576022