In this section, we will walk through the process of building a simple PL/SQL application. This will help you understand how to apply the concepts you've learned in a real-world scenario. We will create a basic employee management system that allows you to add, update, delete, and retrieve employee records.
Objectives
- Understand the structure of a simple PL/SQL application.
- Learn how to create and manage database objects such as tables and sequences.
- Implement CRUD (Create, Read, Update, Delete) operations using PL/SQL.
Step-by-Step Guide
- Setting Up the Database
1.1 Create the Employee Table
First, we need to create a table to store employee information.
CREATE TABLE employees (
employee_id NUMBER GENERATED BY DEFAULT AS IDENTITY,
first_name VARCHAR2(50),
last_name VARCHAR2(50),
email VARCHAR2(100),
hire_date DATE,
job_id VARCHAR2(10),
salary NUMBER(8, 2),
PRIMARY KEY (employee_id)
);Explanation:
employee_id: A unique identifier for each employee.first_nameandlast_name: The employee's first and last names.email: The employee's email address.hire_date: The date the employee was hired.job_id: The job identifier.salary: The employee's salary.
1.2 Create a Sequence for Employee IDs
Although we used an identity column for employee_id, let's also see how to create a sequence, which is another common method for generating unique IDs.
- Writing PL/SQL Procedures
2.1 Procedure to Add an Employee
We will create a procedure to add a new employee to the employees table.
CREATE OR REPLACE PROCEDURE add_employee (
p_first_name IN employees.first_name%TYPE,
p_last_name IN employees.last_name%TYPE,
p_email IN employees.email%TYPE,
p_hire_date IN employees.hire_date%TYPE,
p_job_id IN employees.job_id%TYPE,
p_salary IN employees.salary%TYPE
) IS
BEGIN
INSERT INTO employees (first_name, last_name, email, hire_date, job_id, salary)
VALUES (p_first_name, p_last_name, p_email, p_hire_date, p_job_id, p_salary);
COMMIT;
END;
/Explanation:
- The procedure
add_employeetakes several parameters corresponding to the employee's details. - The
INSERT INTOstatement adds a new record to theemployeestable. COMMITensures that the changes are saved to the database.
2.2 Procedure to Update an Employee
Next, we will create a procedure to update an existing employee's information.
CREATE OR REPLACE PROCEDURE update_employee (
p_employee_id IN employees.employee_id%TYPE,
p_first_name IN employees.first_name%TYPE,
p_last_name IN employees.last_name%TYPE,
p_email IN employees.email%TYPE,
p_hire_date IN employees.hire_date%TYPE,
p_job_id IN employees.job_id%TYPE,
p_salary IN employees.salary%TYPE
) IS
BEGIN
UPDATE employees
SET first_name = p_first_name,
last_name = p_last_name,
email = p_email,
hire_date = p_hire_date,
job_id = p_job_id,
salary = p_salary
WHERE employee_id = p_employee_id;
COMMIT;
END;
/Explanation:
- The procedure
update_employeetakes the employee ID and other details as parameters. - The
UPDATEstatement modifies the existing record in theemployeestable. COMMITensures that the changes are saved to the database.
2.3 Procedure to Delete an Employee
We will create a procedure to delete an employee from the employees table.
CREATE OR REPLACE PROCEDURE delete_employee (
p_employee_id IN employees.employee_id%TYPE
) IS
BEGIN
DELETE FROM employees
WHERE employee_id = p_employee_id;
COMMIT;
END;
/Explanation:
- The procedure
delete_employeetakes the employee ID as a parameter. - The
DELETE FROMstatement removes the record from theemployeestable. COMMITensures that the changes are saved to the database.
2.4 Procedure to Retrieve Employee Information
Finally, we will create a procedure to retrieve an employee's information.
CREATE OR REPLACE PROCEDURE get_employee (
p_employee_id IN employees.employee_id%TYPE,
p_first_name OUT employees.first_name%TYPE,
p_last_name OUT employees.last_name%TYPE,
p_email OUT employees.email%TYPE,
p_hire_date OUT employees.hire_date%TYPE,
p_job_id OUT employees.job_id%TYPE,
p_salary OUT employees.salary%TYPE
) IS
BEGIN
SELECT first_name, last_name, email, hire_date, job_id, salary
INTO p_first_name, p_last_name, p_email, p_hire_date, p_job_id, p_salary
FROM employees
WHERE employee_id = p_employee_id;
END;
/Explanation:
- The procedure
get_employeetakes the employee ID as an input parameter and returns the employee's details as output parameters. - The
SELECT INTOstatement retrieves the employee's information from theemployeestable.
- Testing the Application
3.1 Adding an Employee
BEGIN
add_employee('John', 'Doe', '[email protected]', SYSDATE, 'DEV', 60000);
END;
/3.2 Updating an Employee
BEGIN
update_employee(1, 'John', 'Doe', '[email protected]', SYSDATE, 'DEV', 65000);
END;
/3.3 Deleting an Employee
3.4 Retrieving Employee Information
DECLARE
v_first_name employees.first_name%TYPE;
v_last_name employees.last_name%TYPE;
v_email employees.email%TYPE;
v_hire_date employees.hire_date%TYPE;
v_job_id employees.job_id%TYPE;
v_salary employees.salary%TYPE;
BEGIN
get_employee(1, v_first_name, v_last_name, v_email, v_hire_date, v_job_id, v_salary);
DBMS_OUTPUT.PUT_LINE('First Name: ' || v_first_name);
DBMS_OUTPUT.PUT_LINE('Last Name: ' || v_last_name);
DBMS_OUTPUT.PUT_LINE('Email: ' || v_email);
DBMS_OUTPUT.PUT_LINE('Hire Date: ' || v_hire_date);
DBMS_OUTPUT.PUT_LINE('Job ID: ' || v_job_id);
DBMS_OUTPUT.PUT_LINE('Salary: ' || v_salary);
END;
/Conclusion
In this section, we built a simple PL/SQL application for managing employee records. We covered how to create database objects, write PL/SQL procedures for CRUD operations, and test the application. This exercise should give you a solid foundation for developing more complex PL/SQL applications in the future.