泰仔在線

java學習,心情日記,繽紛時刻

posts - 100, comments - 34, trackbacks - 0, articles - 0

Oracle ref 游標

Posted on 2009-08-07 09:44 泰仔在線閱讀(229) 評論(0) 編輯收藏所屬分類: Oracle 相關

CREATE OR REPLACE FUNCTION GetAreaCdStr(cmpyCd IN varchar2) RETURN VARCHAR2 AS

TYPE REFCURSOR IS REF CURSOR;

AREA_COR REFCURSOR;

areaCdStr VARCHAR2(500);

areaCd VARCHAR2(3);

BEGIN

OPEN AREA_COR FOR

SELECT DISTINCT AREA_CD FROM T_AREA_M WHERE CMPY_CD = cmpyCd ORDER BY AREA_CD;

LOOP

FETCH AREA_COR INTO areaCd;

EXIT WHEN AREA_COR%NOTFOUND;

BEGIN

areaCdStr := areaCdStr || areaCd || ',';

END;

END LOOP;

CLOSE AREA_COR;

RETURN areaCdStr;

END;

調用:

SELECT T.CMPY_CD, T.CMPY_NM, GETAREACDSTR(T.CMPY_CD) AS AREACDSTR

FROM T_CMPY_M T

ORDER BY T.CMPY_CD


只有注冊用戶登錄后才能發表評論。




網站導航: 博客園 IT新聞 Chat2DB C++博客博問管理
相關文章: Oracle 系統變量使用 Oracle Connect By Start With 總結==轉帖 ORACLE UPDATE 語句語法與性能分析的一點看法(轉) Oracle ref 游標 MS SQL Server和Oracle的常用函數對比 ORACLE SEQUENCE學習