ORA-01422:精确提取返回的行数超过请求的行数触发错误

Question

我想做什么 插入前触发器涉及 PATIENT 表，旨在禁止将两名患者分配到同一房间 他们的年龄差异大于10岁。此外，扳机也是 应该禁止两名年龄允许的患者被分配到同一张床上。

CREATE TABLE CODE
set linesize 130;
set pagesize 100;
drop table patient cascade constraints;
create table patient (pat_id char(7), 
pat_name varchar(41) constraint nn_patnm not null,
pat_gender char(1),
pat_age number(2) constraint nn_patage not null,
pat_admit_dt date constraint nn_patadmdt not null,
pat_wing char(1),
pat_room# number,
pat_bed char(1),
constraint pk_pat primary key (pat_id),
constraint chk_gender check (pat_gender in ('M', 'F')),
constraint chk_age check (pat_age between 1 and 90),
constraint chk_bed check(pat_bed in ('A', 'B')));
insert into patient values ('DB77642', 'Davis, Bill', 'M', 38, '14-DEC-2017', 'A', 16, 'A');
insert into patient values ('GD72222', 'Grimes, David', 'M', 23, '14-DEC-2017', 'B', 17, 'A');
insert into patient values ('LH97384', 'Lisauckis, Hal', 'M', 28, '14-DEC-2017', 'B', 17, 'B');
insert into patient values ('RN31679', 'Robbins, Nancy', 'F', 23, '14-DEC-2017', 'A', 20, 'A');
insert into patient values ('HR32343', 'Howell, Sue', 'F', 20, '14-DEC-2017', 'A', 21, 'B');
desc patient
select * from patient;
commit;

我用来测试触发器的列表

column pat_name format a18
column pat_gender format a10
column pat_admit_dt format a13
column pat_wing format a8
column pat_bed format a8
set feedback on
insert into patient values ('AZ24523', 'Zhou, Alicia', 'F', 24, '14-DEC-2016', 'A', 20, 'B');
select * from patient;
insert into patient values ('JA33234', 'Abbott, John', 'M', 50, '14-DEC-2016', 'A', 16, 'B');
select * from patient;
insert into patient values ('AN32676', 'Newman, Andrew', 'M', 10, '14-DEC-2016', 'A', 16, 'B');
select * from patient;
insert into patient values ('ZZ24523', 'Zhang, Zhaoping', 'F', 38, '14-DEC-2016', 'A', 16, 'A');
select * from patient;
insert into patient values ('PS76234', 'Savant, Priyanka', 'F', 21, '14-DEC-2016', 'A', 21, 'B');
select * from patient;
insert into patient values ('SS43823', 'Sundar, Sarayu', 'F', 24, '14-DEC-2016', 'A', 21, 'A');
select * from patient;
insert into patient values ('PM43453', 'Perrin, Michael', 'M', 43, '14-DEC-2016', 'B', 22, 'A');
select * from patient;

触发

create or replace trigger patient_ins_row
before insert on patient
for each row
declare
room_age number;
difference number;
pat_name1 varchar2(41);
pat_bed1 char(1);
pat_room1 number;
ERROR_MESSAGE EXCEPTION;
BEGIN
select pat_name, pat_age, pat_room#, pat_bed into pat_name1, room_age, pat_room1, pat_bed1
from patient where pat_room# = :new.pat_room#;
if (:new.pat_bed = pat_bed1)
then DBMS_OUTPUT.PUT_LINE ('Cannot Assign ' || :new.pat_name || ' to bed already occupied ' || :new.pat_room# || 'years');
else
difference := :new.pat_age - room_age;
end if;
if (difference > 10)
then RAISE ERROR_MESSAGE;
elsif (difference < 10) then
DBMS_OUTPUT.PUT_LINE ('New Patient: ' || :new.pat_name || ' is too young: age difference is ' || difference || 'years');
else
dbms_output.put_line ('Successful insertion of ' || :new.pat_name || 'in room' || pat_room1);
END IF;
EXCEPTION
WHEN ERROR_MESSAGE THEN
RAISE_APPLICATION_ERROR (-20001, 'New Patient: ' || :new.pat_name || ' is too old: age difference is ' || difference || 'years');
END;

对于许多插入，我的标题不断出现错误。不知道我做错了什么。

Answer 1

由于以下陈述ORA-01422引发的问题：

select pat_name, pat_age, pat_room#, pat_bed 
into pat_name1, room_age, pat_room1, pat_bed1
from patient where pat_room# = :new.pat_room#;

因为，您的连续语句尝试从反复使用的:new.pat_room#中选择pat_room1，其值为16和21.

例如，在测试集中申请时

insert into patient values ('JA33234', 'Abbott, John', 'M', 50, '14-DEC-2016', 'A', 16, 'B'); 

上面的选择将为:new.pat_room#的值16提供多行，并且too_many_rows错误将引发。