Oracle兼容-存储过程-TYPE IS TABLE

1. 语法

1. 1. TYPE type IS
2. { assoc_array_type_def
3. | nested_table_type_def
4. } ;

6. assoc_array_type_def：
7. TABLE OF datatype
8. INDEX BY { PLS_INTEGER | BINARY_INTEGER | VARCHAR ( v_size )}

10. nested_table_type_def：
11. TABLE OF datatype

13. datatype:
14. { record_type
15. | rowtype_attribute
16. | type_attribute
17. }
18. rowtype_attribute: db_table_or_view %ROWTYPE

20. 2. var var_def

22. 3. collection.{ COUNT
23. | FIRST
24. | LAST
25. }
26. 4. var(row) := record_value

28. 5. record_value := var(row)

30. 6. var(row) := var1(row)

32. 7. SELECT var(row)

34. 8. SELECT var(row) INTO var

36. 9. TYPE(row => RECORD(value_list) , row => ..)

2. 定义和用法

在GreatSQL中支持用 TYPE IS TABLE 方式来自定义数据类型，支持以下几种用法：

    1. 用 TYPE IS TABLE OF 自定义 TABLE 类型。
    1. 用 var var_def 进行该自定义类型参数的定义。
    1. 用 var.COUNT 获取自定义 TABLE 表的行数。
    1. 用 var(roW) := record_value 给自定义 TABLE 某一行赋值。
    1. 用 record_value := var(row) 从自定义 TABLE 某一行给 RECORD 类型参数赋值。
    1. 用 var(row) := var1(row) 从自定义 TABLE 某一行给另一个自定义 TABLE 某一行赋值。
    1. 用 SELECT var(row) 查询 TABLE 某一行。
    1. 用 SELECT var(row) INTO VAR 从自定义 TABLE 某一行给 RECORD 类型参数赋值。
    1. 用 RECORD TABLE 带 INDEX BY .. 的类型允许用 => 方式赋默认值。

3. Oracle兼容说明

在GreatSQL中支持用 TYPE IS TABLE 方式来自定义数据类型，该用法如下所述：

  1. 在 TYPE IS TABLE OF .. INDEX BY BINARY_INTEGER 定义中支持自增长形式的整数型，不支持如手动指定方式的整数型用法。
  2. 支持无限循环嵌套表，支持用 RECORD 类型数据赋默认值，也支持 RECORD 和 TABLE 两种类型混用，比如 a.b(1).c。
  3. 支持用 %ROWTYPE 方式定义 TABLE 类型，比如 TYPE udt_type IS TABLE OF udt_table%ROWTYPE。
  4. 支持无限循环嵌套表的 SELECT 或 SET 赋值。
  5. 支持用 NO_DATA_FOUND 判断无法查找到数据的 EXCEPTION 状态。详见下方示例3。
  6. 支持不同行和列的 RECORD TABLE 赋值。赋值后 TABLE 行数与源 RECORD TABLE 一致。并支持 RECORD TABLE 类型赋默认值。如果该 RECORD TABLE 是 INDEX BY 的，那么赋默认值时候必须要同时加上行数，比如 v1 record_table := record_table(1, udt_table(1 => udt_type(1, 'row1')))，这里的 1=> 表示第1行。如果该 RECORD TABLE 不是 INDEX BY的，那么赋默认值时候就不能加上行号标记。
  7. 不支持单类型的数组定义，比如 CREATE TYPE v_list_attrs AS VARRAY(10) OF VARCHAR2(80)。
  8. 不支持 TABLE 类型作为 RECORD 类型使用，不支持类似用法 table_type employees%ROWTYPE。
  9. 在 FOR i IN udt_table.FIRST .. udt_table.LAST LOOP 循环中的 i 是整数型，因此 udt_table 的行号值不能是非整数型，否则无法正常赋值。如果该 udt_table 是空的，不会报错而是直接跳出循环，Oracle则会报错。udt_table.FIRST 是 udt_table 行号的最小值，udt_table.LAST 是行号的最大值。
  10. 当指定 INDEX BY BINARY_INTEGER 属性时，则不支持非数字的字符类型行号标记用法，例如 TABLE('a')，但支持数值型的字符类型行号，比如 TABLE('1') / TABLE('-1')。指定 INDEX BY VARCHAR 属性时，允许行号参数为字符类型，例如 TABLE('a')。此外，指定 INDEX BY VARCHAR 时行号会按ASCII码排序，而 INDEX BY BINARY_INTEGER 则会按照整数型排序。
  11. 在 SELECT var(row) 中，如果 var(row) 里有自定义类型数据，将返回 NULL。详见下方示例10。
  12. 在 SELECT var.COUNT 中，如果变量名 var 与 TABLE 名重复，会优先被解析为 RECORD TABLE 名，所以 var.COUNT 会返回 TABLE 的行数。
  13. 当 TYPE IS TABLE OF 后面不带 INDEX BY 时，这个自定义数据类型必须要初始化才能使用。初始化时定义最大行数后就不能再发生变化，后面只支持 UPDATE 操作而不支持 INSERT 操作。例外情况是在 SELECT BULK COLLECT INTO var 时，可以不需要初始化就能直接使用，因为 BULK COLLECT 操作会进行初始化和插入数据操作。暂不支持Oracle中用 EXTEND() 函数来扩容 TABLE 类型。
  14. 在 TYPE IS TABLE OF INDEX BY VARCHAR(v_size) 定义中，Oracle里参数 v_size 的范围是 [1, 32676]，GreatSQL中支持的范围是 [1, 16383]，二者不同。
  15. 不支持使用保留关键字作为 TABLE 列名，比如 NAME、ANY、BULK、TABLE 等。
  16. 在存储过程中的 TYPE 作为保留关键字，不能用作变量名。

