Stored Functionの結果を表形式で取得

時々忘れるのでメモ的に。

• パッケージ宣言は以下

/* パッケージ宣言 */
CREATE OR REPLACE PACKAGE PACKAGE_AAA AS

	TYPE BRANCH_RESULT IS RECORD(
		SUM_ROW_FLG		CHAR(1) := '0',							/* 集計行フラグ。集計行 = 1 */
		BRANCH_NAME		TABLE.BRANCH_NAME%TYPE,	/* 店舗名     */
		SUM						TABLE.SUM%TYPE := 0				/*  小計      */
	);

	/* 構造体の配列。これがFUNC_AAA戻りの型 */
	TYPE BRANCH_RESULT_TBL IS TABLE OF BRANCH_RESULT;

	/* BRANCH_RESULTを定義して初期値をセットする */
	FUNCTION CREATEBRANCH_RESULT(
		DUMMY IN NUMBER
	) RETURN BRANCH_RESULT;

	/* 関数 */
	FUNCTION FUNC_AAA(
		P_AAA	IN ZZZ.AAA%TYPE ,
		P_BBB	IN ZZZ.BBBYPE,
		P_CCC	IN ZZZ.CCC%TYPE
	);
END PACKAGE_AAA;
/
SHOW ERRORS

/* パッケージ BODY */
CREATE OR REPLACE PACKAGE BODY PACKAGE_AAA AS

	FUNCTION CREATEBRANCH_RESULT(
		DUMMY IN NUMBER
	) RETURN BRANCH_RESULT
	IS
		/* BEGIN以下ではデフォルト値が設定されないようなので、ここで宣言 */
		BRANCH BRANCH_RESULT;
	BEGIN
		RETURN BRANCH;
	END CREATEBRANCH_RESULT;

	FUNCTION FUNC_AAA (
		P_AAA	IN ZZZ.AAA%TYPE ,
		P_BBB	IN ZZZ.BBBYPE,
		P_CCC	IN ZZZ.CCC%TYPE
	) RETURN BRANCH_RESULT_TBL PIPELINED
	IS
		/*店舗一覧取得SQL*/
		CURSOR CUR1 IS
			SELECT  BRANCH_NAME, SUM
			FROM BBB
			WHERE  CCC;
			
		RESULT BRANCH_RESULT;
	BEGIN
		FOR REC IN CUR1
			LOOP
				RESULT := CREATEBRANCH_RESULT(0);
				RESULT.BRANCH_NAME := REC.BRANCH_NAME;
				RESULT.SUM = REC.SUM;
				/* 出力 */
				PIPE ROW(RESULT);
			END LOOP;

	END FUNC_AAA;

END PACKAGE_AAA;
/
SHOW ERRORS

• 取得するsqlは以下

SELECT * FROM TABLE(PACKAGE_AAA.FUNC_AAA(:P_AAA, :P_BBB, :P_CCC))