55일차 xml로 작성해보기 results로 대소문자변경(CLOB포함)

oracle

55일차 xml로 작성해보기 results로 대소문자변경(CLOB포함)

비루블 2022. 9. 14. 17:14

요약정리

sql을 java와 쓰다보니 에러메세지로 에러잡기 힘들었음.. 오류를 조금 더 구체적으로 보여줬으면 하는 바램

시험
웹보드ora_20220914

마이바티스는 버전이 두개
지금은 java파일로 만들고 있고
원래는 xml으로 만들어 쓰는 것이 보통이였음.
그래서 자료 찾아 볼때도 xml구조가 훠어어얼씬 더 많음.

여기서 xml 작성할때 주의점은
파라미터로 데이터를 받아올때 하나 밖에 못 받아옴
그래서 변수를 map.???로 쓴게 아니라 바로 ??? 으로 넣어줌

xml에 있는 쿼리문이 mapper에 들어가게 할것임
구체적으로
mappers>ItemMapper.xml에 있는 쿼리문이 mapper>ItemMapper.java에 들어가게 하는 것임.

그러면 쓸때는 mapper의 java파일에 있는 코드를 쓰면 되겠지?
알아서 xml이 mapper.java에 따라 붙음

출력값이 지금 oracle에서 받아오는거라 전부 대문자로 출력

이걸 바꾸려면

지금 이거 하는데 아까한 출력값 소문자로 고치는 result
이걸 여기서도 사용하려고 하는데
이건
xml에서 해줘야함
데이터 이동 순서 xml의 select -> result -> mapper -> test
찾아볼때 mybatis resultmap이라고 치면 됨.

날짜계산 관련 sql

https://coding-factory.tistory.com/440

[Oracle] 오라클 날짜를 계산하는 다양한 방법 (연산자, 함수)

날짜 계산 (연산자) SELECT SYSDATE + 100, --현재날짜 +100일 SYSDATE - 100, --현재날짜 -100일 SYSTIMESTAMP + 100, --현재날짜 +100일 SYSTIMESTAMP - 100 --현재날짜 -100일 FROM DUAL 위와같이 + , - 연산..

coding-factory.tistory.com

오전일과

시험 문제 예시(가장 아래에 적어놓음)

답글 작성용 테이블 생성

CREATE TABLE BOARDREPLYTBL(
    NO NUMBER CONSTRAINT PK_BOARDREPLY_NO PRIMARY KEY,
    BRDNO NUMBER CONSTRAINT FK_BOARDREPLY_BRDNO REFERENCES BOARDTBL(NO),
    CONTENT CLOB,
    WRITER VARCHAR2(100),
    REGDATE TIMESTAMP DEFAULT CURRENT_DATE
);

답글번호용 시퀀스 생성

CREATE SEQUENCE SEQ_BOARDREPLY_NO START WITH 1001 INCREMENT BY 1 NOMAXVALUE NOCACHE;

BoardReplyMapper.java

package com.example.mapper;

import java.util.Date;
import java.util.List;
import java.util.Map;

import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Result;
import org.apache.ibatis.annotations.Results;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.type.JdbcType;

@Mapper
public interface BoardReplyMapper {
    
    // -- 답글 3개 추가
    // DTO 대신에 Map 사용하기
    @Insert({
        " INSERT INTO BOARDREPLYTBL(NO, BRDNO, CONTENT, WRITER) ",
        " VALUES(SEQ_BOARDREPLY_NO.NEXTVAL, #{map.brdno}, ",
        " #{map.content}, #{map.writer} )"
    })
    public int insertReply(@Param("map") Map<String, Object> map);


    // -- 답글 수정
    //@Update({})

    // -- 답글 삭제
    //@Delete({})

    // -- 게시물 번호에 해당하는 답글 목록
    // 컬럼명이 map의 키값이 됨. CLOB표시 안됨
    @Results({
        @Result(property = "no", column = "NO", jdbcType = JdbcType.NUMERIC, javaType = Long.class),
        @Result(property = "brdno", column = "BRDNO", jdbcType = JdbcType.NUMERIC, javaType = Long.class),
        @Result(property = "content", column = "CONTENT", jdbcType = JdbcType.CLOB, javaType = String.class ),
        @Result(property = "writer", column = "WRITER", jdbcType = JdbcType.VARCHAR, javaType = String.class ),
        @Result(property = "regdate", column = "REGDATE", jdbcType = JdbcType.TIMESTAMP, javaType = Date.class ),
    })
    @Select({
        "<script>",
            "SELECT BR.* FROM BOARDREPLYTBL BR WHERE BR.BRDNO=#{brdno}",
        "</script>"
    })
    public List<Map<String, Object>> selectReplyList(
        @Param("brdno") Long brdno);





