ss

Oracle Tips by Burleson

PL/SQL Basic Structure

Like the ADA programming language, PL/SQL is based on blocks, and PL/SQL provides a number of different blocks for different uses. The characteristics of a block include:

A block begins with a declarative section where variables are defined.
This is followed by a section containing the procedural statements surrounded by the BEGIN and END key words. Each block must have a BEGIN and END statement, and may optionally include an exception section to handle errors. The exception section is covered later in the book.

Here is an example of a simple block:

SQL> declare
2   v_line varchar2(40);
3 begin
4    v_line := 'Hello World';
5    dbms_output.put_line (v_line);
6 end;
7 /

Hello World

In the example above, the variable v_line is defined in the declarative section on line 2. Like SQL statements, each line ends with a semicolon. Once v_line is defined, it can be used in the procedural section. First, v_line is assigned the literal string ‘Hello World’ on line 4. Strings are surrounded by single quotes in SQL and PL/SQL. The v_line variable is then placed in the output buffer using the procedure dbms_output.put_line .

In PL/SQL , the semicolon defines the end of a line of code. To execute the PL/SQL block, use the forward slash “/” on a line by itself as shown on line 7. If you forget the forward slash, SQL*Plus will simply wait for the next line to be entered.

Note: If you execute a PL/SQL script and SQL*Plus returns a number, it probably is an indication that you forgot to place the”/” at the end of your script. SQL*Plus is actually waiting for the next line. Entering a “/” will execute the script.

A PL/SQL block with no name is called an anonymous block. It starts with the declare key word to define the declarative section.

declare
… define variables here
begin
… code goes here
exceptions
end;

A named block is a procedure or a function. The name portion defines the declarative section so the DECLARE key word is not used.

create procedure my_proc
as
… define variables here
begin
… code goes here
exceptions
end;

A procedure can be passed and change variables. A function can be passed variables and must return a variable.

create function my_func (v_name varchar2)return number
as
    … define variables here
begin
    … code goes here
    return n_jobNum;
end;

When variables are passed to a procedure or function they can be IN, OUT or INOUT. An IN variable is passed into the procedure or function and is used, but can not be changed. An OUT variable is passed to the procedure but it can be changed and left in the changed state when the procedure ends.

An INOUT variable is passed to a procedure or function, and it can be used by the block, changed by the block, and left in a “changed” state when the block ends. A function can only be passed an IN variable and must return a variable. If this is confusing, don’t worry. When we get to name blocks and provide some examples it will be much clearer.

The above book excerpt is from:

Easy Oracle PL/SQL Programming

Get Started Fast with Working PL/SQL Code Examples

ISBN 0-9759135-7-3

John Garmany

http://www.rampant-books.com/book_2005_1_easy_plsql.htm