[PL/SQL] PL/SQL 변수 선언 및 반복문(FOR LOOP, WHILE LOOP, EXIT WHEN), 제어문(IF, CASE)

EUN JY·2022년 4월 6일
PL/SQL
1

Database

목록 보기
3/21

1. 실습 준비

1-1. PL/SQL 결과 출력

  • PUT_LINE 프로시저를 이용
  • 프로시저를 사용하여 출력되는 내용을 화면에 보여주기 위해서는 환경 변수 SERVEROUTPUT(디폴트값이 OFF이므로) ON으로 변경
SET serveroutput ON
BEGIN 
    dbms_output.put_line('ynjch');
END;

1-2. 테스트용 테이블 준비

  • USER_INFO 테이블 생성
CREATE TABLE USER_INFO 
(
    USER_NUM NUMBER(10) NOT NULL 
    , USER_NM VARCHAR2(20) 
    , USER_BIRTH VARCHAR2(8)
);
SELECT * FROM USER_INFO;
DROP TABLE USER_INFO;
  • USER_INFO 테이블 시퀀스 생성
CREATE SEQUENCE USER_NUM_SEQ START WITH 1 INCREMENT BY 1 MAXVALUE 10000000 CYCLE NOCACHE;
  • 시퀀스 조회 및 초기화
SELECT * FROM USER_SEQUENCES WHERE SEQUENCE_NAME = 'USER_NUM_SEQ';
ALTER SEQUENCE USER_NUM_SEQ INCREMENT BY -1000;
SELECT USER_NUM_SEQ.NEXTVAL FROM DUAL;
ALTER SEQUENCE USER_NUM_SEQ INCREMENT BY 1;
  • USER_SCORE 테이블 생성
CREATE TABLE USER_SCORE 
(
    USER_NUM NUMBER(10) NOT NULL 
    , USER_GRADE VARCHAR(5)
    , CONSTRAINT user_score_pk PRIMARY KEY(USER_NUM)
);
SELECT * FROM USER_SCORE;
DROP TABLE USER_SCORE;
  • 데이터 INSERT
INSERT INTO USER_INFO values(USER_NUM_SEQ.NEXTVAL, '차은우', '19971026');
INSERT INTO USER_INFO values(USER_NUM_SEQ.NEXTVAL, '서강준', '19971001');
INSERT INTO USER_INFO values(USER_NUM_SEQ.NEXTVAL, '강태오', '19911022');
INSERT INTO USER_INFO values(USER_NUM_SEQ.NEXTVAL, '지창욱', '20010107');
INSERT INTO USER_INFO values(USER_NUM_SEQ.NEXTVAL, '이준혁', '19710606');
INSERT INTO USER_INFO values(USER_NUM_SEQ.NEXTVAL, '임시완', '19970714');
INSERT INTO USER_SCORE values(1, 'A');
INSERT INTO USER_SCORE values(2, 'C');
INSERT INTO USER_SCORE values(3, 'A');
INSERT INTO USER_SCORE values(4, 'D');
INSERT INTO USER_SCORE values(5, 'B');
INSERT INTO USER_SCORE values(6, 'C');
  • STORE_INFO 테이블 생성
CREATE TABLE STORE_INFO 
(
    STORE_NUM NUMBER(10) NOT NULL 
    , STORE_NM VARCHAR2(30) 
    , STORE_TEL VARCHAR2(20) 
    , STORE_ADDR VARCHAR2(30)
    , OWNER_NUM NUMBER(10)
    , CONSTRAINT STORE_info_pk PRIMARY KEY(STORE_NUM)
    , CONSTRAINT STORE_info_fk FOREIGN KEY(OWNER_NUM) REFERENCES USER_INFO (USER_NUM)
);
SELECT * FROM STORE_INFO;
DROP TABLE STORE_INFO;

/* 컬럼 사이즈 변경 시 */
ALTER TABLE STORE_INFO MODIFY STORE_NM VARCHAR2(50);
ALTER TABLE STORE_INFO MODIFY STORE_ADDR VARCHAR2(50);
  • STORE_INFO 테이블 시퀀스 생성
CREATE SEQUENCE STORE_NUM_SEQ START WITH 1 INCREMENT BY 1 MAXVALUE 10000000 CYCLE NOCACHE;
  • 시퀀스 조회 및 초기화
SELECT * FROM USER_SEQUENCES WHERE SEQUENCE_NAME = 'STORE_NUM_SEQ';
ALTER SEQUENCE STORE_NUM_SEQ INCREMENT BY -1000;
SELECT STORE_NUM_SEQ.NEXTVAL FROM DUAL;
ALTER SEQUENCE STORE_NUM_SEQ INCREMENT BY 1;
  • 시퀀스 증가 함수 만들기 
