CREATE_RULE

The CREATE_RULE procedure is used to create a custom rewrite rule.

Syntax

PROCEDURE CREATE_RULE (
    rule_name          VARCHAR(256),
    rule_owner_name    VARCHAR(128),
    pattern            LONGTEXT,
    replacement        LONGTEXT,
    enabled            VARCHAR(64) DEFAULT 'YES'
  );

Parameters

Parameter	Description
rule_name	The name of the rule.
rule_owner_name	The name of the database.
pattern	The template of the statement that matches the rule.
replacement	The template that specifies how to rewrite the statement that matches pattern.
enabled	Indicates whether the rule is enabled.

Usage notes

The rewrite binding rules have the following limitations:

• Supported SQL statement types: SELECT, INSERT, REPLACE, UPDATE, DELETE, MERGE, and SET.
• In the rule templates, "?" and ":name" cannot be mixed.
• In the pattern definition, when using ":name" as a parameter marker, the name must be unique.
• Both pattern and replacement cannot be empty.
• pattern and replacement do not support Multi Query.
• rule_name must be unique in seekdb.
• The number of parameter markers in pattern and replacement must be the same.
• The parameter markers in pattern and replacement must be represented in the same way (either both using "?" or both using ":name").

Examples

CALL DBMS_UDR.CREATE_RULE ('rule1','test','select ? from dual','select ? + 1 from dual');
Query OK, 0 rows affected 

SELECT 1 FROM DUAL;
+-------+
| ? + 1 |
+-------+
|     2 |
+-------+
1 row in set 

SELECT DB_NAME, RULE_NAME, PATTERN, REPLACEMENT, NORMALIZED_PATTERN, STATUS, PATTERN_DIGEST FROM oceanbase.DBA_OB_USER_DEFINED_RULES;
+---------+-----------+--------------------+------------------------+--------------------+--------+---------------------+
| DB_NAME | RULE_NAME | PATTERN            | REPLACEMENT            | NORMALIZED_PATTERN | STATUS | PATTERN_DIGEST      |
+---------+-----------+--------------------+------------------------+--------------------+--------+---------------------+
| test    | rule1     | select ? from dual | select ? + 1 from dual | select ? from dual | ENABLE | 2647993221679348756 |
+---------+-----------+--------------------+------------------------+--------------------+--------+---------------------+
1 row in set