Oracle触发器与存储过程高级编程.docVIP

User-Defined Operators—用户自定义运算符 Like built-in operators, user-defined operators take a set of operands as input and return a result. However, you create them with the CREATE OPERATOR statement, and they are identified by names. They reside in the same namespace as tables, views, types, and standalone functions. Once you have defined a new operator, you can use it in SQL statements like any other built-in operator. For example, you can use user-defined operators in the select list of a SELECT statement, the condition of a WHERE clause, or in ORDER BY clauses and GROUP BY clauses. However, you must have EXECUTE privilege on the operator to do so, because it is a user-defined object. For example, if you define an operator includes, which takes as input a text column and a keyword and returns 1 if the row contains the specified keyword, you can then write the following SQL query: SELECT * FROM product_descriptions WHERE includes (translated_description, Oracle and UNIX) = 1; Concatenation Operator—字符连接运算符 The concatenation operator manipulates character strings and CLOB data. Table?3-3 describes the concatenation operator. Table 3-3 Concatenation Operator Operator Purpose Example || Concatenates character strings and CLOB data. SELECT Name is || last_name FROM employees; The result of concatenating two character strings is another character string. If both character strings are of datatype CHAR, the result has datatype CHAR and is limited to 2000 characters. If either string is of datatype VARCHAR2, the result has datatype VARCHAR2 and is limited to 4000 characters. If either argument is a CLOB, the result is a temporary CLOB. Trailing blanks in character strings are preserved by concatenation, regardless of the datatypes of the string or CLOB. On most platforms, the concatenation operator is two solid vertical bars, as shown in Table?3-3. However, some IBM platforms use broken vertical bars for this operator. When moving SQL script files between systems havin