53일차 오라클

요약정리

 

평가 시험
dept
employee 

https://oper6210.tistory.com/149

left, right, inner, outer, natural join

하이버네이트라는게 있음
쿼리문 없이 만들 수 있지만, 복잡한 것을 못짬
JPA

 

 

PL/SQL 프로시저 같은 것. 프로그램이나 함수 같은 것(근데 요새 안쓰는 추새)
이걸로 반복문 돌려서 등록을 여러개 할 수 있음.
일반 프로그래밍 언어 요소를 다 갖고 있으며, 데이터베이스 업무를 처리하기 위한 최적화된 언어
BLOCK 구조로 다수의 SQL 문을 한번에 처리하므로 수행 속도가 빠름

 

프로시저 특징
반환값이 없다.(RETURN이 없다.)

 

board upsert 만들고 vscode에서 직접 기입 해보고 apptest까지 마침

 

 

오전일과

학생테이블에는 강의실이 없는 것을 등록할 수 없음.
외래키를 ABC만 넣어서 F 넣음

(insert로 commit 했다는 것)

JOIN은 개념정리의 url 보면 됨.
INNER JOIN이 교집합임 이걸 가장 많이 씀.(많은 테이블을 들고올땐 아래 VIEW)

 

합쳐서 들고오기

SELECT C.*, S.* FROM CLASSTBL C, STUDENTTBL S WHERE C.CLSCODE=S.STDCLASS;

-- ANSI 표준 SQL

SELECT C.*, S.* FROM CLASSTBL C
INNER JOIN STUDENTTBL S ON C.CLSCODE=S.STDCLASS;

SELECT C.CLSCODE, C.CLSNAME, S.STDNO, S.STDNAME FROM CLASSTBL C
INNER JOIN STUDENTTBL S ON C.CLSCODE=S.STDCLASS;

 

-- RIGHT OUTER JOIN == INNER JOIN 여기서는 두개가 같음

SELECT C.*, S.* FROM STUDENTTBL S, CLASSTBL C WHERE C.CLSCODE=S.STDCLASS(+);
SELECT S.*, C.* FROM STUDENTTBL S, CLASSTBL C WHERE C.CLSCODE=S.STDCLASS(+);
SELECT C.*, S.* FROM STUDENTTBL S, CLASSTBL C WHERE C.CLSCODE(+)=S.STDCLASS;
SELECT S.*, C.* FROM STUDENTTBL S, CLASSTBL C WHERE C.CLSCODE(+)=S.STDCLASS;

SELECT S.*, C.* FROM STUDENTTBL S RIGHT OUTER JOIN CLASSTBL C ON C.CLSCODE=S.STDCLASS;

 

--FULL OUTER JOIN

SELECT S.*, C.* FROM STUDENTTBL S FULL OUTER JOIN CLASSTBL C ON C.CLSCODE=S.STDCLASS; -- ANSI 표준

 

 

4~5개이상 테이블을 여러개 들고오려하면 렉이 걸릴 수 있음.
그래서 뷰를 만들어봄(그대신 뷰는 조회만 가능)

CREATE OR REPLACE VIEW CLASS_STUDENT_VIEW AS
SELECT C.CLSCODE, C.CLSNAME, S.STDNO, S.STDNAME FROM CLASSTBL C
INNER JOIN STUDENTTBL S ON C.CLSCODE=S.STDCLASS;

-- 아래 뷰는 조회만 가능함. 뷰특징이 조회만 가능한 것.
-- (아래 코드는 CLSCODE,CLSNAME,STDNO,STDNAME이 출력 되는데, 이 데이터들이 바뀌면 자동으로 바뀜)

SELECT C.* FROM CLASS_STUDENT_VIEW C;

실습함
ORDER, ITEM, MEMBER 합치기

-- 실습
-- 회원, 물품, 주문 섞기
SELECT M.* FROM MEMBERTBL M;
SELECT I.* FROM ITEMTBL I;
SELECT O.* FROM ORDERTBL O;

-- 문제 주문번호용 시퀀스 SEQ_ORDER_NO 10001
CREATE SEQUENCE SEQ_ORDER_NO START WITH 10001
INCREMENT BY 1 NOMAXVALUE NOCACHE;

-- 문제 제약조건 기본키 시퀀스, 외래키 2개의 제약조건
INSERT INTO ORDERTBL VALUES(SEQ_ORDER_NO.NEXTVAL, 100, 1003, 'a', CURRENT_DATE);

-- INNER JOIN ORDERTBL+ITEMTBL
SELECT O.*, I.* FROM ORDERTBL O INNER JOIN ITEMTBL I ON O.ITEMNO=I.NO;
SELECT O.NO, O.CNT, O.ITEMNO, O.USERID, I.NAME, I.PRICE FROM ORDERTBL O INNER JOIN ITEMTBL I ON O.ITEMNO=I.NO;

