Database Programming

A subquery (nested query) is a SELECT statement written inside another SQL statement (SELECT, INSERT, UPDATE, DELETE). The inner query runs first and its result is used by the outer query. A subquery is enclosed in parentheses and can appear in the WHERE, HAVING or FROM clause.

Types

• Single-row subquery returns one row; used with =, >, <.
• Multiple-row subquery returns many rows; used with IN, ANY, ALL.
• Correlated subquery refers to a column of the outer query and is executed once per outer row.

Example. Find employees earning more than the average salary:

SELECT ename, sal
FROM   emp
WHERE  sal > (SELECT AVG(sal) FROM emp);

The inner query computes the average salary; the outer query lists employees whose salary exceeds it.

Multiple-row example:

SELECT ename FROM emp
WHERE deptno IN (SELECT deptno FROM dept WHERE loc = 'KATHMANDU');

A record in PL/SQL is a composite data type that groups related items of different data types into one unit, like a row of a table. Each item is a field, accessed as record_name.field_name.

Types of records

1. Table-based record (%ROWTYPE) has one field for every column of a table or view.

DECLARE
  emp_rec employees%ROWTYPE;
BEGIN
  SELECT * INTO emp_rec FROM employees WHERE employee_id = 100;
  DBMS_OUTPUT.PUT_LINE(emp_rec.first_name);
END;

2. Cursor-based record is declared with cursor_name%ROWTYPE; its fields match the columns selected by the cursor.

CURSOR c1 IS SELECT ename, sal FROM emp;
r c1%ROWTYPE;

3. User-defined record is declared with the TYPE ... IS RECORD statement, so the programmer chooses the fields.

DECLARE
  TYPE student_rec IS RECORD (
    id   NUMBER,
    name VARCHAR2(30),
    mark NUMBER );
  s student_rec;
BEGIN
  s.id := 1; s.name := 'Ram'; s.mark := 80;
  DBMS_OUTPUT.PUT_LINE(s.name || ' ' || s.mark);
END;

Variable. A named memory location that holds a value which can change while the program runs. It is declared in the DECLARE section.

v_salary NUMBER(8,2) := 5000;

Constant. A named memory location whose value is fixed at declaration and cannot be changed afterwards. The keyword CONSTANT is used and an initial value is mandatory.

c_pi CONSTANT NUMBER := 3.1416;

Differences

A package is a schema object that groups logically related PL/SQL types, variables, cursors, procedures and functions into one unit. It has two parts.

1. Package specification (interface). Declares the public items that can be used from outside. It contains no code bodies.

2. Package body (implementation). Contains the code of the subprograms declared in the specification, plus private items and an optional initialization block.

Example

CREATE OR REPLACE PACKAGE emp_pkg IS
  g_bonus CONSTANT NUMBER := 1000;           -- public variable
  PROCEDURE raise_salary(p_id NUMBER, p_pct NUMBER);
  FUNCTION  get_salary(p_id NUMBER) RETURN NUMBER;
END emp_pkg;
/
CREATE OR REPLACE PACKAGE BODY emp_pkg IS
  PROCEDURE raise_salary(p_id NUMBER, p_pct NUMBER) IS
  BEGIN
    UPDATE emp SET sal = sal + sal * p_pct / 100 WHERE empno = p_id;
  END;

  FUNCTION get_salary(p_id NUMBER) RETURN NUMBER IS
    v_sal NUMBER;
  BEGIN
    SELECT sal INTO v_sal FROM emp WHERE empno = p_id;
    RETURN v_sal;
  END;
END emp_pkg;
/

Calling: EXEC emp_pkg.raise_salary(7369, 10);

Benefits: modularity, information hiding, better performance (whole package is loaded into memory once), overloading and easier maintenance.

A trigger is a named PL/SQL block stored in the database that executes automatically (fires) when a specified event occurs, such as INSERT, UPDATE or DELETE on a table, a DDL statement or a database event. It is used for auditing, enforcing complex business rules and maintaining derived data.

CREATE OR REPLACE TRIGGER trg_name
{BEFORE | AFTER} {INSERT | UPDATE | DELETE} ON table_name
[FOR EACH ROW]
BEGIN
  ...
END;

Statement-level vs row-level trigger

Example (row level)

CREATE OR REPLACE TRIGGER trg_sal_audit
AFTER UPDATE OF sal ON emp
FOR EACH ROW
BEGIN
  INSERT INTO sal_log VALUES (:OLD.empno, :OLD.sal, :NEW.sal, SYSDATE);
END;

A function is a named PL/SQL subprogram stored in the database that performs a task and must return a single value to the caller using RETURN. It can be called from SQL statements or PL/SQL expressions.

CREATE OR REPLACE FUNCTION get_tax(p_sal NUMBER) RETURN NUMBER IS
BEGIN
  RETURN p_sal * 0.1;
END;
/
SELECT ename, get_tax(sal) FROM emp;

Function vs procedure

The procedure uses dynamic SQL so that it works for any table name passed as a parameter.

CREATE OR REPLACE PROCEDURE count_records(p_table IN VARCHAR2) IS
  v_count NUMBER;
BEGIN
  EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM ' || p_table INTO v_count;
  DBMS_OUTPUT.PUT_LINE('Number of records in ' || p_table || ' = ' || v_count);
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
END count_records;
/

Execution:

SET SERVEROUTPUT ON;
EXEC count_records('EMPLOYEES');

Output (example): Number of records in EMPLOYEES = 107

For a fixed table the body can simply be SELECT COUNT(*) INTO v_count FROM emp;.

