[배운내용정리] 1230 자바 국비교육 _ Oracle + Jdbc

2020년 12월 30 9시 ~ 15시 30분 zoom으로 수업 진행

 

---

ORACLE PL/SQL

 

FUNCTION

 내부에서 계산된 결과를 반환하는 객체

[선언] CREATE [OR REPLACE] FUNCTION 함수명(매개변수 [모드] 자료형) RETURN 자료형 IS     --변수; BEGIN     --실행코드; RETURN 결과데이터; END; /

 

[실행] VARIABLE 변수명 변수자료형; EXEC :변수명 := 함수명(전달값, ...);

 

 

TRIGGER

 특정 테이블이나 뷰가 DML을 통해 데이터 변환이 일어날때 그 시점을 감지하여 자동으로 동작하는 스크립트

 즉, 사용자가 직접 DML을 수행하는 것이 아니라 데이터베이스에서 자동으로 처리하는 로직을 구현하는 객체

 

---

 

--입력한 사번에 해당하는 직원의 보너스 급여를 계산
CREATE OR REPLACE FUNCTION BONUS_CALC(V_EMP_ID IN EMPLOYEE.EMP_ID%TYPE)
RETURN NUMBER
IS
    V_SAL EMPLOYEE.SALARY%TYPE;
    V_BONUS EMPLOYEE.BONUS%TYPE;
    RESULT NUMBER;
BEGIN
    SELECT SALARY, NVL(BONUS, 0)
    INTO V_SAL, V_BONUS
    FROM EMPLOYEE
    WHERE EMP_ID = V_EMP_ID;
    
    RESULT := V_SAL * V_BONUS + V_SAL;
    RETURN RESULT;
END;
/

--VARIABLE 약자 VAR
VAR RESULT_SAL NUMBER;
EXEC :RESULT_SAL := BONUS_CALC('&사번');

PRINT RESULT_SAL;
SET AUTOPRINT ON;


--
SELECT EMP_NAME, SALARY, BONUS, BONUS_CALC(EMP_ID)
FROM EMPLOYEE
WHERE BONUS_CALC(EMP_ID) > 3000000;


--제품 정보 테이블

CREATE TABLE PRODUCT(
    PCODE NUMBER PRIMARY KEY,
    PNAME VARCHAR2(30),
    BNAME VARCHAR2(30),
    PRICE NUMBER,
    STOCK NUMBER DEFAULT 0
);

--제품 입,출고 내역 테이블
CREATE TABLE PRODUCT_DETAIL(
    DCODE NUMBER PRIMARY KEY,
    PCODE NUMBER NOT NULL,
    PDATE DATE DEFAULT SYSDATE,
    AMOUNT NUMBER,
    STATUS CHAR(6) CHECK(STATUS IN('입고', '출고')),
    CONSTRAINT FK_PRODUCT FOREIGN KEY(PCODE)
    REFERENCES PRODUCT
);

SELECT * FROM PRODUCT;
SELECT * FROM PRODUCT_DETAIL;


--시퀀스 생성
CREATE SEQUENCE SEQ_PRODUCT;
CREATE SEQUENCE SEQ_DETAIL;



--제품 등록
INSERT INTO PRODUCT
VALUES(SEQ_PRODUCT.NEXTVAL, '노트북', 'LG', 2000000, DEFAULT);

INSERT INTO PRODUCT
VALUES(SEQ_PRODUCT.NEXTVAL, 'TV', 'SAMSUNG', 4000000, DEFAULT);

INSERT INTO PRODUCT
VALUES(SEQ_PRODUCT.NEXTVAL, '휴대폰', 'APPLE', 1000000, DEFAULT);

INSERT INTO PRODUCT
VALUES(SEQ_PRODUCT.NEXTVAL, '볼펜', '모나미', 2000, DEFAULT);

SELECT * FROM PRODUCT;

--제품 입,출고 관련 재고 증감 트리거
CREATE OR REPLACE TRIGGER TRG
AFTER INSERT ON PRODUCT_DETAIL
FOR EACH ROW
BEGIN
    IF:NEW.STATUS = '입고'
    THEN 
        UPDATE PRODUCT SET STOCK= STOCK + :NEW.AMOUNT
        WHERE PCODE = :NEW.PCODE;
    END IF;
    
    IF:NEW.STATUS = '출고' 
    THEN
        UPDATE PRODUCT SET STOCK = STOCK - :NEW.AMOUNT
        WHERE PCODE = :NEW.PCODE;
    END IF;
END;
/

INSERT INTO PRODUCT_DETAIL
VALUES(SEQ_DETAIL.NEXTVAL, 1, SYSDATE, 3, '입고');

COMMIT;

SELECT * FROM PRODUCT;
SELECT * FROM PRODUCT_DETAIL;

--입고
INSERT INTO PRODUCT_DETAIL
VALUES(SEQ_DETAIL.NEXTVAL, 2, SYSDATE, 100, '입고');

INSERT INTO PRODUCT_DETAIL
VALUES(SEQ_DETAIL.NEXTVAL, 3, SYSDATE, 200, '입고');

INSERT INTO PRODUCT_DETAIL
VALUES(SEQ_DETAIL.NEXTVAL, 4, SYSDATE, 1000, '입고');

SELECT * FROM PRODUCT;
SELECT * FROM PRODUCT_DETAIL;


--출고
INSERT INTO PRODUCT_DETAIL
VALUES(SEQ_DETAIL.NEXTVAL, 1, SYSDATE, 1, '출고');

INSERT INTO PRODUCT_DETAIL
VALUES(SEQ_DETAIL.NEXTVAL, 2, SYSDATE, 50, '출고');

INSERT INTO PRODUCT_DETAIL
VALUES(SEQ_DETAIL.NEXTVAL, 3, SYSDATE, 33, '출고');

INSERT INTO PRODUCT_DETAIL
VALUES(SEQ_DETAIL.NEXTVAL, 4, SYSDATE, 532, '출고');

SELECT * FROM PRODUCT;
SELECT * FROM PRODUCT_DETAIL;

 

---

Java Jdbc 시작

package com.test01;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class DBTest01 {

	public static void main(String[] args) throws ClassNotFoundException, SQLException {
		//1. 드라이버 등록
		Class.forName("oracle.jdbc.driver.OracleDriver");
		
		//2. DB 연결
		Connection con = DriverManager.getConnection(
						"jdbc:oracle:thin:@localhost:1521:xe", "KH", "KH");
		
		//3. SQL 실행
		Statement stmt = con.createStatement();
		ResultSet rs = stmt.executeQuery("SELECT * FROM EMP"); 
		
		//4. 결과 리턴
		while(rs.next()) {
			System.out.println(rs.getInt(1) + " " + rs.getString(2) + " " + 
								rs.getInt("SAL"));
		}
		
		//5. 종료
		rs.close();
		stmt.close();
		con.close();
		

	}

}