20220913 [Spring Boot, Oracle]

Yeoonnii·2022년 9월 13일
Spring bootTILoracle
0

TIL

목록 보기
24/52
post-thumbnail

함수

  • 내장함수와 사용자 정의함수로 분류된다
  • 매개변수에 어떤 값을 전달하면, 내부적인 처리를 진행하고, 결과를 반환한다
  • 단일행 함수(1개의 행당 결과 1개)와 복수행 함수(여러개의 행 입력시 1개의 결과)로 나뉜다
  • 단일행 함수는 문자함수, 숫자함수, 날짜함수, 변환함수, 일반함수로 분류한다

🕹️ SERVEROUTPUT 설정

SQL> SET SERVEROUTPUT ON;
➡️ 기본적으로 PL/SQL은 결과물을 보여주지 않기 때문에 결과물을 보고 싶다면 SERVEROUTPUT 설정을 ON 으로 설정해 주어야 한다

🕹️ Dual 테이블

함수 테스트시 DUAL 사용
➡️ 오라클 자체에서 제공되는 테이블이며, 함수를 이용해서 계산 결과값을 확인 할 때 사용이 가능하다

📌Oracle 내장함수

  • CREATE FUNCTION 기능을 이용하여 만든 함수가 아닌 Oracle 자체적으로 제공해주는 함수를 말한다
  • 수학함수, 문자함수, 날짜함수, 형변환함수들이 있다

📌사용자 정의함수

  • Oracle 내장함수들을 사용하다보면 한계가 있다
    사용자 정의함수를 이용하여 사용자가 필요한 대로 함수 만들어 사용가능하다
    ➡️ 함수는 반드시 값이 변환되니 사용자 정의 함수 생성시 리턴되는 데이터 타입을 지정해주어야 한다

📌 함수 표기 형식

/*
    -- 함수 구조
    CREATE [OR REPLACE] FUNCTION function_name[parameters]
        RETURN 리턴되는 데이터 타입
    IS[AS]
        선언부
    BEGIN
        실행부
    EXCEPTION
        예외처리
    END;
*/

📋 실습

[실습1] 포맷 변경 후 반환하는 함수

오늘날짜를 특정 포맷으로 변경해서 반환하는 함수 생성

  • Oracle 내장함수 CURRENT_DATE 이용하여 현재날짜 가져오고 문자로 변환
  • 반환값은 임시변수 V_DATE를 생성하며 데이터 타입은 문자형태(VARCHAR2)로 지정
CREATE OR REPLACE FUNCTION FUNC_TODAY RETURN VARCHAR2
IS
    V_DATE VARCHAR2(50); --임시변수
BEGIN
    SELECT TO_CHAR(CURRENT_DATE, 'YYYY"년" MM"월" DD"일"') INTO V_DATE
    FROM DUAL;
    RETURN V_DATE;
END;
/

--생성한 함수 실행해보기
SELECT FUNC_TODAY FROM DUAL;

[실습2] 시퀀스 값 반환하는 함수

SEQ_BOARDTBL_NO의 시퀀스를 호출해서 시퀀스의 값을 반환하는 함수 생성

  • 시퀀스생성 수업내용 중 INSERT ALL 이용하여 데이터 입력시 SEQ_BOARDTBL_NO.NEXTVAL 를 사용하여 일괄등록이 불가능 했다
    ➡️ 시퀀스를 일괄등록하는 경우 순차적 처리가 불가능한 반면,
    함수는 수행 후 반환값이 반환되기 때문에 순차적 처리가 가능하다
    💡 시퀀스 값을 반환하는 함수를 만들어 데이터 일괄등록INSERT ALL시 시퀀스 함수를 사용하여 일괄등록이 가능하다

시퀀스 반환함수 생성

SEQ_BOARDTBL_NO의 시퀀스를 호출해서 값을 반환하는 함수 생성

