Catalog / PL/SQL Cheatsheet
PL/SQL Cheatsheet
A concise reference for PL/SQL, covering syntax, data types, control structures, and common operations for Oracle database programming.
PL/SQL Basics
Block Structure
PL/SQL code is organized into blocks. A block can be anonymous or named (a stored procedure, function, or trigger).
|
The Variables are declared with a name, data type, and optional initial value:
|
The |
The Exception handlers are defined for specific exceptions or for all exceptions. |
Data Types
|
Numeric data type for storing integers and floating-point numbers. |
|
Variable-length character string with a maximum size specified in bytes. |
|
Stores date and time values. |
|
Stores logical values: |
|
Character Large Object, for storing large amounts of text data (up to 4GB). |
Variables and Constants
Variables are declared in the
Constants are declared using the
|
Referencing Database Columns:
This declares a variable with the same data type as a specified column in a database table. |
Control Structures
Conditional Statements
IF-THEN-ELSE
|
CASE Statement
|
Looping Structures
Basic LOOP |
|
WHILE LOOP |
|
FOR LOOP |
The |
Cursors
Cursors allow you to process rows returned by a SQL query one at a time. Explicit Cursors
|
Cursor Attributes
|
Exception Handling
Exception Handling Block
The
|
Predefined Exceptions: User-Defined Exceptions: Can be declared and raised explicitly using
|
Common Exceptions
|
Raised when a |
|
Raised when a |
|
Raised when an invalid cursor operation is performed. |
|
Raised when an attempt is made to divide by zero. |
|
Raised when attempting to insert a duplicate value into a unique index. |
RAISE_APPLICATION_ERROR
Used to return user-defined error messages from a PL/SQL block to the calling environment.
|
Stored Procedures and Functions
Stored Procedures
A stored procedure is a named PL/SQL block that can accept input parameters and return output parameters. It is stored in the database and can be executed by name.
|
Parameter Modes
|
Functions
A function is a named PL/SQL block that returns a single value. It is stored in the database and can be called from SQL statements or other PL/SQL blocks.
|
Functions must contain a Functions can be called in SQL queries:
|
Calling Stored Procedures and Functions
Calling a Stored Procedure |
|
Calling a Function |
Or directly in a SQL query:
|