Kingbase ES 函数返回-return语句
文章概要：
本文在https://www.cnblogs.com/kingbase/p/15703611.html
一文的基础上总结了Kingbase ES中函数能支持的return语句，整体上兼容oracle的基础上，也和PG语法一样。
同时列举了sqlserevr表值函数和代码故障案例。
一，关于return语句
有5个return语句可以用来从KES函数中返回数据：
RETURN
--执行后退出函数，函数返回类型须是return void
**RETURN expression **
--执行后退出函数，并立刻返回expression的值给调用者
RETURN NEXT expression
--执行后不退出函数，需要立刻退出可以使用return。可以被用于返回标量和复合数据类型；对于复合类型，将返回一个完整的结果“表”
RETURN QUERY query
--执行后不退出函数，而是追加要返回的结果集query，继续执行后面的语句，需要立刻退出可以使用return，函数结束时才最终返回给调用者
RETURN QUERY EXECUTE command-string [ USING expression [, ... ] ]
--RETURN QUERY的变种可以执行指定的动态SQL，同时可以指定传入的参数
二，使用场景
使用场景问题：
return 此情况的函数和存储过程一样无返回值，但是保留函数的调用方式
return expression 只能获取一个值，可以是标量也可以一个复合数据
return next 可以用于标量和复合类型数据，一般结合着for循环使用(如果涉及返回多行数据的话)
return query 将查询到的一条结果追加到函数的结果集中，无需结合for循环获取结果集；
return query execute 是 return query 的变形，用于执行指定的动态sql；
当需要函数返回一个数据集时，使用RETURN NEXT或者RETURN QUERY向结果集追加结果。
三，简单示例
**1，return expression **
return expression 只能获取一个值，可以是标量也可以一个复合数据
CREATE OR REPLACE FUNCTION getValue
(pv1 int)
RETURNS int
AS
DECLARE
vv int;
BEGIN
vv = 10 + pv1 * 0.95;
RETURN vv;
END;
--运行结果
getvalue
----------
20
(1 row)
**2，return **
return 此情况的函数和存储过程一样无返回值，但是保留函数的调用方式
需要声明函数为RETURNS void，return则用于提前退出函数
CREATE OR REPLACE FUNCTION getReturn(pv1 int)
RETURNS void
AS
BEGIN
if pv1 > 10 then
RAISE NOTICE '-----------> %',pv1;
else
return;
end if;
RAISE NOTICE '-----------> end';
END;
--运行结果
test=# select getreturn(10);
test-# /
getreturn
-----------
(1 row)
test=# select getreturn(11); --大于10
test-# /
NOTICE: -----------> 11
NOTICE: -----------> end
getreturn
-----------
(1 row)
**3，return next **
--声明函数是returns setof some_type（就是返回多行some_type数据的意思）时，需要结合for循环使用
--造一些数据
create table t1 (id int, name text);
insert into t1 values (10, 'tom');
insert into t1 values (20, 'kim');
insert into t1 values (30, 'mun');
--返回表的多行数据
create or replace function getallrow() returns setof t1 as
declare
oner t1%rowtype;
begin
for oner in select * from t1 where id > 0
loop
-- 此处可以执行一些其他语句
return next oner; -- 返回当前查询行
end loop;
return;
end;
--测试结果：
test-# /
getallrow
------------
(10,tom)
(20,kim)
(30,mun)
(3 rows)
--返回表的某列的多行数据
create or replace function getallrow1() returns setof text as
declare
onename text;
begin
for onename in select name from t1 where id > 0
loop
-- 此处可以执行一些其他语句
return next onename ; -- 返回当前查询行
end loop;
return;
end;
test-# /
getallrow1
------------
tom
kim
mun
(3 rows)
--返回表多列的多行数据
CREATE TYPE ttype AS (id int, name text);
create or replace function getallrow2() returns setof ttype as
declare
onename record;
begin
for onename in select id,name from t1 where id > 0
loop
-- 此处可以执行一些其他语句
return next onename ; -- 返回当前查询行
end loop;
return;
end;
test-# /
getallrow2
------------
(10,tom)
(20,kim)
(30,mun)
(3 rows)
4，RETURN QUERY query
执行后不退出函数，而是存入要返回的结果集query，继续执行后面的语句，需要立刻退出可以使用return，函数结束时才最终返回给调用者
4.1结合RETURNS SETOF使用
CREATE FUNCTION get_tid(int)
RETURNS SETOF integer
AS
BEGIN
RETURN QUERY SELECT id
FROM t1
WHERE id >= $1;
-- 因为执行还未结束，我们可以检查是否有行被返回
-- 如果没有就抛出异常。
if $1 < 0 then
RAISE EXCEPTION 'error number %.', $1;
end if;
RETURN; --可以使用return在return query之后退出函数
END;
--运行结果
test=# select get_tid(10);
test-# /
get_tid
---------
10
20
30
(3 rows)
test=# select get_tid(15);
test-# /
get_tid
---------
20
30
(2 rows)
test=# select get_tid(-1);
test-# /
ERROR: error number -1.
CONTEXT: PL/SQL function get_tid(integer) line 9 at RAISE
--也可以这样调用（和下面的return table结果有点区别）
test=# select * from get_tid(20);
test-# /
get_tid
---------
20
30
(2 rows)
4.2 结合RETURNS table，也是用来获取多行结果集的
CREATE FUNCTION get_tid2(int)
RETURNS table (num integer )
AS
BEGIN
RETURN QUERY SELECT id
FROM t1
WHERE id >= $1;
-- 因为执行还未结束，我们可以检查是否有行被返回
-- 如果没有就抛出异常。
if $1 < 0 then
RAISE EXCEPTION 'error number %.', $1;
end if;
RETURN; --可以使用return在return query之后退出函数
END;
--运行结果
test=# select get_tid2(10)
test-# /
get_tid2
----------
10
20
30
(3 rows)
test=# select get_tid2(15)
test-# /
get_tid2
----------
20
30
(2 rows)
test=# select get_tid2(-1)
test-# /
ERROR: error number -1.
CONTEXT: PL/SQL function get_tid2(integer) line 9 at RAISE
--也可以这样调用
test=# select * from get_tid2(20);
test-# /
num
-----
20
30
(2 rows)
5, RETURN QUERY EXECUTE
--RETURN QUERY的变种可以执行指定的动态SQL，同时可以指定传入的参数
依然基于4中的例子使用RETURN QUERY EXECUTE来等价改写，
注意：SQL 字符串中的$1 即是指第一个占位符，即使用USING子句传递的第一个值，
USING中的$1就是函数的第一个参数（两者恰好在各自的范围内都是 $1）
create or replace function get_tid3(int)
returns table (num integer )
as
return query execute 'select id from t1 where id >= $1'
using $1;
-- 因为执行还未结束，我们可以检查是否有行被返回
-- 如果没有就抛出异常。
if $1 < 0 then
raise exception 'error number %.', $1;
end if;
return; --可以使用return在return query之后退出函数
end;
test=# select* from get_tid3(5);
test$# /
num
-----
10
20
30
(3 rows)
test=# select* from get_tid3(20);
test$# /
num
-----
20
30
(2 rows)
test=# select* from get_tid3(-1);
test$# /
ERROR: error number -1.
CONTEXT: PL/pgSQL function get_tid3(integer) line 8 at RAISE
四，SQLServer中的表值函数
Sql server 的表值函数是返回一个Table类型,table类型相当与一张存储在内存中的一张虚拟表。
create function tvpoints()
returns @points table (x float , y float )
as begin
insert @points values (1,2);
insert @points values (3,4);
return ;
end
select * from tvpoints() --像查询表一样运行
--运行结果
x y
1 2
3 4
如何？其实作用就是KES中返回值为return query/return next的函数。上面的改写为KES中的语法形式就是：
create function tvpoints()
returns table (x float , y float )
as
begin
create temp table points(x float , y float);
insert into points values (1,2);
insert into points values (3,4);
return query select x,y from points;
end
select * from tvpoints()
test=# select * from tvpoints();
test$# /
x | y
---+---
1 | 2
3 | 4
(2 rows)
五，一个代码故障案例
这个是一个形成了死循环的代码故障案例，显然问题点在于循环里，RETURN query并会立刻退出函数。
CREATE OR REPLACE FUNCTION Get_StrTable("@content" text, "@length" integer)
RETURNS TABLE(id integer, content text)
AS
BEGIN
CREATE temp TABLE p_table
(
id serial,
content text
);
declare @allStrLen int= LEN(@content) ;
declare @lesStrLen int = LEN(@content) ;
declare @subStr_begin int=1 ;
declare @subStrLen int = @length ;
declare @subStrLen_before int = 0 ;
declare @subStr_before text ;
declare @subCONTENT text ;
if isnull(@content,'')<>''
begin
while(@subStr_begin<=@allStrLen)
begin
select @subCONTENT = SUBSTRING(@content,@subStr_begin,@subStrLen);
if(@lesStrLen = LEN(@subCONTENT))
begin
insert into p_table(content) values(@subCONTENT);
RETURN query SELECT * FROM p_table; ---问题在这里，执行后并不会立刻退出函数
end
else if(datalength(@subCONTENT) <= @length*2-1)
begin
select @subStrLen = @subStrLen + 1;
end
else --截取字符长度达到目标长度
begin
insert into p_table(content) values(@subCONTENT) ;
select @subStrLen_before = @subStrLen_before + @subStrLen ;
select @subStr_before = SUBSTRING(@content,1,@subStrLen_before) ;
select @subStr_begin = @subStrLen_before+1;
select @subStrLen = @length ;
select @lesStrLen = @allStrLen - @subStrLen_before ;
END
end;
END;
RETURN query SELECT * FROM p_table;
END;
