Call for Oracle support & training (800) 766-1884
Free Oracle Tips

Home
Corporate Oracle Training
Custom Oracle Training
Oracle New Features Training
Advanced Oracle DBA Classes
Oracle Tuning Courses
Oracle Tips & Tricks
Oracle Training Links
Oracle Training Links
Oracle Training Links

We are top for USA Oracle Training Clients

 

Free Oracle Tips


 
HTML Text AOL

Free Oracle App Server Tips


 
HTML Text

Oracle support

Oracle training

Oracle tuning

Rednecks!

Remote Oracle

Custom Oracle Training

 

   
  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

 
 
 

Oracle performance tuning book

 

 

Oracle performance tuning software

 
Oracle performance tuning software
 
Oracle performance Tuning 10g reference poster
 
Oracle training in Linux commands
 
Oracle training Excel
 
 
 
 

 

email BC:


Copyright © 1996 -  2014 by Burleson Inc. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.