티스토리 뷰
-- ================ PL/SQL (반복문/IF문 쓸수 있다) =======================
-- ORACLE'S PROCEDURAL LANGUAGE EXTENSION TO SQL의 약자
-- -> 오라클 자체에 내장되어 있는 절차적 언어로써, SQL의 단점을 보완하여
-- SQL문장 내에서 변수의 정의(DECLARE), 조건처리, 반복처리 등을 지원함
-- PL/SQL의 유형
-- 1.익명블록(ANONYMOUS BLOCK) : 약간 인라인 느낌??
-- 2.프로시저(PROCEDURE) : 익명블록에 이름 붙여저장
-- 3.함수(FUNCTION) : 프로시저가 리턴값이 있으면 함수값
-- PL/SQL의 구조(익명블록)
-- 1. 선언부(선택) : DECLARE
-- 2. 실행부(필수) : BEGIN
-- 3. 예외처리부(선택): EXCEPTION
-- 4. END;(필수)
-- 5. /(필수)
--================= 조건 처리 ======================
--1.IF 조건문 THEN 실행문 END IF;
--2.IF 조건문 THEN 실행문 ELSE 실행문 END IF;
--3.IF 조건문 THEN 실행문 ELSIF 조건문 THEN 실행문 ELSE 실행문 END IF;
/*
4.CASE 변수
WHEN 값1 THEN 실행문1
WHEN 값2 THEN 실행문2
WHEN 값3 THEN 실행문3
END CASE;
*/
SET SERVEROUTPUT ON;
--PL/SQL에서 DBMS_OUTPUT.PUT_LINE으로 출력하기 전 실행해야 하는 명령어
--============급여등급 출력문을 CASE문으로 바꿔보기 ============
DECLARE--변수선언
EMPID EMPLOYEE.EMP_ID%TYPE;
ENAME EMPLOYEE.EMP_NAME%TYPE;
SAL EMPLOYEE.SALARY%TYPE;
SLEVEL VARCHAR2(2);
BEGIN--명령문
SELECT EMP_ID, EMP_NAME, SALARY
INTO EMPID, ENAME, SAL
FROM EMPLOYEE
WHERE EMP_ID = '&EMP_ID';
DBMS_OUTPUT.PUT_LINE('사번 : '||EMPID);
DBMS_OUTPUT.PUT_LINE('이름 : '||ENAME);
DBMS_OUTPUT.PUT_LINE('급여 : '||SAL);
SAL := FLOOR(SAL/1000000);
CASE SAL
WHEN 0 THEN SLEVEL := 'F';
WHEN 1 THEN SLEVEL := 'E';
WHEN 2 THEN SLEVEL := 'D';
WHEN 3 THEN SLEVEL := 'C';
WHEN 4 THEN SLEVEL := 'B';
ELSE SLEVEL := 'A';
END CASE;
DBMS_OUTPUT.PUT_LINE('급여등급 : '||SLEVEL);
-- SAL := SAL/10000;
-- IF(SAL>=0 AND SAL<=99) THEN SLEVEL := 'F';
-- ELSIF(SAL BETWEEN 100 AND 199) THEN SLEVEL := 'E';
-- ELSIF(SAL BETWEEN 200 AND 299) THEN SLEVEL := 'D';
-- ELSIF(SAL BETWEEN 300 AND 399) THEN SLEVEL := 'C';
-- ELSIF(SAL BETWEEN 400 AND 499) THEN SLEVEL := 'B';
-- ELSE SLEVEL := 'A';
-- END IF;
EXCEPTION--예외처리
WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('NO DATA FOUND~');
END;
/
--CTRL+F로 수정하기??
-- ================ PL/SQL의 반복문 =======================
-- 1. LOOP
-- 2. WHILE LOOP
-- 3. FOR LOOP
-- 1. LOOP
-- 예제 1~5까지 반복 출력하기
DECLARE
N NUMBER := 1;
BEGIN
LOOP
DBMS_OUTPUT.PUT_LINE(N);
N := N +1;
EXIT WHEN N>5;
-- IF(N>5)
-- THEN EXIT;
-- END IF;
END LOOP;
END;
/
-- @ 실습문제1
-- 1~10 사이의 난수를 5개 출력해보시오
-- DBMS_RANDOM.VALUE(N,M): N과 M-1 사이의 난수 생성
-- FOR문 써서
DECLARE
N NUMBER := 1;
BEGIN
LOOP
DBMS_OUTPUT.PUT_LINE(FLOOR(DBMS_RANDOM.VALUE(1,11)));
N := N +1;
EXIT WHEN N > 5;
END LOOP;
END;
/
-- 2. WHILE LOOP
-- 제어 조건이 TRUE인 동안만 문장이 반복실행됨
-- LOOP를 실행할 때 조건이 처음부터 FALSE이면 한번도 수행되지 않을 수 있음
-- EXIT절이 없어도 조건절에 반복문 중지 조건을 기술할 수 있음
-- WHILE (조건식) LOOP (실행문) END LOOP;
-- 1~5까지 WHILE LOOP로 출력해보기
DECLARE
N NUMBER :=1;
BEGIN
WHILE(N <= 5)LOOP
DBMS_OUTPUT.PUT_LINE(N);
N := N+1;
END LOOP;
END;
/
--@실습문제2
--사용자로부터 2~9사이의 수를 '입력받아' 구구단을 출력하시오
--2*1=2
--...
--2~9사이의 수를 입력하세요
DECLARE
N NUMBER :=1;
DAN NUMBER;
BEGIN
DAN := '&단';
IF(DAN BETWEEN 2 AND 9)
THEN
WHILE(N < 10)LOOP
DBMS_OUTPUT.PUT_LINE(DAN||'*'||N||'='||DAN*N);
N := N+1;
END LOOP;
ELSE DBMS_OUTPUT.PUT_LINE('2~9 사이의 수를 입력하세요.');
END IF;
END;
/
-- @실습문제3
-- 1 ~ 30까지의 수 중에서 홀수만 출력하시오~
DECLARE
N NUMBER := 0;
-- N NUMBER := 1;
BEGIN
WHILE(N<30) LOOP
N :=N+1;
CONTINUE WHEN MOD(N,2) =0;
DBMS_OUTPUT.PUT_LINE(N);
-- IF(MOD(N,2) !=0) --만약에 2로 나눴는데 0이 아니면
-- THEN DBMS_OUTPUT.PUT_LINE(N);
-- END IF;
-- N :=N+1;
END LOOP;
END;
/
--3. FOR LOOP
-- FOR LOOP문에서 카운트용 변수는 자동 선언되므로, 따로 선언할 필요가 없음
-- 카운트 값은 자동으로 1씩 증가함
-- FOR LOOP를 이용하여 1~5까지 출력해보시오
BEGIN
FOR D IN 1...5 --1...50,1...100으로 입력하면 그것까지 출력되어 편함!
LOOP
DBMS_OUTPUT.PUT_LINE(D);
END LOOP;
END;
/
-- @실습문제4
-- EMPLOYEE 테이블의 사번이 200 ~ 210인 직원들의 사원번호, 사원명, 이메일을 출력하시오~!
DECLARE
EINFO EMPLOYEE%ROWTYPE;
BEGIN
FOR N IN 200...210
LOOP
SELECT *
INTO EINFO
FROM EMPLOYEE
WHERE EMP_ID = N;
DBMS_OUTPUT.PUT_LINE(EINFO.EMP_ID
||' '||EINFO.EMP_NAME
||' '||EINFO.EMAIL);
END LOOP;
END;
/
-- @실습문제5
-- KH_NUMBER_TBL은 숫자타입의 컬럼 NO와 날짜타입의 컬럼 INPUT_DATE를 가지고 있다.
-- KH_NUMBER_TBL 테이블에 0 ~ 99 사이의 난수를 10개 저장하시오. 날짜는 상관없음.
CREATE TABLE KH_NUMBER_TBL(
NO NUMBER,
INPUT_DATE DATE DEFAULT SYSDATE
);
BEGIN
FOR N IN 1..10
LOOP
INSERT INTO KH_NUMBER_TBL
VALUES (FLOOR(DBMS_RANDOM.VALUE(0,100)), DEFAULT);
END LOOP;
END;
/
SELECT*FROM KH_NUMBER_TBL;
-- ================== PL/SQL 예외처리 ======================
-- 시스템 오류(메모리 초과, 인덱스 중복 키 등)는 오라클이 정의하는 에러로
-- 보통 PL/SQL 실행 엔진이 오류 조건을 탐지하여 발생시키는 예외임
-- 1. 미리 정의된 예외처리
-- 2. 사용자 정의 예외처리
-- 3. 미리 정의되지 않은 예외처리(심화)
/*
예외의 종류
1. NO_DATA_FOUND: SELECT INTO 문장의 결과로 선택된 행이 하나도 없을 경우
2. DUP_VAL_ON_INDEX: UNIQUE 인덱스가 설정된 컬럼에 중복 데이터를 입력할 경우
3. CASE_NOT_FOUND: CASE문장에서 ELSE 구문도 없고 WHEN절에 명시된 조건을 만족하는 것이 없을 경우
4. ACCESS_INTO_NULL: 초기화되지 않은 오브젝트에 값을 할당하려고 할 때
5. COLLECTION_IS_NULL: 초기화되지 않은 중첩 테이블이나 VARRAY같은 컬렉션을 EXISTS외에 다른 메소드로 접근을 시도할 경우
6. CURSOR_ALREADY_OPEN: 이미 오픈된 커서를 다시 오픈하려고 시도하는 경우
7. INVALID_CURSOR: 허용되지 않은 커서에 접근할 경우 (OPEN되지 않은 커서를 닫으려고 할 경우)
8. INVALID_NUMBER: SQL문장에서 문자형 데이터를 숫자형으로 변환할 때 제대로 된 숫자로 변환되지 않을 경우
9. LOGIN_DENIED: 잘못된 사용자명이나 비밀번호로 접속을 시도할 때
*/
BEGIN
INSERT INTO EMPLOYEE(EMP_ID, EMP_NAME, EMP_NO, PHONE, JOB_CODE, SAL_LEVEL)
VALUES(200, '이백용자', '991122-2334411', '01092928383', 'J5', 'S5');
EXCEPTION
WHEN DUP_VAL_ON_INDEX THEN DBMS_OUTPUT.PUT_LINE('이미 존재하는 사번입니다.');
END;
/
-- 오라클 인덱스 실습
-- 1. 100만개의 데이터를 넣을 테이블 생성
-- 2. 100만개의 데이터 삽입(PL/SQL 반복문)
-- 3. 인덱스 설정 전 테스트
-- 4. 인덱스 설정
-- 5. 인덱스 설정 후 테스트
-- #1. 테이블 생성
CREATE TABLE KH_CUSTOMER_TBL(
USER_ID VARCHAR2(20) PRIMARY KEY,
USER_PW VARCHAR2(30) NOT NULL,
USER_NAME VARCHAR2(50) NOT NULL,
USER_PHONE VARCHAR2(13),
USER_ADDR VARCHAR2(100),
REG_DATE TIMESTAMP DEFAULT SYSTIMESTAMP,
MOD_DATE TIMESTAMP DEFAULT SYSTIMESTAMP
);
DESC KH_CUSTOMER_TBL;
CREATE SEQUENCE SEQ_CUSTOMER_USERID
START WITH 1
INCREMENT BY 1
NOCYCLE
NOCACHE;
SELECT * FROM USER_SEQUENCES;
-- #2. 데이터 삽입(PL/SQL 반복문)
DECLARE
V_USERID VARCHAR2(200);
BEGIN
FOR N IN 1..1000000
LOOP
V_USERID := '1'||LPAD(SEQ_CUSTOMER_USERID.NEXTVAL, 9, '0');
INSERT INTO KH_CUSTOMER_TBL
VALUES(V_USERID, '0000', N||'용자'
, '010'||LPAD(FLOOR(DBMS_RANDOM.VALUE(0,10)),4,'2')
||LPAD(FLOOR(DBMS_RANDOM.VALUE(0,10)),4,'1')
, '서울시 중구 남대문로 '||N, DEFAULT, DEFAULT);
END LOOP;
COMMIT;
END;
/
SELECT COUNT(*)FROM KH_CUSTOMER_TBL;
SELECT *FROM KH_CUSTOMER_TBL;
SELECT LPAD((FLOOR(DBMS_RANDOM.VALUE(0.10)),4,'2') FROM DUAL;
-- #3 /#5
-- 인덱스 걸기 전(실행시간)/후 체크
EXPLAIN PLAN FOR
SELECT * FROM KH_CUSTOMER_TBL WHERE USER_NAME LIKE '22%용자';
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
-- #4
-- 인덱스 설정
CREATE INDEX IDX_CUSTOMER_USERNAME ON KH_CUSTOMER_TBL(USER_NAME);
춘대학교 풀기
--================ 5. TRIGGER ==================
-- 트리거의 의미: 방아쇠, 연쇄반응,..
-- 특정 이벤트나 DDL, DML 문장이 실행되었을 때
-- 자동적으로 어떤 일련의 동작(OPERATION), 처리가 수행되도록 하는 데이터베이스 객체 중 하나
-- 예시) 회원 탈퇴가 이루어질 경우 회원 탈퇴 테이블에 대한
-- 기본 회원정보가 자동으로 입력되도록 설정하거나 데이터 변경이 있을 때,
-- 조작한 데이터에 대한 이력을 남기는 경우
/*
사용방법
CREATE OR REPLACE TRIGGER 트리거명
BEFORE(OR AFTER)
DELETE(OR UPDATE OR INSERT) ON 테이블명
[FOR EACH ROW]
BEGIN
(실행문)
END;
/
*/
-- 예제. 사원(EMPLOYEE) 테이블에 새로운 데이터가 들어오면 '신입사원이 입사하였습니다.'를 출력하기
ROLLBACK;
BEGIN
INSERT INTO EMPLOYEE(EMP_ID, EMP_NAME, EMP_NO, PHONE, JOB_CODE, SAL_LEVEL)
VALUES (224, '이십사용자', '971122-1223344', '01082827373', 'J5', 'S5');
DBMS_OUTPUT.PUT_LINE('신입사원이 입사하였습니다.');
END;
/
-- PL/SQL문을 실행하는 것이 아니라 INSERT문만 실행해도 메시지가 나오도록 할 수 있음
-- -> 트리거 사용
CREATE OR REPLACE TRIGGER TRG_EMP_NEW
AFTER
INSERT ON EMPLOYEE
FOR EACH ROW
BEGIN
DBMS_OUTPUT.PUT_LINE('신입사원이 입사하였습니다.');
END;
/
--만들어진 트리거를 데이터 딕셔너리 뷰를 이용해서 조회 가능
SELECT*FROM USER_TRIGGERS;
INSERT INTO EMPLOYEE(EMP_ID, EMP_NAME, EMP_NO, PHONE, JOB_CODE, SAL_LEVEL)
VALUES(224, '이십사용자', '971122-1223344', '01082827373', 'J5', 'S5');
--ORA-00001: 무결성 제약 조건(KH.EMPLOYEE_PK)에 위배됩니다
ROLLBACK;
DESC EMPLOYEE;
-- 예제2. EMPLOYEE 테이블에 급여 정보가 변경되면
-- 전후 정보를 화면에 출력하는 트리거를 생성하시오.
ROLLBACK;
-- 서브쿼리를 이용한 테이블 만들기
CREATE TABLE EMP_COPY
AS SELECT * FROM EMPLOYEE;
SELECT * FROM EMP_COPY;
CREATE OR REPLACE TRIGGER TRG_EMP_SALINFO
AFTER
UPDATE ON EMP_COPY
FOR EACH ROW
BEGIN
DBMS_OUTPUT.PUT_LINE('변경 전 : '||:OLD.SALARY);
DBMS_OUTPUT.PUT_LINE('변경 후 : '||:NEW.SALARY);
END;
/
--DD VIEW로 생성확인
SELECT * FROM USER_TRIGGERS;
UPDATE EMP_COPY
SET SALARY = SALARY * 2
WHERE EMP_NAME = '방명수';
ROLLBACK;
-- ================= 의사레코드 OLD, NEW ====================
-- FOR EACH ROW 사용
-- 1.INSERT: OLD -> NULL, NEW -> 변경 후의 레코드
-- 2.UPDATE: OLD -> 변경 전의 레코드, NEW -> 변경 후의 레코드
-- 3.DELETE: OLD -> 변경 전의 레코드, NEW -> NULL
-- @실습예제1
-- 1. 제품 PRODUCT 테이블은 숫자로 된 PCODE 컬럼이 있고 PRIMARY KEY로 지정
-- 문자열 크기 30인 PNAME인 컬럼, 문자열 크기 30인 BRAND 컬럼, 숫자로 된 PRICE 컬럼,
-- 숫자로 되어 있고 기본값이 0인 STOCK컬럼이 있음.
CREATE TABLE PRODUCT(
PCODE NUMBER PRIMARY KEY,
PNAME VARCHAR2(30),
BRAND VARCHAR2(30),
PRICE NUMBER,
STOCK NUMBER DEFAULT 0
);
DESC PRODUCT;
-- 2. 제품 입출고 PRODUCT_IO 테이블은 숫자로 된 IOCODE 컬럼이 있고 PRIMARY KEY로 지정
-- 숫자로 된 PCODE컬럼, 날짜로 된 PDATE 컬럼, 숫자로 된 AMOUNT컬럼, 문자열 크기가 10인
-- STATUS 컬럼이 있음. STATUS 컬럼은 입고 또는 출고만 입력가능
-- PCODE는 PRODUCT 테이블의 PCODE를 참고하여 외래키로 설정되어 있음
CREATE TABLE PRODUCT_IO(
IOCODE NUMBER CONSTRAINT PK_IOCODE PRIMARY KEY,
PCODE NUMBER CONSTRAINT FK_PIO_PCODE REFERENCES PRODUCT(PCODE),
PDATE DATE,
AMOUNT NUMBER,
STATUS VARCHAR2(10)
);
RENAME PROCUCT_IO TO PRODUCT_IO;
DESC PRODUCT_IO;
-- 3. 시퀀스는 SEQ_PRODUCT_PCODE, SEQ_PRODUCTIO_IOCODE라는 이름으로 기본값으로 설정되어있음.
CREATE SEQUENCE SEQ_PRODUCT_PCODE;
CREATE SEQUENCE SEQ_PRODUCTIO_IOCODE;
-- 4. 트리거의 이름은 TRG_PRODUCT 이고 PRODUCT_IO 테이블에 입고를 하면 PRODUCT 테이블에
-- STOCK 컬럼에 값을 추가하고 PRODUCT_IO 테이블에 출고를 하면 STOCK 컬럼에 값을 빼주는 역할을 함.
CREATE OR REPLACE TRIGGER TRG_PRODUCT
AFTER
INSERT ON PRODUCT_IO
FOR EACH ROW
BEGIN
-- INSERT INTO PRODUCT_IO VALUES(1, 1, SYSDATE, 10, '입고');
IF(:NEW.STATUS = '입고')
THEN
UPDATE PRODUCT
SET STOCK = STOCK + :NEW.AMOUNT
WHERE PCODE = :NEW.PCODE;
DBMS_OUTPUT.PUT_LINE('제품이 입고되었습니다. 수량: '||:NEW.AMOUNT);
-- INSERT INTO PRODUCT_IO VALUES(1, 1, SYSDATE, 10, '출고');
ELSIF(:NEW.STATUS = '출고')
THEN
UPDATE PRODUCT
SET STOCK = STOCK - :NEW.AMOUNT
WHERE PCODE = :NEW.PCODE;
DBMS_OUTPUT.PUT_LINE('제품이 출고되었습니다. 수량: '||:NEW.AMOUNT);
END IF;
END;
/
--5.TEST해보기
DESC PRODUCT;
SELECT *FROM USER_SSEQUENCES;
--부모를 만들어야 돼
INSERT INTO PRODUCT VALUES(SEQ_PRODUCT_PCODE.NEXTVAL, '갤럭시', '샘송', 1000000, DEFAULT);
INSERT INTO PRODUCT VALUES(SEQ_PRODUCT_PCODE.NEXTVAL, '아이폰', '사과', 1300000, DEFAULT);
INSERT INTO PRODUCT VALUES(SEQ_PRODUCT_PCODE.NEXTVAL, '대륙폰', '샤오미', 5000000, DEFAULT);
SELECT *FROM PRODUCT;
COMMIT;
INSERT INTO PRODUCT_IO VALUES(SEQ_PRODUCTIO_IOCODE.NEXTVAL, 1, SYSDATE, 10, '입고');
INSERT INTO PRODUCT_IO VALUES(SEQ_PRODUCTIO_IOCODE.NEXTVAL, 1, SYSDATE, 5, '출고');
DESC PRODUCT_IO;
ROWNUM의 FROM뒤 조건을 TOPN의 AS뒤에 복붙!!!!!!!!!!!
-- ================== 고급쿼리======================
-- 1.TOP-N 분석
-- 2.WITH 구문
-- 3. 계층형 쿼리(HIERARCHICAL QUERY)
-- 4. 윈도우 함수
-- 특정 컬럼에서 가장 큰 N개의 값 또는 가장 작은 N개의 값을 구해야 할 경우가 생김
-- 예) 가장 적게 팔린 제품 10가지는?
-- 회사에서 가장 소득이 많은 사람 3명은?
SELECT MAX(SALARY) FROM EMPLOYEE
WHERE SALARY
ORDER BY 1 DESC;
SELECT EMP_NAME FROM EMPLOYEE WHERE SALARY >= 3900000;
-- # ROWNUM, ROWID 사용하기
-- 테이블을 생성하면 자동으로 만들어지는 값
-- ROWID: 테이블의 특정 레코드를 랜덤하게 접근하기 위한 논리적인 주소값
-- ROWNUM: 각 행에 대한 일련번호, 오라클에서 내부적으로 부여하는 컬럼
SELECT ROWNUM, ROWID, EMP_ID FROM EMPLOYEE;
-- ROWNUM을 쓰면 넘버링할 수 있음
SELECT ROWNUM, EMP_NAME FROM EMPLOYEE;
-- ROWNUM으로 넘버링하여 TOP3, TOP10의 데이터를 조회하려고 했더니
-- ROWNUM이 꼬여버림, ROWNUM이 부여된 후 ORDER BY를 했기 때문임
-- 해결방법: 그렇다면 ORDER BY 후에 ROWNUM을 하면 되지 않음???
--안되고 있음 ROWNUM 꼬여버림
SELECT ROWNUM, EMPLOYEE.* FROM EMPLOYEE
WHERE SALARY IS NOT NULL
ORDER BY SALARY DESC;
--해결방법
SELECT ROWNUM,E.*
FROM(
SELECT EMPLOYEE.* FROM EMPLOYEE
WHERE SALARY IS NOT NULL
ORDER BY SALARY DESC) E
WHERE ROWNUM <=10;
-- @실습문제1
-- D5부서에서 연봉 TOP3의 전체정보를 출력하세요.
SELECT ROWNUM, E.*
FROM(
SELECT EMPLOYEE.* FROM EMPLOYEE
WHERE DEPT_CODE = 'D5'
ORDER BY SALARY DESC) E
WHERE ROWNUM <=3;
-- @실습문제2
-- 부서별 급여평균 TOP3 부서의 부서코드와 부서명, 평균급여를 출력하세요.
SELECT ROWNUM,ED.*
FROM(
SELECT DEPT_CODE, DEPT_TITLE, FLOOR(AVG(SALARY))
FROM EMPLOYEE
JOIN DEPARTMENT ON DEPT_CODE = DEPT_ID
GROUP BY DEPT_CODE, DEPT_TITLE
ORDER BY 3 DESC) ED
WHERE ROWNUM <4;
--ROWNUM에도 별칭을 줄 수 있다. 한번더 감싸야 -> 함수써
SELECT EED.*FROM(
SELECT ROWNUM "RNUM" ,ED.* FROM(
SELECT DEPT_CODE, DEPT_TITLE, FLOOR(AVG(SALARY))
FROM EMPLOYEE
JOIN DEPARTMENT ON DEPT_CODE = DEPT_ID
GROUP BY DEPT_CODE, DEPT_TITLE
ORDER BY 3 DESC) ED)EED
WHERE ROWNUM <4;
--4위에서 6위 구하기
--(안됨)
SELECT ROWNUM,ED.*FROM(
SELECT DEPT_CODE, DEPT_TITLE, FLOOR(AVG(SALARY))
FROM EMPLOYEE
JOIN DEPARTMENT ON DEPT_CODE = DEPT_ID
GROUP BY DEPT_CODE, DEPT_TITLE
ORDER BY 3 DESC) ED
WHERE ROWNUM >=4 AND ROWNUM <=6;
--조건이 맞아야 행이나오는데 ROWNUM이 4보다 절대 클 수 없다
--계속 1위를 부여하면서 해서? 이렇게 해야 함
--(됨)
SELECT * FROM(
SELECT ROWNUM "RNUM", ED.*FROM(
SELECT DEPT_CODE, DEPT_TITLE, FLOOR(AVG(SALARY))
FROM EMPLOYEE
JOIN DEPARTMENT ON DEPT_CODE = DEPT_ID
GROUP BY DEPT_CODE, DEPT_TITLE
ORDER BY 3 DESC) ED) EED
WHERE RNUM BETWEEN 4 AND 6;
-- ================= 2.WITH ==================
--오라클에서만 쓰는 WITH 구문
-- 서브쿼리에 이름을 붙여주고 인라인뷰로 사용시 서브쿼리의 이름을
-- FROM절에 기술할 수 있도록 해줌
-- 같은 서브쿼리가 여러번 사용될 경우 중복 작성을 피할 수 있고
-- 실행속도도 빨라지는 장점이 있음
/*
사용방법
WITH 서브쿼리명 AS (서브쿼리)
SELECT * FROM (서브쿼리명)
*/
-- 연봉 TOP 5 직원의 전체정보를 출력하세요
WITH TOPN_SAL AS(
SELECT * FROM EMPLOYEE
WHERE SALARY IS NOT NULL
ORDER BY SALARY DESC)
SELECT ROWNUM, E.* FROM TOPN_SALE
WHERE ROWNUM < 6;
-- @실습문제1
-- D5부서에서 연봉 TOP3의 전체정보를 출력하세요.
WITH TOPN_D5_SAL AS(
SELECT EMPLOYEE.* FROM EMPLOYEE
WHERE DEPT_CODE = 'D5'
ORDER BY SALARY DESC)
SELECT ROWNUM, E.*
FROM TOPN_D5_SAL E
WHERE ROWNUM <=3;
-- @실습문제2
-- 부서별 급여평균 TOP3 부서의 부서코드와 부서명, 평균급여를 출력하세요.
WITH TOPN_DEPT_SAL AS(
SELECT DEPT_CODE, DEPT_TITLE, FLOOR(AVG(SALARY))
FROM EMPLOYEE
JOIN DEPARTMENT ON DEPT_CODE = DEPT_ID
GROUP BY DEPT_CODE, DEPT_TITLE
ORDER BY 3 DESC)
SELECT ROWNUM, ED.*
FROM TOPN_DEPT_SAL ED
WHERE ROWNUM <4;
-- ================= 3.계층형 쿼리 ====================
-- JOIN을 통해 수평적으로 기준컬럼을 연결시킨 것과 달리 기준 컬럼을 가지고 수직적인 관계를 만듦
-- 조직도, 메뉴, 답변형 게시판 등 프랙탈 구조의 표현 적합함
-- 오라클에서 사용되는 구문
-- 1. START WITH : 부모행(루트)를 지정
-- 2. CONNECT BY : 부모 자식관계를 지정
-- 3. PRIOR : START WITH절에서 제시한 부모행의 기준컬럼을 지정함
-- 4. LEVEL : 의사컬럼(PSEUDO COLUMN), 계층정보를 나타내는 가상컬럼,
-- SELECT, WHERE, ORDER BY에서 사용가능
-- 1명이라도 직원을 관리하는 매니저의 정보를 출력하세요
SELECT EMP_ID, EMP_NAME, MANAGER_ID
FROM EMPLOYEE E
WHERE EXISTS(SELECT 1 FROM EMPLOYEE WHERE MANAGER_ID = E.EMP_ID);
-- 계층구조 정리: START WITH/ CONNECT BY/ PRIOR 다 쓰임 ㅎㅎ
SELECT EMP_ID, EMP_NAME, MANAGER_ID
FROM EMPLOYEE
START WITH EMP_ID = 200
CONNECT BY PRIOR EMP_ID = MANAGER_ID;
--이렇게 해주면 프론트앤드에서 다시한번 조직도 형태로 화면에서 뿌려주면 됨/ 백앤드가 할일은 다함
SELECT LPAD('ㄴ', (LEVEL-1)*5, ' ')||EMP_NAME|| NVL2(MANAGER_ID,'('||MANAGER_ID
FROM EMPLOYEE
START WITH EMP_ID = 200
CONNECT BY PRIOR EMP_ID = MANAGER_ID;
-- @실습예제1
-- MENU_TBL 테이블을 생성하는데 숫자인 NO 컬럼이 PRIMARY KEY로 있고, 문자로 크기가 100인
-- MENU_NAME 컬럼이 있고, 숫자로 된 PARENT_NO이라고 하는 컬럼이 있음. 생성해주세요.
CREATE TABLE MENU_TBL(
NO NUMBER PRIMARY KEY,
MENU_NAME VARCHAR2(100),
PARENT_NO NUMBER
);
INSERT INTO MENU_TBL VALUES(100, '주메뉴1', NULL);
INSERT INTO MENU_TBL VALUES(200, '주메뉴2', NULL);
INSERT INTO MENU_TBL VALUES(300, '주메뉴3', NULL);
INSERT INTO MENU_TBL VALUES(1000, '서브메뉴A', 100);
INSERT INTO MENU_TBL VALUES(1001, '상세메뉴A1', 1000);
INSERT INTO MENU_TBL VALUES(1002, '상세메뉴A2', 1000);
INSERT INTO MENU_TBL VALUES(1003, '상세메뉴A3', 1000);
INSERT INTO MENU_TBL VALUES(2000, '서브메뉴B', 200);
INSERT INTO MENU_TBL VALUES(3000, '서브메뉴C', 300);
INSERT INTO MENU_TBL VALUES(3001, '상세메뉴C1', 3000);
SELECT * FROM MENU_TBL;
COMMIT;
SELECT * FROM MENU_TBL
START WITH PARENT_NO IS NULL
CONNECT BY PRIOR NO=PARENT_NO;
SELECT LPAD('ㄴ',(LEVEL-1)*5, ' ')||MENU_NAME "메뉴"
FROM MENU_TBL
START WITH PARENT_NO IS NULL
CONNECT BY PRIOR NO=PARENT_NO;
-- ================= 4. 윈도우 함수 ================
-- 1. 순위함수
-- A. RANK() OVER
/*
사용법
RANK(0 OVER(ORDER BY 컬럼명 ASC | DESC를 SELECT 뒤에 두면 됨
-> 특정 컬럼 기준으로 랭킹 부여함, 중복 순위 다음은 해당 갯수만큼 건너뛰고 반환함
*/
-- 예제. 회사의 연봉 순위를 출력하시오
SELECT ROWNUM "연봉 순위", E.*
FROM(SELECT EMP_NAME, SALARY FROM EMPLOYEE
WHERE SALARY IS NOT NULL
ORDER BY SALARY DESC) E;
--FROM 뒤꺼 AS뒤에 복붙
WITH TOPN_SAL AS(SELECT EMP_NAME, SALARY FROM EMPLOYEE
WHERE SALARY IS NOT NULL
ORDER BY SALARY DESC)
SELECT ROWNUM "연봉순위", E.*FROM TOPN_SAL E;
--순위함수 사용해보기
SELECT EMP_NAME, SALARY, RANK() OVER(ORDER BY SALARY DESC) AS "연봉순위" FROM EMPLOYEE
WHERE SALARY IS NOT NULL;
-- 랭크오버는 같은 등수 표기하고 생략해버려: 20등(중복)을 없애버린다
-- 인라인뷰를 복잡하게 쓸게 아니라, 정렬을 해서 순위를 매겨보면??
-- @실습문제1
-- 입사일이 빠른 순으로 순위를 정하여 출력하시오.
-- 이름, 입사일, 순위
SELECT EMP_NAME, HIRE_DATE, RANK() OVER(ORDER BY HIRE_DATE ASC) AS "입사순위" FROM EMPLOYEE
WHERE HIRE_DATE IS NOT NULL;
-- B. DENSE_RANK() OVER
-- -> 중복 순위 상관없이 순차적으로 반환, 빠짐없이 빽빽한 순위를 부여함
SELECT EMP_NAME, SALARY, DENSE_RANK() OVER(ORDER BY SALARY DESC) AS "순위" FROM EMPLOYEE
WHERE SALARY IS NOT NULL;
--@실습문제2
-- 기본급여의 등수가 1등부터 10등까지인 직원의 이름, 급여, 순위를 출력하세요.
WITH RANK_SAL AS(
SELECT EMP_NAME, SALARY, RANK() OVER(ORDER BY SALARY DESC) AS "순위"
FROM EMPLOYEE WHERE SALARY IS NOT NULL)
SELECT * FROM RANK_SAL
WHERE 순위 BETWEEN 1 AND 10;