PLSQL collection 示例 之 varray

CREATE OR REPLACE TYPE test_scalar_varray AS VARRAY(3) OF VARCHAR2(20);

CREATE OR REPLACE FUNCTION test_filter_varray
(input_scalar_varray TEST_SCALAR_VARRAY)
RETURN TEST_SCALAR_VARRAY IS
output_scalar_varray TEST_SCALAR_VARRAY :=TEST_SCALAR_VARRAY();
BEGIN
output_scalar_varray:=input_scalar_varray;
output_scalar_varray.TRIM(1);
RETURN output_scalar_varray;
END;–最后的分号必不可少

CREATE OR REPLACE FUNCTION test_get_varray
( input_one VARCHAR2 := NULL
, input_two VARCHAR2 := NULL
, input_three VARCHAR2 := NULL )
RETURN TEST_SCALAR_VARRAY IS
output TEST_SCALAR_VARRAY := test_scalar_varray();
BEGIN
FOR i IN 1..3 LOOP
IF input_one IS NOT NULL AND i = 1 THEN
output.EXTEND;
output(i) := input_one;
ELSIF input_two IS NOT NULL AND i <= 2 THEN
output.EXTEND;
output(i) := input_two;
ELSIF input_three IS NOT NULL THEN
output.EXTEND;
output(i) := input_three;
ELSE
NULL;
END IF;
END LOOP;
RETURN output;
END;

SELECT column_value FROM TABLE(test_filter_varray(test_get_varray(‘AAA’,’BBB’,’CCC’)));–单引号注意