[Oracle] 커서(cursor)

커서(cursor)
    - 오라클에서 할당한 전용 메모리 영역에 대한 포인터
    - 질의의 결과로 얻어진 여러 행이 저장된 메모리상의 위치
    - select 문의 결과 집합을 처리하는데 사용

 

create or replace procedure proc_member_select(
    p_userid in varchar2,
    p_point in number,
    o_cursor out SYS_REFCURSOR -- 조회결과를 출력하는 변수
)
is
    begin
        begin
            open o_cursor for 
                select mem_idx, mem_userid, mem_name, mem_gender from tb_member where mem_userid = p_userid;
            
            exception
                when others then
                    rollback;
                    dbms_output.put_line('SELECT SQL EXCEPTION!');
        end;
        begin
            update tb_member set mem_point = mem_point + p_point where mem_userid = p_userid;
            
            exception
                when others then
                    rollback;
                    dbms_output.put_line('UPDATE SQL EXCEPTION!');

    end;
end proc_member_select;

var o_cursor REFCURSOR -- 결과 담는 변수

exec proc_member_select('apple', 500, :o_cursor);

print o_cursor; -- 결과 출력