국비 지원 개발자 과정_Day34

PL/SQL표준 - 프로시저, 함수, 트리거 - 프로그래밍요소

SELECT … INTO문은 PL/SQL문에서만 지원함

단일행만 사용 가능함(에러발생주의)

다중행이 나오는 경우 → LOOP문, CURSOR필요

/* Formatted on 2023-01-12 오후 4:28:32 (QP5 v5.215.12089.38647) */
--5번 연습문제 문제풀이
--t_worktime테이블의 작업시간 누적 합계를 구하시오.
  SELECT a.workcd_vc, a.time_nu, SUM (b.time_nu) sum_time
    FROM (SELECT seq_vc,
                 workcd_vc,
                 time_nu,
                 ROWNUM num
            FROM t_worktime) A,
         (SELECT seq_vc,
                 workcd_vc,
                 time_nu,
                 ROWNUM num
            FROM t_worktime) B
   WHERE A.num <= B.num
GROUP BY A.seq_vc, A.workcd_vc, A.time_nu
ORDER BY sum_time;

--SUM() OVER()라는 함수를 이용
SELECT workcd_vc, time_nu, SUM (time_nu) OVER (ORDER BY seq_vc) AS "SUM_TIME"
  FROM t_worktime;

--트리거(Trigger)
--한 테이블에 날짜로 선언된 컬럼이 있다고 가정했을 때 이 컬럼에
--데이터는 항상 토요일과 일요일만 입력되어야 한다고 했을 때
--원천적으로 막을 수 있는 방법이 있다.
--
--트리거를 이용해서 UPDATE, INSERT시에 해당 컬럼의 데이터를 checking하면 된다.
--또 Insert, Delete, Update시에 항상 특정 테이블에 작업실행에 대한 history가
--필요할 경우에도 Trigger를 사용하면, 별도의 작업없이도 Trigger에서 이를 실행 할 수 있다.

--트리거는 예약어이고 오브젝트로 취급됨
--트리거 다음에는 이름이 온다
CREATE OR REPLACE TRIGGER check_salary         --선언문 트리거(or replace 재정의, 재컴파일)
   --on다음에 오는 건 테이블명(emp 테이블에서 라는 의미)
   --emp에 update 또는 delete, insert 하기 전에라는 의미
   --반대일때는 before 대신에 after를 사용함.
   BEFORE UPDATE OR DELETE OR INSERT
   ON emp
BEGIN
   --실행문이 온다(변수 초기화, 조건문, 반복문, DML문)
   IF (TO_CHAR (SYSDATE, 'DY') IN ('토', '일', '목'))
   THEN
      raise_application_error (-20500,
                               '주말에는 변경할 수 없습니다.');
   END IF;
END;

SELECT func_crate ('20010908') FROM DUAL;

SELECT deptno, ename
  FROM emp
 WHERE deptno = 10 OR deptno = 30;

SELECT deptno, ename
  FROM emp
 WHERE deptno IN (10, 30);

SELECT * FROM emp;

--삭제하려하면 트리거가 막음 -> 주말에는 변결할 수 없습니다.
DELETE FROM emp
      WHERE empno = 7369;

ROLLBACK;

--아이디 비밀번호 입력하면 닉네임 반환
--OR REPLACE을 쓰지 않으면 재정의 불가(삭제하고 다시만들어야함)
--프로시저, 트리거, 펑션 후 이름과 파라미터가 옴
--프로시저 파라미터에는 IN, OUT, INOUT속성 세가지가 올 수 있다
--IN속성 - 화면에서 입력받은 값을 읽기
--OUT속성 - 오라클 서버에서 처리된 결과를 화면으로 내보낼 때 사용
CREATE OR REPLACE PROCEDURE KIWI.proc_login (u_id     IN     VARCHAR2,
                                             u_pw     IN     VARCHAR2,
                                             r_name      OUT VARCHAR2)
IS
   --선언문
   --자바에서는 타입먼저 이름나중, 오라클에선 이름먼저 타입나중
   r_status   NUMBER;                                           --변수선언, 숫자를 담음