CREATE OR REPLACE FUNCTION FUNC_BOARDSEQ RETURN NUMBER 
IS
BEGIN
    RETURN SEQ_BOARDTBL_NO.NEXTVAL;
END;
/

FUNC_BOARDSEQ 함수 사용하여 데이터 일괄등록

INSERT ALL
    INTO BOARDTBL 
        VALUES(FUNC_BOARDSEQ, '제목', '작성자', 1, CURRENT_DATE, '내용')
    INTO BOARDTBL 
        VALUES(FUNC_BOARDSEQ, '제목', '작성자', 1, CURRENT_DATE, '내용')
    INTO BOARDTBL 
        VALUES(FUNC_BOARDSEQ, '제목', '작성자', 1, CURRENT_DATE, '내용')
SELECT * FROM DUAL;

[실습3] 조건에 해당하는 값을 반환하는 함수

BOARDTBL에서 조회수가 가장 높은 글의 글번호를 찾는 함수 생성

  • 반환값은 임시변수 V_NO를 생성하며 데이터 타입은 숫자형태(NUMBER)로 지정
CREATE OR REPLACE FUNCTION FUNC_BOARD_HIT RETURN NUMBER
IS
    V_NO NUMBER; -- 임시변수 생성
BEGIN
    SELECT B.NO INTO V_NO FROM( 
        SELECT B.HIT, B.NO, RANK() OVER (ORDER BY B.HIT DESC) RNK FROM BOARDTBL B
        ) B WHERE B.RNK=1;
        RETURN V_NO;
END;
/

--생성한 함수 실행해보기
SELECT FUNC_BOARD_HIT FROM DUAL;

트리거(TRIGGER)

트리거는 데이터베이스 시스템에서 데이터의 입력(INSERT), 갱신(UPDATE), 삭제(DELETE) 등의 이벤트가 TABLE에 발생할 때 마다 자동적으로 수행되는 사용자 정의 프로시저이다

📌 트리거 표기 형식

CREATE [ OR REPLACE ]TRIGGER 트리거명
 BEFORE | AFTER
 [ 동작(INSERT,UPDATE,DELETE) ] ON 테이블명 
[ REFERENCING  NEW | OLD  TABLE AS 테이블명 ]
[ FOR EACH ROW ]
[ WHEN 조건식 ]
트리거 BODY문

📌 트리거 삭제

DROP TRIGGER 트리거명;

트리거는 함수와는 다르며 절차적으로 작업을 진행한다

  • 함수 : 일정 기능만 사용, 반환값 있음
    ⇒ 단순 합 구하기,.. (간략화된 기능)
  • 프로시저 : 절차적 작업 진행
    ⇒ DB INSERT ,.. (절차적인것을 순차적으로 한번에 진행)
  • 트리거 : 절차적이다, 함수가 아님, 백업용 또는 통계에 사용

📋 실습

[실습1] 데이터 추가시 백업실행하는 트리거

BOARDTBL의 테이터를 BOARDTBL_BACK에 백업하여 보관하는 트리거 생성

  1. BOARDTBL_BACK생성을 위해 테이블 복사
    ➡️ DDL(칼럼 등) + DML(내용) 동시에 생성 됨
    ➡️ 쿼리 결과 기반으로 DDL과 DML 수행
  2. 트리거 생성
    이때 COMMIT은 자동으로 수행되니 해줄 필요없다
1.테이블 복사 
CREATE TABLE BOARDTBL_BACK AS
    SELECT B.* FROM BOARDTBL B ORDER BY NO DESC;

--복사된 BOARDTBL_BACK 테이블의 내용 확인
SELECT BK.* FROM BOARDTBL_BACK BK ORDER BY NO DESC;

2. 트리거 생성
CREATE OR REPLACE TRIGGER TRI_BOARDTBL_BACK
    AFTER INSERT ON BOARDTBL FOR EACH ROW