    // -- 게시물 + 답글 inner join
    /* SQL문
        SELECT B.NO, B.TITLE, B.WRITER, B.HIT, BR.CONTENT, BR.WRITER REPLYWRITE
        FROM BOARDTBL B 
        INNER JOIN BOARDREPLYTBL BR
        ON B.NO=BR.BRDNO
        WHERE B.NO=69;
    */    
    @Select({
        "<script>",
            " SELECT B.NO, B.TITLE, B.WRITER, B.HIT, BR.CONTENT, BR.WRITER REPLYWRITE ",
            " FROM BOARDTBL B  ",
            " INNER JOIN BOARDREPLYTBL BR ",
            " ON B.NO=BR.BRDNO ",
            " WHERE B.NO=#{brdno} ",  
        "</script>"
    })
    public List<Map<String, Object>> selectReplyJoinList(
        @Param("brdno") Long brdno);

}

MyBoardReplyTest.java

package com.example.boot_20220827;

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

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

import com.example.mapper.BoardReplyMapper;
import com.example.service.BoardReplyService;

import lombok.extern.slf4j.Slf4j;

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

    @Autowired
    BoardReplyMapper brMapper;

    @Autowired
    BoardReplyService brService;

    // 댓글 추가(java파일 방식)
    @Test
    public void inserttest(){
        Map<String, Object> map = new HashMap<>();
        map.put("brdno", 5L);
        map.put("content", "댓글내용2");
        map.put("writer","댓글작성자2");
        int ret = brMapper.insertReply(map);
        log.info(format, ret);
    }
    // 댓글 추가(xml파일 방식)
    @Test
    public void insertxmltest(){
        Map<String, Object> map = new HashMap<>();
        map.put("brdno", 5L);
        map.put("content", "댓글내용3");
        map.put("writer","댓글작성자3");
        int ret = brService.insertReply(map);
        log.info(format, ret);
    }

    
    // 댓글 업데이트
    @Test
    public void updatetest(){
        Map<String, Object> map = new HashMap<>();
        map.put("no", 10012L);
        map.put("content", "변경내용2");
        map.put("writer","변경작성자2");
        int ret = brMapper.updateBoardReply(map);
        log.info(format, ret);
    }

    // 댓글 삭제
    @Test
    public void deletetest(){
        long brdno = 10012L;
        int ret = brMapper.deleteBoardReply(brdno);
        log.info(format, ret);
    }
    
    // 댓글 목록
    @Test
    void selecttest(){
        long brdno = 5L;
        List<Map<String, Object>> list = brMapper.selectReplyList(brdno);
        for(Map<String, Object> map : list){
            log.info(format, map.toString());
        }
    }

    // 댓글 목록 + 게시글 정보
    @Test
    void selectReplyJoinListTest(){
        long brdno = 5L;
        List<Map<String, Object>> list = brMapper.selectReplyJoinList(brdno);
        for(Map<String, Object> map : list){
            log.info(format, map.toString());
        }
    }
}

oracle 개념정리

-- SQL(Structured Query Language)의 종류 : 
-- 데이터 정의(DDL), 데이터 조작(DML), 데이터 제어(DCL), 트랜잭션 제어어(TCL)

-- 데이터 제어어(Data Control Language) : 사용자에게 권한 생성 혹은 권한 삭제 명령어.
CREATE : 계정 생성
DROP : 계정 삭제
GRANT : 권한 생성
REVOKE : 권한 삭제


-- 데이터 정의어(Data Definition Language) : 데이터의 구조를 정의하기 위한 테이블 생성, 수정, 삭제 명령어.
CREATE : 테이블 생성
DROP : 테이블 삭제
ALTER : 테이블 수정
TRUNCATE : 테이블에 있는 모든 데이터 삭제
RENAME : 테이블명 변경


