 |
|
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
|
|
