DB0705

PL/SQL의 확장 요소

DECLARE : 지역변수와 커서, 사용자 예외를 선언

:=
SELECT INTO
FETCH INTO : 변수할당

BEGIN…END : 문장의 블록

-- : 한라인 주석
/…/ : 여러 라인 주석

IF…ELSIF…END IF
CASE…END CASE : 분기문

WHILE…END LOOP
LOOP…END LOOP
FOR…END LOOP : 반복문

EXIT : 반복 블럭 탈출

GOTO : 처리 순서 변경

변수 종류

SCLAR 변수 : 데이터 하나만을 저장하는 일반적인 변수
REFERENCES 변수 : 해당 테이블의 row 나 column의 타입과 크기를 참조하는 변수
COMPOSITE 변수 : PL/SQL에서 사용하는 배열 변수-RECORD TYPE, TABLE TYPE
BIND 변수
: 파라미터로 넘겨지는 IN, INOUT에서 사용되는 변수
: 리턴되는 값을 전달받기 위해 선언되는 변수

SQL - SET SERVEROUTPUT ON

• DBMS_OUTPUT 결과값을 화면에 출력하기 위해 환경설정 변수 변경
• 매 세션마다 초기화 되므로 매번 재설정 필요
• DBMS_OUTPUT은 오라클에서 입,출력을 위해 제공하는 패키지

LPAD

SELECT LPAD('A', 2) FROM DUAL;
SELECT LPAD('A', 2, ' ') FROM DUAL;
SELECT LPAD('A', 3, 'B') FROM DUAL;  --3바이트 저장 장소에 왼쪽 출입구로 A 채우고 나머지 B로 채워라
SELECT LPAD('AA', 3, 'B') FROM DUAL;
SELECT LPAD('AA', 10, 'B') FROM DUAL; --BBBBBBBBAA

RPAD

SELECT RPAD('A', 2) FROM DUAL;
SELECT RPAD('A', 5, 'B') FROM DUAL; --ABBBB

IF 문

-- SELECT  INTO 로 변수에 값을 할당
DECLARE
    v_avg_sale PROD.PROD_SALE%TYPE;  -- v_avg_sale에 NUMBER(10,0) 형태로 변수 담김
    v_sale NUMBER := 500000;
BEGIN
    DBMS_OUTPUT.ENABLE;
    
    SELECT AVG(prod_sale) INTO v_avg_sale FROM PROD;
    
    IF v_sale < v_avg_sale THEN
        DBMS_OUTPUT.PUT_LINE('평균 단가가 500000 초과입니다.');
    ELSE
        DBMS_OUTPUT.PUT_LINE('평균 단가가 500000 이하입니다.');
    END IF;
END;
/

CASE 문

--SQL 에서 사용하는 CASE 문과 동일하다.(단, 차이점은 END CASE 로 마지막을 지정)
DECLARE
    V_NUM NUMBER := 86;
BEGIN
    V_NUM := TRUNC(V_NUM/10);

    CASE V_NUM
        WHEN 10 THEN
            DBMS_OUTPUT.PUT_LINE('수' || '(' || V_NUM || ')');
        WHEN 9 THEN
            DBMS_OUTPUT.PUT_LINE('수' || '(' || V_NUM || ')');
        WHEN 8 THEN
            DBMS_OUTPUT.PUT_LINE('우' || '(' || V_NUM || ')');
        WHEN 7 THEN
            DBMS_OUTPUT.PUT_LINE('미' || '(' || V_NUM || ')');
        ELSE
            DBMS_OUTPUT.PUT_LINE('분발합시가');
    END CASE;
END;
/

LPAD

SELECT LPAD('A', 2) FROM DUAL;
SELECT LPAD('A', 2, ' ') FROM DUAL;
SELECT LPAD('A', 3, 'B') FROM DUAL;  --3바이트 저장 장소에 왼쪽 출입구로 A 채우고 나머지 B로 채워라
SELECT LPAD('AA', 3, 'B') FROM DUAL;
SELECT LPAD('AA', 10, 'B') FROM DUAL; --BBBBBBBBAA

RPAD

SELECT RPAD('A', 2) FROM DUAL;
SELECT RPAD('A', 5, 'B') FROM DUAL; --ABBBB

WHILE문

--WHILE 문
--  1부터 10까지 더하기
DECLARE
 V_SUM NUMBER := 0;
 V_VAR NUMBER := 1;
BEGIN
 WHILE V_VAR <= 10 LOOP
   V_SUM := V_SUM + V_VAR;
   V_VAR := V_VAR + 1;
 END LOOP;
 DBMS_OUTPUT.PUT_LINE('1부터 10까지의 합 = ' || V_SUM);
END;
/

--WHILE문을  사용하여 * 로 피라미드 만들기
DECLARE
    V_ID    NUMBER := 1;
BEGIN
    WHILE V_ID < 20 LOOP
        DBMS_OUTPUT.PUT_LINE(RPAD('*', V_ID, '*'));
        V_ID := V_ID + 2;
    END LOOP;
