MAIN BOOK

============
Q-1)
create a procedure named QUERY_EMP that retrieves the last name and salary of an employee based
on their employee ID. The procedure should accept the employee ID as an input parameter (p_id).

create or replace procedure query_emp

(p_id in number,
p_name out varchar2,
p_salary out number)
is
begin
select last_name , salary
into p_name, p_salary
from employees
where employee_id = p_id;
end query_emp;
/

declare
v_emp_name varchar2 (100);
v_sal number;
begin
query_emp (171, v_emp_name, v_sal);
dbms_output.put_line (v_emp_name || ' earns ' || v_sal);
end;
/

-------------------------------------------------------------------------

Q-2)
create a procedure named raise_salary that updates the salary of an employee based on a percentage
increase.

create or replace procedure raise_salary

(p_id in employees.employee_id%type,
p_percent in number)
is
begin
update employees
set salary = salary * (1 + p_percent/100)
where employee_id = p_id;
end raise_salary;
/
------------------------------------------------------------------------

Q-3)
create a stored procedure named process_employees that processes all employees in the employees
table and gives a salary raise of 10% to each employee.

create or replace procedure process_employees

is
cursor c_emp_cursor
is
select employee_id
from employees;
begin
for emp_rec in c_emp_cursor
loop
raise_salary (emp_rec.employee_id, 10);
end loop;
commit;
end process_employees;
/

-------------------------------------------------------------------------------

Q-4)
create a procedure called ADD_DEPT to insert a new department into the departments provide the
department name and location id of the department using two parameters.

create or replace procedure add_dept

(p_dept_name in varchar2,
p_location_id in number)
is
begin
insert into departments
(department_id, department_name, location_id)
values
(departments_seq.nextval, p_dept_name, p_location_id);
commit;
end add_dept;
/

begin
 add_dept (p_dept_name => 'Advertising' ,
p_location_id => 2400);
end;
/

select * from departments;

-------------------------------------------------------------------------------

Q-5)
create a procedure called GET_EMPLOYEE to query the employees table, retrieving the salary and job
is for an employee when provided with the employee id.

create or replace procedure get_employee

(p_emp_id in number)
is
v_sal number;
v_job varchar2 (100);
begin
select salary, job_id
into v_sal, v_job
from employees
where employee_id = p_emp_id;
dbms_output.put_line (v_sal || ' ' || v_job);
null;
end;
/

begin
get_employee (101);
end;
/

OR
----------------

create or replace procedure get_employee

(p_emp_id in number,
p_salary out number,
p_job_id out varchar2)
is
begin
select salary, job_id
into p_salary, p_job_id
from employees
where employee_id = p_emp_id;
end get_employee;
/

declare
v_sal number;
v_job varchar2 (100);
begin
get_employee (110, v_sal, v_job);
dbms_output.put_line (' salary is: ' || v_sal ||
' and job id is: ' || v_job);
end;
/

=====================================================

ACTIVITY GUIDE
================

Q1.
Create a PL/SQL procedure named add_job that inserts a new job into the jobs table, accepts p_jid
and p_jtitle as input parameters, commits the changes, and test it by adding a job with 'IT_DBA' and
'Database Administrator', then verify the insertion with a query.

(a)
create or replace procedure add_job
(p_job_id jobs.job_id%type,
p_job_title jobs.job_title%type)
is
begin
insert into jobs(job_id,job_title)
values(p_job_id,p_job_title);
end add_job;
/
(b)
execute add_job
('IT_DBA','Databasse Administration');
select job_id
from jobs
where job_title='Databasse Administration';

(c)execute add_job
('ST_MAN','Stock Manager');

----------------------------------------

Q2.
Create a PL/SQL procedure named upd_job that updates a job in the jobs table by accepting p_jid and
p_jtitle as input parameters, updates the JOB_TITLE for the given JOB_ID, and raises an error if no
rowsare updated. Test the procedure by updating the job with 'IT_DBA' to 'Data Administrator' and
verify the update.

(a)
create or replace procedure upd_job
(p_jobid in jobs.job_id%type,
p_job_title in jobs.job_title%type) is
begin
update jobs
set job_title=p_job_title
where job_id=p_jobid ;
if sql%notfound then
raise_application_error(-20202,'No job updated.');
end if;
end upd_job;
/

(b)
execute upd_job('IT_DBA','Data Administrator');

select *
from jobs;

(c)
execute upd_job('It_Web','Web Master');
select *
from jobs
where job_id='It_Web';
---------------------------------------------------

Q3.
Create a PL/SQL procedure named del_job that deletes a job from the jobs table by accepting p_jid as
an input parameter. The procedure should delete the job with the given JOB_ID and raise an error if
no rows are deleted. Test the procedure by attempting to delete a job with JOB_ID = 'IT_DBA' and
handle the case where no job is deleted.

(a)
create or replace procedure del_job
(p_jobid jobs.job_id %type) is
begin
delete
from jobs
where job_id=p_jobid;
if sql%notfound then
raise_application_error(-20203,'No jobs deleted');
end if;
end del_job;
/
(b)
execute del_job('IT_DBA');

select *
from jobs
where job_id='IT_DBA';

(c)
execute del_job('IT_WEB');

----------------------------------------------------

Q4.
Create a PL/SQL procedure named get_employee that accepts an employee ID (p_id) and retrieves the
SALARY and JOB_ID of the employee. The procedure should return these values through output
parameters (p_sal and p_job).

(a)
create or replace procedure get_employee
(p_empid in employees.employee_id%type,
p_sal out employees.salary%type,
p_job out employees.job_id %type)
is
begin
select salary,job_id
into p_sal, p_job
from employees
where employee_id=p_empid;
end get_employee;
/

(b)
declare
v_sal number;
v_jobid varchar2(100);
begin
get_employee(101,v_sal,v_jobid);
dbms_output.put_line('Salary is: ' || v_sal || ' and job id is ' || v_jobid);
end;
/

(c)
declare
v_sal number;
v_jobid varchar2(100);
begin
get_employee(300,v_sal,v_jobid);
dbms_output.put_line('Salary is: ' || v_sal || ' and job id is ' || v_jobid);
end;
/