-- 데이터 조작어(Data Manipulation Language) : 데이터 추가, 조회,  수정 및 삭제를 위한 명령어.
-- commit 또는 rollback
SELECT : 데이터 조회
INSERT : 데이터 입력
UPDATE : 데이터 수정
DELETE : 데이터 삭제


-- 트랜잭션 제어어(Transaction Control Language)
COMMIT : 적용하기
ROLLBACK : 되돌리기
SAVEPOINT : 저장점을 정의

xml mapper는 resources아래에 만듬
mappers> boardreplyMapper.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<!-- 
    public interface BoardReplyMapper{
        @Insert({   })
        public int insertReply( Map<String, Object>  map);

        @Select({   })
        public ?? selectReplyList( Long brdno );
    }
-->

<mapper namespace="BoardReplyMapper">
    <insert id="insertReply" parameterType="Map">
        INSERT INTO BOARDREPLYTBL(NO, BRDNO, CONTENT, WRITER)
        VALUES(SEQ_BOARDREPLY_NO.NEXTVAL, #{brdno}, 
        #{content}, #{writer} )
    </insert>

    <select id="selectReplyList" parameterType="Long"
        resultType="Map">
        SELECT BR.* FROM BOARDREPLYTBL BR WHERE BR.BRDNO=#{brdno}
    </select>    
</mapper>

boot_20220827폴더 아래에 MyBatisConfig.java 만듬
// 아래걸 적어야 서버가 돌아갈때 자동으로 아래 코드도 실행
@Configuration

근데 이게 꼬여버려서 따로 폴더를 만들어주고 거기로 옮겨줌. (위 사진 참고)

package com.example.config;

import javax.sql.DataSource;

import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.Resource;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;

// 아래걸 적어야 서버가 돌아갈때 자동으로 아래 코드도 실행
@Configuration
public class MyBatisConfig {

	@Bean
	public SqlSessionFactory sqlSessionFactory(DataSource dataSource) throws Exception {
		System.out.println("datasource configuration");
		SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
		sqlSessionFactoryBean.setDataSource(dataSource);
		
		// xml mappers 위치 설정  ex) resources폴더에 /mappers/memberMapper.xml
		Resource[] arrResource = new PathMatchingResourcePatternResolver().getResources("classpath:/mappers/*Mapper.xml");
		sqlSessionFactoryBean.setMapperLocations(arrResource);
		return sqlSessionFactoryBean.getObject();
	}
}

xml 작성(위 boardreply.xml 참고)

service>BoardReplyService.java 만듬

package com.example.service;

import java.util.List;
import java.util.Map;

import org.apache.ibatis.session.SqlSessionFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

@Service
public class BoardReplyService {
    
    // xml로 되어있는 mapper을 호출하기 위한 객체
    @Autowired
    SqlSessionFactory sqlSessionFactory;

    public int insertReply(Map<String, Object> map){
        return sqlSessionFactory.openSession()
            .insert("BoardReplyMapper.insertReply", map);
    }

    public List<Map<String, Object>> 
        selectReplyList(Long brdno){
        return sqlSessionFactory.openSession()
            .selectList("BoardReplyMapper.selectReplyList", brdno);
    }        
}

여기서 xml 작성할때 주의점은
파라미터로 데이터를 받아올때 하나 밖에 못 받아옴
그래서 변수를 map.???로 쓴게 아니라 바로 ??? 으로 넣어줌

MyBoardReplyTest.java

package com.example.boot_20220827;

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

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

import com.example.mapper.BoardReplyMapper;
import com.example.service.BoardReplyService;

import lombok.extern.slf4j.Slf4j;

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

    @Autowired
    BoardReplyMapper brMapper;

    @Autowired
    BoardReplyService brService;

    // 댓글 추가(java파일 방식)
    @Test
    public void inserttest(){
        Map<String, Object> map = new HashMap<>();
        map.put("brdno", 5L);
        map.put("content", "댓글내용2");
        map.put("writer","댓글작성자2");
        int ret = brMapper.insertReply(map);
        log.info(format, ret);
    }
    // 댓글 추가(xml파일 방식)
    @Test
    public void insertxmltest(){
        Map<String, Object> map = new HashMap<>();
        map.put("brdno", 5L);
        map.put("content", "댓글내용3");
        map.put("writer","댓글작성자3");
        int ret = brService.insertReply(map);
        log.info(format, ret);
    }

    
    // 댓글 업데이트
    @Test
    public void updatetest(){
        Map<String, Object> map = new HashMap<>();
        map.put("no", 10012L);
        map.put("content", "변경내용2");
        map.put("writer","변경작성자2");
        int ret = brMapper.updateBoardReply(map);
        log.info(format, ret);
    }

    // 댓글 삭제
    @Test
    public void deletetest(){
        long brdno = 10012L;
        int ret = brMapper.deleteBoardReply(brdno);
        log.info(format, ret);
    }
    
    // 댓글 목록
    @Test
    void selecttest(){
        long brdno = 5L;
        List<Map<String, Object>> list = brMapper.selectReplyList(brdno);
        for(Map<String, Object> map : list){
            log.info(format, map.toString());
        }
    }

    // 댓글 목록 + 게시글 정보
    @Test
    void selectReplyJoinListTest(){
        long brdno = 5L;
        List<Map<String, Object>> list = brMapper.selectReplyJoinList(brdno);
        for(Map<String, Object> map : list){
            log.info(format, map.toString());
        }
    }
}

