1 of 28

RDBMS

Diploma – 3rd Semester, Computer Engineering

Unit 5

PL/SQL and Triggers

Prepared by:

Bharti D. Mulchandani

Lecturer, Computer Engineering Department

R. C. Technical Institute, Ahmedabad

2 of 28

Course Outcome (CO)

Develop PL/SQL programs incorporating control structures, exceptions, cursors, procedures, functions, and triggers for database automation.

BDM | RDBMS – Unit 5

1

3 of 28

5.1 What is PL/SQL?

  • PL/SQL = Procedural Language extensions to SQL — Oracle's procedural programming language, built around SQL.
  • Adds programming constructs SQL lacks on its own: variables, loops, conditions, error handling, and reusable blocks of code.
  • A PL/SQL program is organized into 'blocks', each with up to three sections.

DECLARE -- optional: variables, constants, cursors declared here

BEGIN -- mandatory: the executable SQL/PL-SQL statements

EXCEPTION -- optional: error-handling code

END;

BDM | RDBMS – Unit 5

2

4 of 28

Advantages of PL/SQL over SQL

Supports variables, loops & conditions

Processes a whole block in one trip to the server (faster)

Handles run-time errors gracefully

Reusable via procedures, functions & packages

Enables modular, structured code design

Tightly integrated with SQL statements

  • Key idea: plain SQL sends one statement at a time to the server; a PL/SQL block sends many statements together, cutting network round-trips.

BDM | RDBMS – Unit 5

3

5 of 28

PL/SQL Data Types & %TYPE

  • PL/SQL supports the same base types as SQL (NUMBER, VARCHAR2, DATE, CHAR, BOOLEAN, …) plus its own composite types.
  • %TYPE anchors a variable's data type to a table column — if the column's type ever changes, the variable adapts automatically.

DECLARE

v_name student.sname%TYPE; -- same type & size as student.sname

v_marks student.marks%TYPE;

BEGIN

SELECT sname, marks INTO v_name, v_marks FROM student WHERE roll_no = 1;

DBMS_OUTPUT.PUT_LINE(v_name || ' scored ' || v_marks);

END;

BDM | RDBMS – Unit 5

4

6 of 28

PL/SQL Data Types — %ROWTYPE

  • %ROWTYPE declares a single record variable that mirrors an ENTIRE row of a table (or cursor) — one field per column.
  • Individual fields are accessed with dot notation: record_name.column_name.

DECLARE

v_student student%ROWTYPE; -- one field for every column in 'student'

BEGIN

SELECT * INTO v_student FROM student WHERE roll_no = 1;

DBMS_OUTPUT.PUT_LINE(v_student.sname || ' - ' || v_student.marks);

END;

  • %ROWTYPE saves you from declaring (and keeping in sync) one variable per column by hand.

BDM | RDBMS – Unit 5

5

7 of 28

5.2 Control Structures — IF / ELSIF

  • PL/SQL control structures fall into three groups: Conditional (IF, CASE), Iterative (loops), and Sequential (GOTO, NULL).

IF condition1 THEN

statements;

ELSIF condition2 THEN

statements;

ELSE

statements;

END IF;

IF v_marks >= 75 THEN

DBMS_OUTPUT.PUT_LINE('Distinction');

ELSIF v_marks >= 50 THEN

DBMS_OUTPUT.PUT_LINE('Pass');

ELSE

DBMS_OUTPUT.PUT_LINE('Fail');

END IF;

BDM | RDBMS – Unit 5

6

8 of 28

Control Structures — CASE

  • An alternative to a long IF-ELSIF chain — cleaner when checking one expression against several possible values.

CASE

WHEN v_marks >= 75 THEN DBMS_OUTPUT.PUT_LINE('Distinction');

WHEN v_marks >= 50 THEN DBMS_OUTPUT.PUT_LINE('Pass');

ELSE DBMS_OUTPUT.PUT_LINE('Fail');

END CASE;

  • A 'simple CASE' compares one expression for equality (CASE v_grade WHEN 'A' THEN … ); the 'searched CASE' above allows any condition per WHEN.

BDM | RDBMS – Unit 5

7

9 of 28

Iterative Structures — LOOP & WHILE LOOP

Basic LOOP — runs until an explicit EXIT

i := 1;

LOOP

DBMS_OUTPUT.PUT_LINE(i);

i := i + 1;

EXIT WHEN i > 5;

END LOOP;

WHILE LOOP — condition checked BEFORE each pass

i := 1;

WHILE i <= 5 LOOP

DBMS_OUTPUT.PUT_LINE(i);

i := i + 1;

END LOOP;

BDM | RDBMS – Unit 5

8

10 of 28

Iterative Structures — FOR LOOP

  • Automatically declares, increments, and checks the loop counter — no manual initialization needed.

FOR i IN 1..5 LOOP

DBMS_OUTPUT.PUT_LINE(i);

END LOOP;

FOR i IN REVERSE 1..5 LOOP -- counts down: 5, 4, 3, 2, 1

DBMS_OUTPUT.PUT_LINE(i);

END LOOP;

  • The loop counter (i) is implicitly declared, only exists inside the loop, and cannot be assigned to.

