DATABASE PL/SQL

PL/SQL

• PL/SQL

  • 오라클 내부에서 적절한 처리를 위해서 적용해 줄 수 있는 절차 지향적 코드 작성 방식

  • 쿼리의 집합으로 어떠한 동작을 일괄 처리하기 위한 용도로 사용

  • DECLARE (선언), BEGIN (실행), END( 종료) 순으로 구분되며 실행

    SET SERVEROUTPUT ON; -- 출력문 활성화
    
    --프로시저는 드래그해서 F5로 실행
    -- PLSQL은 DECLARE와 BEGIN, END로 구간을 나눔 
    DECLARE -- 변수 선언 (선언부)
        emp_num NUMBER; -- 변수 선언
    BEGIN -- 코드 실행 (실행부)
        emp_num := 10; -- := PLSQL의 대입연산자 
        dbms_output.put_line(emp_num);
        dbms_output.put_line('THIS IS PL/SQL.');
        -- print와 같은 기능
    END; -- PL/SQL이 끝나는 구간 (종료부)
    
    -- 연산자 
    -- 일반 SQL문의 모든 연산자의 사용이 가능
    
    DECLARE
        a NUMBER := 2 * * 2 * 3 * * 2;
    BEGIN
        dbms_output.put_line('a = ' || to_char(a));
    END;
    
    /*
    DDL은 사용이 불가능하고 DML은 사용가능하며 일반적인 SQL문의 SELECT 등을 사용하는데 
    특이한 점은 SELECT 아래에 INTO 절을 사용해 변수에 할당할 수 있음
    */
    
    DECLARE
        v_emp_name VARCHAR2(50); --문자열 변수는 길이 제약 적어야함 
        v_dep_name VARCHAR2(50);
    BEGIN
        SELECT
            e.first_name,
            d.department_name
        INTO
            v_emp_name,
            v_dep_name
    -- 변수에 대입을 의미
    -- SELECT문의 조회 결과를 INTO에 적은 변수에 대입하겠다는 의미
        FROM
            employees   e
            LEFT OUTER JOIN departments d ON e.department_id = d.department_id
        WHERE
            employee_id = 100;
    
        dbms_output.put_line(v_emp_name
                             || '-'
                             || v_dep_name);
    END;
    
    --해당 테이블과 같은 타입의 컬럼 변수를 선언하려면 테이블명, 컬럼명 %type을 사용함으로써 타인을 일일히 확인하는 번거로움을 방지할 수 있음
    DECLARE
        v_emp_name employees.first_name%TYPE; 
    		-- 대입하고자 하는 변수에 자료형을 알지 못할 때 사용 
        v_dep_name departments.department_name%TYPE(50);
    BEGIN
        SELECT
            e.first_name,
            d.department_name
        INTO
            v_emp_name,
            v_dep_name
    -- 변수에 대입을 의미
    -- SELECT문의 조회 결과를 INTO에 적은 변수에 대입하겠다는 의미
        FROM
            employees   e
            LEFT OUTER JOIN departments d ON e.department_id = d.department_id
        WHERE
            employee_id = 100;
    
        dbms_output.put_line(v_emp_name
                             || '-'
                             || v_dep_name);
    END;

    
    

Conditional Statement

• Conditional Statement

  • IF

    DECLARE
        v_num1 NUMBER := 10;
        v_num2 NUMBER := 5;
    BEGIN
        IF v_num1 >= v_num2 THEN
            dbms_output.put_line(v_num1 || '이(가) 큰 수');
        ELSE
            dbms_output.put_line(v_num2 || '이(가) 큰 수');
        END IF;
    END;
    
    -- ELSIF 
    DECLARE
        v_salary        NUMBER := 10;
        v_department_id NUMBER := 0;
    BEGIN
        v_department_id := round(dbms_random.value(10, 120), -1);
    -- 난수발생
        SELECT
            salary
        INTO v_salary
        FROM
            employees
        WHERE
                department_id = v_department_id
            AND ROWNUM = 1; -- 첫째 값만 구해서 변수에 저장 
        dbms_output.put_line(v_salary);
        IF v_salary <= 5000 THEN
            dbms_output.put_line('낮음');
        ELSIF v_salary <= 9000 THEN
            dbms_output.put_line('중간');
        ELSE
            dbms_output.put_line('높음');
        END IF;
    
    END;
    

  • CASE

    DECLARE
        v_salary        NUMBER := 10;
        v_department_id NUMBER := 0;
    BEGIN
        v_department_id := round(dbms_random.value(10, 120), -1);
    
        SELECT
            salary
        INTO v_salary
        FROM
            employees
        WHERE
                department_id = v_department_id
            AND ROWNUM = 1; -- 첫째 값만 구해서 변수에 저장 
        dbms_output.put_line(v_salary);
        CASE
        WHEN v_salary <= 5000 THEN
            dbms_output.put_line('낮음');
        WHEN v_salary <= 9000 THEN
            dbms_output.put_line('중간');
        ELSE
            dbms_output.put_line('높음');
        END CASE;
    
    END;

  • 중첩 if

    DECLARE
        v_salary        NUMBER := 0;
        v_department_id NUMBER := 0;
        v_commission    NUMBER := 0;
    BEGIN
        v_department_id := round(dbms_random.value(10, 120), -1);
        SELECT
            salary,
            commission_pct
        INTO
            v_salary,
            v_commission
        FROM
            employees
        WHERE
                department_id = v_department_id
            AND ROWNUM = 1;
    
        dbms_output.put_line(v_salary);
        IF v_commission > 0 THEN
            IF v_commission > 0.15 THEN
                dbms_output.put_line('커미션포함' || v_salary * v_commission);
            END IF;
        ELSE
            dbms_output.put_line(v_salary);
        END IF;
    
    END;

    
    

Loop Statement

• Loop Statement

  • WHILE

    DECLARE
        v_num   NUMBER := 3;
        v_count NUMBER := 1;
    BEGIN
        WHILE v_count <= 10 LOOP
            dbms_output.put_line(v_count);
            v_count := v_count + 1;
        END LOOP;
    END;
    
    -- 탈출문
    DECLARE
        v_num   NUMBER := 3;
        v_count NUMBER := 1;
    BEGIN
        WHILE v_count <= 10 LOOP
            dbms_output.put_line(v_count);
            EXIT WHEN v_count = 5;
            v_count := v_count + 1;
        END LOOP;
    END;

  • FOR

    DECLARE
        v_num NUMBER := 3;
    BEGIN
        FOR i IN 1..10 -- ..이 '~부터' 라는 뜻이며 마지막 숫자는 -1해야함
         LOOP
            dbms_output.put_line(v_num
                                 || 'X'
                                 || i
                                 || '='
                                 || v_num * i);
        END LOOP;
    END;
    
    -- CONTINUE 
    DECLARE
        v_num NUMBER := 3;
    BEGIN
        FOR i IN 1..9 -- ..이 '~부터' 라는 뜻
         LOOP
         CONTINUE WHEN i = 5;
            dbms_output.put_line(v_num
                                 || 'X'
                                 || i
                                 || '='
                                 || v_num * i);
        END LOOP;
    END;