오후일과

위에꺼 다 꼬여서
그냥 item으로 넘어감
itemVo DTO 생성

package com.example.vo;

import java.util.Date;

import lombok.Getter;
import lombok.NoArgsConstructor;
import lombok.Setter;
import lombok.ToString;

@Getter
@Setter
@ToString
@NoArgsConstructor
public class ItemDTO {
    
    Long no;
    String name;
    String content;
    Long price;
    Long quantity;
    Date regdate;
}

itemmapper.java만듬 (실습이라 부정확 )

package com.example.mapper;

import java.util.List;
import java.util.Map;

import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Mapper;

import com.example.vo.ItemDTO;

@Mapper
public interface ItemMapper {
    
    // 1개 조회
    public ItemDTO selectOneItem(Long no);

    // 재고수량이 n개 미만인 것 조회하기
    public List<ItemDTO> selectQuantityList(Long quantity);
    
    // ex)9월 14일 것만 가져오기
    public List<ItemDTO> selectRegdateList(int regdate);


    // 날짜별 금액합계, 금액평균, 수량, 날짜 조회하기
    public List<Map<String, Object>> selectPriceGroup();
    
    
    // 골뱅이 안할꺼야~~~~~~~~
    // xml에서 들고오면 그마니야~~~~~~~~~~~~
    public List<ItemDTO> selectListItem();



    // xml mapper는 2개를 전달할 수 없음
    // @Param을 정의할 필요가 없음.
    public int insertItem( ItemDTO item );
}

mappers>itemmapper.xml만듬 (실습이라 부정확)

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<!-- 
    public interface BoardReplyMapper{
        @Insert({   })
        public int insertReply( Map<String, Object>  map);

        @Select({   })
        public ?? selectReplyList( Long brdno );
    }
-->

