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
Enhancing Identity and Access Management in Healthcare with Enteros
- 19 November 2024
- Database Performance Management
In the fast-evolving world of finance, where banking and insurance sectors rely on massive data streams for real-time decisions, efficient anomaly man…
Maximizing Efficiency with Enteros: Revolutionizing Cost Allocation Through a Cloud Center of Excellence
In the fast-evolving world of finance, where banking and insurance sectors rely on massive data streams for real-time decisions, efficient anomaly man…
Driving Efficiency in the Transportation Sector: Enteros’ Cloud FinOps and Database Optimization Solutions
- 18 November 2024
- Database Performance Management
In the fast-evolving world of finance, where banking and insurance sectors rely on massive data streams for real-time decisions, efficient anomaly man…
Empowering Nonprofits with Enteros: Optimizing Cloud Resources Through AIOps Platform
In the fast-evolving world of finance, where banking and insurance sectors rely on massive data streams for real-time decisions, efficient anomaly man…