BEGIN
   --   r_status = 0;
   --실행문 - 반복문, 조건문, 1 또는 -1을 r_status에 담아줌
   SELECT NVL ( (SELECT 1
                   FROM MEMBER
                  WHERE mem_id = u_id),
               -1)
     INTO r_status
     FROM DUAL;

   IF r_status = 1
   THEN
      SELECT NVL ( (SELECT mem_name
                      FROM MEMBER
                     WHERE mem_id = u_id AND mem_pw = u_pw),
                  '비번이 틀립니다.')
        INTO r_name
        FROM DUAL;
   ELSIF r_status = -1
   THEN
      r_name := '아이디가 존재하지 않습니다.';
   END IF;
END;

--아래와같이 복제하는 것은 제약조건은 미포함
CREATE TABLE dept_copy
AS
   SELECT * FROM dept;

--선언문 create, or replace 수정가능
--before가 아니라 after, dept에 변화가 생긴다면
CREATE OR REPLACE TRIGGER trg_table_copy
   AFTER INSERT OR UPDATE OR DELETE
   ON dept
   FOR EACH ROW
BEGIN
   IF INSERTING
   THEN
      INSERT INTO dept_copy (deptno, dname, loc)
           VALUES (:new.deptno, :new.dname, :new.loc);
   ELSIF UPDATING
   THEN
      UPDATE dept_copy
         SET dname = :new.dname, loc = :new.loc
       WHERE dept_copy.deptno = :old.deptno;
   ELSIF DELETING
   THEN
      DELETE FROM dept_copy
            WHERE dept_copy.deptno = :old.deptno;
   END IF;
END;

SELECT * FROM dept;

SELECT * FROM dept_copy;

--dept에 insert하지만, dept_copy에도 들어감(트리거때문에)
INSERT INTO dept
     VALUES (66, '전산과', '서울');

--마찬가지로 dept뿐만 아니라 dept_copy도 바뀜
UPDATE dept
   SET loc = '대전'
 WHERE deptno = 66;

DELETE FROM dept
      WHERE deptno = 66;

  SELECT dept_code,
         SUM (salary) AS "합계",
         FLOOR (AVG (salary)) AS "평균",
         COUNT (*) AS "인원수"
    FROM temp
GROUP BY dept_code
  HAVING AVG (salary) > 50000000
ORDER BY dept_code ASC;

SELECT ename
  FROM emp
 WHERE empno = 7566;

--묵시적 형변환
SELECT ename
  FROM emp
 WHERE empno = '7566';

SELECT ename
  FROM emp
 WHERE empno = TO_NUMBER ('7566');

SELECT TO_CHAR (SYSDATE, 'YYYY-MM-DD') FROM DUAL;

SELECT '2000/10/23' FROM DUAL;

--문자열이라 +1 불가
SELECT '2000/10/23' + 1 FROM DUAL;

SELECT SYSDATE, SYSDATE + 1, SYSDATE - 1 FROM DUAL;

--날짜로 바꾸고 +1 가능
SELECT TO_DATE ('2000/10/23') + 1 FROM DUAL;

--트리거의 변경
--트리거의 상태를 비활성화 또는 활성화 할 수 있다.
ALTER TRIGGER trg_table_copy DISABLE;

SELECT * FROM dept;

SELECT * FROM dept_copy;

--비활성화 상태에서 추가하면 dept에만 들어감
INSERT INTO dept
     VALUES (66, '전산과', '서울');

UPDATE dept
   SET loc = '대전'
 WHERE deptno = 66;

DELETE FROM dept
      WHERE deptno = 66;

--self조인 문제풀이
--temp와 tdept를 이용하여 다음 컬럼을 보여주는 SQL을 만들어 보자.
--상위부서가 'CA0001'인 부서에 소속된 직원을 1.사번, 2.성명, 3.부서코드
--4.부서명, 5.상위부서코드, 6.상위부서명, 7.상위부서장코드, 8.상위부서장성명
--순서로 보여주면 된다.
SELECT * FROM temp;

SELECT * FROM tdept;

SELECT a.emp_id,
       a.emp_name,
       b.dept_code,
       b.dept_name
  FROM temp a, tdept b
 WHERE a.dept_code = b.dept_code;

