创建具有左侧/右侧的用户定义的运算符 [英] Create user defined operator with left/right sides

问题描述

我开始使用pl/sql开发Oracle运算符.指南中有一些示例，但现在我坚持一些.

I'm starting to develop an Oracle operator with pl/sql. There are some examples in the guide but now I stick on something.

在我所看到的所有示例中，总是这样:

In all examples that I have seen, it's always like this :

CREATE OPERATOR Contains 
   BINDING (VARCHAR2, VARCHAR2) 
    RETURN NUMBER 
     USING text.contains;

，我们在WHERE子句中这样使用它:

and we use it like this in WHERE clause :

SELECT * 
  FROM MyEmployees 
 WHERE Contains(resume, 'Oracle') = 1;

是否可以创建一个运算符，该运算符的使用方式类似于=, +, ...，左侧和右侧?

Is it possible to create an operator, which is used like =, +, ... with a left and a right side ?

在这种情况下，例如:

SELECT * 
  FROM MyEmployees 
 WHERE resume Contains 'Oracle'

感谢您的帮助.

我知道PostgreSQL有可能，但没有找到Oracle
http://www.postgresql.org/docs/current/static/sql -createoperator.html

EDIT : I know it's possible with PostgreSQL, but didn't find for Oracle
http://www.postgresql.org/docs/current/static/sql-createoperator.html

推荐答案

摘录自您对@ ajmalmhd04 answer

Excerpt from your comment to @ajmalmhd04 answer

我真的很想像内置(=，+ ...)一样使用我的运算符

i really want to use my operator like built-in (=, + ...)

是的，文档有点令人困惑定义用户定义的运算符的方式，尤其是这一部分:

Yes, documentation is a bit confusing in the way it defines user-defined operators, especially this part:

用户定义的运算符的行为类似于<，>和=等内置运算符；

user-defined operators act like the built-in operators such as <, >, and =;

不幸的是，这意味着您可以使用用户定义的运算符，(大多数情况下)您可以使用内置运算符，但不能以与使用内置运算符(例如<)相同的方式(例如，operand1 OPERATOR operand2)使用或=.用户定义的运算符(如果不是非常仔细地看它们)是一种不同的调用函数的方式，除了它们(运算符)只能在DML语句(select，insert等)中使用.您将无法仅通过DML直接在PL/SQL中使用它们. 因此，在PL/SQL中类似这样的结果将导致PLS-00548: invalid use of operator.

Unfortunately, it means that you can use user-defined operators where(mostly) you can use built-in operators but not in the same way(operand1 OPERATOR operand2 for instance) you use built-in operators, like < or =. User-defined operators, if not look at them very very closely, are different way of calling functions with the exception that they(operators) can only be used in DML statements (select, insert and so forth). You won't be able to use them in PL/SQL directly, only through DML. So, something like this, in PL/SQL will result in PLS-00548: invalid use of operator.

if operator(<<arguments>>) = 1 then
  -- something
end if;

如果不参与开发基于应用程序的基于服务器的特定扩展(数据盒式磁带)(例如，索引类型，对我个人而言不可见)，则创建和使用运算符的好处.无法想到可以使用运算符而不能使用函数的情况.相反，这是不正确的.您可能会将运算符具有多个绑定的能力视为将其用作重载函数的一种方式.但是，另一方面，您可以使用软件包轻松实现相同的目的.

The benefits of creating and using operators, if you are not involved in developing application specific server-based extensions (data cartridges), indextypes for instance, not that visible to me personally. Cannot think of a situation where you can use operators and cannot use functions. Opposite is not true, however. You may look at operator's ability to have multiple bindings as a way of using it as an overloaded function. But, on the other hand, you can easily achieve the same with packages.

这篇关于创建具有左侧/右侧的用户定义的运算符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！