Oracle Anonymous Blocks, Constants, Variables, and Type Declarations
Version 11.2.0.3

General Information
Note: Anonymous blocks are run by copying them to the SQL*Plus command prompt then hitting the <Enter> key
 
Anonymous Blocks
Simplest Anonymous Block BEGIN
  <valid statement>;
END <block_name>;
/
BEGIN
  NULL;
END;
/

BEGIN
  NULL;
END test_block;
/
Anonymous Block With Error Exception Handler BEGIN
  <valid statement>;
EXCEPTION
  <exception handler>;
END;
/
BEGIN
  NULL;
EXCEPTION
  WHEN OTHERS THEN
    NULL;
END;
/
Nested Anonymous Blocks With Exception Handlers BEGIN
  <valid statement>;
  BEGIN
    <valid statement>;
  EXCEPTION
    <exception handler>;
  END;
EXCEPTION
  <exception handler>;
END;
/
BEGIN
  NULL;
  BEGIN
    NULL;
  EXCEPTION
    WHEN OTHERS THEN
     NULL;
  END;
  NULL;
EXCEPTION
  WHEN OTHERS THEN
    NULL;
END;
/
Nested Anonymous Blocks With Variable Declaration And Exception Handler That Does Real Work DECLARE
 <variable name> <data type><(length precision)>;
BEGIN
  <valid statement>;
  BEGIN
    <valid statement>;
  EXCEPTION
    <exception handler>;
  END;
EXCEPTION
  <exception handler>;
END;
/
set serveroutput on

DECLARE
 x NUMBER(4);
BEGIN
  x := 1000;
  BEGIN
    x := x + 100;
  EXCEPTION
    WHEN OTHERS THEN
      x := x + 2;
  END;
  x := x + 10;
  dbms_output.put_line(x);
EXCEPTION
  WHEN OTHERS THEN
    x := x + 3;
END;
/
 
Constants & Variables
Constants DECLARE
 <constant name> CONSTANT <data type> := <value>;
 <constant name> CONSTANT <data type> DEFAULT <value>;
BEGIN
  <valid statement>;
EXCEPTION
  <exception handler>;
END;
/
set serveroutput on

DECLARE
 counter CONSTANT NUMBER(3) := 2;
 pi      CONSTANT NUMBER(8,7) DEFAULT 3.1415926;
 today   CONSTANT VARCHAR2(30) := ' today is ' || SYSDATE;
BEGIN
  dbms_output.put_line('Counter: ' || counter);
  dbms_output.put_line('Pi:      ' || pi);
  dbms_output.put_line('Today:  ' || today);
END;
/
Variables DECLARE
 <variable name> <data type>;
 <variable name> CONSTANT <data type> := <value>;
 <variable name> CONSTANT <data type> NOT NULL := <value>;
BEGIN
  <valid statement>;
EXCEPTION
  <exception handler>;
END;
/
set serveroutput on

DECLARE
 counter NUMBER(10,8) := 0.11235813;
 pi      NUMBER(8,7) := 3.1415926;
 test    NUMBER(10,8) NOT NULL := 10;
BEGIN
  counter := pi/counter;
  pi := pi/3;
  dbms_output.put_line('Counter/Pi: ' || counter);
  dbms_output.put_line('Pi:         ' || pi);
  dbms_output.put_line('Test:       ' || test);
END;
/
Declaring Constants and Variables in nested blocks and nested block naming DECLARE
 <variable name> <data type>;
 <variable name> CONSTANT <data type> := <value>;
 <variable name> CONSTANT <data type> NOT NULL := <value>;
BEGIN
  <valid statement>;
  DECLARE
   <variable name> <data type>;
   <variable name> CONSTANT <data type> := <value>;
   <variable name> CONSTANT <data type> NOT NULL := <value>;
  BEGIN
    <valid_statement>;
  EXCEPTION
    <exception handler>
  END;
EXCEPTION
  <exception handler>;
END;
/
set serveroutput on

DECLARE
  i PLS_INTEGER;
BEGIN
  i := 1;
  <<nb>> DECLARE
   i VARCHAR2(5) := 'ABC';
  BEGIN
    dbms_output.put_line(nb.i);
  EXCEPTION
    WHEN OTHERS THEN
      dbms_output.put_line('Exception Trapped 1');
  END nested;
  dbms_output.put_line(i);
EXCEPTION
  WHEN OTHERS THEN
    dbms_output.put_line('Exception Trapped 2');
END outer;
/

-- potential scoping error
DECLARE
  i PLS_INTEGER;
BEGIN
  i := 1;
  <<nb>> DECLARE
   i VARCHAR2 := 'ABC';
  BEGIN
    dbms_output.put_line(i);
    dbms_output.put_line(nb.i);
  EXCEPTION
    WHEN OTHERS THEN
      dbms_output.put_line('Exception Trapped 1');
  END nested;
EXCEPTION
  WHEN OTHERS THEN
    dbms_output.put_line('Exception Trapped 2');
END outer;
/

-- scoping error
DECLARE
  i PLS_INTEGER;
BEGIN
  i := 1;
  <<nb>> DECLARE
   i PLS_INTEGER := 2;
  BEGIN
    NULL;
  EXCEPTION
    WHEN OTHERS THEN
      dbms_output.put_line('Exception Trapped 1');
  END nested;
  dbms_output.put_line(i);
  dbms_output.put_line(nb.i);
EXCEPTION
  WHEN OTHERS THEN
    dbms_output.put_line('Exception Trapped 2');
END outer;
/
Declaring Subtypes DECLARE
 SUBTYPE <variable name> IS <data_type>;
BEGIN
  <valid statement>;
EXCEPTION
  <exception handler>;
END;
/
set serveroutput on

DECLARE
 SUBTYPE ssn_t IS VARCHAR2(11);
 vSSN    ssn_t;
BEGIN
  vSSN := '555-11-2367';
  dbms_output.put_line(vSSN);
END;
/
 
Demo
Constants and Literals set timing on

DECLARE
 x CONSTANT NUMBER := 1;
 y NUMBER;
BEGIN
  FOR i IN 1..1000000 LOOP
    SELECT COUNT(*)
    INTO y
    FROM servers
    WHERE rownum = x;
  END LOOP;
END;
/

DECLARE
 x CONSTANT NUMBER := 1;
 y NUMBER;
BEGIN
  FOR i IN 1..1000000 LOOP
    SELECT COUNT(*)
    INTO y
    FROM servers
    WHERE rownum = 1;
  END LOOP;
END;
/

Related Topics
DDL Triggers
Functions
Instead-Of Triggers
Operators
Packages
Pipelined Table Functions
Procedures
System Event Triggers
Table Triggers

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2013 Daniel A. Morgan All Rights Reserved