BEGIN
    INSERT INTO BOARDTBL_BACK VALUES( :new.NO, :new.TITLE, :new.WRITER, :new.HIT, CURRENT_DATE, :new.CONTENT);
END;
/

INSERT 될 때 마다 같이 자동으로 데이터가 추가되는것을 확인할 수 있다

[실습2] 데이터 추가, 수정, 삭제시 동기화 하는 트리거

데이터 추가 및 수정 또는 삭제 시 데이터 동기화 되도록 하는 트리거 생성

  • OLD 변경전 값을 참조하는 변수명, NEW : 변경후 값을 참조하는 변수명
CREATE OR REPLACE TRIGGER TRI_BOARDTBL_BACK1
    AFTER INSERT OR UPDATE OR DELETE ON BOARDTBL FOR EACH ROW
BEGIN
    IF INSERTING THEN
        INSERT INTO BOARDTBL_BACK VALUES( :new.NO, :new.TITLE, :new.WRITER, :new.HIT, CURRENT_DATE, :new.CONTENT);
    ELSIF DELETING THEN --OLD. 사용시 지워진것을 알수 있다
        DELETE BOARDTBL_BACK WHERE NO=:OLD.NO;
    ELSIF UPDATING THEN
        UPDATE BOARDTBL_BACK SET TITLE=:NEW.TITLE WHERE NO=:OLD.NO;
    END IF;
END;
/

💻 Spring에서 SQL사용

BoardMapper에서 쿼리문 작성시 ;세미콜론 사용불가, COMMIT은 AUTO로 설정되어 있음

  • VScode 에서 mybatis는 기본적으로 insert, update, delete 반환값 int 사용한다

📋 [실습] 게시판 글쓰기

  • INSERT INTO 테이블명(컬럼명들) VALUES(추가할 값들)

📁 BoardDTO.java

@Getter // mybatis Mapper에서 사용함
@Setter // view 에서 사용
@ToString() //현재객체의 내용확인용
@NoArgsConstructor //생성자
@AllArgsConstructor

// @Data =>위 5개와 같은 내용이지만 세밀하게 컨트롤하지 못한다
public class BoardDTO {
    private Long no; // number => Long
    private String title = null;
    private String writer = null;
    private Long hit = 1L;
    private Date regdate = null;
    private String content = null;
    
}

📁 BoardMapper.java

    @Insert({"INSERT INTO BOARDTBL(NO, TITLE, WRITER, HIT, REGDATE, CONTENT) ",
            " VALUES(FUNC_BOARDSEQ, #{board.title}, #{board.writer}, #{board.hit}, CURRENT_DATE, #{board.content} )"
        })
    public int insertBoardOne(@Param("board") BoardDTO board);

📁 BoardTest.java

    @Test
    void insetTest(){
        BoardDTO board = new BoardDTO();
        board.setTitle("mapperTest");
        board.setContent("contentTest");
        board.setWriter("writerTest");

        int ret = bMapper.insertBoardOne(board);
        log.info(format, ret);
    }

📋 [실습1] 전체 글목록 조회

Oracle

SELECT B.* FROM BOARDTBL B;

📁 BoardMapper.java

    @Select({
        "<script>",
        " SELECT B.* FROM BOARDTBL B ",
        "</script>"
    })
    public List<BoardDTO> selectBoardList();

📁 BoardTest.java

    @Test
    void selectBoardList(){
        List<BoardDTO> boardList = bMapper.selectBoardList();
        log.info(format, boardList);
    }

📋 [실습2] 게시글 1개 조회

Oracle

SELECT B.* FROM BOARDTBL B WHERE NO = 71;

📁 BoardMapper.java

    @Select({
        "<script>",
        " SELECT B.* FROM BOARDTBL B WHERE B.NO = #{no} ",
        "</script>"
    })
    public BoardDTO selectBoardOne(@Param("no") Long no);

📁 BoardTest.java

    @Test
    void selectBoardOne(){
        BoardDTO boardOne = bMapper.selectBoardOne(71L);
        log.info(format, boardOne);
    }

