Oracle兼容-存储过程-%TYPE

1. 语法

  1. var_name (ref_var_name | table_name.column_name | ref_rowtype)%TYPE [:= init_val]

2. 定义和用法

在GreatSQL中支持用 %TYPE 来声明变量类型，利用 %TYPE 声明可使得变量的数据类型与其他某个变量或列的数据类型相同。

可以被 %TYPE 应用的变量类型有：

  • 普通变量
  • 表中某列
  • %ROWTYPE类型
  • %ROWTYPE类型中某列

3. Oracle兼容说明

在 ORACLE 模式下，GreatSQL支持用 %TYPE 来声明变量类型。该用法如下所述：

  1. 支持所有已兼容的数据类型。

  2. 不支持引用临时表中的列。

  3. 不支持事务（VIEW），包括 view%TYPE 和 view%ROWTYPE。

  4. 不支持复用自己类型的用法，例如 x x%TYPE。

4. 示例

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

  1. greatsql> SET SESSION sql_generate_invisible_primary_key = 0;

创建测试表并初始化数据

  1. greatsql> CREATE TABLE t1 (a INT NOT NULL, b VARCHAR(20) NOT NULL);
  2. greatsql> INSERT INTO t1 VALUES(1, 'row1'), (2, 'row2'), (3,'row3') ;
    1. 示例1
  1. greatsql> SET sql_mode = ORACLE;
  2. greatsql> DELIMITER //
  4. greatsql> CREATE OR REPLACE PROCEDURE vtype_sp1() AS
  5.   v_id t1.a%TYPE;
  6.   v_vchar VARCHAR(100) := 'varchar_type';
  8.   v3 v_vchar%TYPE;
  9.   v4 v3%TYPE;
  10. BEGIN
  11.   v_id := 1;
  12.   v_vchar := 'v_vchar_type';
  13.   v3 := 'v3_varchar';
  14.   v4 := 'v4_varchar';
  16.   SELECT v_id, v_vchar, v3, v4;
  17. END; //
  19. greatsql> CALL vtype_sp1() //
  20. +------+--------------+------------+------------+
  21. | v_id | v_vchar      | v3         | v4         |
  22. +------+--------------+------------+------------+
  23. |    1 | v_vchar_type | v3_varchar | v4_varchar |
  24. +------+--------------+------------+------------+
  25. 1 row in set (0.00 sec)
  27. Query OK, 0 rows affected (0.00 sec)
    1. 示例2
  1. greatsql> SET sql_mode = ORACLE;
  2. greatsql> DELIMITER //
  4. greatsql> CREATE OR REPLACE PROCEDURE vtype_sp2() AS
  5.   CURSOR cur1 (v_a INT) IS SELECT * FROM t1 WHERE a = v_a;
  6.   rec1 cur1%ROWTYPE;
  7.   rec2 rec1%TYPE;
  8. BEGIN
  9.   FOR rec2 IN cur1(1)
  10.   LOOP
  11.     SELECT rec2.b;
  12.   END LOOP;
  13. END; //
  15. greatsql> CALL vtype_sp2() //
  16. +--------+
  17. | rec2.b |
  18. +--------+
  19. | row1   |
  20. +--------+
  21. 1 row in set (0.00 sec)
  23. Query OK, 0 rows affected (0.00 sec)
    1. 示例3
  1. greatsql> SET sql_mode = ORACLE;
  2. greatsql> DELIMITER //
  4. greatsql> DECLARE
  5.   rec t1.a%TYPE;
  6. BEGIN
  7.   rec := ROUND(RAND()*10240);
  9.   SELECT rec;
  10. END; //
  11. +------+
  12. | rec  |
  13. +------+
  14. | 8473 |
  15. +------+
  16. 1 row in set (0.01 sec)
  18. Query OK, 0 rows affected (0.01 sec)
    1. 示例4
  1. greatsql> SET sql_mode = ORACLE;
  2. greatsql> SET udt_format_result = 'DBA';
  4. greatsql> CREATE OR REPLACE TYPE udt1 AS OBJECT(a INT, b VARCHAR(20));
  5. greatsql> CREATE TABLE udt_t1(a INT, b udt1);
  6. greatsql> INSERT INTO udt_t1 VALUES(1, udt1(1, 'c1_row1'));
  7. greatsql> INSERT INTO udt_t1 VALUES(2, udt1(2, 'c1_row2'));
  8. greatsql> INSERT INTO udt_t1 VALUES(3, udt1(3, 'c1_row3'));
  10. greatsql> DELIMITER //
  12. greatsql> CREATE OR REPLACE PROCEDURE vtype_sp3() AS
  13.   vt1 udt_t1.b%TYPE;
  14.   vt2 udt1;
  15. BEGIN
  16.   SELECT b INTO vt1 FROM udt_t1 WHERE a = 2;
  17.   SELECT vt1.a, vt1.b;
  19.   SELECT b INTO vt2 FROM udt_t1 WHERE a = 3;
  20.   SELECT vt2.a, vt2.b;
  22.   SELECT vt1 INTO vt2;
  23.   SELECT vt2.a, vt2.b;
  24. END; //
  26. greatsql> CALL vtype_sp3() //
  27. +-------+---------+
  28. | vt1.a | vt1.b   |
  29. +-------+---------+
  30. |     2 | c1_row2 |
  31. +-------+---------+
  32. 1 row in set (0.00 sec)
  34. +-------+---------+
  35. | vt2.a | vt2.b   |
  36. +-------+---------+
  37. |     3 | c1_row3 |
  38. +-------+---------+
  39. 1 row in set (0.00 sec)
  41. +-------+---------+
  42. | vt2.a | vt2.b   |
  43. +-------+---------+
  44. |     2 | c1_row2 |
  45. +-------+---------+
  46. 1 row in set (0.00 sec)
  48. Query OK, 0 rows affected (0.00 sec)
    1. 示例5
  1. -- 在示例4的基础上继续
  2. greatsql> SET sql_mode = ORACLE;
  3. greatsql> DELIMITER //
  5. greatsql> CREATE OR REPLACE PROCEDURE vtype_sp4() AS
  6.   vt1 udt_t1%ROWTYPE;
  7. BEGIN
  8.   vt1.b := udt1(10, 'c1_row10');
  10.   SELECT vt1, vt1.a, vt1.b.a, vt1.b.b;
  11. END; //
  13. greatsql> CALL vtype_sp4() //
  14. +------------------------------+-------+---------+----------+
  15. | vt1                          | vt1.a | vt1.b.a | vt1.b.b  |
  16. +------------------------------+-------+---------+----------+
  17. | a:NULL | b:a:10 | b:c1_row10 |  NULL |      10 | c1_row10 |
  18. +------------------------------+-------+---------+----------+
  19. 1 row in set (0.00 sec)
  21. Query OK, 0 rows affected (0.00 sec)

问题反馈

联系我们

扫码关注微信公众号

greatsql-wx