Q.1 Write a PL/SQL procedure raise_salary for raising salary of entered employee number by rupees 500. (use emp table)
Ans.
create or replace procedure raise_salary (eno in NUMBER)
is
begin
update emp set sal=sal+500 where empno=eno;
end raise_salary;

declare
eno number:=&eno;
begin
exec raise_salary(10);
end;

Q.2 Write a PL/SQL procedure replace_name to replace all names with the first character in capital and others in lower case. (use emp table)
Ans.
create or replace procedure replace_name (name in VARCHAR2)
is
update emp set ename= where empno=;
end raise_salary;

declare
eno number:=&eno;
begin
exec raise_salary(10);
end;

Q.3 Write a PL/SQL procedure to display all the details of emp table for given empno.
Ans.
create or replace procedure emp_detail (eno in NUMBER)
is
cursor c1 is select * from emp where empno=eno;
rec c1%rowtype;
begin
  open c1;
  loop
  fetch c1 into rec;
  dbms_output.put_line(rec.empno || ' ' ||rec.ename|| ' '||rec.job||' ' ||rec.deptno);
  exit when c1%notfound;
  end loop;
  close c1;
end emp_detail;

Q.4 Write a PL/SQL function getname to find  name of employee when empno is provided by user. (use emp table)
Ans.
Create or replace function getname(p_empno emp.empno%type) return varchar
Is
Cursor empname_cur(p_emp[no emp.empno%type) is select ename from emp where empno=p_empno;
Begin
Open empname_cur(p_empno);
Fetch empname_cur into r_empname;
If empname_cur%notfound then
R_empname.ename := ‘UNKNOWN EMPLOYEE’;
End if;
Close empname_cur;
Return r_empname.ename;
End;

Q.5 Write a Pl/SQL function Factorial to find factorial of the number provided by user.
Ans.
create or replace function factorial ( n in number) return number
is
  res number(5):=1;
  i number;
begin
  for i in 1..N
  loop
  res:=res*i;
  end loop;
 return res;
end factorial;
 
declare
 num number;
begin
 num:=factorial(&num);
 dbms_output.put_line('factorial='||num);
end;

Q.6 Write a PL/SQL function total_salary to find out total salary amount of all employees. (use emp table)
Ans.
Create or replace function total_salary( ) return number
Is
Cursor total_salary_cur(p_emp[no emp.empno%type) is select ename from emp;
Begin
Open total_salary_cur(p_empno);
Fetch empname_cur into r_empname;
If empname_cur%notfound then
R_empname.ename := ‘UNKNOWN EMPLOYEE’;
End if;
Close empname_cur;
Return r_empname.ename;
End;

Q.7Create and invoke a package that contains private and public constructs.
    a) create package specification and package body called COMM_PACKAGE that contains G_COMM  variable for global commission  and RESET_COMM procedure as public construct. Package also have VALIDATE_COMM function which verify commission entered is more than maximum commission if so then return FALSE otherwise return TRUE. If commission is not more than max then set commission to G_COMM otherwise raise error “Invalid commission”. 
Ans.
create or replace package comm_package is
g_comm number:=0.10;
procedure reset_comm
(p_comm in number);
end comm_package;

create or replace package body comm_package is
function validate_comm (p_comm in number)
RETURN BOOLEAN
is
v_max_comm number;
begin
select Max(comm) into v_max_comm from emp;
if p_comm> v_max_comm then
return(false);
else return(true);
end if;
end validate_comm;
procedure reset_comm (p_comm in number)
is begin
if validate_comm(p_comm) THEN g_comm:=p_comm; --reset global variable
else
Raise_application_error(-20210,'invalid commision');
end if;
end reset_comm;
end comm_package;

exec comm_package.reset_comm(1000);

select comm from emp;


Q.8 Create and invoke a package that contains private and public constructs.
a)  Create a package specification and package body called EMP_PACK that contains NEW_EMP procedure as a public construct and VALID_DEPTID function as a private construct.
b) Invoke the NEW_EMP procedure, using 15 as a department number. As department id 15 does not exist in the department table. You should get an error message. VALID_DEPTID will return whether deptid is valid or not.
c) Invoke the NEW_EMP procedure, using an existing department id 20.