10/24 27-12 (UpdateProc.CallableStatement)

수정용 오라클 함수

 create or replace PROCEDURE sp_UDT_member
(
        --매개변수 정의!
        id IN member.username%TYPE,
        pass IN member.password%TYPE,
        name_ IN member.name%TYPE,
        
        rtval OUT NCHAR
)
IS
BEGIN
   ------수정시 모두 변수로 처리됨, 그래서 컬럼명과 변수 다르게 해야함
        UPDATE member SET password=pass,name=name_
        WHERE username= id;

        if sql%found THEN
                rtval := '입력성공';
                COMMIT;
        else  -- 존재하지 않은 id로 수정 시
            rtval := 'ID가 존재하지 않아요';        
        END iF;

       EXCEPTION
   WHEN OTHERS THEN
ROLLBACK;-- 실행이 1개밖에 없어 의미 없지만 그래도 써놔
rtval := '입력실패: 입력값이 너무 길어요';
END;

public class UpdateProc extends IConnectImpl {

	public UpdateProc() {
		connect(ORACLE_URL,"KOSMO","KOSMO");		
	}

	@Override
	public void execute() throws Exception {
    	csmt = conn.prepareCall("{call sp_UDT_member (?,?,?,?)}");
        //인파라
		csmt.setString(1,getValue("수정 할 아이디"));
		csmt.setString(2,getValue("비밀번호"));
		csmt.setString(3,getValue("이름"));
        //아웃파라
        csmt.registerOutParameter(4,Types.NCHAR);
        System.out.println(csmt.execute());
		System.out.println("프로시저의 아웃 파라미터 값:"+csmt.getString(4));
		close();      
    }
	public static void main(String[] args) throws Exception {
		 new UpdateProc().execute();
	}
}