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;
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s