RDBMS Source Note

[NOTE: This OS source note is only for studying purposes and this doesn’t contain all
the information from your syllabus. This source note is 100% AI generated]

UNIT I — INTRODUCTION & ER MODEL

1. INTRODUCTION TO DBMS
1.1 Data vs Information

●​ Data: Raw facts, figures, without context.​

E.g. “100”, “John”, “23/04/2025”
●​ Information: Processed, meaningful data that helps decision-making.​
E.g. “John has scored 100 marks on 23/04/2025”

1.2 Database & DBMS

●​ Database: A structured collection of persistent, organized data (for a particular

domain).
●​ DBMS (Database Management System): Software that manages the database,
providing interfaces, enforcing constraints, handling storage, queries, concurrency,
etc.

1.3 Objectives of DBMS

1.​ Data independence: Logical & physical independence

2.​ Efficient data access
3.​ Data integrity & security
4.​ Enforcement of constraints
5.​ Concurrent access & transaction management
6.​ Recovery from failure
7.​ Reduced redundancy & inconsistency

1.4 Advantages of DBMS

 ●​ Reduced data redundancy
●​ Data sharing among multiple users
●​ Data consistency & integrity
●​ Enforcing constraints
●​ Security & access control
●​ Backup & recovery
●​ Support transactions & concurrency
●​ Ease of data access (query languages)

1.5 Components of DBMS

Key functional modules:

Component Role / Responsibility

Storage Manager (File Manager) Manages disk space, data files, indexing

Buffer Manager / Cache Manages memory buffers, page

replacement

Query Processor Parses, optimizes, executes queries

Transaction Manager Ensures ACID properties

Recovery Manager Handles logs, rollback, crash recovery

Authorization / Security Manager Controls access rights

Data Dictionary / Catalog Stores metadata (schema, constraints)

User Interface / Application Interface Tools, front-end, APIs

1.6 DBMS Architecture

Levels / Abstraction Layers

●​ External Level (View level): What users see (views)

●​ Conceptual Level: Logical structure of entire database
●​ Internal Level: Physical storage structure

These three levels provide data independence.

Types of Architecture

●​ 1-Tier Architecture: DB + application on same system

●​ 2-Tier Architecture: Client-server (client app + DB server)
 ●​ 3-Tier Architecture: Client / application server / database server​
The 3-tier adds an intermediate layer for business logic, improving scalability &
security GeeksforGeeks+2SprinkleData+2

2. ER MODEL (Entity-Relationship Model)

This is the conceptual modeling tool to design the database before implementation.

2.1 Building Blocks of ER Diagram

●​ Entity Type / Entity Set: A “thing” or object in real world (e.g. Student, Department).
●​ Attributes: Properties of entities (e.g. StudentID, Name, DOB).
○​ Types of attributes: simple, composite, multivalued, derived, key.
●​ Relationship Type / Relationship Set: Associations among entities (e.g. Student
Enrolls in Course).
●​ Keys: Primary key uniquely identifies entity in its set.

2.2 Relationship Degree & Classification

●​ Degree: Number of entity sets participating in the relationship

○​ Binary (2 entities), Ternary (3), Unary (self relationship)
●​ Cardinality / Participation: How many instances of one entity relate to instances of
another (1:1, 1:N, N:M)
●​ Participation Constraints: Total (mandatory) or Partial (optional) participatio

2.3 ER Diagram → Tables (Relational Schema Mapping)

Steps to convert:

1.​ For each entity type, create a relation (table) with its attributes; primary key becomes
primary key column.
2.​ For each relationship:
○​ 1:1 → choose one side, include foreign key
○​ 1:N → put primary key of “1” side as foreign key in “N” side
○​ N:M → create a new relation with foreign keys referencing both entities, plus
any relationship attributes
3.​ Handle weak entities accordingly (include identifying relationship’s key).
4.​ For multi-valued attributes: create separate relation.

2.4 ISA Relationship (Specialization / Generalization)

●​ ISA / “is a”: Hierarchical relationship between supertype and subtype.

●​ Subtype inherits attributes of supertype, and may have its own attributes.
●​ Constraints / options: disjoint / overlapping, total / partial participation
●​ In relational mapping:
 ○​ Single table approach (all in one)
