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)
Operators
Cursor Attributes: %FOUND, %NOTFOUND, %ISOPEN, ROWCOUNT
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
logical operator and
logical operator or
logical operators and/or
logical operator not
correlates data with a template
uses regular expressions
determines whether the value or list of values corresponds to the expression in the specified set
NULL check
NOT NULL check
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!
RELATED POSTS
How to Improve Retail Profitability with Enteros Cost Attribution, Database Analytics, and Software Management
- 21 June 2026
- Software Engineering
Introduction The retail industry is evolving at an unprecedented pace. Driven by ecommerce growth, omnichannel experiences, customer personalization, and digital transformation initiatives, retailers are investing heavily in technology to remain competitive. Modern retail organizations rely on: Ecommerce platforms Point-of-sale (POS) systems Inventory management applications Customer engagement platforms Supply chain systems Retail analytics environments AI-powered recommendation … Continue reading “How to Improve Retail Profitability with Enteros Cost Attribution, Database Analytics, and Software Management”
How to Drive Education Platform Growth with Enteros Database Performance Analytics and RevOps Efficiency
Introduction The education sector is experiencing a profound digital transformation. Universities, colleges, online learning providers, K-12 institutions, and corporate learning platforms are increasingly relying on technology to deliver engaging learning experiences, manage academic operations, and support student success. Modern education ecosystems now support: Learning Management Systems (LMS) Student Information Systems (SIS) Online learning platforms Virtual … Continue reading “How to Drive Education Platform Growth with Enteros Database Performance Analytics and RevOps Efficiency”
Why Intelligent Database Workload Management Is Essential for High-Growth SaaS Platforms
- 19 June 2026
- Database Performance Management
Introduction Telecommunications providers are operating in one of the most competitive and technology-intensive industries in the world. While demand for connectivity, mobile services, broadband access, and digital experiences continues to grow, profit margins are increasingly challenged by rising infrastructure costs, complex network operations, and expanding customer expectations. Modern telecom organizations must support: 5G networks Cloud-native … Continue reading “Why Intelligent Database Workload Management Is Essential for High-Growth SaaS Platforms”
Reducing Operational Complexity with AI-Driven Database Observability and AIOps
Modern enterprises operate in increasingly complex digital environments. Applications now span hybrid cloud infrastructures, multi-cloud deployments, containerized platforms, microservices architectures, and globally distributed data systems. While this transformation enables greater scalability, agility, and innovation, it also creates significant operational challenges for IT and engineering teams. At the heart of these complex environments lies the database … Continue reading “Reducing Operational Complexity with AI-Driven Database Observability and AIOps”