The scenario is whan user passes an employee_no to be inserted into EMP table i wan that value to be check against the existing record. If the employee_no does not exists the record will be inserted into the table , but if the record alredy exists the employee_no will be incremented by 1 each time and checked against existing record till no more such employee_no exists and finally the record gets inserted into the database table.
So hear we go.
[CODE]
create or replace procedure find(eno number, flag out boolean) is
var1 number(2);
begin
select count(*) into var1 from emp where empno = eno;
if var1 = 0 then
flag := FALSE;
else
flag := TRUE;
end if;
end;
[/CODE]
The above procedure will check for the existance of the record in the database table. So what i did here is pass the Primay key column as an argument to the procedure. This procedure also has an OUT parameter, that tells what the query output was. Simply i take a count of the record based on the PK column, if the record does not exists (count =0) return FALSE ,TRUE otherwise.
Now this procedure is called inside another procedure, the next one (INSERT_EMP).
I pass a single number parameter to the procedure, which in turn is passed into the first procedure (FIND). So the logic is tested inside FIND and the output of that procedure is returned back to this prodecure. Based on that value the actual transaction is completed.
[CODE]
create or replace procedure INSERT_EMP(emp_num number) is
eno emp.empno%type;
fg boolean;
begin
eno := emp_num;
<>
find(eno, fg);
if fg = true then
dbms_output.put_line(eno || ' Already exists...');
eno := eno + 1;
goto abc;
else
insert into emp (empno) values (eno);
COMMIT;
dbms_output.put_line('New empno inserted is : ' || eno);
end if;
end;[/CODE]
Submit an article or tip