○​ Separate table for subtype with primary key = primary key of supertype
○​ One-to-one mapping

2.5 Constraints in ER Model

●​ Key constraints
●​ Participation constraints (total vs partial)
●​ Referential integrity
●​ Domain constraints (type, range)
●​ Cardinality constraints
●​ Disjointness / overlap constraints (in ISA)

2.6 Aggregation & Composition

●​ Aggregation: When you need to model a relationship about a relationship. You

treat a relationship set + its participating entities as a higher-level entity and relate it
to another entity.​
Example: Employee Works_On Project; relationship Works_On might relate to
Department via 1-to-many; then aggregate Works_On as one entity and link
Department to it. Database System Concepts+1
●​ Composition: Stronger form of aggregation: a part cannot exist independently of the
whole. E.g. Tree & Branch: branch cannot exist outside tree.
●​ Advantage: models complex relationships more naturally, avoids redundancy

You can see example diagrams above.

2.7 Advantages of ER Model

●​ Conceptual clarity: high-level & intuitive

●​ Good for communication with users
●​ Helps in designing relational schemas systematically
●​ Supports constraints, inheritance, aggregation
●​ Implementation-independent

UNIT II — RELATIONAL MODEL & QUERY LANGUAGES

1. RELATIONAL MODEL & CODD’S RULES

1.1 Codd’s Rules

●​ Edgar F. Codd defined a set of 13 rules (numbered 0 to 12) that a DBMS should
satisfy to be truly “relational.” Wikipedia
●​ Some key rules among them:
 1.​ Information Rule: All information stored in tables (relations).
2.​ Guaranteed Access Rule: Each atomic value is accessible via (table,
primary key, column).
3.​ Null Handling Rule: Nulls supported and handled consistently.
4.​ Catalog as Relational: The metadata (catalog) is stored in tables and
accessible via queries.
5.​ Comprehensive Data Sublanguage: The database must support a language
that can express queries, updates, integrity constraints, authorization,
transactions.
6.​ View Updatability: All theoretically updatable views must be updatable by the
system.
7.​ Physical & Logical Data Independence: Changes in storage or logical
schema shouldn’t affect application programs.
8.​ Integrity Independence: Integrity constraints stored in the catalog, not in
application code.
9.​ Distribution Independence: Users should not see whether data is
distributed.
10.​Nonsubversion Rule: Low-level access should not bypass integrity rules.
●​ Not all real DBMS satisfy all rules fully, but they’re ideal guidelines.

1.2 Relational Data Model

●​ Relation: A table with rows and columns.

●​ Tuple: A row in a relation.
●​ Attribute: A column (with name and domain).
●​ Domain: A set of permissible values (like integer, string).
●​ Schema of a relation: Name + its attributes.
●​ Instance (state): Set of tuples at a given time.

1.3 Keys & Integrity

●​ Superkey: A set of attributes that uniquely identify tuples.

●​ Candidate key: Minimal superkey (cannot remove any attribute and still be unique).
●​ Primary key: Chosen candidate key for a relation.
●​ Foreign key: An attribute (or set) in one relation that references primary key of
another relation.

Integrity Constraints:

1.​ Entity Integrity: No primary key value can be null.

2.​ Referential Integrity: A foreign key must either be null or match a primary key in the
referenced table.
3.​ Domain Constraints: Each attribute value must come from its domain.
4.​ Key Constraints: Uniqueness of keys.
5.​ Other constraints: Assertions, check constraints, etc.

2. RELATIONAL ALGEBRA
Relational algebra is a procedural (operational) query language — you tell how to get
data.

It operates on relations and returns relations (closure property).

2.1 Basic Operators

1.​ Selection (σ)

○​ Filters rows based on a condition.
○​ Notation: σ_{condition}(R)
○​ Example: σ_{age > 25}(Employee) Wikipedia
2.​ Projection (π)
○​ Chooses certain columns (attributes).
○​ Removes duplicates.
○​ Notation: π_{attr1, attr2,…}(R)
3.​ Rename (ρ)
○​ Rename relation or attribute names to avoid ambiguity.
4.​ Union (⋃)
○​ Combines tuples from two relations (same schema).
5.​ Set Difference (−)
○​ Tuples in R but not in S (same schema).
6.​ Cartesian Product (×)
○​ Combines every tuple of R with every tuple of S.

