| Note: Anonymous blocks are run by copying them to the
SQL*Plus command prompt then hitting the <Enter>key
|
Simplest Anonymous Block |
BEGIN
<valid statement>;
END;
/
|
BEGIN
NULL;
END;
/
|
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(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;
/ |
