[PL/SQL] 제어문, 함수, 프로시저 문제

01

다음은 구구단을 출력하는 익명 블록이다. 이 블록을 실행해 보고 결과가 왜 그렇게 나왔는지 설명해 보자.

DECLARE
    vn_base_num NUMBER := 3;
BEGIN
    FOR i IN 1..9
        LOOP
            DBMS_OUTPUT.PUT_LINE (vn_base_num || '*' || i || '= ' || vn_base_num * i);
        END LOOP;
END;

-- 잘못된 쿼리
DECLARE
    vn_base_num constant NUMBER := 3;
BEGIN
    FOR i IN REVERSE 9..1
        LOOP
            DBMS_OUTPUT.PUT_LINE (vn_base_num || '*' || i || '= ' || vn_base_num * i);
        END LOOP;
END;


BEGIN
    FOR i IN REVERSE 1 .. 9
        LOOP
            DBMS_OUTPUT.put_line (i);
        END LOOP;
END;

결과값

WMSADM> BEGIN
            FOR i IN REVERSE 1 .. 9
                LOOP
                    DBMS_OUTPUT.put_line (i);
                END LOOP;
        END;
[2022-09-14 10:45:35] completed in 25 ms
9
8
7
6
5
4
3
2
1
WMSADM> DECLARE
            vn_base_num NUMBER := 3;
        BEGIN
            FOR i IN 1..9
                LOOP
                    DBMS_OUTPUT.PUT_LINE (vn_base_num || '*' || i || '= ' || vn_base_num * i);
                END LOOP;
        END;
[2022-09-14 10:45:38] completed in 101 ms
3*1= 3
3*2= 6
3*3= 9
3*4= 12
3*5= 15
3*6= 18
3*7= 21
3*8= 24
3*9= 27
WMSADM> DECLARE
            vn_base_num constant NUMBER := 3;
        BEGIN
            FOR i IN REVERSE 9..1
                LOOP
                    DBMS_OUTPUT.PUT_LINE (vn_base_num || '*' || i || '= ' || vn_base_num * i);
                END LOOP;
        END;
[2022-09-14 10:45:38] completed in 43 ms
WMSADM> BEGIN
            FOR i IN REVERSE 1 .. 9
                LOOP
                    DBMS_OUTPUT.put_line (i);
                END LOOP;
        END;
[2022-09-14 10:45:38] completed in 23 ms
9
8
7
6
5
4
3
2
1

02

SQL 함수 중 INITCAP이라는 함수가 있다. 이 함수는 매개변수로 전달한 문자열에서 앞 글자만 대문자로 변환한다. INITCAP과 똑같이 동작하는 my_initcap이라는 이름으로 함수를 만들어 보자(단 여기서는 공백 한 글자로 단어 사이를 구분한다고 가정한다).

create or replace function fn_my_initcap(str varchar2)
    return varchar2
is
	vn_res_str varchar2(80);
	vn_len number := length(str);
	vn_position number := 0;
begin
    -- 첫번째 문자가 알파벳이라면 대문자로 치환
    if regexp_like(substr(str, 1, 1), '^[a-z]') then
        vn_res_str := vn_res_str || upper(substr(str, 1, 1));
    else --첫번째 문자가 알파벳이 아닐경우
        vn_res_str := vn_res_str || substr(str, 1, 1);
    end if;

    --두번째 문자부터 끝까지
    for i in 2 .. vn_len
        loop
            --해당 위치 문자 이미 변경 continue
            if vn_position = i then
                continue;
            end if;
            --공백 바로 뒤 문자를 변경 소 -> 대
            if instr(str, ' ', i) = i then
                if i < vn_len then
                    vn_res_str := vn_res_str || ' ';
                    vn_res_str := vn_res_str || upper(substr(str, i+1, 1));
                    vn_position := i + 1;
                end if;
            -- 공백이 아닐 시
            else
                vn_res_str := vn_res_str || substr(str, i, 1);
            end if;
        end loop;

    --새로운 문자열 리턴
    return vn_res_str;
end;

select fn_my_initcap('hello world asdfasdf 1111dasdfasd 14fadsf fdsaffasdf   a@a') as fn_my_initcap
from dual;

WITH test AS (SELECT 'hello world testtest test' as test_str
              FROM DUAL
              UNION ALL
              SELECT 'cjtest2 world2 teaatest2' as test_str
              FROM DUAL)
SELECT test_str as                                 old_value,
       LISTAGG(upper(substr(split_value, 1, 1)) || substr(split_value, 2), ' ')
               within group (order by split_value) new_value
FROM (SELECT test_str, REGEXP_SUBSTR(test_str, '(.*?)([[:space:]]|$)', 1, COLUMN_VALUE, NULL, 1) AS split_value
      FROM TEST,
          TABLE (
              CAST(MULTISET(SELECT LEVEL
                            FROM DUAL
                            CONNECT BY LEVEL < REGEXP_COUNT(test_str, '(.*?)([[:space:]]|$)')) AS SYS.ODCINUMBERLIST
                  )
              ))
GROUP BY test_str;

WITH test AS (SELECT 1 as seq,'hello world testtest test' as test_str
              FROM DUAL
              UNION ALL
              SELECT 2 as seq, 'cjtest2 world2 teaatest2' as test_str
              FROM DUAL)
SELECT max(seq) as seq, test_str as                                 old_value,
       LISTAGG(upper(substr(split_value, 1, 1)) || substr(split_value, 2), ' ')
               within group (order by COLUMN_VALUE) new_value
FROM (SELECT seq,COLUMN_VALUE, test_str, REGEXP_SUBSTR(test_str, '(.*?)([[:space:]]|$)', 1, COLUMN_VALUE, NULL, 1) AS split_value
      FROM TEST,
          TABLE (
              CAST(MULTISET(SELECT LEVEL
                            FROM DUAL
                            CONNECT BY LEVEL < REGEXP_COUNT(test_str, '(.*?)([[:space:]]|$)')) AS SYS.ODCINUMBERLIST
                  )
              ))
GROUP BY  test_str
order by seq

03

날짜형 SQL 함수 중에는 해당 월 마지막 일자를 반환하는 LAST_DAY란 함수가 있다. 매개변수로 문자형으로 날짜를 받아, 해당 날짜의 월 마지막 날짜를 문자형으로 반환하는 함수를 my_last_day란 이름으로 만들어 보자.