假设你对 deptno ，或主键，然后可以使用 DUP_VAL_ON_INDEX 以及 RAISE_APPLICATION_ERROR ```
create or replace procedure ADD_DEPT(DEPTNO in dept.deptno%type,
DNAME in dept.dname%type,LOC in dept.loc%type)
is
begin

insert into dept values(DEPTNO,DNAME,LOC);

exception

when dup_val_on_index
then
raise_application_error(-20001,'Value duplicated on deptno' );

end;

更新
我给你举个例子。

SQL> create table x ( c1 number not null primary key , c2 number ) ;

insert into x values ( 1 , '1' );

insert into x values ( 2 , '1' );

Table created.

SQL> SQL>
1 row created.

SQL> SQL>
1 row created.

SQL> create or replace procedure add_to_x ( p_c1 in number , p_c2 in number )
2 is
begin
3 4 insert into x values (p_c1 , p_c2);
commit;
5 6 exception when dup_val_on_index
then
7 8 raise_application_error(-20001,'Value duplicated on deptno' );
when others then
9 10 raise;
end; 11
12 /

Procedure created.

SQL> select * from x ;

    C1         C2

     1          1
     2          1

SQL> exec add_to_x ( 1 , 3 ) ;
BEGIN add_to_x ( 1 , 3 ) ; END;

ERROR at line 1:
ORA-20001: Value duplicated on deptno
ORA-06512: at "SYS.ADD_TO_X", line 8
ORA-06512: at line 1

SQL>

我希望你在找这样的东西。

create or replace procedure ADD_DEPT(P_DEPTNO  in dept.deptno%type,
P_DNAME in dept.dname%type,P_LOC in dept.loc%type) is
    duplicate_value_Exists EXCEPTION;
    PRAGMA exception_init( duplicate_value_Exists, -20001 );
    v_count number;
    begin

        select count(1) 
        into v_count
        from dept 
        WHERE DEPTNO = P_DEPTNO 
        AND P_DNAME = DNAME 
        AND LOC = P_LOC;

        if v_count >= 1 then
            RAISE duplicate_value_Exists;
        else
            insert into dept values(P_DEPTNO,P_DNAME,P_LOC);
        end if;
    end;