SELECT a.emp_id,
       a.emp_name,
       b.dept_code,
       b.dept_name,
       c.dept_code AS "상위부서코드",
       c.dept_name AS "상위부서명"
  FROM temp a, tdept b, tdept c
 WHERE a.dept_code = b.dept_code AND b.parent_dept = c.dept_code;

SELECT a.emp_id,
       a.emp_name,
       b.dept_code,
       b.dept_name,
       c.dept_code AS "상위부서코드",
       c.dept_name AS "상위부서명",
       d.emp_id AS "부서장아이디",
       d.emp_name AS "부서장이름"
  FROM temp a,
       tdept b,
       tdept c,
       temp d
 WHERE     a.dept_code = b.dept_code
       AND b.parent_dept = c.dept_code
       AND d.emp_id = c.boss_id
       AND c.dept_code = 'CA0001';

SELECT *
  FROM tdept a, temp b
 WHERE a.boss_id = b.emp_id;

SELECT b.emp_name
  FROM tdept a, temp b
 WHERE a.boss_id = b.emp_id AND a.dept_code = 'CA0001';

SELECT b.dept_code, b.dept_name
  FROM tdept a, tdept b
 WHERE a.parent_dept = b.dept_code;

 

package dev_java.util;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.Statement;
import java.sql.ResultSet;

public class DBConnectionMgr {
  public static final String _DRIVER = "oracle.jdbc.driver.OracleDriver";
  public static final String _URL = "jdbc:oracle:thin:@192.168.10.71:1521:orcl11";
  public static String _USER = null;
  public static String _PW = null;

  public DBConnectionMgr() {
  }

  // 파라미터가 있는 생성자가 하나라도 있으면 디폴트 생성자 제공 안 됨
  public DBConnectionMgr(String user, String pw) {
    // static으로 선언된 변수는 this나 super같은 예약어 사용 불가함
    // this에대한 어려움으로 리액트훅(함수형 프로그래밍, 자바: 람다식, 익명클래스, 내부클래스 컨벤션과 동일) 새로운 방식 제안
    // 웹브라우저에서 this가 문제인 이유 - 캡쳐링, 버블링효과
    _USER = user;
    _PW = pw;
  }

  public Connection getConnection() {
    Connection con = null;
    try {
      Class.forName(_DRIVER);
      con = DriverManager.getConnection(_URL, _USER, _PW);
    } catch (ClassNotFoundException ce) {
      System.out.println("드라이버 클래스를 찾을 수 없습니다.");
    } catch (Exception e) { // 멀티 블럭 작성 시 범위가 넓은 Exception을 바깥쪽에 적기
      System.out.println("오라클 서버와 연결 실패");
    }
    return con;
  }

  public Connection getConnection(String user, String pw) {
    _USER = user;
    _PW = pw;
    Connection con = null;
    try {
      Class.forName(_DRIVER);
      con = DriverManager.getConnection(_URL, _USER, _PW);
    } catch (ClassNotFoundException ce) {
      System.out.println("드라이버 클래스를 찾을 수 없습니다.");
    } catch (Exception e) { // 멀티 블럭 작성 시 범위가 넓은 Exception을 바깥쪽에 적기
      System.out.println("오라클 서버와 연결 실패");
    }
    return con;
  }

  // 사용한 자원을 반납하는 코드는 명시적으로 하는 것을 원칙으로 함
  // 반납하는 순서는 생성된 역순으로 진행할 것 - 자바튜닝팀 지적사항
  // 사용한 자원 반납하기 - INSERT, UPDATE, DELETE
  public void freeConnection(Connection con, Statement stmt) {
    if (stmt != null) {
      try {
        stmt.close();
      } catch (Exception e) {
        // TODO: handle exception
      }
    }
    if (con != null) {
      try {
        con.close();
      } catch (Exception e) {
        // TODO: handle exception
      }
    }
  } // end of freeConnection

  public void freeConnection(Connection con, PreparedStatement pstmt) {
    if (pstmt != null) {
      try {
        pstmt.close();
      } catch (Exception e) {
        // TODO: handle exception
      }
    }
    if (con != null) {
      try {
        con.close();
      } catch (Exception e) {
        // TODO: handle exception
      }
    }
  } // end of freeConnection

