背景
在24.1之前的版本,LightDB 在PLoraSQL中进行函数创建时,如果这个函数的参数是OUT模式的,那么调用这个函数时,无需传参,这和Oracle存在差异。在LightDB 24.1增量版本中,对上述行为进行了兼容。
用法
CREATE OR REPLACE FUNCTION f_test_out_param (
a PLS_INTEGER, -- IN by default
b IN PLS_INTEGER,
c OUT PLS_INTEGER,
d IN OUT BINARY_FLOAT
) RETURN INT IS
BEGIN
-- Print values of parameters:
DBMS_OUTPUT.PUT_LINE('Inside function f');
DBMS_OUTPUT.PUT('IN a = ');
DBMS_OUTPUT.PUT_LINE(NVL(TO_CHAR(a), 'NULL'));
DBMS_OUTPUT.PUT('IN b = ');
DBMS_OUTPUT.PUT_LINE(NVL(TO_CHAR(b), 'NULL'));
DBMS_OUTPUT.PUT('OUT c = ');
DBMS_OUTPUT.PUT_LINE(NVL(TO_CHAR(c), 'NULL'));
DBMS_OUTPUT.PUT_LINE('IN OUT d = ' || TO_CHAR(d));
-- Can reference IN parameters a and b,
-- but cannot assign values to them.
c := a+10; -- Assign value to OUT parameter
d := 10/b; -- Assign value to IN OUT parameter
return 0;
END;
/
DECLARE
aa CONSTANT PLS_INTEGER := 1;
bb PLS_INTEGER := 2;
cc PLS_INTEGER := 3;
dd BINARY_FLOAT := 4;
ee PLS_INTEGER;
ff BINARY_FLOAT := 5;
v INT;
BEGIN
DBMS_OUTPUT.PUT_LINE('Before invoking function f:');
DBMS_OUTPUT.PUT('aa = ');
DBMS_OUTPUT.PUT_LINE(NVL(TO_CHAR(aa), 'NULL'));
DBMS_OUTPUT.PUT('bb = ');
DBMS_OUTPUT.PUT_LINE(NVL(TO_CHAR(bb), 'NULL'));
DBMS_OUTPUT.PUT('cc = ');
DBMS_OUTPUT.PUT_LINE(NVL(TO_CHAR(cc), 'NULL'));
DBMS_OUTPUT.PUT_LINE('dd = ' || TO_CHAR(dd));
v := f_test_out_param (aa, -- constant
bb, -- initialized variable
cc, -- initialized variable
dd -- initialized variable
);
DBMS_OUTPUT.PUT_LINE('After invoking function f:');
DBMS_OUTPUT.PUT('aa = ');
DBMS_OUTPUT.PUT_LINE(NVL(TO_CHAR(aa), 'NULL'));
DBMS_OUTPUT.PUT('bb = ');
DBMS_OUTPUT.PUT_LINE(NVL(TO_CHAR(bb), 'NULL'));
DBMS_OUTPUT.PUT('cc = ');
DBMS_OUTPUT.PUT_LINE(NVL(TO_CHAR(cc), 'NULL'));
DBMS_OUTPUT.PUT_LINE('dd = ' || TO_CHAR(dd));
DBMS_OUTPUT.PUT_LINE('v = ' || TO_CHAR(v));
END;
/
Before invoking function f:
aa = 1
bb = 2
cc = 3
dd = 4
Inside function f
IN a = 1
IN b = 2
OUT c = 3
IN OUT d = 4
After invoking function f:
aa = 1
bb = 2
cc = 11
dd = 5
v = 0
DROP FUNCTION f_test_out_param;
总结
目前LightDB中的OUT参数和Oracle还存在一些差异,如Oracle中OUT形参值是类型的默认值(非RECORD类型,为NULL);LightDB中OUT形参值等于实参值。