하위^^

ROLLUP은 그룹별 산출한 결과값의 소계/누계를 구하는 함수이다.

GROUP BY와 함께 쓰이며 GROUP BY로 묶은 부분을 ROLLUP을 추가하여 () 괄호로 묶어 사용한다.

GROUP BY ROLLUP( [] );

DECODE는 자바의 Switch 문과 비슷하다고 생각하면 된다.

SELECT 문의 컬럼명에 쓰이며 다음과 같이 쓰인다.

DECODE (검사 할 조건 계산식(결과가 값으로 떨어져야 함), 값1, 처리할 식, 값2, 처리할 식, ..... , DEFAULT값(생략가능))

예제를 통해 실제로 어떻게 쓰이나 보자

--rollup
--그룹별 산출한 결과값의 소계/누계를 구하는 함수

SELECT depart_no, commision, COUNT(*)
FROM employees
GROUP BY depart_no, commision;

SELECT depart_no, commision, COUNT(*)
FROM employees
GROUP BY ROLLUP(depart_no, commision);

-- GROUPING 함수를 통해 (null)을 제거할 수 있다.
-- 실제 데이터와 집계 데이터를 구분하는 함수
-- 0 : 실제 데이터
-- 1 : 집계 데이터
SELECT GROUPING(depart_no), GROUPING(commision), COUNT(*)
FROM employees
GROUP BY ROLLUP(depart_no, commision);

-- DECODE()
-- 자바에서 switch 문과 유사한 선택 함수
-- DECODE(검사 할 조건 계산식(결과가 값으로 떨어져야 함), 값1, 처리할 식 1, 값2, 처리할 식 2 ...... , DEFAULT 값(생략 가능))

SELECT
    DECODE(GROUPING(depart_no), 0, depart_no, 1, ' ') 부서번호,
    DECODE(GROUPING(commision), 0, TO_CHAR(commision), 1, ' ') 커미션,
    COUNT(*)
FROM employees
GROUP BY ROLLUP(depart_no, commision);

eXERD.zip

SQL.zip

eXERD는 이클립스와 똑같이 작업공간으로 할당하면 되고

SQL은 그냥 열면 됩니다, 단 SQL Developer의 코드 편집기를 UTF - 8 설정을 하지 않으면 깨져 보이므로 주의!

https://qdgbjsdnb.tistory.com/100?category=719243 <-- sql파일 열기 및 UTF - 8 설정 방법

숫자형의 함수로는 6가지를 소개한다.

ABS

CEIL

FLOOR

OMD

ROUND

TRUNC

각각의 기능을 예제와 주석을 통해 학인해보자

-- 1. 숫자형 함수 : 수 값을 인수로 받아 NUMBER 타입의 값을 반환하는 함수.
-- 여기서는 자주 사용 하는 함수 몇가지만 소개 한다.
-- DUAL table : function 이나 계산식을 테이블 생성 없이 수행하기 위한 일종의 dummy 테이블

-- 1) ABS(n) : 절대값을 계산하는 함수이다.
SELECT ABS (-1.234) absolute FROM DUAL;
-- 결과 : 1.234

-- 2) CEIL(n) : 올림값을 반환하는 함수이다.
SELECT CEIL(10.1234) "CEIL" FROM DUAL;
-- 결과 : 11
SELECT CEIL(-10.1234) "CEIL" FROM DUAL;
-- 결과 : -10

-- 3) FLOOR(n) : 버림값을 반환하는 함수이다.
SELECT FLOOR(10.1234) "FLOOR" FROM DUAL;
-- 결과 : 10
SELECT FLOOR(-10.1234) "FLOOR" FROM DUAL;
-- 결과 : -11 

-- 4) MOD(m, n) : m을 n으로 나눈 나머지를 반환 한다. n이 0일 경우 m을 반환 한다.
SELECT MOD(9, 4) "MOD" FROM DUAL ;
-- 결과 : 1

-- 5) ROUND(n, [m]) : 반올림, n 값을 반올림, m은 반올림 자릿수
SELECT ROUND(192.153, 1) "ROUND" FROM DUAL; 
-- 결과 : 192.2 소숫점 2자리를 반올림 하여 1자리 까지 표현
SELECT ROUND(192.153, -1) "ROUND" FROM DUAL;
-- 결과 : 190 숫자 2자리를 반올림 하여 1자리 까지 표현