A cursor is a pointer to a private memory area (context area) created by Oracle to hold the rows returned by a SQL statement. It lets a PL/SQL program process the rows of a query one at a time.

Why it is required: a plain SELECT ... INTO can handle only one row (more rows raise TOO_MANY_ROWS). A cursor is needed when a query returns multiple rows that must be processed individually, and it gives control over fetching and attributes such as %FOUND, %NOTFOUND, %ROWCOUNT, %ISOPEN.

Steps of an explicit cursor

1. Declare the cursor with the query in the declaration section.

CURSOR c_emp IS SELECT ename, sal FROM emp;

2. Open the cursor: executes the query and fills the context area (the active set).

OPEN c_emp;

3. Fetch rows one at a time into variables or a record.

FETCH c_emp INTO v_name, v_sal;

4. Close the cursor to release memory.

CLOSE c_emp;

Example

DECLARE
  CURSOR c_emp IS SELECT ename, sal FROM emp;
  v_name emp.ename%TYPE;
  v_sal  emp.sal%TYPE;
BEGIN
  OPEN c_emp;
  LOOP
    FETCH c_emp INTO v_name, v_sal;
    EXIT WHEN c_emp%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(v_name || ' ' || v_sal);
  END LOOP;
  CLOSE c_emp;
END;

Joins combine rows from two or more tables. Consider:

EMP(empno, ename, deptno) and DEPT(deptno, dname).

1. Inner join. Returns only rows that have matching values in both tables.

SELECT e.ename, d.dname
FROM emp e INNER JOIN dept d ON e.deptno = d.deptno;

2. Natural join. An inner join that automatically joins on all columns with the same name (here deptno), which appears once in the result.

SELECT ename, dname FROM emp NATURAL JOIN dept;

3. Outer joins. Return matching rows plus unmatched rows (padded with NULL).

• Left outer join: all rows of the left table plus matches from the right.

SELECT e.ename, d.dname
FROM emp e LEFT OUTER JOIN dept d ON e.deptno = d.deptno;

• Right outer join: all rows of the right table plus matches from the left (e.g. departments with no employees).

SELECT e.ename, d.dname
FROM emp e RIGHT OUTER JOIN dept d ON e.deptno = d.deptno;

• Full outer join: all rows from both tables; unmatched rows on either side get NULL.

SELECT e.ename, d.dname
FROM emp e FULL OUTER JOIN dept d ON e.deptno = d.deptno;

4. Cross join. Cartesian product: every row of the first table is paired with every row of the second. If EMP has $m$ rows and DEPT has $n$ rows, the result has $m \times n$ rows.

SELECT e.ename, d.dname FROM emp e CROSS JOIN dept d;

A repetitive (iterative) control statement executes a block of statements repeatedly until a condition is met or for a fixed number of times. PL/SQL provides three looping statements.

1. Basic LOOP. Runs indefinitely until an EXIT or EXIT WHEN is executed.

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

2. WHILE loop. The condition is tested before each iteration; the body may run zero times.

DECLARE i NUMBER := 1;
BEGIN
  WHILE i <= 5 LOOP
    DBMS_OUTPUT.PUT_LINE(i);
    i := i + 1;
  END LOOP;
END;

3. FOR loop. Runs a fixed number of times; the counter is declared implicitly and increments automatically (REVERSE counts down).

BEGIN
  FOR i IN 1..5 LOOP
    DBMS_OUTPUT.PUT_LINE(i);
  END LOOP;
END;

Each of the above prints 1 to 5. EXIT leaves a loop and CONTINUE skips to the next iteration. Loops can be nested and labelled.

PL/SQL is a block-structured language. A block has up to four sections:

1. DECLARE (optional): declares variables, constants, cursors, user-defined types.
2. BEGIN (mandatory): executable SQL and PL/SQL statements.
3. EXCEPTION (optional): handles runtime errors.
4. END; (mandatory): ends the block.

DECLARE
  v_name  emp.ename%TYPE;
  v_sal   emp.sal%TYPE;
BEGIN
  SELECT ename, sal INTO v_name, v_sal FROM emp WHERE empno = 7369;
  DBMS_OUTPUT.PUT_LINE(v_name || ' earns ' || v_sal);
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('No such employee');
END;
/

Blocks may be nested, and may be anonymous or named (procedures, functions, packages, triggers).

A record is a composite variable made of related fields of possibly different data types, treated as one unit (like a table row). It is declared with %ROWTYPE or TYPE ... IS RECORD.

A nested record is a record that has another record as one of its fields.

DECLARE
  TYPE address_rec IS RECORD (
    city   VARCHAR2(20),
    street VARCHAR2(30) );
  TYPE person_rec IS RECORD (
    name VARCHAR2(30),
    addr address_rec );          -- nested record
  p person_rec;
BEGIN
  p.name      := 'Sita';
  p.addr.city := 'Pokhara';
  DBMS_OUTPUT.PUT_LINE(p.name || ', ' || p.addr.city);
END;

Benefits of records

• Group related data so that it can be handled and passed to subprograms as one parameter.
• %ROWTYPE records adapt automatically when table columns change, so less maintenance.
• Fewer variables to declare; code is shorter and more readable.
• A whole row can be fetched, inserted or updated in a single statement (INSERT INTO t VALUES rec;).

A function is a stored subprogram that returns one value. It can take parameters, which are declared in the header with a mode (IN by default) and a data type.

Creating a parameterized function

CREATE OR REPLACE FUNCTION area_rect(p_len IN NUMBER, p_wid IN NUMBER)
RETURN NUMBER IS
BEGIN
  RETURN p_len * p_wid;
END;
/

Ways of calling it

