ASSIGNMENT 8
TASK 1 :
CODE1:
declare
v sal_details%rowtype;
begin
v.EMPID :='&empid';
v.EMPname :='&empname';
v.Basic :=&basic;
v.Hra :=&hra;
v.Doj :='&date';
v.gender :='&GENDER';
insert into sal_details values(v.EMPID,V.EMPNAME,V.BASIC,V.HRA,V.DOJ,V.GENDER);
DBMS_OUTPUT.PUT_LINE('employee added to the database ');
commit;
EXCEPTION
When Dup_Val_On_Index Then
DBMS_OutPut.Put_Line('One Employee Already Exist with this EmpNo, Insert Aborted');
when others then
DBMS_OutPut.Put_Line(SQLERRM);
end;
/
OUTPUT:
WHEN MORE THAN ONE VALUE ENTERED WITH SAME
EMPID
WHEN VALUE ERROR EXISTS
------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------
CODE 2:
Alter table sal_details
Drop primary key;
insert into sal_details values('SAP2345','Sudha N',30000,750,null,'F');-- inserting data with
same name
insert into sal_details values('SAP9976','Sudha N',30000,750,null,'F');--inserting data with
same name
declare
emp_count number;
v sal_details%rowtype;
name varchar2(50);
duplicate_data exception;
no_data_found exception;
begin
v.empname:='&name';
select count(*) into emp_count from sal_details where empname like v.empname;
if(emp_count>1) then
raise duplicate_data;
elsif(emp_count=1) then
select * into v from sal_Details where empname like v.empname;
DBMS_OUTPUT.PUT_LINE('v.empid'||'---'||'v.EmpName'||'---'||'v.basic'||'---'||'v.hra'||'---
'||'v.doj'||'--'||'v.gender');
DBMS_OUTPUT.PUT_LINE(v.empid||'---'||v.EmpName||'---'||v.basic||'---'||v.hra||'---'||v.doj||'-
-'||v.gender);
else
raise no_data_found;
end if ;
exception
when duplicate_data then
DBMS_OUTPUT.PUT_LINE('DUPLICATE VALUES ENTERED');
when no_data_found then
DBMS_OUTPUT.PUT_LINE('EMP DOES NOT EXIST IN DATABASE');
end;
OUTPUT:
WHEN ONLY ONE PERSON EXISTS WITH SAME NAME
WHEN MORE THAN ONE PERSON EXISTS WITH SAME NAME
WHEN NO PERSON EXISTS
TASK2.2:
CODE1:
Create or replace procedure add_emp_details(vempid varchar2,vempname
varchar2,vbasic number,vhra number,vdoj date,vgender varchar2,vdno number) as
begin
insert into sal_details values(vdno||'00001',vempname,vbasic,vhra,vdoj,vgender,vdno);
commit;
DBMS_OutPut.Put_Line('1');
Exception
When Dup_Val_On_Index Then
DBMS_OutPut.Put_Line('One Employee Already Exist with this EmpNo, Insert Aborted');
When Others Then
DBMS_OutPut.Put_Line(SQLERRM);
End;
OUTPUT: EMPLOYEE IS ADDED WHEN NO EMPLOYEE
EXISTS IN THE DATABASE
WHEN EMPLOYEE ALREADY EXISTS
TABLE
CODE 2:
declare
v sal_details%rowtype;
i number(1);
begin
for i in 1..5 loop
insert into sal_details values('SQL-'||i,'EMPLOYEE-'||i,i*1000,i*100,sysdate+i,'M',10*i);
end loop;
end;
/
OUTPUT:
