Introduction to PL/SQL
Key Concepts
1. PL/SQL Overview
PL/SQL (Procedural Language/Structured Query Language) is Oracle's extension to SQL. It allows for the creation of stored procedures, functions, triggers, and packages, enabling more complex and efficient database operations.
Example:
PL/SQL can be used to create a stored procedure that automatically updates a table when a new record is inserted.
2. Blocks
PL/SQL programs are structured into blocks. Each block consists of three parts: the declaration section, the executable section, and the exception-handling section.
Example:
A simple PL/SQL block:
DECLARE
v_name VARCHAR2(50);
BEGIN
SELECT name INTO v_name FROM employees WHERE employee_id = 101;
DBMS_OUTPUT.PUT_LINE('Employee Name: ' || v_name);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('No employee found with ID 101');
END;
3. Variables and Data Types
PL/SQL supports a variety of data types, including scalar types (such as NUMBER, VARCHAR2, DATE) and composite types (such as RECORD and TABLE). Variables can be declared in the declaration section of a block.
Example:
Declaring and using variables:
DECLARE
v_salary NUMBER(8,2);
v_hire_date DATE;
BEGIN
SELECT salary, hire_date INTO v_salary, v_hire_date FROM employees WHERE employee_id = 101;
DBMS_OUTPUT.PUT_LINE('Salary: ' || v_salary || ', Hire Date: ' || v_hire_date);
END;
4. Control Structures
PL/SQL supports control structures such as IF-THEN-ELSE, CASE, LOOP, WHILE, and FOR. These structures allow for conditional execution and iteration.
Example:
Using an IF-THEN-ELSE statement:
DECLARE
v_salary NUMBER(8,2);
BEGIN
SELECT salary INTO v_salary FROM employees WHERE employee_id = 101;
IF v_salary > 5000 THEN
DBMS_OUTPUT.PUT_LINE('High Salary');
ELSE
DBMS_OUTPUT.PUT_LINE('Low Salary');
END IF;
END;
5. Procedures and Functions
Procedures and functions are named PL/SQL blocks that can be called with parameters. Procedures perform actions, while functions return a value.
Example:
Creating and calling a procedure:
CREATE OR REPLACE PROCEDURE increase_salary (emp_id IN NUMBER, inc_amount IN NUMBER) IS
BEGIN
UPDATE employees SET salary = salary + inc_amount WHERE employee_id = emp_id;
END;
BEGIN
increase_salary(101, 1000);
END;
6. Triggers
Triggers are PL/SQL blocks that are automatically executed in response to specific events, such as INSERT, UPDATE, or DELETE operations on a table.
Example:
Creating a trigger:
CREATE OR REPLACE TRIGGER check_salary_before_insert
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
IF :NEW.salary < 1000 THEN
RAISE_APPLICATION_ERROR(-20001, 'Salary too low');
END IF;
END;
7. Cursors
Cursors are used to handle the result sets of SQL queries. They allow you to fetch and process rows one at a time.
Example:
Using a cursor to process rows:
DECLARE
CURSOR emp_cur IS SELECT * FROM employees;
emp_rec emp_cur%ROWTYPE;
BEGIN
OPEN emp_cur;
LOOP
FETCH emp_cur INTO emp_rec;
EXIT WHEN emp_cur%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('Employee ID: ' || emp_rec.employee_id || ', Name: ' || emp_rec.name);
END LOOP;
CLOSE emp_cur;
END;
8. Exception Handling
Exception handling in PL/SQL allows you to manage errors and exceptions that occur during the execution of a block. You can define your own exceptions or use predefined ones.
Example:
Handling exceptions:
DECLARE
v_name VARCHAR2(50);
BEGIN
SELECT name INTO v_name FROM employees WHERE employee_id = 999;
DBMS_OUTPUT.PUT_LINE('Employee Name: ' || v_name);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('No employee found with ID 999');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('An error occurred');
END;
9. Packages
Packages are PL/SQL constructs that group related procedures, functions, variables, and cursors into a single object. They provide modularity and encapsulation.
Example:
Creating a package:
CREATE OR REPLACE PACKAGE emp_pkg IS
PROCEDURE increase_salary (emp_id IN NUMBER, inc_amount IN NUMBER);
FUNCTION get_employee_name (emp_id IN NUMBER) RETURN VARCHAR2;
END emp_pkg;
CREATE OR REPLACE PACKAGE BODY emp_pkg IS
PROCEDURE increase_salary (emp_id IN NUMBER, inc_amount IN NUMBER) IS
BEGIN
UPDATE employees SET salary = salary + inc_amount WHERE employee_id = emp_id;
END increase_salary;
FUNCTION get_employee_name (emp_id IN NUMBER) RETURN VARCHAR2 IS
v_name VARCHAR2(50);
BEGIN
SELECT name INTO v_name FROM employees WHERE employee_id = emp_id;
RETURN v_name;
END get_employee_name;
END emp_pkg;
10. Collections
Collections in PL/SQL are similar to arrays in other programming languages. They allow you to store and manipulate groups of related data items.
Example:
Using a collection (VARRAY):
DECLARE
TYPE name_array IS VARRAY(5) OF VARCHAR2(50);
names name_array := name_array('Alice', 'Bob', 'Charlie', 'David', 'Eve');
BEGIN
FOR i IN 1..names.COUNT LOOP
DBMS_OUTPUT.PUT_LINE('Name ' || i || ': ' || names(i));
END LOOP;
END;
11. Records
Records are composite data types that allow you to group related data items into a single variable. They are similar to structures in other programming languages.
Example:
Using a record:
DECLARE
TYPE emp_rec IS RECORD (
emp_id NUMBER,
name VARCHAR2(50),
salary NUMBER(8,2)
);
emp emp_rec;
BEGIN
SELECT employee_id, name, salary INTO emp FROM employees WHERE employee_id = 101;
DBMS_OUTPUT.PUT_LINE('Employee ID: ' || emp.emp_id || ', Name: ' || emp.name || ', Salary: ' || emp.salary);
END;
12. Dynamic SQL
Dynamic SQL allows you to construct and execute SQL statements at runtime. This is useful when the SQL statement is not known until runtime.
Example:
Using dynamic SQL:
DECLARE
sql_stmt VARCHAR2(100);
emp_name VARCHAR2(50);
BEGIN
sql_stmt := 'SELECT name FROM employees WHERE employee_id = :id';
EXECUTE IMMEDIATE sql_stmt INTO emp_name USING 101;
DBMS_OUTPUT.PUT_LINE('Employee Name: ' || emp_name);
END;
13. Bulk Operations
Bulk operations allow you to process multiple rows of data in a single operation, improving performance by reducing context switches between PL/SQL and SQL engines.
Example:
Using BULK COLLECT:
DECLARE
TYPE emp_id_table IS TABLE OF employees.employee_id%TYPE;
emp_ids emp_id_table;
BEGIN
SELECT employee_id BULK COLLECT INTO emp_ids FROM employees WHERE department_id = 10;
FOR i IN 1..emp_ids.COUNT LOOP
DBMS_OUTPUT.PUT_LINE('Employee ID: ' || emp_ids(i));
END LOOP;
END;
14. User-Defined Exceptions
User-defined exceptions allow you to create custom error conditions that can be raised and handled within your PL/SQL code.
Example:
Using a user-defined exception:
DECLARE
invalid_salary EXCEPTION;
v_salary NUMBER(8,2);
BEGIN
SELECT salary INTO v_salary FROM employees WHERE employee_id = 101;
IF v_salary < 1000 THEN
RAISE invalid_salary;
END IF;
EXCEPTION
WHEN invalid_salary THEN
DBMS_OUTPUT.PUT_LINE('Invalid Salary');
END;
15. PL/SQL Debugging
Debugging PL/SQL code involves using tools and techniques to identify and fix errors. Oracle provides various debugging tools, such as SQL*Plus and Oracle SQL Developer.
Example:
Using SQL*Plus to debug:
SQL> SET SERVEROUTPUT ON
SQL> DECLARE
v_name VARCHAR2(50);
BEGIN
SELECT name INTO v_name FROM employees WHERE employee_id = 101;
DBMS_OUTPUT.PUT_LINE('Employee Name: ' || v_name);
END;
/
16. PL/SQL Performance Tuning
Performance tuning in PL/SQL involves optimizing code to reduce execution time and resource usage. Techniques include minimizing context switches, using bulk operations, and optimizing SQL statements.
Example:
Optimizing a loop with bulk operations:
DECLARE
TYPE emp_id_table IS TABLE OF employees.employee_id%TYPE;
emp_ids emp_id_table;
BEGIN
SELECT employee_id BULK COLLECT INTO emp_ids FROM employees WHERE department_id = 10;
FORALL i IN 1..emp_ids.COUNT
UPDATE employees SET salary = salary + 1000 WHERE employee_id = emp_ids(i);
END;
17. PL/SQL Security
Security in PL/SQL involves controlling access to database objects and ensuring that sensitive data is protected. This includes using roles, privileges, and encryption.
Example:
Granting and revoking privileges:
SQL> GRANT EXECUTE ON emp_pkg TO hr_user;
SQL> REVOKE EXECUTE ON emp_pkg FROM hr_user;
18. PL/SQL Best Practices
Best practices in PL/SQL include writing modular and reusable code, using meaningful variable names, handling exceptions, and documenting code. These practices improve code maintainability and reliability.
Example:
Best practices checklist:
- Use packages for modularity
- Handle exceptions properly
- Use meaningful variable names
- Document code for clarity