-- 6) TRUNC(n, m) : n값을 절삭하는 함수로 m은 소숫점 아래 자릿수를 나타낸다.
SELECT TRUNC(7.5597, 2) "TRUNC" FROM DUAL;
-- 결과 : 7.55
 
-- 7) TRUNC 예제
SELECT TRUNC(789.5597, -2) "TRUNC" FROM DUAL;
-- 결과 : 700

DB에서 날짜를 표시할 때 쓰는 키워드를 소개한다.

SYSDATE

SYSTIMESTAMP, 

ADD_MONTHS, 

MONTHS_BETWEEN

LAST_DAY

NEXT_DAY

이와 같이 여러개가 있는데 차이점을 살펴보자.

-- 3.Datetime Functions (날짜 함수)
-- 날짜 함수는 오라클 날짜에 대해 연산을 한다.
-- 날짜 함수는 NUMBER형 값 또는 DATE형의 값을 RETURN 한다.

-- 1) SYSDATE : 현재 일자와 시간(시스템기준)을 얻을 수 있다.(최소단위 1초)
-- [뺄 숫자]/[24시간중]/[60분 중]/[60 초 중]
-- HH:MM:SS 는 안된다. MM 은 MONTH 의 의미로 사용
SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS') "지금시간" FROM DUAL ;
 
SELECT TO_CHAR(SYSDATE-1,'YYYY-MM-DD HH24:MI:SS') "하루전지금시간" FROM DUAL ;

-- AS 나 그냥 컬럼명을 쓸 수 있지만 앞에 숫자가 있는 경우 필히 더블쿼터 사용 
SELECT TO_CHAR(SYSDATE-1/24,'YYYY-MM-DD HH24:MI:SS') "1시간전시간" FROM DUAL ;
 
SELECT TO_CHAR(SYSDATE-1/24/60,'YYYY-MM-DD HH24:MI:SS') "1분전시간" FROM DUAL ;

SELECT TO_CHAR(SYSDATE-(5/24 + 30/24/60 + 10/24/60/60),'YYYY-MM-DD HH24:MI:SS') 
"5시간 30분 10초전" FROM DUAL ;

-- 2) SYSTIMESTAMP : 현재 일자와 시간(시스템기준)을 얻을 수 있다.(최소단위 10억분의 1초)
-- SYSTIMESTAMP 예제

-- 초 단위 이상 표기 할 시에는 날짜 계산이 안된다.
SELECT TO_CHAR(SYSTIMESTAMP,'YYYY-MM-DD HH24:MI:SS.FF3') FROM DUAL ;
SELECT TO_CHAR(SYSTIMESTAMP,'YYYY-MM-DD HH24:MI:SS.FF9') FROM DUAL ;
 
SELECT TO_CHAR(SYSTIMESTAMP -1/24,'YYYY-MM-DD HH24:MI:SS') "1시간전시간" FROM DUAL ;
 
SELECT TO_CHAR(SYSTIMESTAMP -1/24/60,'YYYY-MM-DD HH24:MI:SS') "1분전시간" FROM DUAL ;

-- 3)ADD_MONTHS(a, b) : a의 날짜에 b의 달을 더한 값을 반환 한다.
-- 오늘 날짜에 3달을 추가
SELECT TO_CHAR(ADD_MONTHS(SYSDATE,3),'RRRR-MM-DD')  "date" FROM DUAL;   

SELECT TO_CHAR(ADD_MONTHS(SYSTIMESTAMP,3),'RRRR-MM-DD')  "date" FROM DUAL;

-- 4) MONTHS_BETWEEN(a1, a2) : a1과 a2 사이의 달의 수를 NUMBER형 타입으로 반환 한다.
-- 2016년01월01일 과 2016년 11월 01일 사이의 달 수
SELECT MONTHS_BETWEEN(TO_DATE('2016-11-01','RRRR-MM-DD'), 
                      TO_DATE('2016-01-01','RRRR-MM-DD')) "month" FROM DUAL;
 
-- 2016년 06월 05일 과 2016년 15월 01일 사이의 일 수
SELECT TO_DATE('2016-06-05','RRRR-MM-DD') - TO_DATE('2016-05-01','RRRR-MM-DD') "Day" FROM DUAL;