📋 [실습3] 게시글 제목 검색결과 + 글목록 조회

Oracle

SELECT B.* FROM BOARDTBL B WHERE B.TITLE LIKE '%' || '제' || '%';

📁 BoardMapper.java

    @Select({
        "<script>",
        " SELECT B.* FROM BOARDTBL B WHERE B.TITLE LIKE '%' || #{text} || '%' ",
        "</script>"
    })
    public List<BoardDTO> searchBoardList(@Param("text") String text);

📁 BoardTest.java

    @Test
    void searchBoardList(){
        List<BoardDTO> searchBoardList = bMapper.searchBoardList("m");
        log.info(format, searchBoardList);
    }

📋 [실습4] 게시글 제목 검색결과 + 글목록 조회 + 페이지네이션

1.BOARDTBL에서 데이터중 TITLE에 text가 포함된 조건을 조회한다
➡️ text먼저 검색
2. text 검색 결과에 rown을 붙여준다
3. 2.에서 rown이 1~10인 데이터만 가져온다

Oracle

SELECT B.* FROM (
    SELECT B.*, ROW_NUMBER () OVER (ORDER BY NO DESC) ROWN 
    FROM BOARDTBL B WHERE B.TITLE LIKE '%' || 'p' || '%'
    ) B WHERE ROWN BETWEEN 1 AND 10;

📁 BoardMapper.java

    @Select({
        "<script>",
        " SELECT B.* FROM (SELECT B.*, ROW_NUMBER () OVER (ORDER BY NO DESC) ROWN FROM BOARDTBL B WHERE B.TITLE LIKE '%' || #{text} || '%') B WHERE ROWN BETWEEN #{start} AND #{start}+9 ",
        "</script>"
    })
    public List<BoardDTO> searchBoardPag(@Param("text") String text, @Param("start") int start);

📁 BoardTest.java

    @Test
    void searchBoardPag(){
        List<BoardDTO> searchBoardPag = bMapper.searchBoardPag("2",1);
        log.info(format, searchBoardPag);
    }

📋 [실습5] 게시글 제목 검색결과 + 글개수 조회

Oracle

SELECT COUNT(*) CNT FROM BOARDTBL B WHERE B.TITLE LIKE '%' || '제' || '%' ;

📁 BoardMapper.java

    @Select({
        "<script>",
        " SELECT COUNT(*) CNT FROM BOARDTBL B WHERE B.TITLE LIKE '%' || #{text} || '%'  ",
        "</script>"
    })
    public long searchBoardCnt(@Param("text") String text);

📁 BoardTest.java

    @Test
    void searchBoardCnt(){
        long searchBoardCnt = bMapper.searchBoardCnt("제");
        log.info(format, searchBoardCnt);
    }

📋 [실습6] 게시글 조회수 증가

Oracle

UPDATE BOARDTBL SET HIT=HIT+1 WHERE NO=71;

--결과 조회
SELECT B.* FROM BOARDTBL B;

📁 BoardMapper.java

    @Update({
        " UPDATE BOARDTBL SET HIT=HIT+1 WHERE NO=#{no} "
    })
    public int updateBoardHit(@Param("no") Long no);

📁 BoardTest.java

    @Test
    void updateBoardHit(){
       int ret = bMapper.updateBoardHit(71L);
       log.info(format, ret);
    }

🤯 오류


int 값을 Integer로 바꿨을때 값이 null이 나왔다

➡️ BoardMapper의 조회수증가 코드 부분이 @Select로 되어있었다
@Update로 바꾸니 오류없이 작동한다

📋 [실습7] 이전글

Oracle

SELECT NVL(MAX(NO), 0) FROM BOARDTBL WHERE NO < 16;

📁 BoardMapper.java

    @Select({
        " SELECT NVL(MAX(NO), 0) FROM BOARDTBL WHERE NO > #{no} "
    })
    public Long selectBoardPrev(@Param("no") Long no);