4. 示例

修改 sql_generate_invisible_primary_key 选项设定，因为下面案例中创建的表没有显式主键，关闭该选项可以避免自动创建隐式主键 my_row_id，可能会对下面的案例造成影响。

1. greatsql> SET SESSION sql_generate_invisible_primary_key = 0;
    1. 示例1：TYPE IS RECORD/TYPE IS TABLE
1. greatsql> SET sql_mode = ORACLE;
2. greatsql> SET udt_format_result = 'DBA';
3. greatsql> DELIMITER //

5. greatsql> CREATE OR REPLACE PROCEDURE table_sp1 AS
6. TYPE t1_record IS RECORD(
7. id INT := 1,
8. c1 VARCHAR(20),
9. c2 FLOAT := 33.06
10. );

12. TYPE t1_list IS TABLE OF t1_record INDEX BY BINARY_INTEGER;

14. TYPE t2_record IS RECORD(
15. v_i INT := 1,
16. t1_record_v1 t1_list
17. );
18. t1_record_val t2_record;

20. BEGIN
21. t1_record_val.t1_record_v1(1).id := 1;
22. t1_record_val.t1_record_v1(2).c1 := 'row1';
23. t1_record_val.t1_record_v1(0).id := 2;
24. t1_record_val.t1_record_v1(3).c2 := 3.306;

26. SELECT t1_record_val.t1_record_v1(1).id,
27. t1_record_val.t1_record_v1(1).c1,
28. t1_record_val.t1_record_v1(0).id,
29. t1_record_val.t1_record_v1(3).c2;

31. SELECT t1_record_val.v_i;
32. END; //

34. greatsql> CALL record_sp1() //
35. +----------------------------------+----------------------------------+----------------------------------+----------------------------------+
36. | t1_record_val.t1_record_v1(1).id | t1_record_val.t1_record_v1(1).c1 | t1_record_val.t1_record_v1(0).id | t1_record_val.t1_record_v1(3).c2 |
37. +----------------------------------+----------------------------------+----------------------------------+----------------------------------+
38. |                                1 | NULL                             |                                2 |                            3.306 |
39. +----------------------------------+----------------------------------+----------------------------------+----------------------------------+
40. 1 row in set (0.00 sec)

42. +-------------------+
43. | t1_record_val.v_i |
44. +-------------------+
45. |                 1 |
46. +-------------------+
47. 1 row in set (0.00 sec)

