 |
|
Oracle Tips by Burleson |
Calling Procedures from SQL*Plus
To call a procedure from SQL*Plus use the
execute command.
SQL> set
serveroutput on
SQL> execute num_check(5);
Number OK
PL/SQL
procedure successfully completed.
Because this procedure writes data to the buffer,
SQL*Plus must be told to retrieve and display it. The SET
SERVEROUTPUT ON
command directs SQL*Plus to do
this. The execute command runs the num_check procedure passing
the literal number 5 as the IN variable. In order to execute a
procedure from SQL*Plus that contains an OUT or INOUT variable, a
SQL*Plus variable must be created and used. In the example below, the
procedure get_area is created and then executed being passed
three variables. It then calculates the area and copies that value to
the OUT variable n_area.
SQL> create
or replace procedure get_area
2 (n_length in number,
3 n_width in number,
4 n_area out number)
5 as
6 begin
7 n_area := n_length*n_width;
8 end get_area;
9 /
Procedure
created.
SQL> variable
w_area number;
SQL> exec get_area(10,20,:w_area);
PL/SQL
procedure successfully completed.
SQL> print
w_area
W_AREA
----------
200
The variable w_area is the SQL*Plus
variable. It is passed to the procedure by reference as indicated by
the colon at the beginning of the variable name.
PL/SQL does not impose a limit on the number of
variables that are passed into a procedure; however, the practical
limit is based on user requirements. The more variables that are
passed to a procedure, the harder it is to use and the more likely
that the developer will cause errors. If the number of variables is
too large, look at creating compound variables such as records, list
or arrays. Passing five to six variables is about the practical
limit. If the procedure is going to be overloaded in a package, that
limit is reduced to 3 or 4 variables. Packages and overloading
procedures
is covered later in chapter 5
Bulk Operations, Packages and
Triggers.
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
|