📁 BoardTest.java

📋 [실습8] 다음글

Oracle

SELECT NVL(MIN(NO), 0) FROM BOARDTBL WHERE NO > 64;

📁 BoardMapper.java

    @Select({
        " SELECT NVL(MIN(NO), 0) FROM BOARDTBL WHERE NO > #{no} "
    })
    public Long selectBoardNext(@Param("no") Long no);

📁 BoardTest.java

이미지

이미지 보관 테이블용 시퀀스 생성

CREATE SEQUENCE SEQ_BOARDIMAGE_NO
START WITH 1 INCREMENT BY 1 NOCACHE NOMAXVALUE;

이미지 보관용 테이블 생성

테이블 생성시 BOARDTBL의 기본키를 외래키로 참조하기
PK_BOARDIMAGE_NO 기본키 이름 지정해줘야 임의로 생성이 안됨

CREATE TABLE BOARDIMAGETBL(
    NO NUMBER CONSTRAINT PK_BOARDIMAGE_NO PRIMARY KEY, 
    BRDNO NUMBER CONSTRAINT FK_BOARD_NO REFERENCES BOARDTBL(NO),
    IMAGENAME VARCHAR2(200),
    IMAGESIZE NUMBER,
    IMAGETYPE VARCHAR2(30),
    IMAGEDATA BLOB,
    REGDATE TIMESTAMP DEFAULT CURRENT_DATE
);

이미지 등록

Oracle

INSERT INTO BOARDIMAGETBL(NO, BRDNO, IMAGENAME, IMAGESIZE, IMAGETYPE, IMAGEDATA)
    VALUES(SEQ_BOARDIMAGE_NO.NEXTVAL, 71, '테스트', 0, 'image/jpeg', null);
COMMIT;

SELECT BI.* FROM BOARDIMAGETBL BI;

📁 BoardImageDTO.java

@Getter
@Setter
@ToString
@NoArgsConstructor
public class BoardImageDTO {
    // private protected public
    protected long no;
    long brdno;
    String imagename;
    int imagesize;
    String imagetype;
    byte[] imagedata; // BLOB == byte[], imagedata는 byte배열로 온다
    Date regdate;
}

📁 BoardImageMapper.java

    @Insert({
        " INSERT INTO BOARDIMAGETBL(NO, BRDNO, IMAGENAME, IMAGESIZE, IMAGETYPE, IMAGEDATA) ",
        " VALUES(SEQ_BOARDIMAGE_NO.NEXTVAL, ",
        " #{obj.brdno}, #{obj.imagename}, #{obj.imagesize}, ",
        " #{obj.imagetype}, #{obj.imagedata, jdbcType=BLOB}) "
    })
    public int insertBoardImageOne(
        @Param("obj") BoardImageDTO obj
    );

📁 BoardImageTest.java

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

    @Autowired
    ResourceLoader resourceLoader; // 업로드 전 이미지가 resource파일 안에 있는지 확인!

    @Autowired
    BoardImageMapper biMapper;

    // 이미지 등록하기
    @Test
    public void insertimage() throws IOException{
        InputStream is =  resourceLoader.getResource(
            "classpath:/static/image/사과.jpg").getInputStream();
    
        BoardImageDTO obj = new BoardImageDTO();
        obj.setBrdno(71L); //외래키(게시글번호)
        obj.setImagedata(is.readAllBytes());
        obj.setImagesize(obj.getImagedata().length);
        obj.setImagetype("image/jpg");
        obj.setImagename("사과.jpg");

        int ret = biMapper.insertBoardImageOne(obj);
        log.info(format, ret);
    }

이미지 가져오기

