[211005] 반복문(LOOP)

<FOR LOOP>

 

BEGIN     FOR n IN REVERSE 1..5 LOOP                          -- n은 지역 변수이다.                  DBMS_OUTPUT.PUT_LINE('반복 횟수 : ' || n);     END LOOP; END; /

결과  반복 횟수 : 1 반복 횟수 : 2 반복 횟수 : 3 반복 횟수 : 4 반복 횟수 : 5

BEGIN     FOR n IN REVERSE 1..5 LOOP         DBMS_OUTPUT.PUT_LINE('반복 횟수 : ' || n);     END LOOP; END; /

결과  반복 횟수 : 5 반복 횟수 : 4 반복 횟수 : 3 반복 횟수 : 2 반복 횟수 : 1

 

 

<BASIC LOOP>

 

DECLARE

    /* 
      변수 선언부로 BEGIN 안에 사용할 변수를 미리 구성한다.

      변수명 데이터타입;
    */

-- BASIC LOOP DECLARE     n NUMBER := 1; BEGIN     LOOP         DBMS_OUTPUT.PUT_LINE('반복 횟수 : ' || n);         n := n + 1;         IF n >= 5 THEN             EXIT;         END IF;     END LOOP; END; /

 

 

<LOOP 이용한 테이블 행 출력>

 

BEGIN     FOR r IN (SELECT * FROM EMPLOYEES) LOOP -- 첫번째 행 데이터가 r 변수에 담긴다. 루프 돌면서 행 끝까지         DBMS_OUTPUT.PUT_LINE('이름 : ' || CONCAT(r.FIRST_NAME || ' ', r.LAST_NAME));     END LOOP;                             -- CONCAT SELECT CONCAT(‘가나다라’, ‘ABCD’) FROM DUAL; -- 가나다라ABCD END;                                         -- CONCAT : 컬럼의 문자 혹은 문자열을 두 개 전달 받아 하나로 합친 후 반환 / -- 조회될 내용이 없다면 커밋만 되고 결과 나오지 않음 BEGIN     FOR r IN (SELECT * FROM EMPLOYEES WHERE EMPLOYEE_ID = 1) LOOP           DBMS_OUTPUT.PUT_LINE('이름 : ' || CONCAT(r.FIRST_NAME || ' ', r.LAST_NAME));     END LOOP; END; / -- 조회되는만큼 결과 출력 BEGIN     FOR r IN (SELECT * FROM EMPLOYEES WHERE EMPLOYEE_ID = 100) LOOP         DBMS_OUTPUT.PUT_LINE('이름 : ' || CONCAT(r.FIRST_NAME || ' ', r.LAST_NAME));     END LOOP; END; / -- 응용 DECLARE     cnt NUMBER := 0; BEGIN     FOR r IN (SELECT * FROM EMPLOYEES WHERE EMPLOYEE_ID = 1) LOOP         DBMS_OUTPUT.PUT_LINE('이름 : ' || CONCAT(r.FIRST_NAME || ' ', r.LAST_NAME));         cnt := cnt + 1;     END LOOP;     IF cnt = 0 THEN         DBMS_OUTPUT.PUT_LINE('조회 결과가 없습니다. ');     END IF; END; / -- 굳이 IF절로 아래에서 카운트 세지 않고 발전된 형태 [놓침 1005 16:20] DECLARE     cnt NUMBER := 0; BEGIN     SELECT COUNT(*) INTO cnt FROM EMPLOYEES WHERE EMPLOYEE_ID = 100;      IF cnt = 0 THEN         DBMS_OUTPUT.PUT_LINE('조회 결과가 없습니다. ');     ELSE         FOR r IN (SELECT * FROM EMPLOYEES WHERE EMPLOYEE_ID = 100) LOOP             DBMS_OUTPUT.PUT_LINE('이름 : ' || CONCAT(r.FIRST_NAME || ' ', r.LAST_NAME));         END LOOP;     END IF; END; /

 

