국비지원 37일차 - CallableStatement, NetBeans, SQL PACKAGE, Trigger

CallableStatement
JDBC, PROCEDURE 연동

프로시저를 호출하는 객체 => CallableStatement객체
테스트 하기 위해서 먼저 DB에서 memo테이블에 글을 등록하는 프로시저를 작성

--작성자, 메모내용을 인파라미터로 받아 MEMO테이블에 INSERT하는 프로시저 작성

CREATE OR REPLACE PROCEDURE MEMO_ADD
(PNAME IN MEMO.NAME%TYPE, PMSG IN MEMO.MSG%TYPE)
IS
BEGIN
  INSERT INTO MEMO(IDX,NAME,MSG,WDATE)
  VALUES(MEMO_SEQ.NEXTVAL,PNAME,PMSG,SYSDATE);
END;
/

JDBC로 PROCEDURE 실행

package jdbc.day04;

import java.sql.*;

import jdbc.util.DBUtil;

public class CallableStatementTest {
	public static void main(String[] args) throws Exception
	{
		if(args.length!=2) {
			System.out.println("명령줄 인수 2개를 입력하세요");
			return;
		}
		Connection con=DBUtil.getCon();
		//in parameter가 있을 경우
		String sql="{call MEMO_ADD(?,?)}";
		CallableStatement cstmt = con.prepareCall(sql);
		cstmt.setString(1, args[0]);
		cstmt.setString(2, args[1]);
		boolean b=cstmt.execute();
		cstmt.close();
		con.close();
	}
}

--메모 글번호를 인파라미터로 받아 해당 글을 삭제하는 프로시저를 작성 후
자바에서 삭제할 글의 글번호를 Scanner로 입력받아 삭제

PROCEDURE

CREATE OR REPLACE PROCEDURE MEMO_DELETE(PIDX IN MEMO.IDX%TYPE)
IS
BEGIN
  DELETE FROM MEMO WHERE IDX=PIDX;
END;
/

JAVA

package jdbc.day04;

import java.sql.*;
import java.util.Scanner;

import jdbc.util.DBUtil;

public class CSTMT {
	public static void main(String[] args) throws Exception{
		Scanner sc= new Scanner(System.in);
		System.out.println("삭제할 글 번호");
		int idx = sc.nextInt();
		
		Connection con=DBUtil.getCon();
		String sql="{call MEMO_DELETE(?)}";
		CallableStatement cstmt = con.prepareCall(sql);
		cstmt.setInt(1, idx);
		boolean b = cstmt.execute();
		if(b==false) {
			System.out.println("없음");
		}else {
			System.out.println("성공");
		}
		cstmt.close();
		con.close();
	}
}

--부서번호를 인파라티터로 받아들여 해당 부서의 사번, 사원명, 부서번호, 부서명, 업무, 근무지를
가져오는 프로시저를 작성 후 자바와 연동해보자

PROCEDURE

CREATE OR REPLACE PROCEDURE EMP_LIST
(PDNO IN NUMBER, EMPCR OUT SYS_REFCURSOR)
IS
BEGIN
  OPEN empcr for
    select empno, ename, d.deptno, dname, job, loc
    from emp e join dept d
    on e.deptno=d.deptno and e.deptno=pdno;
END;
/

JAVA

package jdbc.day04;

import java.sql.*;
import jdbc.util.DBUtil;

public class CSTMT2 {
	public static void main(String[] args) throws Exception
	{
		if(args.length!=1) {
			System.out.println("명령줄 인수로 부서번호를 입력");
			return;
		}
		Connection con = DBUtil.getCon();
		String sql="{call EMP_LIST(?,?)}";
		CallableStatement cstmt = con.prepareCall(sql);
		//1. 인파라미터 셋팅
		cstmt.setInt(1, Integer.parseInt(args[0]));
		//2. 아웃파라미터 등록
		cstmt.registerOutParameter(2, oracle.jdbc.OracleTypes.CURSOR);
		cstmt.execute();//프로시저 호출 실행
		
		ResultSet rs = (ResultSet)cstmt.getObject(2);
		while(rs.next()) {
			int empno=rs.getInt("empno");
			String ename=rs.getString("ename");
			String job = rs.getString("job");
			int deptno =rs.getInt("deptno");
			String dname = rs.getString("dname");
			String loc = rs.getString("loc");
			System.out.println(empno+" "+ename+" "+job+" "+deptno+" "+dname+" "+loc);
		}
		rs.close();
		cstmt.close();
		con.close();
	}
}

ㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡ

NetBeans

UI를 DRAG AND DROP 형식으로 만들면 Source Code가 작성된다.

ㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡㅡ

SQL PACKAGE
- 패키지는 오라클 데이터베이스에 저장되어 있는 서로 관련있는 PL/SQL 프로시져와 함수들의 집합
- 패키지는 선언부와 본문 두 부분으로 나누어진다.

--패키지 선언
CREATE OR REPLACE PACKAGE MYEMP AS
PROCEDURE allEmp;
PROCEDURE allSal;
END MYEMP;
/

--패키지 바디 구성
CREATE OR REPLACE PACKAGE BODY MYEMP AS
PROCEDURE allEmp
IS
  CURSOR empCr is
  SELECT empno, ename, job from emp order by 1 asc;
