(28)[ORACLE] 11일차 : PL/SQL

 

 

PL/SQL이란?
프로그래밍 언어의 특성을 가지는 SQL의 확장이며, 데이터 조작과 질의 문장은
PL/SQL의 절차적 코드 안에 포함된다. 
주로 자료 내부에서 SQL명령문만으로 처리하기에는 복잡한 자료의 저장이나
프로시저와 트리거 등을 작성하는데 사용된다.

PL/SQL은 프로그램을 논리적인 블록으로 나누는 구조화 된 블록언어이다
PL/SQL 블록은 선언부(선택적),실행부(필수적),예외 처리부(선택적)로 구성되어 있고, 
BEGIN과 END 키워드는 반드시 기술해 주어야 한다.

 

PL/SQL 프로그래밍 단위

PL/SQL 익명 블록

함수

프로시저

패기지 : 패키지 명세, 패키지 바디

트리거

 

 

기본문법

--PL/SQL SELECT
SELECT 컬럼, 컬럼 INTO 변수, 변수, FROM  테이블 WHERE 조건;

%TYPE 속성 : 테이블의 컬럼을 참조하는 속성

DECLARE
    --변수 선언 
    vname    emp.name%TYPE;
    vpay     NUMBER;
    BEGIN
            SELECT name,sal+bonus INTO vname,vpay
            FROM emp
            WHERE empNO = '1001';
            
            DBMS_OUTPUT.PUT_LINE('이름 :' || vname);
            DBMS_OUTPUT.PUT_LINE('급여 :' || vpay);
    
    END;

 

DECLARE : 실행부에서 참조할 모든 변수,상수,커서,exception을 선언한다.

BEGIN ~ END : 처리할 SQL이나 PL/SQL 블록을 기술한다.

EXCEPTION : 실행부에서 에러가 발생했을때 수행될 문장을 기술한다. 

--%ROWTYPE : 테이블 또는 뷰의 행을 참조하는 레코드 선언 

DECLARE
        vrec emp%ROWTYPE;    
BEGIN
        SELECT name,sal INTO vrec.name,vrec.sal
       
        FROM emp
        WHERE  empNO = '1001';
        
        DBMS_OUTPUT.PUT_LINE('이름 :' || vrec.name);
        DBMS_OUTPUT.PUT_LINE('급여 :' || vrec.sal);
END;

 

• %TYPE : 상수,변수,필드 또는 매개 변수를 이전에 선언된 변수,필드,레코드,중첩 테이블 또는 데이터베이스 컬럼과 동일한 데이터 형식으로 선언한다. 
• %ROWTYPE 
  • 테이블 또는 뷰의 행을 참조하는 레코드를 선언한다.
  • 레코드에는 참조된 테이블 또는 뷰의 각 컬럼에 대해 데이터 유형 및 이름이 같은 필드를 갖는다.
  • 레코드에서 필드를 참조하려면 record_name.field_name을 사용한다. 

DECLARE
        --레코드 유형 선언
        TYPE MYTYPE IS RECORD
        
        (
            name emp.name%TYPE,
            pay emp.sal%TYPE
        );
        
    vrec MYTYPE;
BEGIN
        SELECT name,sal INTO vrec.name,vrec.pay
        FROM emp
        WHERE  empNO = '1001';
        
        DBMS_OUTPUT.PUT_LINE('이름 :' || vrec.name);
        DBMS_OUTPUT.PUT_LINE('급여 :' || vrec.pay);
    END;

 

 

제어구조

IF문

 

DECLARE
    a NUMBER := 10;
BEGIN
    IF MOD(a,6) = 0 THEN
       DBMS_OUTPUT.PUT_LINE(a||':2또는 3의배수');
    ELSIF MOD(a,2) = 0 THEN
       DBMS_OUTPUT.PUT_LINE(a||':2의배수');
    ELSIF MOD(a,3) = 0 THEN
        DBMS_OUTPUT.PUT_LINE(a||':3의배수');
    ELSE
         DBMS_OUTPUT.PUT_LINE(a||':2또는 3의배수가 아니다');
    END IF;
    END;
    /

 

 

CASE문

DECLARE
    vname VARCHAR2(30);
    vpay  NUMBER;
    vtax  NUMBER;
