 |
|
|
|
|
|
Oracle provides a rich set of functions to extend the power of the database. Functions add to standard
SQL and provide a rich toolset for manipulating and massaging data in SQL queries. Oracle provides
functions for math operations, string operations, analytic functions, and XML processing, among
others. Oracle also allows you to create your own functions in much the same way as you would
create custom procedures. After reading this chapter, you will be comfortable using the Oracle-defined
functions in your SQL statements, be able to create your own custom functions, and be familiar
with the range of functions that Oracle provides in 10g.
What Is a Function?
Functions are blocks of code that perform an action and that are referenced by an identifier and
can be passed arguments. Functions in Oracle are very similar to object methods in languages like
Java or functions or procedures in languages like Visual Basic and C. Functions are different than
procedures, as they return a result after they execute and can return multiple results through the
use of OUT parameters. Functions can be created as standalone objects or included in a package
with other objects.
Oracle provides a rich set of functions with the database, but if you need something special, you
can create your own. Before you create a new function, it’s good practice to review what Oracle
provides for you out of the box, as Oracle’s functions are highly optimized.
Defining Your Own Functions
If Oracle doesn’t come with what you need, you can always define your own functions. Functions
are defined just like other items in the database. You create functions in PL/SQL or Java to extend
the functionality of the database.The general form of a function declaration is as follows:
CREATE [OR REPLACE] FUNCTION function_name
[([arg [{IN | OUT}] datatype, .....]
RETURN datatype {IS | AS} function_body_here
Functions are declared in a very similar way to procedures. You can specify CREATE OR REPLACE, you
can name the function, and you can specify several arguments that can be either IN or OUT. With a function,
the arguments are optional, but you must specify a RETURN type, as functions must return a value.
The following example creates a function that converts Fahrenheit to Celsius. You pass a FLOAT into the
function as an input parameter, and the function returns a FLOAT representing the temperature in Celsius.
CREATE OR REPLACE FUNCTION FtoC(F FLOAT)
RETURN FLOAT IS
BEGIN
RETURN (5/9)*(F-32);
END FtoC;
Functions can also operate on table data, as shown in the following example. You should create this
function in the SCOTT schema if you want to execute it.
CREATE OR REPLACE FUNCTION moneyMaker(eid emp.EMPNO%type)
RETURN VARCHAR2 IS
salary NUMBER;
BEGIN
SELECT SAL into salary FROM emp WHERE empno = eid;
IF salary >= 3000 THEN
RETURN ‘THIS PERSON MAKES $$!’;
ELSE
RETURN ‘THIS PERSON NEEDS A RAISE’
END IF;
END moneyMaker;
This function takes an employee ID as input and determines his or her current salary status. This function
uses the %type type attribute to get the data type from the field directly.
When you create a function, Oracle compiles it and stores the result in the database. That way, when you
go to use it, execution is fast, since the code is precompiled. Oracle also checks for any dependencies and
recompiles any dependent objects when needed.
Permissions to Create Functions
You must have CREATE PROCEDURE permission to be able to create functions in your schema. You need
the EXECUTE permission granted to you to run functions in another user’s schema.Where Can Functions Be Used?
Functions can be used anywhere you can have an expression. They can be used in a SELECT statement, a
WHERE clause, the CONNECT BY, START WITH, ORDER BY, and GROUP BY clauses of a SELECT, the VALUES
clause of an INSERT, and the SET clause of an UPDATE.
266
Chapter 13
Defining a Java Function
In recent versions of Oracle, you can also create functions in Java. To do this, you must follow four steps:
? Create your Java program exposing public static methods for your functions
? Compile your class file
? Upload the class file into Oracle using the LoadJava utility
? Create a PL/SQL wrapper for the Java function
The following example shows how to implement the temperature conversion function to Java.
First, create a class exposing a public static method:
public class f
public static double f2c(double f){
double c = (5.0 / 9.0) * (f - 32.0);
return c
}
}
Second, compile the class using javac:
javac FtoCjava.java
Next, upload the java class to the SCOTT schema in Oracle using LoadJava. You may have to first grant
the JAVAUSERPRIV privilege to SCOTT:
LoadJava –verbose –schema scott -thin -user scott/tiger@localhost:1521:orcl
FtoCjava.class (this should be on one line).
Finally, create a PL/SQL wrapper to call the function:
CREATE OR REPLACE FUNCTION FtoCJ(F NUMBER) RETURN NUMBER
AS LANGUAGE JAVA NAME ‘FtoCjava.f2c(double) return double’;
Then calling the function would return the following:
SQL> select FtoCJ(212) from dual;
FTOCJ(212)
----------SapereOnLine
100 |
|
|
|
|
|
|
|
ORACLE
|
|
|
|
|
| |
|
 |
|
|
|
|
