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