The CREATE_RULE procedure is used to create custom rewrite rules.
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. This parameter is required only in MySQL mode. |
| pattern | The template of the statement to be matched by the rule. |
| replacement | The template that indicates how to rewrite the statement matched by pattern. |
| enabled | Indicates whether the rule is enabled. |
Considerations
The rewrite rules are subject to the following constraints:
- Supported SQL statements:
SELECT,INSERT,REPLACE,UPDATE,DELETE,MERGE, andSET. - The question mark (?) and the colon and name ({name}) cannot be mixed in the rule templates.
- When the colon and name ({name}) are used as parameter markers in the
patterndefinition, thenamecannot be repeated. - The
patternandreplacementcannot be defined as empty. - The
patternandreplacementdo not support Multi Query. - The
rule_namemust be unique at the tenant level. - The number of parameter markers in the
patternandreplacementmust be the same. - The parameter markers in the
patternandreplacementmust be represented in the same way (either both using ? or both using :{name}).
Examples
obclient> CALL DBMS_UDR.CREATE_RULE ('rule1','test','select ? from dual','select ? + 1 from dual');
Query OK, 0 rows affected
obclient> SELECT 1 FROM DUAL;
+-------+
| ? + 1 |
+-------+
| 2 |
+-------+
1 row in set
obclient> 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