이미지는 그냥 가져올 수 없으며 수동으로 변환하여 가져온다
➡️ BoardImageDTO의 변수와 BOARDIMAGETBL의 컬럼값이 일치해야 데이터가 들어갈 수 있다

  • 새로운 변수를 생성하거나 변수명을 변경한경우 데이터가 들어갈 수 없다
  • DTO의 변수와 SQL테이블 컬럼값의 타입이 다르기 때문에 변환해줘야 한다
    (원래 모든 데이터를 Mapper에서 변환하여 넣었어야 하지만 DTO의 변수와 SQL테이블 컬럼값을 일치하게 맞췄기 때문에 생략했음)

📁 BoardImageMapper.java

이미지는 타입이 다르기 때문에 컬럼명이 같더라도 result를 통해 변환작업이 필요하다

    @Results({
        // @Result(property = "no1", column = "NO"), => 이와 같이 변수명과 컬럼명 다른경우 변환해준다
        @Result(property = "imagedata", column = "IMAGEDATA",
            jdbcType = JdbcType.BLOB, javaType = byte[].class
        )
    })
    @Select({
        " SELECT BI.* FROM BOARDIMAGETBL BI WHERE NO=#{no} "
    })
    public BoardImageDTO selectBoardImageOne(@Param("no") long no);

📁 BoardImageTest.java

    @Test
    public void selectImageOne() {
        BoardImageDTO obj = biMapper.selectBoardImageOne(3);
        log.info(format, obj);
    }

이미지 여러개 등록하기

INSERT ALL은 시퀀스 사용 불가하니 함수 만들어서 시퀀스 사용해준다

Oracle

  • 이미지 여러개 등록용 시퀀스 함수 생성
    ➡️ SEQ_BOARDIMAGE_NO의 시퀀스를 호출해서 값을 반환하는 함수 생성
CREATE OR REPLACE FUNCTION FUNC_BOARDIMAGESEQ RETURN NUMBER 
IS
BEGIN
    RETURN SEQ_BOARDIMAGE_NO.NEXTVAL;
END;
/

📁 BoardImageMapper.java

  • 이미지 여러개 등록 = 반복문 이용
  • INSERT ALL ➡️ into 부분이 반복되어 입력, 반복횟수는 list만큼 반복
  • separator = 구분자 (insert all은 공백으로 구분된다)
    @Insert({
        "<script>",
        " INSERT ALL ",
        "<foreach collection='list' item='obj' separator=' '>",
        " INTO BOARDIMAGETBL(NO, BRDNO, IMAGENAME, IMAGESIZE, IMAGETYPE, IMAGEDATA) ",
        " VALUES(FUNC_BOARDIMAGESEQ, ",
        " #{obj.brdno}, #{obj.imagename}, #{obj.imagesize}, ",
        " #{obj.imagetype}, #{obj.imagedata, jdbcType=BLOB}) ",
        " </foreach> ",
        "</script>"
    })
    public int insertImageBatch(
        @Param("list") List<BoardImageDTO> list);

📁 BoardImageTest.java

📋 [실습1] 이미지 삭제

데이터 삭제 후 롤백한 경우 시퀀스는 이전의 번호로 다시 사용할 수 없다
이미 지나간 번호라고 보기때문에 새 번호가 부여된다
1,2,3 중 2를 삭제 후 롤백시키면 데이터에는 1,4,3번이 존재한다

Oracle

DELETE FROM BOARDIMAGETBL WHERE NO=4;

📁 BoardImageMapper.java

    @Delete({
        " DELETE FROM BOARDIMAGETBL WHERE NO=#{no} "
    })
    public int deleteImage(@Param("no") long no);

📁 BoardImageTest.java

    // 이미지 삭제하기
    @Test
    public void deleteImage(){
        int ret = biMapper.deleteImage(4);
        log.info(format, ret);
    }

📋 [실습2] 이미지 수정

📋 [실습3] 물품번호에 해당하는 전체 이미지 조회

profile
Yeoonnii
이전 포스트

20220908 [Spring Boot, Oracle]

다음 포스트

20220914 [Spring Boot, Oracle, MyBatis]

0개의 댓글