From these, we derive:

●​ Join (natural, theta, equi-join)

●​ Intersection
●​ Division

Relational algebra is foundational for query optimization and internal implementation.

2.2 Advantages & Limitations of Relational Algebra

Advantages:

●​ Well-defined and rigorous

●​ Basis for SQL queries (optimizers)
●​ Closed form (outputs are relations)
●​ Supports composition (build complex queries)

Limitations:

●​ Procedural — user must specify how to retrieve data, not just what
●​ Less intuitive for non-technical users
●​ Some complex queries (e.g. recursion, transitive closure) are not expressible
●​ Doesn’t directly express aggregation (sum, count, etc.)
3. RELATIONAL CALCULUS

Relational calculus is non-procedural (declarative) — you specify what you want, not how.

Two main forms:

3.1 Tuple Relational Calculus (TRC)

●​ Variables range over tuples.

●​ A typical form: { t | P(t) } where t is a tuple variable and P(t) is a predicate (logical
formula) involving t.
●​ Example: { t | t ∈ Student ∧ [Link] > 20 } (all students older than 20)
●​ Uses quantifiers (∃, ∀). Wikipedia

3.2 Domain Relational Calculus (DRC)

●​ Variables refer to domain values (attribute values), not entire tuples.

●​ Query form: { ⟨x1, x2, …, xn⟩ | P(x1, x2, …) }
●​ Example: { ⟨s, a⟩ | ∃c (Student(s, c, a) ∧ a > 20) }

Safety / Domain-independence: queries must return same result regardless of extraneous

domain values. Only domain-independent queries considered valid.

3.3 Equivalence & Codd’s Theorem

●​ Codd’s Theorem: Relational algebra and (domain-independent) relational calculus

have the same expressive power — any query expressible in one can be expressed
in the other. Wikipedia+2Wine Cellars Association+2
●​ They form the theoretical foundation for relational query languages.

4. QBE (Query By Example)

●​ Graphical / visual query language developed by Moshe Zloof at IBM in the 1970s.
GeeksforGeeks+2UW Computer Sciences+2
●​ Users fill in example tables (skeleton tables) to specify the query, without writing
SQL.
●​ QBE is essentially a graphical interface over domain relational calculus. CSE
UCSD+1
●​ For example, you show a table template and in “where” columns you put example
values or variables, and the system interprets that as a query.
●​ It’s intuitive for users not comfortable with textual query languages.

Features:

●​ Two-dimensional layout (tables shown horizontally).

●​ Conditions box, output box, and pattern box.
●​ Converts to SQL internally (under the hood).
UNIT III — RELATIONAL DATABASE DESIGN &
TRANSACTIONS

1. STRUCTURE OF A RELATIONAL DATABASE

●​ A relational database is composed of relations (tables).
●​ Each relation schema is defined by a set of attributes (columns) and a name.
●​ An instance (state) of relation is a set of tuples (rows) at a given time.
●​ The database schema consists of the schemas of all relations, plus constraints.
●​ The relational model enforces: keys, integrity constraints, relationships via
foreign keys, etc.

2. INTRODUCTION TO RELATIONAL DATABASE

DESIGN
2.1 Objectives of Good Design

●​ Eliminate redundancy (avoid unnecessary duplicate data)

●​ Prevent anomalies (insertion, update, deletion anomalies)
●​ Preserve data integrity
●​ Ensure lossless decomposition
●​ Dependency preservation (functional dependencies should still be enforced)
●​ Achieve normalization to suitable normal form

2.2 Tools / Concepts

●​ Functional dependency (FD)

●​ Closure of attribute sets
●​ Minimal cover / canonical cover
●​ Decomposition / projection / join
●​ Normalization theory (normal forms)

2.3 Redundancy & Data Anomaly

●​ Redundancy: repetition of same data in multiple places​

→ wastes space, causes inconsistency risk
●​ Anomalies:
○​ Insertion anomaly: you cannot insert data because other data is missing
○​ Deletion anomaly: deleting a row might lose other important data
○​ Update anomaly: you must update duplicate data in all places; if you miss
one, inconsistency arises
Designing well (via normalization) helps avoid these anomalies.