1. In an anonymous PL/SQL block:

DECLARE a NUMBER;
BEGIN
  a := area_rect(5, 4);
  DBMS_OUTPUT.PUT_LINE('Area = ' || a);   -- 20
END;

2. In a SQL statement:

SELECT area_rect(5, 4) FROM dual;

3. From SQL*Plus using a bind variable:

VARIABLE r NUMBER
EXEC :r := area_rect(5, 4);
PRINT r

Parameter passing notations

• Positional: area_rect(5, 4).
• Named: area_rect(p_wid => 4, p_len => 5).
• Mixed: area_rect(5, p_wid => 4).

Default values can be given (p_wid IN NUMBER DEFAULT 1), so the argument may be omitted.

A cursor is a pointer to the private SQL work area (context area) where Oracle stores the result set of a query, allowing a program to process the returned rows one by one.

Types of cursors

1. Implicit cursor. Created automatically by Oracle for every INSERT, UPDATE, DELETE and single-row SELECT INTO. The programmer does not declare or control it, but can check its attributes through the name SQL: SQL%FOUND, SQL%NOTFOUND, SQL%ROWCOUNT, SQL%ISOPEN.

BEGIN
  UPDATE emp SET sal = sal + 100 WHERE deptno = 10;
  DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT || ' rows updated');
END;

2. Explicit cursor. Declared and controlled by the programmer for queries returning multiple rows, through DECLARE, OPEN, FETCH and CLOSE.

CURSOR c IS SELECT ename FROM emp;

3. Parameterized cursor. An explicit cursor that accepts parameters so the same cursor can be opened with different values: CURSOR c(p_dept NUMBER) IS SELECT ename FROM emp WHERE deptno = p_dept;

4. REF cursor (cursor variable). A pointer to a query that can be assigned at run time and passed between subprograms, e.g. TYPE rc IS REF CURSOR;.

Why triggers are needed

• Enforce complex business rules and integrity constraints that ordinary constraints cannot express.
• Audit changes made to data automatically (who, when, old and new values).
• Maintain derived or summary columns and replicate data automatically.
• Prevent invalid transactions, e.g. block DML outside office hours.
• Generate primary key values automatically (with sequences).

Examples

(a) Auditing (row-level, AFTER trigger)

CREATE OR REPLACE TRIGGER trg_audit
AFTER DELETE ON emp
FOR EACH ROW
BEGIN
  INSERT INTO emp_audit(empno, deleted_on) VALUES (:OLD.empno, SYSDATE);
END;
/

(b) Business rule (BEFORE trigger): salary must not be reduced.

CREATE OR REPLACE TRIGGER trg_sal_check
BEFORE UPDATE OF sal ON emp
FOR EACH ROW
BEGIN
  IF :NEW.sal < :OLD.sal THEN
    RAISE_APPLICATION_ERROR(-20001, 'Salary cannot be decreased');
  END IF;
END;
/

(c) Statement-level restriction

CREATE OR REPLACE TRIGGER trg_time
BEFORE INSERT ON emp
BEGIN
  IF TO_CHAR(SYSDATE,'DY') IN ('SAT') THEN
    RAISE_APPLICATION_ERROR(-20002, 'No insertion on Saturday');
  END IF;
END;
/

A user-defined exception is an error condition defined by the programmer, for situations that Oracle does not treat as errors (e.g. a business rule violation). Creating and using one takes three steps.

1. Declare the exception in the declaration section: e_name EXCEPTION;
2. Raise it explicitly in the executable section with RAISE e_name; (usually inside an IF).
3. Handle it in the EXCEPTION section with WHEN e_name THEN ....

Example

DECLARE
  e_low_balance EXCEPTION;
  v_balance NUMBER := 500;
  v_withdraw NUMBER := 2000;
BEGIN
  IF v_withdraw > v_balance THEN
    RAISE e_low_balance;
  END IF;
  v_balance := v_balance - v_withdraw;
EXCEPTION
  WHEN e_low_balance THEN
    DBMS_OUTPUT.PUT_LINE('Insufficient balance');
END;
/

Output: Insufficient balance.

An exception can also be tied to an Oracle error number with PRAGMA EXCEPTION_INIT(e_name, -2292);, or a custom error can be sent to the caller with RAISE_APPLICATION_ERROR(-20001, 'message'); (error numbers -20000 to -20999).

(a) INSTEAD OF trigger

An INSTEAD OF trigger is defined on a view and fires in place of the DML statement (INSERT, UPDATE, DELETE) issued against it. It is always row-level. It is used to make complex views (for example, join views) updatable, because Oracle cannot directly modify them.

CREATE OR REPLACE TRIGGER trg_emp_dept_ins
INSTEAD OF INSERT ON emp_dept_view
FOR EACH ROW
BEGIN
  INSERT INTO emp(empno, ename, deptno) VALUES (:NEW.empno, :NEW.ename, :NEW.deptno);
END;

(c) Parameterized cursor

A parameterized cursor is an explicit cursor that takes parameters, so the same cursor can be reopened with different values. Parameters are passed when the cursor is opened and are used in the query's WHERE clause.

DECLARE
  CURSOR c_emp(p_dept NUMBER) IS
    SELECT ename FROM emp WHERE deptno = p_dept;
BEGIN
  FOR r IN c_emp(10) LOOP
    DBMS_OUTPUT.PUT_LINE(r.ename);
  END LOOP;
END;

PL/SQL architecture

PL/SQL code is processed by the PL/SQL engine, which can reside in the Oracle server or in tools such as Oracle Forms.

