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