Oracle PL/SQL: Anonymous blocks: Constants and Variables - Anonymous blocks are used in Oracle tools where PL/SQL code is either executed immediately (SQL*Plus

Quick Search:

Oracle PL/SQL: Anonymous blocks: Constants and Variables

Jump to:

Category: >> Oracle PL/SQL >> Anonymous blocks: Constants and Variables

<< last

next >>

Snippet Name: Anonymous blocks: Constants and 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.

Also see:
» Declaring Variables
» SEQUENCE: get sequence value into vari...

Comment: (none)

Language: PL/SQL
Highlight Mode: PLSQL
Last Modified: March 14th, 2009

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