1. A PL/SQL block is submitted to the PL/SQL engine.
2. The engine executes the procedural statements (assignments, loops, conditions) itself.
3. SQL statements inside the block are sent to the SQL statement executor in the Oracle server.
4. Results come back to the engine and the output is returned to the client.

Since a whole block is sent at once, network traffic is reduced and performance improves.

SQL vs PL/SQL

Conditional constructs

1. IF-THEN

IF sal > 5000 THEN bonus := 500; END IF;

2. IF-THEN-ELSE

IF sal > 5000 THEN bonus := 500; ELSE bonus := 200; END IF;

3. IF-THEN-ELSIF

IF sal > 8000 THEN grade := 'A';
ELSIF sal > 5000 THEN grade := 'B';
ELSE grade := 'C';
END IF;

4. CASE

CASE grade
  WHEN 'A' THEN DBMS_OUTPUT.PUT_LINE('Excellent');
  WHEN 'B' THEN DBMS_OUTPUT.PUT_LINE('Good');
  ELSE DBMS_OUTPUT.PUT_LINE('Fair');
END CASE;

Stored procedure. A named PL/SQL block, compiled and stored in the database, that performs a specific task and can be called repeatedly by name with parameters. It is created with CREATE [OR REPLACE] PROCEDURE.

CREATE OR REPLACE PROCEDURE raise_sal(p_id IN NUMBER, p_new_sal OUT NUMBER) IS
BEGIN
  UPDATE emp SET sal = sal * 1.1 WHERE empno = p_id
  RETURNING sal INTO p_new_sal;
END;
/

Parameter modes

CREATE OR REPLACE PROCEDURE demo(a IN NUMBER, b OUT NUMBER, c IN OUT NUMBER) IS
BEGIN
  b := a * 2;
  c := c + a;
END;

Function vs stored procedure

Function example:

CREATE FUNCTION sq(n NUMBER) RETURN NUMBER IS BEGIN RETURN n*n; END;
-- SELECT sq(4) FROM dual;  gives 16

Procedure example:

CREATE PROCEDURE greet(n VARCHAR2) IS BEGIN DBMS_OUTPUT.PUT_LINE('Hello '||n); END;
-- EXEC greet('Ram');

Row trigger. Fires once for each row affected by the DML statement. Declared with FOR EACH ROW; can use :OLD and :NEW.

CREATE OR REPLACE TRIGGER trg_row
BEFORE UPDATE ON emp
FOR EACH ROW
BEGIN
  DBMS_OUTPUT.PUT_LINE('Old: ' || :OLD.sal || ' New: ' || :NEW.sal);
END;

If an update changes 5 rows, it fires 5 times.

Statement trigger. Fires once per DML statement regardless of the number of rows (even for zero rows). No FOR EACH ROW; cannot use :OLD and :NEW.

CREATE OR REPLACE TRIGGER trg_stmt
BEFORE DELETE ON emp
BEGIN
  DBMS_OUTPUT.PUT_LINE('A delete on EMP is being attempted');
END;

A delete removing 5 rows fires it only once.

Five SQL*Plus commands

1. DESCRIBE emp (or DESC emp): shows the column names, nulls and data types of a table.
2. SET SERVEROUTPUT ON: enables display of DBMS_OUTPUT output.
3. SPOOL file.txt / SPOOL OFF: saves the session output to a file.
4. SHOW USER: displays the current user (also SHOW ERRORS for compile errors).
5. RUN or /: executes the SQL or PL/SQL statement held in the buffer. Others include EDIT, LIST, CLEAR SCREEN, @script.sql, CONNECT and EXIT.

An Oracle server consists of an instance and a database.

1. Memory structures

• System Global Area (SGA): shared memory for the instance. Its parts are the database buffer cache (recently used data blocks), redo log buffer (changes before writing to redo logs), shared pool (library cache holding parsed SQL/PLSQL and data dictionary cache), large pool and Java pool.
• Program Global Area (PGA): private memory for each server process (sort area, session data, cursor state).

2. Background processes

• DBWn (database writer): writes dirty buffers to the data files.
• LGWR (log writer): writes the redo log buffer to the online redo log files.
• CKPT (checkpoint): signals checkpoints and updates the file headers.
• SMON (system monitor): instance recovery and cleanup of temporary segments.
• PMON (process monitor): cleans up failed user processes and releases their resources.
• ARCn (archiver): copies filled redo logs to archive storage.

3. Physical storage structures

• Data files: store the actual data.
• Control files: record the database structure, names and locations of files, and checkpoint information.
• Redo log files: record all changes for recovery.
• Parameter and password files, archived logs.

4. Logical storage structures: tablespaces contain segments, which contain extents, which contain data blocks.

User side: user process connects through a listener to a server process which accesses the SGA and the files.

Data Definition Language (DDL) commands define or change the structure of database objects. They are auto-committed.

1. CREATE: creates an object.

CREATE TABLE student (id NUMBER PRIMARY KEY, name VARCHAR2(30), age NUMBER);

2. ALTER: modifies an existing object.

ALTER TABLE student ADD (address VARCHAR2(40));
ALTER TABLE student MODIFY (name VARCHAR2(50));
ALTER TABLE student DROP COLUMN age;

3. DROP: removes an object permanently (a table goes to the recycle bin unless PURGE is used).

DROP TABLE student;

4. TRUNCATE: deletes all rows quickly and releases the space; cannot be rolled back.

TRUNCATE TABLE student;

5. RENAME: renames an object.

RENAME student TO learner;

6. COMMENT: adds a comment to the data dictionary.

COMMENT ON TABLE student IS 'Student details';

Other DDL commands include FLASHBACK and PURGE.