<mapper namespace="com.example.mapper.ItemMapper">
    <!-- 아이템 등록 -->
    <insert id="insertItem" parameterType="com.example.vo.ItemDTO">
        INSERT INTO ITEMTBL(NO, NAME, CONTENT, PRICE, QUANTITY, REGDATE)
        VALUES(SEQ_ITEMTBL_NO.NEXTVAL, #{name}, 
        #{content}, #{price}, #{quantity}, CURRENT_DATE )
    </insert>

    <!-- 아이템 리스트 들고오기 -->
    <!-- <resultMap id="resultMap1" type="com.example.vo.ItemDTO">
        <result property="no" column="NO" jdbcType="NUMERIC" javaType="Long" />
    </resultMap>
    <select id="selectListItem" resultMap="resultMap1"> -->
    <select id="selectListItem" resultType="com.example.vo.ItemDTO">
        SELECT * FROM ITEMTBL
    </select>

    <!-- 1개 조회 -->
    <select id="selectOneItem" parameterType="Long" resultType="com.example.vo.ItemDTO">
        SELECT * FROM ITEMTBL WHERE NO=#{no}
    </select>

    <!-- 재고수량이 n개 미만인 것 조회 -->
    <select id="selectQuantityList" parameterType="Long" resultType="com.example.vo.ItemDTO">
        SELECT I.* FROM ITEMTBL I WHERE I.QUANTITY &lt; #{quantity}
    </select>

    <!-- ex)9월 14일 것만 조회 -->
    <select id="selectRegdateList" parameterType="int" resultType="com.example.vo.ItemDTO">
        <!-- SELECT * FROM ITEMTBL WHERE REGDATE BETWEEN TO_DATE('20220914') AND TO_DATE('20220915'); -->
        SELECT * FROM ITEMTBL WHERE REGDATE BETWEEN TO_DATE(#{regdate}) AND TO_DATE(#{regdate}+1)
    </select>

    <!-- 날짜별 금액합계, 금액평균, 수량, 날짜 조회하기 -->
    <select id="selectPriceGroup" resultType="Map">
        select 
            TO_CHAR(REGDATE, 'YYYYMMDD'),
            SUM(PRICE),
            ROUND(AVG(PRICE)),
            count(*)
        from 
            ITEMTBL
        where
            REGDATE <= TO_CHAR(SYSDATE + 1 ,'yyyy/mm/dd')
        GROUP BY
            to_char(REGDATE, 'YYYYMMDD')
    </select>


</mapper>

xml에 있는 쿼리문이 mapper에 들어가게 할것임
구체적으로
mappers>ItemMapper.xml에 있는 쿼리문이 mapper>ItemMapper.java에 들어가게 하는 것임.

그러면 쓸때는 mapper의 java파일에 있는 코드를 쓰면 되겠지?
알아서 xml이 mapper.java에 따라 붙음

지금 이거 하는데 아까한 출력값 소문자로 고치는 result
이걸 여기서도 사용하려고 하는데
이건
xml에서 해줘야함
데이터 이동 순서 xml의 select -> result -> mapper -> test
찾아볼때 mybatis resultmap이라고 치면 됨.

MyItemTest.java(실습이라 부정확)

package com.example.boot_20220827;

import java.util.List;
import java.util.Map;

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

import com.example.mapper.ItemMapper;
import com.example.vo.ItemDTO;

import lombok.extern.slf4j.Slf4j;

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

    @Autowired
    ItemMapper iMapper;

    // 아이템 등록
    @Test
    public void insertitem(){
        ItemDTO item = new ItemDTO();
        item.setName("물품명1");        
        item.setContent("물품내용1");        
        item.setPrice(1000L);        
        item.setQuantity(50L);        
        int ret = iMapper.insertItem(item);
        log.info(format, ret);
    }

    // 아이템 전체 조회
    @Test
    public void selectListItem(){
        List<ItemDTO> list = iMapper.selectListItem();
        for(ItemDTO item : list){
            log.info(format, item.toString());
        }
    }

    // 아이템 한개 조회
    @Test
    public void selectOneItem(){
        Long no = 1002L;
        ItemDTO item = iMapper.selectOneItem(no);
            log.info(format, item);
    }

    // 재고수량이 n개 미만인 것 조회하기
    @Test
    public void selectQuantityList(){
        Long quan = 150L;
        List<ItemDTO> list = iMapper.selectQuantityList(quan);
        for(ItemDTO item : list){
            log.info(format, item.toString());
        }
    }

    // ex)9월 14일 것만 가져오기
    @Test
    public void selectRegdateList(){
        int reg = 20220914;
        List<ItemDTO> list = iMapper.selectRegdateList(reg);
        for(ItemDTO item : list){
            log.info(format, item.toString());
        }
    }

    // 물품명 금액합계 조회하기
    @Test
    public void selectPriceGroup(){
        List<Map<String, Object>> list = iMapper.selectPriceGroup();
        log.info(format, list.toString());
    }
}

시험

0. 부서 테이블 생성
-- depttbl(부서) 테이블 ( 제약조건 없음 )
    -- deptno (부서번호), 숫자
    -- dname (부서명) 가변 길이(7자) 문자 VARCHAR2(7)


1. deptno 기본키 설정

2. address 주소 가변길이 30자

3. 부서명 가변길이 30 not null 설정

4. 자료 추가
-- 101 총무부 서울
-- 102 영업부 부산
-- 103 기획부 대구
-- 104 홍보부 서울



----------------------------------------------

5. 테이블 생성