-- INNER JOUN (ORDERTBL+ITEMTBL)+MEMBERTBL
SELECT OI.*, M.* FROM (
    SELECT
        O.NO, O.CNT, O.ITEMNO, O.USERID,
        I.NAME, I.PRICE
    FROM
        ORDERTBL O
        INNER JOIN
            ITEMTBL I
        ON
            O.ITEMNO = I.NO
) OI INNER JOIN MEMBERTBL M ON OI.USERID=M.USERID;
--------------------------------
SELECT OI.*, M.AGE, M.PHONE, M.GENDER FROM (
    SELECT 
        O.NO, O.CNT, O.ITEMNO,O.USERID, 
        I.NAME, I.PRICE
    FROM 
        ORDERTBL O 
    INNER JOIN 
        ITEMTBL I 
    ON 
        O.ITEMNO=I.NO
) OI INNER JOIN MEMBERTBL M ON OI.USERID=M.USERID;
-- 위에꺼 뷰로 만듬
-- view ORDER_ITEM_MEMBER_VIEW
CREATE OR REPLACE VIEW ORDER_ITEM_MEMBER_VIEW AS
SELECT OI.*, M.AGE, M.PHONE, M.GENDER FROM (
    SELECT 
        O.NO, O.CNT, O.ITEMNO,O.USERID, O.REGDATE,
        I.NAME, I.PRICE
    FROM 
        ORDERTBL O 
    INNER JOIN 
        ITEMTBL I 
    ON 
        O.ITEMNO=I.NO
) OI INNER JOIN MEMBERTBL M ON OI.USERID=M.USERID;
-- 아래는 뷰 불러오기
SELECT OV.* FROM ORDER_ITEM_MEMBER_VIEW OV;

-- 위에꺼 페이지 네이션 할 것임. 전체에 대해 번호 부여
SELECT OV.*, ROW_NUMBER() OVER (ORDER BY OV.NO DESC) FROM ORDER_ITEM_MEMBER_VIEW OV;
SELECT ROW_NUMBER() OVER (ORDER BY OV.NO DESC) ROWN, OV.* FROM ORDER_ITEM_MEMBER_VIEW OV; -- 이거랑 물품별 번호 비교해보기

-- 물품별 번호 부여(번호 : ROW_NUMBER() 순위 : RANK())
SELECT
    ROW_NUMBER() OVER (PARTITION BY OV.ITEMNO ORDER BY OV.NO DESC) ROWN, 
    OV.* 
FROM ORDER_ITEM_MEMBER_VIEW OV;

-- 페이지네이션(생성된 것 기반으로 다시 조회하여 페이지 네이션을 만듬)
SELECT T1.* FROM(
    SELECT
        ROW_NUMBER() OVER (ORDER BY OV.NO DESC) ROWN, 
        OV.* 
    FROM ORDER_ITEM_MEMBER_VIEW OV
) T1
WHERE T1.ROWN BETWEEN 1 AND 3;

 

뷰만듬
페이지 네이션
- 전체에 대해 번호 부여
- 물품별 번호 부여 (ROW_NYMBER(), RANK())
- 페이지네이션(생성된 것 기반으로 다시 조회하여 페이지 네이션을 만듬)

 

- 주문개수CNT에 조건을 줘서 테이블에 A, B , C 등급 추가해줌

-- 주문수량 0~100 A등급
-- 주문수량 101~200 B등급
-- 주문수량 300이상 C등급
-- 뷰에서 일단 들고옴
SELECT OV.* FROM ORDER_ITEM_MEMBER_VIEW OV;
-- 위에꺼 만져서 아래껄로 만듬
SELECT 
    OV.*,
    CASE
        WHEN(OV.CNT >= 0 AND OV.CNT<=100) THEN 'A등급'
        WHEN(OV.CNT >= 101 AND OV.CNT<=200) THEN 'B등급'
        WHEN(OV.CNT >= 201) THEN 'C등급'
    END GRADE,
    OV.*
FROM ORDER_ITEM_MEMBER_VIEW OV;

인덱스 오류 있는 사람 있어서 인덱스 설명

-- 검색속도를 향샹시켜줌
-- 기본키는 인덱스를 자동 설정함.
-- 인덱스 생성 IDX_ITEM_NAME ON 테이블명(컬럼명);
CREATE INDEX IDX_ITEM_NAME ON ITEMTBL(NAME);

DEPT
EMPLOYEE
테이블 데이터 까지 만들어 주심 + 실습 문제

SELECT D.* FROM DEPT D;
INSERT INTO DEPT VALUES(101, '총무부', CURRENT_DATE, '서울');
INSERT INTO DEPT VALUES(102, '영업부', CURRENT_DATE, '부산');
INSERT INTO DEPT VALUES(103, '기획부', CURRENT_DATE, '대구');
INSERT INTO DEPT VALUES(104, '홍보부', CURRENT_DATE, '서울');
-- 101 총무부 서울
-- 102 영업부 부산
-- 103 기획부 대구
-- 104 홍보부 서울

-- SEQ_EMP_NO 시작숫자 10001
CREATE SEQUENCE SEQ_EMP_NO START WITH 10001 INCREMENT BY 1 NOMAXVALUE NOCACHE;
SELECT E.* FROM EMPTBL E;
INSERT INTO EMPTBL VALUES(SEQ_EMP_NO.NEXTVAL, '가나다1', 101, 3000000, CURRENT_DATE, '대리');
INSERT INTO EMPTBL VALUES(SEQ_EMP_NO.NEXTVAL, '가나다2', 102, 2800000, CURRENT_DATE, '대리');
INSERT INTO EMPTBL VALUES(SEQ_EMP_NO.NEXTVAL, '가나다3', 103, 5600000, CURRENT_DATE, '과장');
INSERT INTO EMPTBL VALUES(SEQ_EMP_NO.NEXTVAL, '가나다4', 104, 2700000, CURRENT_DATE, '대리');
INSERT INTO EMPTBL VALUES(SEQ_EMP_NO.NEXTVAL, '가나다5', 102, 3300000, CURRENT_DATE, '대리');
INSERT INTO EMPTBL VALUES(SEQ_EMP_NO.NEXTVAL, '가나다6', 101, 2250000, CURRENT_DATE, '사원');

DELETE FROM EMPTBL;
COMMIT;

실습 문제 내주심1~8