-- 5) LAST_DAY(d) : 달의 마지막 날의 날짜를 반환 한다
SELECT SYSDATE today, LAST_DAY(SYSDATE) lastday FROM DUAL;
 
-- 6) NEXT_DAY(d, c1) : 돌아오는 명시된 요일의 일자를 반환한다.
-- 요일은 1(일) ~ 7(토)
-- 4이므로 수요일
SELECT NEXT_DAY(SYSDATE, 4) "Next Wednesday" FROM DUAL;

SELECT NEXT_DAY(TO_DATE('20110501','RRRRMMDD'), 2) "5월 첫째주 월요일" FROM DUAL;

문자형 함수는 데이터타입 CHAR, VARCHAR2의 데이터를 이용해 다양한 기능을 하는 함수들로

아래와 같은 종류가 있다.

CONCAT

INITCAP

LOWER

UPPER

LPAD

RPAD

SUBSTR

SUBSTRB

LENGTH

LENGTHB

REPLACE

INSTR

TRIM

LTRIM

RTRIM

과 같이 여러개가 있다.

아래의 예제를 통해 쓰임을 파악해보자

-- 2. 문자형 함수: CHAR, VARCHAR2 타입을 인수로 받아 VARCHAR2 타입의 값을 반환하는 함수이다.
-- 1) CONCAT(char1, char2)
-- CONCAT 함수는 Concatenation의 약자로 두 문자를 결합하는 역할을 한며, "||" 연산자와 같은 역할을 한다.
SELECT CONCAT('www.', 'oracleclub')||'.com' name FROM DUAL;
 
  
-- 2) INITCAP(char), LOWER(char), UPPER(char)
-- INITCAP : 주어진 문자열의 첫 번째 문자를 대문자로 변환시켜 준다.
-- LOWER : 주어진 문자열을 소문자로 변환시켜 준다.
-- UPPER : 주어진 문자열을 대문자로 변환시켜 준다.

SELECT INITCAP('oracleclub') name FROM DUAL UNION ALL
SELECT UPPER('oracleclub') name FROM DUAL UNION ALL
SELECT LOWER('oracleclub') name FROM DUAL;
-- UNION 은 합집합이며, UNION ALL 은 중복을 포함하는 합집합 이다.


-- 3) LPAD(char1, n [,char2]), RPAD(char1, n [,char2])
-- LPAD : 왼쪽에 문자열을 끼어 넣는 역할을 한다.
-- RPAD : 오른쪽에 문자열을 끼어 넣는 역할을 한다.
-- n은 반환되는 문자열의 전체 길이를 나타내며, char1의 문자열이 n보다 클 경우 char1을 n개 문자열 만큼 반환 한다.
SELECT LPAD('oracleclub', 12, '*') name FROM DUAL UNION ALL
SELECT RPAD('oracleclub', 12, '*') name FROM DUAL;


-- 4) SUBSTR(char, m ,[n]), SUBSTRB(char, m ,[n])
-- SUBSTR 함수는 m 번째 자리부터 길이가 n개인 문자열을 반환 한다.
-- m이 음수일 경우에는 뒤에서 m 번째 문자부터 n개의 문자를 반환한다.
-- SUBSTRB 함수에서 B는 Byte단위로 처리하겠다는 의미이다.
-- 세번째 이후 문자열 반환.
SELECT SUBSTR('oracleclub', 3) name FROM DUAL;  
-- 세번째 이후 네개의 문자열 반환.
SELECT SUBSTR('oracleclub', 3, 4) name FROM DUAL;
-- 뒤에서 세번째아후 두개의 문자열 반환.
SELECT SUBSTR('oracleclub', -3, 2) name FROM DUAL;
 
-- DB가 UTF-8인 경우 아래 SQL의 결과는?(이경우 한글 한 글자는 3바이트 이다.)
SELECT SUBSTRB('오라클클럽',1) name FROM DUAL  UNION ALL
SELECT SUBSTRB('오라클클럽',3) name FROM DUAL;


-- 5) LENGTH(char), LENGTHB(char)
-- 문자열의 길이를 반환 한다.
SELECT LENGTH('오라클클럽') len FROM DUAL UNION ALL
SELECT LENGTHB('오라클클럽') len FROM DUAL;
 