3. FUNCTIONAL DEPENDENCY
●​ A functional dependency X→YX → YX→Y means: in a relation, if two tuples agree
on attributes X, they must also agree on attributes Y.
●​ Trivial FD: if Y ⊆ X
●​ Non-trivial FD: Y is not subset of X
●​ Fully functional dependency: no proper subset of X determines Y
●​ Partial dependency: some subset of a composite key determines a non-key
attribute
●​ Transitive dependency: X → Y and Y → Z ⇒ X → Z (Y is non-prime)

You should know how to compute closure X+X^{+}X+ (the set of attributes determined by X)
and how to derive a minimal cover (minimize dependencies).

4. NORMALIZATION
Normalization is process of organizing relations to reduce redundancy and avoid anomalies,
through successive normal forms. Wikipedia+1

4.1 1st Normal Form (1NF)

●​ Relation is in 1NF if every attribute is atomic (indivisible), and there are no repeating
groups or nested relations. Wikipedia
●​ So multi-valued or composite attributes are broken into separate tables or attributes.

4.2 2nd Normal Form (2NF)

●​ A relation is in 2NF if:

1.​ It is already in 1NF
2.​ No partial dependency exists (no non-prime attribute depends on part of a
candidate key). Wikipedia

Example: If (A, B) is key, but C depends on A alone, that’s partial dependency — violates
2NF.

4.3 3rd Normal Form (3NF)

●​ A relation is in 3NF if:

1.​ It is in 2NF
2.​ No transitive dependency of non-prime attributes through other non-prime
attributes
 ●​ In other words: non-key attributes depend only on key (not via another non-key).

4.4 Boyce-Codd Normal Form (BCNF)

●​ A stricter version of 3NF.

●​ For every non-trivial functional dependency X→YX → YX→Y, X must be a
superkey.
●​ So in BCNF, no dependency violates the rule even if a relation is in 3NF. Wikipedia
●​ Note: Every BCNF is 3NF, but not every 3NF is BCNF. Some schemas in 3NF might
violate BCNF due to certain FDs.

5. TRANSACTION PROCESSING
●​ Transaction: A logical unit of work on the database (sequence of operations) that
must be executed fully or not at all.
●​ ACID properties guarantee correctness:
1.​ Atomicity – all or nothing
2.​ Consistency – database remains in valid state
3.​ Isolation – concurrent transactions do not interfere
4.​ Durability – once committed, changes persist despite failure
●​ Concurrency control and locking protocols (shared / exclusive locks, two-phase
locking) help maintain isolation.
●​ Techniques like serializability (ensuring effect is same as if transactions ran one
after another)
●​ Deadlocks, cascading rollback are issues to manage.
●​ Recovery & logging (undo/redo logs) ensure durability and atomicity in case of
crashes.

6. DATABASE SECURITY
●​ Ensures that only authorized users access data, and data are protected from
malicious or accidental threats.
●​ Access control: grant / revoke privileges (SELECT, INSERT, UPDATE, DELETE)
●​ Authorization models: role-based, discretionary, mandatory access control
●​ Encryption at rest / transit
●​ User accounts + authentication
●​ Audit trails / logging to track who did what

UNIT IV — SQL: Commands, Data Types, DDL, DML,

Queries & Constraints
1. INTRODUCTION TO SQL & COMMANDS

●​ SQL (Structured Query Language) is the standard language for managing relational
databases. Wikipedia+1
●​ SQL statements are divided into categories (sublanguages) based on their purpose:

Sub-language Purpose Examples / Commands

DDL (Data Define or modify CREATE, ALTER, DROP, TRUNCATE, RENAME

Definition database schema / GeeksforGeeks+2AlmaBetter+2
Language) structure

DML (Data Query or change the SELECT, INSERT, UPDATE, DELETE

Manipulation data in the tables BCcampus Open
Language) Publishing+2AlmaBetter+2

DQL (Data Query Retrieve / query data SELECT (with FROM, WHERE, etc.)
Language) (some treat it Wikipedia+2GeeksforGeeks+2
separately from DML)