-- 문제1) DEPT의 영업부를 "대구" 나머지는 "부산"으로 변경
UPDATE DEPT SET AREA='부산' WHERE NAME!='영업부';
UPDATE DEPT SET AREA='대구' WHERE NAME='영업부';
UPDATE DEPT03 SET AREA =
    CASE 
        WHEN (DEPTNO=102) THEN '대구'
        ELSE '부산'
    END;

-- 문제2) EMPLOYEE의 부서번호가 103인 항목 삭제
DELETE FROM EMPTBL WHERE DEPTNO = 103;

-- 문제3) EMPLOYEE의 영업부102 직원과 총무부101 직원의 이름, 급여, 직급을 사원번호 순으로 내림차순 조회
SELECT E.* FROM EMPTBL E;
SELECT NAME, PAY, POSITION FROM EMPTBL WHERE DEPTNO = '102' ORDER BY EMPNO DESC;
SELECT NAME, PAY, POSITION FROM EMPTBL WHERE DEPTNO = '101' ORDER BY EMPNO DESC;
SELECT E.DEPTNO, E.NAME, E.PAY, E.POSITION FROM EMPLOYEE E WHERE E.DEPTNO IN(101,102); 

SELECT D.*, E.* FROM DEPT D
INNER JOIN(
    SELECT E.DEPTNO, E.NAME, E.PAY, E.POSITION FROM EMPLOYEE E WHERE E.DEPTNO IN(101,102)
) E 
ON D.DEPTNO=E.DEPTNO; 



-- 문제4) EMPLOYEE의 급여에서 0~230 이면 5%, 231~300이면 10%, 301~400 15%, 400~ 20%의 세금을 조회(사원번호, 사원명, 급여, 세금 표시)
-- CASE사용
SELECT 
    E.EMPNO, E.NAME, E.PAY,
    CASE
        WHEN(0<=E.PAY AND E.PAY<=2300000) THEN TRUNC(E.PAY*0.05)
        WHEN(2310000<=E.PAY AND E.PAY<=3000000) THEN TRUNC(E.PAY*0.1)
        WHEN(3010000<=E.PAY AND E.PAY<=4000000) THEN TRUNC(E.PAY*0.15)
        WHEN(4010000<=E.PAY) THEN TRUNC(E.PAY*0.2)
    END TAX,
    E.*
FROM EMPTBL E;

SELECT E.EMPNO, E.NAME, E.PAY, 
CASE
    WHEN(E.PAY >= 0 AND E.PAY<=2300000) THEN E.PAY*0.05
    WHEN(E.PAY >= 2310000 AND E.PAY<=3000000) THEN E.PAY*0.1
    WHEN(E.PAY >= 3010000 AND E.PAY<=4000000) THEN E.PAY*0.15
    WHEN(E.PAY >= 4010000) THEN E.PAY*0.2
END TAX
FROM EMPLOYEE E;

-- 문제5) EMPLOYEE의 부서별 평균 급여와 인원수 출력
-- GROUP BY 사용
SELECT E.DEPTNO, AVG(E.PAY) DEPTAVG, COUNT(*) FROM EMPTBL E GROUP BY DEPTNO;
SELECT E.DEPTNO 부서번호, AVG(E.PAY) 평균급여, COUNT(*) 인원수 FROM EMPLOYEE E GROUP BY E.DEPTNO;

-- 문제6) EMPLOYEE의 부서별 급여 평균이 300이상인 부서 조회(부서번호, 부서명, 급여 평균)
SELECT T1.* FROM(
    SELECT E.DEPTNO, AVG(E.PAY) DEPTAVG
    FROM EMPTBL E 
    GROUP BY DEPTNO
) T1
WHERE DEPTAVG>3000000;


SELECT E.DEPTNO 부서번호, AVG(E.PAY) 평균급여 FROM EMPTBL E GROUP BY E.DEPTNO HAVING AVG(E.PAY)>=3000000;

SELECT D.* , E.* FROM DEPT D INNER JOIN ( 
    SELECT E.DEPTNO, AVG(E.PAY) 평균급여 FROM EMPTBL E GROUP BY E.DEPTNO HAVING AVG(E.PAY)>=3000000
) E 
ON D.DEPTNO = E.DEPTNO;

-- 문제7) 직급별 총급여, 평균급여, 인원수 출력
SELECT E.POSITION, SUM(E.PAY) POSITIONSUM, AVG(E.PAY) POSITIONAVG, COUNT(*) FROM EMPTBL E GROUP BY POSITION;
SELECT E.POSITION 직급, SUM(E.PAY)총급여, AVG(E.PAY) 평균급여, COUNT(*) 인원수 FROM EMPLOYEE E GROUP BY E.POSITION;

-- 문제8) 직급별 급여가 높은 순으로 RANK() 출력
-- PARTITON사용
SELECT
    RANK() OVER (PARTITION BY E.POSITION ORDER BY E.PAY DESC) RANK, 
    E.* 
FROM EMPTBL E;

SELECT 
    RANK() OVER (PARTITION BY E.POSITION ORDER BY E.PAY DESC) RNK,
    E.* 
FROM EMPLOYEE E;

문제1) DEPT의 영업부를 "대구" 나머지는 "부산"으로 변경

UPDATE DEPT SET AREA =
    CASE 
        WHEN (DEPTNO=102) THEN '대구'
        ELSE '부산'
    END;

문제2) EMPLOYEE의 부서번호가 103인 항목 삭제

DELETE FROM EMPTBL WHERE DEPTNO = 103;

문제3) EMPLOYEE의 영업부102 직원과 총무부101 직원의 이름, 급여, 직급을 사원번호 순으로 내림차순 조회

SELECT E.DEPTNO, E.NAME, E.PAY, E.POSITION FROM EMPTBL E WHERE E.DEPTNO IN(101,102);