49. Query OK, 0 rows affected (0.00 sec)
    1. 示例2：TYPE IS TABLE OF %ROWTYPE
1. greatsql> SET sql_mode = ORACLE;

3. greatsql> CREATE TABLE t1(a INT, b CHAR(50));
4. greatsql> INSERT INTO t1 VALUES(1, 'row1'), (2, 'row2');

6. greatsql> DELIMITER //

8. greatsql> CREATE OR REPLACE PROCEDURE table_sp2() AS
9. TYPE udt_t1 IS TABLE OF t1%ROWTYPE INDEX BY BINARY_INTEGER;
10. udt_t2 udt_t1;
11. BEGIN
12. SELECT * BULK COLLECT INTO udt_t2 FROM t1;

14. FOR i IN udt_t2.FIRST .. udt_t2.LAST LOOP
15. SELECT udt_t2(i).a, udt_t2(i).b;
16. END LOOP;
17. END; //

19. greatsql> CALL table_sp2() //
20. +-------------+-------------+
21. | udt_t2(i).a | udt_t2(i).b |
22. +-------------+-------------+
23. |           1 | row1        |
24. +-------------+-------------+
25. 1 row in set (0.00 sec)

27. +-------------+-------------+
28. | udt_t2(i).a | udt_t2(i).b |
29. +-------------+-------------+
30. |           2 | row2        |
31. +-------------+-------------+
32. 1 row in set (0.00 sec)

34. Query OK, 0 rows affected (0.00 sec)
    1. 示例3：EXCEPTION WHEN NO_DATA_FOUND
1. greatsql> SET sql_mode = ORACLE;
2. greatsql> DELIMITER //

4. greatsql> CREATE OR REPLACE PROCEDURE table_sp3() AS
5. TYPE t1_record IS RECORD(
6. id INT := 1
7. );

9. TYPE t1_table IS TABLE OF t1_record INDEX BY BINARY_INTEGER;
10. v1 t1_table;
11. BEGIN
12. v1(0).id := 3306;
13. SELECT v1(0).id;
14. EXCEPTION
15. WHEN NO_DATA_FOUND THEN SELECT 'The row does not exist!' AS ret;
16. END; //

18. greatsql> CALL table_sp3() //
19. +----------+
20. | v1(0).id |
21. +----------+
22. |     3306 |
23. +----------+
24. 1 row in set (0.00 sec)

26. Query OK, 0 rows affected (0.00 sec)
    1. 示例4：对 RECORD / TABLE 类型数据赋值
1. greatsql> SET sql_mode = ORACLE;
2. greatsql> SET udt_format_result = 'DBA';
3. greatsql> DELIMITER //

5. greatsql> CREATE OR REPLACE PROCEDURE table_sp4() AS
6. TYPE t1_record IS RECORD(
7. id INT := 1,
8. c1  VARCHAR(20) := 'row1',
9. c2 FLOAT := 33.06
10. );

12. TYPE t1_list IS TABLE OF t1_record INDEX BY BINARY_INTEGER;

14. t_record_v1 t1_list;
15. t_record_v2 t1_list;

17. BEGIN
18. t_record_v1(0).id := 2;
19. t_record_v1(0).c2 := 3.306;
20. t_record_v2(10).id := 20;

22. t_record_v2 := t_record_v1;

24. SELECT t_record_v1(0).id, t_record_v1(0).c1, t_record_v1(0).c2;

26. SELECT t_record_v2(0).id, t_record_v2(0).c1, t_record_v2(0).c2;
27. END; //

29. greatsql> CALL table_sp4() //
30. +--------------------+--------------------+--------------------+
31. |  t_record_v1(0).id |  t_record_v1(0).c1 |  t_record_v1(0).c2 |
32. +--------------------+--------------------+--------------------+
33. |                  2 | row1               |              3.306 |
34. +--------------------+--------------------+--------------------+
35. 1 row in set (0.00 sec)

37. +--------------------+--------------------+--------------------+
38. |  t_record_v2(0).id |  t_record_v2(0).c1 |  t_record_v2(0).c2 |
39. +--------------------+--------------------+--------------------+
40. |                  2 | row1               |              3.306 |
41. +--------------------+--------------------+--------------------+
42. 1 row in set (0.00 sec)