-- 6) REPLACE(char1, str1, str2)
-- REPLACE는 문자열의 특정 문자를 다른 문자로 변환 한다.
SELECT REPLACE('oracleclub','oracle','db') name FROM DUAL;
 
-- 대소문자를 구분한다는 것을 알수 있다.
SELECT REPLACE('OracleClub','oracle','DB') name FROM DUAL;
SELECT REPLACE('OracleClub','Oracle','DB') name FROM DUAL;
 
 
-- 7) INSTR (char1, str1, m, n)
-- 문자열이 포함되어 있는지를 조사하여 문자열의 위치를 반환 한다. 
-- 지정한 문자열이 발견되지 않으면 0이 반환 된다.
-- char1 : 지정문자, str1 : 검색문자, m:시작위치, n:검색순위
-- 지정한 문자 OK가 발견되지 않아 0을 반환.
SELECT INSTR('CORPORATE FLOOR','OK') idx FROM DUAL; -- 0 반환
SELECT INSTR('CORPORATE FLOOR','OR') idx FROM DUAL; -- 2 반환
 
-- 왼쪽에서 세 번째부터 시작을 해서 검색된 OR의 위치를 반환 한다.
SELECT INSTR('CORPORATE FLOOR','OR', 3) idx FROM DUAL; -- 5 반환 
 
-- 왼쪽에서 세 번째부터 시작을 해서 비교를 하여 OR이 두 번째 검색되는 지점의 위치를 반환 한다.
SELECT INSTR('CORPORATE FLOOR','OR', 3, 2) idx FROM DUAL;
 
-- 8) TRIM (char1 FROM char2), LTRIM (char1[,char2]), RTRIM (char1[,char2])
-- 앞(왼쪽) 또는 뒤(오른쪽)
-- 9) TRIM : 특정한 문자를 제거 한다. 제거할 문자를 입력하지 않으면 기본적으로 공백이 제거 된다.
-- 10) LTRIM : 왼쪽부터 문자를 제거 한다. 제거할 문자를 입력하지 않으면 기본적으로 왼쪽 공백이 제거 된다.
-- 11) RTRIM : 오른쪽부터 문자를 제거 한다. 제거할 문자를 입력하지 않으면 기본적으로 오른쪽 공백이 제거 된다.

-- o와 공백을 제거 하는 TRIM 예제
SELECT TRIM('o' FROM 'oracleclub') name FROM DUAL; -- racleclub

-- 왼쪽의 문자열을 TRIM하는 예제 
-- 공백의 경우 왼쪽 공백만 제거 되는 것을 확인 할 수 있다.
SELECT LTRIM('oracleclub','oracle') name FROM DUAL;
SELECT REPLACE(LTRIM(' oracleclub '),' ','*') name FROM DUAL;
 
-- 오른쪽의 문자열을 TRIM 하는 예제 
-- 공백의 경우 오른쪽 공백만 제거 되는 것을 확인 할 수 있다.
SELECT RTRIM('oracleclub','club') name FROM DUAL
UNION ALL
SELECT REPLACE(RTRIM(' oracleclub '),' ','*') name FROM DUAL;

집계함수, aggregate는 db에 저장된 컬럼의 값의 평균을 나타낸다던가 합계, 최소, 최대값을 보여주는

결과값을 출력하는 함수이다.

보통 GROUP BY 절과 함께 쓰인다.

예시 코드를 확인해보자

-- 집계 함수 : 여러행 또는 테이블 전체 행으로부터 하나의 결과값을 반환하는 함수

-- 집계함수(Aggregate function)의 이해
-- GROUP BY절을 이용하여 그룹 당 하나의 결과로 그룹화 할 수 있다.
-- HAVING절을 사용하여 집계함수를 이용한 조건 비교를 할 수 있다.
-- MIN, MAX 함수는 모든 자료형에 사용 할 수 있다.
-- 일반적으로 가장 많이 사용하는 집계함수에는 AVG(평균), COUNT(개수), MAX(최대값), MIN(최소값), SUM(합계) 등이 있다.

--1) COUNT : 검색된 행의 수를 반환 한다.
-- 검색된 행의 총 수 4개를 반환. 즉 4개의 부서가 존재한다.
SELECT COUNT(deptno) FROM dept;
 

