Oracle Operator操作

用户定义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;

我的示例是通过C自动产生的, 所以看起来有点奇怪, 为了保证通用性的需要!