この記事は約24分54秒で読むことができます。

複数行をサマルってコレクションを返却するとき、bulk collectしてはだめ。

まえがき

作りたいものあって、作ろうとしてたら、いまさら気づいた。気づけてよかった。12cから使えるwith functionで示してみる。

bulk collectは複数行対応

サマッテはだめ。普通に考えたら、collectってかいてあるしな。

collectionで返却

コード表示

WITH 
	FUNCTION calc(p_liz liz) RETURN liz IS
	
	rt liz :=NULL;
BEGIN
	SELECT
		item(rn,ln)
	BULK COLLECT
	INTO rt
	FROM
		(SELECT rn,ln FROM TABLE(p_liz))CONNECT BY rn >= LEVEL;
	RETURN rt;
END;
SELECT calc(liz(item(3,'1-2-1'))) FROM dual
/

CALC(LIZ(ITEM(3,'1-2-1')))(RN, LN)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
LIZ(ITEM(3, '1-2-1'), ITEM(3, '1-2-1'), ITEM(3, '1-2-1'))

table()でテーブル展開

サマッテはだめ。普通に考えたら、collectってかいてあるしな。

コード表示

CREATE OR REPLACE TYPE item IS OBJECT(rn NUMBER,ln VARCHAR2(4000));
/
CREATE OR REPLACE TYPE liz IS TABLE OF item;
/
WITH 
	FUNCTION calc(p_liz liz) RETURN liz IS
	
	rt liz :=NULL;
BEGIN
	SELECT
		item(rn,ln)
	BULK COLLECT
	INTO rt
	FROM
		(SELECT rn,ln FROM TABLE(p_liz))CONNECT BY rn >= LEVEL;
	RETURN rt;
END;
SELECT * FROM TABLE(calc(liz(item(3,'1-2-1'))))
/

        RN LN        
---------- ----------
         3 1-2-1     
         3 1-2-1     
         3 1-2-1     

bulk collectしないやつ。

collectionで返却

コード表示

CREATE OR REPLACE TYPE item IS OBJECT(rn NUMBER,ln VARCHAR2(4000));
/
CREATE OR REPLACE TYPE liz IS TABLE OF item;
/
WITH 
	FUNCTION calc(p_liz liz) RETURN liz IS
	
	rt liz :=NULL;
BEGIN
	SELECT
		CAST(COLLECT(ITEM(RN,LN)) AS LIZ)
	INTO rt
	FROM
		(SELECT rn,ln FROM TABLE(p_liz))CONNECT BY rn >= LEVEL;
	RETURN rt;
END;
SELECT calc(liz(item(3,'1-2-1'))) FROM dual
/

CALC(LIZ(ITEM(3,'1-2-1')))(RN, LN)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
LIZ(ITEM(3, '1-2-1'), ITEM(3, '1-2-1'), ITEM(3, '1-2-1'))

table()でテーブル展開

コード表示

CREATE OR REPLACE TYPE item IS OBJECT(rn NUMBER,ln VARCHAR2(4000));
/
CREATE OR REPLACE TYPE liz IS TABLE OF item;
/
WITH 
	FUNCTION calc(p_liz liz) RETURN liz IS
	
	rt liz :=NULL;
BEGIN
	SELECT
		CAST(COLLECT(ITEM(RN,LN)) AS LIZ)
	INTO rt
	FROM
		(SELECT rn,ln FROM TABLE(p_liz))CONNECT BY rn >= LEVEL;
	RETURN rt;
END;
SELECT * FROM table(calc(liz(item(3,'1-2-1'))))
/

        RN LN        
---------- ----------
         3 1-2-1     
         3 1-2-1     
         3 1-2-1     

表題のやつ

コード表示

CREATE OR REPLACE TYPE item IS OBJECT(rn NUMBER,ln VARCHAR2(4000));
/
CREATE OR REPLACE TYPE liz IS TABLE OF item;
/
WITH
	FUNCTION calc(p_liz liz) RETURN liz IS
	
	rt liz :=NULL;
BEGIN
	SELECT
		CAST(COLLECT(ITEM(RN,LN)) AS LIZ)
	BULK COLLECT
	INTO rt
	FROM
		(SELECT rn,ln FROM TABLE(p_liz))CONNECT BY rn >= LEVEL;
	RETURN rt;
END;
SELECT * FROM table(calc(liz(item(3,'1-2-1'))))
/

ORA-06552: PL/SQL: ORA-00932: データ型が一致しません: AINE.LIZが予想されましたがAINE.ITEMです。

Leave a Reply

Your email address will not be published. Required fields are marked *