 |
|
Oracle Tips by Burleson |
Calling PL/SQL Functions from
SQL*Plus
To call a function in SQL*Plus, simply
embed it in a SQL statement. Below we execute the f2c function
by selecting it from the pseudo table called dual. The “Dual”
pseudo-table allows you to select from it to execute functions.
SQL> select f2c(40) from dual;
F2C(40)
----------
4.44444444
The function can be applied to any select
statement that passed the correct datatype.
SQL> select f2c(quantity) from sales;
F2C(QUANTITY)
-------------
537.777778
-12.222222
93.3333333
204.444444
426.666667
82.2222222
482.222222
…
37.7777778
4426.66667
3037.77778
4926.66667
4871.11111
37.7777778
100 rows selected.
The above example calculates the degrees Celsius
from all the quantity values in the sales table
(pretending that they are actually degrees Fahrenheit). For each
row, the f2c function was executed, passing in the value of
quantity for that row and returning that value converted to
degrees Celsius.
Below is an example of a function that takes an
author’s first and last name and returns a formatted complete name.
Notice that it used the %typedefinition for the passed in the variables.
SQL> create or replace function full_name
2 (v_fname IN author.author_first_name%type,
3 v_lname IN author.author_last_name%type)
4 return varchar2
5 as
6 begin
7 return initcap(v_fname||' '||v_lname);
8 end;
9 /
Function created.
SQL> select
2 full_name(author_first_name, author_last_name) from author;
FULL_NAME(AUTHOR_FIRST_NAME,AUTHOR_LAST_NAME)
----------------------------------------------------
Mark Jones
Alvis Hester
Erin Weaton
Pierre Jeckle
Lester Withers
Juan Petty
Louis Clark
Minnie Mee
Dirk Shagger
Diego Smith
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
|