44. Query OK, 0 rows affected (0.00 sec)
    1. 示例5：table.COUNT
1. greatsql> SET sql_mode = ORACLE;
2. greatsql> SET udt_format_result = 'DBA';
3. greatsql> DELIMITER //

5. greatsql> CREATE OR REPLACE PROCEDURE table_sp5() AS
6. TYPE t1_list IS TABLE OF VARCHAR(20) INDEX BY BINARY_INTEGER;
7. t_record_v1 t1_list;
8. id INT := 1;

10. BEGIN
11. t_record_v1(id) := 1;
12. t_record_v1(3) := 1;

14. SELECT t_record_v1(id), t_record_v1(3);

16. SELECT t_record_v1.COUNT;
17. END; //

19. greatsql> CALL table_sp5() //
20. +------------------+-----------------+
21. |  t_record_v1(id) |  t_record_v1(3) |
22. +------------------+-----------------+
23. | 1                | 1               |
24. +------------------+-----------------+
25. 1 row in set (0.00 sec)

27. +--------------------+
28. | t_record_v1.COUNT |
29. +--------------------+
30. |                  2 |
31. +--------------------+
32. 1 row in set (0.00 sec)

34. Query OK, 0 rows affected (0.00 sec)
    1. 示例6： TYPE IS TABLE OF TYPE INDEX BY VARCHAR
1. greatsql> SET sql_mode = ORACLE;
2. greatsql> SET udt_format_result = 'DBA';
3. greatsql> DELIMITER //

5. greatsql> CREATE OR REPLACE PROCEDURE table_sp6() AS
6. TYPE t1_list IS TABLE OF VARCHAR(20) INDEX BY VARCHAR(20);
7. t_record_v1 t1_list;
8. id VARCHAR(20) := 'row1';

10. BEGIN
11. t_record_v1(id) := 10;
12. t_record_v1(1) := 1;

14. SELECT t_record_v1(id), t_record_v1(1);
15. SELECT t_record_v1.COUNT;
16. END; //

18. greatsql> CALL table_sp6() //
19. +------------------+-----------------+
20. |  t_record_v1(id) |  t_record_v1(1) |
21. +------------------+-----------------+
22. | 10               | 1               |
23. +------------------+-----------------+
24. 1 row in set (0.00 sec)

26. +--------------------+
27. |  t_record_v1.COUNT |
28. +--------------------+
29. |                  2 |
30. +--------------------+
31. 1 row in set (0.00 sec)

33. Query OK, 0 rows affected (0.00 sec)
    1. 示例7： TYPE IS TABLE OF RECORD INDEX BY VARCHAR
1. greatsql> SET sql_mode = ORACLE;
2. greatsql> SET udt_format_result = 'DBA';
3. greatsql> DELIMITER //

5. greatsql> CREATE OR REPLACE TYPE udt1 AS OBJECT(id INT, c1 VARCHAR(20));
6. greatsql> CREATE TABLE udt_t1(id INT, c2 udt1);

8. greatsql> INSERT INTO udt_t1 VALUES(1, udt1(10, 'c2_row10'));
9. greatsql> INSERT INTO udt_t1 VALUES(2, udt1(20, 'c2_row20'));

11. greatsql> CREATE OR REPLACE PROCEDURE table_sp7() AS
12. TYPE udt_t2 IS TABLE OF udt_t1%ROWTYPE index by INT;
13. udt_t3 udt_t2;
14. BEGIN
15. SELECT * BULK COLLECT INTO udt_t3 FROM udt_t1;

17. FOR i IN 1 .. udt_t3.COUNT LOOP
18. SELECT udt_t3(i).id, udt_t3(i).c2;
19. END LOOP;
20. END; //

22. greatsql> CALL table_sp7() //
23. +--------------+---------------------+
24. | udt_t3(i).id | udt_t3(i).c2        |
25. +--------------+---------------------+
26. |            1 | id:10 | c1:c2_row10 |
27. +--------------+---------------------+
28. 1 row in set (0.01 sec)

