The PL/SQL cheat sheet includes symbol syntax and methods to help you using PL/SQL. ORACLE PL/SQL is an extension of SQL language that combines the data manipulation power of SQL with the processing power of procedural language to create super-powerful SQL queries. PL/SQL means instructing the compiler ‘what to do’ through SQL and ‘how to do’ through its procedural way.

Scalar Data Types and Subtypes

Numeric Data Types and Subtypes

Below is a valid declaration

DECLARE 
   num1 INTEGER; 
   num2 REAL; 
   num3 DOUBLE PRECISION; 
BEGIN 
   null; 
END; 
/ 

Character Data Types and Subtypes

Datetime and Interval Types

PL/SQL Large Object (LOB) Data Types

Variables

Variable_name  datatype(size);

Constants

Constant_Name  constant  Datatype(size) := value;

Literals

Arithmetic Operators

Relational Operators

Comparison Operators

Logical Operators

IF-THEN Statement

IF condition THEN  
   Statement; 
END IF; 

IF-THEN-ELSE Statement

IF condition THEN 
   Statement1;  
ELSE  
   Statement2; 
END IF;

IF-THEN-ELSIF Statement

IF(boolean_expression 1)THEN  
   Statement1; -- Executes when the boolean expression 1 is true  
ELSIF( boolean_expression 2) THEN 
   Statement2;  -- Executes when the boolean expression 2 is true  
ELSIF( boolean_expression 3) THEN 
   Statement3; -- Executes when the boolean expression 3 is true  
ELSE  
   Statement4; -- executes when the none of the above condition is true  
END IF; 

CASE Statement

CASE selector 
   WHEN 'value1' THEN Statement1; 
   WHEN 'value2' THEN Statement2; 
   WHEN 'value3' THEN Statement3; 
   ... 
   ELSE Sn;  -- default case 
END CASE;

Searched CASE Statement

CASE 
   WHEN selector = 'value1' THEN Statement1; 
   WHEN selector = 'value2' THEN Statement2; 
   WHEN selector = 'value3' THEN Statement3; 
   ... 
   ELSE Sn;  -- default case 
END CASE; 

Nested IF-THEN-ELSE Statements

IF( boolean_expression 1)THEN 
   -- executes when the boolean expression 1 is true  
   IF(boolean_expression 2) THEN 
      -- executes when the boolean expression 2 is true  
      sequence-of-statements; 
   END IF; 
ELSE 
   -- executes when the boolean expression 1 is not true 
   else-statements; 
END IF; 

Basic Loop

LOOP 
   Sequence of statements; 
END LOOP; 

WHILE Loop

WHILE condition LOOP 
   sequence_of_statements 
END LOOP; 

FOR Loop

FOR counter IN initial_value .. final_value LOOP 
   sequence_of_statements; 
END LOOP;

Nested Loops

• Nested basic LOOP

LOOP 
   Sequence of statements1 
   LOOP 
      Sequence of statements2 
   END LOOP; 
END LOOP;

• Nested FOR LOOP 

FOR counter1 IN initial_value1 .. final_value1 LOOP 
   sequence_of_statements1 
   FOR counter2 IN initial_value2 .. final_value2 LOOP 
      sequence_of_statements2 
   END LOOP; 
END LOOP;

• Nested WHILE LOOP

WHILE condition1 LOOP 
   sequence_of_statements1 
   WHILE condition2 LOOP 
      sequence_of_statements2 
   END LOOP; 
END LOOP; 

EXIT statement 

EXIT;

CONTINUE statement

CONTINUE;

GOTO statement 

GOTO label;
..
..
<< label >>
statement;

Creating a Varray Type

CREATE OR REPLACE TYPE varray_type_name IS VARRAY(n) of <element_type>

Where,

• varray_type_name is a valid attribute name,
• n is the number of elements (maximum) in the varray,
• element_type is the data type of the elements of the array.

Resize the max size of array

CREATE Or REPLACE TYPE PrintArray AS VARRAY(10) OF VARCHAR2(10);

Drop the varray

DROP TYPE varray_type_name ;

Creating a Function

CREATE [OR REPLACE] FUNCTION function_name 
[(parameter_name [IN | OUT | IN OUT] type [, ...])] 
RETURN return_datatype 
{IS | AS} 
BEGIN 
   < function_body > 
END [function_name];

Where,

• function-name specifies the name of the function.
• [OR REPLACE] the option allows the modification of an existing function.
• The optional parameter list contains the name, model, and types of the parameters. IN represents the value that will be passed from outside and OUT represents the parameter that will be used to return a value outside of the procedure.
• The function must contain a return statement.
• The RETURN clause specifies the data type you are going to return from the function.
• function-body contains the executable part.
• The AS keyword is used instead of the IS keyword for creating a standalone function.

Drop Function

DROP FUNCTION function_name;

Implicit Cursors

Below is table provides the description of the most used attributes

Any SQL cursor attribute will be accessed as sql%attribute_name

