In the realm of relational databases, Oracle stands out not only for the robustness of its management systems but also for the advanced tools it provides developers to optimize, automate, and secure data operations. Among these tools is PL/SQL (Procedural Language/Structured Query Language), a powerful programming language designed to seamlessly integrate with SQL, the standard query language for relational databases.
PL/SQL extends the capabilities of SQL by adding procedural features such as loops, conditions, and exceptions, allowing developers to create scripts for database management.
This language, essential for any developer working with Oracle, aims to combine database management commands with a procedural programming language. It provides more comprehensive programming solutions for creating critical applications running on the Oracle database.
Why learn PL/SQL?
PL/SQL is a crucial programming language for developers using databases. It presents numerous advantages:
- Integration with Oracle Database: PL/SQL is designed to work optimally with Oracle Database, allowing for smooth and efficient interactions with stored data.
- Integration with SQL: PL/SQL is an extension of the SQL language that enables writing complex SQL queries, managing transactions, and manipulating data directly within the same programming environment.
- Ease of use: If you are already familiar with SQL, you will find PL/SQL syntax quite intuitive. The transition from SQL to PL/SQL is natural for those with SQL experience, making it easier to learn this language.
- Enhanced security: PL/SQL includes robust security features, controlling data access. You can protect sensitive data by limiting direct access to tables and creating additional layers of security.
- Automation of repetitive tasks: PL/SQL includes features (procedures, functions, triggers) that automate database management processes. This reduces the need for manually executing repetitive tasks and minimizes human errors.
- Portability: PL/SQL code blocks can be moved between different Oracle databases without modification.
The various applications of PL/SQL
PL/SQL offers multiple applications in database management. Thanks to its flexibility and power, it is used for various use cases such as:
- Application development: PL/SQL is used for developing web applications based on Oracle databases.
- Automation of administrative tasks: PL/SQL enables the automation of routine database administration tasks, thereby improving the efficiency and reliability of processes.
- Database security: PL/SQL helps protect the integrity of the database.
- Analysis and reporting: PL/SQL is used to generate customized reports that meet specific data management needs. PL/SQL also allows creating data for dashboards and visualization tools.
The basics of PL/SQL syntax
- Declarative section (DECLARE): This section is used to declare variables, constants, cursors, and other PL/SQL objects. It is optional.
- Executable section (BEGIN): This section contains the instructions that will be executed. It is mandatory.
- Exception section (EXCEPTION): This section is used to handle errors that may occur during the execution of instructions in the BEGIN section. It is optional.
1. Variables and data types:
Variables in PL/SQL must be declared in the DECLARE section. The data types used for variables are similar to those in SQL, with extensions specific to PL/SQL.
VARCHAR2: Used for character strings.
NUMBER: Used for numbers.
DATE: Used for dates and times.
Example:
DECLARE
v_employee_name VARCHAR2(50);
v_salary NUMBER(10, 2);
v_hire_date DATE;
>BEGIN
v_employee_name := ‘Jane Doe’;
v_salary := 60000;
v_hire_date := SYSDATE;
DBMS_OUTPUT.PUT_LINE(‘Employee: ‘ || v_employee_name || ‘hired on: ‘ || v_hire_date);
END;
2. Conditional structures:
Conditional structures allow testing conditions and executing code blocks based on the results of these tests.
The IF-THEN-ELSE conditional structure allows executing statements if a condition is true, and optionally other statements if it is false.
Example:
BEGIN
IF v_salary > 50000 THEN
DBMS_OUTPUT.PUT_LINE(‘High salary’);
ELSIF v_salary > 30000 THEN
DBMS_OUTPUT.PUT_LINE(‘Medium salary’);
ELSE
DBMS_OUTPUT.PUT_LINE(‘Low salary’);
END IF;
END;
3. Loops:
PL/SQL supports several types of loops, allowing repeated execution of instructions.
FOR loop: Executes a block of instructions a defined number of times.
Example:
BEGIN
FOR i IN 1..10 LOOP
DBMS_OUTPUT.PUT_LINE(‘Iteration: ‘ || i);
END LOOP;
END;
WHILE loop: Executes a block of instructions as long as a condition is true.
Example:
DECLARE
v_counter NUMBER := 1;
BEGIN
WHILE v_counter <= 10 LOOP
DBMS_OUTPUT.PUT_LINE(‘Counter: ‘ || v_counter);
v_counter := v_counter + 1;
END LOOP;
END;
LOOP: Executes a block of instructions indefinitely until an exit condition is met.
Example:
DECLARE
v_counter NUMBER := 1;
BEGIN
LOOP
EXIT WHEN v_counter > 10;
DBMS_OUTPUT.PUT_LINE(‘Counter: ‘ || v_counter);
v_counter := v_counter + 1;
END LOOP;
END;
4. Procedures:
Stored procedures are PL/SQL subprograms that can be reused to perform specific tasks.
Here is an example of a procedure to increase employees’ salaries based on their performance:
CREATE OR REPLACE PROCEDURE adjust_salary (emp_id NUMBER, increment NUMBER) AS BEGIN
UPDATE employees
SET salary = salary + increment
WHERE employee_id = emp_id; COMMIT;
END;
This procedure takes an employee ID and an increment amount as input, then updates the specified employee’s salary.
To call this procedure, simply do:
BEGIN
adjust_salary(1001, 5000);
END;
5. Functions:
Functions are PL/SQL subprograms that can return a single value.
Here is an example of a function that calculates the average salary of employees:
CREATE OR REPLACE FUNCTION calculate_average_salary RETURN NUMBER AS avg_salary NUMBER;
BEGIN
SELECT AVG(salary) INTO avg_salary FROM employees;
RETURN avg_salary;
END;
This function calculates the average salary of employees and returns the result.
6. Exception handling:
Exception handling is an essential part of PL/SQL, allowing errors to be managed elegantly and ensuring application stability.
Exceptions are handled in the EXCEPTION section of a PL/SQL block. There are predefined exceptions such as NO_DATA_FOUND and TOO_MANY_ROWS, as well as the possibility to define custom exceptions.
Example:
BEGIN
SELECT name INTO v_employee_name FROM employees WHERE employee_id = 9999;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE(‘No data found for the specified employee ID.’);
WHEN TOO_MANY_ROWS THEN
DBMS_OUTPUT.PUT_LINE(‘Multiple rows found for the specified employee ID.’);
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(‘An unexpected error occurred: ‘ || SQLERRM);
END;
7. Triggers:
Triggers are PL/SQL blocks that execute automatically in response to specific events on a table, such as insertion, update, or deletion of rows.
Here is an example of a trigger that records modification history on a critical table:
CREATE OR REPLACE TRIGGER audit_trigger
AFTER INSERT OR UPDATE OR DELETE ON employees
FOR EACH ROW
BEGIN
IF INSERTING THEN
INSERT INTO audit_log (action, employee_id, action_time)
VALUES (‘INSERT’, :NEW.employee_id, SYSDATE);
ELSIF UPDATING THEN
INSERT INTO audit_log (action, employee_id, action_time)
VALUES (‘UPDATE’, :NEW.employee_id, SYSDATE);
ELSIF DELETING THEN
INSERT INTO audit_log (action, employee_id, action_time)
VALUES (‘DELETE’, :OLD.employee_id, SYSDATE);
END IF;
END;
This trigger tracks all changes made to the `employees` table by recording the actions in an `audit_log` table.
Conclusion
PL/SQL is a powerful language that extends the capabilities of SQL with procedural features. It offers significant advantages in terms of performance, error management, security, and portability. With its control structures, stored procedures, functions, and triggers, PL/SQL facilitates the creation of robust and performant applications. By understanding and applying these concepts, developers can fully leverage the features offered by Oracle Database to develop effective and reliable solutions.
To learn more about the SQL language and train for careers in Data, join DataScientest.