30. +--------------+---------------------+
31. | udt_t3(i).id | udt_t3(i).c2        |
32. +--------------+---------------------+
33. |            2 | id:20 | c1:c2_row20 |
34. +--------------+---------------------+
35. 1 row in set (0.01 sec)

37. Query OK, 0 rows affected (0.01 sec)
    1. 示例8：var(row) := record_value
1. greatsql> SET sql_mode = ORACLE;
2. greatsql> SET udt_format_result = 'DBA';
3. greatsql> DELIMITER //

5. greatsql> CREATE OR REPLACE PROCEDURE table_sp8() AS
6. TYPE t1_list IS TABLE OF VARCHAR(20) INDEX BY VARCHAR2(30);
7. v_array t1_list;

9. TYPE t1_record IS RECORD(
10. c1 VARCHAR(20) := 'c1_row1',
11. c2 VARCHAR(20) := 'c2_row1'
12. );

14. TYPE t2_list IS TABLE OF t1_record INDEX BY pls_integer;
15. t3_list t2_list;

17. v_c1 VARCHAR(10) := 'b';

19. BEGIN
20. t3_list(1) := t1_record(v_c1, 'c2_row1');
21. t3_list(2) := t1_record('b', 'c2_row2');

23. FOR i IN 1 .. t3_list.COUNT LOOP
24. v_array(t3_list(i).c1) := t3_list(i).c2;

26. SELECT v_array(t3_list(i).c1);
27. END LOOP;
28. END; //

30. greatsql> CALL table_sp8() //
31. +------------------------+
32. | v_array(t3_list(i).c1) |
33. +------------------------+
34. | c2_row1                |
35. +------------------------+
36. 1 row in set (0.00 sec)

38. +------------------------+
39. | v_array(t3_list(i).c1) |
40. +------------------------+
41. | c2_row2                |
42. +------------------------+
43. 1 row in set (0.00 sec)

45. Query OK, 0 rows affected (0.00 sec)
    1. 示例9： record_value := var(row)
1. greatsql> SET sql_mode = ORACLE;
2. greatsql> SET udt_format_result = 'DBA';
3. greatsql> DELIMITER //

5. greatsql> CREATE OR REPLACE PROCEDURE table_sp9() AS
6. TYPE t1_record IS RECORD(
7. id INT := 1,
8. c1  VARCHAR(20) := 'row1',
9. c2 FLOAT := 33.06
10. );

12. TYPE t1_list IS TABLE OF t1_record INDEX BY BINARY_INTEGER;
13. t_record_v1 t1_list;
14. t_record_v2 t1_record;

16. BEGIN
17. t_record_v1(1).id := 10;
18. t_record_v1(2).id := 20;
19. t_record_v2 := t_record_v1(2);

21. SELECT t_record_v1(1), t_record_v2;

23. SELECT t_record_v1.COUNT;

25. t_record_v1(2).id := 30;
26. SELECT t_record_v1(2), t_record_v1(1);
27. END; //

29. greatsql> CALL table_sp9() //
30. +----------------------------+----------------------------+
31. |  t_record_v1(1)            |  t_record_v2               |
32. +----------------------------+----------------------------+
33. | id:10 | c1:row1 | c2:33.06 | id:20 | c1:row1 | c2:33.06 |
34. +----------------------------+----------------------------+
35. 1 row in set (0.00 sec)

37. +--------------------+
38. | t_record_v1.COUNT |
39. +--------------------+
40. |                  2 |
41. +--------------------+
42. 1 row in set (0.00 sec)

44. +----------------------------+----------------------------+
45. |  t_record_v1(2)            |  t_record_v1(1)            |
46. +----------------------------+----------------------------+
47. | id:30 | c1:row1 | c2:33.06 | id:10 | c1:row1 | c2:33.06 |
48. +----------------------------+----------------------------+
49. 1 row in set (0.00 sec)

51. Query OK, 0 rows affected (0.00 sec)
    1. 示例10： SELECT var(row)
1. greatsql> SET sql_mode = ORACLE;
2. greatsql> SET udt_format_result = 'DBA';
3. greatsql> DELIMITER //

