Oracle兼容-语法-CREATE TYPE

1. 语法

1. SET sql_mode = ORACLE;

3. CREATE [OR REPLACE] [DEFINER = user] TYPE type_name [AS|IS] OBJECT(column_list) [sp_c_chistics]

5. CREATE [OR REPLACE] [DEFINER = user] TYPE type_name [AS|IS] TABLE OF [udt_name|udt_type]

7. CREATE [OR REPLACE] [DEFINER = user] TYPE type_name [AS|IS] VARRAY(n) OF [udt_name|udt_type]

需要先切换到 ORACLE 模式下才能支持本语法。

2. 定义和用法

GreatSQL支持用户通过 CREATE TYPE 创建自定义数据类型，有几点注意事项：

  1. 支持常用的 INT/VARCHAR 等类型，暂不支持 TINYBLOB/BLOB/MEDIUMBLOB/LONGBLOB 等几个类型。
  2. 在 VARRAY(n) 中，n的取值范围：[1024, 18446744073709551615]，默认值为1048576，由全局选项 tf_udt_table_max_rows 控制。如果是在MGR集群中使用，则必须确保所有节点的 tf_udt_table_max_rows 选项值设置一致，否则可能造成MGR运行报错。
  3. 支持跨库访问用户自定义类型。

3. Oracle兼容说明

GreatSQL和Oracle自定义类型的使用主要有以下几点不同：

  1. 可以定义具体列类型，暂不支持多层类型嵌套定义。
  2. 不支持设置或赋默认值。
  3. 执行 SELECT type_name 结果的显示，会由于GreatSQL跟Oracle终端网络传输协议不一致而不同。可用设置选项 udt_format_result = 'DBA' 控制输出格式为字符串格式如 col1:value | col2:value2，详见下面示例。
  4. 在Oracle中，TYPE, FUNCTION, PACKAGE 这些数据对象名都不能重名；在GreatSQL中，TYPE, FUNCTION, PACKAGE 这些数据对象名也不能重名，例外的是，FUNCTION 和 PACKAGE可以重名。
  5. 执行 SELECT type_table 返回值为输入的字符串，如果想变为表格的形式输出就使用 TABLE 函数。
  6. 执行 SELECT type_table() 的参数必须为定义时候用的type类型对象，否则会报错。
  7. 执行 CREATE TYPE AS TABLE/VARRAY(n) OF type_name 语句中，type_name 只支持 UDT 类型，不支持 UDT TABLE 类型，即不支持多层嵌套自定义表的类型。
  8. 目前不支持 SELECT * FROM udt_table 用法，可以改用 SELECT * FROM TABLE(udt_table) 来获取udt_table的值。
  9. 不支持 CREATE TABLE table_name AS SELECT udt_table() FROM DUAL 这种用法。

4. 示例

    1. 示例1：CREATE TYPE, SHOW CREATE TYPE, SHOW TYPE STATUS
1. -- 先切换到ORACLE模式
2. greatsql> SET sql_mode = Oracle;

4. -- 1. 创建UDT，不支持 IF NOT EXISTS
5. greatsql> CREATE OR REPLACE TYPE udt1 AS OBJECT(id INT ,c1 VARCHAR(20));

7. greatsql> SELECT udt1(1, 'c1_row1') FROM DUAL;
8. +--------------------+
9. | udt1(1, 'c1_row1') |
10. +--------------------+
11. | id:1 | c1:c1_row1  |
12. +--------------------+
13. 1 row in set (0.01 sec)

15. -- 2. 查看UDT DDL
16. greatsql> SHOW CREATE TYPE udt1\G
17. *************************** 1. row ***************************
18. Type: udt1
19. sql_mode: PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ONLY_FULL_GROUP_BY,ORACLE,STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
20. Create Type: CREATE DEFINER="root"@"localhost" TYPE "udt1" AS OBJECT(id INT ,c1 VARCHAR(20))
21. character_set_client: utf8mb4
22. collation_connection: utf8mb4_0900_ai_ci
23. Database Collation: utf8mb4_0900_ai_ci
24. 1 row in set (0.00 sec)

