PL/SQL Exercise 6

Practice 9
Note: You can find table descriptions and sample data in Appendix D “Table Descriptions and Data.”
Save your subprograms as .sql files, using the Save Script button.
Remember to set the SERVEROUTPUT ON if you set it off previously.
1. Create and invoke the ADD_JOB procedure and consider the results.
a. Create a procedure called ADD_JOB to insert a new job into the JOBS table. Provide the ID and title of the job, using two parameters.
b. Compile the code, and invoke the procedure with IT_DBA as job ID and Database Administrator as job title. Query the JOBS table to view the results.
CREATE PROCEDURE ADD_JOB(Id IN jobs.job_id%type,title IN jobs.job_title%type) IS
BEGIN
INSERT INTO JOBS values(Id,title,'1222','123');
END;

EXECUTE ADD_JOB('IT_DBA','Database Administrator');


c. Invoke your procedure again, passing a job ID of ST_MAN and a job title of Stock Manager. What happens and why?
EXECUTE ADD_JOB('ST_MAN','Stock Manager');

Errored as JOB_ID is a unique key and we are passing the same job id.Handled it using Exception

select * from JOBS
CREATE OR REPLACE PROCEDURE ADD_JOB(Id IN jobs.job_id%type,title IN jobs.job_title%type) IS
BEGIN
INSERT INTO JOBS values(Id,title,'1222','123');
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Some Other Errors');
END;


SET SERVEROUTPUT ON
EXECUTE ADD_JOB('ST_MAN','Stock Manager');
___________________________________________________________________
___________________________________________________________________
2. Create a procedure called UPD_JOB to modify a job in the JOBS table.
a. Create a procedure called UPD_JOB to update the job title. Provide the job ID and a new title, using two parameters. Include the necessary exception handling if no update occurs.
b. Compile the code; invoke the procedure to change the job title of the job ID IT_DBA to Data Administrator. Query the JOBS table to view the results.



Also check the exception handling by trying to update a job that does not exist (you can use job ID IT_WEB and job title Web Master).

CREATE OR REPLACE PROCEDURE UPD_JOB(jobId jobs.job_id%type,title jobs.job_title%type) IS
counter jobs.max_salary%type :=0;
JOB_ID_NOT_FOUND EXCEPTION;
CURSOR C1 is select JOB_ID from JOBS;
BEGIN
FOR jobsid IN C1
LOOP
IF jobsid.job_id =jobId THEN
counter :=counter+1;
END IF;
END LOOP;
IF counter=1 THEN
update jobs
SET JOB_TITLE=title
WHERE JOB_ID=JOBID;
ELSE
RAISE JOB_ID_NOT_FOUND;
END IF;
EXCEPTION
WHEN JOB_ID_NOT_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Job Id not found in Table');
END;

SET SERVEROUTPUT ON
EXECUTE UPD_JOB('IT_WEB','ADMINISTRATION_VICE_PRESIDENT');






3. Create a procedure called DEL_JOB to delete a job from the JOBS table.
a. Create a procedure called DEL_JOB to delete a job. Include the necessary exception handling if no job is deleted.
b. Compile the code; invoke the procedure using job ID IT_DBA. Query the JOBS table to view the results.


Also, check the exception handling by trying to delete a job that does not exist (use job ID IT_WEB). You should get the message you used in the exception-handling section of the procedure as output.

CREATE OR REPLACE PROCEDURE DEL_JOB(jobId jobs.job_id%type) IS
counter jobs.max_salary%type :=0;
JOB_ID_NOT_FOUND EXCEPTION;
CURSOR C1 is select JOB_ID from JOBS;
BEGIN
FOR jobsid IN C1
LOOP
IF jobsid.job_id =jobId THEN
counter :=counter+1;
END IF;
END LOOP;
IF counter=1 THEN
DELETE FROM JOBS WHERE JOB_ID=JOBID;
DBMS_OUTPUT.PUT_LINE('DELETED Job Id IS '||JOBID);
ELSE
RAISE JOB_ID_NOT_FOUND;
END IF;
EXCEPTION
WHEN JOB_ID_NOT_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Job Id not found in Table');
END;

SET SERVEROUTPUT ON
EXECUTE DEL_JOB('IT_WEB');


Practice 9 (continued)
4. Create a procedure called QUERY_EMP to query the EMPLOYEES table, retrieving the salary and job ID for an employee when provided with the employee ID.
a. Create a procedure that returns a value from the SALARY and JOB_ID columns for a specified employee ID.
Use host variables for the two OUT parameters salary and job ID.
b. Compile the code, invoke the procedure to display the salary and job ID for employee ID 120.

CREATE OR REPLACE PROCEDURE
QUERY_EMP(empId IN employees.employee_id%type,jobId OUT employees.job_id%type,EmpSalary OUT employees.salary%type) IS
BEGIN
SELECT SALARY,JOB_ID INTO EMPSALARY, jobId FROM EMPLOYEES where EMPLOYEE_ID=empId;
DBMS_OUTPUT.PUT_LINE('Salary is '||EMPSALARY||' and job id is '||jobId);
END;
/

SET SERVEROUTPUT ON
DECLARE
Ide employees.job_id%type;
sal employees.salary%type;
BEGIN
QUERY_EMP(120,IDE,SAL);
DBMS_OUTPUT.PUT_LINE('Annonnymous Block Salary is '||sal||' and job id is '||Ide);
END;




c. Invoke the procedure again, passing an EMPLOYEE_ID of 300. What happens and why?

CREATE OR REPLACE PROCEDURE
QUERY_EMP(empId IN employees.employee_id%type,jobId OUT employees.job_id%type,EmpSalary OUT employees.salary%type) IS
BEGIN
SELECT SALARY,JOB_ID INTO EMPSALARY, jobId FROM EMPLOYEES where EMPLOYEE_ID=empId;
DBMS_OUTPUT.PUT_LINE('Salary is '||EMPSALARY||' and job id is '||jobId);
EXCEPTION
WHEN OTHERS THEN
jobId:='not found';
Empsalary:=000;
DBMS_OUTPUT.PUT_LINE('Error');
END;
/

SET SERVEROUTPUT ON
DECLARE
Ide employees.job_id%type;
sal employees.salary%type;
BEGIN
QUERY_EMP(300,Ide,sal);
DBMS_OUTPUT.PUT_LINE('Salary is '||sal||' and job id is '||Ide);
END;