[JDBC] days05- CollableStatement

• emp테이블에 사원을  추가하는 up_insertemp 저장 프로시저를 선언
• CallableStatement를 사용해서 선언

up_insertemp 

오라클

create or replace procedure up_insertemp
(
PEMPNO    NUMBER,
PENAME   VARCHAR2 := null,
PJOB      VARCHAR2 := null, 
PMGR      NUMBER := null,    
PHIREDATE   DATE := null,        
PSAL   NUMBER := null,
PCOMM    NUMBER := null,
PDEPTNO   NUMBER:= null  
)
is 
begin
    insert into emp(empno, ename, job,mgr,hiredate,sal,comm,deptno)
    values (pempno, pename, pjob,pmgr,phiredate,psal,pcomm,pdeptno);
    commit;
--exception
end;

 

자바에서 프로시저를 이용하여 사원추가 

package days05;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.Date;
import java.sql.SQLException;

import com.util.DBconn;

public class Ex03 {
public static void main(String[] args) {
	int empno=9999;
	String ename="admin";
	int mgr=7369;
	int deptno=20;
	Date hiredate=new Date(1981,2,20);
	
	String sql="{call up_insertemp (pempno=>? ,pename=>?, pmgr=>?, pdeptno=>?, phiredate=>?)}";
	Connection conn=null;
	CallableStatement cstmt=null;
	int rowCount=0;

	conn=DBconn.getConnection();
	
	try {
		cstmt=conn.prepareCall(sql);
		cstmt.setInt(1, empno);
		cstmt.setString(2, ename);
		cstmt.setInt(3, mgr);
		cstmt.setInt(4, deptno);
		cstmt.setDate(5, hiredate);
		rowCount=cstmt.executeUpdate();
		if(rowCount==1) {
			System.out.println("사원추가완료");
		}
	} catch (SQLException e) {
		e.printStackTrace();
	}finally {
		try {
			cstmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	DBconn.close();
	System.out.println("-end-");
}
}

 

 

 

up_updateDept

원래 부서명은 유지하고 지역만 바꾸겠다.

5번을 수정 

 

오라클에서 프로시저 생성

create or replace procedure up_updateDept
(
pdeptno  dept.deptno%type,
pdname  dept.dname%type := null,
ploc  dept.loc%type := null
)
is 
vdname  dept.dname%type;
vloc  dept.loc%type;
begin
--안바꿀수도있으니까 저장해놓기 
    select dname,loc into vdname,vloc
    from dept
    where deptno=pdeptno;
    
    update dept
    set dname=nvl(pdname,vdname), loc=nvl(ploc,vloc)
    where deptno=pdeptno;
    commit;
--exception
end;

--Procedure UP_UPDATEDEPT이(가) 컴파일되었습니다.

 

 

 

자바

package days05;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.SQLException;

import com.util.DBconn;

public class Ex04 {
public static void main(String[] args) {
	String sql= "{call UP_UPDATEDEPT(pdeptno=>?, ploc=>? )}"; //원래 부서명은 유지하고 지역만 바꾸겠다. 
	//String sql= "{call up_updateDept(pdeptno=?,pdname=>?, ploc=>?}";
	
	Connection conn=null;
	CallableStatement cstmt=null;
	int rowCount=0;

	conn=DBconn.getConnection();

	try {
		cstmt=conn.prepareCall(sql);
		cstmt.setInt(1, 90);
		cstmt.setString(2, "pohang");

		rowCount=cstmt.executeUpdate();
		if(rowCount==1) {
			System.out.println("사원추가완료");
		}
	} catch (SQLException e) {
		e.printStackTrace();
	}finally {
		try {
			cstmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	DBconn.close();
	System.out.println("-end-");
}}

 

 

에러 - 인덱스에서 누락된 in 또는 out 매개변수 :2

cstmt.setInt(1, 90);
cstmt.setString(1, "pohang"); 

이렇게 잘못 썼었다. ㅎ