An identifier is a name given by the programmer to a PL/SQL object such as a variable, constant, cursor, exception, procedure, function, package or label.

Properties (rules)

1. It must start with a letter.
2. It may then contain letters, digits and the symbols $, _ and #.
3. Maximum length is 30 characters.
4. It is not case sensitive (Salary and SALARY are the same) unless enclosed in double quotes.
5. It cannot be a reserved word (e.g. BEGIN, END, SELECT), unless quoted.
6. It cannot contain spaces or other special characters (a quoted identifier can).
7. It must be unique within its scope; an inner block may redeclare a name, which hides the outer one.
8. Names of columns and variables should differ, otherwise the column name takes precedence in SQL statements.

Valid: v_salary, emp_name1, total$. Invalid: 1name, emp name, end.

A collection is an ordered group of elements of the same data type, each identified by an index (subscript). PL/SQL has three types.

1. Associative array (index-by table). A set of key-value pairs; the key is an integer or a string. Sparse, unbounded, exists only in PL/SQL.

TYPE sal_tab IS TABLE OF NUMBER INDEX BY VARCHAR2(20);
salary sal_tab;
BEGIN salary('Ram') := 5000; END;

2. Nested table. An unbounded one-dimensional collection that can be stored in a database column. It starts dense but may become sparse after deletes. It must be initialized with a constructor and extended with EXTEND.

TYPE name_list IS TABLE OF VARCHAR2(20);
names name_list := name_list('Ram', 'Sita');

3. VARRAY (variable-size array). A collection with a fixed maximum size set at declaration, always dense, can be stored in a table column.

TYPE marks_arr IS VARRAY(5) OF NUMBER;
m marks_arr := marks_arr(60, 70, 80);

Collection methods: COUNT, FIRST, LAST, EXISTS, NEXT, PRIOR, EXTEND, TRIM, DELETE, LIMIT.

A user-defined exception is declared in the DECLARE section with type EXCEPTION, and it is not raised automatically by Oracle. It must be raised explicitly with the RAISE statement (inside the executable section or even inside a handler to re-raise it), after which control passes to the matching handler in the EXCEPTION section.

An alternative is RAISE_APPLICATION_ERROR(error_number, message), which raises an error with a custom number between -20000 and -20999 to the calling program.

Example

DECLARE
  e_invalid_age EXCEPTION;
  v_age NUMBER := 15;
BEGIN
  IF v_age < 18 THEN
    RAISE e_invalid_age;           -- explicit raise
  END IF;
  DBMS_OUTPUT.PUT_LINE('Eligible to vote');
EXCEPTION
  WHEN e_invalid_age THEN
    DBMS_OUTPUT.PUT_LINE('Age must be at least 18');
END;
/

Output: Age must be at least 18.

Using RAISE_APPLICATION_ERROR:

IF v_age < 18 THEN
  RAISE_APPLICATION_ERROR(-20010, 'Age must be at least 18');
END IF;

A row-level trigger is a database trigger created with the FOR EACH ROW clause. It fires once for every row affected by the triggering INSERT, UPDATE or DELETE statement, so an UPDATE touching 100 rows fires it 100 times. Inside the trigger body the correlation names :OLD (row before the change) and :NEW (row after the change) give access to column values. A statement-level trigger, in contrast, fires only once per statement.

BEFORE row trigger. It fires before each row is written to the table. It is used to validate data, derive column values, or reject a change before it happens. It is the only kind of trigger in which the :NEW values can be assigned.

CREATE OR REPLACE TRIGGER trg_emp_before_ins
BEFORE INSERT OR UPDATE ON emp
FOR EACH ROW
BEGIN
  :NEW.ename := UPPER(:NEW.ename);      -- modify the incoming value
  IF :NEW.sal < 0 THEN
    RAISE_APPLICATION_ERROR(-20001, 'Salary cannot be negative');
  END IF;
END;
/

Here every inserted or updated employee name is stored in capitals and a negative salary is rejected before it reaches the table.

AFTER row trigger. It fires after each row has been changed. It is used for auditing, logging, or updating other tables, because the change is already made. :NEW can be read but not modified.

CREATE OR REPLACE TRIGGER trg_emp_after_upd
AFTER UPDATE OF sal ON emp
FOR EACH ROW
BEGIN
  INSERT INTO sal_audit(empno, old_sal, new_sal, changed_on)
  VALUES (:OLD.empno, :OLD.sal, :NEW.sal, SYSDATE);
END;
/

Whenever a salary is updated, one audit row per employee is written recording the old salary, the new salary and the date.

A package is a schema object that groups logically related PL/SQL types, variables, constants, cursors, exceptions, procedures and functions under one name. It is stored in the database and compiled once. Its advantages are modularity, information hiding (private items), better performance (the whole package is loaded into memory on first call), easier maintenance, and support for overloading.

A package consists of two parts.

1. Package specification (interface). It declares the public items that can be used from outside. It contains no implementation of subprograms.

CREATE OR REPLACE PACKAGE emp_pkg IS
  g_max_sal CONSTANT NUMBER := 100000;
  PROCEDURE raise_sal(p_empno NUMBER, p_pct NUMBER);
  FUNCTION get_sal(p_empno NUMBER) RETURN NUMBER;
END emp_pkg;
/

2. Package body (implementation). It defines the code of the subprograms declared in the specification and may also contain private variables and subprograms visible only inside the package. It may also hold an optional initialization block that runs once per session.