5. greatsql> CREATE OR REPLACE PROCEDURE table_sp10() AS
6. TYPE t1_record IS RECORD (
7. a VARCHAR(30) := 'a',
8. b VARCHAR(30) := 'b',
9. c VARCHAR(30) := 'c'
10. );

12. TYPE t2_record IS RECORD (
13. v_t1 t1_record,
14. d VARCHAR(30) := 'd'
15. );

17. TYPE t3 IS TABLE OF t2_record INDEX BY VARCHAR2(30);
18. t4 t3;

20. TYPE udt_t2 IS TABLE OF t1_record INDEX BY VARCHAR2(30);
21. udt_t3 udt_t2;

23. BEGIN
24. t4(1).d := 'e';
25. SELECT t4(1), t4(1).v_t1;

27. udt_t3(1) := t4(1).v_t1;
28. SELECT udt_t3(1);
29. END; //

31. greatsql> CALL table_sp10() //
32. +-----------------+-----------------+
33. | t4(1)           | t4(1).v_t1      |
34. +-----------------+-----------------+
35. | v_t1:NULL | d:e | a:a | b:b | c:c |
36. +-----------------+-----------------+
37. 1 row in set (0.00 sec)

39. +-----------------+
40. | udt_t3(1)       |
41. +-----------------+
42. | a:a | b:b | c:c |
43. +-----------------+
44. 1 row in set (0.00 sec)

46. Query OK, 0 rows affected (0.00 sec)
    1. 示例11：var(row) := var1(row)
1. greatsql> SET sql_mode = ORACLE;
2. greatsql> SET udt_format_result = 'DBA';
3. greatsql> DELIMITER //

5. greatsql> CREATE OR REPLACE PROCEDURE table_sp11() AS
6. TYPE t1_record IS RECORD(
7. id INT := 1,
8. c1 VARCHAR(20) := 'c1',
9. c2 FLOAT := 33.06
10. );
11. TYPE t1_list IS TABLE OF t1_record INDEX BY BINARY_INTEGER;
12. t_record_v1 t1_list;
13. t_record_v2 t1_list;
14. BEGIN
15. t_record_v1(1).id := 10;
16. SELECT t_record_v1;

18. t_record_v1(2).id := 20;
19. t_record_v1(3).c1 := 'c1_30';
20. t_record_v1(4) := t_record_v1(2);

22. SELECT t_record_v1(1), t_record_v1(2), t_record_v1(3), t_record_v1(4), t_record_v1;

24. SELECT t_record_v1.COUNT;

26. t_record_v2(2) := t_record_v1(2);

28. SELECT t_record_v2(2);

30. SELECT t_record_v1(1).id, t_record_v1(2).id, t_record_v1(3).id, t_record_v1(4).id, t_record_v1;
31. END; //

33. greatsql> CALL table_sp11() //
34. +---------------------------------+
35. |  t_record_v1                    |
36. +---------------------------------+
37. | t1_list(t1_record(10,c1,33.06)) |
38. +---------------------------------+
39. 1 row in set (0.00 sec)

41. +-------------------+-------------------+-------------------+-------------------+--------------------------------------------------------------------------------------------------------+
42. | t_record_v1(1).id | t_record_v1(2).id | t_record_v1(3).id | t_record_v1(4).id | t_record_v1
43. |
44. +-------------------+-------------------+-------------------+-------------------+--------------------------------------------------------------------------------------------------------+
45. |                10 |                20 |                 1 |                20 | t1_list(t1_record(10,c1,33.06),t1_record(20,c1,33.06),t1_record(1,c1_30,33.06),t1_record(20,c1,33.06)) |
46. +-------------------+-------------------+-------------------+-------------------+--------------------------------------------------------------------------------------------------------+
47. 1 row in set (0.00 sec)

49. +--------------------+
50. |  t_record_v1.COUNT |
51. +--------------------+
52. |                  4 |
53. +--------------------+
54. 1 row in set (0.00 sec)

