Oracle Consulting Oracle Training Oracle Support Development
Oracle Books
SQL Server Books
IT Books
Job Interview Books
Rampant Horse Books
911 Series
Pedagogue Books

Oracle Software
Write for Rampant
Publish with Rampant
Rampant News
Rampant Authors
Rampant Staff
Oracle News
Oracle Forum
Oracle Tips
Articles by our Authors
Press Releases
SQL Server Books

Oracle 11g Books

Oracle tuning

Oracle training

Oracle support

Remote Oracle


Privacy Policy




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. 

    …  define variables here
    …  code goes here

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
    …  define variables here
    …  code goes here

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
    …  define variables here
    …  code goes here
    return n_jobNum;

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

Linux Oracle commands syntax poster

ION Oracle tuning software

Oracle data dictionary reference poster

Oracle Forum

BC Oracle consulting support training

BC remote Oracle DBA   



 Copyright © 1996 -2017 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation. SQL Server® is the registered trademark of Microsoft Corporation. 
Many of the designations used by computer vendors to distinguish their products are claimed as Trademarks