  // 오라클 서버와 연계에 필요한 객체
  // 사용 후에 반드시 자원 반납할 것(명시적으로)
  public void freeConnection(Connection con, CallableStatement cstmt) {
    if (cstmt != null) {
      try {
        cstmt.close();
      } catch (Exception e) {
        // TODO: handle exception
      }
    }
    if (con != null) {
      try {
        con.close();
      } catch (Exception e) {
        // TODO: handle exception
      }
    }
  } // end of freeConnection

  // 사용한 자원 반납하기 - SELECT(커서 조작 필요)
  public void freeConnection(Connection con, Statement stmt, ResultSet rs) {
    if (rs != null) {
      try {
        rs.close();
      } catch (Exception e) {
        // TODO: handle exception
      }
    }
    if (stmt != null) {
      try {
        stmt.close();
      } catch (Exception e) {
        // TODO: handle exception
      }
    }
    if (con != null) {
      try {
        con.close();
      } catch (Exception e) {
        // TODO: handle exception
      }
    }
  } // end of freeConnection

  public void freeConnection(Connection con, PreparedStatement pstmt, ResultSet rs) {
    if (rs != null) {
      try {
        rs.close();
      } catch (Exception e) {
        // TODO: handle exception
      }
    }
    if (pstmt != null) {
      try {
        pstmt.close();
      } catch (Exception e) {
        // TODO: handle exception
      }
    }
    if (con != null) {
      try {
        con.close();
      } catch (Exception e) {
        // TODO: handle exception
      }
    }
  } // end of freeConnection

  public static void main(String[] args) {
    // DBConnectionMgr dbMgr = new DBConnectionMgr();
    // Connection con = dbMgr.getConnection();
    // System.out.println("con ==> " + con);
  }
}

/*
 * JDBC API를 이용하여 DB연동하기
 * 1. 각 제조사가 제공하는 드라이버 클래스를 로딩한다.(ojdbc6.jar)
 * Class.forName(드라이버 클래스 풀네임 적기);
 * 
 * 2. 물리적으로 떨어져있는 오라클 서버와 연결 통로를 확보한다.
 * Connection은 인터페이스이다 -> 오른쪽에 구현체 클래스가 와야한다
 * Connection con = DriverManager.getConnection(URL, "scott", "tiger");
 * 
 * 3. DML문을 자바에서 오라클 서버로 전달해 줄 인터페이스를 생성한다.
 * Statement: 정적쿼리문, PreparedStatement:동적쿼리문 지원
 * 
 * 4. SELECT문의 경우 오라클 서버에서 제공하는 커서를 지원하는 ResultSet 인터페이스를 활용하여
 * 테이블에 제공되는 커서를 조작해 해당 로우에 데이터가 존재하면 Cursor..open..fetch과정을 통해
 * 오라클 서버로부터 데이터를 꺼내서 List<DeptVO>에 담을 수 있다.
 * 주의: INSERT, UPDATE, DELETE에서는 커서가 필요없음 - commit, rollback
 * 리턴값은 int
 * 이것들을 요청할 떄는 executeUpdate(INSERT문 or UPDATE문 or DELETE문)
 * 
 * SELECT일때는
 * ResultSet rs = executeQuery("SELECT * FROM dept");
 * 오라클에서 제공되는 커서를 조작하는 인터페이스를 리턴타입으로 제공받아서
 * 조회 결과를 Collection에 담을 수 있다.
 * List<DeptVO> deptList = new ArrayList<>();
 * DeptVO dVO = null;
 * while(rs.next()){
 * dVO = new DeptVO();
 * deptList.add(dVO);
 * }
 * 
 * 사용한 자원을 반납할 때는 사용한 역순으로 닫아주면 됨
 * SELECT인 경우
 * ResultSet 먼저 닫고
 * Statement 혹은 PreparedStatement 두 번째로 닫고
 * Connection을 맨 나중에 닫아주면 됨
 * 닫지 않으면 나중에 자동으로 닫아주기는 하지만
 * 명시적으로 닫아주면 닫는 시간을 앞당길 수 있어, 프로젝트에서는 명시적으로 닫는 코드 작성을 가이드로 정함
 * 
 * INSERT, UPDATE, DELETE는 두 번째와 세 번째만 닫으면 됨
 * 왜냐하면 리턴타입이 커서가 필요없으니까
 */

 

