Skip to content

Oracle_Comprehensive_Notes.md

Latest commit

 

History

History
1172 lines (968 loc) · 35.8 KB

Oracle_Comprehensive_Notes.md

File metadata and controls

1172 lines (968 loc) · 35.8 KB
Raw

Oracle Database — Comprehensive Notes (Basic to Advanced)

Table of Contents

  1. Introduction to Oracle Database
  2. Oracle Data Types
  3. Database Objects & Connecting to Oracle
  4. DDL — Creating & Altering Objects
  5. DML — Manipulating Data
  6. Constraints
  7. Operators & the WHERE Clause
  8. Sorting & Row Limiting (ROWNUM, FETCH, Top-N)
  9. Aggregate Functions & Grouping
  10. Joins
  11. Subqueries
  12. Set Operators (UNION, INTERSECT, MINUS)
  13. Views & Materialized Views
  14. Sequences & Synonyms
  15. Indexes
  16. String, Numeric & Date Functions
  17. Introduction to PL/SQL
  18. Cursors
  19. Stored Procedures & Functions
  20. Packages
  21. Triggers
  22. Exception Handling
  23. Transactions & Locking
  24. User Management & Privileges
  25. Advanced: Analytic Functions, Hierarchical Queries, Collections
  26. Performance: EXPLAIN PLAN & Optimization

1. Introduction to Oracle Database

Oracle Database is a commercial, enterprise-grade Relational Database Management System (RDBMS) developed by Oracle Corporation, first released in 1979 (making it one of the earliest commercial SQL-based RDBMS products). It is widely used in large enterprises, banking, telecom, and government systems due to its scalability, reliability, and rich feature set.

Key characteristics of Oracle:

  • Strong support for PL/SQL (Procedural Language/SQL) — Oracle's procedural extension to SQL, enabling stored procedures, functions, packages, and triggers.
  • Robust multi-version concurrency control (MVCC) — readers never block writers and vice versa.
  • Advanced features: partitioning, materialized views, analytic (window) functions, Real Application Clusters (RAC) for high availability, Data Guard for disaster recovery.
  • Uses tablespaces to logically organize physical storage.
  • Available in editions: Express Edition (XE — free), Standard Edition, and Enterprise Edition.

Oracle vs MySQL — key differences to keep in mind:

