[Oracle] 34.PL/SQL

31 Jul 2018 in DB on Oracle

PL/SQL 구조

PL/SQL의 구조는 다음과 같습니다.

DECLARE

--PL/SQL에서 사용하는 모든 변수나 상수를 선언하는 부분

BEGIN

--절차적 형식으로 SQL문을 실행할 수 있도록 절차적 언어의 요소인 제어문, 반복문, 함수 정의 등 로직을 기술할 수 있는 부분

EXCEPTION

--예외를 처리하는 부분

END;
/

PL/SQL을 작성하는 요령은 다음과 같습니다.

PL / SQL 블록내에서는 한 문장이 종료할 때마다 세미콜론(;)을 사용

END뒤에 ;을 사용하여 하나의 블록이 끝났다는 것을 명시

PL / SQL 블록의 작성은 편집기를 통해 파일로 작성할 수도 있고, 프롬프트에서 바로 작성

SQL*PLUS환경에서는 DELCLARE나 BEGIN이라는 키워드로 PL/SQL블럭이 시작

단일행 주석은 --이고 여러행 주석 /* */

쿼리문을 수행하기 위해서 /가 반드시 입력되어야 PL/SQL 블록은 행에 / 가 있으면 종결된 것으로 간주

한 번 PL/SQL을 사용해서 간단한 메시지를 출력해볼까요?

-- 오라클의 환경 변수 SERVEROUTPUT는 오라클에서 제공해주는 프로시저를 사용해서 출력

SET SERVEROUTPUT ON 

--PL/SQL

BEGIN
	dbms_output.put_line('Hello world!'); -- 화면 출력 프로시저
END;
/ -- 슬러쉬 안하면 연속적으로 실행됨(끝을 알수 없음)

변수 선언, 할당, 출력

스칼라변수: SQL에서 사용하던 자료형과 유사하게 선언하는 것

DECLARE 
    VEMPNO NUMBER(4);
    VENAME VARCHAR2(10);
BEGIN
    VEMPNO:=7788;
    VENAME:='SCOTT';
    DBMS_OUTPUT.PUT_LINE('사번 / 이름');
    DBMS_OUTPUT.PUT_LINE('==============');
    DBMS_OUTPUT.PUT_LINE(VEMPNO|| '/' ||VENAME);
END;
/

레퍼런스 변수: 테이블 속성의 자료형과 크기를 그대로 참조해서 정의: 테이블의 정보를 수정하면 자동으로 수정됨

--PL/SQL 에서 SELECT 문

DECLARE
    ENAME EMP.LAST_NAME%TYPE;
    SAL EMP.SALARY%TYPE;
BEGIN
    SELECT LAST_NAME,SALARY
    INTO ENAME,SAL --대입(데이터형이 알맞아야하고, select 결과가 1행이어야함)
    FROM EMPLOYEES
    WHERE EMPLOYEE_ID=150;
    DBMS_OUTPUT.PUT_LINE(ENAME||'의 급여는'||SAL||'입니다.');
END;
/

ROWTYPE 변수 : 테이블 전체의 속성타입을 자료형으로 함

DECLARE
    EMP_ROW EMPLOYEES%ROWTYPE; --EMP의 전체 속성타입을 변수로 잡음
BEGIN
    SELECT *
    INTO EMP_ROW
    FROM EMPLOYEES
    WHERE EMPLOYEE_ID=150;
    DBMS_OUTPUT.PUT_LINE(EMP_ROW.EMPLOYEE_ID||'의 이름은 '||EMP_ROW.FIRST_NAME||' '||EMP_ROW.LAST_NAME);
END;
/

IF 문

부서명 출력예제(IF문 사용)

DECLARE
    VEMPNO NUMBER(4);
    VENAME VARCHAR2(20);
    VDEPTNO EMP.DEPARTMENT_ID%TYPE;
    VDNAME VARCHAR2(20) :=NULL;
BEGIN
    SELECT EMPLOYEE_ID,FIRST_NAME||' '||LAST_NAME,DEPARTMENT_ID
    INTO VEMPNO, VENAME, VDEPTNO
    FROM EMPLOYEES
    WHERE EMPLOYEE_ID=200;
    IF(VDEPTNO=10) THEN
        VDNAME :='ACCOUNTING';
    ELSIF(VDEPTNO=20)THEN
        VDNAME := 'RESEARCH';
    ELSE
        VDNAME := '부서없음';
    END IF;
    DBMS_OUTPUT.PUT_LINE('사번    이름    부서명');
    DBMS_OUTPUT.PUT_LINE(VEMPNO||'    '||VENAME||'    '||VDNAME);
 END;
 /

연봉 구하기 (IF문 사용)

DECLARE 
    VEMP EMPLOYEES%ROWTYPE;
    ANNSAL NUMBER(10,2);