package dev_java.oracle;

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

import dev_java.util.DBConnectionMgr;

// 클래스 설계에 있어서 DAO패턴의 사용은 필수가 되었다
// 특히나 MyBatis와 같은 ORM솔루션이 제공되면서 더 강조되었다
// JDBC API -> MyBatis -> JPA(Hibernate)-쿼리문이 없음(추상적), 대신 클래스설계
public class ChatDao {
  Connection con = null; // 인터페이스 - 연계, 연동
  CallableStatement cstmt = null;
  DBConnectionMgr dbMgr = new DBConnectionMgr();

  public String login(String mem_id, String mem_pw) {
    String mem_name = null;
    try {
      con = dbMgr.getConnection("kiwi", "tiger"); // 물리적으로 떨어져있는 오라클 서버와 연결통로 확보
      cstmt = con.prepareCall("{call proc_login(?,?,?)}");
      cstmt.setString(1, mem_id);
      cstmt.setString(2, mem_pw);
      cstmt.registerOutParameter(3, java.sql.Types.VARCHAR);
      cstmt.executeUpdate();
      mem_name = cstmt.getString(3);
      System.out.println(mem_name);
    } catch (Exception e) {
      e.printStackTrace();
    }
    return mem_name;
  }

  public static void main(String[] args) {
    ChatDao chatDao = new ChatDao();
    chatDao.login("", "123");
  }
}

 

package dev_java.oracle;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import dev_java.util.DBConnectionMgr;
//모든 RDBMS에서 제공되는타입이아니라, 오라클에서만 제공되는 타입(sys_refcursor)
import oracle.jdbc.OracleCallableStatement;
import oracle.jdbc.OracleType;
import oracle.jdbc.OracleTypes;

public class EmpList {
  Connection con = null; // 인터페이스 - 연계, 연동
  CallableStatement cstmt = null; // 프로시저를 요청할 때 사용하는 인터페이스
  OracleCallableStatement ocstmt = null;
  ResultSet rs = null; // 오라클 커서를 조작하는데 필요한 추상메소드를 가짐
  DBConnectionMgr dbMgr = new DBConnectionMgr();

  public List<Map<String, Object>> getEmpList() {
    List<Map<String, Object>> list = new ArrayList<>();

    try {
      con = dbMgr.getConnection("scott", "tiger"); // 물리적으로 떨어져있는 오라클 서버와 연결통로 확보
      cstmt = con.prepareCall("{call my_proc(?)}");
      cstmt.registerOutParameter(1, OracleTypes.CURSOR);
      cstmt.execute();
      // 굳이 추가로 제공되는 클래스로 치환하는 것은 ResultSet을 주입받는 메소드 호출이 필요하기에
      // CallableStatement에서는 getCursor()를 지원하지 않기때문에 굳이 형전환을 함
      ocstmt = (OracleCallableStatement) cstmt;
      rs = ocstmt.getCursor(1);
      Map<String, Object> rmap = null; // 게으른 인스턴스화
      while (rs.next()) {
        rmap = new HashMap<>();
        rmap.put("empno", rs.getInt(1));
        rmap.put("deptno", rs.getInt(2));
        rmap.put("ename", rs.getString(3));
        // List에 Map 추가하기
        // List에 튜플 추가
        // Map에는 컬럼 추가
        list.add(rmap);
      }
    } catch (Exception e) {
      e.printStackTrace();
    }
    return list;
  }

  // 프로시저 안에서는 여러가지 DML문을 한 번 서버에 접속한 상태에서 한 번에 처리가능
  // commit, rollback 가능함
  // 자바로 꺼내서 처리하지 않고 프로시저 내부에서 비즈니스로직(업무포함 프로세스)을
  // 처리 가능 -> 변수활용, 제어문사용
  // 프로시저 내부에서 자바의 힘을 빌리지 않고도 처리할 수 있는 프로세스들이 있다 -> 강점, 시스템부하줄임
  public static void main(String[] args) {
    EmpList eList = new EmpList();
    List<Map<String, Object>> list = eList.getEmpList();
    System.out.println(list);
  }
}