SELECT D.*, E.* FROM DEPT D
INNER JOIN(
    SELECT E.DEPTNO, E.NAME, E.PAY, E.POSITION FROM EMPTBL E WHERE E.DEPTNO IN(101,102)
) E 
ON D.DEPTNO=E.DEPTNO;

문제4) EMPLOYEE의 급여에서 0~230 이면 5%, 231~300이면 10%, 301~400 15%, 400~ 20%의 세금을 조회(사원번호, 사원명, 급여, 세금 표시)

SELECT E.EMPNO, E.NAME, E.PAY, 
CASE
    WHEN(E.PAY >= 0 AND E.PAY<=2300000) THEN E.PAY*0.05
    WHEN(E.PAY >= 2310000 AND E.PAY<=3000000) THEN E.PAY*0.1
    WHEN(E.PAY >= 3010000 AND E.PAY<=4000000) THEN E.PAY*0.15
    WHEN(E.PAY >= 4010000) THEN E.PAY*0.2
END TAX
FROM EMPTBL E;

문제5) EMPLOYEE의 부서별 평균 급여와 인원수 출력

SELECT E.DEPTNO 부서번호, AVG(E.PAY) 평균급여, COUNT(*) 인원수 FROM EMPTBL E GROUP BY E.DEPTNO;

문제6) EMPLOYEE의 부서별 급여 평균이 300이상인 부서 조회(부서번호, 부서명, 급여 평균)

SELECT D.* , E.* FROM DEPT D INNER JOIN ( 
    SELECT E.DEPTNO, AVG(E.PAY) 평균급여 FROM EMPTBL E GROUP BY E.DEPTNO HAVING AVG(E.PAY)>=3000000
) E 
ON D.DEPTNO = E.DEPTNO;

문제7) 직급별 총급여, 평균급여, 인원수 출력

SELECT E.POSITION 직급, SUM(E.PAY)총급여, AVG(E.PAY) 평균급여, COUNT(*) 인원수 FROM EMPTBL E GROUP BY E.POSITION;

문제8) 직급별 급여가 높은 순으로 RANK() 출력

SELECT 
    RANK() OVER (PARTITION BY E.POSITION ORDER BY E.PAY DESC) RNK,
    E.* 
FROM EMPTBL E;

오후일과
PL/SQL 프로시저 같은 것. 프로그램이나 함수 같은 것
이걸로 반복문 돌려서 등록을 여러개 할 수 있음.
일반 프로그래밍 언어 요소를 다 갖고 있으며, 데이터베이스 업무를 처리하기 위한 최적화된 언어
BLOCK 구조로 다수의 SQL 문을 한번에 처리하므로 수행 속도가 빠름

시작하기전에 서버 켜야함

SET SERVEROUTPUT ON;
--서버 꺼져있어서 켜야함

출력해봄

-- 자바로 따지면 SYSO임.
BEGIN
    DBMS_OUTPUT.PUT_LINE('hello');
END;
/
-- PL/SQL종료

변수선언

-- 변수도 만들 수 있음. INT A = 13;
DECLARE
    V_STR VARCHAR2(30) := 'asdf'; -- 원래 이런 변수값들은 스프링 부트에서 받아야함
    V_NUM NUMBER := 13;
BEGIN
    DBMS_OUTPUT.PUT_LINE('str =>' || V_STR);
    DBMS_OUTPUT.PUT_LINE('num =>' || V_NUM);
END;
/

IF FOR 해봄

-- IF
DECLARE
    V_STR VARCHAR2(2); -- 원래 이런 변수값들은 스프링 부트에서 받아야함
    V_NUM NUMBER := 90;
BEGIN
    IF V_NUM >=90 THEN
        V_STR := 'A';
    ELSIF V_NUM >=80 THEN
        V_STR := 'B';
    ELSIF V_NUM >=70 THEN
        V_STR := 'C';
    ELSE
        V_STR := 'D';
    END IF;
    DBMS_OUTPUT.PUT_LINE('등급은 =>' || V_STR);
END;
/

-- FOR
DECLARE
    V_NUM NUMBER := 5;
BEGIN
    FOR i IN 1..V_NUM LOOP
        IF i =1 OR i = 3 OR i =5 THEN
            DBMS_OUTPUT.PUT_LINE('반복숫자 =>' || i);
        END IF;
    END LOOP;
END;
/

프로시저 특징
반환값이 없다.(RETURN이 없다.)

 

프로시저 연습용 하나 만들었고

CREATE OR REPLACE PROCEDURE PROC_GRADE(
    V_NUM IN NUMBER -- IN은 외부에서 들어가는 값
)
IS
    V_NUM1 NUMBER := 5;
BEGIN
    DBMS_OUTPUT.PUT_LINE('합은 =>' || (V_NUM+V_NUM1));
END;
/

EXEC PROC_GRADE(50); -- 테스트용 SPRING BOOT에서 호출해야함.

게시판으로 예제
- 게시판에 글번호가 존재하면 update를 수행,
- 존재하지 않으면 insert를 수행하는 프로시저

-------------------------------게시판 예제
CREATE OR REPLACE PROCEDURE PROC_BOARD_UPSERT(
    V_NO IN BOARDTBL.NO%TYPE, --number
    V_TITLE IN BOARDTBL.TITLE%TYPE,
    V_CONTENT IN BOARDTBL.CONTENT%TYPE,
    V_WRITER IN BOARDTBL.WRITER%TYPE,
    V_RET OUT NUMBER
)
IS
    V_CHK NUMBER(1) := 0; -- 존재유무 확인용
BEGIN
    -- 조회한 결과를 V_CHK에 보관
    SELECT COUNT(*) INTO V_CHK FROM BOARDTBL B WHERE B.NO = V_NO;