CREATE OR REPLACE PACKAGE BODY emp_pkg IS
  PROCEDURE raise_sal(p_empno NUMBER, p_pct NUMBER) IS
  BEGIN
    UPDATE emp SET sal = sal * (1 + p_pct/100) WHERE empno = p_empno;
  END;

  FUNCTION get_sal(p_empno NUMBER) RETURN NUMBER IS
    v_sal emp.sal%TYPE;
  BEGIN
    SELECT sal INTO v_sal FROM emp WHERE empno = p_empno;
    RETURN v_sal;
  END;
END emp_pkg;
/

Use: EXEC emp_pkg.raise_sal(7369, 10); and SELECT emp_pkg.get_sal(7369) FROM dual;

Difference between stored procedure and stored function

Procedure example

CREATE OR REPLACE PROCEDURE show_name(p_id NUMBER) IS
  v_name emp.ename%TYPE;
BEGIN
  SELECT ename INTO v_name FROM emp WHERE empno = p_id;
  DBMS_OUTPUT.PUT_LINE('Employee: ' || v_name);
END;
/
EXEC show_name(7369);

Function example

CREATE OR REPLACE FUNCTION get_name(p_id NUMBER) RETURN VARCHAR2 IS
  v_name emp.ename%TYPE;
BEGIN
  SELECT ename INTO v_name FROM emp WHERE empno = p_id;
  RETURN v_name;
END;
/
SELECT get_name(7369) FROM dual;

Parameterized stored procedure. A procedure that accepts parameters lets one piece of code work on different inputs and send results back. Each parameter has a mode: IN (read-only input, default), OUT (value returned to caller) and IN OUT (passed in and returned changed). Parameters can be passed by position, by name (p_id => 7369), and may have default values.

CREATE OR REPLACE PROCEDURE raise_salary(
    p_empno IN  emp.empno%TYPE,
    p_pct   IN  NUMBER DEFAULT 10,
    p_newsal OUT emp.sal%TYPE) IS
BEGIN
  UPDATE emp SET sal = sal + sal * p_pct / 100
  WHERE empno = p_empno
  RETURNING sal INTO p_newsal;
END;
/
DECLARE
  v_new emp.sal%TYPE;
BEGIN
  raise_salary(7369, 20, v_new);
  DBMS_OUTPUT.PUT_LINE('New salary: ' || v_new);
END;
/

The call increases the salary of employee 7369 by 20% and returns the new salary through the OUT parameter.

(a) Parent and child tables with referential integrity

CREATE TABLE department (
  dept_id   NUMBER PRIMARY KEY,
  dept_name VARCHAR2(30) NOT NULL
);

CREATE TABLE employee (
  emp_id   NUMBER PRIMARY KEY,
  emp_name VARCHAR2(30) NOT NULL,
  salary   NUMBER(10,2),
  dept_id  NUMBER,
  CONSTRAINT fk_emp_dept FOREIGN KEY (dept_id)
    REFERENCES department(dept_id)
);

department is the parent and employee the child. A row in employee must have a dept_id that exists in department (or NULL), and a department with employees cannot be deleted unless ON DELETE CASCADE or ON DELETE SET NULL is specified.

Sample data:

INSERT INTO department VALUES (10, 'Sales');
INSERT INTO department VALUES (20, 'IT');
INSERT INTO department VALUES (30, 'Finance');
INSERT INTO employee VALUES (1, 'Ram', 40000, 10);
INSERT INTO employee VALUES (2, 'Sita', 50000, 20);
INSERT INTO employee VALUES (3, 'Hari', 35000, NULL);

(b) Inner join (natural join)

SELECT e.emp_id, e.emp_name, d.dept_name
FROM employee e INNER JOIN department d
ON e.dept_id = d.dept_id;

-- natural join (joins on the common column dept_id)
SELECT emp_name, dept_name
FROM employee NATURAL JOIN department;

Only matching rows are returned: Ram-Sales and Sita-IT.

(c) Outer joins

-- Left outer: all employees, matching departments (Hari with NULL)
SELECT e.emp_name, d.dept_name
FROM employee e LEFT OUTER JOIN department d ON e.dept_id = d.dept_id;

-- Right outer: all departments, matching employees (Finance with NULL)
SELECT e.emp_name, d.dept_name
FROM employee e RIGHT OUTER JOIN department d ON e.dept_id = d.dept_id;

-- Full outer: all rows of both tables
SELECT e.emp_name, d.dept_name
FROM employee e FULL OUTER JOIN department d ON e.dept_id = d.dept_id;

Left outer keeps unmatched rows of the left table, right outer keeps those of the right table, and full outer keeps both.

(d) Cross join

SELECT e.emp_name, d.dept_name
FROM employee e CROSS JOIN department d;

It returns the Cartesian product: every employee paired with every department, here $3 \times 3 = 9$ rows.

Implicit vs explicit cursors

A cursor is a pointer to the private SQL work area where Oracle stores the result of a SQL statement.

Program to display name and salary using an explicit cursor

SET SERVEROUTPUT ON;
DECLARE
  CURSOR c_emp IS                       -- 1. DECLARE the cursor
    SELECT ename, sal FROM emp;
  v_name emp.ename%TYPE;
  v_sal  emp.sal%TYPE;
BEGIN
  OPEN c_emp;                           -- 2. OPEN: executes the query
  LOOP
    FETCH c_emp INTO v_name, v_sal;     -- 3. FETCH: one row into variables
    EXIT WHEN c_emp%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(v_name || '  ' || v_sal);
  END LOOP;
  CLOSE c_emp;                          -- 4. CLOSE: release the work area
END;
/

Control steps

1. Declare the cursor with its SELECT statement in the declaration section.
2. Open the cursor; the query is executed and the active set is identified.
3. Fetch rows one at a time into variables; the pointer advances after each fetch, and %NOTFOUND becomes true when no row is left.
4. Close the cursor to free memory.

