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

1. 语法

1. 1.
2. SET sql_mode = ORACLE;
3. TYPE var IS RECORD(columns type [default value]...)

5. 2.
6. SET sql_mode = ORACLE;
7. var var_def

2. 定义和用法

在GreatSQL中支持用 TYPE IS RECORD() 方式来自定义数据类型，同时也支持用 var var_def 方式自定义数据类型。

3. Oracle兼容说明

在GreatSQL中支持用 TYPE IS RECORD() 方式来自定义数据类型，同时也支持用 var var_def 方式自定义数据类型。该用法如下所述：

  1. 支持无限循环嵌套，支持定义时设置默认值，也支持 RECORD 和 TABLE 混用，比如 a.b(1).c。
  2. 支持自定义类型时 DEFAULT 属性采用表达式，并进行赋值。
  3. 支持 UDT 类型，包括 CREATE TYPE AS OBJECT 和 CREATE TYPE IS TABLE 两种方式。支持给 RECORD 里的成员变量赋初始值，除了 RECORD TABLE类型。
  4. 如果在存储过程的定义中用到 UDT 类型，在调用存储过程时允许操作这个 UDT 类型，包括对其删除、修改等，操作完后再次调用存储过程会重新解析。
  5. 支持无限循环嵌套的 SELECT、SET 赋值。
  6. 支持用参数赋值的方法定义 RECORD 字段，例如v1 VARCHAR(20) := v2，详见下面示例2。如果该参数同为 RECORD 内的参数，则赋值失败。
  7. 不支持单类型的数组定义，比如 CREATE TYPE v1 AS VARRAY(10) OF VARCHAR2(80);。
  8. 不支持自定义类型时附加 NOT NULL 属性。
  9. 不支持 TABLE 类型作为 RECORD 的类型使用。
  10. 不支持 RECORD%TYPE 作为默认值，但支持自定义 RECORD 类型和 UDT TABLE 类型默认赋值。例如：e stu_record := stu_record(2)。
  11. 不支持 RECORD 的成员字段定义为 %TYPE 类型。
  12. 不支持使用保留关键字作为 RECORD 列名，比如 NAME、ANY、BULK、TABLE 等。
  13. 在存储过程中的 TYPE 作为保留关键字，不能用作变量名。

4. 示例

    1. 示例1：TYPE IS RECORD/TYPE IS TABLE
1. greatsql> SET sql_mode = ORACLE;
2. greatsql> DELIMITER //

4. greatsql> CREATE OR REPLACE PROCEDURE record_sp1 AS
5. TYPE t1_record IS RECORD(
6. id INT := 1,
7. c1 VARCHAR(20),
8. c2 FLOAT := 33.06
9. );
10. TYPE t1_list IS TABLE OF t1_record INDEX BY BINARY_INTEGER;
11. TYPE t1_record1 IS RECORD(
12. v_i INT := 1,
13. t1_record_v1 t1_list
14. );
15. t1_record_val t1_record1;
16. BEGIN
17. t1_record_val.t1_record_v1(1).id := 1;
18. t1_record_val.t1_record_v1(2).c1 := 'row1';
19. t1_record_val.t1_record_v1(0).id := 2;
20. t1_record_val.t1_record_v1(3).c2 := 36.06;

22. SELECT t1_record_val.t1_record_v1(1).id,
23. t1_record_val.t1_record_v1(1).c1,
24. t1_record_val.t1_record_v1(0).id,
25. t1_record_val.t1_record_v1(3).c2;

27. SELECT t1_record_val.v_i;
28. END; //

30. greatsql> CALL record_sp1() //
31. +----------------------------------+----------------------------------+----------------------------------+----------------------------------+
32. | 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 |
33. +----------------------------------+----------------------------------+----------------------------------+----------------------------------+
34. |                                1 | NULL                             |                                2 |                            36.06 |
35. +----------------------------------+----------------------------------+----------------------------------+----------------------------------+
36. 1 row in set (0.00 sec)

38. +-------------------+
39. | t1_record_val.v_i |
40. +-------------------+
41. |                 1 |
42. +-------------------+
43. 1 row in set (0.00 sec)

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

更多用法请参考：ora_type_is_table.md

    1. 示例2：TYPE RECORD + 默认赋值
1. greatsql> SET sql_mode = ORACLE;
2. greatsql> DELIMITER //

4. greatsql> CREATE OR REPLACE PROCEDURE record_sp2() AS
5. v1 VARCHAR(20) := 'v1_str';

7. TYPE t1_record IS RECORD(
8. v2 VARCHAR(20) := v1
9. );

11. t1_record1 t1_record;
12. BEGIN
13. SELECT t1_record1.v2;
14. END; //

16. greatsql> CALL record_sp2() //
17. +---------------+
18. | t1_record1.v2 |
19. +---------------+
20. | v1_str        |
21. +---------------+
22. 1 row in set (0.00 sec)

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

27. greatsql> CREATE OR REPLACE PROCEDURE record_sp2() AS
28. v1 VARCHAR(20) := 'v1_str';

30. TYPE t1_record IS RECORD(
31. v2 VARCHAR(20) := nullptr
32. );

34. t1_record1 t1_record;
35. BEGIN
36. SELECT t1_record1.v2;
37. END; //
38. ERROR 1054 (42S22): Unknown column 'nullptr' in 'field list'

42. greatsql> CREATE OR REPLACE PROCEDURE record_sp1() AS
43. TYPE t1_record IS RECORD(
44. id INT := 1,
45. v1 VARCHAR(20) := 'v1_str'
46. );