--    SELECT COUNT(*) FROM BOARDTBL B WHERE B.NO = 1;
--    SELECT COUNT(*) FROM BOARDTBL B WHERE B.NO = 1000;
    IF V_CHK > 0 THEN
        UPDATE BOARDTBL SET TITLE=V_TITLE, CONTENT=V_CONTENT, 
        WRITER=V_WRITER WHERE NO=V_NO; 
        V_RET := 1;
    ELSE
        INSERT INTO BOARDTBL(NO, TITLE, WRITER, HIT, REGDATE, CONTENT)
        VALUES (SEQ_BOARDTBL_NO.NEXTVAL, V_TITLE, V_WRITER, 1, CURRENT_DATE, V_CONTENT);
        V_RET := 2;
    END IF;
    COMMIT;
EXCEPTION
    WHEN OTHERS THEN
    V_RET := 0;
    ROLLBACK;
END;
/

SELECT B.* FROM BOARDTBL B;

DECLARE
    V_RET NUMBER;
BEGIN
    PROC_BOARD_UPSERT(100, 'PROC제목', 'PROC내용', 'PROC작성자', V_RET);
    DBMS_OUTPUT.PUT_LINE('OUT변수 값은 =>' || V_RET);
END;
/

EXEC PROC_BOARD_UPSERT(15, 'PROC제목', 'PROC내용', 'PROC작성자');

 

게시물 조회로 있나없나확인하고
업설트 이거 중요중요중요중요중요중요중요중요중요중요중요중요
V_RET OUT

vscode로 이동
boardmapper
프로시저는 insert

 

BoardMapper.java

package com.example.mapper;

import java.util.Map;

import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Options;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.mapping.StatementType;

@Mapper
public interface BoardMapper {
    
    @Insert({
        "{ call PROC_BOARD_UPSERT(", 
            "#{map.no, mode=IN, jdbcType=NUMERIC, javaType=Long }, ",
            "#{map.title, mode=IN, jdbcType=VARCHAR, javaType=String }, ",
            "#{map.content, mode=IN, jdbcType=VARCHAR, javaType=String }, ",
            "#{map.writer, mode=IN, jdbcType=VARCHAR, javaType=String }, ",
            "#{map.ret, mode=OUT, jdbcType=NUMERIC, javaType=Integer }",
        ")}"
    })
    @Options(statementType = StatementType.CALLABLE)
    public void callProcedure(@Param("map") Map<String, Object> map);

}

MyBoardTest.java

package com.example.boot_20220827;

import java.util.HashMap;
import java.util.Map;

import org.junit.jupiter.api.Test;
import org.slf4j.Marker;
import org.slf4j.MarkerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;

import com.example.mapper.BoardMapper;

import lombok.extern.slf4j.Slf4j;

@SpringBootTest
@Slf4j
public class MyBoardTest {
    final String format = "BOARDTBL => {}";

    @Autowired
    BoardMapper bMapper;

    @Test
    public void selectListTest(){
        log.info(format,"PROC TEST");
        Map<String, Object> map = new HashMap<>();
        map.put("no", 1L);
        map.put("title", "제목");
        map.put("content", "내용");
        map.put("writer", "작성자");
        map.put("ret", 0);
        bMapper.callProcedure( map );
        // System.out.println( "retretretretretretretretretretretretretret"+map.get("ret") );
        log.info(format,map.get("ret"));
    }
}

sql

-- 22_09_08 오전

SELECT C.* FROM CLASSTBL C;

SELECT S.* FROM STUDENTTBL S;

-- 합쳐서 들고오기
-- INNER JOIN
SELECT C.*, S.* FROM CLASSTBL C, STUDENTTBL S WHERE C.CLSCODE=S.STDCLASS;
-- ANSI 표쥰 SQL
SELECT C.*, S.* FROM CLASSTBL C
INNER JOIN STUDENTTBL S ON C.CLSCODE=S.STDCLASS;
SELECT C.CLSCODE, C.CLSNAME, S.STDNO, S.STDNAME FROM CLASSTBL C
INNER JOIN STUDENTTBL S ON C.CLSCODE=S.STDCLASS;

-- RIGHT OUTER JOIN == INNER JOIN 여기서는 두개가 같음
SELECT C.*, S.* FROM STUDENTTBL S, CLASSTBL C WHERE C.CLSCODE=S.STDCLASS(+);
SELECT S.*, C.* FROM STUDENTTBL S, CLASSTBL C WHERE C.CLSCODE=S.STDCLASS(+);
SELECT C.*, S.* FROM STUDENTTBL S, CLASSTBL C WHERE C.CLSCODE(+)=S.STDCLASS;
SELECT S.*, C.* FROM STUDENTTBL S, CLASSTBL C WHERE C.CLSCODE(+)=S.STDCLASS; -- 요거

SELECT S.*, C.* FROM STUDENTTBL S RIGHT OUTER JOIN CLASSTBL C ON C.CLSCODE=S.STDCLASS;

--FULL OUTER JOIN
SELECT S.*, C.* FROM STUDENTTBL S, CLASSTBL C WHERE C.CLSCODE(+)=S.STDCLASS(+); -- 이건 안됨

SELECT S.*, C.* FROM STUDENTTBL S FULL OUTER JOIN CLASSTBL C ON C.CLSCODE=S.STDCLASS; -- ANSI 표준

-- ANSI 표준 SQL
-- 조인한 결과를 기반으로 VIEW 생성 (CLASS_STUDENT_VIEW)
-- 4~5개이상 테이블을 여러개 들고오려하면 렉이 걸릴 수 있음. 그래서 아래처럼 VIEW를 씀
CREATE OR REPLACE VIEW CLASS_STUDENT_VIEW AS
SELECT C.CLSCODE, C.CLSNAME, S.STDNO, S.STDNAME FROM CLASSTBL C
INNER JOIN STUDENTTBL S ON C.CLSCODE=S.STDCLASS;