Explicit Cursors

CURSOR cursor_name IS select_statement; 

Declaring the Cursor

CURSOR c_customers IS 
   SELECT id, name, address FROM customers; 

Opening the Cursor

OPEN c_customers; 

Fetching the Cursor

FETCH c_customers INTO c_id, c_name, c_addr; 

Closing the Cursor

CLOSE c_customers;

Exception Handling

The default exception will be handled using WHEN others THEN

DECLARE 
   <declarations section> 
BEGIN 
   <executable command(s)> 
EXCEPTION 
   <exception handling goes here > 
   WHEN exception1 THEN  
      exception1-handling-statements  
   WHEN exception2  THEN  
      exception2-handling-statements  
   WHEN exception3 THEN  
      exception3-handling-statements 
   ........ 
   WHEN others THEN 
      exception3-handling-statements 
END;

Raising Exceptions

Below is the simple syntax for raising an exception:

DECLARE 
   exception_name EXCEPTION; 
BEGIN 
   IF condition THEN 
      RAISE exception_name; 
   END IF; 
EXCEPTION 
   WHEN exception_name THEN 
   statement; 
END; 

User-defined Exceptions

DECLARE 
   my-exception EXCEPTION; 

Below is syntax for creating a trigger:

CREATE [OR REPLACE ] TRIGGER trigger_name  
{BEFORE | AFTER | INSTEAD OF }  
{INSERT [OR] | UPDATE [OR] | DELETE}  
[OF col_name]  
ON table_name  
[REFERENCING OLD AS o NEW AS n]  
[FOR EACH ROW]  
WHEN (condition)   
DECLARE 
   Declaration-statements 
BEGIN  
   Executable-statements 
EXCEPTION 
   Exception-handling-statements 
END; 

Where,

• CREATE [OR REPLACE] TRIGGER trigger_name − Creates or replaces an existing trigger with the trigger_name.
• {BEFORE | AFTER | INSTEAD OF} − This specifies when the trigger will be executed. The INSTEAD OF clause is used for creating a trigger on a view.
• {INSERT [OR] | UPDATE [OR] | DELETE} − This specifies the DML operation.
• [OF col_name] − This specifies the column name that will be updated.
• [ON table_name] − This specifies the name of the table associated with the trigger.
• [REFERENCING OLD AS o NEW AS n] − This allows you to refer new and old values for various DML statements, such as INSERT, UPDATE, and DELETE.
• [FOR EACH ROW] − This specifies a row-level trigger, i.e., the trigger will be executed for each row being affected. Otherwise, the trigger will execute just once when the SQL statement is executed, which is called a table-level trigger.
• WHEN (condition) − This provides a condition for rows for which the trigger would fire. This clause is valid only for row-level triggers.

Types of PL/SQL Triggers

• Row-level trigger – An event is triggered for each row updated, inserted, or deleted.
• Statement level trigger – An event is triggered for each SQL statement executed.

Below is the syntax for creating PL/SQL package specification:

CREATE [OR REPLACE] PACKAGE package_name [ AUTHID { CURRENT_USER | DEFINER } ] { IS | AS }

Package Body

CREATE [OR REPLACE] PACKAGE BODY package_name { IS | AS }

Package elements

package_name.package_element

Varrays

TYPE <type_name> IS VARRAY (<SIZE>) OF <DATA_TYPE>;

the memory allocation of Varray (dense) diagrammatically.

Nested Tables

TYPE type_name IS TABLE OF element_type [NOT NULL]; 

table_name type_name;

The memory allocation of Nested Table (dense and sparse) diagrammatically. The black-colored element space denotes the empty element in a collection i.e. sparse.

Index-by Table

TYPE type_name IS TABLE OF element_type [NOT NULL] INDEX BY subscript_type; 

table_name type_name;

The memory allocation of Nested Table (sparse) diagrammatically. The black-colored element space denotes the empty element in a collection i.e. sparse.

Collection Methods

Collection Exceptions

Using COMMIT

The commit command permanently changes the data in the database.

Commit;

By default, automatic commit for DML commands is off. The automatic commit for DML commands can be set by using the following command

SET AUTOCOMMIT ON; 
-- and to turn it off
SET AUTOCOMMIT OFF;

Using ROLLBACK

ROLLBACK [TO SAVEPOINT < savepoint_name>]; 

Using SAVEPOINT

SAVEPOINT < savepoint_name >;

Two classes of date and time related data types in PL/SQL

• Datetime data types
• Interval data types

The Datetime data types 

• DATE
• TIMESTAMP
• TIMESTAMP WITH TIME ZONE
• TIMESTAMP WITH LOCAL TIME ZONE

The Interval data types are

• INTERVAL YEAR TO MONTH
• INTERVAL DAY TO SECOND

Field Values for Datetime and Interval Data Types

The Datetime Data Types and Functions

• Datetime functions 

• Timestamp functions

The Interval Data Types and Functions