LightDB 23.3 plorasql 函数支持inout参数输出

开篇立意

oracle PLSQL函数中返回值有两种情况：
（1）使用return返回值；
（2）使用out修饰的参数（oracle不支持inout）

SQL> create function yu(id inout int) return int asbeginnull;return 12;end;  2    3    4    5  6  /Warning: Function created with compilation errors.SQL> show error function yu;
Errors for FUNCTION YU:LINE/COL ERROR
-------- -----------------------------------------------------------------
1/22	 PLS-00103: Encountered the symbol "INT" when expecting one of thefollowing::= . ) , @ % default character

在一个函数中上述两种返回值的方法都可以使用在同一个函数中

create table nested_tab(id int, name varchar2(100), job varchar2(100), age int);insert into nested_tab values (2, 'sdfsd', 'cvxvx', 14);SQL> create or replace function nested_func(id1 out int, name1 out varchar2, job1 out varchar2, age1 out int)
return int asid2 int;name2 varchar2(100);job2  varchar2(100);age2  int;
beginselect * into id2, name2, job1, age1 from nested_tab where   2    3    4    5    6    7    8  age = 14;id1 := id2;name1 := name2;job1 := job2;age1 := age2;beginid1 := 45;name1 := 'name';job1 := 'job';age1 := 45;end;return 0;
end;
/  9   10   11   12   13   14   15   16   17   18   19   20   21  Function created.SQL> create or replace function nested_func12(id1 out int, name1 out varchar2, job1 out varchar2, age1 out int)
return int asretcode int;
beginbeginretcode := nested_func(id1, name1, job1, age1);end;return 1;
end;
/  2    3    4    5    6    7    8    9   10  Function created.SQL> declareid2 int;name2 varchar2(100);job2  varchar2(100);age2  int;retcode int;
beginbeginretcode := nested_func12(id2, name2, job2, age2);end;dbms_output.put_line(id2 || name2 || job2 || age2);
end;
/  2    3    4    5    6    7    8    9   10   11   12   13  
45namejob45PL/SQL procedure successfully completed.

问题就出现了，LightDB是不支持这两者混用的，而且要有返回值。则这个参数必须既是输入又是输出，即inout，不然这个值是不能作为参数出现在函数中的。

问题解决

从plorasql内核代码可以知道，匿名块中函数的返回值是不能返回到上层的。涉及到执行器状态的切换，会释放函数内出现的变量的值，就算不释放也找不到函数内的变量和函数外的变量的对应值。因此解决这个问题需要分两步：
（1）解决执行器切换值释放问题；
（2）函数内的变量与函数外值的对应关系。

测试

 CREATE  TABLE fusettfundaccount (branch_no numeric(10,0) NOT NULL DEFAULT 0,fund_account character varying(18) NOT NULL DEFAULT ' '::character varying,client_id character varying(18) NOT NULL DEFAULT ' '::character varying,client_name character varying(60) NOT NULL DEFAULT ' '::character varying,asset_prop character(1) NOT NULL DEFAULT ' '::bpchar,fundacct_status character(1) NOT NULL DEFAULT ' '::bpchar,remark character varying(2000) NOT NULL DEFAULT ' '::character varying,position_str character varying(100) NOT NULL DEFAULT ' '::character varying,client_group numeric(10,0) NOT NULL DEFAULT 0,room_code numeric(10,0) NOT NULL DEFAULT 0,organ_flag character(1) NOT NULL DEFAULT ' '::bpchar) ;CREATE UNIQUE INDEX idx_fusettfundacct ON fusettfundaccount USING btree (fund_account);CREATE INDEX IF NOT EXISTS idx_fusettfundacct_id ON fusettfundaccount USING btree (client_id);CREATE INDEX IF NOT EXISTS idx_fusettfundacct_pos ON fusettfundaccount USING btree (position_str);
insert into fusettfundaccount values (6666, '6661347', '6661347', 'F148', '9','0',' ', '06666000000000006661347', 666607, 666617,'0');
CREATE OR REPLACE FUNCTION ap_fusett_fundaccount_get(p_fund_account varchar2, INOUT p_branch_no numeric, INOUT p_client_id varchar2, INOUT p_client_name varchar2, INOUT p_asset_prop varchar2, INOUT p_remark varchar2, INOUT p_error_pathinfo varchar2, INOUT p_error_info varchar2, INOUT p_error_no numeric, INOUT p_error_id numeric, INOUT p_error_sysinfo varchar2)RETURNS numericLANGUAGE plorasql
AS $function$ 
v_error_pathinfo_tmp  varchar2(100); beginp_branch_no  :=  0;
p_client_id  :=  ' ';
p_client_name  :=  ' ';
p_asset_prop  :=  ' ';
p_remark  :=  ' ';
p_error_info  :=  ' ';
p_error_no  :=  0;
p_error_id  :=  0;
p_error_sysinfo  :=  ' ';p_error_pathinfo := substr(p_error_pathinfo || '-->AP_FUSETT_FUNDACCOUNT_GET',1,500);
v_error_pathinfo_tmp := p_error_pathinfo;beginselect/*AP_FUSETT_FUNDACCOUNT_GET*/ branch_no, client_id,  client_name,  asset_prop, 
remarkinto p_branch_no, p_client_id, p_client_name, p_asset_prop, p_remarkfrom fusettfundaccountwhere fund_account = p_fund_account;exceptionwhen others thenp_error_no   := 318100;p_error_info := substrb('p_fund_account='||p_fund_account||']', 1, 500);p_error_id   := SQLCODE;p_error_sysinfo := substr(SQLERRM,1,500);return (p_error_no);end;
return(0);
exceptionwhen others thenp_error_no   := 101;p_error_info :='ִAP_FUSETT_FUNDACCOUNT_GET';p_error_id := SQLCODE;p_error_sysinfo := SQLERRM;return(p_error_no);
end AP_FUSETT_FUNDACCOUNT_GET$function$;
DO $$BEGINbegin$1 := AP_FUSETT_FUNDACCOUNT_GET ($2,$3,$4,$5,$6,$7,$8 ,$9,$10,$11,$12);end;if ($1 = 0) then$8 := $13;end if;END;$$ (INT, CHAR, INT, char, char, char, char, char, char, INT, INT, varchar, char) using (0, 6661347, 0, null,null, 0, null, NULL, null, 0, 0, null,null) Language plorasql;column1 | column2 | column3 | column4 | column5 | column6 | column7 | column8 | column9 | column10 | column11 | column12 | column13 
---------+---------+---------+---------+---------+---------+---------+---------+---------+----------+----------+----------+----------0 | 6661347 |    6666 | 6661347 | F148    | 9       |         |         |         |        0 |        0 |          | 
(1 row)

总结

在lightdb中，使用该功能需要注意：（1）函数用inout修饰需要返回的变量；（2）如果匿名块需要函数返回值时，使用inout修饰参数，return返回值必须要有具体的返回值，不然返回的就是一个record。