07 PL/SQL

★ 프로시져 간단 예제2

- empno 2개를 입력 받아, 첫번째 사람의 sal을 v_sal에 받은 후,

두번째 사람의 sal을 v_sal * 1.2로 바꾼다.

create or replace procedure proc2(a_empno in number, a_empno2 in number)
is
   v_sal number;
begin
   proc1(a_empno, v_sal);   // 미리 만든 proc1을 사용한다.
   update emp set sal = 1.2 * v_sal where empno = a_empno2;
end;
/

show errors

// 결과 확인

SQL> select empno, sal from emp;   // 프로시져 실행 전의 결과

     EMPNO        SAL
---------- ----------
      7369        800
      7499       1600
      7521       1250
      7566       2975
      7654       1250
      7698       2850
      7782       2450
      7788       3000
      7839       5000
      7844       1500
      7876       1100

      7900        950                  // 두번째 사람의 sal을 바꾼다
      7902       3000
      7934       1300                  // 첫번째 사람의 sal을 가져와서

14 개의 행이 선택되었습니다.

SQL>  exec proc2(7934, 7900);

PL/SQL 처리가 정상적으로 완료되었습니다.

SQL> select empno, sal from emp;       // 실행 후의 결과

     EMPNO        SAL
---------- ----------
      7369        800
      7499       1600
      7521       1250
      7566       2975
      7654       1250
      7698       2850
      7782       2450
      7788       3000
      7839       5000
      7844       1500
      7876       1100

      7900       1560             // 7934의 1.2배로 바뀌었다.
      7902       3000
      7934       1300

14 개의 행이 선택되었습니다.

/******************************************************************************
*   파일      : CallTest.java
*   용도      : CallableStatement를 테스트하는 예제
*   작성자   : 성홍제
*   작성일   : 2006. 07. 28
*   Version : 1.0
******************************************************************************/

package CallableStatement;

import java.sql.*;
import java.io.*;
import ConnectionPool.DBConnectionMgr;

public class CallTest
{

    /**
     * @param args
     */
    public static void main(String[] args)
    {
        // TODO Auto-generated method stub
        DBConnectionMgr mgr = DBConnectionMgr.getInstance();
        Connection conn = null;
        CallableStatement cstmt = null;
        BufferedReader br = new BufferedReader(new InputStreamReader(System.in));
        int num = 0;
       
        try
        {
           conn = mgr.getConnection();
           cstmt = conn.prepareCall("{call proc1(?, ?)}");   // 함수 호출하는 방식
          
           System.out.print("찾을 사원의 번호은? >> ");
           String str = br.readLine();      // 사원 번호를 입력 받아서          
           num = Integer.parseInt(str);
          
           cstmt.setInt(1, num);        // 넣고
           cstmt.registerOutParameter(2, Types.CHAR);   // 결과를 어떤 형식으로 받을 것인가.
          
           cstmt.execute();
          
           int salary = cstmt.getInt(2);   // 결과를 받는다.
          
           System.out.println(salary);
           System.out.println("End");
          
           cstmt.close();
           conn.close();
           mgr.freeConnection(conn);
        }
        catch(Exception e)
        {
            e.printStackTrace();
        }

    }

}

CallableStatement는 PreparedStatement를 상속받은 인터페이스로 DB에 저장된 Stored Procedure를 수행하는데 활용된다.
설정은 PreparedStatement와 동일하게 ?, ?, ? 로 설정을 하고 이후에 setXXX 메소드로 설정 할 수 있다.
실행한 결과를 받아 올때는 out 파라미터 등록 메소드인 registerOutParameter()로 등록을 해야만

실행 후에 getXXX 메소드로 결과를 추출할 수 있다.
conn.prepareCall(“{cal test1}”);
conn.prepareCall(“{call test2(?,?)}”);
conn.prepareCall(“{? = call test2(?,?)}”);