[펌] Java에서 Oracle Stored Procedure호출하기

1. Single Row 삽입

-- 사진 등록 (사진의 내용을 등록하고 등록된 사진의 id를 반환한다.)
CREATE OR REPLACE PROCEDURE insertPhoto
(
        p_bl_id IN NUMBER,             /* board ID */
        p_p_writetime IN DATE,          /* 글쓴시간 */
        p_p_subject IN VARCHAR2,        /* 제목 */
        p_p_userid IN VARCHAR2,         /* 글쓴이 */
        p_p_contents IN VARCHAR2,       /* 내용 */
        p_p_attach IN VARCHAR2,         /* 첨부파일 */
        p_p_hit IN NUMBER,              /* 조회수 */
        p_p_ip IN VARCHAR2,             /* 아이피 */
        p_p_id OUT NUMBER
)
IS
BEGIN
        INSERT INTO t_photo(p_id, bl_id, p_writetime, p_subject, p_userid, p_contents, p_attach, p_hit, p_ip)
        VALUES(p_id_seq.nextval, p_bl_id, p_p_writetime, p_p_subject, p_p_userid, p_p_contents, p_p_attach, p_p_hit, p_p_ip);

        select max(p_id) into p_p_id from t_photo where p_userid=p_p_userid;
 
        COMMIT;
END;
/

        public 
        int 
        insertPhoto(
                           PhotoBean photoBean
                           ) throws SQLException
        {
                if(!isOpen())
                        throw new SQLException("먼저 Connection을 Open하세요\n");
               
                cstmt = conn.prepareCall(
                                "{call insertPhoto(?,?,?,?,?,?,?,?,?)}"
                                );
               
               
                cstmt.setInt(1, photoBean.getBl_id());
                cstmt.setDate(2, photoBean.getP_writetime());
                cstmt.setString(3, photoBean.getP_subject());
                cstmt.setString(4, photoBean.getP_userid());
                cstmt.setString(5, photoBean.getP_contents());
                cstmt.setString(6, photoBean.getP_attach());
                cstmt.setInt(7, 0);
                cstmt.setString(8, photoBean.getP_ip());
               
                cstmt.registerOutParameter(9, java.sql.Types.INTEGER);
                cstmt.execute();
               
                int result = cstmt.getInt(9);
                               
                return result;
        }
       

2. Single Row 검색

-- 사진 세부 조회
CREATE OR REPLACE PROCEDURE selectDetailPhoto
(
        p_p_id IN NUMBER,
        p_p_writetime OUT DATE,         /* 글쓴시간 */
        p_p_subject OUT VARCHAR2,       /* 제목 */
        p_p_userid OUT VARCHAR2,        /* 글쓴이 */
        p_p_contents OUT VARCHAR2,      /* 내용 */
        p_p_attach OUT VARCHAR2,        /* 첨부파일 */
        p_p_hit OUT NUMBER,             /* 조회수 */
        p_p_ip OUT VARCHAR2             /* 아이피 */
)
IS
BEGIN
        select p_writetime, p_subject, p_userid, p_contents, p_attach, p_hit, p_ip
        into p_p_writetime, p_p_subject, p_p_userid, p_p_contents, p_p_attach, p_p_hit, p_p_ip
        from t_photo where p_id=p_p_id;

        COMMIT;
