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 Concepts by Burleson Consulting

Creating and Executing PL/SQL

Since most of you already have experience with traditional procedural languages we won't belabor the special features of PL/SQL.  The PL/SQL language is just the same as any other procedural language in its support for variable definition, Boolean logic, looping support, and support for internal RAM array processing.

The assigned chapters from the Feuerstein book may seem like a lot of reading, but it's very important for you to understand all of the different constructs within the PL/SQL language and hopefully relate them to other languages such as C++ for which you may already be familiar.

Once you've completed the readings on the basic features of PL/SQL we will reinforce these concepts by asking you to write several small PL/SQL code snippets to demonstrate your abilities at using PL/SQL to effectively solve business problems.

Let’s begin our discussion by looking at a simple PL/SQL anonymous code block:

This is how your PL/SQL scripts will look when you name them on your PC as executable files.  Note that PL/SQL, just like SQL, must be named in a file with a .sql suffix in order to be executed without the fully-qualified path name.

Let’s examine each section of this code:

The set serveroutput command is required when you are using dbms_output.put_line to display information from your PL/SQL.  Hence, you may want to get into the habit of making this command the first line in every PL/SQL snippet that you write.

Next, we have the DECLARE section.  This section is used to declare all cursors and variables that are used in your PL/SQL program.  As a matter of convention, most PL/SQL programmers will start all variables with a v_ character to easily identify them as program variables. One really nice feature of PL/SQL is the %type operator. Using this operator, you do not have to know the exact datatype for your variables.  In the example below, we see that v_emp_salary is set to be the same datatype as the emp_salary column of the emp table.

Finally, the BEGIN and END operators bound the body of the PL/SQL program.  Within this section, all process logic, loops, and I/O are contained. 

   select
      initcap(emp_last_name),
      emp_salary
   into
      v_emp_last_name,
      v_emp_salary
   from
      emp
   where
      emp_last_name = 'johnson';

In the example above, we see an SQL statement embedded directly inside the PL/SQL, and the use of the INTO clause to transfer the Oracle database columns directly into PL/SQL variables.  SQL that returns single rows can appears at any point in the BEGIN-END section, but SQL that returns multiple rows must be defined in the DECLARE section as a cursor.

   dbms_output.put_line('Employee '||v_emp_last_name||' has a salary of '||v_emp_salary);

Next, we see how to display output in PL/SQL.  PL/SQL is very powerful, and you can make PL/SQL write out to data files of display output interactively.  In this case, we use the dbms_output PL/SQL package to display our output.  Within this PL/SQL package we have a PL/SQL stored procedure called put_line to display program variable contents. 

END;
/

Finally, we come to the end of the PL/SQL.  Here we see the END statement, terminated with a semicolon.  Note that in PL/SQL, all statements must be ended with a semicolon.  At the very end we see the forward-slash character.  This character is required in all PL/SQL because it serves as a signal to SQL*Plus to begin parsing and executing the PL/SQL code snippet.

More on dbms_output.put_line

As you begin to de-bug your PL/SQL you will commonly place temporary dbms_output.put_line statements in your code to trace program execution.  Note that the put_line display function is written in PL/SQL.  While we will introduce PL/SQL packages in the next tutorial, a PL/SQL package is a collection of stored procedures and functions.  In Oracle, package interfaces can be viewed using the SQL*Plus describe command, just like a table:

SQL> desc dbms_output 

PROCEDURE DISABLE
PROCEDURE ENABLE
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 BUFFER_SIZE                    NUMBER(38)              IN     DEFAULT
PROCEDURE GET_LINE
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 LINE                           VARCHAR2                OUT  
 STATUS                         NUMBER(38)              OUT  
PROCEDURE GET_LINES
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 LINES                          TABLE OF VARCHAR2(255)  OUT  
 NUMLINES                       NUMBER(38)              IN/OUT
PROCEDURE NEW_LINE
PROCEDURE PUT
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 A                              VARCHAR2                IN   
PROCEDURE PUT
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 A                              NUMBER                  IN   
PROCEDURE PUT_LINE
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 A                              VARCHAR2                IN   
PROCEDURE PUT_LINE
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 A                              NUMBER                  IN   

Above, we described the dbms_output packages and we see the names of all stored procedures and functions within the package.  When we look for the put_line procedure, we see that it accepts a single input argument of type VARCHAR2.  This is known as a procedure prototype.  A prototype tells you the type of argument that you may pass to any PL/SQL procedure or function.  In this case, if we want to display multiple character columns, we must use the concatenation “||” symbol.  For example:

dbms_output.put_line(
   ‘The current customer ‘||
    v_cust_name||
   ’ has a low balance.’
);

If we want to display a number with put_line, we must use the to_char BIF to convert it to a character.  For example:

dbms_output.put_line(
   ‘The account balance for ‘||
    v_cust_name||
   ’ is ‘||
    to_char(v_balance)
);

We will be using dbms_output extensively through the remainder of this course,  you should get familiar with using the put_line procedure to display PL/SQL variables.

Now, if you have completed the Feuerstein readings, you are ready to try some hands-on exercises and assignments.



For more details, see the "Easy Oracle Series" a set of books especially designed by Oracle experts to get you started fast with Oracle database technology.


Download your Oracle scripts now:

www.oracle-script.com

The definitive Oracle Script collection for every Oracle professional DBA

 
 
 

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.