-- 아래 뷰는 조회만 가능함. 뷰특징이 조회만 가능한 것.
-- (아래 코드는 CLSCODE,CLSNAME,STDNO,STDNAME이 출력 되는데, 이 데이터들이 바뀌면 자동으로 바뀜)
SELECT C.* FROM CLASS_STUDENT_VIEW C;

-- 실습
-- 회원, 물품, 주문 섞기
SELECT M.* FROM MEMBERTBL M;
SELECT I.* FROM ITEMTBL I;
SELECT O.* FROM ORDERTBL O;

-- 문제 주문번호용 시퀀스 SEQ_ORDER_NO 10001
CREATE SEQUENCE SEQ_ORDER_NO START WITH 10001
INCREMENT BY 1 NOMAXVALUE NOCACHE;

-- 문제 제약조건 기본키 시퀀스, 외래키 2개의 제약조건
INSERT INTO ORDERTBL VALUES(SEQ_ORDER_NO.NEXTVAL, 100, 1003, 'a', CURRENT_DATE);

-- INNER JOIN ORDERTBL+ITEMTBL
SELECT O.*, I.* FROM ORDERTBL O INNER JOIN ITEMTBL I ON O.ITEMNO=I.NO;
SELECT O.NO, O.CNT, O.ITEMNO, O.USERID, I.NAME, I.PRICE FROM ORDERTBL O INNER JOIN ITEMTBL I ON O.ITEMNO=I.NO;

-- INNER JOUN (ORDERTBL+ITEMTBL)+MEMBERTBL
SELECT OI.*, M.* FROM (
    SELECT
        O.NO, O.CNT, O.ITEMNO, O.USERID,
        I.NAME, I.PRICE
    FROM
        ORDERTBL O
        INNER JOIN
            ITEMTBL I
        ON
            O.ITEMNO = I.NO
) OI INNER JOIN MEMBERTBL M ON OI.USERID=M.USERID;
--------------------------------
SELECT OI.*, M.AGE, M.PHONE, M.GENDER FROM (
    SELECT 
        O.NO, O.CNT, O.ITEMNO,O.USERID, 
        I.NAME, I.PRICE
    FROM 
        ORDERTBL O 
    INNER JOIN 
        ITEMTBL I 
    ON 
        O.ITEMNO=I.NO
) OI INNER JOIN MEMBERTBL M ON OI.USERID=M.USERID;
-- 위에꺼 뷰로 만듬
-- view ORDER_ITEM_MEMBER_VIEW
CREATE OR REPLACE VIEW ORDER_ITEM_MEMBER_VIEW AS
SELECT OI.*, M.AGE, M.PHONE, M.GENDER FROM (
    SELECT 
        O.NO, O.CNT, O.ITEMNO,O.USERID, O.REGDATE,
        I.NAME, I.PRICE
    FROM 
        ORDERTBL O 
    INNER JOIN 
        ITEMTBL I 
    ON 
        O.ITEMNO=I.NO
) OI INNER JOIN MEMBERTBL M ON OI.USERID=M.USERID;
-- 아래는 뷰 불러오기
SELECT OV.* FROM ORDER_ITEM_MEMBER_VIEW OV;

-- 위에꺼 페이지 네이션 할 것임. 전체에 대해 번호 부여
SELECT OV.*, ROW_NUMBER() OVER (ORDER BY OV.NO DESC) FROM ORDER_ITEM_MEMBER_VIEW OV;
SELECT ROW_NUMBER() OVER (ORDER BY OV.NO DESC) ROWN, OV.* FROM ORDER_ITEM_MEMBER_VIEW OV; -- 이거랑 물품별 번호 비교해보기

-- 물품별 번호 부여(번호 : ROW_NUMBER() 순위 : RANK())
SELECT
    ROW_NUMBER() OVER (PARTITION BY OV.ITEMNO ORDER BY OV.NO DESC) ROWN, 
    OV.* 
FROM ORDER_ITEM_MEMBER_VIEW OV;

-- 페이지네이션(생성된 것 기반으로 다시 조회하여 페이지 네이션을 만듬)
SELECT T1.* FROM(
    SELECT
        ROW_NUMBER() OVER (ORDER BY OV.NO DESC) ROWN, 
        OV.* 
    FROM ORDER_ITEM_MEMBER_VIEW OV
) T1
WHERE T1.ROWN BETWEEN 1 AND 3;

-- 주문수량 0~100 A등급
-- 주문수량 101~200 B등급
-- 주문수량 300이상 C등급
-- 뷰에서 일단 들고옴
SELECT OV.* FROM ORDER_ITEM_MEMBER_VIEW OV;
-- 위에꺼 만져서 아래껄로 만듬
SELECT 
    OV.*,
    CASE
        WHEN(OV.CNT >= 0 AND OV.CNT<=100) THEN 'A등급'
        WHEN(OV.CNT >= 101 AND OV.CNT<=200) THEN 'B등급'
        WHEN(OV.CNT >= 201) THEN 'C등급'
    END GRADE,
    OV.*
FROM ORDER_ITEM_MEMBER_VIEW OV;


-- 검색속도를 향샹시켜줌
-- 기본키는 인덱스를 자동 설정함.
-- 인덱스 생성 IDX_ITEM_NAME ON 테이블명(컬럼명);
CREATE INDEX IDX_ITEM_NAME ON ITEMTBL(NAME);