-- emptbl(사원) 테이블 생성
    -- name (사원이름) 가변길이(10자) 문자 
    -- empno (사원코드) 숫자, 기본키
    -- deptno (부서코드) 숫자, not null 제약 조건
    -- pempno (상사코드) 숫자
    -- pay (급여) 숫자, not null 제약 조건
    -- regdate (등록일) timestamp
    -- position (직급) 가변길이(10자) 문자, “사원, 대리, 과장” 값만 가능


6. 시퀀스 생성
-- 시퀀스 생성 (시퀀스명 : SEQ_EMPTBL_NO, 시작값 10001, 증가값 1)


7. 10명의 사원 추가, 상사코드는 null로


8. depttbl의 address를 영업부는 "대구" 나머지는 "부산" 으로 변경


9. emptbl의 부서번호가 103인 항목 삭제


10. emptbl의 영업부 직원과 총무부 직원의 이름, 급여, 직급을 사원번호 순으로 내림차로 조회


11. emptbl의 급여에서 0~230 이면 5%, 231~300이면 10%, 301~400 15%, 400이상은 20%의 세금을 조회(사원번호, 사원명, 급여, 세금 표시) CASE사용


12. emptbl의 부서별 평균 급여와 인원수 출력 GROUP BY사용


13. emptbl의 부서별 급여 평균이 300이상인 조회(부서번호, 부서명, 급여 평균) 


14. 직급별 총급여, 평균급여, 인원수 출력 


15. 14번에서 조회한 내용을 기반으로 view생성(뷰 이름: EMPTBL_VIEW)

16. 직급별 급여가 높은 순으로 rank()출력 PARTITION사용

-- 인덱스, 함수 생성


--------------------------------------------------------

0.
CREATE TABLE depttbl(
    deptno NUMBER,
    dname VARCHAR2(7)
);


1.
ALTER TABLE depttbl ADD CONSTRAINT PK_DETPTBL_NO PRIMARY KEY(DEPTNO);

2. 
ALTER TABLE depttbl ADD address VARCHAR2(30);

3.
ALTER TABLE depttbl MODIFY dname VARCHAR2(30);
ALTER TABLE DEPTtbl MODIFY dname NOT NULL;

4.
INSERT INTO depttbl(deptno, dname, address) VALUES(101,'총무부','서울');


5.
CREATE TABLE emptbl(
    empno NUMBER CONSTRAINT PK_EMPTBL_EMPNO PRIMARY KEY, 
    name VARCHAR2(10),
    deptno NUMBER NOT NULL, 
    pempno NUMBER,
    pay NUMBER NOT NULL,
    position VARCHAR(10),
    regdate TIMESTAMP,
    CONSTRAINT CK_EMPTBL_POSITION CHECK ( position IN ('사원','대리','과장') )
);


8.
UPDATE DEPTTBL SET ADDRESS =
    CASE 
        WHEN (DEPTNO=101) THEN '대구'
        ELSE '부산'
    END;
COMMIT;

9.
DELETE FROM 테이블명 WHERE 조건;

10.
SELECT * FROM 테이블명 WHERE 조건 OR 조건 ORDER BY 컬럼 ASC|DESC;


11.
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;


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


13.
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;


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


15.
CREATE OR REPLACE VIEW 뷰이름 AS SELECT ....


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


-- 함수
CREATE OR REPLACE FUNCTION 함수명 RETURN 리턴타입 
IS
BEGIN
    RETURN 처리후 리턴할 값;
END;
/


-- 인덱스 생성
CREATE INDEX 인덱스명 ON 테이블명(컬럼명));

'oracle' 카테고리의 다른 글

54일차 insert all(seq문제), 트리거(프로시저), vscode에서 일괄 등록 (2)	2022.09.13
53일차 오라클 (0)	2022.09.08
50, 51일차 oracle 연결, 테이블, 그룹, 페이지 네이션 (0)	2022.09.07

현재글55일차 xml로 작성해보기 results로 대소문자변경(CLOB포함)

Today :
Yesterday :

내 블로그 - 관리자 홈 전환	`Q` `Q`
새 글 쓰기	`W` `W`

글 수정 (권한 있는 경우)	`E` `E`
댓글 영역으로 이동	`C` `C`

이 페이지의 URL 복사	`S` `S`
맨 위로 이동	`T` `T`
티스토리 홈 이동	`H` `H`
단축키 안내	`Shift` + `/` `⇧` + `/`

마루쓰