56. +--------------------------+
57. | t_record_v2(2)          |
58. +--------------------------+
59. | id:20 | c1:c1 | c2:33.06 |
60. +--------------------------+
61. 1 row in set (0.00 sec)

63. +-------------------+-------------------+-------------------+-------------------+--------------------------------------------------------------------------------------------------------+
64. | t_record_v1(1).id | t_record_v1(2).id | t_record_v1(3).id | t_record_v1(4).id | t_record_v1                                                                                            |
65. +-------------------+-------------------+-------------------+-------------------+--------------------------------------------------------------------------------------------------------+
66. |                10 |                20 |                 1 |                20 | t1_list(t1_record(10,c1,33.06),t1_record(20,c1,33.06),t1_record(1,c1_30,33.06),t1_record(20,c1,33.06)) |
67. +-------------------+-------------------+-------------------+-------------------+--------------------------------------------------------------------------------------------------------+
68. 1 row in set (0.00 sec)

70. Query OK, 0 rows affected (0.00 sec)
    1. 示例12： SELECT var(row) INTO var
1. greatsql> SET sql_mode = ORACLE;
2. greatsql> SET udt_format_result = 'DBA';
3. greatsql> DELIMITER //

5. greatsql> CREATE OR REPLACE PROCEDURE table_sp12() AS
6. TYPE t1_record IS RECORD(
7. id INT := 1,
8. c1 VARCHAR(20) := 'c1_1',
9. c2 FLOAT := 33.06
10. );
11. TYPE t1_list IS TABLE OF t1_record INDEX BY BINARY_INTEGER;
12. t_record_v1 t1_list;
13. t_record_v2 t1_record;
14. BEGIN
15. t_record_v1(2) := t1_record(10, 'c1_10', 43.06);

17. SELECT t_record_v1(2) INTO t_record_v2;

19. SELECT t_record_v2;
20. END; //

22. greatsql> CALL table_sp12() //
23. +-----------------------------+
24. |  t_record_v2                |
25. +-----------------------------+
26. | id:10 | c1:c1_10 | c2:43.06 |
27. +-----------------------------+
28. 1 row in set (0.00 sec)

30. Query OK, 0 rows affected (0.00 sec)
    1. 示例13：TYPE IS TABLE OF RECORD中不含 INDEX BY
1. greatsql> SET sql_mode = ORACLE;
2. greatsql> SET udt_format_result = 'DBA';
3. greatsql> DELIMITER //

5. greatsql> CREATE OR REPLACE PROCEDURE table_sp13() AS
6. TYPE t1_record IS RECORD(
7. id INT := 1,
8. c1  VARCHAR(20) := 'c1_1',
9. c2 FLOAT := 33.06
10. );
11. -- 这里可以不初始化，到后面再赋值进行初始化。
12. TYPE t1_list IS TABLE OF t1_record;

14. t_record_v1 t1_list := t1_list(t1_record(11, 'c1_11', 44.06), NULL);
15. t_record_v2 t1_record;

17. BEGIN
18. -- 如果前面没有初始化，这里还可以进行赋值，如下所示
19. -- t_record_v1 := t1_list(t1_record(11, 'c1_11', 44.06), NULL);

21. t_record_v1(1).id := 10;
22. t_record_v1(2).id := 20;
23. t_record_v2 := t_record_v1(2);

25. SELECT t_record_v1(1), t_record_v2;

27. SELECT t_record_v1.COUNT;

29. t_record_v1(2).id := 30;
30. SELECT t_record_v1(2), t_record_v1(1);
31. END; //

33. greatsql> CALL table_sp13() //
34. +-----------------------------+---------------------------+
35. |  t_record_v1(1)             |  t_record_v2              |
36. +-----------------------------+---------------------------+
37. | id:10 | c1:c1_11 | c2:44.06 | id:20 | c1:NULL | c2:NULL |
38. +-----------------------------+---------------------------+
39. 1 row in set (0.00 sec)

41. +--------------------+
42. |  t_record_v1.COUNT |
43. +--------------------+
44. |                  2 |
45. +--------------------+
46. 1 row in set (0.00 sec)

