-- 创建 "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;
没分了
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;
--二楼多了一个begin--谢谢两位了。