PL/SQL Fundamentals

PL/SQL (Procedural Language/SQL) is Oracle's powerful procedural extension to SQL. It combines the data manipulation power of SQL with the processing power of procedural languages, allowing you to write complex business logic that runs directly in the database.

---

Why PL/SQL?

• Performance — reduces network round trips by processing data in the database
• Portability — runs on any platform that supports Oracle Database
• Integration — seamlessly combines SQL and procedural logic
• Security — encapsulate business logic and grant access via procedures
• Error handling — robust exception handling framework

---

Block Structure

Every PL/SQL program is built from blocks:

DECLARE
  -- Variable declarations (optional)
  v_name VARCHAR2(50);
  v_salary NUMBER(10, 2);
BEGIN
  -- Executable statements (required)
  SELECT first_name, salary
  INTO v_name, v_salary
  FROM employees
  WHERE employee_id = 100;

  DBMS_OUTPUT.PUT_LINE(v_name || ' earns ' || v_salary);
EXCEPTION
  -- Error handling (optional)
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('Employee not found');
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
END;
/

Section	Purpose	Required?
DECLARE	Declare variables, constants, cursors	Optional
BEGIN...END	Executable code	Required
EXCEPTION	Error handling	Optional

---