|
----------------------------------------------------------------
-- 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; |