-- 2) MAX : 컬럼값 중에서 최대값을 반환 한다.
-- sal 컬럼값 중에서 제일 큰값을 반환. 즉 가장 큰 급여를 반환.
SELECT MAX(salary) salary FROM employees;
 
 
-- 3) MIN : 컬럼값 중에서 최소값을 반환 한다.
-- sal 컬럼값 중에서 가장 작은 값 반환. 즉 가장 적은 급여를 반환
SELECT MIN(salary) salary FROM employees;
  
  
-- 4) AVG : 평균 값을 반환 한다.
SELECT AVG(salary) salary FROM employees;

-- 부서번호 30의 사원 평균 급여를 소수점 1자리 이하에서 반올림
SELECT ROUND(AVG(salary),1) salary FROM emp WHERE deptno = 30;
 
 
-- 5) SUM : 검색된 컬럼의 합을 반환 한다.
SELECT SUM(salary) salary FROM employees;

-- 부서번호 30의 사원 급여 합계를 조회.
SELECT SUM(sal) salary FROM emp WHERE deptno = 30;

트리거는 사전적 의미로는 방아쇠를 의미한다

말 그대로 트리거는 방아쇠를 당겼을때 총이 나가는 것 처럼

트리거를 실행하면 안에 담겨있는 실행문이 그대로 실행되는 것이다.

VIEW에 대해서는 트리거를 이용할 수 없고 INSERT, UPDATE, DELETE문만 사용가능하다.

CREATE OR REPLACE TRIGGER 트리거 이름

[BEFORE|AFTER]

[INSERT|UPDATE|DELETE] ON 테이블 이름

[FOR EACH ROW] …

Pl/sql 실행문

와 같은 익숙한 구조로 만들어진다.

안의 옵션의 설명은

[BEFORE|AFTER] : 문장이 실행 되기 전/후에 실행된다.

[INSERT|UPDATE|DELETE] ON 테이블 이름 : 작동할 구문

[FOR EACH ROW] : 이 옵션은 행 트리거가 된다.

행/문장 트리거

행 Trigger     : 컬럼의 각 행의 데이터 변화가 생길 때마다 실행 (데이터 행 제어 O)

문장 Trigger  : 트리거 사건에 의해 단 한번만 실행(데이터 행 제어 X)

SET SERVEROUTPUT ON;

-- 사용 테이블 2개 생성

CREATE TABLE emp01(
    empno NUMBER(4) PRIMARY KEY,
    ename VARCHAR2(20),
    job VARCHAR2(20)
);

CREATE TABLE emp02(
    empno NUMBER(4) PRIMARY KEY,
    ename VARCHAR2(20),
    job VARCHAR2(20)
);

-- INSERT TRIGGER
-- emp01에 데이터가 들어가면 emp02에도 들어간다.
CREATE OR REPLACE TRIGGER tri_insert
    AFTER INSERT ON emp01
-- emp01에서 insert가 일어난 후에 작동하여라
    FOR EACH ROW
-- 각 행의 데이터를 변화를 감시, 감지되면 아래 구문을 실행
    BEGIN
        -- :NEW 는 새로 들어온 데이터를 의미
        INSERT INTO emp02 VALUES(:NEW.empno, :NEW.ename, :NEW.job);
    END;
/

-- 데이터 삽입
INSERT INTO emp01 VALUES(1, 'kim', '개발자');
INSERT INTO emp01 VALUES(2, 'lee', '디자이너');
INSERT INTO emp01 VALUES(3, 'park', '기획자');
-- 트리거 결과 확인
SELECT * FROM emp01;
SELECT * FROM emp02;



-- UPDATE TRIGGER
-- emp01이 수정되면 emp02도 수정된다.
CREATE OR REPLACE TRIGGER tri_update
    AFTER UPDATE ON emp01
    FOR EACH ROW
    BEGIN
        UPDATE emp02 SET ename = :NEW.ename, job = :NEW.job WHERE empno = :NEW.empno;
    END;
/

-- 데이터 변경
UPDATE emp01 SET ename = 'oh', job = '개발자' WHERE empno = 2;

-- 트리거 결과 확인
SELECT * FROM emp01;
SELECT * FROM emp02;




