delete deptno record in dept table

hi sir,
the following procedure is doing delete deptno record in dept table

but reference to deptno in emp table so i can not delete data

i want delete unless do match deptno in emp then
match data would came error



Create or replace procedure dept_del(pname varchar2)
is
dname_exc exception;
pragma exception_int(dname_exc,-2292);
cursor dept_cur is select deptno from dept where
dname=pname;
dept_rec dept%rowtype;
ss number;
emp_rec emp%rowtype;
begin
for dept_rec in dept_cur loop
ss:=dept_rec.deptno;
for emp_rec in(select deptno from emp) loop
if emp_rec.deptno<>ss then
delete from dept where dname=pname;
else
dbms_output.put_line(ss);
end if;
end loop;
end loop;
exception
when dname_exc then
dbms_output.put_line('integrity constraint');
end;

[QUOTE=priyasp_msit;29735]hi sir,
the following procedure is doing delete deptno record in dept table

but reference to deptno in emp table so i can not delete data

i want delete unless do match deptno in emp then
match data would came error



Create or replace procedure dept_del(pname varchar2)
is
dname_exc exception;
pragma exception_int(dname_exc,-2292);
cursor dept_cur is select deptno from dept where
dname=pname;
dept_rec dept%rowtype;
ss number;
emp_rec emp%rowtype;
begin
for dept_rec in dept_cur loop
ss:=dept_rec.deptno;
for emp_rec in(select deptno from emp) loop
if emp_rec.deptno<>ss then
delete from dept where dname=pname;
else
dbms_output.put_line(ss);
end if;
end loop;
end loop;
exception
when dname_exc then
dbms_output.put_line('integrity constraint');
end;[/QUOTE]

Here are some suggestions?

[B]>> pragma exception_int(dname_exc,-2292);[/B]
it is pragma exception_[B]init[/B] not [B]int[/B]


[B]>>if emp_rec.deptno<>ss then
delete from dept where dname=pname;[/B]

Here you are checking condition. If no record is found in emp then only you are deleting that record in dept table. Then there will not be any integrity constraint problem.


[B]>>for emp_rec in(select deptno from emp) loop[/B]
Here better to use select [B]distinct[/B] deptno from emp


[B]>>pragma exception_int(dname_exc,-2292)[/B]
Do you know what is the use of prgama exception_init?
As per my expectation it will never raise this exception.


What is your doubt? Still I am clear about that.

Create or replace procedure dept_del(pname varchar2)
is
dname_exc exception;
pragma exception_init(dname_exc,-2292);
cursor dept_cur is select deptno from dept where
dname=pname;
dept_rec dept%rowtype;
ss number;
emp_rec emp%rowtype;
begin
for dept_rec in dept_cur loop
ss:=dept_rec.deptno;
for emp_rec in(select deptno from emp) loop
if emp_rec.deptno<>ss then
delete from dept where dname=pname;
else
dbms_output.put_line(ss);
end if;
end loop;
end loop;
exception
when dname_exc then
dbms_output.put_line('integrity constraint');
end;


this program is run successfully.

my expected result have not came.


some data only foreign key reference(deptno in dept) deptno in emp table

so i want to delete unmatched data
at deptno in dept table.
but this above program checked full table then match only consider so result came integrity constraint

but i want match data result come integrity constraint
not matched data will dlelete

[B]>>but i want match data result come integrity constraint
not matched data will dlelete[/B]

For testing purpose first I have given OPERATIONS AS PARAMATER
It deleted one record for deptno = 40

Next I have given SALES as parameter.
It raised exception and displaying message
"integrity constraint"

It is working as per your requirement. Then what is your problem?

The following modifications avoid full table scan of emp table.

Create or replace procedure dept_del(pname varchar2)
is
dname_exc exception;
pragma exception_init(dname_exc,-2292);
cursor dept_cur is select deptno from dept where
dname=pname;
v_count number;
dept_rec dept%rowtype;
ss number;
emp_rec emp%rowtype;
begin
[B]for dept_rec in dept_cur loop
ss:=dept_rec.deptno;
select count(*) into v_count
from emp
where deptno = dept_rec.deptno;
if v_count = 0 then
delete from dept where dname=pname;
else
dbms_output.put_line(ss);
end if;[/B]
end loop;
exception
when dname_exc then
dbms_output.put_line('integrity constraint');
end;

i want following
For testing purpose first I have given OPERATIONS AS PARAMATER
It deleted one record for deptno = 40

Next I have given SALES as parameter.
It raised exception and displaying message
"integrity constraint

but my program and ur program is coming same result

scaning full table suppose if one matched data came,result came "integrity constraint" ,i can't delete other un matched datas

Once an exception is raised it terminates the normal processing and control transfers to exception handling part. After executing exception handling part control goes to host environment. So you can not delete remaining records once exception is raised.