Question

oracle中condition和operator的区别

Answer 1

用户定义operator的特性:
Identified by names, which are in the same namespace as tables, views, types, and standalone functions
Bound to functions, which define operator behavior in specified contexts
Controlled by privileges, which indicate the circumstances in which each operator can be used
Often associated with indextypes, which can be used to define indexes that are not built into the database

Operator可以绑定的函数有:
Standalone functions
Package functions
User-defined type member methods

与Operator相关的数据字典:
USER_OPERATOR_COMMENTS
ALL_OPERATOR_COMMENTS
DBA_OPERATOR_COMMENTS

Operator可以使用的地方(同函数很相似的):
the select list of a SELECT command
the condition of a WHERE clause
the ORDER BY and GROUP BY clauses

-- (具体语法请参考Oracle Database SQL Reference)
-- 创建Operator
CREATE OPERATOR Ordsys.Contains
BINDING(VARCHAR2, VARCHAR2) RETURN NUMBER
USING text.contains,
(Spatial.Geo, Spatial.Geo) RETURN NUMBER
USING Spatial.contains;

-- 删除Operator
DROP OPERATOR Contains;
DROP OPERATOR Contains FORCE;

-- Alter Operator
ALTER OPERATOR Ordsys.Contains
ADD BINDING (music.artist, music.artist) RETURN NUMBER
USING music.contains;

-- 给Operator加注释
COMMENT ON OPERATOR
Ordsys.Contains IS 'a number indicating whether the text contains the key';

Examples
-- 创建Operator
CREATE OPERATOR Ordsys.Contains
BINDING(VARCHAR2, VARCHAR2) RETURN NUMBER
USING text.contains,
(spatial.geo, spatial.geo) RETURN NUMBER
USING spatial.contains;

-- Operator的使用
SELECT * FROM Employee
WHERE Contains(resume, 'Oracle')=1 AND Contains(location, :bay_area)=1;

/*
Contains(resume, 'Oracle') 实质是使用 text.contains(resume, 'Oracle')
Contains(location, :bay_area) 实质是使用 spatial.contains(location, :bay_area)
有点象C++中的函数重载噢.
*/

我的示例:
-- 创建函数
CREATE OR REPLACE FUNCTION OSM_DML_3SP.OSM_FUNC_OP_1(a NUMBER)
RETURN INTEGER AS
BEGIN
IF a > 292 THEN
RETURN (a-95);
ELSE
RETURN (a+63);
END IF;
END;
/
CREATE OR REPLACE FUNCTION OSM_DML_3SP.OSM_FUNC_OP_2(s VARCHAR2)
RETURN INTEGER AS
BEGIN
IF LENGTH(s) > 37 THEN
RETURN (-50);
ELSE
RETURN (91);
END IF;
END;
/
CREATE OR REPLACE OPERATOR OSM_DML_3SP.OSM_DML_OPER
BINDING (NUMBER) RETURN INTEGER
USING OSM_DML_3SP.OSM_FUNC_OP_1,
(VARCHAR2) RETURN INTEGER USING OSM_DML_3SP.OSM_FUNC_OP_2;
/
BEGIN
INSERT INTO OSM_DML_3SP.OSM_TAB_OPERATOR SELECT * FROM OSM_DML_3SP.OSM_TAB_OPERATOR
WHERE OSM_DML_3SP.OSM_DML_OPER(COL_0) > 40;
INSERT INTO OSM_DML_3SP.OSM_TAB_OPERATOR SELECT * FROM OSM_DML_3SP.OSM_TAB_OPERATOR
WHERE OSM_DML_3SP.OSM_DML_OPER(COL_3) > 0;
COMMIT;
END;