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 | Rule name. |
| rule_owner_name | The name of the database. |
| pattern | The template for the statement that matches the rule. |
| replacement | Indicates how to rewrite and pattern The template of the matched statement. |
| enabled | Whether the rule takes effect. |
Usage instructions
The rewriting of binding rules is subject to the following limitations:
- Supported SQL statement types:
SELECT,INSERT,REPLACE,UPDATE,DELETE,MERGE, andSET. - In the defined rule template, you cannot mix question marks (?) with "{name}" expressions.
- When using ":{name}" as a parameter marker in the
patterndefinition, thenamemust not appear repeatedly. - The
patternandreplacementparameters cannot be defined as empty strings. - The
patternandreplacementparameters do not support multi queries. - The
rule_namemust be unique at the tenant level. - The number of parameter markers in the
patternandreplacementfields of a rule template must be the same. - In a rule template, the
patternandreplacementparameters must be specified in the same format (either both use "?", or both use ":{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