LightDB中的存储过程（七）—— 子程序

LightDB支持存储过程，除了支持Postgres的plpgsql存储过程，还兼容Oracle的存储过程，新增了plorasql过程语言。上一篇中我们介绍了存储过程中的集合类型，这一篇主要讲述存储过程中的子程序。

子程序

子程序是命名的PL/SQL块，区别于匿名块，可参考文章LightDB中的存储过程（一），相比与匿名块，其优点再有可以反复调用，类似于C语言中的函数。

PL/SQL中的子程序包括过程和函数两种。通常，过程用于执行特定的操作，不需要返回值，而函数则有返回值。在调用时，过程可以作为一个独立的表达式被调用，而函数只能作为表达式的一部分被调用。

下面重点讲述LightDB中的存储过程以及函数的创建与使用。

创建存储过程

创建存储过程可通过CREATE PROCEDURE语法创建，示例如下：

create or replace procedure shenlan
as
declare
	type mytype is table of int index by integer;  -- 在存储过程中声明一个联合数组类型
	v mytype;
begin	
	select id bulk collect  into v from mystudent where rownum < 5;
	raise notice '%', v(1);
end;
/

drop procedure shenlan;  -- 同时会删掉类型mytype
1
2
3
4
5
6
7
8
9
10
11
12

其中 OR REPLACE表示如果存储过程已经存在，则重建该存储过程。另外也可以指定存储过程的模式，示例如下：

create or replace procedure changan.shenlan  -- changan为模式名，shenlan为存储过程名
as
declare
	type mytype is table of int index by integer;  -- 在存储过程中声明一个联合数组类型
	v mytype;
begin	
	select id bulk collect  into v from mystudent where rownum < 5;
	for i in 1..4 loop
		raise notice '%', v(i);		-- 通过下标访问联合数组
	end loop;
end;
/

-- 可通过call changan.shenlan进行调用。
1
2
3
4
5
6
7
8
9
10
11
12
13
14

删除可用drop procedure xxx语法删除存储过程。

存储过程创建后，可在pg_proc系统表中查看到：

postgres@postgres=# select * from pg_proc where proname='shenlan';
-[ RECORD 1 ]---+-----------------------------------------------------------------------------------
oid             | 45477
proname         | shenlan
pronamespace    | 2200
proowner        | 10
prolang         | 13583
procost         | 100
prorows         | 0
provariadic     | 0
prosupport      | -
prokind         | p
prosecdef       | t
proleakproof    | f
proisstrict     | f
proretset       | f
provolatile     | v
proparallel     | u
pronargs        | 0
pronargdefaults | 0
prorettype      | 2278
proargtypes     | 
proallargtypes  | 
proargmodes     | 
proargnames     | 
proargdefaults  | 
protrftypes     | 
prosrc          |                                                                                   +
                | declare                                                                           +
                | type mytype is table of int index by integer;  -- 在存储过程中声明一个联合数组类型+
                | v mytype;                                                                         +
                | begin                                                                             +
                | select id bulk collect  into v from mystudent where rownum < 5;                   +
                | for i in 1..4 loop                                                                +
                | raise notice '%', v(i);-- 通过下标访问联合数组                                    +
                | end loop;                                                                         +
                |                                                                                   +
                | v.delete(1);                                                                      +
                | -- for j in 1..3 loop                                                             +
                | -- raise notice '%', v(j);-- 通过下标访问联合数组                                 +
                | -- end loop;                                                                      +
                | end;
probin          | 
proconfig       | 
proacl          | 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46

创建函数

函数的创建与存储过程相似，不同之处在于函数有一个返回值。示例如下：

create or replace function sl03(a int) return int
as
declare
	type mytype is table of int index by integer;
	v mytype;
begin	
	select id bulk collect into v from mystudent where rownum < a;
	for i in v.first..v.last loop
		raise notice '%', i;
	end loop;
	
	return v.count;
end;
/
1
2
3
4
5
6
7
8
9
10
11
12
13
14

调用函数/存储过程

调用函数与存储过程是不同的，调用存储过程可用CALL命令调用，调用函数可用SELECT进行调用。

调用函数的示例：

create or replace function sl03(a int) return int
as
declare
	type mytype is table of int index by integer;
	v mytype;
begin	
	select id bulk collect into v from mystudent where rownum < a;
	for i in v.first..v.last loop
		raise notice '%', i;
	end loop;
	
	return v.count;
end;
/

postgres@postgres=# select sl03(2);
 sl03 
------
    1
(1 row)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

在块中调用函数示例如下：

create or replace function sl03(a int) return int
as
declare
	type mytype is table of int index by integer;
	v mytype;
begin	
	select id bulk collect into v from mystudent where rownum < a;
	return v.count;
end;
/

begin
	update mystudent set id = sl03(2);    -- 作为表达式的一部分被调用
end;
/

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

讲完了函数的调用，我们再看一下存储过程的调用，调用存储过程的实例如下：

postgres@postgres=# call shenlan;
NOTICE:  1
NOTICE:  2
NOTICE:  8
NOTICE:  4
CALL
1
2
3
4
5
6

如果在块中调用存储过程，则可省略CALL，直接调用即可。

begin
	shenlan;
end;
/
-- 运行结果：
NOTICE:  1
DO
1
2
3
4
5
6
7

更多请参考LightDB官网