Anonymous Blocks – Constants & Variables

Description: Anonymous blocks are used in Oracle tools where PL/SQL code is either executed immediately (SQL*Plus) or attached directly to objects in that tool environment. That object then provides the context or name for the PL/SQL code, making the anonymous block both the most appropriate and the most efficient way to attach programmatic functionality to the object.

--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(10,8) := 2;
 pi      CONSTANT NUMBER(8,7) DEFAULT 3.1415926;
BEGIN
  DBMS_OUTPUT.put_line(counter);
  DBMS_OUTPUT.put_line(pi);
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) := 2;
 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);
  DBMS_OUTPUT.put_line(pi);
END;
/

Enjoyed this post? Share it!

 

Leave a comment

Your email address will not be published.