DCL (Data Control Grant or revoke GRANT, REVOKE

Language) permissions / access GeeksforGeeks+2SitePoint+2

TCL (Transaction Manage transactions COMMIT, ROLLBACK, SAVEPOINT

Control Language) (group of DML GeeksforGeeks+2GeeksforGeeks+2
operations)

Note: Some sources include TRUNCATE in DML, others in DDL; your syllabus
lists “Truncation” under SQL, so treat it as a command to empty a table while
keeping its structure.

2. DATA TYPES

●​ SQL supports various data types for columns. Common ones include:
 Data Type Description / Usage

INT / INTEGER Whole numbers

SMALLINT, BIGINT Smaller / larger integer ranges

VARCHAR(n) / CHAR(n) Variable / fixed-length character strings

TEXT / CLOB Large text / character data

DATE, TIME, DATETIME, For date / time values

TIMESTAMP

FLOAT, DOUBLE, REAL Approximate numeric (decimal) types

DECIMAL(p, s) / Exact numeric with precision & scale

NUMERIC(p, s)

BOOLEAN TRUE / FALSE

BLOB Binary large object (for binary data)

●​ ​
When creating a table, you declare each column’s data type:

CREATE TABLE Student (

StudentID INT PRIMARY KEY,

Name VARCHAR(100),

DOB DATE,
 GPA DECIMAL(4, 2)

);

●​ Choosing appropriate data types helps with storage efficiency, validation, and data
integrity.

3. DDL (Data Definition Language)

DDL commands let you define or alter schema (structure of tables, databases, constraints).
(“Commands – DDL” part of your portion)

●​ CREATE
○​ Create new tables, indexes, views, etc.

Syntax:​
​
CREATE TABLE TableName (

col1 datatype [constraint],

col2 datatype [constraint],

...

[table_constraints]

);

○​
●​ ALTER
○​ Modify existing table structure: add / drop / modify columns, change
datatypes, add constraints.​

ALTER TABLE TableName

ADD columnName datatype;

●​
●​ DROP​

○​ Remove tables, views, or other objects (including all data).​

DROP TABLE TableName;

●​
●​ TRUNCATE
○​ Remove all records in a table, and reset storage (but keep table definition).
○​ Faster than DELETE without WHERE; cannot roll back in many DBs.
●​ RENAME / COMMENT
○​ Rename table/column or add description.
○​ (Less emphasized but sometimes within DDL scope)

4. QUERY OPERATIONS: SELECT, PROJECTION, JOINS & SET

OPERATIONS

These are key parts of SQL queries.

4.1 SELECT & Projection

●​ SELECT statement is used to retrieve data. It allows specifying which columns

(projection) and rows (selection) you want. SitePoint+3BCcampus Open
Publishing+3GeeksforGeeks+3​

Basic syntax:​
​
SELECT col1, col2, …

FROM TableName

WHERE condition

ORDER BY colX [ASC | DESC]

●​
●​ Projection: the list of columns in SELECT is the projection — you pick which
attributes to show.​

●​ Selection (filtering): WHERE clause specifies conditions to pick only certain tuples
(rows).​

Example:​
​
SELECT Name, GPA

FROM Student
WHERE GPA > 3.0;

●​
●​ DISTINCT can be used to remove duplicates.​

4.2 JOINS

Joining multiple tables based on relationship / matching columns:

●​ INNER JOIN: rows where matching condition holds in both tables.

●​ LEFT (OUTER) JOIN: all rows from left table + matching rows from right;
non-matching have NULLs.
●​ RIGHT (OUTER) JOIN, FULL OUTER JOIN: analogous variations.

Example:

SELECT [Link], [Link]

FROM Student s

INNER JOIN Enrollment e ON [Link] = [Link]

INNER JOIN Course c ON [Link] = [Link];

●​ Also CROSS JOIN (Cartesian product), NATURAL JOIN, SELF JOIN, etc.​

4.3 SET OPERATIONS

SQL supports operations combining result sets (provided they have the same number of
columns and compatible types):

●​ UNION — combine unique rows from both sets​

●​ UNION ALL — combine all rows, including duplicates​

●​ INTERSECT — rows common to both​

