PL/SQL Demo Examples
Practice scripts, one file each. Every demo is self-contained and commented; run it with SQL*Plus, sqlcl, or any Oracle client. Scripts that build their own table drop it first, so they can be re-run safely.
How to use: click View to open a file in the code viewer, read its comments, download it, then run it against your database.
sqlplus user/password@db @01_hello.sql # or: sqlcl user/password@db @01_hello.sql
Foundations
Variables, types, operators, and the two shapes of decision-making.
| # | Description | Link |
|---|---|---|
| 1 | Hello, World | View |
| 2 | Declaring Variables | View |
| 3 | Data Types & %TYPE | View |
| 4 | Operators & Expressions | View |
Control Flow
IF/CASE, loops, cursors, and exception handling.
| # | Description | Link |
|---|---|---|
| 5 | IF/ELSIF and CASE | View |
| 6 | LOOP, WHILE, FOR | View |
| 7 | Cursors & %ROWTYPE | View |
| 8 | Exception Handling | View |
Functions, Procedures & Packages
Stored, reusable, named PL/SQL.
| # | Description | Link |
|---|---|---|
| 9 | Function | View |
| 10 | Procedure | View |
| 11 | Package Specification | View |
| 12 | Package Body | View |
| 13 | Package Build/Test Script | View |
Advanced
Collections and triggers — the intermediate-to-expert features.
| # | Description | Link |
|---|---|---|
| 14 | Collections (Associative Array, Nested Table, VARRAY) | View |
| 15 | Triggers (BEFORE INSERT, :NEW) | View |
Study Project: A Bitwise Utility Package
A complete spec/body/test trio — the professional three-file package pattern, compiled and exercised in one script.
| # | Description | Link |
|---|---|---|
| 16 | bit_util — Package Specification | View |
| 17 | bit_util — Package Body | View |
| 18 | bit_util — Build & Test Script | View |
Study Project: A Small Order Schema
A three-table schema plus the joins, aggregates, and a view built on top of it — ties together Build and Transform.
| # | Description | Link |
|---|---|---|
| 19 | Build Schema (tables, constraints, seed data) | View |
| 20 | Transform / ETL (JOIN, GROUP BY, CTE, VIEW) | View |
Sample Data Files
Referenced from the Operate lesson's homework exercises.
| File | Used by |
|---|---|
| developer.csv | Operate — DML: INSERT homework |
| members.csv | Operate — SQL: WHERE homework |
Practice move: after running each demo, change one thing on purpose — remove a
WHEN OTHERS handler, drop the EXTEND call before assigning to a nested table, or comment out :NEW.price's check — and read the resulting error before fixing it.