在存储过程中使用循环（loop）
---下面是真实环境下loop运用的实例
CREATE OR REPLACE FUNCTION USF_GET_CS_FAC_PUR_MULTI_DSCP
/*
Copyright(c) 2007 XXX
*/
(
fac_id_in IN TBL_FAC_PUR.FAC_ID%TYPE
)
RETURN VARCHAR2
AS
RETURN_FAC_DSCP NVARCHAR2(2000);
v_record NVARCHAR2(2000);
V_MT_FAC_DSCP_CUR TYPES.cursor_type;
BEGIN
BEGIN
RETURN_FAC_DSCP := ' ';
-- 1. select records into cursor
OPEN V_MT_FAC_DSCP_CUR FOR
SELECTTBL_MT_FAC_PUR.DSCP
FROMTBL_MT_FAC_PUR
INNER JOIN TBL_FAC_PUR ON TBL_FAC_PUR.MT_FAC_PUR_CD = TBL_MT_FAC_PUR.CD
WHERE TBL_FAC_PUR.FAC_ID = fac_id_in;
-- 2. loop the cursor
LOOP
FETCH V_MT_FAC_DSCP_CUR INTO v_record;
EXIT WHEN V_MT_FAC_DSCP_CUR%NOTFOUND;

IF RETURN_FAC_DSCP = ' ' THEN

RETURN_FAC_DSCP := v_record;

ELSE
RETURN_FAC_DSCP := RETURN_FAC_DSCP || ', ' || v_record;
END IF;
END LOOP;
END;
RETURN RETURN_FAC_DSCP;
END;


=====实例2==============

CREATE OR REPLACE FUNCTION USF_GET_CS_COND_CDI_MULTI_DSCP
/*
Copyright(c) 2007 XXX
20080314 : Initial Creation by XXX
*/
(
fac_id_in IN TBL_FAC_COND.FAC_ID%TYPE
)
RETURN VARCHAR2
AS

RETURN_COND_DSCP NVARCHAR2(2000);
v_record NVARCHAR2(2000);
V_COND_DSCP_CUR TYPES.cursor_type;

BEGIN
BEGIN
RETURN_COND_DSCP := ' ';
-- 1. select records into cursor
OPEN V_COND_DSCP_CUR FOR
SELECTTBL_FAC_COND.CONDITION
FROMTBL_FAC_COND
WHERE TBL_FAC_COND.FAC_ID = fac_id_in;
-- 2. loop the cursor
LOOP
FETCH V_COND_DSCP_CUR INTO v_record;
EXIT WHEN V_COND_DSCP_CUR%NOTFOUND;

IF RETURN_COND_DSCP = ' ' THEN

RETURN_COND_DSCP := v_record;

ELSE

RETURN_COND_DSCP := RETURN_COND_DSCP || ', ' || v_record;

END IF;
END LOOP;
END;
RETURN RETURN_COND_DSCP;
END;