PL/SQL Cheat Sheet

Here’s a cheat sheet for PL/SQL, which is a procedural language designed specifically for the Oracle Database:

Basic Structure

DECLARE
   -- Declarations (optional)
BEGIN
   -- Execution block
   -- SQL statements and PL/SQL code
END;
/

Variables and Constants

Declaration:

DECLARE
   variable_name datatype;
   constant_name CONSTANT datatype := value;
BEGIN
   -- Code
END;
/

Data Types

• Numeric:
  • NUMBER, BINARY_INTEGER, PLS_INTEGER
• Character:
  • VARCHAR2(size), CHAR(size), CLOB
• Date/Time:
  • DATE, TIMESTAMP
• Boolean:
  • BOOLEAN

Assigning Values

variable_name := value;

Conditional Statements

IF-THEN-ELSE:

IF condition THEN
   -- Code block executed if condition is true
ELSE
   -- Code block executed if condition is false
END IF;

CASE Statement:

CASE variable
   WHEN value1 THEN
      -- Code for value1
   WHEN value2 THEN
      -- Code for value2
   ELSE
      -- Code for other values
END CASE;

Loops

FOR Loop:

FOR i IN 1..10 LOOP
   -- Code block executed 10 times
END LOOP;

WHILE Loop:

WHILE condition LOOP
   -- Code block executed while condition is true
END LOOP;

Exception Handling

BEGIN
   -- Code that might raise an exception
EXCEPTION
   WHEN exception1 THEN
      -- Code to handle exception1
   WHEN exception2 THEN
      -- Code to handle exception2
   WHEN OTHERS THEN
      -- Code to handle any other exception
END;
/

Cursors

Implicit Cursor (SELECT INTO):

DECLARE
   variable_name datatype;
BEGIN
   SELECT column INTO variable_name FROM table WHERE condition;
   -- Code to use variable_name
END;
/

Explicit Cursor:

DECLARE
   CURSOR cursor_name IS SELECT column FROM table WHERE condition;
   variable_name datatype;
BEGIN
   OPEN cursor_name;
   FETCH cursor_name INTO variable_name;
   -- Code to use variable_name
   CLOSE cursor_name;
END;
/

Procedures and Functions

Procedure:

CREATE OR REPLACE PROCEDURE procedure_name IS
BEGIN
   -- Code
END procedure_name;
/

Function:

CREATE OR REPLACE FUNCTION function_name RETURN datatype IS
   -- Declaration
BEGIN
   -- Code
   RETURN value;
END function_name;
/

Triggers

CREATE OR REPLACE TRIGGER trigger_name
BEFORE/AFTER INSERT/UPDATE/DELETE ON table_name
FOR EACH ROW
BEGIN
   -- Code
END trigger_name;
/

Packages

CREATE OR REPLACE PACKAGE package_name AS
   -- Declarations (variables, cursors, etc.)
END package_name;
/

CREATE OR REPLACE PACKAGE BODY package_name AS
   -- Implementation (procedures, functions, etc.)
END package_name;
/

This cheat sheet covers some fundamental aspects of PL/SQL. Keep in mind that PL/SQL is tightly integrated with Oracle Database, and its syntax may vary from other procedural languages.