26. -- 3. 查看UDT状态
27. greatsql> SHOW TYPE STATUS LIKE 'udt1'\G
28. *************************** 1. row ***************************
29. Db: greatsql
30. Name: udt1
31. Type: TYPE
32. Definer: root@localhost
33. Modified: 2023-11-22 09:46:23
34. Created: 2023-11-22 09:46:23
35. Security_type: DEFINER
36. Comment:
37. character_set_client: utf8mb4
38. collation_connection: utf8mb4_0900_ai_ci
39. Database Collation: utf8mb4_0900_ai_ci
40. 1 row in set (0.00 sec)

42. -- 4. 删除UDT，支持 IF EXISTS
43. greatsql> DROP TYPE IF EXISTS udt1;
44. Query OK, 0 rows affected (0.00 sec)

use db1; set sql_mode=”Oracle”; CREATE OR REPLACE TYPE udt1 AS OBJECT(id INT ,c1 VARCHAR(20));

3. - 2. 示例2：支持将TYPE授权给其他用户

5. ```sql
6. greatsql> SET sql_mode = Oracle;

8. greatsql> SELECT CURRENT_USER();
9. +----------------+
10. | CURRENT_USER() |
11. +----------------+
12. | root@localhost |
13. +----------------+

15. greatsql> CREATE OR REPLACE DEFINER = 'u2@localhost' TYPE udt1 AS OBJECT(id INT, c1 CHAR(10)) SQL SECURITY INVOKER;

17. greatsql> CREATE USER u1@localhost IDENTIFIED BY '';

19. greatsql> GRANT EXECUTE ON TYPE greatsql.udt1 TO u1@localhost;

用 u1 账户新建连接，使用UDT：

1. $ mysql -S/data/GreatSQL/mysql.sock -uu1 greatsql
2. ...
3. greatsql> SHOW GRANTS;
4. +-------------------------------------------------------------+
5. | Grants for u1@localhost                                     |
6. +-------------------------------------------------------------+
7. | GRANT USAGE ON *.* TO "u1"@"localhost"                      |
8. | GRANT EXECUTE ON TYPE `greatsql`.`udt1` TO "u1"@"localhost" |
9. +-------------------------------------------------------------+
10. 2 rows in set (0.00 sec)

12. -- 直接执行会报错
13. greatsql> SELECT udt1(1, 'c1_row1') FROM DUAL;
14. ERROR 1370 (42000): execute command denied to user 'u1'@'localhost' for routine 'greatsql.udt1'

16. -- 要先切换到ORACLE模式
17. greatsql> SET sql_mode = ORACLE;

19. greatsql> SELECT udt1(1, 'c1_row1') FROM DUAL;
20. +-----------------------------------------------+
21. | udt1(1, 'c1_row1')                            |
22. +-----------------------------------------------+
23. |    c1_row1                                   |
24. +-----------------------------------------------+
25. 1 row in set (0.00 sec)
    1. 示例3：CREATE TYPE AS TABLE/VARRAY OF udt_name
1. greatsql> SET sql_mode = Oracle;

3. greatsql> CREATE OR REPLACE TYPE udt1 AS OBJECT(id INT, c1 CHAR(10));
4. greatsql> CREATE OR REPLACE TYPE udt2 AS OBJECT(id INT, c1 VARCHAR(10));

6. greatsql> SELECT @@tf_udt_table_max_rows;
7. +-----------------------+---------+
8. | Variable_name         | Value   |
9. +-----------------------+---------+
10. | tf_udt_table_max_rows | 1048576 |
11. +-----------------------+---------+

13. greatsql> CREATE OR REPLACE TYPE udt1_varray AS VARRAY(1) OF udt1;

15. greatsql> CREATE OR REPLACE TYPE udt1_varray AS VARRAY(1048577) OF udt1;
16. ERROR 7556 (HY000): The number of records generated by UDT_TABLE() cannot exceed 1048576 (1048577 requested). Try increasing @@tf_udt_table_max_rows to a larger value.

18. greatsql> CREATE OR REPLACE TYPE udt1_table AS TABLE OF udt1;

20. greatsql> SELECT udt1_table(udt1(1, 'c1_row1'), udt1(2, 'c1_row2'));
21. +----------------------------------------------------+
22. | udt1_table(udt1(1, 'c1_row1'), udt1(2, 'c1_row2')) |
23. +----------------------------------------------------+
24. | udt1_table(udt1(1,c1_row1),udt1(2,c1_row2))        |
25. +----------------------------------------------------+

27. greatsql> SELECT udt1_table(udt1(1,'c1_row1'), udt2(2,'c1_row2'));
28. ERROR 7550 (HY000): inconsistent datatypes: expected 'greatsql'.'udt1' got 'greatsql'.'udt2'

30. greatsql> SELECT udt1_varray(udt1(1, 'c1_row1'), udt1(2,'c1_row2'));
31. ERROR 3950 (HY000): Out of memory

33. greatsql> SELECT udt1_varray(udt1(1, 'c1_row1'));
34. +---------------------------------+
35. | udt1_varray(udt1(1, 'c1_row1')) |
36. +---------------------------------+
37. | udt1_varray(udt1(1,c1_row1))    |
38. +---------------------------------+
    1. 示例4：CREATE TYPE AS TABLE/VARRAY OF TYPE
1. greatsql> CREATE OR REPLACE TYPE my_int IS VARRAY(100) OF INT;

3. greatsql> SELECT my_int('1', 0, 1);
4. +-------------------+
5. | my_int('1', 0, 1) |
6. +-------------------+
7. | my_int(1,0,1)     |
8. +-------------------+

10. greatsql> CREATE OR REPLACE TYPE my_vchar IS VARRAY(100) OF VARCHAR2(8000);

12. greatsql> SELECT my_vchar('1', 'GreatSQL', 'GreatSQL is a branch of MySQL');
13. +------------------------------------------------------------+
14. | my_vchar('1', 'GreatSQL', 'GreatSQL is a branch of MySQL') |
15. +------------------------------------------------------------+
16. | my_vchar(1,GreatSQL,GreatSQL is a branch of MySQL)         |
17. +------------------------------------------------------------+

5. TABLE UDT数据字典

1. -- 1. 查询 information_schema.ROUTINES 查看所有 UDT
2. greatsql> SELECT ROUTINE_SCHEMA, ROUTINE_NAME, ROUTINE_TYPE FROM information_schema.ROUTINES WHERE ROUTINE_NAME LIKE 'UDT%';
3. +----------------+--------------+--------------+
4. | ROUTINE_SCHEMA | ROUTINE_NAME | ROUTINE_TYPE |
5. +----------------+--------------+--------------+
6. | greatsql       | udt1         | TYPE         |
7. | greatsql       | udt2         | TYPE         |
8. | greatsql       | udt1_varray  | TYPE         |
9. | greatsql       | udt1_table   | TYPE         |
10. +----------------+--------------+--------------+

12. -- 2. 查询 information_schema.PARAMETERS 查看UDT定义
13. greatsql> SELECT SPECIFIC_SCHEMA, SPECIFIC_NAME, ORDINAL_POSITION, PARAMETER_NAME, DATA_TYPE, CHARACTER_OCTET_LENGTH
14. FROM information_schema.PARAMETERS WHERE SPECIFIC_NAME LIKE 'UDT%';
15. +-----------------+---------------+------------------+----------------+-----------+------------------------+
16. | SPECIFIC_SCHEMA | SPECIFIC_NAME | ORDINAL_POSITION | PARAMETER_NAME | DATA_TYPE | CHARACTER_OCTET_LENGTH |
17. +-----------------+---------------+------------------+----------------+-----------+------------------------+
18. | greatsql        | udt1          |                1 | id             | int       |                   NULL |
19. | greatsql        | udt1          |                2 | c1             | char      |                     40 |
20. | greatsql        | udt2          |                1 | id             | int       |                   NULL |
21. | greatsql        | udt2          |                2 | c1             | varchar   |                     40 |
22. +-----------------+---------------+------------------+----------------+-----------+------------------------+

6. 导出备份

在使用 mysqldump 导出数据时，可通过指定 --routines 选项（默认为关闭），导出UDT对象。

示例：

1. mysqldump -S/data/GreatSQL/mysql.sock -uroot -pxxx -B greatsql -d --routines > /data/backup/GreatSQL/greatsql-ddl.sql

问题反馈

联系我们

扫码关注微信公众号

greatsql-wx