Feature Oracle MySQL
Procedural language PL/SQL (very rich) Simpler stored procedure syntax
Row limiting ROWNUM / FETCH FIRST LIMIT
Sequence generation SEQUENCE objects AUTO_INCREMENT column attribute
String concatenation `
Empty string vs NULL Treats empty string as NULL Treats them as distinct
Set operators UNION, INTERSECT, MINUS Only UNION (INTERSECT/MINUS added in 8.0.31+)

Connecting to Oracle (SQL*Plus command line):

sqlplus username/password@database_alias
-- Example:
sqlplus hr/hr@orclpdb

SQL command categories (same as standard SQL):

Category Commands
DDL CREATE, ALTER, DROP, TRUNCATE, RENAME
DML INSERT, UPDATE, DELETE, MERGE
DQL SELECT
DCL GRANT, REVOKE
TCL COMMIT, ROLLBACK, SAVEPOINT

2. Oracle Data Types

Category Data Type Description
Character CHAR(n) Fixed-length string (max 2000 bytes)
VARCHAR2(n) Variable-length string (max 4000 bytes) — preferred over CHAR
CLOB Character Large Object — for large text data
NVARCHAR2 Unicode variable-length string
Numeric NUMBER(p,s) Universal numeric type — p = precision (total digits), s = scale (digits after decimal)
INTEGER Whole number (subtype of NUMBER)
FLOAT Floating-point number
Date/Time DATE Stores date and time (down to seconds); no separate "time-only" type
TIMESTAMP Date and time with fractional seconds precision
TIMESTAMP WITH TIME ZONE Includes timezone information
INTERVAL YEAR TO MONTH / INTERVAL DAY TO SECOND Represents a duration
Binary BLOB Binary Large Object — images, files
RAW(n) Small binary data
Other ROWID Unique physical address of a row
LONG Legacy large text type (deprecated — use CLOB)

Example:

CREATE TABLE products (
    product_id NUMBER(6),
    name VARCHAR2(100),
    price NUMBER(10,2),
    added_on DATE,
    description CLOB
);

Important note: Oracle has no native BOOLEAN type for table columns (BOOLEAN exists only within PL/SQL). Booleans in tables are commonly simulated with NUMBER(1) (0/1) or CHAR(1) ('Y'/'N').

3. Database Objects & Connecting to Oracle

Oracle organizes data using schemas (a schema is owned by a database user and contains that user's objects: tables, views, sequences, etc.) rather than separate "databases" the way MySQL does.

-- Viewing current user / schema
SELECT USER FROM DUAL;

-- DUAL is a special one-row, one-column dummy table used for evaluating expressions
SELECT SYSDATE FROM DUAL;
SELECT 5 * 3 FROM DUAL;

Viewing objects:

SELECT table_name FROM user_tables;         -- tables owned by current user
DESCRIBE employees;                          -- table structure (SQL*Plus/Developer)
SELECT * FROM user_indexes WHERE table_name = 'EMPLOYEES';

4. DDL — Creating & Altering Objects

4.1 Creating a Table

CREATE TABLE employees (
    emp_id       NUMBER(6)      PRIMARY KEY,
    first_name   VARCHAR2(50)   NOT NULL,
    last_name    VARCHAR2(50)   NOT NULL,
    email        VARCHAR2(100)  UNIQUE,
    salary       NUMBER(10,2)   DEFAULT 0,
    dept_id      NUMBER(4),
    hire_date    DATE           DEFAULT SYSDATE
);

4.2 Altering a Table

ALTER TABLE employees ADD phone VARCHAR2(15);                  -- add column
ALTER TABLE employees DROP COLUMN phone;                        -- drop column
ALTER TABLE employees MODIFY salary NUMBER(12,2);                -- change data type/size
ALTER TABLE employees RENAME COLUMN first_name TO fname;          -- rename column
RENAME employees TO staff;                                        -- rename table (own statement)

4.3 Dropping & Truncating

DROP TABLE employees;                 -- deletes structure + data (goes to "recycle bin" by default)
DROP TABLE employees PURGE;           -- deletes permanently, bypassing recycle bin
TRUNCATE TABLE employees;             -- removes all rows instantly, keeps structure; cannot be rolled back
FLASHBACK TABLE employees TO BEFORE DROP;   -- restores a dropped table from the recycle bin

5. DML — Manipulating Data

5.1 INSERT

INSERT INTO employees (emp_id, first_name, last_name, email, salary, dept_id, hire_date)
VALUES (101, 'Amit', 'Sharma', 'amit@company.com', 55000, 10, TO_DATE('2022-03-15','YYYY-MM-DD'));

-- Multi-row insert using INSERT ALL
INSERT ALL
    INTO employees (emp_id, first_name, salary) VALUES (102, 'Priya', 62000)
    INTO employees (emp_id, first_name, salary) VALUES (103, 'Rahul', 48000)
SELECT * FROM DUAL;

5.2 SELECT

SELECT * FROM employees;
SELECT first_name, salary FROM employees WHERE dept_id = 10;
SELECT DISTINCT dept_id FROM employees;

5.3 UPDATE

UPDATE employees
SET salary = salary * 1.10
WHERE dept_id = 10;

5.4 DELETE

DELETE FROM employees WHERE emp_id = 105;

5.5 MERGE (Upsert — Insert or Update in one statement)

Very commonly used in Oracle for synchronizing data between a source and a target table.

MERGE INTO employees tgt
USING new_hires src
ON (tgt.emp_id = src.emp_id)
WHEN MATCHED THEN
    UPDATE SET tgt.salary = src.salary, tgt.dept_id = src.dept_id
WHEN NOT MATCHED THEN
    INSERT (emp_id, first_name, last_name, salary, dept_id)
    VALUES (src.emp_id, src.first_name, src.last_name, src.salary, src.dept_id);

6. Constraints

Constraint Purpose
PRIMARY KEY Uniquely identifies each row
FOREIGN KEY Enforces referential integrity between tables
UNIQUE Ensures distinct values in a column
NOT NULL Disallows empty values
CHECK Restricts values to satisfy a condition
DEFAULT Supplies a default value

Example with named constraints (recommended in Oracle for clarity in error messages):

CREATE TABLE departments (
    dept_id    NUMBER(4)     CONSTRAINT dept_pk PRIMARY KEY,
    dept_name  VARCHAR2(50)  CONSTRAINT dept_name_nn NOT NULL
);

CREATE TABLE employees (
    emp_id    NUMBER(6)     CONSTRAINT emp_pk PRIMARY KEY,
    salary    NUMBER(10,2)  CONSTRAINT salary_chk CHECK (salary >= 0),
    dept_id   NUMBER(4),
    CONSTRAINT emp_dept_fk FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
        ON DELETE CASCADE
);

Adding/dropping constraints after creation:

ALTER TABLE employees ADD CONSTRAINT email_uk UNIQUE (email);
ALTER TABLE employees DROP CONSTRAINT salary_chk;
ALTER TABLE employees DISABLE CONSTRAINT emp_dept_fk;    -- temporarily disable
ALTER TABLE employees ENABLE CONSTRAINT emp_dept_fk;     -- re-enable

7. Operators & the WHERE Clause

SELECT * FROM employees WHERE salary > 50000;
SELECT * FROM employees WHERE dept_id = 10 AND salary > 40000;
SELECT * FROM employees WHERE salary BETWEEN 40000 AND 60000;
SELECT * FROM employees WHERE dept_id IN (10, 20, 30);
SELECT * FROM employees WHERE first_name LIKE 'A%';       -- starts with 'A'
SELECT * FROM employees WHERE email IS NULL;
SELECT * FROM employees WHERE NOT (dept_id = 10);

Oracle-specific concatenation operator ||:

SELECT first_name || ' ' || last_name AS full_name FROM employees;

8. Sorting & Row Limiting (ROWNUM, FETCH, Top-N)

8.1 ORDER BY

SELECT * FROM employees ORDER BY salary DESC;
SELECT * FROM employees ORDER BY dept_id, salary DESC;

8.2 ROWNUM (traditional Oracle way)

ROWNUM is a pseudo-column assigned to rows as they are retrieved, before any ORDER BY is applied — so limiting top-N results correctly requires a subquery.

-- WRONG (ROWNUM is assigned before sorting, so this doesn't reliably give the "top 5")
SELECT * FROM employees WHERE ROWNUM <= 5 ORDER BY salary DESC;

-- CORRECT: sort first in a subquery, then apply ROWNUM outside
SELECT * FROM (
    SELECT * FROM employees ORDER BY salary DESC
)
WHERE ROWNUM <= 5;

8.3 FETCH FIRST / OFFSET (Oracle 12c+, ANSI standard, much simpler)

SELECT * FROM employees
ORDER BY salary DESC
FETCH FIRST 5 ROWS ONLY;

SELECT * FROM employees
ORDER BY salary DESC
OFFSET 5 ROWS FETCH NEXT 5 ROWS ONLY;   -- pagination: skip 5, take next 5

8.4 ROW_NUMBER() (analytic function alternative)

SELECT * FROM (
    SELECT e.*, ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn
    FROM employees e
)
WHERE rn <= 5;

9. Aggregate Functions & Grouping

SELECT COUNT(*) FROM employees;
SELECT AVG(salary), MAX(salary), MIN(salary), SUM(salary) FROM employees;

SELECT dept_id, COUNT(*) AS emp_count, AVG(salary) AS avg_salary
FROM employees
GROUP BY dept_id;

SELECT dept_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY dept_id
HAVING AVG(salary) > 55000;

GROUP BY with ROLLUP / CUBE (Oracle extensions for subtotals):

-- ROLLUP adds subtotal rows and a grand total
SELECT dept_id, job_id, SUM(salary)
FROM employees
GROUP BY ROLLUP(dept_id, job_id);

-- CUBE adds subtotals for every combination of grouped columns
SELECT dept_id, job_id, SUM(salary)
FROM employees
GROUP BY CUBE(dept_id, job_id);

10. Joins

Oracle supports standard ANSI join syntax (recommended) as well as its own older "Oracle join" syntax using (+) for outer joins (legacy, but still seen in older codebases).

10.1 ANSI Syntax (Recommended)

-- INNER JOIN
SELECT e.first_name, d.dept_name
FROM employees e
JOIN departments d ON e.dept_id = d.dept_id;

-- LEFT OUTER JOIN
SELECT e.first_name, d.dept_name
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.dept_id;

-- RIGHT OUTER JOIN
SELECT e.first_name, d.dept_name
FROM employees e
RIGHT JOIN departments d ON e.dept_id = d.dept_id;

-- FULL OUTER JOIN (natively supported in Oracle, unlike MySQL)
SELECT e.first_name, d.dept_name
FROM employees e
FULL OUTER JOIN departments d ON e.dept_id = d.dept_id;

10.2 Legacy Oracle Join Syntax (using (+))

The (+) symbol is placed on the side of the join that may have missing matches (i.e., the "optional" side).

-- Equivalent to a LEFT JOIN (all employees, matching departments if they exist)
SELECT e.first_name, d.dept_name
FROM employees e, departments d
WHERE e.dept_id = d.dept_id(+);

-- Equivalent to a RIGHT JOIN
SELECT e.first_name, d.dept_name
FROM employees e, departments d
WHERE e.dept_id(+) = d.dept_id;

Modern Oracle style strongly favors ANSI JOIN syntax — it's clearer, and (+) cannot express a FULL OUTER JOIN at all.

10.3 SELF JOIN

SELECT e.first_name AS employee, m.first_name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.emp_id;

10.4 CROSS JOIN

SELECT e.first_name, d.dept_name
FROM employees e
CROSS JOIN departments d;

11. Subqueries

-- Scalar subquery in WHERE
SELECT first_name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

-- Subquery with IN
SELECT first_name
FROM employees
WHERE dept_id IN (SELECT dept_id FROM departments WHERE dept_name = 'Sales');

-- Correlated subquery
SELECT first_name, salary, dept_id
FROM employees e1
WHERE salary > (
    SELECT AVG(salary) FROM employees e2 WHERE e2.dept_id = e1.dept_id
);

-- EXISTS
SELECT dept_name
FROM departments d
WHERE EXISTS (SELECT 1 FROM employees e WHERE e.dept_id = d.dept_id);

-- Subquery in FROM (inline view)
SELECT dept_id, avg_sal
FROM (SELECT dept_id, AVG(salary) AS avg_sal FROM employees GROUP BY dept_id)
WHERE avg_sal > 50000;

12. Set Operators (UNION, INTERSECT, MINUS)

Oracle supports the full set of relational set operators (unlike older MySQL versions):

Operator Returns
UNION Combined rows from both queries, duplicates removed
UNION ALL Combined rows, duplicates kept (faster)
INTERSECT Only rows that appear in both query results
MINUS Rows in the first query result that do not appear in the second 
SELECT first_name FROM employees WHERE dept_id = 10
UNION
SELECT first_name FROM employees WHERE dept_id = 20;

SELECT emp_id FROM employees
INTERSECT
SELECT emp_id FROM project_assignments;
-- Employees who are also assigned to at least one project

SELECT emp_id FROM employees
MINUS
SELECT emp_id FROM project_assignments;
-- Employees who are NOT assigned to any project

13. Views & Materialized Views

13.1 Views

A virtual table based on a stored query — always reflects live, current data.

CREATE VIEW high_earners AS
SELECT first_name, last_name, salary
FROM employees
WHERE salary > 60000;

SELECT * FROM high_earners;

CREATE OR REPLACE VIEW high_earners AS
SELECT first_name, last_name, salary, dept_id
FROM employees
WHERE salary > 65000;

DROP VIEW high_earners;

13.2 Materialized Views

Unlike a regular view, a materialized view physically stores the query's result set, and must be refreshed to reflect changes in the underlying tables — used heavily for reporting/data warehousing to avoid re-running expensive queries repeatedly.

CREATE MATERIALIZED VIEW dept_salary_summary
BUILD IMMEDIATE
REFRESH COMPLETE ON DEMAND
AS
SELECT dept_id, AVG(salary) AS avg_salary, COUNT(*) AS emp_count
FROM employees
GROUP BY dept_id;

-- Manually refreshing it later, after underlying data changes
EXEC DBMS_MVIEW.REFRESH('DEPT_SALARY_SUMMARY');

14. Sequences & Synonyms

14.1 Sequences

Since Oracle has no AUTO_INCREMENT column attribute (unlike MySQL), sequences are used to generate unique, sequential numbers — typically for primary keys.

CREATE SEQUENCE emp_seq
    START WITH 1
    INCREMENT BY 1
    NOCACHE
    NOCYCLE;

-- Using the sequence
INSERT INTO employees (emp_id, first_name, salary)
VALUES (emp_seq.NEXTVAL, 'Neha', 58000);

SELECT emp_seq.CURRVAL FROM DUAL;   -- current value (after at least one NEXTVAL call)

DROP SEQUENCE emp_seq;

In Oracle 12c+, columns can also use identity columns (GENERATED AS IDENTITY), which work similarly to MySQL's AUTO_INCREMENT under the hood using a sequence automatically:

CREATE TABLE customers (
    cust_id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    cust_name VARCHAR2(100)
);

14.2 Synonyms

An alternate name for a database object (table, view, sequence, procedure) — useful for simplifying references, especially across schemas.

CREATE SYNONYM emp AS employees;
SELECT * FROM emp;    -- same as SELECT * FROM employees

CREATE PUBLIC SYNONYM global_emp FOR hr.employees;   -- accessible to all users
DROP SYNONYM emp;

15. Indexes

CREATE INDEX idx_lastname ON employees(last_name);
CREATE UNIQUE INDEX idx_email ON employees(email);
CREATE INDEX idx_dept_salary ON employees(dept_id, salary);   -- composite index

-- Function-based index (indexes the result of an expression)
CREATE INDEX idx_upper_lastname ON employees(UPPER(last_name));

DROP INDEX idx_lastname;

Oracle-specific index types:

Type Best suited for
B-Tree (default) General-purpose, high-cardinality columns
Bitmap Low-cardinality columns (e.g., gender, status flags) — common in data warehouses
Function-based Indexing computed expressions (e.g., UPPER(col)) 
CREATE BITMAP INDEX idx_status ON employees(status);

16. String, Numeric & Date Functions

16.1 String Functions

SELECT first_name || ' ' || last_name AS full_name FROM employees;
SELECT UPPER(first_name), LOWER(last_name) FROM employees;
SELECT LENGTH(first_name) FROM employees;
SELECT SUBSTR(first_name, 1, 3) FROM employees;      -- first 3 characters
SELECT TRIM('  Hello  ') FROM DUAL;
SELECT REPLACE(email, '@old.com', '@new.com') FROM employees;
SELECT INSTR('Hello World', 'World') FROM DUAL;        -- position of substring: 7
SELECT LPAD(emp_id, 6, '0') FROM employees;             -- left-pad with zeros

16.2 Numeric Functions

SELECT ROUND(salary, 0) FROM employees;
SELECT CEIL(45.2) FROM DUAL;      -- 46
SELECT FLOOR(45.8) FROM DUAL;     -- 45
SELECT MOD(10, 3) FROM DUAL;      -- 1
SELECT TRUNC(45.789, 1) FROM DUAL;  -- 45.7 (truncates, doesn't round)

16.3 Date Functions

SELECT SYSDATE FROM DUAL;                                    -- current date & time
SELECT SYSTIMESTAMP FROM DUAL;                                -- current timestamp with fractional seconds
SELECT ADD_MONTHS(hire_date, 6) FROM employees;                -- add 6 months
SELECT MONTHS_BETWEEN(SYSDATE, hire_date) FROM employees;      -- difference in months
SELECT TO_CHAR(hire_date, 'DD-MON-YYYY') FROM employees;        -- formatted output
SELECT TO_DATE('2026-07-12', 'YYYY-MM-DD') FROM DUAL;           -- string to date
SELECT NEXT_DAY(SYSDATE, 'MONDAY') FROM DUAL;                    -- next occurring Monday
SELECT LAST_DAY(SYSDATE) FROM DUAL;                               -- last day of current month

17. Introduction to PL/SQL

PL/SQL (Procedural Language extensions to SQL) is Oracle's proprietary procedural language, combining SQL with programming constructs like variables, loops, conditionals, and exception handling — used for stored procedures, functions, packages, and triggers.

17.1 Basic Block Structure

DECLARE
    -- variable declarations (optional section)
    v_name VARCHAR2(50);
    v_salary NUMBER(10,2) := 50000;    -- := is the assignment operator in PL/SQL
BEGIN
    -- executable statements (mandatory section)
    v_name := 'Amit';
    DBMS_OUTPUT.PUT_LINE('Name: ' || v_name || ', Salary: ' || v_salary);
EXCEPTION
    -- exception handling (optional section)
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('An error occurred.');
END;
/

DBMS_OUTPUT.PUT_LINE prints output to the console (requires SET SERVEROUTPUT ON in SQL*Plus/Developer to display). The trailing / executes the PL/SQL block.

17.2 Variables & %TYPE

DECLARE
    v_emp_salary employees.salary%TYPE;   -- automatically matches the column's data type
BEGIN
    SELECT salary INTO v_emp_salary FROM employees WHERE emp_id = 101;
    DBMS_OUTPUT.PUT_LINE('Salary: ' || v_emp_salary);
END;
/

17.3 Conditional Statements

DECLARE
    v_salary NUMBER := 45000;
BEGIN
    IF v_salary > 50000 THEN
        DBMS_OUTPUT.PUT_LINE('High earner');
    ELSIF v_salary > 30000 THEN
        DBMS_OUTPUT.PUT_LINE('Mid-level earner');
    ELSE
        DBMS_OUTPUT.PUT_LINE('Entry-level earner');
    END IF;
END;
/

17.4 Loops

-- Basic LOOP
DECLARE
    i NUMBER := 1;
BEGIN
    LOOP
        DBMS_OUTPUT.PUT_LINE('i = ' || i);
        i := i + 1;
        EXIT WHEN i > 5;
    END LOOP;
END;
/

-- WHILE loop
DECLARE
    i NUMBER := 1;
BEGIN
    WHILE i <= 5 LOOP
        DBMS_OUTPUT.PUT_LINE('i = ' || i);
        i := i + 1;
    END LOOP;
END;
/

-- FOR loop (most common — range is inclusive)
BEGIN
    FOR i IN 1..5 LOOP
        DBMS_OUTPUT.PUT_LINE('i = ' || i);
    END LOOP;
END;
/

18. Cursors

A cursor is a pointer to the private memory area (context area) holding the result set of a query, allowing row-by-row processing.

18.1 Implicit Cursor

Oracle automatically creates one for every DML/SELECT-INTO statement; its status can be checked via attributes like SQL%ROWCOUNT, SQL%FOUND.

BEGIN
    UPDATE employees SET salary = salary * 1.05 WHERE dept_id = 10;
    DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT || ' rows updated.');
END;
/

18.2 Explicit Cursor

Declared and controlled manually — needed when processing multiple rows one at a time.

DECLARE
    CURSOR emp_cursor IS
        SELECT first_name, salary FROM employees WHERE dept_id = 10;
    v_name employees.first_name%TYPE;
    v_salary employees.salary%TYPE;
BEGIN
    OPEN emp_cursor;
    LOOP
        FETCH emp_cursor INTO v_name, v_salary;
        EXIT WHEN emp_cursor%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(v_name || ' earns ' || v_salary);
    END LOOP;
    CLOSE emp_cursor;
END;
/

18.3 Cursor FOR Loop (simplified — no explicit OPEN/FETCH/CLOSE needed)

BEGIN
    FOR emp_rec IN (SELECT first_name, salary FROM employees WHERE dept_id = 10) LOOP
        DBMS_OUTPUT.PUT_LINE(emp_rec.first_name || ' earns ' || emp_rec.salary);
    END LOOP;
END;
/

18.4 Parameterized Cursor

DECLARE
    CURSOR emp_cursor(p_dept_id NUMBER) IS
        SELECT first_name, salary FROM employees WHERE dept_id = p_dept_id;
BEGIN
    FOR emp_rec IN emp_cursor(20) LOOP
        DBMS_OUTPUT.PUT_LINE(emp_rec.first_name);
    END LOOP;
END;
/

19. Stored Procedures & Functions

19.1 Procedures

CREATE OR REPLACE PROCEDURE give_raise(
    p_emp_id IN NUMBER,
    p_percent IN NUMBER
)
IS
BEGIN
    UPDATE employees
    SET salary = salary + (salary * p_percent / 100)
    WHERE emp_id = p_emp_id;

    COMMIT;
END give_raise;
/

-- Calling the procedure
BEGIN
    give_raise(101, 10);
END;
/
-- or, in SQL*Plus:
EXEC give_raise(101, 10);

Procedure with an OUT parameter:

CREATE OR REPLACE PROCEDURE get_salary(
    p_emp_id IN NUMBER,
    p_salary OUT NUMBER
)
IS
BEGIN
    SELECT salary INTO p_salary FROM employees WHERE emp_id = p_emp_id;
END get_salary;
/

DECLARE
    v_salary NUMBER;
BEGIN
    get_salary(101, v_salary);
    DBMS_OUTPUT.PUT_LINE('Salary: ' || v_salary);
END;
/

19.2 Functions

Must return exactly one value, and can be used directly within SQL expressions.

CREATE OR REPLACE FUNCTION annual_salary(p_monthly NUMBER)
RETURN NUMBER
IS
BEGIN
    RETURN p_monthly * 12;
END annual_salary;
/

SELECT first_name, salary, annual_salary(salary) AS yearly_salary FROM employees;

20. Packages

A package groups logically related procedures, functions, variables, and cursors into a single named unit — consisting of a specification (public interface) and a body (implementation). Packages promote modularity, encapsulation (private vs public members), and improved performance (loaded into memory as a unit).

Package Specification (declares what's publicly available):

CREATE OR REPLACE PACKAGE emp_pkg AS
    PROCEDURE give_raise(p_emp_id NUMBER, p_percent NUMBER);
    FUNCTION annual_salary(p_monthly NUMBER) RETURN NUMBER;
END emp_pkg;
/

Package Body (contains the actual implementation):

CREATE OR REPLACE PACKAGE BODY emp_pkg AS

    PROCEDURE give_raise(p_emp_id NUMBER, p_percent NUMBER) IS
    BEGIN
        UPDATE employees
        SET salary = salary + (salary * p_percent / 100)
        WHERE emp_id = p_emp_id;
        COMMIT;
    END give_raise;

    FUNCTION annual_salary(p_monthly NUMBER) RETURN NUMBER IS
    BEGIN
        RETURN p_monthly * 12;
    END annual_salary;

END emp_pkg;
/

Calling package members:

BEGIN
    emp_pkg.give_raise(101, 10);
END;
/

SELECT emp_pkg.annual_salary(salary) FROM employees;

21. Triggers

CREATE TABLE salary_audit (
    audit_id     NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    emp_id       NUMBER,
    old_salary   NUMBER,
    new_salary   NUMBER,
    changed_on   DATE
);

CREATE OR REPLACE TRIGGER trg_salary_audit
BEFORE UPDATE OF salary ON employees
FOR EACH ROW
BEGIN
    IF :NEW.salary <> :OLD.salary THEN
        INSERT INTO salary_audit (emp_id, old_salary, new_salary, changed_on)
        VALUES (:OLD.emp_id, :OLD.salary, :NEW.salary, SYSDATE);
    END IF;
END;
/
  • In Oracle, OLD/NEW are referenced with a leading colon: :OLD.column, :NEW.column.
  • Trigger timing: BEFORE / AFTER; trigger event: INSERT / UPDATE / DELETE; granularity: FOR EACH ROW (row-level) vs statement-level (no FOR EACH ROW clause).
UPDATE employees SET salary = 70000 WHERE emp_id = 101;
SELECT * FROM salary_audit;   -- shows the automatically logged change

Disabling/enabling a trigger:

ALTER TRIGGER trg_salary_audit DISABLE;
ALTER TRIGGER trg_salary_audit ENABLE;
DROP TRIGGER trg_salary_audit;

22. Exception Handling

PL/SQL provides structured error handling using EXCEPTION blocks, distinguishing predefined, named user-defined, and unnamed (ORA-numbered) exceptions.

22.1 Predefined Exceptions

DECLARE
    v_salary NUMBER;
BEGIN
    SELECT salary INTO v_salary FROM employees WHERE emp_id = 9999;  -- doesn't exist
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('No employee found with that ID.');
    WHEN TOO_MANY_ROWS THEN
        DBMS_OUTPUT.PUT_LINE('Query returned more than one row.');
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Unexpected error: ' || SQLERRM);
END;
/

Common predefined exceptions:

Exception Raised When
NO_DATA_FOUND A SELECT INTO returns no rows
TOO_MANY_ROWS A SELECT INTO returns more than one row
ZERO_DIVIDE Division by zero is attempted
DUP_VAL_ON_INDEX A unique constraint/index is violated
VALUE_ERROR A conversion/assignment error (e.g., value too large for a variable)
OTHERS Catch-all for any exception not explicitly handled

22.2 User-Defined Exceptions

DECLARE
    e_invalid_salary EXCEPTION;
    v_salary NUMBER := -5000;
BEGIN
    IF v_salary < 0 THEN
        RAISE e_invalid_salary;
    END IF;
EXCEPTION
    WHEN e_invalid_salary THEN
        DBMS_OUTPUT.PUT_LINE('Error: Salary cannot be negative.');
END;
/

22.3 RAISE_APPLICATION_ERROR

Used to raise a custom error with a specific error number (must be between -20000 and -20999) and message — commonly used inside procedures/triggers to reject invalid operations.

CREATE OR REPLACE PROCEDURE update_salary(p_emp_id NUMBER, p_new_salary NUMBER)
IS
BEGIN
    IF p_new_salary < 0 THEN
        RAISE_APPLICATION_ERROR(-20001, 'Salary cannot be negative.');
    END IF;

    UPDATE employees SET salary = p_new_salary WHERE emp_id = p_emp_id;
    COMMIT;
END;
/

23. Transactions & Locking

23.1 Transaction Control

UPDATE accounts SET balance = balance - 5000 WHERE acc_id = 1;
UPDATE accounts SET balance = balance + 5000 WHERE acc_id = 2;

COMMIT;    -- makes changes permanent

-- On error or to undo:
ROLLBACK;

-- Partial rollback with SAVEPOINT
SAVEPOINT before_update;
UPDATE accounts SET balance = balance - 1000 WHERE acc_id = 1;
ROLLBACK TO before_update;

Unlike MySQL (where autocommit is often ON by default per session), Oracle does not auto-commit DML by default within a session — changes stay pending until an explicit COMMIT (or are undone by ROLLBACK, or auto-committed when the session ends normally).

23.2 Locking

Oracle uses row-level locking automatically for DML. Explicit locking can be requested:

SELECT * FROM employees WHERE emp_id = 101 FOR UPDATE;
-- Locks the selected row(s) until COMMIT/ROLLBACK, preventing other sessions
-- from updating them concurrently.

SELECT * FROM employees WHERE dept_id = 10 FOR UPDATE NOWAIT;
-- Fails immediately with an error if rows are already locked, instead of waiting.

23.3 ACID Properties (same as any relational database)

Property Meaning
Atomicity All-or-nothing execution of a transaction
Consistency Database remains in a valid state, respecting constraints
Isolation Concurrent transactions don't see each other's uncommitted changes
Durability Committed changes survive a crash

24. User Management & Privileges

-- Creating a user
CREATE USER app_user IDENTIFIED BY StrongPassword123;

-- Granting system privileges
GRANT CREATE SESSION TO app_user;    -- allows the user to log in
GRANT CREATE TABLE TO app_user;

-- Granting object privileges
GRANT SELECT, INSERT, UPDATE ON hr.employees TO app_user;

-- Granting all privileges on an object
GRANT ALL ON hr.employees TO app_user;

-- Revoking
REVOKE INSERT ON hr.employees FROM app_user;

-- Roles group multiple privileges together for easier management
CREATE ROLE hr_manager;
GRANT SELECT, INSERT, UPDATE, DELETE ON hr.employees TO hr_manager;
GRANT hr_manager TO app_user;

-- Removing a user
DROP USER app_user CASCADE;   -- CASCADE also drops all objects owned by the user

25. Advanced: Analytic Functions, Hierarchical Queries, Collections

25.1 Analytic (Window) Functions

SELECT
    first_name, dept_id, salary,
    RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS dept_rank,
    AVG(salary) OVER (PARTITION BY dept_id) AS dept_avg_salary,
    LAG(salary) OVER (ORDER BY salary) AS prev_salary,
    LEAD(salary) OVER (ORDER BY salary) AS next_salary
FROM employees;
  • RANK() / DENSE_RANK() / ROW_NUMBER() — assign ranking/row numbers within partitions.
  • LAG() / LEAD() — access a preceding/following row's value without a self-join.

25.2 Hierarchical Queries (CONNECT BY) — Oracle-specific

Used to query tree-structured data (e.g., an organizational chart) — Oracle's own syntax, predating standard recursive CTEs.

SELECT emp_id, first_name, manager_id, LEVEL
FROM employees
START WITH manager_id IS NULL          -- root of the hierarchy (e.g., CEO)
CONNECT BY PRIOR emp_id = manager_id   -- each row's manager_id must equal parent's emp_id
ORDER SIBLINGS BY first_name;
  • LEVEL — a pseudo-column indicating depth in the hierarchy (root = 1).
  • PRIOR — refers to the "parent" row's column value in the recursive step.

Standard recursive CTE (also supported in Oracle 11g R2+):

WITH org_chart (emp_id, first_name, manager_id, lvl) AS (
    SELECT emp_id, first_name, manager_id, 1
    FROM employees WHERE manager_id IS NULL
    UNION ALL
    SELECT e.emp_id, e.first_name, e.manager_id, oc.lvl + 1
    FROM employees e
    JOIN org_chart oc ON e.manager_id = oc.emp_id
)
SELECT * FROM org_chart ORDER BY lvl;

25.3 PL/SQL Collections

Collections are PL/SQL's array-like data structures for holding multiple values in memory.

Type Description
Associative Array (Index-by table) Key-value pairs, indexed by integer or string, size not fixed upfront
Nested Table Ordered collection, can be stored in a database column
VARRAY Fixed maximum size ordered collection 
DECLARE
    TYPE salary_table IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
    v_salaries salary_table;
BEGIN
    v_salaries(1) := 50000;
    v_salaries(2) := 62000;
    DBMS_OUTPUT.PUT_LINE(v_salaries(1) + v_salaries(2));   -- 112000
END;
/

25.4 BULK COLLECT & FORALL (High-performance bulk operations)

Reduces context-switching between the SQL and PL/SQL engines when processing many rows.

DECLARE
    TYPE id_table IS TABLE OF employees.emp_id%TYPE;
    v_ids id_table;
BEGIN
    SELECT emp_id BULK COLLECT INTO v_ids FROM employees WHERE dept_id = 10;

    FORALL i IN v_ids.FIRST..v_ids.LAST
        UPDATE employees SET salary = salary * 1.05 WHERE emp_id = v_ids(i);

    COMMIT;
END;
/

26. Performance: EXPLAIN PLAN & Optimization

EXPLAIN PLAN FOR
SELECT * FROM employees WHERE dept_id = 10;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);   -- displays the execution plan

Key things to look for in an execution plan:

Concept What to look for
Full Table Scan Reading every row — acceptable for small tables, costly for large ones
Index Range Scan Efficiently using an index for a range/equality condition — usually good
Cost Oracle's internal estimate of query expense — lower is generally better (relative, not absolute)
Cardinality Estimated number of rows a step will return

Common optimization practices:

  • Index columns used in WHERE, JOIN, and ORDER BY.
  • Use bind variables (:variable) in application code to enable SQL statement reuse and avoid re-parsing.
  • Avoid wrapping indexed columns in functions in WHERE clauses (unless a matching function-based index exists).
  • Use EXPLAIN PLAN / DBMS_XPLAN and Oracle's SQL Tuning Advisor for slow queries.
  • Consider partitioning very large tables (by range, list, or hash) to improve manageability and query performance.
  • Gather optimizer statistics regularly: EXEC DBMS_STATS.GATHER_TABLE_STATS('HR','EMPLOYEES');

Quick Revision Summary

Concept One-line takeaway
Oracle Database Enterprise RDBMS with rich PL/SQL support, schemas instead of separate "databases"
Data Types VARCHAR2, NUMBER, DATE, CLOB, BLOB — no native table-level BOOLEAN
DDL/DML Standard CREATE/ALTER/DROP, INSERT/UPDATE/DELETE, plus Oracle's MERGE for upserts
Constraints PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL, CHECK — nameable for clearer errors
Row limiting ROWNUM (careful with ordering) or modern FETCH FIRST...ROWS ONLY
Aggregates/Grouping GROUP BY/HAVING, plus ROLLUP/CUBE for subtotal reporting
Joins ANSI JOIN syntax preferred; legacy (+) syntax still seen; native FULL OUTER JOIN support
Subqueries Scalar, IN, correlated, EXISTS, inline views — same concepts as standard SQL
Set Operators UNION, UNION ALL, INTERSECT, MINUS
Views Regular (virtual, live) vs Materialized (physically stored, manually/auto refreshed)
Sequences/Synonyms SEQUENCE for auto-numbering; SYNONYM for aliasing objects
PL/SQL Procedural blocks: DECLARE / BEGIN / EXCEPTION / END, with variables, loops, conditionals
Cursors Implicit (automatic) vs explicit (OPEN/FETCH/CLOSE) row-by-row processing
Procedures/Functions Reusable PL/SQL units; functions must return exactly one value
Packages Bundle related procedures/functions/variables into one modular, encapsulated unit
Triggers Auto-fire on DML events; use :OLD/:NEW to access row values
Exception Handling Predefined (NO_DATA_FOUND, etc.), user-defined, and RAISE_APPLICATION_ERROR
Transactions No autocommit by default; COMMIT/ROLLBACK/SAVEPOINT; row-level locking, FOR UPDATE
User Management CREATE USER, GRANT/REVOKE privileges and roles
Analytic Functions RANK, ROW_NUMBER, LAG, LEAD with OVER (PARTITION BY...)
Hierarchical Queries Oracle's CONNECT BY PRIOR / START WITH, or standard recursive WITH CTEs
Collections Associative arrays, nested tables, VARRAYs; BULK COLLECT/FORALL for performance
Performance EXPLAIN PLAN/DBMS_XPLAN, indexing, bind variables, partitioning, fresh optimizer stats

End of Oracle Notes