创建存储过程又遇到有关问题。

-- 创建 "SDD"."DEPT" 表CREATE TABLE "SDD"."DEPT"(  "DEPTNO" NUMBER(2,0),  "DNAME" VARCHAR2(14),    "LOC" VARCHAR2(13))TABLESPACE "SDD";-- 创建 "SDD"."EMP" 表CREATE TABLE "SDD"."EMP"(  "EMPNO" NUMBER(4,0),  "ENAME" VARCHAR2(10),  "JOB" VARCHAR2(9),  "MGR" NUMBER(4,0),  "HIREDATE" DATE,  "SAL" NUMBER(7,2),  "COMM" NUMBER(7,2),  "DEPTNO" NUMBER(2,0),  CONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO"))TABLESPACE "SDD";如果这样创建就会出问题create or replace procedure pro_insert_tab_emp(empno number, ename varchar2,job varchar2, mgr number, sal number, comm number, p_deptno number)as  myexception  exception;       --定义异常类型变量 begin  if (p_deptno not in (select deptno from dept)) then --这边select 怎么报错。。    raise myexception;  end if;    insert into emp(empno, ename, job, mgr,hiredate, sal, comm, deptno) values(empno, ename, job, mgr, sysdate(), sal, comm, p_deptno);  commit;  dbms_output.put_line('insert a record');exception                          when myexception then                     --定义异常时处理的语句       dbms_output.put_line('deptno取值错误'); end;------------查看 user_objects表，status为invalid------如果这么创建就没问题。。难道存储过程里面不能查询其他的表？create or replace procedure pro_insert_tab_emp(empno number, ename varchar2,job varchar2, mgr number, sal number, comm number, p_deptno number)as  myexception  exception;       --定义异常类型变量 begin  if (p_deptno not in (10, 20, 30)) then --这边select 怎么报错。。    raise myexception;  end if;    insert into emp(empno, ename, job, mgr,hiredate, sal, comm, deptno) values(empno, ename, job, mgr, sysdate(), sal, comm, p_deptno);  commit;  dbms_output.put_line('insert a record');exception                          when myexception then                     --定义异常时处理的语句       dbms_output.put_line('deptno取值错误'); end;没分了。。。

create or replace procedure pro_insert_tab_emp(empno number, ename varchar2,job varchar2, mgr number, sal number, comm number, p_deptno number)as  iCount number;  myexception  exception;       --定义异常类型变量 begin --简单点可以采用这样折中的方法嘛 select nvl(count(1),0) into iCount from dept where deptno = p_deptno;--  if (p_deptno not in (select deptno from dept)) then --这边select 怎么报错。。--    raise myexception;--  end if;  if(iCount = 0) then    raise myexception;  end if;    insert into emp(empno, ename, job, mgr,hiredate, sal, comm, deptno) values(empno, ename, job, mgr, sysdate(), sal, comm, p_deptno);  commit;  dbms_output.put_line('insert a record');exception                          when myexception then                     --定义异常时处理的语句       dbms_output.put_line('deptno取值错误'); end;
------解决方案--------------------
SQL code
create or replace procedure pro_insert_tab_emp(empno number, ename varchar2,job varchar2, mgr number, sal number, comm number, p_deptno number)as  iCount number; begin --简单点可以采用这样折中的方法嘛begin select count(1) into iCount from dept where deptno = p_deptno; insert into emp(empno, ename, job, mgr,hiredate, sal, comm, deptno) values(empno, ename, job, mgr, sysdate(), sal, comm, p_deptno);  commit;  dbms_output.put_line('insert a record'); when NO_DATA_FOUND then                    --定义异常时处理的语句       dbms_output.put_line('deptno取值错误'); end;