Oracle PL/SQL tutorial.

Preamble

Oracle PL/SQL is a combination of SQL, along with the procedural features of programming languages. It was developed by Oracle Corporation in the early 90’s to extend the capabilities of SQL.

PL / SQL is one of the three key programming languages built into an Oracle database, along with SQL and Java itself. This guide will give you a deep understanding of PL / SQL to continue working with the Oracle database and other advanced DBMS concepts.

Oracle PL/SQL Functions

Analytical	Symbolic / string
Conversion	Numerical / mathematical
Supporting	Date / time

Types of Oracle requests

SELECT	retrieves records from one or more tables
FROM	specifies a list of tables and any attachments
WHERE	is used to filter the results
ORDER BY	is used for sorting records
GROUP BY	groups the results of one or more columns
HAVING	used in combination with GROUP BY to limit groups of returned strings
DISTINCT	removes duplicates from the resulting SELECT set
EXISTS	used in conjunction with a subquery
INSERT	used to insert one or more records into an Oracle table
INSERT ALL	is used to add multiple lines using a single INSERT operator
UPDATE	used to update existing table entries in the Oracle database
DELETE	used to remove one or more records from a table in Oracle
TRUNCATE TABLE	is used to remove all records from the table in Oracle
UNION	removes repetitive lines between SELECT requests
UNION ALL	returns all rows from the query and does not delete repeating rows
INTERSECT	returns the intersection of 2 sets of results
MINUS	returns one data set minus another data set
PIVOT	used to rotate rows into columns
Subqueries	subqueries

Oracle PL/SQL programming

Data Types	Data Types
Literals	Such as text, integer, number and datetime
Declaring Variables	Announcement of variables, constants
Comments within SQL	SQL comments are used to comment on program code
Procedures	Creation and removal of procedures
Functions	Create and delete functions
Triggers	Create, delete, enable and disable triggers
Dynamic SQL	Dynamic SQL
Packages	Packages and package bodies – create, delete
Sequences	Sequences
Synonyms	Synonyms
Aliases	Aliases (aliases) for tables or columns
WITH	Operator WITH

Cursors (CREATE CURSOR)

Declare a Cursor

Operators

OPEN

FETCH

CLOSE

Cursor Attributes: %FOUND, %NOTFOUND, %ISOPEN, ROWCOUNT

SELECT FOR UPDATE

WHERE CURRENT OF

Oracle tables and views

CREATE TABLE	Create a table
CREATE TABLE AS	Create a table from the definition and data of another table
ALTER TABLE	Add, change or delete columns in the table; rename the table
DROP TABLE	Delete the table (no recovery option)
GLOBAL TEMPORARY TABLES	Create a global time table
LOCAL TEMPORARY TABLES	Create a local time table
VIEW	Virtual tables (views of other tables)

Database administration Oracle

CREATE USER	Operator to create an account in the database
DROP USER	Operator to delete a user from the database
ALTER USER(Change Password)	Change user password in Oracle
Find Users in Oracle	Search for a user in Oracle
Find Users logged into Oracle	Search for users logged in to Oracle
TABLESPACE	Tabular spaces create, modify, delete. Search for default table spaces . Default setting of tabular spaces
Grant/Revoke PRIVILEGES	Providing privileges to users in Oracle
ROLES	Creating a role, granting/disabling privileges to a role, enabling/disabling a role, setting a default role and removing a role
SCHEMA	The scheme allows logical grouping of objects in an Oracle database

Oracle keys, indexes, unique restrictions

Primary Keys	Primary Keys
Foreign Keys	External keys
Foreign Keys with cascade delete	External keys with cascading removal
Foreign Keys with set null on delete	External keys with “set null on delete”
Drop a foreign key	Deletion of external key
Disable a foreign key	Turn off external key
Enable a foreign key	Turning on the external key
Indexes	Indexes
Unique constraints	Unique restrictions
Check constraints	Checking restrictions

Oracle conditional statements

IF-THEN-ELSE

CASE

GOTO

Oracle cycles

Comparison operators

Oracle PL/SQL comparison statements such as =, !=, <>, >, >=, <, <= etc.

Oracle conditions

AND

logical operator and

logical operator or

AND & OR

logical operators and/or

NOT

logical operator not

LIKE

correlates data with a template

REGEXP_LIKE

uses regular expressions

determines whether the value or list of values corresponds to the expression in the specified set

IS NULL

NULL check

IS NOT NULL

NOT NULL check

BETWEEN

is used to obtain values within a range

Oracle PL/SQL collections and records

