存储过程_错误

Question

create or replace procedure add_employee
(eno number,name varchar2,sal number,job varchar2 default 'CLERK',dno number)
IS
  e_integrity exception;
  pragma exception_init(e_integrity,-2291);
BEGIN
   insert into emp(empno,ename,sal,job,deptno)
   values(emo,name,sal,job,dno);
   EXCEPTION
     when dup_val_on_index then
       raise_application_error(-20000,'雇员号不能重复');
     when e_integrity then
      raise_application_error(-20001,'部门号存在');
END;
错误(7,4): PL/SQL: SQL Statement ignored
错误(8,11): PL/SQL: ORA-00984: 列在此处不允许

Answer 1

推测是JOB参数是关键字,建议变量名建立规范

Answer 2

推测是JOB参数是关键字,建议变量名建立规范 

Answer 3

不是:
deptno frreign_key
empno primary_key

Answer 4

LZ 太粗心了，values(emo,name,sal,job,dno)中的emo应该是eno

Answer 5

SQL> create or replace procedure add_employee
  2  (eno number,name varchar2,sal number,job varchar2 default 'CLERK',dno number)
  3  IS
  4    e_integrity exception;
  5    pragma exception_init(e_integrity,-2291);
  6  BEGIN
  7    insert into emp(empno,ename,sal,job,deptno)
  8    values(eno,name,sal,job,dno);--eno
  9    EXCEPTION
 10    when dup_val_on_index then
 11    raise_application_error(-20000,'雇员号不能重复');
 12    when e_integrity then
 13    raise_application_error(-20001,'部门号存在');
 14  END;
 15  /Procedure created

Answer 6

再麻烦一下大家了：当雇员号重复时，为什么，出现下面*Cause:*Action:exec add_employee(1111,'MaRY',2000,'MANAGER',10)
错误报告:
ORA-20000: 雇员号不能重复
ORA-06512: 在 "SCOTT.ADD_EMPLOYEE", line 11
ORA-06512: 在 line 1
20000. 00000 -  "%s"
*Cause:    The stored procedure 'raise_application_error'
           was called which causes this error to be generated.
*Action:   Correct the problem as described in the error message or contact
           the application administrator or DBA for more information.

Answer 7

create or replace procedure add_employee(eno  number,
                                         name varchar2,
                                         sal  number,
                                         job  varchar2 default 'CLERK',
                                         dno  number) IS
  e_integrity exception;
  pragma exception_init(e_integrity, -2291);
BEGIN
  insert into emp
    (empno, ename, sal, job, deptno)
  values
    (emo, name, sal, job, dno);   ---emo写错了，应该是eno
EXCEPTION
  when dup_val_on_index then
    raise_application_error(-20000, '雇员号不能重复');
  when e_integrity then
    raise_application_error(-20001, '部门号存在');
END;