-- DELETE TRIGGER
-- emp01이 삭제되면 emp02도 삭제된다.
CREATE OR REPLACE TRIGGER tri_del
    AFTER DELETE ON emp01
    FOR EACH ROW
    BEGIN
        -- OLD는 이전 데이터를 의미하지만 DELETE문에서는 삭제할 자료를 의미
        DELETE FROM emp02 WHERE empno = :OLD.empno;
    END;
/

-- 데이터 삭제
DELETE FROM emp01 WHERE empno = 2;

-- 트리거 결과 확인
SELECT * FROM emp01;
SELECT * FROM emp02;


-- 트리거 조회
SELECT * FROM USER_TRIGGERS;
-- 트리거 소스 확인
SELECT * FROM USER_SOURCE WHERE type = 'TRIGGER' AND name = 'TRI_INSERT';
-- 트리거 삭제
DROP TRIGGER tri_insert;

이와같이 데이터의 변경을 감지 할 수 있으므로 백업같은 작업에 잘 사용한다.

암시적 커서

암시적 커서는 SQL문장이 처리되는 곳에 대한 익명의 주소

실행되는 모든 SQL문은 암시적인 커서가 생성되고, 그대로 이용 할 수 있다.

SQL%ROWCOUNT, 해당 SQL문에 영향을 받는 행의 수

SQL%FOUND, 해당 SQL문에 영향을 받는 행의 수가 한 개 이상일 때 TRUE

SQL%NOTFOUNT, 해당 SQL문에 영향을 받는 행의 수가 없을 때 TRUE

SQL%ISOPEN, 항상 FALSE, 암시적 커서가 열려있는지 여부

커서는 사용하고 꼭 닫아줘야 한다.

JAVA의 몇몇 클래스들 처럼...

결론으로 커서는 쿼리문을 주소로 담고있는것.

명시적 커서

명시적 커서는 직접 커서를 만들어 사용하는 것.

커서에는 한개 이상의 데이터가 담기게 되고 데이터를 OPEN, PATCH, CLOSE 등을 사용하여 다룬다.

CURSOR 커서이름 IS 실행 할 문장

CURSOR test_cursor IS SELECT * FROM dept

와 같은 형식으로 사용

SET SERVEROUTPUT ON;

-- 1) 암시적(묵시적) 커서
-- SQL%ROWCOUNT : 쿼리를 만족하는 갯수 추출
-- SQL%FOUND    : 쿼리를 만족하는 갯수가 있는가? T/F
-- SQL%NOTFOUND : 쿼리를 만족하는 갯수가 없는가? T/F

DECLARE
    v_emp employees%ROWTYPE;
    BEGIN
        SELECT salary INTO v_emp.salary FROM employees WHERE emp_no = 115;
        IF SQL%FOUND THEN
        -- FOUND - 해당 문장 값이 존재하는가
            DBMS_OUTPUT.PUT_LINE('검색한 값이 있습니다.');
            DBMS_OUTPUT.PUT_LINE('115번 사원의 급여 : '||v_emp.salary);
            DBMS_OUTPUT.PUT_LINE('115번 사원의 수 : '||SQL%ROWCOUNT);
        ELSE
            DBMS_OUTPUT.PUT_LINE('검색한 값이 없습니다.');
        END IF;
    END;
/


SELECT * FROM dept;
-- 2) 명시적 커서
DECLARE
    CURSOR cur IS SELECT * FROM dept;
    v_dept dept%ROWTYPE;
    BEGIN
        OPEN cur;
        LOOP
            FETCH cur INTO v_dept.deptno, v_dept.deptname, v_dept.loc;
            DBMS_OUTPUT.PUT_LINE('부서번호 : '||v_dept.deptno);
            DBMS_OUTPUT.PUT_LINE('부서명 : '||v_dept.deptname);
            DBMS_OUTPUT.PUT_LINE('위치 : '||v_dept.loc);
            DBMS_OUTPUT.PUT_LINE('Count : '||cur%ROWCOUNT);
            EXIT WHEN cur%ROWCOUNT >= 5;
        END LOOP;
        CLOSE cur;
    END;
/

-- FOR IN LOOP
-- CURSOR의 OPEN,PATCH,CLOSE 가 필요 없다.
DECLARE
    CURSOR cur IS SELECT * FROM dept;
    v_dept dept%ROWTYPE;
    BEGIN
        FOR v_dept IN cur LOOP
            DBMS_OUTPUT.PUT_LINE('부서번호 : '||v_dept.deptno);
            DBMS_OUTPUT.PUT_LINE('부서명 : '||v_dept.deptname);
            DBMS_OUTPUT.PUT_LINE('위치 : '||v_dept.loc);
        END LOOP;
    END;