SELECT D.* FROM DEPT D;
INSERT INTO DEPT VALUES(101, '총무부', CURRENT_DATE, '서울');
INSERT INTO DEPT VALUES(102, '영업부', CURRENT_DATE, '부산');
INSERT INTO DEPT VALUES(103, '기획부', CURRENT_DATE, '대구');
INSERT INTO DEPT VALUES(104, '홍보부', CURRENT_DATE, '서울');
-- 101 총무부 서울
-- 102 영업부 부산
-- 103 기획부 대구
-- 104 홍보부 서울

-- SEQ_EMP_NO 시작숫자 10001
CREATE SEQUENCE SEQ_EMP_NO START WITH 10001 INCREMENT BY 1 NOMAXVALUE NOCACHE;
SELECT E.* FROM EMPTBL E;
INSERT INTO EMPTBL VALUES(SEQ_EMP_NO.NEXTVAL, '가나다1', 101, 3000000, CURRENT_DATE, '대리');
INSERT INTO EMPTBL VALUES(SEQ_EMP_NO.NEXTVAL, '가나다2', 102, 2800000, CURRENT_DATE, '대리');
INSERT INTO EMPTBL VALUES(SEQ_EMP_NO.NEXTVAL, '가나다3', 103, 5600000, CURRENT_DATE, '과장');
INSERT INTO EMPTBL VALUES(SEQ_EMP_NO.NEXTVAL, '가나다4', 104, 2700000, CURRENT_DATE, '대리');
INSERT INTO EMPTBL VALUES(SEQ_EMP_NO.NEXTVAL, '가나다5', 102, 3300000, CURRENT_DATE, '대리');
INSERT INTO EMPTBL VALUES(SEQ_EMP_NO.NEXTVAL, '가나다6', 101, 2250000, CURRENT_DATE, '사원');

DELETE FROM EMPTBL;
COMMIT;
--
-- 문제1) DEPT의 영업부를 "대구" 나머지는 "부산"으로 변경
UPDATE DEPT SET AREA='부산' WHERE NAME!='영업부';
UPDATE DEPT SET AREA='대구' WHERE NAME='영업부';
UPDATE DEPT03 SET AREA =
    CASE 
        WHEN (DEPTNO=102) THEN '대구'
        ELSE '부산'
    END;

-- 문제2) EMPLOYEE의 부서번호가 103인 항목 삭제
DELETE FROM EMPTBL WHERE DEPTNO = 103;

-- 문제3) EMPLOYEE의 영업부102 직원과 총무부101 직원의 이름, 급여, 직급을 사원번호 순으로 내림차순 조회
SELECT E.* FROM EMPTBL E;
SELECT NAME, PAY, POSITION FROM EMPTBL WHERE DEPTNO = '102' ORDER BY EMPNO DESC;
SELECT NAME, PAY, POSITION FROM EMPTBL WHERE DEPTNO = '101' ORDER BY EMPNO DESC;
SELECT E.DEPTNO, E.NAME, E.PAY, E.POSITION FROM EMPLOYEE E WHERE E.DEPTNO IN(101,102); 

SELECT D.*, E.* FROM DEPT D
INNER JOIN(
    SELECT E.DEPTNO, E.NAME, E.PAY, E.POSITION FROM EMPLOYEE E WHERE E.DEPTNO IN(101,102)
) E 
ON D.DEPTNO=E.DEPTNO; 



-- 문제4) EMPLOYEE의 급여에서 0~230 이면 5%, 231~300이면 10%, 301~400 15%, 400~ 20%의 세금을 조회(사원번호, 사원명, 급여, 세금 표시)
-- CASE사용
SELECT 
    E.EMPNO, E.NAME, E.PAY,
    CASE
        WHEN(0<=E.PAY AND E.PAY<=2300000) THEN TRUNC(E.PAY*0.05)
        WHEN(2310000<=E.PAY AND E.PAY<=3000000) THEN TRUNC(E.PAY*0.1)
        WHEN(3010000<=E.PAY AND E.PAY<=4000000) THEN TRUNC(E.PAY*0.15)
        WHEN(4010000<=E.PAY) THEN TRUNC(E.PAY*0.2)
    END TAX,
    E.*
FROM EMPTBL E;

SELECT E.EMPNO, E.NAME, E.PAY, 
CASE
    WHEN(E.PAY >= 0 AND E.PAY<=2300000) THEN E.PAY*0.05
    WHEN(E.PAY >= 2310000 AND E.PAY<=3000000) THEN E.PAY*0.1
    WHEN(E.PAY >= 3010000 AND E.PAY<=4000000) THEN E.PAY*0.15
    WHEN(E.PAY >= 4010000) THEN E.PAY*0.2
END TAX
FROM EMPLOYEE E;

-- 문제5) EMPLOYEE의 부서별 평균 급여와 인원수 출력
-- GROUP BY 사용
SELECT E.DEPTNO, AVG(E.PAY) DEPTAVG, COUNT(*) FROM EMPTBL E GROUP BY DEPTNO;
SELECT E.DEPTNO 부서번호, AVG(E.PAY) 평균급여, COUNT(*) 인원수 FROM EMPLOYEE E GROUP BY E.DEPTNO;

-- 문제6) EMPLOYEE의 부서별 급여 평균이 300이상인 부서 조회(부서번호, 부서명, 급여 평균)
SELECT T1.* FROM(
    SELECT E.DEPTNO, AVG(E.PAY) DEPTAVG
    FROM EMPTBL E 
    GROUP BY DEPTNO
) T1
WHERE DEPTAVG>3000000;


SELECT E.DEPTNO 부서번호, AVG(E.PAY) 평균급여 FROM EMPTBL E GROUP BY E.DEPTNO HAVING AVG(E.PAY)>=3000000;