-- 최종 발전된 형태 DECLARE     cnt NUMBER := 0;     emp_id NUMBER; BEGIN     emp_id := 100;     SELECT COUNT(*) INTO cnt FROM EMPLOYEES WHERE EMPLOYEE_ID >= emp_id; -- 반드시 '1' 행의 데이터 조회해서 cnt에 담는 구문 (카운트, 결과 없으면 0)     IF cnt = 0 THEN         DBMS_OUTPUT.PUT_LINE('조회 결과가 없습니다. ');     ELSE         FOR r IN (SELECT * FROM EMPLOYEES WHERE EMPLOYEE_ID >= emp_id) LOOP             DBMS_OUTPUT.PUT_LINE('이름 : ' || CONCAT(r.FIRST_NAME || ' ', r.LAST_NAME));         END LOOP;     END IF; END; /

결과 이름 : Steven King 이름 : Neena Kochhar 이름 : Lex De Haan 이름 : Alexander Hunold           .           .           .

 

 

▶ 타입 변수 선언

 

<레코드 타입의 변수 선언과 초기화, 변수 값 출력> 

-- 레코드 (임의로 타입 만들기) DECLARE     TYPE my_record_type IS RECORD (          emp_id EMPLOYEES.EMPLOYEE_ID%TYPE        , f_name EMPLOYEES.FIRST_NAME%TYPE        , l_name EMPLOYEES.LAST_NAME%TYPE        , dept_name DEPARTMENTS.DEPARTMENT_NAME%TYPE        , job_title JOBS.JOB_TITLE%TYPE     );     row_data my_record_type; -- row_data 라는 변수를 위에서 만든 타입으로 선언 (필수) BEGIN     SELECT EMPLOYEE_ID         , FIRST_NAME         , LAST_NAME         , DEPARTMENT_NAME         , JOB_TITLE     INTO row_data  -- 간단      FROM EMPLOYEES A JOIN DEPARTMENTS B         ON A.DEPARTMENT_ID = B.DEPARTMENT_ID     JOIN JOBS C         ON A.JOB_ID = C.JOB_ID     WHERE EMPLOYEE_ID = 100;  -- 이해를 위해 결과 하나만 조회 -- 출력용 구문     DBMS_OUTPUT.PUT_LINE(row_data.emp_id || ' | ' || row_data.f_name || ' | ' || row_data.l_name);     DBMS_OUTPUT.PUT_LINE(row_data.dept_name || ' | ' || row_data.job_title); END; /

 

 

<테이블 타입의 변수 선언과 초기화, 변수 값 출력>

 자바의 배열과 유사, 컬렉션처럼 크기 지정 없는 가변배열의 형태이다.

DECLARE  -- 배열을 만드는데 그 배열을 저장할 데이터 타입을 TABLE OF ~ 로 지정해주는것      TYPE array_type IS TABLE OF EMPLOYEES.EMPLOYEE_ID%TYPE INDEX BY BINARY_INTEGER; -- 테이블타입 1번부터 시작해서 i번까지 접근하려면 인덱스가 필요한데, 숫자타입으로 인덱싱할 수 있게 BINARY_INTEGER     arr1 array_type; -- 타입 선언 (위에서 row_data my_record_type; 한 것처럼)     i NUMBER := 1;  -- 해당 배열에 사용할 인덱싱 위한 번호 (오라클 DB에서는 배열 사용 시 1번부터 쓴다) BEGIN -- 배열에 값을 저장하는 과정     FOR r IN (SELECT EMPLOYEE_ID FROM EMPLOYEES) LOOP        -- r 은 일종의 지역변수         arr1(i) := r.EMPLOYEE_ID;       -- arr1 변수 안에 EMPLOYEE_ID 값을 저장하는 것                                                -- 테이블 타입은 i = 1부터 시작, 1행에 값이 있을테니까(내생각)         i := i + 1;     END LOOP;     FOR n IN 1..i-1 LOOP                        -- 배열을 출력하는 과정         DBMS_OUTPUT.PUT_LINE(n || ': ' || arr1(n));       END LOOP; END; / -- 마지막 FOR LOOP 설명 : i는 지역 변수가 아니다. DECLARE에서 선언했으니 배열에 값 다 넣고난 최종 증가 된 i 값이 올 것이고 위의 마지막 루프 돌면 i + 1 된 상태로 다음 루프 들어올테니까 끝 범위 설정을 i - 1로 해준 것