/

다른 언어의 기본적인 문법 중 반복문 역시 사용 가능하다.

LOOP, WHILE, FORLOOP 가 있다.

차이점은 따로 없으며 예제를 통해 간단히 확인하자

LOOP

SET SERVEROUTPUT ON;
-- LOOP문은 빠져나갈 조건이 만족 될 때까지 반복된다.

DECLARE
    i NUMBER(1) := 0;
    BEGIN
        LOOP
            i := i + 1;
            DBMS_OUTPUT.PUT_LINE(i);
            EXIT WHEN i > 5;
        END LOOP;
    END;
/

-- dept에 있는 데이터를 하나씩 불러온다. (전체 5개))
SELECT deptno, deptname, loc FROM dept WHERE deptno = 1;

DECLARE
    i NUMBER(1) := 0;
    v_val dept%ROWTYPE;
    BEGIN
        LOOP
            i := i + 1;
            SELECT deptno, deptname, loc INTO v_val.deptno, v_val.deptname, v_val.loc FROM dept WHERE deptno = i;
            DBMS_OUTPUT.PUT_LINE(v_val.deptno||'/'||v_val.deptname||'/'||v_val.loc);
            EXIT WHEN i > 4;
        END LOOP;
    END;
/

WHILE

-- WHILE 문, 브레이크를 하나 더 만들어 주는 것.
DECLARE
    i NUMBER(1) := 0;
    v_val dept%ROWTYPE;
    BEGIN
        WHILE i <= 5 LOOP
            i := i + 1;
            SELECT deptno, deptname, loc INTO v_val.deptno, v_val.deptname, v_val.loc FROM dept WHERE deptno = i;
            DBMS_OUTPUT.PUT_LINE(v_val.deptno||'/'||v_val.deptname||'/'||v_val.loc);
            EXIT WHEN i >= 5;
        END LOOP;
    END;
/

FOR LOOP

FOR문은 향상된 FOR문처럼 데이터를 가져오는데

DB의 데이터를 반복문을 통해 한번에 가져올 때 씀

SET SERVEROUTPUT ON;
-- for loop은 JAVA의 향상된 FOR문과 비슷하다
-- 덩어리에서 하나씩 쪼개어 변수에 담는다.
-- FOR[변수] IN [덩어리]


-- 숫자를 통해 반복
DECLARE
    BEGIN
        FOR i IN 1..10
        LOOP
            DBMS_OUTPUT.PUT_LINE(i);
        END LOOP;
    END;
/

-- 반복으로 찾을 SELECT항목
SELECT ename, hiredate FROM emp WHERE hiredate > TO_DATE('15/01/02') ORDER BY hiredate;
-- SELECT 항목으로 반복으로 찾을 데이터
DECLARE
    BEGIN
        FOR emp_list IN (SELECT ename, hiredate FROM emp WHERE hiredate > TO_DATE('15/01/02') ORDER BY hiredate)
        LOOP
            DBMS_OUTPUT.PUT_LINE('사원이름 : '||emp_list.ename);
            DBMS_OUTPUT.PUT_LINE('입사일 : '||emp_list.hiredate);
        END LOOP;
    END;
/

IF문은 말그대로 JAVA같은 타 언어에서 사용하던 조건문이랑 똑같은 기능을 한다.

조건에 맞으면 작성한 코드를 실행하고

틀리다면 작성한 다른 코드를 실행하는 구문

데이터 결과랑 출력 구문인 DBMS_OUTPUT이랑 틀릴것입니다. 제가 ~~팀 입니다를 귀찮아서 안찾고 막 적었습니다.

SET SERVEROUTPUT ON;
SELECT * FROM dept;
-- 부서 번호를 통해 팀 이름 출력과 위치
SELECT deptname, loc FROM dept WHERE deptno = 1;

