ORA-01403未找到任何数据select into

问题：

执行SQL，当无返回值时，会提示“ORA-01403未找到任何数据”。

create or replace trigger tri_kf_trial 
before insert on kf_trial 
for each row 
declare
  v_id    varchar2(25);
begin
    SELECT id INTO v_id FROM HI_PSNDOC_BAD WHERE id = :new.id;
    if (v_id IS NOT NULL) then
      raise_application_error(-20101, '该员工处在黑名单之中!');
    end if;end;

解决办法：

方法一：先判断结果是否存在

create or replace trigger tri_kf_trial 
before insert on kf_trial 
for each row 
declare
  v_count NUMBER;
  v_id    varchar2(25);
begin
  SELECT count(id) INTO v_count FROM HI_PSNDOC_BAD WHERE id = :new.id;
  if v_count > 0 then
    SELECT id INTO v_id FROM HI_PSNDOC_BAD WHERE id = :new.id;
    if (v_id IS NOT NULL) then
      raise_application_error(-20101, '该员工处在黑名单之中!');
    end if;
  end if;
end;

方法二：通过EXCPTION来判断。

create trigger tri_kf_trial
  before insert on kf_trial
  for each row
declare
  v_id varchar2(25);
begin
  SELECT id
    INTO v_id
    FROM HI_PSNDOC_BAD
   WHERE id = :new.id exception when no_data_found then v_id = null;
  if (v_id IS NULL) then
    raise_application_error(-20101, '该员工处在黑名单之中!');
  end if;
end;