-- 선생님 추가 예시. 꼭 보고 스스로 해보기 DECLARE     TYPE my_record_type IS RECORD (          emp_id EMPLOYEES.EMPLOYEE_ID%TYPE        , f_name EMPLOYEES.FIRST_NAME%TYPE        , l_name EMPLOYEES.LAST_NAME%TYPE        , dept_name DEPARTMENTS.DEPARTMENT_NAME%TYPE        , job_title JOBS.JOB_TITLE%TYPE     );     TYPE array_type IS TABLE OF my_record_type INDEX BY BINARY_INTEGER;     my_table array_type;     i NUMBER := 1; BEGIN     FOR r IN (SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME                    , DEPARTMENT_NAME, JOB_TITLE                 FROM EMPLOYEES A JOIN DEPARTMENTS B                   ON A.DEPARTMENT_ID = B.DEPARTMENT_ID                 JOIN JOBS C                   ON A.JOB_ID = C.JOB_ID) LOOP         my_table(i).emp_id := r.EMPLOYEE_ID;         my_table(i).f_name := r.FIRST_NAME;         my_table(i).l_name := r.LAST_NAME;         my_table(i).dept_name := r.DEPARTMENT_NAME;         my_table(i).job_title := r.JOB_TITLE;         i := i + 1;     END LOOP;     FOR n IN 1..i-1 LOOP         DBMS_OUTPUT.PUT(my_table(n).emp_id || ' | ' || my_table(n).f_name || ' | ' || my_table(n).l_name);         DBMS_OUTPUT.PUT_LINE(my_table(n).dept_name || ' | ' || my_table(n).job_title);     END LOOP; END; /

---

연습문제

 

<Q1> 

/* [1005 18:11]

  ACCOUNTS 테이블에 다음의 데이터 추가 DEVELOP1 계정

            - 2021년 01월 한 달간 매주 평일마다 식비로 6,500 원을 사용하였다.

*/