BEGIN
    DBMS_OUTPUT.PUT_LINE('사번 / 이름 / 월급 / 연봉');
    DBMS_OUTPUT.PUT_LINE('-------------------------');
    SELECT *
    INTO VEMP
    FROM EMPLOYEES
    WHERE FIRST_NAME='Steven' and LAST_NAME='King';
    IF(VEMP.COMMISSION_PCT IS NULL) THEN --커미션이 null이면 연봉=월급*12
        ANNSAL := VEMP.SALARY*12;
    ELSE  --커미션이 null이 아니면 연봉=월급*12+커미션
        ANNSAL := VEMP.SALARY*12+VEMP.COMMISSION_PCT;
    END IF;
    DBMS_OUTPUT.PUT_LINE(VEMP.EMPLOYEE_ID||'/'||VEMP.LAST_NAME||'/'||VEMP.SALARY||'/'||ANNSAL);
END;
/

LOOP 문

1부터 5까지 출력

DECLARE
     n   NUMBER := 1;
BEGIN
    LOOP
        dbms_output.put_line(n);
        n := n + 1;
        IF
            n > 5
        THEN
            EXIT;
        END IF;
    END LOOP;
 END;
 /

구구단 출력

DECLARE
    DAN NUMBER :=2;
    I NUMBER :=1;
BEGIN
    LOOP
        LOOP
            dbms_output.put_line(DAN||'*'||I||'='||(DAN*I));
            I:=I+1;
            IF I>=10 THEN
                EXIT;
            END IF;
        END LOOP;
        I:=1;
        DAN:=DAN+1;
        IF DAN>=10 THEN
            EXIT;
        END IF;
    END LOOP;
END;
/

FOR LOOP

1~5 출력(for문 이용)

--1~5 까지 출력

DECLARE
--for문의 인덱스로 사용되는 n을 따로 선언할 필요없다.
BEGIN
    FOR n IN 1..5 LOOP
        dbms_output.put_line(n);
    END LOOP;
END;
/

부서정보 출력(FOR문에 SELECT문 적용)

DECLARE 
BEGIN
    dbms_output.put_line('부서번호/부서명/지역명');
    dbms_output.put_line('====================');
    
    --departments 를 조회해서 한행 씩 cnt에 대입
    FOR cnt IN (SELECT * FROM departments) LOOP  
        dbms_output.put_line(cnt.department_id|| '/'|| cnt.department_name|| '/'|| cnt.location_id);
    END LOOP;

END;
/

WHILE LOOP

1부터 5출력

DECLARE
    n   NUMBER := 1;
BEGIN
    WHILE n <= 5 LOOP
        dbms_output.put_line(n);
        n := n + 1;
    END LOOP;
END;
/

별찍기

DECLARE
    v_cnt   NUMBER := 1;
    v_str   VARCHAR2(10) := NULL;
BEGIN
    WHILE v_cnt < 10 LOOP
        v_str := v_str || '*';
        dbms_output.put_line(v_str);
        v_cnt := v_cnt + 1;
    END LOOP;
END;
/

배열처럼 사용하는 테이블 변수

테이블 변수를 사용하여 EMP 테이블에서 이름과 업무를 출력

DECLARE
--테이블 타입을 정의

	--employees.last_name 타입을 배열처럼 담을 수 있는 테이블 생성
    TYPE ename_table_type IS
        TABLE OF employees.last_name%TYPE INDEX BY BINARY_INTEGER; 
        
    --employees.job_id 타입을 배열처럼 담을 수 있는 테이블 생성
    TYPE job_table_type IS
        TABLE OF employees.job_id%TYPE INDEX BY BINARY_INTEGER; 

-- 변수 선언
    ename_table   ename_table_type; -- 테이블 변수
    job_table     job_table_type; --테이블 변수
    i             BINARY_INTEGER := 0; --테이블 인덱스(1부터 시작)
BEGIN
    FOR emploop IN (SELECT last_name,job_id FROM employees) LOOP
        i := i + 1;
        ename_table(i) := emploop.last_name;
        job_table(i) := emploop.job_id;
    END LOOP;
    dbms_output.put_line('사원명'|| '/'|| '직책' );
    dbms_output.put_line('======================' );
    FOR j IN 1..i LOOP
        dbms_output.put_line(ename_table(j)|| '  /  '|| job_table(j));
    END LOOP;

END;
/

구조체처럼 사용하는 레코드 변수

DECLARE
    -- 레코드 타입 정의
    TYPE emp_record_type IS RECORD ( 
    v_empno      employees.employee_id%TYPE,
    v_ename      employees.first_name%TYPE,
    v_job        employees.job_id%TYPE,
    v_deptno     employees.department_id%TYPE );
    
    --레코드 변수 선언
    emp_record   emp_record_type;
BEGIN
    -- SCOTT 사원의 정보를 레코드 변수에 저장
    SELECT
        employee_id,
        first_name,
        job_id,
        department_id
    INTO emp_record
    FROM
        employees
    WHERE
        employee_id = 100;
    
    -- 레코드 변수에 저장된 사원 정보를 출력

    dbms_output.put_line('사원번호:' || TO_CHAR(emp_record.v_empno) );
    dbms_output.put_line('이   름:' || emp_record.v_ename);
    dbms_output.put_line('담당업무:' || emp_record.v_job);
    dbms_output.put_line('부서번호:' || TO_CHAR(emp_record.v_deptno) );
END;
/