The CREATE_RULE procedure creates a user-defined rewrite binding 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. You must specify this parameter only in MySQL mode. |
| pattern | The pattern that determines whether a statement matches the rule. |
| replacement | Indicates how to rewrite statements that match pattern. |
| enabled | Indicates whether the rule takes effect. |
Considerations
Limitations for rewrite binding rules:
- Supported types of SQL statement:
SELECT,INSERT,REPLACE,UPDATE,DELETE,MERGE, andSET. - You cannot use both question marks (?) and
:{name}in the same rule pattern. - If you use
:{name}as a placeholder inpattern, the value ofnamecannot be duplicated. patternandreplacementcannot be empty.patternandreplacementdo not support multiple queries.rule_namemust be unique in the tenant.patternandreplacementmust have the same number of placeholders.patternandreplacementmust use the same placeholder type.
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