CREATE FUNCTION FUNC_STORE_NUM_SEQ RETURN NUMBER IS
BEGIN
    RETURN STORE_NUM_SEQ.NEXTVAL;
END;
/
SELECT * FROM USER_SOURCE WHERE TYPE = 'FUNCTION' AND NAME = 'FUNC_STORE_NUM_SEQ';
DROP FUNCTION FUNC_STORE_NUM_SEQ;
  • STORE_INFO 데이터 INSERT
INSERT INTO STORE_INFO (STORE_NUM, STORE_NM, STORE_TEL, STORE_ADDR, OWNER_NUM)
SELECT              FUNC_STORE_NUM_SEQ, '카페노티드 제주애월', '070-4415-9377', '제주특별자치도 제주시', 1 FROM DUAL
UNION ALL SELECT    FUNC_STORE_NUM_SEQ, '런던베이글뮤지엄 도산점', '02-111-4453', '서울 강남구', 2 FROM DUAL
UNION ALL SELECT    FUNC_STORE_NUM_SEQ, '카페 레이어드 연남점', '02-2341-4277', '서울 마포구', 3 FROM DUAL
UNION ALL SELECT    FUNC_STORE_NUM_SEQ, '카페 쿠운', '02-3245-8775', '서울 서초구', 5 FROM DUAL
UNION ALL SELECT    FUNC_STORE_NUM_SEQ, '태시트 TACIT', '02-2341-4277', '강원 고성군', 5 FROM DUAL
UNION ALL SELECT    FUNC_STORE_NUM_SEQ, '귤꽃다락', '02-2341-4277', '제주특별자치도 서귀포시', 5 FROM DUAL;

COMMIT;

2. 변수

  • 선언부(DECLARE) 변수명, 데이터 타입을 기술
    • identifier : 변수명(식별자)
    • CONSTANT : 상수로 지정 (초기치를 반드시 할당해야 함)
    • datatype : 자료형을 기술
    • NOT NULL : 값을 반드시 포함
    • expression : Literal, 다른 변수, 연산자나 함수를 포함하는 표현식
identifier [CONSTANT] datatype [NOT NULL] [:=|DEFAULT expression];

2-1. 변수 선언

  • 단일 변수 선언
DECLARE NAME VARCHAR2(10);
DECLARE NAME VARCHAR2(10) := 'ynjch';
DECLARE NAME VARCHAR2(10) DEFAULT 'ynjch';
  • 여러 개 선언 시
DECLARE 
	NAME    VARCHAR2(20);
	AGE     NUMBER(2); 
	GENDER  VARCHAR(5)   DEFAULT '여';

2-2. Type으로 변수 선언

2-2-1. %ROWTYPE

  • 해당 테이블이나 뷰의 컬럼 속성을 그대로 들고 오는 형태
    • 변수명 테이블명%ROWTYPE
DECLARE
    userNum NUMBER(5) := 1;
    rowData USER_INFO%ROWTYPE;
BEGIN
    SELECT * INTO rowData
    FROM USER_INFO
    WHERE USER_NUM = userNum;
    dbms_output.put_line(rowData.USER_NUM||'. '||rowData.USER_NM);
END;

2-2-2. %TYPE

  • 해당 테이블의 컬럼 속성을 지정하여 그대로 들고 오는 형태
    • 변수명 테이블명.컬럼명%TYPE
DECLARE
    userNum NUMBER(5) := 1;
    userNm USER_INFO.USER_NM%TYPE;
    userBirth USER_INFO.USER_BIRTH%TYPE;
BEGIN
    SELECT
        USER_NM, USER_BIRTH INTO userNm, userBirth
    FROM USER_INFO
    WHERE USER_NUM = userNum;
    dbms_output.put_line(userNm||'('||userBirth||')');
END;
  • SELECT 문을 이용하여 값 대입
    • INTO 절에 조회 결과 값을 저장할 변수를 기술
    • SELECT 문은 INTO 절에 의해 하나의 행만을 저장 가능
    • SELECT 이후 작성한 컬럼은 INTO 절에 있는 변수와 1:1로 대응해야 함 > 개수와 데이터 타입, 길이를 일치시켜야 함

3. 반복문

  • FOR LOOP문, LOOP문으로 나뉨

3-1. FOR LOOP문

  • index는 자동 선언되는 binary_integer형 변수이며 1씩 증가
  • REVERSE 옵션이 사용 될 경우 index는 upper_bound에서 lower_bound로 1씩 감소
  • IN 다음에는 coursor나 SELECT 문이 올 수 있음
