User-Defined Operators
One of the features with regard to objects in the database is the introduction of user-defined operators. This section discusses the methods of defining and invoking user-defined operators in the database. The similarity and differences between user-defined operators and functions and when to use user-defined operators are highlighted.
Defining User-Defined Operators
A user-defined operator is an operator identified by a name that takes a set of operands as input and returns a result. It's a top-level schema object that's created with the CREATE OPERATOR statement and has an operator binding associated with it. An operator binding identifies the operator with a unique signature via argument data types and allows associating a function that provides an implementation for the operator. This function is executed when the operator is invoked. You can evaluate the binding associated with an operator using a user-defined function that could be one of the following:
Stand-alone functions
Packaged functions
OBJECT member methods
You create a user-defined operator by specifying the operator name and its bindings. To do so, first you create a function to implement the binding. Here's an example:
CREATE OR REPLACE FUNCTION f_eq (p1 VARCHAR2, p2 VARCHAR2) RETURN NUMBER
IS
BEGIN
IF p1 = p2 THEN
RETURN 1;
ELSE
RETURN 0;
END IF;
END f_eq;
/
This function gives the functional implementation of EQ.
Next, you define the operator with binding. Here's an example:
CREATE OR REPLACE OPERATOR eq
BINDING (VARCHAR2, VARCHAR2) RETURN NUMBER
USING f_eq;
The argument's data type list enclosed within parentheses is called the signature of the binding and uniquely determines the binding associated with a particular operator. The return data type is not part of the signature.
The RETURN data type of the BINDING clause should be a valid SQL data type and not a PL/SQL data type. For example, if I define a second function f_eq1 in the lines of the f_eq function but returning BOOLEAN values, and then I proceed to define the operator with the associated binding based on f_eq1, I get an error, as shown here:
CREATE OR REPLACE FUNCTION f_eq1 (p1 VARCHAR2, p2 VARCHAR2)
RETURN BOOLEAN
IS
BEGIN
IF p1 = p2 THEN
RETURN TRUE;
ELSE
RETURN FALSE;
END IF;
END f_eq1;
/
Creating an operator based on this function yields an error. This is shown here:
CREATE OR REPLACE OPERATOR eq1
BINDING (VARCHAR2, VARCHAR2) RETURN BOOLEAN
USING f_eq1;
Here's the error:
| Tip� | The argument's data type list and the RETURN data type of the BINDING clause should match exactly with those in the actual function implementation of the binding. |
| Tip� | You can define multiple bindings on an operator as along as each binding has a different signature. |
You can define multiple operators using the same binding function. Here's the code to illustrate this:
CREATE OR REPLACE OPERATOR eq1
BINDING (VARCHAR2, VARCHAR2) RETURN NUMBER
USING f_eq;
User-Defined Operators and Functions
User-defined operators are domain-specific operators that you can use as native SQL built-in operators and integrate into the Oracle9i server. They are another way of defining function-like functionality, especially when the functional logic is complex. When to use user-defined operators is more of a project-specific need than a technical issue. For example, to simulate a complex mathematical function in a way similar to a built-in operator, you can define a user-defined operator.
However, there a few subtle differences between function bindings in operators and user-defined functions:
The signatures of the bindings of a user-defined operator can't have OUT or INOUT parameters. A function can have OUT or INOUT parameters in addition to a return value.The binding functions of a user-defined operator can't perform INSERT, UPDATE, or DELETE operations, whereas these operations are allowed in regular functions.
The return value of a user-defined operator or the parameters of the corresponding function can't be a REF type, whereas regular functions can return REF types and pass REF types as parameters.
When Should Operators Be Used?
User-defined operators are a second way of defining function-like functionality, especially when the functional logic is complex. The question regarding when to use a user-defined operator is more of a project-specific need and design issue than a technical issue. For example, to simulate a complex mathematical function in a way similar to a built-in operator, you can use a user-defined operator. However, for implementing simple functional logic, using an operator would involve an administrative overhead.
An operator is simply a way of calling a function. There's no place where you can use an operator where you couldn't use a function just as well. Why would you want to use operators? Traditionally, when you create a function to use within SQL, it's placed in a package with a RESTRICT REFERENCES PRAGMA. The invocation of that function is "package.<function name>."
It's not necessary to place functions in packages, but failure to do so is considered bad programming practice. If you want to create a reusable component whose status as either an Oracle built-in or a custom package is seamless to the developer (for example, as part of a template library), you can build your function, place it in a package, and place an operator wrapper around it. When operators are used in this way, their limitations (only IN parameters) actually strengthen the case for their use.
Invoking a User-Defined Operator
You can invoke a user-defined operator anywhere you can use a built-in operator in SQL DML. Specifically, you can invoke user-defined operators in
The select list of a SELECT statementThe condition of a WHERE clause
The GROUP BY and ORDER BY clauses
The VALUES clause of an INSERT statement
The SET clause of an UPDATE statement
The WHERE clause of a DELETE statement
Here's an example that provides a simple use of EQ in the WHERE clause of a SELECT statement:
SELECT * FROM employee_test
WHERE EQ(lastname, 'SMITH') = 1;
Here's the output of this SELECT statement:
Here's an example that illustrates the use of EQ in the VALUES clause of an INSERT statement. First, I create a table named test:
SQL> create table test (test number);
Table created.
Next, I insert into this table using EQ:
SQL> insert into test values (eq('ROBERT','SMITH'));
1 row created.
Querying the test table now yields the following:
Also, EQ can be used in an INSERT statement with a SELECT query. Here's an example:
SQL> insert into test select eq('ROBERT','SMITH') from dual;
1 row created.
To use a user-defined operator in an expression, the operator must have been created in the user schema or must have the EXECUTE privilege on it granted to the user schema.
Using User-Defined Operators in PL/SQL
You can use user-defined operators in PL/SQL blocks in the same way you use them in SQL. However, you must use them in SQL statements inside the PL/SQL block. Here's an example that demonstrates the use of the EQ operator described earlier in PL/SQL:
declare
v_empid number;
v_lastname varchar2(30);
v_firstname varchar2(30);
begin
select empid, lastname, firstname
into v_empid, v_lastname, v_firstname
from employee_test
where EQ(lastname, 'SMITH') = 1;
dbms_output.put_line(to_char(v_empid)||' '||v_lastname||' '||v_firstname);
end;
/
Here's the output of this program:
Also, you can use user-defined operators in stand-alone procedures, functions, and packaged procedures and functions in a manner similar to the PL/SQL block outlined earlier.
You can't use user-defined operators in PL/SQL constructs such as the IF clause. The following code causes a compilation error:
declare
string1 varchar2(10) := 'ROBERT';
string2 varchar2(10) := 'SMITH';
begin
if eq(string1, string2) = 1 then
dbms_output.put_line('String1 equals String2');
else
dbms_output.put_line('String1 does not equal String2');
end if;
end;
/
Here's the output of this program:
| Tip� | You can use user-defined operators in SQL DML statements such as SELECT, INSERT, UPDATE, and DELETE. You can't use user-defined operators in PL/SQL constructs such as the IF clause, WHILE and FOR loops, PL/SQL assignment statements, and so on. |
Dropping a User-Defined Operator
You can drop user-defined operators using the DROP OPERATOR statement. Here's an example that illustrates this:
SQL> drop operator eq;
Operator dropped
You can drop an operator that's being referenced by other schema objects such as index types, procedures, functions, packages, and so on using the FORCE option. Here's the syntax:
DROP OPERATOR operator_name FORCE;
Here's an example:
SQL> drop operator eq FORCE;
This invalidates the referencing objects of the operator.
| Tip� | When a user-defined operator is dropped, the underlying binding function isn't dropped. |
| Tip� | For large projects, you should consider using operators as part of template construction. They can effectively be used to provide developer access to functions that can be safely used within SQL. Although they don't provide any additional functionality in comparison with functions, operators can assist with the organization of a code library. |
No comments:
Post a Comment