48. +---------------------------+-----------------------------+
49. |  t_record_v1(2)           |  t_record_v1(1)             |
50. +---------------------------+-----------------------------+
51. | id:30 | c1:NULL | c2:NULL | id:10 | c1:c1_11 | c2:44.06 |
52. +---------------------------+-----------------------------+
53. 1 row in set (0.00 sec)

55. Query OK, 0 rows affected (0.00 sec)
    1. 示例14： TYPE IS TABLE OF RECORD 包含 INDEX BY 以及默认值
1. greatsql> SET sql_mode = ORACLE;
2. greatsql> SET udt_format_result = 'DBA';
3. greatsql> DELIMITER //

5. greatsql> CREATE OR REPLACE PROCEDURE table_sp14 AS
6. TYPE t1_record IS RECORD(
7. id INT := 1,
8. c1 VARCHAR(20),
9. c2 FLOAT := 33.06
10. );
11. TYPE t1_list IS TABLE OF t1_record INDEX BY BINARY_INTEGER;

13. TYPE t2_record IS RECORD(
14. id INT := 1,
15. c1 t1_list,
16. c2 FLOAT := 33.06
17. );

19. TYPE t3_record IS RECORD(
20. v_i INT := 1,
21. t_record_v1 t2_record := t2_record(10, t1_list(1=>t1_record(1, 'c1_row1', 34.06)), 43.06)
22. );
23. t_record_v2 t3_record;

25. BEGIN
26. SELECT t_record_v2.t_record_v1.c1(1).c1;
27. END; //

29. greatsql> CALL table_sp14() //
30. +----------------------------------+
31. | t_record_v2.t_record_v1.c1(1).c1 |
32. +----------------------------------+
33. | c1_row1                          |
34. +----------------------------------+
35. 1 row in set (0.00 sec)

37. Query OK, 0 rows affected (0.00 sec)
    1. 示例15： table.FIRST / table.LAST
1. greatsql> SET sql_mode = ORACLE;
2. greatsql> SET udt_format_result = 'DBA';
3. greatsql> DELIMITER //

5. greatsql> CREATE OR REPLACE TYPE udt1 AS OBJECT(id INT, c1 VARCHAR(20));
6. greatsql> CREATE TABLE udt_t1(id INT, c2 udt1);

8. greatsql> CREATE OR REPLACE PROCEDURE table_sp15() AS
9. TYPE udt_t2 IS TABLE OF udt_t1%ROWTYPE INDEX BY BINARY_INTEGER;
10. udt_t3 udt_t2 := udt_t2(1=>NULL, -1=>NULL);
11. BEGIN
12. udt_t3(0).id := 10;
13. udt_t3(1).id := 20;
14. FOR i IN REVERSE udt_t3.FIRST .. udt_t3.LAST LOOP
15. SELECT i, udt_t3.FIRST, udt_t3.LAST, udt_t3(i);
16. END LOOP;
17. END; //

19. greatsql> CALL table_sp15() //
20. +------+--------------+-------------+-----------------+
21. | i    | udt_t3.FIRST | udt_t3.LAST | udt_t3(i)       |
22. +------+--------------+-------------+-----------------+
23. |    1 | -1           | 1           | id:20 | c2:NULL |
24. +------+--------------+-------------+-----------------+
25. 1 row in set (0.00 sec)

27. +------+--------------+-------------+-----------------+
28. | i    | udt_t3.FIRST | udt_t3.LAST | udt_t3(i)       |
29. +------+--------------+-------------+-----------------+
30. |    0 | -1           | 1           | id:10 | c2:NULL |
31. +------+--------------+-------------+-----------------+
32. 1 row in set (0.00 sec)

34. +------+--------------+-------------+-------------------+
35. | i    | udt_t3.FIRST | udt_t3.LAST | udt_t3(i)         |
36. +------+--------------+-------------+-------------------+
37. |   -1 | -1           | 1           | id:NULL | c2:NULL |
38. +------+--------------+-------------+-------------------+
39. 1 row in set (0.00 sec)

41. Query OK, 0 rows affected (0.00 sec)

更多关联用法：

问题反馈

联系我们

扫码关注微信公众号

greatsql-wx