END;
/

--  WHILE문을 사용하여 다음 형태의 피라미드 만들기 
DECLARE
    V_ID NUMBER := 1;
    V_ID2 NUMBER := 10;
BEGIN
    DBMS_OUTPUT.PUT_LINE(' ');
    WHILE V_ID < 20 LOOP
        DBMS_OUTPUT.PUT(RPAD(' ', V_ID2, ' '));
        DBMS_OUTPUT.PUT_LINE(RPAD('*', V_ID, '*'));
        V_ID := V_ID + 2;
        V_ID2 := V_ID2 - 1;
    END LOOP;
END;
/

--다중 WHILE문을  사용하여 구구단
DECLARE
    V_I NUMBER := 2;
    V_J NUMBER := 1;
BEGIN
    WHILE V_I < 10 LOOP
   		DBMS_OUTPUT.PUT_LINE(' ');
        DBMS_OUTPUT.PUT_LINE(V_I || ' 단 ');
        
        V_J := 1;
        
        WHILE V_J < 10 LOOP
            DBMS_OUTPUT.PUT_LINE(V_I || ' X ' || V_J || ' = ' || V_I * V_J);
            
            V_J := V_J + 1;
            END LOOP;
            
            V_I := V_I + 1;
    END LOOP;
END;
/

WHILE 문-커서 속성

• Oracle Server는 SQL문장을 실행할 때 PL/SQL은 SQL식별자를 가지는 암시적 커서를 생성, 또한 PL/SQL은 자동적으로 이 커서를 관리함

  SQL%ISOPEN : 항상 FALSE, (암시적 커서는 바로 CLOSE가 됨)
  SQL%NOTFOUND : SQL문장이 어떠한 영향을 미치지 않았다면 TRUE
  SQL%FOUND : SQL문장이 하나 이상의 영향을 미쳤다면 TRUE
  SQL%ROWCOUNT : SQL 문장에 의해 영향을 받은 행의 수

DECLARE
    V_NM VARCHAR2(20);
BEGIN
    SELECT LPROD_NM INTO V_NM FROM LPROD WHERE LPROD_GU = 'X201';
    IF SQL%FOUND THEN
        DBMS_OUTPUT.PUT_LINE('받은 값 = ' || V_NM);
        DBMS_OUTPUT.PUT_LINE('행 수 = ' || SQL%ROWCOUNT);
    END IF;
END;
/

--상품분류 테이블에 6개의 코드 증가
DECLARE
    V_ADD NUMBER(5) := 1000;
    V_CODE CHAR(4) := '';
    V_ID NUMBER(5);
BEGIN
    SELECT MAX(LPROD_ID) INTO V_ID FROM LPROD;
    WHILE V_ADD <= 1005 LOOP
        V_ADD := V_ADD + 1;
        V_ID := V_ID + 1;
        V_CODE := 'TT' || SUBSTR(TO_CHAR(V_ADD), -2);
        INSERT INTO LPROD(LPROD_ID, LPROD_GU, LPROD_NM)
VALUES(V_ID, V_CODE, 'LOOP TEST');
        IF SQL%FOUND THEN
        DBMS_OUTPUT.PUT_LINE('신규코드' || V_CODE || '가 추가되었음');
        END IF;
    END LOOP;
END;
/

--WHILE문을 사용하여  상기 INSERT된 데이터를 삭제 (삭제가 되었는지 확인 메시지 출력)
DECLARE
    V_ADD NUMBER(5) := 1007;
    V_CODE CHAR(4) := '';
    V_ID NUMBER(5);
BEGIN
    SELECT MAX(LPROD_ID) INTO V_ID FROM LPROD;
    
    WHILE V_ADD >= 1002 LOOP
        DELETE FROM LPROD WHERE LPROD_ID = V_ID; --V_ID : 16, 15, 14, 13, 12, 11
        
        V_ADD := V_ADD -1; --1006, 1005, 1004, 1003, 1002, 1001까지 1씩 감소(6회 반복)
        V_ID := V_ID -1;             --LPROD_ID 최대값 즉, 16, 15, 14, 13, 12, 11
        V_CODE := 'TT' || SUBSTR(TO_CHAR(V_ADD), -2); --화면에 결과를 출력하기 위함
        
        IF SQL%FOUND THEN
            DBMS_OUTPUT.PUT_LINE('기존코드' || V_CODE || '가 삭제되었습니다.');
        END IF;
    END LOOP;
END;
/

LOOP 문

: 조건이 없는 단순한 무한 반복문, EXIT 문을 사용하여 반복문 탈출

--  1부터 10까지 더하기
DECLARE
    V_SUM NUMBER := 0;
    V_VAR NUMBER := 1;
BEGIN
    LOOP
        V_SUM := V_SUM + V_VAR;
        V_VAR := V_VAR + 1;
        IF V_VAR > 10 THEN
            EXIT;
        END IF;
    END LOOP;
    DBMS_OUTPUT.PUT_LINE('1부터 10까지의 합 = ' || V_SUM);
END;
/