Anonymous Blocks by malj


									Anonymous Blocks
Anonymous blocks of code are not stored, and not named. They are executed insession
and cannot be called from another session. To execute the same code again,
you must save the anonymous block to an OS file and run it, type it in again, or
include it in a program that executes the block when needed.
You will find throughout the examples that anonymous blocks are used extensively.
Anonymous blocks are perfect for scripting, or activities that you do not wish to
repeat frequently. The following example is an anonymous block:

Versıon 1                               Versıon 2                               Versıon 3
BEGIN                                   DECLARE                                 DECLARE
DBMS_OUTPUT.PUT_LINE(Hello:             v_name VARCHAR2(20);                    v_name VARCHAR2(20) :=
Ayse');                                 BEGIN                                   ‘Ayse’;;
END;                                    V_name := ‘Ayse’;                       BEGIN
/                                       DBMS_OUTPUT.PUT_LINE(Hello:             DBMS_OUTPUT.PUT_LINE(Hello:
                                        '||v_name);                             '||v_name);
                                        END;                                    END;
                                        /                                       /

The block begins with DECLARE or BEGIN and is not stored anywhere once

Output to the Screen Using SQL*Plus and PL/SQL
Oracle provides a built-in package called DBMS_OUTPUT. This package includes a number of subprograms,
including a procedure called PUT_LINE. Using DBMS_OUTPUT.PUT_LINE, we can write
a simple program that displays text when running a program from SQL*Plus.
Let’s take a look at a simple example. The following PL/SQL code prints a line
of text to the screen:
This doesn’t have the intended effect, however. The following is all that is shown
when the code is run from the SQL prompt:
PL/SQL procedure successfully completed.
So, the text wasn’t printed to the screen. This is because of another setting
called SERVEROUTPUT. By default, this is set to OFF. We can set it to ON by
typing the following:
Now, when we run the same code, we get a different result:
Hello Ayse
PL/SQL procedure successfully completed.
The text is displayed to the screen.

■ PL/SQL block structure
■ Variable declarations
■ Literals, characters, and reserved words
■ Data types available for PL/SQL
■ Wrapper utility to hide code

The basic program unit in PL/SQL is called a block.Blocks contain sets of instructions
for Oracle to execute, display information to the screen, write to files, call other
programs, manipulate data, and more. All PL/SQL programs are made of at least
one block. Methods of implementation range from programs that executed one time
only and are not stored anywhere, to blocks that are stored in the database for later
use. Blocks support all DML statements, and using Native Dynamic SQL (NDS) or
the built-in DBMS_SQL (see Appendix B for more information on DBMS_SQL), they
can run DDL statements.

The Basic Structure
The minimum structure for a PL/SQL block is a BEGIN and an END with at least one
executable command in between. The following block successfully compiles and
runs, and is the most basic statement you can create:
46 Oracle Database 10 gPL/SQL Programming
If we were to omit the NULL from the preceding statement, it would generate the
following exception:
ERROR at line 2:
ORA-06550: line 2, column 1:
PLS-00103: Encountered the symbol "END" when expecting one of the following:
begin case declare exit for goto if loop mod null pragma
raise return select update while with <an identifier>
So, a block mustcontain some set of instructions, even if those instructions say
to do nothing, or NULL. As shown in Figure 3-1, the section between the BEGIN
and END commands is called the EXECUTION section. All types of PL/SQL blocks
support two other optional sections; the DECLARATION and EXCEPTION sections.
All three are discussed in detail next.
The Declaration Section
The DECLARATION section is optional. It is used to list the variables used in the
block along with the types of data they support. Cursors (discussed in Chapter 4)
are also declared in this section. This is the place where all local variables used
in the program are defined and documented.

The following declaration section lists variables that will be used later in the
block, defines the type of data that will be stored in each variable, and in one case,
initializes the variable:
-- Available online as part of BlockStructure.sql
v_date_time TIMESTAMP;
The block begins with DECLARE, telling the PL/SQL compiler the type of code
that comes next. The variable V_DATE_TIME is of type TIMESTAMP, so only
compatible data can be stored in it.
The Execution Section
This section is the only one required. The cont
The Execution Section
This section is the only one required. The contents must be complete to allow the
block to compile. By complete, we mean that a complete set of instructions for the
PL/SQL engine must be between the BEGIN and END keywords. As you saw earlier
with an execution section of NULL, compiled code does not mean it must actually
perform an action.
The execution section supports all DML commands and SQL*Plus built-in functions.
It supports DDL commands using Native Dynamic SQL (NDS) and/or the DBMS_SQL
built-in package.
The following example shows just the EXECUTION section of a block:
-- Available online as part of BlockStructure.sql
-- Retrieve the timestamp into a variable
SELECT systimestamp
INTO v_date_time
FROM dual;
-- Display the current time to the screen
The EXECUTION section starts with BEGIN. In this example, the system time is
retrieved and stored in the variable declared in the DECLARATION section. It is then
displayed on the screen using the built-in package DBMS_OUTPUT.
The Exception Section
The EXCEPTION section is optional and traps errors generated during program
execution. This section can trap for specific errors using functions provided in the
statements (for an example of using the EXCEPTION_INIT pragma, see the
CreateUser.sql script included online with this chapter). Chapter 7 covers
exceptions in detail.
The following exception section uses WHEN OTHERS to trap any error and
perform an action:
-- Available online as part of BlockStructure.sql
The action in this case was to display the error message to the screen.
Anonymous Blocks
Anonymous blocksare not given a name and are not stored in the database. They
can call other programs, but they cannot be called themselves (how do you call
something without a name!). The next example is an anonymous block that performs a row
count of the number of books each author has written, and displays the output

To top