FOR index in [REVERSE] 시작값 .. END값 LOOP
    STATEMENT 1
    STATEMENT 2
    ...
END LOOP;

3-1-1. 예제

  • 짝수, 홀수 판별
    • IF문을 사용하여 분기처리
BEGIN
    FOR i IN 1..5 LOOP
        IF MOD(i,2) = 0 THEN 
            dbms_output.put_line(i||'는 짝수');
        ELSE
            dbms_output.put_line(i||'는 홀수');
        END IF;
    END LOOP;
END;
  • USER_NUM 1~3 까지의 데이터 출력하기
DECLARE
    userNum NUMBER(5) := 1;
    rowData USER_INFO%ROWTYPE;
BEGIN
    FOR i IN 1..3 LOOP
        userNum := i;
        SELECT * INTO rowData
        FROM USER_INFO
        WHERE USER_NUM = userNum;
        dbms_output.put_line(rowData.USER_NM||'('||rowData.USER_NUM||')');
    END LOOP;
END;
  • SELECT 문을 이용하여 별도의 변수 선언 없이 사용 가능
BEGIN
    FOR userList IN (
        SELECT * FROM USER_INFO
    ) LOOP
        dbms_output.put_line(userList.USER_NM||'('||userList.USER_NUM||')');
    END LOOP;
END;
  • 나와 나이 비교하기
DECLARE
    userNum NUMBER(5) := 1;
    rowData USER_INFO%ROWTYPE;
BEGIN
    SELECT * INTO rowData
    FROM USER_INFO
    WHERE USER_NUM = userNum;
        
    FOR userList IN (
        SELECT * FROM USER_INFO WHERE USER_NUM <> userNum
    ) LOOP
        IF ( SUBSTR(rowData.USER_BIRTH,1,4) = SUBSTR(userList.USER_BIRTH,1,4) ) THEN
            dbms_output.put_line(userList.USER_NM||'은(는) '||rowData.USER_NM||'의 친구');
        ELSIF ( SUBSTR(rowData.USER_BIRTH,1,4) > SUBSTR(userList.USER_BIRTH,1,4) ) THEN
            dbms_output.put_line(userList.USER_NM||'은(는) '||rowData.USER_NM||'보다 나이가 많다');
        ELSE 
            dbms_output.put_line(userList.USER_NM||'은(는) '||rowData.USER_NM||'보다 나이가 적다');
        END IF;
    END LOOP;
END;

3-1-2. 예제

  • 테이블 JOIN하여 유저들의 점수 조회하기 1
    • USER_INFO, USER_SCORE 테이블의 컬럼을 담은 트랜잭션 GTT 생성(TX_USER_INFO_SCORE)
    • WITH문, FOR LOOP문 사용
CREATE GLOBAL TEMPORARY TABLE TX_USER_INFO_SCORE (
    USER_NUM NUMBER(10) NOT NULL 
    , USER_NM VARCHAR2(20) 
    , USER_BIRTH VARCHAR2(8)
    , USER_GRADE VARCHAR(5)
)
ON COMMIT DELETE ROWS;

DECLARE
    userCnt NUMBER;
    rowData TX_USER_INFO_SCORE%ROWTYPE;
BEGIN 
    -- 1. userCnt 구하기 
    SELECT COUNT(1) INTO userCnt FROM USER_INFO;
    dbms_output.put_line('userCnt : '||userCnt);
    
    -- 2. for loop
    FOR i IN 1..userCnt LOOP
        WITH TEMP_USER_INFO_SCORE AS -- WITH 문
        (
            SELECT I.USER_NUM, I.USER_NM, I.USER_BIRTH, S.USER_GRADE
            FROM USER_INFO I
            LEFT JOIN USER_SCORE S
            ON I.USER_NUM = S.USER_NUM
        )
        SELECT * INTO rowData FROM TEMP_USER_INFO_SCORE WHERE USER_NUM = i;
        dbms_output.put_line(rowData.USER_NM ||'('|| rowData.USER_NUM ||')''s score : ' || rowData.USER_GRADE);
    END LOOP;
END;
  • 테이블 JOIN하여 유저들의 점수 조회하기 2
    • FOR [변수명] IN [쿼리] LOOP 사용
    • WITH문, FOR LOOP문 사용