CREATE OR REPLACE PROCEDURE dept_find(v_no IN NUMBER)
    IS
    v_dept dept%ROWTYPE;
    BEGIN
        SELECT loc INTO v_dept.loc
        FROM dept WHERE deptno = v_no;
        
        IF v_no = 1 THEN
            DBMS_OUTPUT.PUT('세일즈 팀');
        END IF;
        
        IF v_no = 2 THEN
            DBMS_OUTPUT.PUT('인사 팀');
        END IF;
        
        IF v_no = 3 THEN
            DBMS_OUTPUT.PUT('배송 팀');
        END IF;
        
        IF v_no = 4 THEN
            DBMS_OUTPUT.PUT('개발 1팀');
        END IF;
        
        IF v_no = 5 THEN
            DBMS_OUTPUT.PUT('개발 2팀');
        END IF;
        DBMS_OUTPUT.PUT_LINE('위치 : '||v_dept.loc);
    END;
/

EXEC dept_find(3);

IF ELSE 문

SET SERVEROUTPUT ON;

-- 부서 번호에 따라 세일즈 팀인지 아닌지를 출력
-- sales_find(v_no)
-- 테이블 : dept
SELECT * FROM dept;
CREATE OR REPLACE PROCEDURE sales_find(v_no IN NUMBER)
    IS
    v_dept dept%ROWTYPE;
    BEGIN
        SELECT deptname INTO v_dept.deptname FROM dept WHERE deptno = v_no;
        
        IF v_dept.deptname = 'sales' THEN
            DBMS_OUTPUT.PUT_LINE(v_no||'은 세일즈팀 입니다.');
        ELSE IF
            DBMS_OUTPUT.PUT_LINE(v_no||'은 세일즈팀이 아닙니다.');
        END IF;
    END;
/
EXEC sales_find(2);



-- 조건에 없는 번호가 나왔을 경우 '해당 데이터가 없습니다.' 출력
-- JAVA에선 예외처리하면 되지만 DB에선??
CREATE OR REPLACE PROCEDURE sales_find2(v_no IN NUMBER)
    IS
    v_dept dept%ROWTYPE;
    BEGIN
        IF v_no = 1 THEN
            DBMS_OUTPUT.PUT_LINE(v_no||'은 세일즈팀 입니다.');
        ELSE
            IF v_no >= 2 THEN
                IF v_no <= 5 THEN
                    DBMS_OUTPUT.PUT_LINE(v_no||'은 세일즈팀 아닙니다.');
                ELSE
                    DBMS_OUTPUT.PUT_LINE('올바른 숫자를 입력해주세요.');
                END IF;
            ELSE
                DBMS_OUTPUT.PUT_LINE('올바른 숫자를 입력해주세요.');
            END IF;
        END IF;
    END;
/
EXEC sales_find2(-1);
-- IF안에 IF를 넣는것도 가능

ELS IF 문

SET SERVEROUTPUT ON;
-- IF로 만든 프로시저를 ELSE IF로 변경
-- dept_find
SELECT name, text FROM USER_SOURCE WHERE name = 'DEPT_FIND';
-- 만들어 둔 프로시저 소스 확인

-- 부서 번호를 통해 팀 이름 출력과 위치
SELECT deptname, loc FROM dept WHERE deptno = 1;

CREATE OR REPLACE PROCEDURE dept_findels(v_no IN NUMBER)
    IS
    v_dept dept%ROWTYPE;
    BEGIN
        SELECT loc INTO v_dept.loc
        FROM dept WHERE deptno = v_no;
        
        IF v_no = 1 THEN
            DBMS_OUTPUT.PUT('세일즈 팀');
        ELSIF v_no = 2 THEN
            DBMS_OUTPUT.PUT('인사 팀');
        ELSIF v_no = 3 THEN
            DBMS_OUTPUT.PUT('배송 팀');
        ELSIF v_no = 4 THEN
            DBMS_OUTPUT.PUT('개발 1팀');
        ELSIF v_no = 5 THEN
            DBMS_OUTPUT.PUT('개발 2팀');
        END IF;
        DBMS_OUTPUT.PUT_LINE('위치 : '||v_dept.loc);
    END;
/

EXEC dept_findels(2);

위의 코드

SELECT name, text FROM USER_SOURCE WHERE name = 'DEPT_FIND';

위와 같이 USER_SOURCE를 조회하여 확인할 수 있다.

또는

좌측에서 프로시저 항목을 보여주는데 클릭하면 위와같이 보여준다.