BEGIN
  FOR K IN empCr LOOP
    DBMS_OUTPUT.PUT_LINE('사번: '||K.empno);
    DBMS_OUTPUT.PUT_LINE('사원명: '||K.ename);
    DBMS_OUTPUT.PUT_LINE('사번: '||K.job);
  END LOOP;
END allEmp;
PROCEDURE allSal
IS
BEGIN
  --평균 급여, 최대급여, 최소급여 업무별
  for k in (select job, round(avg(sal),2) av, max(sal) mx, min(sal) mn from emp group by job) loop
    dbms_output.put_line(k.job||' '||k.av||' '||k.mx||' '||k.mn);
  end loop;
END allSal;
END MYEMP;
/

set serveroutput on

execute myemp.allemp;
execute myemp.allsal;

Trigger
- 트리거란 INSERT, UPDATE, DELETE문이 TABLE에 대해 행해질 때 묵시적으로 수행되는 PROCEDURE이다.
- Tirgger는 TABLE과는 별도로 DATABASE에 저장된다.
- Trigger는 VIEW에 대해서가 아니라 TABLE에 관해서만 정의할 수 있다.

--구문----------------------------------------
create [or replace] trigger triger_name
{BEFORE | AFTER} trigger_event [OF column1,...]
ON table_name
[FOR EACH ROW [WHEN trigger_condition]]
trigger_body;

--DEPT테이블이 수정될 때 로그 기록을 남기는 트리거

CREATE OR REPLACE TRIGGER TRG_TEST
BEFORE UPDATE ON DEPT 
FOR EACH ROW
BEGIN
  DBMS_OUTPUT.PUT_LINE('변경 전 컬럼값: '||:OLD.DNAME);
  DBMS_OUTPUT.PUT_LINE('변경 전 컬럼값: '||:NEW.DNAME);
END;
/

UPDATE DEPT SET DNAME='회계부서' WHERE DEPTNO=20;
rollback;

--트리거 실습
1) 입고 테이블에 상품이 입고될 경우 상품 테이블의 상품 보유 수량이 자동으로 변경
2) 입고 테이블의 상품 수량을 수정하면 상품 테이블의 수량도 자동으로 조절되도록 트리거를 작성

CREATE TABLE 상품(
  PCODE VARCHAR2(10) PRIMARY KEY, --상품코드
  PNAME VARCHAR2(20) NOT NULL, --상품명
  PCOMPANY VARCHAR2(20), --제조사
  PRICE NUMBER(8), --가격
  PQTY NUMBER(6) DEFAULT 0 --보유 수량
);


CREATE SEQUENCE 상품_SEQ
START WITH 1
INCREMENT BY 1
NOCACHE;


INSERT INTO 상품
VALUES('A'||상품_SEQ.NEXTVAL,'키보드','삼성',130000,20);
INSERT INTO 상품
VALUES('A'||상품_SEQ.NEXTVAL,'오토바이','BMW',1300000,10);
commit;


CREATE TABLE 입고(
  IDX NUMBER(5) PRIMARY KEY, --입고번호
  PCODE_FK VARCHAR2(10) REFERENCES 상품(PCODE), --상품코드
  INDATE DATE DEFAULT SYSDATE, --입고일
  IQTY NUMBER(6), --입고 수량
  IPRICE NUMBER(8) --입고가
);


CREATE SEQUENCE 입고_SEQ
START WITH 1
INCREMENT BY 1
NOCACHE;

--입고 테이블에 상품이 입고될 경우 상품 테이블의 상품 보유 수량이 자동으로 변경

CREATE OR REPLACE TRIGGER MYTRG
AFTER INSERT ON 입고
FOR EACH ROW
BEGIN
  UPDATE 상품 SET PQTY=PQTY+:NEW.IQTY WHERE PCODE=:NEW.PCODE_FK;
  DBMS_OUTPUT.PUT_LINE(:NEW.PCODE_FK||' '||:NEW.IQTY);
END;
/

select * from 상품;
select * from 입고;

INSERT INTO 입고(IDX,PCODE_FK,IQTY,IPRICE)
VALUES(입고_SEQ.NEXTVAL,'A2',5,120000);

--입고 테이블의 상품 수량을 수정하면 상품 테이블의 수량도 자동으로 조절되도록 트리거를 작성

CREATE OR REPLACE TRIGGER MYTRG2
AFTER UPDATE ON 입고
FOR EACH ROW
BEGIN
  UPDATE 상품 SET PQTY=PQTY-(:OLD.IQTY-:NEW.IQTY) WHERE PCODE=:NEW.PCODE_FK;
  DBMS_OUTPUT.PUT_LINE(:NEW.PCODE_FK||' '||:NEW.IQTY);
END;
/

--트리거 삭제
DROP TRIGGER MYTRG

--트리거 비활성화 / 활성화
ALTER TRIGGER MYTRG DISABLE
ALTER TRIGGER MYTRG ENABLE

--트리거  데이터 사전 조회
SELECT * FROM USER_TRIGGERS WHERE TRIGGER_NAME='MYTRG';