Create a PLSQL Package with Procedure that take Designation and Incentive

Create a PL/SQL Package with Procedure in it. Procedure will take designation and incentive as input and update the employee salary by adding the incentive for the given designation. Display the number of employee records that have got updated, e.g. '3 employee(s) are updated'.

Table: EMPLOYEE

COLUMN	DATATYPE	CONS.
EMPID	NUMBER(5)	PK
EMP_NAME	VARCHAR2(25)	NOT NULL
SALARY	NUMBER(10,2)
DESIGNATION	VARCHAR2(25)

EMP_ID  EMP_NAME  SALARY  DESIGNATION
101     Mathew    45000   PROGRAMMER    
102     Sam       54000   MANAGER
103     John      35000   TEAM LEAD
104     James     48000   PROGRAMMER
105     Josh      25000   TESTER
            

Functional Requirement: Package name as EMP_DESIGNATION, and Procedure signature: EMP_DETAILS(design employee.designation%TYPE, incentive number);

(Hint: Data is case sensitive. Use '/' to terminate the PLSQL block)

Step-by-Step Guide to Create a PL/SQL Package with Procedure that take Designation and Incentive

Step 1: Setting Up for Output:
First, we ensure that the output from the database is visible by enabling server output. This will allow us to see messages from the PL/SQL code.

Step 2: Creating the Package Specification:
We define a package named EMP_DESIGNATION. Think of a package as a container for logically grouped procedures, functions, and other PL/SQL constructs.
Inside this package, we declare a procedure called EMP_DETAILS. This procedure will take two parameters:

• design which represents the employee's designation (job title).
• incentive which is a numeric value to be added to the employee's salary.

Step 3: Creating the Package Body:
We then create the body of the package, which contains the actual implementation of the EMP_DETAILS procedure.
In the procedure:

• We specify that we want to update the Employee table.
• For employees with a particular designation (as passed in the design parameter), we add the incentive amount to their current salary.
• After the update operation, we use dbms_output.put_line to display the number of employees whose salaries were updated. This uses SQL%ROWCOUNT to get the count of rows affected by the UPDATE statement.

Recap:
The package (EMP_DESIGNATION) is like a folder that groups related procedures.
The procedure (EMP_DETAILS) inside the package updates the salary of employees with a specific designation.
The parameters passed to the procedure are:

• design: The specific job title of employees whose salary you want to update.
• incentive: The amount to be added to each of these employees' salaries.

After the update, the procedure outputs how many employees were updated.

This setup allows for organized, reusable, and easy-to-maintain code for managing employee designations and salary updates in the database.