SELECT D.* , E.* FROM DEPT D INNER JOIN ( 
    SELECT E.DEPTNO, AVG(E.PAY) 평균급여 FROM EMPTBL E GROUP BY E.DEPTNO HAVING AVG(E.PAY)>=3000000
) E 
ON D.DEPTNO = E.DEPTNO;

-- 문제7) 직급별 총급여, 평균급여, 인원수 출력
SELECT E.POSITION, SUM(E.PAY) POSITIONSUM, AVG(E.PAY) POSITIONAVG, COUNT(*) FROM EMPTBL E GROUP BY POSITION;
SELECT E.POSITION 직급, SUM(E.PAY)총급여, AVG(E.PAY) 평균급여, COUNT(*) 인원수 FROM EMPLOYEE E GROUP BY E.POSITION;

-- 문제8) 직급별 급여가 높은 순으로 RANK() 출력
-- PARTITON사용
SELECT
    RANK() OVER (PARTITION BY E.POSITION ORDER BY E.PAY DESC) RANK, 
    E.* 
FROM EMPTBL E;

SELECT 
    RANK() OVER (PARTITION BY E.POSITION ORDER BY E.PAY DESC) RNK,
    E.* 
FROM EMPLOYEE E;


-- 08 오후일과
-- PL/SQL 프로시제 같은 것
-- 일반 프로그래밍 언어 요소를 다 갖고 있으며, 데이터베이스 업무를 처리하기 위한 최적화된 언어
-- BLOCK 구조로 다수의 SQL 문을 한번에 처리하므로 수행 속도가 빠름
SET SERVEROUTPUT ON;
--서버 꺼져있어서 켜야함

-- 자바로 따지면 SYSO임.
BEGIN
    DBMS_OUTPUT.PUT_LINE('hello');
END;
/
-- PL/SQL종료

-- 변수도 만들 수 있음. INT A = 13;
DECLARE
    V_STR VARCHAR2(30) := 'asdf'; -- 원래 이런 변수값들은 스프링 부트에서 받아야함
    V_NUM NUMBER := 13;
BEGIN
    DBMS_OUTPUT.PUT_LINE('str =>' || V_STR);
    DBMS_OUTPUT.PUT_LINE('num =>' || V_NUM);
END;
/

-- IF
DECLARE
    V_STR VARCHAR2(2); -- 원래 이런 변수값들은 스프링 부트에서 받아야함
    V_NUM NUMBER := 90;
BEGIN
    IF V_NUM >=90 THEN
        V_STR := 'A';
    ELSIF V_NUM >=80 THEN
        V_STR := 'B';
    ELSIF V_NUM >=70 THEN
        V_STR := 'C';
    ELSE
        V_STR := 'D';
    END IF;
    DBMS_OUTPUT.PUT_LINE('등급은 =>' || V_STR);
END;
/

-- FOR
DECLARE
    V_NUM NUMBER := 5;
BEGIN
    FOR i IN 1..V_NUM LOOP
        IF i =1 OR i = 3 OR i =5 THEN
            DBMS_OUTPUT.PUT_LINE('반복숫자 =>' || i);
        END IF;
    END LOOP;
END;
/

-- 프로시저
-- 없으면 추가 있으면 수정
CREATE OR REPLACE PROCEDURE PROC_GRADE(
    V_NUM IN NUMBER -- IN은 외부에서 들어가는 값
)
IS
    V_NUM1 NUMBER := 5;
BEGIN
    DBMS_OUTPUT.PUT_LINE('합은 =>' || (V_NUM+V_NUM1));
END;
/

EXEC PROC_GRADE(50); -- 테스트용 SPRING BOOT에서 호출해야함.

-------------------------------게시판 예제
CREATE OR REPLACE PROCEDURE PROC_BOARD_UPSERT(
    V_NO IN BOARDTBL.NO%TYPE, --number
    V_TITLE IN BOARDTBL.TITLE%TYPE,
    V_CONTENT IN BOARDTBL.CONTENT%TYPE,
    V_WRITER IN BOARDTBL.WRITER%TYPE,
    V_RET OUT NUMBER
)
IS
    V_CHK NUMBER(1) := 0; -- 존재유무 확인용
BEGIN
    -- 조회한 결과를 V_CHK에 보관
    SELECT COUNT(*) INTO V_CHK FROM BOARDTBL B WHERE B.NO = V_NO;
--    SELECT COUNT(*) FROM BOARDTBL B WHERE B.NO = 1;
--    SELECT COUNT(*) FROM BOARDTBL B WHERE B.NO = 1000;
    IF V_CHK > 0 THEN
        UPDATE BOARDTBL SET TITLE=V_TITLE, CONTENT=V_CONTENT, 
        WRITER=V_WRITER WHERE NO=V_NO; 
        V_RET := 1;
    ELSE
        INSERT INTO BOARDTBL(NO, TITLE, WRITER, HIT, REGDATE, CONTENT)
        VALUES (SEQ_BOARDTBL_NO.NEXTVAL, V_TITLE, V_WRITER, 1, CURRENT_DATE, V_CONTENT);
        V_RET := 2;
    END IF;
    COMMIT;
EXCEPTION
    WHEN OTHERS THEN
    V_RET := 0;
    ROLLBACK;
END;
/

SELECT B.* FROM BOARDTBL B;

DECLARE
    V_RET NUMBER;
BEGIN
    PROC_BOARD_UPSERT(100, 'PROC제목', 'PROC내용', 'PROC작성자', V_RET);
    DBMS_OUTPUT.PUT_LINE('OUT변수 값은 =>' || V_RET);
END;
/

EXEC PROC_BOARD_UPSERT(15, 'PROC제목', 'PROC내용', 'PROC작성자');