●​ EXCEPT / MINUS — rows in first not in second​

Example:

SELECT StudentID FROM Undergraduates

UNION
SELECT StudentID FROM Graduates;

5. AGGREGATE FUNCTIONS

Aggregate / summary computations over groups of rows. SitePoint+2GeeksforGeeks+2

Common functions:

●​ COUNT(*), COUNT(col) — count rows (or non-null values)​

●​ SUM(col) — sum of values​

●​ AVG(col) — average​

●​ MIN(col), MAX(col) — minimum / maximum​

●​ GROUP BY — group rows by one or more columns​

●​ HAVING — filter groups after aggregation​

Example:

SELECT Department, AVG(Salary) AS AvgSal

FROM Employee

GROUP BY Department

HAVING AVG(Salary) > 50000;

●​ Note: HAVING is like WHERE but works after grouping.​

6. DML (Data Manipulation Language)

These commands modify / insert / delete data in tables. BCcampus Open

Publishing+2Wikipedia+2
INSERT​
Add new rows:​
​
INSERT INTO TableName (col1, col2, …)

VALUES (val1, val2, …);

●​

UPDATE​
Modify existing rows:​
​
UPDATE TableName

SET col1 = val1, col2 = val2, …

WHERE condition;

●​

DELETE​
Remove rows:​
​
DELETE FROM TableName

WHERE condition;

●​
●​ Also, sometimes MERGE (UPSERT) is supported in advanced SQL dialects.​

7. MODIFICATION vs TRUNCATION

●​ Modification (INSERT / UPDATE / DELETE) changes particular rows based on

conditions.
●​ TRUNCATION removes all rows from a table (resets it) but keeps its structure intact.
●​ TRUNCATE is faster and less logging overhead than DELETE (without WHERE), but
often non-transactional (cannot roll back) in many DBMS.​

8. CONSTRAINTS

Constraints enforce rules and integrity on data in tables.

Medium+3W3Schools+3GeeksforGeeks+3
Common types:

●​ NOT NULL: column must always have a value (cannot be NULL)

●​ UNIQUE: all values in the column (or combination) must be unique
●​ PRIMARY KEY: uniquely identifies each row; implies NOT NULL + UNIQUE
●​ FOREIGN KEY (REFERENTIAL INTEGRITY): ensures value in this column matches
a primary key in another (or same) table
●​ CHECK: value must satisfy a condition (e.g. age >= 18) Wikipedia+1
●​ DEFAULT: default value when none is provided
●​ Composite / Multi-column constraints can also be defined
●​ Constraints can be specified at column level or table level

Example:

CREATE TABLE Employee (

EmpID INT PRIMARY KEY,

Name VARCHAR(100) NOT NULL,

Age INT CHECK (Age >= 18),

DepartmentID INT,

FOREIGN KEY (DepartmentID) REFERENCES Department(DeptID)

);

9. SUBQUERIES

●​ A subquery (inner query) is a query nested inside another (outer) query, used often
in WHERE, FROM, or SELECT clauses.​

Example:

SELECT Name

FROM Employee

WHERE DepartmentID = (

SELECT DeptID

FROM Department
 WHERE DeptName = 'Sales'

);

●​ Subqueries can return a single value, a single column, or multiple columns / rows
(use IN, EXISTS, etc.).​

●​ Correlated subqueries: outer query’s row is used in inner query condition.

UNIT V — PL/SQL: Structure, Elements, Control,

Cursors, Subprograms, Triggers

1. INTRODUCTION & STRUCTURE

●​ PL/SQL: Procedural Language / Structured Query Language — Oracle’s procedural
extension for SQL. Wikipedia+1
●​ It combines SQL (for data access) with procedural constructs (conditions, loops,
variables, exception handling).
●​ PL/SQL units (blocks) are stored in the database and reused: procedures, functions,
packages, triggers.

Basic Block Structure

A PL/SQL block generally has this form:

[ DECLARE – optional ]

-- declarations of variables, cursors, subprograms

BEGIN – mandatory

-- executable statements (SQL + PL/SQL logic)

[ EXCEPTION – optional ]

-- exception handlers

END;
You can embed one or more nested blocks.

2. ELEMENTS & OPERATORS PRECEDENCE