48. TYPE t1_list IS TABLE OF t1_record INDEX BY BINARY_INTEGER;
49. v1 t1_list;
50. BEGIN
51. v1(1).id := 100;

53. SELECT v1(1).id, v1(1).v1;
54. END; //

56. greatsql> CALL record_sp1() //
57. +----------+----------+
58. | v1(1).id | v1(1).v1 |
59. +----------+----------+
60. |      100 | v1_str   |
61. +----------+----------+
62. 1 row in set (0.00 sec)

64. Query OK, 0 rows affected (0.00 sec)
    1. 示例3：TYPE IS UDT TYPE AND SET DEFAULT VALUE
1. greatsql> SET sql_mode = ORACLE;
2. greatsql> SET udt_format_result = 'DBA';

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

8. greatsql> CREATE OR REPLACE PROCEDURE record_sp3 AS
9. TYPE t0_record IS RECORD(
10. id INT := 1,
11. c1 udt_t1,
12. c2 FLOAT := 33.06
13. );
14. TYPE t1_record IS RECORD(
15. v_i INT := 1,
16. t1_record_v1 t0_record := t0_record(3, udt_t1(udt1(3, 'c1_row3')), 36.06)
17. );
18. t1_record_val t1_record;

20. BEGIN
21. SELECT t1_record_val.t1_record_v1.c1(1).id, t1_record_val.t1_record_v1.c1(1).c1;

23. t1_record_val.t1_record_v1.c1(1).c1 := 'c1_row30';

25. SELECT t1_record_val.t1_record_v1.c1(1).c1;
26. END; //

28. greatsql> CALL record_sp3() //
29. +-------------------------------------+-------------------------------------+
30. | t1_record_val.t1_record_v1.c1(1).id | t1_record_val.t1_record_v1.c1(1).c1 |
31. +-------------------------------------+-------------------------------------+
32. |                                   3 | c1_row3                             |
33. +-------------------------------------+-------------------------------------+
34. 1 row in set (0.00 sec)

36. +-------------------------------------+
37. | t1_record_val.t1_record_v1.c1(1).c1 |
38. +-------------------------------------+
39. | c1_row30                            |
40. +-------------------------------------+
41. 1 row in set (0.00 sec)

43. Query OK, 0 rows affected (0.00 sec)
    1. 示例4：TYPE IS RECORD TYPE AND SET DEFAULT VALUE
1. greatsql> SET sql_mode = ORACLE;
2. greatsql> SET udt_format_result = 'DBA';

4. greatsql> DELIMITER //

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

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

19. TYPE t1_record1 IS RECORD(
20. v_i INT := 1,
21. t1_record_v1 t1_record := t1_record(10, t0_record(10, 'c1_row10', 43.06), 43.06)
22. );

24. t1_record_val t1_record1;

26. BEGIN
27. SELECT t1_record_val.t1_record_v1.id, t1_record_val.t1_record_v1.c1, t1_record_val.t1_record_v1.c2;

29. SELECT t1_record_val.t1_record_v1.c1.id, t1_record_val.t1_record_v1.c1.c1, t1_record_val.t1_record_v1.c1.c2;
30. END; //

32. greatsql> CALL record_sp4() //
33. +-------------------------------+--------------------------------+-------------------------------+
34. | t1_record_val.t1_record_v1.id | t1_record_val.t1_record_v1.c1  | t1_record_val.t1_record_v1.c2 |
35. +-------------------------------+--------------------------------+-------------------------------+
36. |                            10 | id:10 | c1:c1_row10 | c2:43.06 |                         43.06 |
37. +-------------------------------+--------------------------------+-------------------------------+
38. 1 row in set (0.00 sec)

40. +----------------------------------+----------------------------------+----------------------------------+
41. | t1_record_val.t1_record_v1.c1.id | t1_record_val.t1_record_v1.c1.c1 | t1_record_val.t1_record_v1.c1.c2 |
42. +----------------------------------+----------------------------------+----------------------------------+
43. |                               10 | c1_row10                         |                            43.06 |
44. +----------------------------------+----------------------------------+----------------------------------+
45. 1 row in set (0.00 sec)

47. Query OK, 0 rows affected (0.00 sec)
    1. 示例5：TYPE IS UDT TABLE TYPE AND SET DEFAULT VALUE
1. greatsql> SET sql_mode = ORACLE;
2. greatsql> SET udt_format_result = 'DBA';

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

8. greatsql> CREATE OR REPLACE PROCEDURE record_sp5() AS
9. rec1 udt_t1 := udt_t1(udt1(1, 'c1_row1'), udt1(2, 'c1_row2'));
10. BEGIN
11. rec1(1).id := 10;
12. rec1(1).c1 := 'c1_row10';

14. FOR i IN rec1.FIRST .. rec1.LAST LOOP
15. SELECT rec1(i).id, rec1(i).c1;
16. END LOOP;
17. END; //

19. greatsql> CALL record_sp5() //
20. +------------+------------+
21. | rec1(i).id | rec1(i).c1 |
22. +------------+------------+
23. |         10 | c1_row10   |
24. +------------+------------+
25. 1 row in set (0.00 sec)

27. +------------+------------+
28. | rec1(i).id | rec1(i).c1 |
29. +------------+------------+
30. |          2 | c1_row2    |
31. +------------+------------+
32. 1 row in set (0.00 sec)

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

更多关联用法：

问题反馈

联系我们

扫码关注微信公众号

greatsql-wx