BEGIN
    SELECT name,sal+bonus INTO vname,vpay
    FROM emp
    WHERE empNO = '1001';
    
    CASE 
    
    WHEN  vpay >= 3000000 THEN
        vtax := TRUNC(vpay * 0.03);
    WHEN   vpay >= 2000000 THEN
            vtax := TRUNC(vpay * 0.02);
    ELSE
        vtax := 0;
    END CASE;
    
    DBMS_OUTPUT.PUT_LINE('이름:'||vname);
    DBMS_OUTPUT.PUT_LINE('급여:'||vpay);
    DBMS_OUTPUT.PUT_LINE('세금:'||vtax);
END;
/

 

 

LOOP문

• 무한 반복되는 LOOP문장이다.
• EXIT문을 만나면 빠져나간다.
• EXIT WHEN <condition>;을 사용하여 LOOP문을 빠져 나가는 조건을 제어 할 수 있다.
• 다른 LOOP를 포함하여 중첩 가능하다. 

EXIT문

• LOOP문에서 EXIT문을 만나면 loop가 완료되고 제어가 END LOOP 바로 다음 문장을 실행한다.
• EXIT WHEN <condition>문은 WHEN 절의 조건이 TRUE인 경우 loop가 완료되고 제어가 END LOOP 바로 다음 문장을 실행한다. 

--1~100까지 합 출력 
DECLARE
        n NUMBER :=0;
        s NUMBER :=0;
BEGIN
        LOOP
            n:= n+1;
            s:= s+n;
            EXIT WHEN n = 100;
        END LOOP;
        DBMS_OUTPUT.PUT_LINE('결과:'||s);
END;
/

 

 

CONTINUE문

• LOOP문에서 CONTINUE문을 만나면 loop의 현재 반복이 완료되고 제어가 loop의 다음 반복으로 전달 된다.
• CONTUINUE WHEN<condition>문은 WHEN 절의 조건이 TRUE인 loop의 현재 반복이 완료되고 제어가 loop의 다음 반복으로 전달 된다. 

WHILE-LOOP문

• 조건이 TRUE인 경우 반복되는 LOOP 문장이다.
• 다른 LOOP를 포함하여 중첩 가능하다. 

-- 1~100까지 수중 2또는 3의 배수를 제외하고 출력 
DECLARE
    n NUMBER := 0;
BEGIN
    WHILE n < 100 LOOP
        n := n + 1;
        EXIT WHEN n > 100; -- 추가: 루프 종료 조건
        CONTINUE WHEN MOD(n, 2) = 0 OR MOD(n, 3) = 0;
        DBMS_OUTPUT.PUT(n || ' ');
    END LOOP;
    DBMS_OUTPUT.NEW_LINE();
END;

 

 

 

FOR-LOOP문

• 단순 FOR-LOOP문은 지정된 정수 범위에서 반복된다.
• FOR루프가 처음 입력 될때 범위가 평가되고 다시 평가되지 않으며, lower_bound가 upper_bound와 같으면 루프 본문이 한 번 실행된다
• FOR문의 반복에 사용된 변수는 초기값에서 시작해 최종값까지 루프를 돌며 1씩 증가되는데, 변수는 참조는 가능하지만 변경할 수는 없고 참조도 오직 루프 안에서만 가능하다.
• FOR문의 반복에 사용된 변수는 선언부에서 선언하지 않는다.
• 일반적으로 반복은 적은 값에서 큰 값으로 진행하지만 REVERSE는 큰 값에서 적은 값으로 반복된다. 

--1부터 100까지 합
DECLARE
    s NUMBER := 0;
BEGIN
--FOR에서 반복을 나타내는 변수는 선언하지 않는다 
    FOR n IN 1..100 LOOP
        s := s + n;
    END LOOP;
        DBMS_OUTPUT.PUT_LINE('결과:' || s);
END;
/

--REVERSE역으로 출력 
--10 9 8 7 6 5 4 3 2 1 
DECLARE
BEGIN
    FOR n IN REVERSE 1..10 LOOP
    DBMS_OUTPUT.PUT(n||' ');
    END LOOP;
    DBMS_OUTPUT.NEW_LINE();
END;
/

 

 

SQL Cursor FOR LOOP

• FOR LOOP에 쿼리 텍스트를 포함시켜 각 행을 loop로 처리한다.
• 쿼리 각 행의 실행 결과는 레코드 변수에 저장되어 loop내에서 레코드 변수의 각 필드를 참조하여 연산하거나 결과를 표시 할 수 있다.
• FOR문의 record 변수는 선언부에서 선언하지 않는다.

DECLARE
BEGIN
    FOR rec IN (SELECT name,sal+bonus pay FROM emp) LOOP
    DBMS_OUTPUT.PUT_LINE(rec.name||':'||rec.pay);
    END LOOP;
   
END;
/