2.1 Elements (Variables, Constants, Types, Literals, Expressions)

●​ Variables & Constants: you declare with a type, optionally initialize.

●​ Types: built-in (NUMBER, VARCHAR2, DATE, BOOLEAN) or user-defined types.
●​ Literals: fixed values (numbers, strings, dates, NULL).
●​ Expressions: formed using operators, constants, variables.

2.2 Operators in PL/SQL

PL/SQL supports:

1.​ Arithmetic operators: +, -, *, /, MOD, exponentiation ** GeeksforGeeks+1​

2.​ Relational / Comparison operators: =, <> / != / ~=, <, >, <=, >= GeeksforGeeks​

3.​ Logical operators: AND, OR, NOT​

4.​ String / concatenation operator: || (concatenate strings)​

5.​ Membership / pattern operators: IN, BETWEEN, LIKE, IS NULL GeeksforGeeks+1

Operator Precedence

●​ Expressions are evaluated based on operator precedence (higher precedence first).

Oracle Docs+1​

●​ Precedence order (highest to lowest) includes (excerpt): ** → unary +/- → * / / →

+ / - / || → comparison operators → NOT → AND → OR Oracle Docs+1​

●​ Use parentheses () to override default order when needed.​

Example:

DECLARE

a NUMBER := 1 + 2**2; -- ** has highest precedence → 2**2 = 4 →

result = 5

b NUMBER := (1 + 2)**2; -- parentheses first → (3)**2 = 9

BEGIN

DBMS_OUTPUT.PUT_LINE('a = ' || a);

DBMS_OUTPUT.PUT_LINE('b = ' || b);

END;

(This example is shown in Oracle docs) Oracle Docs

3. CONTROL STRUCTURES & ITERATIVE CONTROL

3.1 Conditional / Decision Control

●​ IF … THEN … [ELSIF …] [ELSE] … END IF;​

IF condition1 THEN

-- statements

ELSIF condition2 THEN

-- statements

ELSE

-- statements

END IF;

●​ CASE statement (PL/SQL also supports CASE … WHEN … THEN … ELSE … END
CASE)​

3.2 Loops / Iterative Control

●​ Basic LOOP … END LOOP; (unconditional loop with EXIT condition)​

LOOP
 -- statements

EXIT WHEN some_condition;

END LOOP;

●​ WHILE … LOOP … END LOOP;​

WHILE condition LOOP

-- statements

END LOOP;

●​ FOR loop​

FOR i IN lower_bound .. upper_bound LOOP

-- statements using i

END LOOP;

●​ You can exit loops using EXIT, use CONTINUE, nested loops etc.​

4. CURSORS
Cursors allow you to fetch and process query results row by row in PL/SQL.

4.1 Implicit Cursors

●​ Default cursor used for single SQL statements (INSERT, UPDATE, DELETE).​

●​ Attributes like %FOUND, %NOTFOUND, %ROWCOUNT, %ISOPEN can be used.​

4.2 Explicit Cursors

 ●​ For multi-row queries. Steps:
1.​ Declare the cursor with a SELECT statement.
2.​ OPEN the cursor.
3.​ FETCH rows into variables (one row at a time).
4.​ LOOP until %NOTFOUND.
5.​ CLOSE the cursor.​

DECLARE

CURSOR c_emp IS SELECT emp_id, salary FROM employees;

v_emp employees.emp_id%TYPE;

v_sal [Link]%TYPE;

BEGIN

OPEN c_emp;

LOOP

FETCH c_emp INTO v_emp, v_sal;

EXIT WHEN c_emp%NOTFOUND;

-- process each row

END LOOP;

CLOSE c_emp;

END;

●​ Cursor FOR loops: simpler form combining open, fetch, close.​

5. PROCEDURES, FUNCTIONS & PACKAGES

These are subprograms in PL/SQL providing modularization.

5.1 Procedure
 ●​ A named PL/SQL block that can accept IN, OUT, IN OUT parameters but does not
return a value directly (though out params can achieve result).
●​ Syntax:​

CREATE [OR REPLACE] PROCEDURE proc_name (

p_in IN datatype,

p_out OUT datatype,

p_inout IN OUT datatype

) IS

