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!

Eliminating Growth Friction: How Enteros Aligns Database Performance, Cloud FinOps, and RevOps

28 January 2026
Database Performance Management

Introduction For modern enterprises, growth is no longer limited by market demand alone—it is increasingly constrained by technology efficiency. As organizations scale digital platforms, launch new products, expand globally, and adopt AI-driven services, hidden friction inside their technology stack quietly erodes margins, slows execution, and undermines revenue outcomes. At the center of this friction sits … Continue reading “Eliminating Growth Friction: How Enteros Aligns Database Performance, Cloud FinOps, and RevOps”

AI SQL-Powered Database Management: Enteros’ Performance Intelligence Platform for Tech Enterprises

Database Performance Management

Introduction Technology enterprises today operate at unprecedented scale and speed. SaaS platforms, cloud-native applications, AI services, data marketplaces, and digital ecosystems now serve millions of users globally—often in real time. At the heart of this digital machinery lie databases. Databases power application responsiveness, AI pipelines, analytics engines, customer experiences, and revenue-generating workflows. Yet as technology … Continue reading “AI SQL-Powered Database Management: Enteros’ Performance Intelligence Platform for Tech Enterprises”

Keeping Operations Running at Scale: Enteros’ AIOps-Driven Database Performance Platform

27 January 2026
Database Performance Management

Introduction In manufacturing plants and insurance enterprises alike, operational continuity is non-negotiable. A delayed production schedule, a failed claims transaction, or a slow underwriting system can ripple into lost revenue, regulatory exposure, and eroded customer trust. At the heart of these operations sit databases—quietly powering everything from shop-floor automation and supply chain planning to policy … Continue reading “Keeping Operations Running at Scale: Enteros’ AIOps-Driven Database Performance Platform”

Managing Real Estate Data at Scale: Enteros AI Platform for Database Performance and Cost Estimation

Database Performance Management

Introduction The real estate sector has undergone a dramatic digital transformation over the past decade. From commercial real estate (CRE) platforms and property management systems to residential marketplaces, smart buildings, and PropTech startups, modern real estate enterprises are now fundamentally data-driven organizations. Behind digital leasing platforms, pricing engines, tenant experience apps, IoT-enabled buildings, analytics dashboards, … Continue reading “Managing Real Estate Data at Scale: Enteros AI Platform for Database Performance and Cost Estimation”