-- ACCOUNTS 테이블 생성 (#sample1) SELECT * FROM ACCOUNTS; DESC ACCOUNTS; CREATE TABLE ACCOUNTS (        ID         NUMBER      PRIMARY KEY      , AMOUNT     NUMBER      NOT NULL      , INOUT      CHAR(1)     CHECK(INOUT IN('I','O'))      , ACC_DATE   DATE        NOT NULL      , ACC_TYPE   CHAR(3)     REFERENCES ACCTYPES ); -- 문제 풀이의 기본이 될 함수 구문들 SELECT LAST_DAY(TO_DATE('202101', 'yyyymm')) FROM DUAL; -- 마지막 날 추출  31-1-21 SELECT EXTRACT(DAY FROM LAST_DAY(TO_DATE('202101', 'yyyymm'))) FROM DUAL; -- 일자만 추출  31 SELECT EXTRACT(DAY FROM LAST_DAY(TO_DATE('20210201', 'yyyymmdd') -1)) FROM DUAL; -- 2월달에서 하루를 빼는 방법 -- 문자열로 변환 그 문자열은 D 라는 포맷으로 변환 -- D : 요일 뽑기. SELECT TO_CHAR(TO_DATE('20210101', 'yyyymmdd'), 'D')      , TO_CHAR(TO_DATE('20210102', 'yyyymmdd'), 'D')      , TO_CHAR(TO_DATE('20210103', 'yyyymmdd'), 'D')      , TO_CHAR(TO_DATE('20210104', 'yyyymmdd'), 'D')      , TO_CHAR(TO_DATE('20210105', 'yyyymmdd'), 'D')      , TO_CHAR(TO_DATE('20210106', 'yyyymmdd'), 'D')      , TO_CHAR(TO_DATE('20210107', 'yyyymmdd'), 'D') FROM DUAL; -- 결과 값은 숫자로 나온다. -- 6 = 금, 7 = 토, 1 = 일, 2 = 월, 3 = 화, 4 = 수, 5 = 목. 7이나 1이 나오면 주말라는 걸 알 수 있음

-- (연습용) LAST_DAY 함수 이용해서 DAY 출력  DECLARE      lastday NUMBER;  BEGIN      lastday := EXTRACT(DAY FROM LAST_DAY(TO_DATE('202101', 'yyyymm'))); -- 월 바꿔가면서 적용하면 효율적      FOR d IN 1..lastday LOOP          DBMS_OUTPUT.PUT_LINE(d || '일');      END LOOP;  END;  / --프로시저를 사용하여 출력하는 내용을 화면에 보여주도록 설정하는 환경변수, 기본 값은 OFF SET SERVEROUTPUT ON; -- DEVELOP1 계정에는 설정 안했으므로 해줘야 출력 된다. 1일 2일 3일 4일 . . .

-- (실전용) 문제풀이 CREATE SEQUENCE ACCOUNS_SEQ; DECLARE     lastday NUMBER;     weekday NUMBER;     base_month VARCHAR(6);  BEGIN     base_month := '202101'; -- 가독성을 높인 월 적용     lastday := EXTRACT(DAY FROM LAST_DAY(TO_DATE(base_month, 'yyyymm')));     FOR d IN 1..lastday LOOP     weekday := TO_CHAR(TO_DATE(base_month || TO_CHAR(d, '00'), 'yyyymmdd'), 'D'); -- 요일에 해당하는 정수 값 할당         IF weekday NOT IN(1, 7) THEN         INSERT INTO ACCOUNTS VALUES(ACCOUNS_SEQ.NEXTVAL, 6500, 'O', TO_DATE(base_month || TO_CHAR(d, '00'), 'yyyymmdd'), 'O04');         END IF;     END LOOP; END; / SELECT * FROM ACCOUNTS; DELETE FROM ACCOUNTS; -- 시퀀스 삭제해가면서 확인 (계속 증가하기 때문) DROP SEQUENCE ACCOUNS_SEQ;

 

<Q2>

/*

    ACCOUNTS 테이블에 다음의 데이터 추가

        - 2021년 01월 한 달간 매주 평일 마다 식비로 6,500원을 사용하였다.

*/

-- 우선 매개변수 없는 프로시저로 생성  --DECLARE는 오류 날 수 있어서 지운다 DROP PROCEDURE PROC_한달식비일괄저장; CREATE OR REPLACE PROCEDURE PROC_한달식비일괄저장 IS     lastday NUMBER;     weekday NUMBER;     base_month VARCHAR(6); BEGIN     base_month := '202102';     lastday := EXTRACT(DAY FROM LAST_DAY(TO_DATE(base_month, 'yyyymm'))); -- 월 바꿔가면서 적용하면 효율적     FOR d IN 1..lastday LOOP     weekday := TO_CHAR(TO_DATE(base_month || TO_CHAR(d, '00'), 'yyyymmdd'), 'D'); -- 요일에 해당하는 정수값이 할당된다.         IF weekday NOT IN(1, 7) THEN         INSERT INTO ACCOUNTS VALUES(ACCOUNS_SEQ.NEXTVAL, 6500, 'O', TO_DATE(base_month || TO_CHAR(d, '00'), 'yyyymmdd'), 'O04');         END IF;     END LOOP; END; / -- 프로시저 동작 EXEC PROC_한달식비일괄저장(); EXECUTE PROC_한달식비일괄저장();

-- 매개변수 있는 프로시저, 작동하도록 구문 수정해야 함 CREATE OR REPLACE PROCEDURE PROC_한달식비일괄저장(i_base_month IN VARCHAR)  -- IN 매개변수 받겠다. i_base_month 이름으로 IS     lastday NUMBER;     weekday NUMBER;     base_month VARCHAR(6); BEGIN     base_month := i_base_month;     lastday := EXTRACT(DAY FROM LAST_DAY(TO_DATE(base_month, 'yyyymm'))); -- 월 바꿔가면서 적용하면 효율적     FOR d IN 1..lastday LOOP     weekday := TO_CHAR(TO_DATE(base_month || TO_CHAR(d, '00'), 'yyyymmdd'), 'D'); -- 요일에 해당하는 정수값이 할당된다.         IF weekday NOT IN(1, 7) THEN         INSERT INTO ACCOUNTS VALUES(ACCOUNS_SEQ.NEXTVAL, 6500, 'O', TO_DATE(base_month || TO_CHAR(d, '00'), 'yyyymmdd'), 'O04');         END IF;     END LOOP; END; / -- 매개변수 프로시저 실행만으로 정상적으로 INSERT 되는지 확인 EXEC PROC_한달식비일괄저장('202103'); SELECT * FROM ACCOUNTS;

 

<Q3>

-- 지출코드가 'O04' 인 데이터를 'O05' 로 변경하는 프로시저

-- 혼자 푼 것 (불필요한 정의, 할당 有) SELECT * FROM ACCTYPES; DESC ACCTYPES EXEC PROC_지출코드일괄변경('O04', 'O05'); SELECT* FROM ACCOUNTS; CREATE OR REPLACE PROCEDURE PROC_지출코드일괄변경(i_old_id IN CHAR, i_new_id IN CHAR) -- 프로시저는 길이 안정해도 됨 IS     OLD_ID CHAR(3);  -- 삭제     NEW_ID CHAR(3); -- 삭제 BEGIN     OLD_ID := i_old_id; -- 삭제     NEW_ID := i_new_id; -- 삭제     UPDATE ACCOUNTS SET ACC_TYPE = NEW_ID WHERE ACC_TYPE = OLD_ID;  END; /

-- 답안 SELECT* FROM ACCOUNTS; CREATE OR REPLACE PROCEDURE PROC_지출코드일괄변경(i_old_code IN VARCHAR, i_new_code IN VARCHAR) IS BEGIN     UPDATE ACCOUNTS        SET ACC_TYPE = i_new_code      WHERE ACC_TYPE = i_old_code;  END; / EXEC PROC_지출코드일괄변경('O04', 'O05'); SELECT * FROM ACCOUNTS;

 

<Q4>

-- 지정된 날짜 범위안에서의 지출코드가 'O05'인 데이터의 금액을 변경하는 프로시저

SELECT * FROM ACCOUNTS; DESC ACCOUNTS; CREATE OR REPLACE PROCEDURE PROC_금액변경(acc_type IN VARCHAR, i_amount IN NUMBER, f_date IN DATE, l_date IN DATE) IS                -- 매개변수 받을 변수 이름 습관적으로 앞에 i 붙이기, amount 그냥 쓰면 만들어지기만 하고 절대 동작 안 함 BEGIN     UPDATE ACCOUNTS        SET AMOUNT = i_amount      WHERE ACC_TYPE = acc_type        AND ACC_DATE BETWEEN f_date AND l_date; END; / EXEC PROC_금액변경('O05', 8000, TO_DATE('20210101', 'yyyymmdd'), TO_DATE('20210131', 'yyyymmdd')); SELECT * FROM ACCOUNTS;

 

<Q5>

-- 아래에 작성된 프로시저 형식을 보고 올바르게 동작 할 수 있는 프로시저를 만든다.

EXEC PROC_가계부작성(100000, TO_DATE('20211005', 'yyyymmdd'), '출금', '교통비');

EXEC PROC_가계부작성(3000000, TO_DATE('20211025', 'yyyymmdd'), '입금', '급여');

EXEC PROC_가계부작성(30000, TO_DATE('20211027', 'yyyymmdd'), '출금', '배달음식');

-- 존재하지 않는 출금/입금 타입은 자동으로 만들어질 수 있게 한다.

CREATE OR REPLACE PROCEDURE PROC_가계부작성(i1 IN NUMBER, i2 IN DATE, i3 IN VARCHAR, i4 IN VARCHAR) IS     inout VARCHAR(1);     type_error EXCEPTION;     acctype ACCTYPES.ID%TYPE;     cnt NUMBER;     code NUMBER; BEGIN     IF i3 = '출금' THEN         inout := 'O';     ELSIF i3 = '입금' THEN         inout := 'I';     ELSE         RAISE type_error;     END IF;     SELECT COUNT(*) INTO cnt FROM ACCTYPES WHERE TYPE_NAME = i4;     IF cnt = 1 THEN         SELECT ID INTO acctype FROM ACCTYPES WHERE TYPE_NAME = i4;     ELSE         SELECT COUNT(*) INTO code FROM ACCTYPES WHERE ID LIKE inout || '%';         acctype := inout || TRIM(TO_CHAR(code+1, '00'));         INSERT INTO ACCTYPES VALUES(acctype, i4);     END IF;     INSERT INTO ACCOUNTS VALUES(ACCOUNTS_SEQ.NEXTVAL, i1, inout, i2, acctype); EXCEPTION     WHEN type_error THEN         DBMS_OUTPUT.PUT_LINE('입금/출금만 할 수 있습니다.'); END; / EXEC PROC_가계부작성(100000, TO_DATE('20211005', 'yyyymmdd'), '출금', '교통비'); EXEC PROC_가계부작성(3000000, TO_DATE('20211025', 'yyyymmdd'), '입금', '급여'); EXEC PROC_가계부작성(30000, TO_DATE('20211027', 'yyyymmdd'), '출금', '배달음식');