Database programming is the writing of programs that interact with a database to store, retrieve, modify and process data, using SQL together with a programming language. It includes embedded SQL, stored procedures, functions, triggers and packages (for example PL/SQL), as well as application access through APIs such as JDBC or ODBC.

Procedural vs non-procedural access

Example. Non-procedural: SELECT ename FROM emp WHERE sal > 30000; Procedural: open a cursor, loop over every row, test sal > 30000 and print the name.

Subquery. A subquery (nested or inner query) is a SELECT statement written inside another SQL statement (in WHERE, HAVING, FROM or SELECT). The inner query runs first and its result is used by the outer query.

SELECT ename, sal FROM emp
WHERE sal > (SELECT AVG(sal) FROM emp);

GROUP BY. It groups rows that have the same values in the given columns into summary rows, so that aggregate functions (COUNT, SUM, AVG, MAX, MIN) are computed for each group.

SELECT dept_id, COUNT(*) AS total_emp, AVG(sal) AS avg_sal
FROM emp
GROUP BY dept_id;

HAVING. It filters groups after grouping, using conditions on aggregate values. WHERE filters individual rows before grouping and cannot use aggregates, while HAVING works on the groups.

SELECT dept_id, AVG(sal) AS avg_sal
FROM emp
WHERE sal > 10000
GROUP BY dept_id
HAVING AVG(sal) > 30000;

This lists only those departments whose average salary exceeds 30000, considering employees earning more than 10000.

Parameter modes define how the actual argument passed to a PL/SQL procedure or function is used.

IN (default). The value is passed into the subprogram and is read-only; it cannot be assigned inside. The actual argument can be a literal, variable or expression.

OUT. The parameter is meant to send a value back to the caller. It starts as NULL inside the subprogram, is assigned a value there, and that value is returned when the subprogram ends. The actual argument must be a variable.

IN OUT. The initial value is passed in, can be read and changed, and the changed value is returned to the caller. The actual argument must be a variable.

CREATE OR REPLACE PROCEDURE demo_modes(
    p_a IN     NUMBER,      -- input only
    p_b OUT    NUMBER,      -- output only
    p_c IN OUT NUMBER) IS   -- both
BEGIN
  p_b := p_a * 2;           -- assign OUT parameter
  p_c := p_c + p_a;         -- read and modify IN OUT parameter
END;
/
DECLARE
  x NUMBER := 5; y NUMBER; z NUMBER := 10;
BEGIN
  demo_modes(x, y, z);
  DBMS_OUTPUT.PUT_LINE('y=' || y || ', z=' || z);
END;
/

Output: y=10, z=15. x is unchanged because it is an IN parameter, y receives $5 \times 2 = 10$, and z becomes $10 + 5 = 15$.

A trigger is a stored PL/SQL block associated with a table, view, schema or database that the database executes automatically when a specified event occurs, such as INSERT, UPDATE or DELETE. Triggers are used for auditing, enforcing complex business rules, validation and maintaining derived data.

Syntax of a row-level trigger

CREATE [OR REPLACE] TRIGGER trigger_name
{BEFORE | AFTER | INSTEAD OF}
{INSERT | UPDATE [OF column] | DELETE} [OR ...]
ON table_name
[REFERENCING OLD AS old NEW AS new]
FOR EACH ROW
[WHEN (condition)]
DECLARE
  -- declarations (optional)
BEGIN
  -- trigger body
EXCEPTION
  -- exception handlers (optional)
END;
/

FOR EACH ROW makes the trigger row-level, so it fires once per affected row, and :OLD and :NEW refer to the row's values before and after the change.

Example:

CREATE OR REPLACE TRIGGER trg_log_delete
AFTER DELETE ON emp
FOR EACH ROW
BEGIN
  INSERT INTO emp_log VALUES (:OLD.empno, :OLD.ename, SYSDATE);
END;
/

SET SERVEROUTPUT ON;
DECLARE
  CURSOR c_emp IS
    SELECT ename FROM employee;         -- declare the explicit cursor
  v_name employee.ename%TYPE;
BEGIN
  OPEN c_emp;                           -- open
  LOOP
    FETCH c_emp INTO v_name;            -- fetch one row at a time
    EXIT WHEN c_emp%NOTFOUND;           -- stop when no more rows
    DBMS_OUTPUT.PUT_LINE(v_name);
  END LOOP;
  CLOSE c_emp;                          -- close
END;
/

The cursor is declared with the query, opened to execute it, fetched row by row inside a loop until c_emp%NOTFOUND is true, and finally closed. Each employee name is printed on its own line.

A constant in PL/SQL is a named memory location whose value is assigned at declaration and cannot be changed afterwards. Attempting to assign to it later raises a compilation error. Constants make code readable and prevent accidental modification of fixed values such as tax rates or $\pi$.

Syntax

constant_name CONSTANT datatype [NOT NULL] := value;

The keyword CONSTANT is required and it must be initialized in the declaration (DEFAULT may be used instead of :=).

Example

DECLARE
  pi       CONSTANT NUMBER := 3.14159;
  vat_rate CONSTANT NUMBER(4,2) := 13;
  radius   NUMBER := 5;
  area     NUMBER;
BEGIN
  area := pi * radius * radius;
  DBMS_OUTPUT.PUT_LINE('Area = ' || area);
  -- pi := 3.14;   -- illegal: cannot assign to a constant
END;
/

Output: Area = 78.53975, since $3.14159 \times 5 \times 5 = 78.53975$.

Anonymous block example:

BEGIN
  DBMS_OUTPUT.PUT_LINE('Hello');
END;
/

Named block example:

CREATE OR REPLACE PROCEDURE say_hello IS
BEGIN
  DBMS_OUTPUT.PUT_LINE('Hello');
END;
/
EXEC say_hello;

Stored procedure. A stored procedure is a named PL/SQL (or SQL) block compiled and stored in the database that performs a specific task and is executed by calling its name. It may take IN, OUT and IN OUT parameters. Benefits: code reuse, reduced network traffic, better performance, centralized business logic and security through privileges.

CREATE OR REPLACE PROCEDURE add_dept(p_id NUMBER, p_name VARCHAR2) IS
BEGIN
  INSERT INTO department VALUES (p_id, p_name);
  COMMIT;
END;
/
EXEC add_dept(40, 'HR');

Views. A view is a virtual table defined by a stored SELECT query. It stores no data of its own; it shows data from base tables. Views simplify complex queries, provide security by hiding columns and rows, and give logical data independence.

Creation

CREATE VIEW emp_it AS
SELECT emp_id, emp_name, salary
FROM employee
WHERE dept_id = 20;

SELECT * FROM emp_it;

WITH READ ONLY or WITH CHECK OPTION can be added to restrict DML through the view.

Modification. The definition is changed by recreating it:

CREATE OR REPLACE VIEW emp_it AS
SELECT emp_id, emp_name, salary, dept_id
FROM employee
WHERE dept_id = 20;

DML on a simple view (one base table, no aggregates or DISTINCT) modifies the base table, for example UPDATE emp_it SET salary = 55000 WHERE emp_id = 2;.

Deletion

DROP VIEW emp_it;

Dropping a view removes only its definition; the base tables and their data are not affected.

Structure of a PL/SQL block

A PL/SQL block has up to three sections:

1. Declaration (DECLARE) - optional; declares variables, constants, cursors and user-defined exceptions.
2. Executable (BEGIN ... END) - mandatory; contains the SQL and procedural statements.
3. Exception handling (EXCEPTION) - optional; handles runtime errors.

DECLARE
  v_name emp.ename%TYPE;
  v_sal  emp.sal%TYPE;
BEGIN
  SELECT ename, sal INTO v_name, v_sal FROM emp WHERE empno = 7369;
  DBMS_OUTPUT.PUT_LINE(v_name || ' earns ' || v_sal);
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('No such employee');
END;
/

Blocks can be nested, and the block ends with END; followed by / in SQL*Plus.

Exception handling. An exception is a runtime error or warning condition. When it is raised, normal execution stops and control passes to the matching handler in the EXCEPTION section; if none matches, the exception propagates to the enclosing block. The general form is WHEN exception_name THEN statements; and WHEN OTHERS THEN catches all remaining ones.

Predefined exceptions are named by Oracle and raised automatically, such as NO_DATA_FOUND (SELECT INTO returns no row), TOO_MANY_ROWS, ZERO_DIVIDE, DUP_VAL_ON_INDEX and INVALID_NUMBER.

DECLARE
  a NUMBER := 10; b NUMBER := 0; c NUMBER;
BEGIN
  c := a / b;
EXCEPTION
  WHEN ZERO_DIVIDE THEN
    DBMS_OUTPUT.PUT_LINE('Cannot divide by zero');
END;
/

User-defined exceptions are declared by the programmer, raised explicitly with RAISE, and handled in the EXCEPTION section.

DECLARE
  e_low_balance EXCEPTION;
  v_bal NUMBER := 500;
BEGIN
  IF v_bal < 1000 THEN
    RAISE e_low_balance;
  END IF;
EXCEPTION
  WHEN e_low_balance THEN
    DBMS_OUTPUT.PUT_LINE('Balance below minimum');
END;
/

RAISE_APPLICATION_ERROR(-20001, 'message') may also be used to raise an error with a custom number (-20000 to -20999) and message.

Cursor parameters. An explicit cursor can be declared with parameters so the same cursor can be opened with different values each time. The parameters are given in the declaration with a data type (no size) and values are supplied in OPEN or in a cursor FOR loop. They are used in the WHERE clause of the cursor query.

DECLARE
  CURSOR c_emp(p_dept NUMBER) IS
    SELECT ename, sal FROM emp WHERE deptno = p_dept;
BEGIN
  FOR r IN c_emp(10) LOOP
    DBMS_OUTPUT.PUT_LINE(r.ename || ' ' || r.sal);
  END LOOP;
  FOR r IN c_emp(20) LOOP
    DBMS_OUTPUT.PUT_LINE(r.ename || ' ' || r.sal);
  END LOOP;
END;
/

The same cursor lists employees of department 10 and then of department 20.

Iterative control structures in PL/SQL

1. Basic (simple) LOOP. Repeats indefinitely until an EXIT or EXIT WHEN is executed.

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

2. WHILE LOOP. The condition is tested before each iteration; the body runs while it is true.

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

3. FOR LOOP. The counter is implicitly declared, increases by 1 over a fixed range, and REVERSE counts downward.

BEGIN
  FOR i IN 1..5 LOOP
    DBMS_OUTPUT.PUT_LINE(i);
  END LOOP;
  FOR i IN REVERSE 1..3 LOOP
    DBMS_OUTPUT.PUT_LINE(i);
  END LOOP;
END;
/

4. Cursor FOR LOOP (a special FOR loop) opens, fetches and closes a cursor automatically, as shown in the cursor example above.

EXIT, EXIT WHEN and CONTINUE can be used to leave a loop or skip an iteration, and loops may be labelled and nested.