END;
/

        public 
        PhotoBean
        selectDetail(
                                 int p_id
                                 ) throws SQLException
        {
                if(!isOpen())
                        throw new SQLException("먼저 Connection을 Open하세요\n");
               
                cstmt = conn.prepareCall(
                                "{call selectDetailPhoto(?,?,?,?,?,?,?,?)}"
                                );
                               
                cstmt.setInt(1, p_id);
                cstmt.registerOutParameter(2, java.sql.Types.DATE);
                cstmt.registerOutParameter(3, java.sql.Types.VARCHAR);
                cstmt.registerOutParameter(4, java.sql.Types.VARCHAR);
                cstmt.registerOutParameter(5, java.sql.Types.VARCHAR);
                cstmt.registerOutParameter(6, java.sql.Types.VARCHAR);
                cstmt.registerOutParameter(7, java.sql.Types.INTEGER);
                cstmt.registerOutParameter(8, java.sql.Types.VARCHAR);
               
                cstmt.execute();
               
                PhotoBean photoBean = new PhotoBean();
                photoBean.setP_writetime(cstmt.getDate(2));
                photoBean.setP_subject(cstmt.getString(3));
                photoBean.setP_userid(cstmt.getString(4));
                photoBean.setP_contents(cstmt.getString(5));
                photoBean.setP_attatch(cstmt.getString(6));
                photoBean.setP_hit(cstmt.getInt(7));
                photoBean.setP_ip(cstmt.getString(8));
               
                return photoBean;
        }

3. Multy Row 검색 (페이징 적용)
  - 이전의 방법은 결과로 나온 Single Row를 각 Column을 모두 Output Parameter로 맵핑시켜서 가지고 나온 방법이었지만, 이번 방법은 패키지를 사용하여 오라클의 커서를 직접 반환시킨다.

-- 사진목록 가져오기
create or replace PACKAGE ref_photo_list_pkg AS
TYPE ref_type IS REF CURSOR;
PROCEDURE ref_photo_list_body
(
        p_bl_id IN NUMBER,
        p_start IN NUMBER,
        p_end IN NUMBER,
        v_result OUT ref_type
);
       
END;
/


--그리고 다음과 같이 위 패키지에 대한 본문을 만듭니다.

create or replace PACKAGE BODY ref_photo_list_pkg AS
PROCEDURE ref_photo_list_body
(
        p_bl_id IN NUMBER,
        p_start IN NUMBER,
        p_end IN NUMBER,
        v_result OUT ref_type
)
AS
begin
        OPEN v_result FOR
        select a.p_id, a.p_subject, a.p_userid, a.p_attach
                from (select rownum as num, p_id, p_subject, p_userid, p_attach from t_photo where bl_id=p_bl_id order by p_id desc) a
        where a.num>=p_start and a.num < p_end;
END;
END;
/

        public
        ArrayList<PhotoBean>
        selectList(
                           int bl_id,
                           int pageNo,
                           int pageSize
                           ) throws SQLException
        {
                if(!isOpen())
                        throw new SQLException("먼저 Connection을 Open하세요\n");
               
                int start = 1;
                if(pageNo > 1) {
                        start = pageSize*(pageNo-1)+1;
                }
                int end = start + pageSize;
               
               
                ArrayList<PhotoBean> listOfPhotoBean = new ArrayList<PhotoBean>();
               
                cstmt = conn.prepareCall(
                                "BEGIN" +
                                "   ref_photo_list_pkg.ref_photo_list_body(?,?,?,?);" +
                                "END;"
                                );
               
                cstmt.setInt(1, bl_id);
                cstmt.setInt(2, start);
                cstmt.setInt(3, end);
                cstmt.registerOutParameter(4, OracleTypes.CURSOR);
               
                cstmt.execute();
               
                OracleCallableStatement ocstmt = (OracleCallableStatement)cstmt;
                rs = (OracleResultSet)ocstmt.getCursor(4);
               
                PhotoBean photoBean = null;

                while(rs.next()) {
                        photoBean = new PhotoBean();
                       
                        photoBean.setP_id(rs.getInt(1));
                        photoBean.setP_subject(rs.getString(2));
                        photoBean.setP_userid(rs.getString(3));
                        photoBean.setP_attatch(rs.getString(4));
                       
                        System.out.println(photoBean);
                        listOfPhotoBean.add(photoBean);
                }
                return listOfPhotoBean;
        }

출처 [http://cherrykyun.tistory.com]