Thread: how can I use a bound cursor

how can I use a bound cursor

From

윤동수

Date:

03 April 2005, 04:10:02

----------------------------------------------------------------
-- I ' ll use a bound cursor, bun when I execute following cursor
-- I met a error message within for loop
-- Help me...
----------------------------------------------------------------
CREATE OR REPLACE FUNCTION cursor_test(

          vp_param1               VARCHAR
         ,vp_param2               VARCHAR

)

RETURNS VARCHAR AS $BODY$

DECLARE

p_param1                        VARCHAR;
p_param1                        VARCHAR;

cur_test CURSOR (c_param VARCHAR) IS
    SELECT col_1, col_2, col_3 FROM tab_1 WHERE col_1 = c_param;

rec_test RECORD;

v_count                            NUMERIC;

BEGIN

    p_param1        := nullif(vp_param1 , '');
    p_param2        := nullif(vp_param2 , '');

    IF p_param1 = 'txn' THEN

       -------------------------------------------------
       -- 커서처리
       -------------------------------------------------
       OPEN cur_test(p_param2);

       for rec_test in cur_test loop

         --FETCH cur_test INTO rec_test;

         SELECT count(*) INTO v_count FROM tab_2 WHERE col_1 =
rec_test.col_1 and col_2 = rec_test.col_2 ;

         IF v_count >0 THEN

           insert into tab_2
           (
             col_1
           , col_2
           , col_3
           )
           values
           (
             rec_test.col_1
           , rec_test.col_2
           , rec_test.col_3
           );

         END IF;

       end loop; -- cur_test 커서처리

       CLOSE cur_test;

    END IF;

    RETURN '처리완료';

END;
$BODY$ LANGUAGE plpgsql;