BDM | RDBMS – Unit 5

9

11 of 28

Sequential Control — GOTO & NULL

  • Rarely used in modern code, but part of the syllabus's 'sequential statement' category.

IF v_marks < 0 THEN

GOTO invalid_marks;

END IF;

...

<<invalid_marks>>

DBMS_OUTPUT.PUT_LINE('Invalid marks entered');

IF v_marks IS NULL THEN

NULL; -- placeholder: do nothing, intentionally

END IF;

  • NULL as a statement is a deliberate no-op — useful to satisfy a branch that needs SOME statement but should do nothing.

BDM | RDBMS – Unit 5

10

12 of 28

5.3 Cursors — Introduction

  • A cursor is a pointer to the private memory area Oracle uses to process a SQL statement — essentially a handle on a query's result set.
  • Needed because PL/SQL processes data ROW BY ROW, while SQL naturally returns a whole set of rows at once.
  • Two kinds: Implicit cursors (Oracle creates & manages automatically) and Explicit cursors (the programmer declares & controls them).

BDM | RDBMS – Unit 5

11

13 of 28

Implicit Cursors

  • Automatically created by Oracle for every INSERT, UPDATE, DELETE, and single-row SELECT INTO — no DECLARE needed.

UPDATE student SET marks = marks + 5 WHERE dept_id = 10;

DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT || ' rows updated');

Attribute

Meaning

SQL%FOUND

TRUE if the last statement affected at least one row

SQL%NOTFOUND

TRUE if it affected zero rows

SQL%ROWCOUNT

Number of rows affected

BDM | RDBMS – Unit 5

12

14 of 28

Explicit Cursors

  • Used for a SELECT that can return MULTIPLE rows — declared explicitly, then processed one row at a time in four steps.

DECLARE

CURSOR c_student IS SELECT sname, marks FROM student;

v_name student.sname%TYPE;

v_marks student.marks%TYPE;

BEGIN

OPEN c_student;

LOOP

FETCH c_student INTO v_name, v_marks;

EXIT WHEN c_student%NOTFOUND;

DBMS_OUTPUT.PUT_LINE(v_name || ': ' || v_marks);

END LOOP;

CLOSE c_student;

END;

BDM | RDBMS – Unit 5

13

15 of 28

Cursor FOR Loop & Cursor Attributes

  • A cursor FOR loop automatically OPENs, FETCHes every row, and CLOSEs the cursor — no manual OPEN/FETCH/EXIT/CLOSE needed.

FOR rec IN (SELECT sname, marks FROM student) LOOP

DBMS_OUTPUT.PUT_LINE(rec.sname || ': ' || rec.marks);

END LOOP;

Cursor Attribute

Meaning

%FOUND

TRUE if the last FETCH returned a row

%NOTFOUND

TRUE if the last FETCH found no row (used to EXIT)

%ROWCOUNT

Number of rows fetched so far

%ISOPEN

TRUE if the cursor is currently open

BDM | RDBMS – Unit 5

14

16 of 28

5.4 Procedures — Create & Execute

  • A stored procedure is a named, reusable PL/SQL block saved in the database — called by name whenever needed.

CREATE OR REPLACE PROCEDURE raise_marks (

p_roll IN student.roll_no%TYPE, p_amount IN NUMBER)

IS

BEGIN

UPDATE student SET marks = marks + p_amount WHERE roll_no = p_roll;

COMMIT;

END raise_marks;

EXEC raise_marks(1, 5); -- or: CALL raise_marks(1, 5);

BDM | RDBMS – Unit 5

15

17 of 28

Procedures — IN, OUT & IN OUT Parameters

Mode

Direction

Default?

IN

Passed INTO the procedure (read-only inside)

Yes — the default mode

OUT

Passed back OUT to the caller

No — must be stated explicitly

IN OUT

Value goes in, can be changed, and comes back out

No — must be stated explicitly

CREATE OR REPLACE PROCEDURE get_marks (

p_roll IN student.roll_no%TYPE, p_marks OUT student.marks%TYPE)

IS

BEGIN

SELECT marks INTO p_marks FROM student WHERE roll_no = p_roll;

END;

BDM | RDBMS – Unit 5

16

18 of 28

5.4 Functions — Create & Execute

  • A function is like a procedure, but it MUST return exactly one value using the RETURN keyword — so it can be used directly inside a SQL expression.

CREATE OR REPLACE FUNCTION get_grade (p_marks IN NUMBER)

RETURN VARCHAR2

IS

BEGIN

IF p_marks >= 75 THEN RETURN 'Distinction';

ELSIF p_marks >= 50 THEN RETURN 'Pass';

ELSE RETURN 'Fail';

END IF;

END get_grade;

SELECT sname, get_grade(marks) FROM student;

BDM | RDBMS – Unit 5

17

19 of 28

Procedure vs Function

Procedure

Function

May or may not return a value

MUST return exactly one value

Called as a standalone statement (EXEC)

Called inside an expression (e.g. within SELECT)

Can have IN, OUT, and IN OUT parameters

Typically only IN parameters

Used to PERFORM an action

Used to COMPUTE and return a value

