PL/SQL Basics
Key Concepts
1. PL/SQL Block Structure
A PL/SQL block is the basic unit of a PL/SQL program. It consists of three sections: DECLARE, BEGIN, and EXCEPTION.
Example:
DECLARE
v_name VARCHAR2(50);
BEGIN
SELECT name INTO v_name FROM employees WHERE id = 1;
DBMS_OUTPUT.PUT_LINE(v_name);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('No employee found');
END;
2. Variables and Data Types
Variables are used to store data temporarily. PL/SQL supports various data types like VARCHAR2, NUMBER, DATE, etc.
Example:
DECLARE
v_name VARCHAR2(50);
v_salary NUMBER(10, 2);
v_hire_date DATE;
BEGIN
-- Variable assignments
END;
3. Control Structures
Control structures like IF-THEN-ELSE, LOOP, and CASE are used to control the flow of execution in PL/SQL.
Example:
DECLARE
v_grade CHAR(1);
BEGIN
v_grade := 'B';
IF v_grade = 'A' THEN
DBMS_OUTPUT.PUT_LINE('Excellent');
ELSIF v_grade = 'B' THEN
DBMS_OUTPUT.PUT_LINE('Good');
ELSE
DBMS_OUTPUT.PUT_LINE('Needs improvement');
END IF;
END;
4. Loops
Loops are used to repeat a block of code. PL/SQL supports FOR, WHILE, and LOOP constructs.
Example:
BEGIN
FOR i IN 1..5 LOOP
DBMS_OUTPUT.PUT_LINE('Iteration: ' || i);
END LOOP;
END;
5. Cursors
Cursors are used to handle result sets from SQL queries. They allow you to fetch rows one at a time.
Example:
DECLARE
CURSOR c_employees IS SELECT id, name FROM employees;
v_id employees.id%TYPE;
v_name employees.name%TYPE;
BEGIN
OPEN c_employees;
LOOP
FETCH c_employees INTO v_id, v_name;
EXIT WHEN c_employees%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('ID: ' || v_id || ', Name: ' || v_name);
END LOOP;
CLOSE c_employees;
END;
6. Procedures
Procedures are named PL/SQL blocks that perform a specific task. They can be called from other PL/SQL blocks.
Example:
CREATE OR REPLACE PROCEDURE greet(p_name IN VARCHAR2) IS
BEGIN
DBMS_OUTPUT.PUT_LINE('Hello, ' || p_name);
END;
/
BEGIN
greet('John');
END;
7. Functions
Functions are similar to procedures but return a value. They are used to perform calculations and return results.
Example:
CREATE OR REPLACE FUNCTION get_salary(p_id IN NUMBER) RETURN NUMBER IS
v_salary employees.salary%TYPE;
BEGIN
SELECT salary INTO v_salary FROM employees WHERE id = p_id;
RETURN v_salary;
END;
/
BEGIN
DBMS_OUTPUT.PUT_LINE('Salary: ' || get_salary(1));
END;
8. Packages
Packages are collections of related procedures, functions, variables, and cursors. They provide modularity and encapsulation.
Example:
CREATE OR REPLACE PACKAGE employee_pkg IS
PROCEDURE add_employee(p_name IN VARCHAR2, p_salary IN NUMBER);
FUNCTION get_employee_count RETURN NUMBER;
END employee_pkg;
/
CREATE OR REPLACE PACKAGE BODY employee_pkg IS
PROCEDURE add_employee(p_name IN VARCHAR2, p_salary IN NUMBER) IS
BEGIN
-- Add employee logic
END add_employee;
FUNCTION get_employee_count RETURN NUMBER IS
v_count NUMBER;
BEGIN
SELECT COUNT(*) INTO v_count FROM employees;
RETURN v_count;
END get_employee_count;
END employee_pkg;
/
9. Exceptions
Exceptions are errors that occur during the execution of a PL/SQL block. They can be handled using the EXCEPTION section.
Example:
DECLARE
v_name VARCHAR2(50);
BEGIN
SELECT name INTO v_name FROM employees WHERE id = 100;
DBMS_OUTPUT.PUT_LINE(v_name);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('No employee found');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('An error occurred');
END;
10. Records
Records are composite data structures that can hold multiple related fields. They are similar to structures in other programming languages.
Example:
DECLARE
TYPE t_employee IS RECORD (id NUMBER, name VARCHAR2(50), salary NUMBER);
v_employee t_employee;
BEGIN
v_employee.id := 1;
v_employee.name := 'John Doe';
v_employee.salary := 5000;
DBMS_OUTPUT.PUT_LINE('ID: ' || v_employee.id || ', Name: ' || v_employee.name || ', Salary: ' || v_employee.salary);
END;
11. Collections
Collections are ordered groups of elements, all of the same type. PL/SQL supports three types of collections: VARRAY, nested tables, and associative arrays.
Example:
DECLARE
TYPE t_names IS TABLE OF VARCHAR2(50);
v_names t_names := t_names('John', 'Jane', 'Jim');
BEGIN
FOR i IN 1..v_names.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(v_names(i));
END LOOP;
END;
12. Triggers
Triggers are PL/SQL blocks that are automatically executed in response to certain events, such as DML operations on a table.
Example:
CREATE OR REPLACE TRIGGER before_insert_employee
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
IF :NEW.salary < 0 THEN
:NEW.salary := 0;
END IF;
END;
/
13. Dynamic SQL
Dynamic SQL allows you to construct and execute SQL statements dynamically at runtime.
Example:
DECLARE
v_sql VARCHAR2(100);
BEGIN
v_sql := 'SELECT COUNT(*) FROM employees';
EXECUTE IMMEDIATE v_sql INTO v_count;
DBMS_OUTPUT.PUT_LINE('Employee count: ' || v_count);
END;
14. Bulk Operations
Bulk operations allow you to process multiple rows at once, improving performance by reducing context switches.
Example:
DECLARE
TYPE t_ids IS TABLE OF employees.id%TYPE;
v_ids t_ids;
BEGIN
SELECT id BULK COLLECT INTO v_ids FROM employees;
FOR i IN 1..v_ids.COUNT LOOP
DBMS_OUTPUT.PUT_LINE('ID: ' || v_ids(i));
END LOOP;
END;
15. User-Defined Exceptions
User-defined exceptions allow you to create custom error conditions and handle them in your PL/SQL code.
Example:
DECLARE
e_invalid_salary EXCEPTION;
v_salary NUMBER := -1000;
BEGIN
IF v_salary < 0 THEN
RAISE e_invalid_salary;
END IF;
EXCEPTION
WHEN e_invalid_salary THEN
DBMS_OUTPUT.PUT_LINE('Invalid salary');
END;
16. Pipelined Functions
Pipelined functions return a set of values one at a time, allowing you to process large datasets efficiently.
Example:
CREATE OR REPLACE FUNCTION get_employees RETURN SYS.ODCINUMBERLIST PIPELINED IS
BEGIN
FOR rec IN (SELECT id FROM employees) LOOP
PIPE ROW(rec.id);
END LOOP;
RETURN;
END;
/
SELECT * FROM TABLE(get_employees);
17. Object-Oriented Features
PL/SQL supports object-oriented programming (OOP) features like objects, methods, and inheritance.
Example:
CREATE OR REPLACE TYPE t_employee AS OBJECT (
id NUMBER,
name VARCHAR2(50),
MEMBER FUNCTION get_details RETURN VARCHAR2
);
/
CREATE OR REPLACE TYPE BODY t_employee AS
MEMBER FUNCTION get_details RETURN VARCHAR2 IS
BEGIN
RETURN 'ID: ' || id || ', Name: ' || name;
END;
END;
/
© 2024 Ahmed Baheeg Khorshid. All rights reserved.