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