[JDBC] 2021.01.05. day_49 Procedure사용 _ update, delete, insert, select
2021. 1. 5. 14:11ㆍWeb_Back-end/JDBC
○ 주의할 점 * update, delete, select 사용하는 컬럼명과 in parameter명을 다르게 설정
※ insert
test_proc 테이블에 사원번호, 사원명, 연봉 정보를 입력
1. 프로시저 코딩
create or replace procedure insert_proc(empno in number, ename in varchar2, sal in number,
CNT OUT NUMBER, MSG OUT VARCHAR2)
IS
I_EMPNO NUMBER;
BEGIN
I_EMPNO := EMPNO;
IF EMPNO > 9999 THEN
I_EMPNO := 0;
END IF;
INSERT INTO TEST_PROC (EMPNO, ENAME, SAL, HIREDATE) VALUES (I_EMPNO,ENAME,SAL,SYSDATE);
CNT :=SQL%ROWCOUNT;
IF CNT = 1 THEN
COMMIT;
MSG := '정보가 추가되었습니다.';
END IF;
EXCEPTION
WHEN OTHERS THEN
MSG :=SQLERRM || '정보가 추가되지 않았습니다.';
END;
/2. 저장 : proc_insert.sql
3. 컴파일 : sql> @파일명.sql
show error : 컴파일 에러 확인
4. 실행 : 바인드 변수 선언
var cnt number;
var msg varchar2(300);5. 직접실행 : exec, execute 를 사용
exec insert_proc(1, '김현규', 3000, :cnt, :msg);6. 바인드 변수에 값 출력
※ update
test_proc 테이블에서 사원번호, 사원명, 연봉을 입력받아 해당사원의 사원명, 연봉을 변경
1. 프로시저 코딩
create or replace procedure update_proc(in_empno number, in_ename varchar2,
in_sal number, cnt out number, msg out varchar2)
is
begin
update test_proc
set ename = in_ename, sal = in_sal
where empno = in_empno;
--위의 쿼리문이 실행한 행의 수를 암시적커서(sql)의 속성(rowcount)을 사용하여 얻을 수 있다
cnt := sql%rowcount;
if cnt != 0 then
msg := in_empno || '번 사원 정보가 변경되었습니다';
commit;
else
msg := in_empno || '번 사원은 존재하지 않습니다';
end if;
exception
when others then
msg := sqlerrm || '문제가 발생';
end;
/2. 저장 : proc_update.sql
3. 컴파일 : sql> @파일명.sql
show error : 컴파일 에러 확인
select * from user_procedures;
4. 실행 : 바인드 변수 선언
var cnt number
var msg varchar2(300)
var5. 직접실행 : exec, execute 를 사용
execute update_proc(1, '현규', 3100, :cnt, :msg);6. 바인드 변수에 값 출력
print cnt;
print msg;
select * from test_proc;
※ delete
사원테이블에서 사원번호를 입력받아 해당 사원을 삭제
1. 프로시저 작성
create or replace procedure delete_proc(in_empno number,
cnt out number, msg out varchar2)
is
begin
delete from test_proc
where empno = in_empno;
--위의 쿼리문이 실행한 행의 수를 암시적커서(sql)의 속성(rowcount)을 사용하여 얻을 수 있다
cnt := sql%rowcount;
if cnt = 0 then
msg := in_empno || '번 사원은 존재하지 않습니다';
else
msg := in_empno || '번 사원정보가 삭제되었습니다';
end if;
exception
when others then
msg := sqlcode || '' || sqlerrm || '쿼리 실행 중 문제발생';
end;
/2. 저장 : proc_delete.sql
3. 컴파일 : sql> @파일명.sql
4. 바인드 변수 선언
var cnt number;
var msg varchar2(1000);5. 직접실행
exec delete_proc(1, :cnt, :msg);6. 바인드 변수에 값 출력
print cnt;
print msg;
select * from test_proc;
※ Select _ 개념잡기
| procedure를 사용한 조회 | |
| PL/SQL 에서는 쿼리문을 사용할 수 있는데, select 은 into절이 사용되며 한행 조회만 가능하다 | |
| 한 행 조회 | |
| 문법 | select 컬럼명,,, into 변수명,, from 테이블명 ... ... 한행이 아니면 예외가 발생 |
| 여러행 조회 |
|
| 조회 결과가 0~n 행 |
|
| cursor | cursor를 사용하여 조회 |
| PL/SQL 에서는 선언, 열기, 인출, 닫기의 생명주기를 가진 커서를 사용하여 여러 행을 조회한다 | |
| Procedure 에서는 조회 결과를 프로시저 내부에서 사용하는 것이 아닌 조회 결과를 프로시저 외부에서(예: java 에서) 사용하게 된다 |
|
| 커서의 제어권을SYS_REFCURSOR를 사용하여 반환해야한다 |
|
| SYS_REFCURSOR를 SQLPlus에서는 REFCURSOR를 사용하여 받고 | |
| Java에서는 ResultSet을 사용하여 받는다 | |
※ REFCURSOR 사용법
1. out parameter 로 커서 선언
create or replace procedure 프로시저명(변수명 out sys_refcursor)2. 프로시저 안에서, 커서를 열고 쿼리를 실행
(모든 select 쿼리문 사용가능)
open 커서명 for select,,,,,,,,;1. 사용 - SQLPlus에서 사용
- 바인드 변수 선언
var cur REFCURSOR
- 프로시저 호출
exec 프로시저명( :cur)
- 출력 : 조회 결과가 출력
print cur
2. Java 에서 사용
- java.sql.Types에서 제공하는 REF_CURSOR를 사용할 수 없다
--> oracle.jdbc.OracleTypes에서 제공하는 CURSOR를 사용해야한다
// 쿼리문 실행 객체 얻기
CallableStatement cstmt = con.prepareCall({call 프로시저명 ( ? ) });
// 바인드변수 값 할당
cstmt.registerOutParameter(1, OracleTypes.CURSOR);
// 쿼리 실행
cstmt.execute();
// out parameter에 설정된 값 받기
// ResultSet을 반환하는 method가 없기 때문에 (예: getResultSet())
// getObject() 를 casting하여 사용
ResultSet rs = (ResultSet)cstmt.getObject(1);
※ Select _ 생성해보기
1. 프로시저 작성
create or replace procedure select_proc(in_hiredate varchar2,
cur_emp out sys_refcursor, errm out varchar2)
is
begin
errm:= '문제가 없다';
open cur_emp for
select d.deptno, d.dname, d.loc, e.empno, e.ename,
to_char(e.hiredate, 'yyyy-mm') as hiredate
from dept d, emp e
where (e.deptno = d.deptno) and to_char(hiredate, 'yyyy') = in_hiredate;
exception
when others then
errm := sqlerrm || '문제발생';
end;
/
2. 저장 : proc_select.sql
3. 컴파일 : sql> @파일명.sql
4. 바인드 변수 선언
var cur refcursor;
var msg varchar2(300);5. 직접실행
exec select_proc('1987', :cur, :msg);
sqlplus에서는 print해야 값이 출력된다
6. 바인드 변수에 값 출력
print cur;
※ Java에서 procedur 실행해보기 _ CRUD
empno, ename, sal, hiredate 컬럼이 있는 test_proc 테이블을 이용해서 프로시저를 실행해보자
package day0105;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Types;
/**
* Procedure를 사용한 CRUD
*
* @author owner
*/
public class UseCallableStatementDAO {
private static UseCallableStatementDAO ucsDAO;
private UseCallableStatementDAO() {
}
public static UseCallableStatementDAO getInstance() {
if (ucsDAO == null) {
ucsDAO = new UseCallableStatementDAO();
}
return ucsDAO;
}
private Connection getConnection() throws SQLException {
Connection con = null;
try {
Class.forName("oracle.jdbc.OracleDriver");
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
String url = "jdbc:oracle:thin:@localhost:1521:orcl";
String id = "scott";
String pass = "tiger";
con = DriverManager.getConnection(url, id, pass);
return con;
}
/**
* procedure를 사용한 insert
*
* @throws SQLException
*/
public void insertTestProc() throws SQLException {
Connection con = null;
CallableStatement cstmt = null;
int empno = 1111;
String ename = "김현규";
int sal = 2800;
try {
// 1. 드라이버로딩
// 2. 커넥션 얻기
con = getConnection();
// 3. 쿼리문생성객체얻기
cstmt = con.prepareCall("{call insert_proc(?, ?, ?, ?, ?)}");
// 4. 바인드변수에 값넣기
// in parameter : procedure에 입력하는 값 설정
cstmt.setInt(1, empno);
cstmt.setString(2, ename);
cstmt.setInt(3, sal);
// out parameter : procedure가 처리한 결과 값을 받기 위한 설정
cstmt.registerOutParameter(4, Types.NUMERIC);
cstmt.registerOutParameter(5, Types.VARCHAR);
// 5. 쿼리 실행 : procedure 호출
cstmt.execute();
// 6. out parameter에 설정된 값 받기
int cnt = cstmt.getInt(4);
String msg = cstmt.getString(5);
System.out.println(cnt);
System.out.println(msg);
} finally {
// 7. 연결끊기
if (cstmt != null) {
cstmt.close();
}
if (con != null) {
con.close();
}
}
}
/**
* procedure를 사용한 update
*
* @return update 한 횟수
* @throws SQLException
*/
public int updateTestProc() throws SQLException {
int cnt = 0;
Connection con = null;
CallableStatement cstmt = null;
int empno = 1111;
String ename = "현규";
int sal = 3000;
try {
// 1. 드라이버로딩
// 2. 커넥션얻기
con = getConnection();
// 3. 쿼리문생성객체얻기
cstmt = con.prepareCall("{call update_proc(?, ?, ?, ?, ?)}");
// 4. 바인드변수에 값넣기
// in parameter
// in_empno, in_ename, in_sal
// : select, update, delete에는 컬럼명과 in parameter명을 다르게 설정해야한다
cstmt.setInt(1, empno);
cstmt.setString(2, ename);
cstmt.setInt(3, sal);
// out parameter
// cnt, msg
cstmt.registerOutParameter(4, Types.NUMERIC);
cstmt.registerOutParameter(5, Types.VARCHAR);
// 5. 쿼리 실행 : procedure 호출
cstmt.execute();
// 6. out parameter에 설정된 값 받기
cnt = cstmt.getInt(4);
String msg = cstmt.getString(5);
System.out.println(cnt + " / " + msg);
} finally {
// 7. 연결끊기
if (cstmt != null) {
cstmt.close();
}
if (con != null) {
con.close();
}
}
return cnt;
}
/**
* procedure를 사용한 delete
*
* @return 삭제한 횟수
* @throws SQLException
*/
public int deleteTestProc() throws SQLException {
int cnt = 0;
Connection con = null;
CallableStatement cstmt = null;
int empno = 1111;
try {
// 1. 드라이버로딩
// 2. 커넥션얻기
con = getConnection();
// 3. 쿼리문생성객체얻기
cstmt = con.prepareCall("{call delete_proc(?, ?, ?)}");
// 4. 바인드변수에 값넣기
// in parameter
// in_empno
// : select, update, delete에는 컬럼명과 in parameter명을 다르게 설정해야한다
cstmt.setInt(1, empno);
// out parameter
// cnt, msg
cstmt.registerOutParameter(2, Types.NUMERIC);
cstmt.registerOutParameter(3, Types.VARCHAR);
// 5. 쿼리 실행 : procedure 호출
cstmt.execute();
// 6. out parameter에 설정된 값 받기
cnt = cstmt.getInt(2);
String msg = cstmt.getString(3);
System.out.println(cnt + " / " + msg);
} finally {
// 7. 연결끊기
if (cstmt != null) {
cstmt.close();
}
if (con != null) {
con.close();
}
}
return cnt;
}
public static void main(String[] args) {
UseCallableStatementDAO ucsDAO = UseCallableStatementDAO.getInstance();
try {
// 1. insert
// ucsDAO.insertTestProc();
// 2. update
// int cnt = ucsDAO.updateTestProc();
// if(cnt != 0) {
// System.out.println("사원정보 변경 성공");
// }else {
// System.out.println("사원번호를 확인하세요");
// }
// 3. delete
int cnt = ucsDAO.deleteTestProc();
if (cnt != 0) {
System.out.println("사원정보 삭제 성공");
} else {
System.out.println("삭제할 사원번호를 확인하세요");
}
} catch (SQLException e) {
System.out.println("쿼리문 실행 중 문제발생");
e.printStackTrace();
}
}
}
▽▼ VO 클래스 ▼▽
package day0105;
public class ProcedureVO {
private int deptno, empno, sal;
private String dname, loc, ename, hiredate;
public ProcedureVO() {
super();
}
public ProcedureVO(int deptno, int empno, int sal, String dname, String loc, String ename, String hiredate) {
super();
this.deptno = deptno;
this.empno = empno;
this.sal = sal;
this.dname = dname;
this.loc = loc;
this.ename = ename;
this.hiredate = hiredate;
}
public int getDeptno() {
return deptno;
}
public void setDeptno(int deptno) {
this.deptno = deptno;
}
public int getEmpno() {
return empno;
}
public void setEmpno(int empno) {
this.empno = empno;
}
public int getSal() {
return sal;
}
public void setSal(int sal) {
this.sal = sal;
}
public String getDname() {
return dname;
}
public void setDname(String dname) {
this.dname = dname;
}
public String getLoc() {
return loc;
}
public void setLoc(String loc) {
this.loc = loc;
}
public String getEname() {
return ename;
}
public void setEname(String ename) {
this.ename = ename;
}
public String getHiredate() {
return hiredate;
}
public void setHiredate(String hiredate) {
this.hiredate = hiredate;
}
@Override
public String toString() {
return "ProcedureVO [deptno=" + deptno + ", empno=" + empno + ", sal=" + sal + ", dname=" + dname + ", loc="
+ loc + ", ename=" + ename + ", hiredate=" + hiredate + "]";
}
}
▽▼ 실행 클래스 ▼▽
package day0105;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.List;
/**
* Procedure를 사용한 CRUD
*
* @author owner
*/
public class UseCallableStatementDAO {
private static UseCallableStatementDAO ucsDAO;
private UseCallableStatementDAO() {
}
public static UseCallableStatementDAO getInstance() {
if (ucsDAO == null) {
ucsDAO = new UseCallableStatementDAO();
}
return ucsDAO;
}
private Connection getConnection() throws SQLException {
Connection con = null;
try {
Class.forName("oracle.jdbc.OracleDriver");
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
String url = "jdbc:oracle:thin:@localhost:1521:orcl";
String id = "scott";
String pass = "tiger";
con = DriverManager.getConnection(url, id, pass);
return con;
}
public List<ProcedureVO> selectProcedure(String hiredate) throws SQLException {
List<ProcedureVO> list = new ArrayList<ProcedureVO>();
Connection con = null;
CallableStatement cstmt = null;
ResultSet rs = null;
try {
// 1. 드라이버로딩
// 2. 커넥션얻기
con = getConnection();
// 3. 쿼리문생성객체얻기
cstmt = con.prepareCall("{call select_proc(?, ?, ?)}");
// 4. 바인드변수에 값넣기
// in parameter
cstmt.setString(1, hiredate);
// out parameter
// cstmt.registerOutParameter(2, Types.REF_CURSOR); // Java에서 제공하는 커서 ==> oracle에서는 사용할 수 없다
// SYS_REFCURSOR를 받기 위해 OracleTypes.CURSOR를 사용한다
cstmt.registerOutParameter(2, OracleTypes.CURSOR);
cstmt.registerOutParameter(3, Types.VARCHAR);
// 5. 쿼리 실행 : procedure 호출
cstmt.execute();
// 6. out parameter에 설정된 값 받기
rs = (ResultSet) cstmt.getObject(2);
String msg = cstmt.getString(3);
System.out.println(msg);
ProcedureVO pVO = null;
while (rs.next()) {// ResultSet 이 cursor의 제어권을 받는다
pVO = new ProcedureVO(rs.getInt("deptno"), rs.getInt("empno"),
rs.getString("dname"), rs.getString("loc"),
rs.getString("ename"), rs.getString("hiredate"));
list.add(pVO);
}
} finally {
// 7. 연결끊기
if (rs != null) {
rs.close();
}
if (cstmt != null) {
cstmt.close();
}
if (con != null) {
con.close();
}
}
return list;
}
public static void main(String[] args) {
UseCallableStatementDAO ucsDAO = UseCallableStatementDAO.getInstance();
try {
String hiredate ="1981";
List<ProcedureVO> list = ucsDAO.selectProcedure(hiredate);
System.out.println(hiredate + "년도 조회");
if(list.isEmpty()) {
System.out.println("해당 년도에 입사한 사원이 없습니다");
}else {
for(ProcedureVO pVO: list) {
System.out.println(pVO.getDeptno() + " / " + pVO.getDname() +
" / " + pVO.getLoc() + " / " + pVO.getEmpno() + " / " + pVO.getEname() +
" / " + pVO.getHiredate());
}
}
} catch (SQLException e) {
System.out.println("쿼리문 실행 중 문제발생");
e.printStackTrace();
}
}
}
'Web_Back-end > JDBC' 카테고리의 다른 글
| [JDBC] 2021.01.05 day_49 transaction처리 (0) | 2021.01.05 |
|---|---|
| [JDBC] 2021.01.05 day_49 CLOB 생성, java에서 사용 (0) | 2021.01.05 |
| [JDBC] 2021.01.04. day_48 CallableStatement, Procedure (0) | 2021.01.04 |
| [JDBC] 2021.01.04. day_48 ResultSetMetaData, java swing으로 view생성하고, 테이블의 컬럼 정보 조회하기 (0) | 2021.01.04 |
| [JDBC] 2020.12.30. day_47 OWASP , SQL Injection, SQL Injection 방어하기 (0) | 2020.12.30 |