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


1. 语法

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

2. 定义和用法

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

3. Oracle兼容说明

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

  1. 支持无限循环嵌套,支持定义时设置默认值,也支持 RECORDTABLE 混用,比如 a.b(1).c

  2. 支持自定义类型时 DEFAULT 属性采用表达式,并进行赋值。

  3. 支持 UDT 类型,包括 CREATE TYPE AS OBJECTCREATE TYPE IS TABLE 两种方式。支持给 RECORD 里的成员变量赋初始值,除了 RECORD TABLE类型。

  4. 如果在存储过程的定义中用到 UDT 类型,在调用存储过程时允许操作这个 UDT 类型,包括对其删除、修改等,操作完后再次调用存储过程会重新解析。

  5. 支持无限循环嵌套的 SELECTSET 赋值。

  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 列名,比如 NAMEANYBULKTABLE 等。

  13. 在存储过程中的 TYPE 作为保留关键字,不能用作变量名。

4. 示例

    1. 示例1:TYPE IS RECORD/TYPE IS TABLE
  1. greatsql> SET sql_mode = ORACLE;
  2. greatsql> DELIMITER //
  3. greatsql> CREATE OR REPLACE PROCEDURE record_sp1 AS
  4. TYPE t1_record IS RECORD(
  5. id INT := 1,
  6. c1 VARCHAR(20),
  7. c2 FLOAT := 33.06
  8. );
  9. TYPE t1_list IS TABLE OF t1_record INDEX BY BINARY_INTEGER;
  10. TYPE t1_record1 IS RECORD(
  11. v_i INT := 1,
  12. t1_record_v1 t1_list
  13. );
  14. t1_record_val t1_record1;
  15. BEGIN
  16. t1_record_val.t1_record_v1(1).id := 1;
  17. t1_record_val.t1_record_v1(2).c1 := 'row1';
  18. t1_record_val.t1_record_v1(0).id := 2;
  19. t1_record_val.t1_record_v1(3).c2 := 36.06;
  20. SELECT t1_record_val.t1_record_v1(1).id,
  21. t1_record_val.t1_record_v1(1).c1,
  22. t1_record_val.t1_record_v1(0).id,
  23. t1_record_val.t1_record_v1(3).c2;
  24. SELECT t1_record_val.v_i;
  25. END; //
  26. greatsql> CALL record_sp1() //
  27. +----------------------------------+----------------------------------+----------------------------------+----------------------------------+
  28. | 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 |
  29. +----------------------------------+----------------------------------+----------------------------------+----------------------------------+
  30. | 1 | NULL | 2 | 36.06 |
  31. +----------------------------------+----------------------------------+----------------------------------+----------------------------------+
  32. 1 row in set (0.00 sec)
  33. +-------------------+
  34. | t1_record_val.v_i |
  35. +-------------------+
  36. | 1 |
  37. +-------------------+
  38. 1 row in set (0.00 sec)
  39. 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 //
  3. greatsql> CREATE OR REPLACE PROCEDURE record_sp2() AS
  4. v1 VARCHAR(20) := 'v1_str';
  5. TYPE t1_record IS RECORD(
  6. v2 VARCHAR(20) := v1
  7. );
  8. t1_record1 t1_record;
  9. BEGIN
  10. SELECT t1_record1.v2;
  11. END; //
  12. greatsql> CALL record_sp2() //
  13. +---------------+
  14. | t1_record1.v2 |
  15. +---------------+
  16. | v1_str |
  17. +---------------+
  18. 1 row in set (0.00 sec)
  19. Query OK, 0 rows affected (0.00 sec)
  20. greatsql> CREATE OR REPLACE PROCEDURE record_sp2() AS
  21. v1 VARCHAR(20) := 'v1_str';
  22. TYPE t1_record IS RECORD(
  23. v2 VARCHAR(20) := nullptr
  24. );
  25. t1_record1 t1_record;
  26. BEGIN
  27. SELECT t1_record1.v2;
  28. END; //
  29. ERROR 1054 (42S22): Unknown column 'nullptr' in 'field list'
  30. greatsql> CREATE OR REPLACE PROCEDURE record_sp1() AS
  31. TYPE t1_record IS RECORD(
  32. id INT := 1,
  33. v1 VARCHAR(20) := 'v1_str'
  34. );
  35. TYPE t1_list IS TABLE OF t1_record INDEX BY BINARY_INTEGER;
  36. v1 t1_list;
  37. BEGIN
  38. v1(1).id := 100;
  39. SELECT v1(1).id, v1(1).v1;
  40. END; //
  41. greatsql> CALL record_sp1() //
  42. +----------+----------+
  43. | v1(1).id | v1(1).v1 |
  44. +----------+----------+
  45. | 100 | v1_str |
  46. +----------+----------+
  47. 1 row in set (0.00 sec)
  48. 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';
  3. greatsql> CREATE OR REPLACE TYPE udt1 AS OBJECT(id INT, c1 VARCHAR(20));
  4. greatsql> CREATE OR REPLACE TYPE udt_t1 AS TABLE OF udt1;
  5. greatsql> DELIMITER //
  6. greatsql> CREATE OR REPLACE PROCEDURE record_sp3 AS
  7. TYPE t0_record IS RECORD(
  8. id INT := 1,
  9. c1 udt_t1,
  10. c2 FLOAT := 33.06
  11. );
  12. TYPE t1_record IS RECORD(
  13. v_i INT := 1,
  14. t1_record_v1 t0_record := t0_record(3, udt_t1(udt1(3, 'c1_row3')), 36.06)
  15. );
  16. t1_record_val t1_record;
  17. BEGIN
  18. SELECT t1_record_val.t1_record_v1.c1(1).id, t1_record_val.t1_record_v1.c1(1).c1;
  19. t1_record_val.t1_record_v1.c1(1).c1 := 'c1_row30';
  20. SELECT t1_record_val.t1_record_v1.c1(1).c1;
  21. END; //
  22. greatsql> CALL record_sp3() //
  23. +-------------------------------------+-------------------------------------+
  24. | t1_record_val.t1_record_v1.c1(1).id | t1_record_val.t1_record_v1.c1(1).c1 |
  25. +-------------------------------------+-------------------------------------+
  26. | 3 | c1_row3 |
  27. +-------------------------------------+-------------------------------------+
  28. 1 row in set (0.00 sec)
  29. +-------------------------------------+
  30. | t1_record_val.t1_record_v1.c1(1).c1 |
  31. +-------------------------------------+
  32. | c1_row30 |
  33. +-------------------------------------+
  34. 1 row in set (0.00 sec)
  35. 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';
  3. greatsql> DELIMITER //
  4. greatsql> CREATE OR REPLACE PROCEDURE record_sp4 AS
  5. TYPE t0_record IS RECORD(
  6. id INT := 1,
  7. c1 VARCHAR(20),
  8. c2 FLOAT := 33.06
  9. );
  10. TYPE t1_record IS RECORD(
  11. id INT := 1,
  12. c1 t0_record,
  13. c2 FLOAT := 33.06
  14. );
  15. TYPE t1_record1 IS RECORD(
  16. v_i INT := 1,
  17. t1_record_v1 t1_record := t1_record(10, t0_record(10, 'c1_row10', 43.06), 43.06)
  18. );
  19. t1_record_val t1_record1;
  20. BEGIN
  21. SELECT t1_record_val.t1_record_v1.id, t1_record_val.t1_record_v1.c1, t1_record_val.t1_record_v1.c2;
  22. 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;
  23. END; //
  24. greatsql> CALL record_sp4() //
  25. +-------------------------------+--------------------------------+-------------------------------+
  26. | t1_record_val.t1_record_v1.id | t1_record_val.t1_record_v1.c1 | t1_record_val.t1_record_v1.c2 |
  27. +-------------------------------+--------------------------------+-------------------------------+
  28. | 10 | id:10 | c1:c1_row10 | c2:43.06 | 43.06 |
  29. +-------------------------------+--------------------------------+-------------------------------+
  30. 1 row in set (0.00 sec)
  31. +----------------------------------+----------------------------------+----------------------------------+
  32. | t1_record_val.t1_record_v1.c1.id | t1_record_val.t1_record_v1.c1.c1 | t1_record_val.t1_record_v1.c1.c2 |
  33. +----------------------------------+----------------------------------+----------------------------------+
  34. | 10 | c1_row10 | 43.06 |
  35. +----------------------------------+----------------------------------+----------------------------------+
  36. 1 row in set (0.00 sec)
  37. 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';
  3. greatsql> CREATE OR REPLACE TYPE udt1 AS OBJECT(id INT, c1 VARCHAR(20));
  4. greatsql> CREATE OR REPLACE TYPE udt_t1 AS TABLE OF udt1;
  5. greatsql> DELIMITER //
  6. greatsql> CREATE OR REPLACE PROCEDURE record_sp5() AS
  7. rec1 udt_t1 := udt_t1(udt1(1, 'c1_row1'), udt1(2, 'c1_row2'));
  8. BEGIN
  9. rec1(1).id := 10;
  10. rec1(1).c1 := 'c1_row10';
  11. FOR i IN rec1.FIRST .. rec1.LAST LOOP
  12. SELECT rec1(i).id, rec1(i).c1;
  13. END LOOP;
  14. END; //
  15. greatsql> CALL record_sp5() //
  16. +------------+------------+
  17. | rec1(i).id | rec1(i).c1 |
  18. +------------+------------+
  19. | 10 | c1_row10 |
  20. +------------+------------+
  21. 1 row in set (0.00 sec)
  22. +------------+------------+
  23. | rec1(i).id | rec1(i).c1 |
  24. +------------+------------+
  25. | 2 | c1_row2 |
  26. +------------+------------+
  27. 1 row in set (0.00 sec)
  28. Query OK, 0 rows affected (0.00 sec)

更多关联用法:

问题反馈

联系我们

扫码关注微信公众号

greatsql-wx