Records
Record	Record
%ROWTYPE	Record Modifier
%TYPE	Column Modifier
Collections
Associative Arrays	Association massifs
Varrays	Variable length arrays
Nested Tables	Attached Tables

Oracle PL/SQL collection methods

Collection method	Type	Description
DELETE	Procedure	Removes elements from the collection.
TRIM	Procedure	Removes elements from the end of varray or Nested Tables.
EXTEND	Procedure	Adds elements to the end of varray or Nested Tables.
EXISTS	Function	Returns TRUE if and only if a certain varray or Nested Tables element exists.
FIRST & LAST	Functions	FIRST returns the first, LAST the last index in the collection.
COUNT	Function	Returns the number of items in the collection.
LIMIT	Function	Returns the maximum number of items that a collection can have.
PRIOR & NEXT	Functions	PRIOR returns the index that precedes the specified index, NEXT returns the index that follows the specified index.

Oracle PL/SQL operations on collections

MULTISET UNION	Returns the merger of the two collections
MULTISET UNION DISTINCT	Returns the merger of the two collections with a dystinct (removes duplicates)
MULTISET INTERSECT	Returns the intersection of two collections
MULTISET INTERSECT DISTINCT	Returns the intersection of the two collections with distinct (removes duplicates)
SET	Returns a collection with dystinct (i.e. a collection without takes)
MULTISET EXCEPT	Returns the difference (truncation) between the two collections
MULTISET EXCEPT DISTINCT	Returns the difference (truncation) between two collections with distinct (removes duplicates)

ORACLE error

ORACLE error messages

Exception handling

PL/SQL Oracle tutorial, Oracle introduction, PL/SQL basics

About Enteros

Enteros offers a patented database performance management SaaS platform. It proactively identifies root causes of complex business-impacting database scalability and performance issues across a growing number of clouds, RDBMS, NoSQL, and machine learning database platforms.

The views expressed on this blog are those of the author and do not necessarily reflect the opinions of Enteros Inc. This blog may contain links to the content of third-party sites. By providing such links, Enteros Inc. does not adopt, guarantee, approve, or endorse the information, views, or products available on such sites.

Are you interested in writing for Enteros’ Blog? Please send us a pitch!

From Network Traffic to Cost Transparency: Enteros Approach to Amortized Cost Management in Telecom

12 February 2026
Database Performance Management

Introduction Telecom operators today are no longer just connectivity providers. They are digital service platforms supporting 5G networks, IoT ecosystems, streaming services, cloud-native core systems, enterprise connectivity, and real-time analytics. Every call, message, streaming session, IoT signal, and digital interaction generates massive volumes of transactional and analytical data. That data is processed, stored, and monetized … Continue reading “From Network Traffic to Cost Transparency: Enteros Approach to Amortized Cost Management in Telecom”

From Transactions to Transparency: Enteros’ AI SQL Platform for Financial Database Performance and Cost Intelligence

Database Performance Management

Introduction In the financial sector, performance is not optional—it is existential. Banks, insurance providers, capital markets firms, fintech platforms, and payment processors operate in environments where milliseconds matter, compliance is mandatory, and financial transparency is critical. Every transaction—whether it’s a trade execution, loan approval, insurance claim, or digital payment—flows through complex database infrastructures. Yet as … Continue reading “From Transactions to Transparency: Enteros’ AI SQL Platform for Financial Database Performance and Cost Intelligence”

Driving Healthcare RevOps Efficiency with AI SQL–Powered Database Performance Management Software

11 February 2026
Database Performance Management

Introduction Healthcare organizations today operate at the intersection of clinical excellence, regulatory compliance, and financial sustainability. Hospitals, health systems, payer organizations, and healthtech SaaS providers depend on digital platforms to manage electronic health records (EHRs), billing systems, revenue cycle management (RCM), patient portals, telehealth platforms, claims processing engines, and analytics tools. At the core of … Continue reading “Driving Healthcare RevOps Efficiency with AI SQL–Powered Database Performance Management Software”

Retail Revenue Meets Cloud Economics: Enteros AIOps-Driven Approach to Database Cost Attribution

Database Performance Management

Introduction Retail has become a real-time, data-driven industry. From omnichannel commerce and dynamic pricing engines to inventory optimization, loyalty platforms, recommendation systems, and last-mile logistics, modern retail runs on software—and software runs on databases. As retailers scale their digital presence, they increasingly rely on SaaS platforms, microservices architectures, hybrid cloud infrastructure, and distributed database environments. … Continue reading “Retail Revenue Meets Cloud Economics: Enteros AIOps-Driven Approach to Database Cost Attribution”