日期:2014-05-17 浏览次数:20882 次
-- 创建 "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;
------解决方案--------------------
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;