Oracle兼容-语法-TABLE FUNCTION

1. 语法

  1. SET sql_mode = ORACLE;
  3. TABLE(expr_list)
  5. expr_list:
  6.   expr, type_table_name(type_name(expr, expr..), type_name(expr, expr..) ..)

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

2. 定义和用法

GreatSQL支持用TABLE函数获取自定义表类型数据的用法。

3. 示例

    1. 示例1：SELECT FROM TABLE()
  1. -- 先切换到ORACLE模式
  2. greatsql> SET sql_mode = ORACLE;
  4. greatsql> CREATE OR REPLACE TYPE ud_type1 AS OBJECT(id INT, c1 VARCHAR(10)); 
  5. greatsql> CREATE OR REPLACE TYPE ud_tbl1 AS TABLE OF greatsql.ud_type1;
  6. greatsql> CREATE OR REPLACE TYPE ud_varray1 AS VARRAY(10) OF ud_type1;
  8. greatsql> SELECT * FROM TABLE(ud_tbl1(ud_type1(1, 'c1_rowa'), ud_type1(2, 'c1_rowb')));
  9. +------+---------+
  10. | id   | c1      |
  11. +------+---------+
  12. |    1 | c1_rowa |
  13. |    2 | c1_rowb |
  14. +------+---------+
  15. 2 rows in set (0.00 sec)
  17. greatsql> SELECT * FROM TABLE(ud_tbl1(ud_type1(1, 'c1_rowa'), ud_type1(2, 'c1_rowb'))) a, 
  18.   TABLE(ud_varray1(ud_type1(1, 'c1_rowa'), ud_type1(2, 'c1_rowb'))) b;
  19. +------+---------+------+---------+
  20. | id   | c1      | id   | c1      |
  21. +------+---------+------+---------+
  22. |    1 | c1_rowa |    1 | c1_rowa |
  23. |    1 | c1_rowa |    2 | c1_rowb |
  24. |    2 | c1_rowb |    1 | c1_rowa |
  25. |    2 | c1_rowb |    2 | c1_rowb |
  26. +------+---------+------+---------+
  27. 4 rows in set (0.00 sec)
  29. greatsql> CREATE TABLE ud_tbl2(id INT, c1 ud_type1);
  30. greatsql> INSERT INTO ud_tbl2 VALUES(10, ud_type1(10, 'c1_rowa10')), (11, ud_type1(11, 'c1_rowb11'));
  32. greatsql> SELECT a.id, a.c1 FROM ud_tbl2 b,
  33.   TABLE(ud_tbl1(b.c1, ud_type1(1, 'c1_rowa'))) a;
  34. +------+-----------+
  35. | id   | c1        |
  36. +------+-----------+
  37. |   10 | c1_rowa10 |
  38. |    1 | c1_rowa   |
  39. |   11 | c1_rowb11 |
  40. |    1 | c1_rowa   |
  41. +------+-----------+
  42. 4 rows in set (0.00 sec)
    1. 示例2：CREATE TABLE AS ... SELECT FROM TABLE()
  1. greatsql> SET sql_mode = ORACLE;
  3. greatsql> CREATE OR REPLACE TYPE ud_type1 AS OBJECT(id INT, c1 VARCHAR(10)); 
  4. greatsql> CREATE OR REPLACE TYPE ud_tbl1 AS TABLE OF greatsql.ud_type1;
  6. greatsql> CREATE TABLE ud_tbl2 AS SELECT * FROM TABLE(ud_tbl1(ud_type1(1, 'c1_rowa'), ud_type1(2, 'c1_rowb')));
  7. Query OK, 2 rows affected (0.00 sec)
  8. Records: 2  Duplicates: 0  Warnings: 0
  10. greatsql> SELECT * FROM ud_tbl2;
  11. +------+---------+
  12. | id   | c1      |
  13. +------+---------+
  14. |    1 | c1_rowa |
  15. |    2 | c1_rowb |
  16. +------+---------+
  17. 2 rows in set (0.00 sec)
    1. 示例3：INSERT INTO ... SELECT FROM TABLE()
  1. greatsql> SET sql_mode = ORACLE;
  3. greatsql> CREATE OR REPLACE TYPE ud_type1 AS OBJECT(id INT, c1 VARCHAR(10)); 
  4. greatsql> CREATE OR REPLACE TYPE ud_tbl1 AS TABLE OF greatsql.ud_type1;
  5. greatsql> CREATE TABLE ud_tbl2 AS SELECT * FROM TABLE(ud_tbl1(ud_type1(1, 'c1_rowa'), ud_type1(2, 'c1_rowb')));
  6. Query OK, 2 rows affected (0.02 sec)
  7. Records: 2  Duplicates: 0  Warnings: 0
  9. greatsql> INSERT INTO ud_tbl2 SELECT * FROM TABLE(ud_tbl1(ud_type1(3, 'c1_rowc'), ud_type1(4, 'c1_rowd')));
  10. Query OK, 2 rows affected (0.00 sec)
  11. Records: 2  Duplicates: 0  Warnings: 0
  13. greatsql> SELECT * FROM ud_tbl2;
  14. +------+---------+
  15. | id   | c1      |
  16. +------+---------+
  17. |    1 | c1_rowa |
  18. |    2 | c1_rowb |
  19. |    3 | c1_rowc |
  20. |    4 | c1_rowd |
  21. +------+---------+
  22. 4 rows in set (0.00 sec)
    1. 示例4：SELECT FROM TABLE() WHERE
  1. greatsql> SET sql_mode = ORACLE;
  3. greatsql> CREATE OR REPLACE TYPE ud_type1 AS OBJECT(id INT, c1 VARCHAR(10)); 
  4. greatsql> CREATE OR REPLACE TYPE ud_tbl1 AS TABLE OF greatsql.ud_type1;
  6. greatsql> SELECT * FROM TABLE(ud_tbl1(ud_type1(1, 'c1_rowa'), ud_type1(2, 'c1_rowb'))) WHERE id>1;
  7. +------+---------+
  8. | id   | c1      |
  9. +------+---------+
  10. |    2 | c1_rowb |
  11. +------+---------+
  12. 1 row in set (0.00 sec)
  14. greatsql> SELECT * FROM TABLE(ud_tbl1(ud_type1(1, 'c1_rowa'), ud_type1(2, 'c1_rowb')));
  15. +------+---------+
  16. | id   | c1      |
  17. +------+---------+
  18. |    1 | c1_rowa |
  19. |    2 | c1_rowb |
  20. +------+---------+
  21. 2 rows in set (0.00 sec)
  23. greatsql> CREATE OR REPLACE TYPE my_int IS VARRAY(100) OF INTEGER;
  24. Query OK, 0 rows affected (0.00 sec)
  26. greatsql> SELECT * FROM TABLE(my_int('1', 0, NULL));
  27. +--------------+
  28. | column_value |
  29. +--------------+
  30. |            1 |
  31. |            0 |
  32. |         NULL |
  33. +--------------+
  34. 3 rows in set (0.00 sec)
    1. 示例5：EXPLAIN SELECT FROM TABLE()
  1. greatsql> SET sql_mode = ORACLE;
  3. greatsql> CREATE OR REPLACE TYPE ud_type1 AS OBJECT(id INT, c1 VARCHAR(10)); 
  4. greatsql> CREATE OR REPLACE TYPE ud_varray1 AS VARRAY(10) OF ud_type1;
  6. greatsql> EXPLAIN SELECT * FROM TABLE(ud_varray1(ud_type1(1, 'c1_rowa'), ud_type1(2, 'c1_rowb')))\G
  7. *************************** 1. row ***************************
  8.            id: 1
  9.   select_type: SIMPLE
  10.         table: udt_table_-5015528632416731088
  11.    partitions: NULL
  12.          type: ALL
  13. possible_keys: NULL
  14.           key: NULL
  15.       key_len: NULL
  16.           ref: NULL
  17.          rows: 2
  18.      filtered: 100.00
  19.         Extra: Table function: udt_table; Using temporary
  21. greatsql> SHOW WARNINGS\G
  22. *************************** 1. row ***************************
  23.   Level: Note
  24.    Code: 1003
  25. Message: /* select#1 */ select "greatsql"."udt_table_-5015528632416731088"."id" AS "id","greatsql"."udt_table_-5015528632416731088"."c1" AS "c1" from table(ud_varray1(ud_type1(1,'c1_rowa'),ud_type1(2,'c1_rowb'))) "udt_table_-5015528632416731088"

4. 导出备份

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

示例：

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

问题反馈

联系我们

扫码关注微信公众号

greatsql-wx