DECLARE
BEGIN     
    FOR userList IN (
        WITH TEMP_USER_INFO_SCORE AS -- WITH 문
        (
            SELECT I.USER_NUM, I.USER_NM, I.USER_BIRTH, S.USER_GRADE
            FROM USER_INFO I
            LEFT JOIN USER_SCORE S
            ON I.USER_NUM = S.USER_NUM
        )
        SELECT * FROM TEMP_USER_INFO_SCORE ORDER BY USER_NUM
    ) LOOP
        dbms_output.put_line(userList.USER_NM ||'('|| userList.USER_NUM ||')''s score : ' || userList.USER_GRADE);
    END LOOP;
END;

3-2. LOOP문

  • EXIT : 무조건 LOOP 문을 빠져나감
  • EXIT WHEN : WHEN 절에서 LOOP를 빠져나가는 조건을 제어
LOOP 
    STATEMENT 1
        다른 LOOP를 포함하여 중첩으로 사용 가능
    EXIT [WHEN CONDITION]
END LOOP;

3-2-1. 예제

  • 1에서 5까지 숫자 출력하기
DECLARE
    startNum NUMBER := 1;
    endNum NUMBER := 5;
    loopCnt NUMBER := 0;
BEGIN
    LOOP
        dbms_output.put_line('현재 숫자 : '||startNum);
        loopCnt := loopCnt + 1;
        startNum := startNum + 1;
        EXIT WHEN loopCnt >= endNum;
    END LOOP;
    dbms_output.put_line('반복 횟수 : '||loopCnt);
END;

3-2-2. WHILE LOOP문

  • 3-2-1. 의 예제를 WHILE LOOP문으로 표현
DECLARE
    startNum NUMBER := 1;
    endNum NUMBER := 5;
    loopCnt NUMBER := 0;
BEGIN
    WHILE loopCnt < endNum LOOP
        dbms_output.put_line('현재 숫자 : '||startNum);
        loopCnt := loopCnt + 1;
        startNum := startNum + 1;
    END LOOP;
    dbms_output.put_line('반복 횟수 : '||loopCnt);
END;

4. 제어문

  • 일반적인 프로그래밍에서 사용되는 IF문, CASE문 등의 제어문(조건절) 사용 가능

4-1. IF문

  • ELSE IF를 ELSIF로 쓰는 것에 주의
IF 조건1 THEN
    처리문1
ELSIF 조건2 THEN
    처리문2
ELSE
    처리문
END IF;

4-1-1. 예제

DECLARE
    userNum NUMBER := 1;
    userBirthYear VARCHAR(4);
    rowData USER_INFO%ROWTYPE;
BEGIN
    SELECT * INTO rowData
    FROM USER_INFO
    WHERE USER_NUM = userNum;
    userBirthYear := SUBSTR(rowData.USER_BIRTH, 1, 4);
    
    IF (2022 - userBirthYear + 1) > 49 THEN
        DBMS_OUTPUT.PUT_LINE(rowData.USER_NM||'님은 50세 이상입니다.');
    ELSIF (2022 - userBirthYear + 1) > 29 THEN
        DBMS_OUTPUT.PUT_LINE(rowData.USER_NM||'님은 30세 이상입니다.');
    ELSIF (2022 - userBirthYear + 1) > 19 THEN
        DBMS_OUTPUT.PUT_LINE(rowData.USER_NM||'님은 20세 이상입니다.');
    ELSE
        DBMS_OUTPUT.PUT_LINE(rowData.USER_NM||'님은 20세 미만입니다.');
    END IF;
END;

4-2. CASE문

  • 조건에 따른 값을 대입하거나, PL/SQL 명령문 실행 시 사용

4-2-1. 예제

  • 성적 확인
DECLARE
    userNum NUMBER := 1;
    userGrade USER_SCORE.USER_GRADE%TYPE;
    resultStr VARCHAR(30);
BEGIN 
    SELECT USER_GRADE INTO userGrade
    FROM USER_SCORE 
    WHERE USER_NUM = userNum;
    
    resultStr := 
    CASE userGrade
        WHEN 'A' THEN 'Perfect'
        WHEN 'B' THEN 'Very Good'
        WHEN 'C' THEN 'Good'
        ELSE 'Hmm...'
    END;
    DBMS_OUTPUT.PUT_LINE(userGrade||' is '||resultStr);
END;
profile
EUN JY
개린이
이전 포스트

[PL/SQL] PL/SQL 특징 및 문법 구조

다음 포스트

[Oracle] 임시 테이블 트랜잭션 GTT와 세션 GTT / WITH문을 활용한 SubQuery 재사용

1개의 댓글

comment-user-thumbnail
홈런볼
2024년 3월 21일

게시물 공유 감사합니다 :)

답글 달기