2024.03.07

PL/SQL에서 쿼리문 실행

• PL/SQL에서 insert,update, delete는 그대로 사용할 수 있으나, select는 into절이 사용된다.
• cursor를 사용하지 않은 select은 한 행만 조회되어야한다.
  • select : 0행 , n행이 조회될 경우 error 발생
    • 0행이 조회될 경우 : NO_DATA_FOUND exception이 발생
    • n행이 조회될 경우 : TOO_MANY_ROWS exception이 발생
      • CURSOR를 사용
• 문법 )
  • select 컬럼명,,,,,
    into 변수명,,,, <- 조회된 컬럼 값을 저장할 변수들,,,, (조회컬럼과 into절의 변수의 수가 맞지 않으면 error 발생)
    // into절 : 컬럼명에 조회된 결과를 변수에 저장한다.
    from 테이블명
    where 조건

CURSOR

• 쿼리문이 실행된 행을 얻거나, 여러 행을 조회할 때 사용하는 객체.
  • 여러 행
    • 명시적 커서 : 개발자가 선언하여 사용하는 커서
      • PL/SQL에서 여러 행을 조회할 때 사용하는 객체
      • 생명 주기를 가진다(선언 -> 열기 -> 인출 -> 닫기)
      • INTO절이 필요없다.
      • OUT PARAMETER로 커서를 내보낼 때에는 SYS_REFCURSOR를 사용
        BIND 변수로는 REF_CURSOR사용
  • 쿼리문이 실행된 행
    • 임시적 커서 : INSERT, UPDATE, DELETE문이 실행되면 실행된 행의 수를 얻을 수 있는 커서
    • 커서명은 SQL로 설정되어있으며 %ROWCOUNT속성으로 실행된 행의 수를 얻을 수 있다.
    • 사용법 )
      • SQL%ROWCOUNT CNT :- SQL%ROWCOUNT

조회

• PL/SQL에서는 여러 행을 조회하기 위해 명시적 커서를 사용한다.
• SYS_REFUCURSOR를 OUT PARAMETER의 데이터형으로 사용하면 조회는 PROCEDURE안에서 수행하고 조회 결과를 PROCEDURE외부로 내 보낼 수 있다.
• 순서 )

1. 프로시저를 실행
2. REFCURSOR로 제어권을 받는다
3. PRINT 커서명을 사용하면 조회된 결과를 출력할 수 있다.

• 사용법 )
  • CREATE OR REPLACE PROCEDURE 프로시저명(변수명 OUT SYS_REFCURSOR)
    IS
    BEGIN
    OPEN 커서명 FOR SELECT,,,,;
    //쿼리 실행 결과를 PROCEDURE 외부에서 사용하기 때문에
    //PROCEDURE 내부에서는 생명주기에 대한 코딩을 하지 않는다.
    END;
    /
• 컴파일
• 호출 )
  1. 바인드변수 선언
  • VAR 커서명 REFCURSOR
  2. 프로시저 호출
  • EXEC 프로시저명(:커서명)
  3. 출력
  • PRINT 커서명

Callable Statement로 sys_refcursor 받기

• Types.REF_CURSOR 필드를 사용하거나, OracleTypes.CURSOR를 사용하여 Procedure의
  REF_CURSOR를 받을 수 있다.
• 사용법)
  • CallableStatement 얻기

    CallableStatement cstmt = con.prepareCall("{call 프로시저명(?,,,,)}");
    // ? : SYS_REFCURSOR가 설정된 out parameter

  • CallableStatement 얻기

    cstmt.registerOutParameter(인덱스, Types.REF_CURSOR);
    // Types.REF_CURSOR이나 OracleTypes.CURSOR 사용

  • 프로시저 실행

    cstmt.excute();

  • out parameter 값 받기 !!!!!!

    ResultSet rs = (ResultSet)cstmt.getObject(인덱스);

순서

1. 프로시저 선언

• Create or replace procedure select all proc(cur out sys_refcursor)
  is
  begin
  open cur for select,,,,;end;
  /
• java
  1. CallableStatement 선언

     CallableStatement cstmt = con.propareCall("{call select_all_proc(?)}")

  2. 바인드 변수 선언

     //in parameter
     //out parameter
     cstmt.registerOutParameter(1,Types.REF_CURSOR);

  3. 실행

     cstmt.execute();

  4. out parameter에 할당된 값 얻기

     ResultSet rs = (ResultSet)cstmt.getObject(인덱스);
     while(ns.next()){
     }
     ``