Oracle PL/SQL Tutorial - PL/SQL Blocks

• « Previous
• Next »

In PL/SQL the smallest meaningful grouping of code is known as a block.

A block code provides execution and scoping boundaries for variable declarations and exception handling.

PL/SQL allows you to create anonymous blocks and named blocks.

Named blocks may be packages, procedures, functions, triggers, or object types.

PL/SQL is the Procedure Language extension for SQL and it has a block structure.

Every piece of code executes in a block.

A PL/SQL block has up to four different sections, only one of which is mandatory:

Syntax

PL/SQL has four keywords:

DECLARE: Every PL/SQL block has a declaration section.

This is where you allocate memory for cursors, data type definitions, variables, embedded functions, and procedures.

BEGIN: Every PL/SQL block has an executable section.

It starts with the keyword BEGIN.

BEGIN marks the beginning of where you put your program logic.

And every PL/SQL program must have at least one line of executable code, even if it's the keyword NULL, which means no operation.

EXCEPTION: Every PL/SQL block has an exception-handling section.

It starts with the keyword EXCEPTION.

This is where you will catch any database or PL/SQL errors.

Like the declaration section, you won't use an exception-handling section, but it's still there.

END: Every PL/SQL block ends with the keyword END.

Anonymous Blocks

We'll start with an anonymous PL/SQL block.

Anonymous blick is not saved in the database.

They're perfect for creating test units.

The general syntax of an anonymous PL/SQL block is as follows:

[ DECLARE ... declaration statements ... ] 
BEGIN ... one or more executable statements ... 
[ EXCEPTION 
... exception handler statements ... ] 
END; 

The square brackets indicate an optional part of the syntax.

You must have BEGIN and END statements, and you must have at least one executable statement.

Note

The following code is an example of an anonymous PL/SQL block.

Comments describe the different sections.

-- This is an anonymous procedure, so it has no name
declare-- w w  w.  j a v a  2  s  .  c o  m
  /* 
   declare local cursors, variables, and methods here.
   We don't need to have a declaration section.
  */
begin
  -- You write your logic here
  null;  -- at least one command!
exception
  when NO_DATA_FOUND then
    raise_application_error(-20000, 'No Data Found!');
end;
/   -- the forward slash says execute this procedure


set serveroutput on size 1000000 

begin
   SYS.DBMS_OUTPUT.put_line('Hi from java2s.com!'); 
end; 
/ 

The first line tells SQL*Plus to echo the database's output for your session to the screen after the PL/SQL procedure is finished executing.

Note that set serveroutput on size 1000000 is a SQL*Plus command and is not part of the anonymous block.

Example

The following code shows a bare minimum anonymous block:

BEGIN 
    DBMS_OUTPUT.PUT_LINE(SYSDATE); 
END; 

The code above generates the following result.

The following code is a functionally similar block with a declaration section:

DECLARE 
    l_right_now VARCHAR2(9); 
BEGIN 
    l_right_now := SYSDATE; 
    DBMS_OUTPUT.PUT_LINE (l_right_now); 
END; 
/

The code above generates the following result.

The following code adds an exception handler.

DECLARE -- w  w  w. j  a  v  a  2 s .c  o m
    l_right_now VARCHAR2(9); 
BEGIN 
    l_right_now := SYSDATE; 
    DBMS_OUTPUT.PUT_LINE (l_right_now); 
EXCEPTION WHEN VALUE_ERROR 
    THEN
     DBMS_OUTPUT.PUT_LINE('error!'); 
END; 
/

The code above generates the following result.

Exceptions

Exceptions allow you to catch errors as your PL/SQL program executes.

PL/SQL predefines more than 20 named exceptions.

There are a handful of commonly seen exceptions.

Two exceptions are very common:

NO_DATA_FOUND: You'll get a NO_DATA_FOUND exception anytime you code a SQL SELECT statement that does not return any rows.

TOO_MANY_ROWS: If you code a SELECT statement where you expect only one row but you get more than one, you'll get a TOO_MANY_ROWS exception.

Catching an Exception

You add the keyword EXCEPTION between the keywords BEGIN and END in order to add an exception-handling section to your PL/SQL block.

Once you do that, any error that occurs between the keywords BEGIN and EXCEPTION will be handled by your exception-handling section.

Nested Blocks

You can nest PL/SQL blocks.

To nest a PL/SQL block means to embed one or more PL/SQL blocks inside another PL/SQL block.

Here's a PL/SQL example showing a procedure containing an anonymous, nested block:

create or replace PROCEDURE calc_totals 
   ...-- w  ww. j a va2s .c om
BEGIN
     ...
    /* Beginning of nested block */
    DECLARE 
        month_total NUMBER; 
    BEGIN 
        month_total := year_total / 12; 
    END set_month_total; 
    /* End of nested block */ 
    ...
END; 
/

exec calc_totals();

Example 3

You can wrap PL/SQL or SQL statements in their own PL/SQL block, and catch an exception generated within that block.

--   w  ww.  java  2s  .  c o m
declare 
    v_max_line varchar2(32767); 
begin 
    -- The next three lines initialize a variable v_max_line with 32,768 spaces. 
    for i in 1..32768 loop 
        v_max_line := v_max_line || ' '; 
    end loop; 
    SYS.DBMS_OUTPUT.put_line('Test a line > 32767'); 
    begin 
        SYS.DBMS_OUTPUT.put_line(v_max_line||' '); 
    exception 
        when OTHERS then 
        SYS.DBMS_OUTPUT.put_line(SQLERRM); 
    end; 
end; 
/ 

The code above generates the following result.

• Next »
• « Previous