BEGIN

-- statements

EXCEPTION

-- exception handlers

END proc_name;

●​ You call it using EXEC proc_name(...) or within PL/SQL block.

5.2 Function

●​ Similar to procedure but returns exactly one value (via RETURN statement).
●​ Can be used in SQL expressions.
●​ Syntax:​

CREATE [OR REPLACE] FUNCTION func_name (

p_in IN datatype

) RETURN return_datatype IS

BEGIN

-- compute result

RETURN some_value;
EXCEPTION

-- handlers

END func_name;

●​ Use: v := func_name(arguments); or SELECT func_name(...) FROM

DUAL;​

5.3 Packages

●​ A package is a group of related procedures, functions, variables, cursors, types,

exceptions, etc.
●​ Consists of two parts: package specification (spec) and package body
(implementation).
●​ Spec declares public elements; body defines them.
●​ Benefits: modularity, encapsulation, reusable code, improved performance (loading
once), session-scoped package variables.​

CREATE PACKAGE pkg_name IS

-- declarations: procedures, types, constants

PROCEDURE proc1(...);

FUNCTION func1(...) RETURN ...;

END pkg_name;

CREATE PACKAGE BODY pkg_name IS

-- implementations:

PROCEDURE proc1 IS

BEGIN

-- ...

END proc1;
 FUNCTION func1 RETURN ... IS

BEGIN

-- ...

END func1;

END pkg_name;

6. EXCEPTIONAL HANDLING
●​ PL/SQL supports exception handling within the EXCEPTION section of the
block/subprogram.
●​ Predefined exceptions: NO_DATA_FOUND, TOO_MANY_ROWS, VALUE_ERROR,
ZERO_DIVIDE, etc.
●​ You can declare user-defined exceptions.
●​ Use WHEN exception_name THEN … to catch and handle.
●​ OTHERS clause catches any unhandled exceptions.

Example:

BEGIN

-- statements that may raise exceptions

EXCEPTION

WHEN NO_DATA_FOUND THEN

-- handle no data found

WHEN VALUE_ERROR THEN

-- handle type conversion error

WHEN OTHERS THEN

-- catch-all

END;
7. TRIGGERS
●​ A trigger is a stored PL/SQL block that automatically fires when a specified event
happens (INSERT, UPDATE, DELETE, DDL, or system events). Oracle
Docs+2GeeksforGeeks+2
●​ You don’t call a trigger – DB invokes it automatically when its event condition is met.

7.1 Trigger Components / Syntax

CREATE [OR REPLACE] TRIGGER trigger_name

{ BEFORE | AFTER | INSTEAD OF } -- timing

{ INSERT | UPDATE | DELETE } ON table_or_view

[ FOR EACH ROW ]

[ WHEN (condition) ]

DECLARE

-- declarations

BEGIN

-- trigger action (SQL / PL/SQL)

EXCEPTION

-- exception handlers

END trigger_name;

●​ BEFORE / AFTER: defines timing relative to the triggering DML.

●​ FOR EACH ROW: row-level trigger (executes for every affected row). If omitted, it’s a
statement-level trigger.
●​ WHEN clause: optional condition to limit triggering.

7.2 Types of Triggers

●​ Row-level triggers vs statement-level triggers

●​ DML triggers (on tables / views)
●​ DDL triggers / system triggers (on schema / database operations)
●​ INSTEAD OF triggers (for views)
7.3 Use Cases & Benefits

●​ Maintain audit logs (who changed what, when)

●​ Enforce business / referential integrity not possible via constraints
●​ Automatically populate derived columns
●​ Prevent certain actions based on logic

7.4 Caveats & Mutating Table Issue

●​ Triggers fire during DML statements; if trigger code queries or modifies the same
table, it may cause mutating table error in Oracle.
●​ When multiple triggers exist for same event on same object, default firing order is:
○​ Statement-level BEFORE
○​ Row-level BEFORE
○​ Row-level AFTER
○​ Statement-level AFTER
○​ But among triggers at same level, order is not guaranteed (in Oracle 10g).
Stack Overflow+1
○​ Starting Oracle 11g, you can use FOLLOWS / PRECEDES clause to specify
order. Stack Overflow+1