| General |
| Data Dictionary Objects Related To Operators |
operator$
| DBA |
ALL |
USER |
| dba_operator_comments |
all_operator_comments |
user_operator_comments |
| dba_operators |
all_operators |
user_operators |
|
| Privileges Related To Operators |
create any operator
create operator
drop any operator
execute any operator |
| Note:
Oracle allows developers of object-oriented applications to extend the list of built-in
relational operators (for example, +, -, /, *, LIKE, AND, OR) with domain specific
operators (for example, Contains, Within_Distance, Similar) called user-defined operators.
A user-defined operator can be used anywhere built-in operators can be used, for example,
in the select list or the where clause. Similar to built-in operators, a user-defined
operator may support arguments of different types, and that it may be evaluated using an
index. Similar to built-in operators, user-defined operators allow efficient content-based
querying and sorting on object data. |
| |
| Create Operator |
| Create Operator |
CREATE OR REPLACE OPERATOR <operator_name>
BINDING (data_type_in) RETURN <data_type_out> USING <function_name>; |
| Multiple Binding Operator Creation |
CREATE OR REPLACE OPERATOR
contains
BINDING(data_type_in) RETURN <data_type_out>
USING <function_name>,
(data_type_in) RETURN <data_type_out>
USING <function_name>; |
| |
| Comment Operator |
| This functionality, while documented, was
never implemented. The Oracle documentation is incorrect. |
| |
| Alter Operator |
| Recompile |
ALTER OPERATOR <operator_name> COMPILE; |
| ALTER OPERATOR contains COMPILE; |
| |
| Drop Operator |
| Drop Operator |
DROP OPERATOR <operator_name>; |
| DROP OPERATOR contains; |
| |
| Single Binding
Operator Demo |
| Operator Function |
CREATE OR REPLACE FUNCTION fn_contains(
stringin VARCHAR2,
valuein VARCHAR2)
RETURN NUMBER IS
BEGIN
IF INSTR(stringin, valuein, 1, 1) = 0 THEN
RETURN 0;
END IF;
RETURN 1;
END fn_contains;
/ |
| Single Binding Operator Creation |
CREATE OR REPLACE OPERATOR contains
BINDING(VARCHAR2, VARCHAR2)
RETURN NUMBER USING fn_contains;
|
| Simple Operator Demo Table And Data |
CREATE TABLE optab (
test VARCHAR2(20));
INSERT INTO optab VALUES ('Dan Morgan');
INSERT INTO optab VALUES ('J Sweet');
INSERT INTO optab VALUES ('Liz Scott');
INSERT INTO optab VALUES ('Capable');
COMMIT; |
| Operator Demonstration |
SELECT *
FROM optab
WHERE contains(test, 'a') = 1;
SELECT *
FROM optab
WHERE contains(test, 'Morgan') = 1;
SELECT *
FROM optab
WHERE contains(test, ' ') = 1; |
| |
| Multiple Binding
Operator Demo |
| Second Operator Function |
CREATE OR REPLACE FUNCTION fn_int_contains(
numbin NUMBER,
valuein NUMBER)
RETURN NUMBER IS
numinstr VARCHAR2(100);
valinstr VARCHAR2(100);
BEGIN
numinstr := TO_CHAR(numbin);
valinstr := TO_CHAR(valuein);
IF INSTR(numinstr, valinstr, 1, 1) = 0 THEN
RETURN 0;
END IF;
RETURN 1;
END fn_int_contains;
/ |
| Multiple Binding Operator Creation |
CREATE OR REPLACE OPERATOR contains
BINDING(VARCHAR2, VARCHAR2)
RETURN NUMBER USING fn_contains,
(NUMBER, NUMBER) RETURN NUMBER
USING fn_int_contains; |
| Multiple Bindings Operator Demo Table And Data |
CREATE TABLE numtab (
test NUMBER(10));
INSERT INTO numtab VALUES (000010000);
INSERT INTO numtab VALUES (213567);
INSERT INTO numtab VALUES (9835456);
INSERT INTO numtab VALUES (27334);
COMMIT; |
| Multiple Binding Operator Demonstration |
SELECT *
FROM optab
WHERE contains(test, 'a') = 1;
SELECT *
FROM optab
WHERE contains(test, 'Morgan') = 1;
SELECT *
FROM optab
WHERE contains(test, ' ') = 1;
SELECT *
FROM numtab
WHERE contains(test, 1) = 1;
SELECT *
FROM numtab
WHERE contains(test, 35) = 1; |
| |
| Add Binding |
| Bind a new function
into an existing operator |
ALTER OPERATOR <operator_name>
ADD BINDING <input_parameters> RETURN <data_type>
USING <function_name>; |
CREATE OR REPLACE OPERATOR contains
BINDING(VARCHAR2, VARCHAR2) RETURN NUMBER USING fn_contains;
ALTER OPERATOR contains
ADD BINDING (NUMBER, NUMBER) RETURN NUMBER
USING fn_int_contains; |
| |
| Add Comments |
| Comment an operator |
COMMENT ON OPERATOR <operator_name>
IS '<comment_text>'; |
COMMENT
ON OPERATOR contains IS 'This is a user created operator';
set linesize 131
col comments format a60
SELECT *
FROM user_operator_comments; |
| |
| And Not Demo |
| The AndNot Operator is intended to check a
string or number to see if it contains one nested element but does not contain a second
nested element. |
| Demo Table & Data |
CREATE TABLE ant (
memo_fld VARCHAR2(100));
INSERT INTO ant VALUES
('The quick brown fox jumped over the lazy dogs.');
INSERT INTO ant VALUES
('I feel a lot more like I do now than I did at 11 last night.');
INSERT INTO ant VALUES
('There are three erors in this statment. True or false?');
INSERT INTO ant VALUES
('There are only 10 kinds of people in the world. Those who understand binary and those
who don''t.');
INSERT INTO ant VALUES ('520-34-5678');
INSERT INTO ant VALUES ('206-555-1212');
COMMIT; |
| Function For String Handling |
CREATE OR REPLACE FUNCTION
AndNotStr (
evalstr VARCHAR2,
str1in VARCHAR2,
str2in VARCHAR2)
RETURN NUMBER IS
x BOOLEAN := FALSE;
NoGood EXCEPTION;
BEGIN
IF INSTR(evalstr, str1in, 1, 1) = 0 THEN
RAISE NoGood;
END IF;
IF INSTR(evalstr, str2in, 1, 1) > 0 THEN
RAISE NoGood;
END IF;
RETURN 1;
EXCEPTION
WHEN NoGood THEN
RETURN 0;
END AndNotStr;
/ |
| Queries To Test String Handling Function |
SELECT AndNotStr('Daniel Morgan',
'an', 'or') FROM dual;
SELECT AndNotStr('Daniel Morgan', 'an', 'bb') FROM dual; |
| Function For Number Handling |
CREATE OR REPLACE FUNCTION
AndNotNum (
evalnum NUMBER,
num1in NUMBER,
num2in NUMBER)
RETURN NUMBER IS
evalstr VARCHAR2(38);
num1str VARCHAR2(38);
num2str VARCHAR2(38);
NoGood EXCEPTION;
BEGIN
evalstr := TO_CHAR(evalnum);
num1str := TO_CHAR(num1in);
num2str := TO_CHAR(num2in);
IF INSTR(evalstr, num1str, 1, 1) = 0 THEN
RAISE NoGood;
END IF;
IF INSTR(evalstr, num2str, 1, 1) > 0 THEN
RAISE NoGood;
END IF;
RETURN 1;
EXCEPTION
WHEN NoGood THEN
RETURN 0;
END AndNotNum;
/ |
| Queries To Test Number Handling Function |
SELECT AndNotNum(1003402, 34,10)
FROM dual;
SELECT AndNotNum(1003402, 34,11) FROM dual; |
| AndNot Operator |
CREATE OR REPLACE OPERATOR AndNot
BINDING (VARCHAR2, VARCHAR2, VARCHAR2)
RETURN NUMBER USING AndNotStr,
(NUMBER, NUMBER, NUMBER)
RETURN NUMBER USING AndNotNum; |
| Test Operator |
SELECT * FROM ant WHERE andnot(memo_fld, 'dog', 'cat') = 1;
SELECT * FROM ant WHERE andnot(memo_fld, 'are', 'dog') = 1;
SELECT * FROM ant WHERE andnot(memo_fld, '10', '11') = 1;
SELECT * FROM ant WHERE andnot(memo_fld, '0', '11') = 1; |