BDM | RDBMS – Unit 5

18

20 of 28

5.3 Exception Handling — Predefined Exceptions

  • An exception is a run-time error. PL/SQL lets you 'trap' it in the EXCEPTION section instead of crashing the whole block.

Exception

Raised when…

NO_DATA_FOUND

A SELECT INTO returns zero rows

TOO_MANY_ROWS

A SELECT INTO returns more than one row

ZERO_DIVIDE

Dividing a number by zero

VALUE_ERROR

A value doesn't fit its assigned variable type

OTHERS

Catches any exception not handled above

BDM | RDBMS – Unit 5

19

21 of 28

Exception Handling — Example & User-Defined Exceptions

BEGIN

SELECT sname INTO v_name FROM student WHERE roll_no = 999;

EXCEPTION

WHEN NO_DATA_FOUND THEN

DBMS_OUTPUT.PUT_LINE('No student with that roll number');

WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE('Unexpected error: ' || SQLERRM);

END;

DECLARE

invalid_marks EXCEPTION;

BEGIN

IF v_marks > 100 THEN

RAISE invalid_marks;

END IF;

EXCEPTION

WHEN invalid_marks THEN

DBMS_OUTPUT.PUT_LINE('Marks cannot exceed 100');

END;

BDM | RDBMS – Unit 5

20

22 of 28

5.5 Triggers — Fundamentals

  • A trigger is a stored PL/SQL block that fires AUTOMATICALLY when a specified database event occurs — INSERT, UPDATE, or DELETE.
  • Used for automation: auditing changes, enforcing complex business rules, keeping derived data in sync, logging.

CREATE OR REPLACE TRIGGER trigger_name

{BEFORE | AFTER} {INSERT | UPDATE | DELETE} ON table_name

[FOR EACH ROW]

BEGIN

-- trigger body

END;

BDM | RDBMS – Unit 5

21

23 of 28

Types of Triggers — BEFORE vs AFTER

BEFORE Trigger

  • Fires BEFORE the triggering statement executes.
  • Can validate or MODIFY the data before it's written.
  • Common use: default values, input validation.

AFTER Trigger

  • Fires AFTER the triggering statement completes.
  • Cannot change the row being inserted/updated — the change already happened.
  • Common use: audit logging, cascading updates to other tables.

BDM | RDBMS – Unit 5

22

24 of 28

Types of Triggers — FOR EACH ROW vs FOR EACH STATEMENT

​

FOR EACH ROW

FOR EACH STATEMENT (default)

Fires

Once per affected row

Once per triggering statement, regardless of row count

Access to data

Can use :OLD and :NEW to read/modify the row's values

Cannot reference individual row values

Typical use

Per-row validation, computed columns

One summary audit entry, bulk checks

  • :OLD.column_name — the row's value before the change; :NEW.column_name — the value after. :OLD is unavailable on INSERT, :NEW is unavailable on DELETE.

BDM | RDBMS – Unit 5

23

25 of 28

Trigger Example — Audit Log

  • Automatically record every salary change made to the 'employee' table:

CREATE OR REPLACE TRIGGER trg_salary_audit

AFTER UPDATE OF salary ON employee

FOR EACH ROW

BEGIN

INSERT INTO salary_audit (emp_id, old_salary, new_salary, changed_on)

VALUES (:OLD.emp_id, :OLD.salary, :NEW.salary, SYSDATE);

END;

UPDATE employee SET salary = salary + 2000 WHERE emp_id = 101;

-- trigger fires automatically, inserting one row into salary_audit

BDM | RDBMS – Unit 5

24

26 of 28

Problem Solving Practice

  • 1. Write a PL/SQL block that prints 'Even' or 'Odd' for a given number using IF-ELSE.
  • 2. Write a cursor-based block that prints every student's name and grade (using IF/CASE to compute the grade).
  • 3. Create a function that returns the total number of students scoring above a given mark.
  • 4. Create a procedure that transfers a value from one account balance to another, using an exception handler for insufficient funds.
  • 5. Write a BEFORE INSERT trigger that automatically uppercases a student's name before it's stored.
  • 6. Write an AFTER DELETE trigger that logs deleted employee records into an audit table.

BDM | RDBMS – Unit 5

25

27 of 28

Quick Check: MCQs

1. Which PL/SQL block section is mandatory?

a) DECLARE b) BEGIN c) EXCEPTION d) None are mandatory

2. Which exception fires when SELECT INTO returns no rows?

a) TOO_MANY_ROWS b) NO_DATA_FOUND c) ZERO_DIVIDE d) VALUE_ERROR

3. Which cursor type must be explicitly declared, opened, and closed?

a) Implicit b) Explicit c) Both d) Neither

4. A function differs from a procedure mainly because it:

a) Cannot take parameters b) Must return a value c) Cannot use loops d) Runs faster

5. Which trigger timing can modify the row before it's saved?

a) AFTER b) INSTEAD OF c) BEFORE d) None

BDM | RDBMS – Unit 5

26

28 of 28

Thank You

Bharti D. Mulchandani

Lecturer, Computer Engineering Department

R. C. Technical Institute, Ahmedabad