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