PL SQL Cheatsheet

PL/SQL Syntax for reference

Basic PL/SQL block

 DECLARE  
  --variable declarations go here  
 BEGIN  
  --program logic goes here  
 END;  

Functions - Named pl/sql blocks mainly used for computation and not for write operations. They return value to the caller as defined in function.

 CREATE OR REPLACE FUNCTION myFunc(  
  arg1_in IN VARCHAR2,  
  arg2_in VARCHAR2)  
  RETURN BOOLEAN  
 AS  
  RETURN true;  
 END;  

Procedure - Named pl/sql block used for write operations.

 CREATE OR REPLACE PROCEDURE myProc(  
  arg1_in IN VARCHAR2,  
  arg2_in IN VARCHAR2)  
 AS  
  arg3 VARCHAR2(2048);  
  arg4 BOOLEAN;  
 BEGIN  
  --logic  
 END;  

Variable Declarations 

 arg VARCHAR2(2048) := 'myString';  
 arg BOOLEAN := true;  
 arg NUMBER := 10;  

IF ELSE Conditions

 IF (condition) THEN  
  --logic 1  
 ELSIF (condition2) THEN  
  --logic 2  
 ELSE  
  --logic3  
 END IF;  

SWITCH Statements
With selector

 arg1 NUMBER := 0;  
 CASE arg1  
  WHEN 0 THEN  
   --logic;  
  WHEN 1 THEN  
   --logic;  
  WHEN 2 THEN  
   --logic;  
  ELSE  
   --logic;  
 END CASE;  

Without selector - it behaves just like multiple if-else conditions

 CASE  
  WHEN (arg1 = 0) THEN  
   --logic;  
  WHEN (arg1 = 1 ) THEN  
   --logic;  
  ELSE  
   --logic;  
 END CASE;  

WHILE loop

 WHILE (condition)  
 LOOP  
  --logic;  
 END LOOP;  

Exception Handling

 BEGIN  
 EXCEPTION  
 --catches all exceptions  
 WHEN OTHERS THEN  
  --outputs error message  
  dbms_output.put_line(SUBSTR(SQLERRM, 1, 100));  
 END;  

Custom Exception

 AS  
  --custom exception  
  missing_parameter EXCEPTION;  
  pragma exception_init(missing_parameter, -20001);  
 BEGIN  
  --raising a custom exception with error message  
  raise_application_error(-20001,'Missing parameter');  
 END;  

Cursor

 --cursor declaration  
 CURSOR entries  
  IS  
  SELECT *  
  FROM entrytable;  
 BEGIN  
 --opens cursor  
  OPEN entries;  
  LOOP  
 --fetch rows one by one, entry is rowtype of entrytable  
  FETCH entries INTO entry;  
 -- if no rows found then exit  
  EXIT  
  WHEN entries%notfound;  
 END LOOP;  
  CLOSE entries;  

User records - custom user defined records

 --definition of customer record  
 type customer  
 IS  
  record  
  (  
  username VARCHAR2(2048),  
  email VARCHAR2(2048),  
  firstname VARCHAR2(2048),  
  lastname VARCHAR2(2048));  
 --variable of type customer record defined above  
 customer_rec customer;  

Rowtype - variable in pl/sql which have same structure as a table row.

 entry entrytable%rowtype;  

Dynamic Procedure Calling

 proc VARCHAR2(2048);  
 execute_scenario VARCHAR2(2048) := 'myProc';  
 proc := 'BEGIN SCENARIO'|| execute_scenario || ' (:email, :firstname); END;';  
 execute immediate (proc) USING entry.email, entry.firstname;