The CREATE_RULE procedure creates a user-defined rewrite rule.
Applicability
This topic applies only to OceanBase Database Enterprise Edition. OceanBase Database Community Edition provides only the MySQL mode.
Syntax
PROCEDURE CREATE_RULE (
rule_name IN VARCHAR,
pattern IN CLOB,
replacement IN CLOB,
enabled IN VARCHAR := '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
The limitations on rewrite rules are as follows:
- Supported SQL statement types are
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.- In a rule template,
patternandreplacementmust use the same parameter marker, which can be “?” or “:{name}”.
Examples
obclient> CALL DBMS_UDR.CREATE_RULE('rule1', 'select ? from dual','select ? + 1 from dual');
Query OK, 0 rows affected
obclient> SELECT 1 FROM DUAL;
+-----+
| ?+1 |
+-----+
| 2 |
+-----+
1 row in set