PL 기초 활용

/*
    PL/SQL Procedual extension to structured  Query Language
 
    Procedure == 처리자
 
    SQL을 확장한 순차적인 언어 -> Procedure, Function, Trigger
 
    SQL Query문을 간단히 접근하기 위해서 작성해 놓은 함수
 
    
 
    + script
 
    
 
*/
 
 
 
/*
 
Script 구조
 
1. 선언부 : 사용할 변수, 상수를 선언, 초기화
 
2. 실행부 : 실제 처리할 Query 부분이나 제어문등을 활용 
 
3. 예외 처리부 : 각종 오류 부분(생략가능)
 
*/
 
 
 
SET SERVEROUTPUT ON
 
BEGIN
 
    -- 실행부
 
--    DBMS_OUTPUT.PUT --print
 
    dbms_output.put_line('HELLO PL'); -- println 
 
END;
 
/ --원래는 END 후에 /를 사용해주어야 한다.
 
 
 
--SET SERVEROUTPUT OFF
 
 
 
DECLARE
 
    -- 선언부
 
    message VARCHAR2(10);
 
BEGIN
 
    -- 실행부  
 
    message := 'hello PL'; -- 변수를 대입할때 := 를 사용한다
 
    
 
    DBMS_OUTPUT.PUT_LINE('message = ' || message );
 
    -- 예외처리
 
END;
 
/
 
 
 
-- if
 
DECLARE
 
    counter INTEGER;
 
BIGIN
 
    counter := 1;
 
    counter := counter + 1;
 
    
 
    IF counter IS NULL THEN 
 
        DBMS_OUTPUT.PUT_LINE('RESULT:counter is null');
 
--    ELSIF --elseif가 아닌 elsif       
 
    ELSE
 
        DBMS_OUTPUT.PUT_LINE('RESULT:counter is not null');
 
    END IF;
 
 
 
END;
 
/
 
 
 
-- FOR
 
DECLARE
 
    counter INTEGER;
 
    i INTEGER;
 
BEGIN
 
    FOR i IN 1..9 LOOP
 
        DBMS_OUTPUT.PUT_LINE('i = ' || i);
 
        counter := i * 2;
 
        DBMS_OUTPUT.PUT_LINE('2 * ' || i || ' = ' || counter);
 
        END LOOP;
 
END;
 
/
 
 
 
-- loop문
 
DECLARE
 
--변수 선언과 동시에 초기화가 가능하다
 
    v_count NUMBER := 0; 
 
    v_total NUMBER := 0;
 
BEGIN
 
    
 
    LOOP
 
        EXIT WHEN v_count = 10; -- :가 없다
 
        
 
        v_count := v_count + 1;
 
        v_total := v_total + v_count;
 
        DBMS_OUTPUT.PUT_LINE('v_count = ' || v_count);
 
    END LOOP;
 
    
 
    DBMS_OUTPUT.PUT_LINE('v_total = ' || v_total);
 
END;
 
/
 
 
 
-- while
 
DECLARE 
 
    v_count NUMBER := 0; 
 
    v_total NUMBER := 0;
 
BEGIN
 
    while v_count < 10
 
    LOOP
 
        v_count := v_count + 1;
 
        v_total := v_total + v_count;
 
    END LOOP;
 
    DBMS_OUTPUT.PUT_LINE('v_total = ' || v_total);
 
END;
 
/
 
 
 
 
 
 
 
-- GOTO
 
DECLARE
 
    v_name VARCHAR2(10) := 'LEE';
 
    v_case NUMBER := 1;
 
BEGIN
 
     CASE WHEN MOD(v_case, 2) = 0 THEN
 
            GOTO test1;
 
        WHEN MOD(v_case, 2) = 1 THEN
 
            GOTO test2;
 
        ELSE
 
            GOTO ERR;
 
     END CASE;
 
<<test1>>
 
    DBMS_OUTPUT.PUT_LINE(v_name || ' is woman ');
 
    GOTO sub_end;
 
<<test2>>
 
    DBMS_OUTPUT.PUT_LINE(v_name || ' is man ');
 
    GOTO sub_end;
 
<<err>>
 
    DBMS_OUTPUT.PUT_LINE('ERR');
 
    GOTO sub_end;
 
    
 
<<sub_end>>
 
    DBMS_OUTPUT.PUT_LINE('Exit');
 
END;
 
/
 
 
 
-- 예외처리부
 
DECLARE
 
    counter INTEGER;
 
BEGIN
 
    counter := 10;
 
    counter := counter / 0;
 
    
 
EXCEPTION WHEN OTHERS THEN
 
    DBMS_OUTPUT.PUT_LINE('예외가 발생했습니다');    
 
END;
 
/
 
 
 
-- varray : VARIABLE ARRY
 
 
 
DECLARE
 
    TYPE varray_test IS VARRAY(3) OF INTEGER; --자료형 적기  -- int varray_test[] = new int[3];
 
    varr varray_test; --변수 선언 
 
BEGIN
 
    varr := varray_test(111, 222, 333); 
 
    DBMS_OUTPUT.PUT_LINE('varr(2) = ' || varr(2)); --1번부터 시작한다.
 
END;
 
/
 
 
 
ACCEPT p_deptno PROMPT '부서번호를 입력하시오(급여의 합)'
 
DECLARE
 
    v_salTotal NUMBER;
 
BEGIN
 
    SELECT SUM(salary) into v_salTotal --SELECT일때만 
 
    FROM employees
 
    where department_id = &p_deptno; --프롬프트를 사용하기위해값을 가져오게 하는 방법 &를 붙인다.
 
    
 
    DBMS_OUTPUT.PUT_LINE('&p_deptno'|| '번 부서의 급여의 합은 ' 
 
                         || to_char(v_salTotal, '$999,999,999')); --1번부터 시작한다.
 
END;
 
/
 
 
 
-- 사원번호 입력 받고 그 사원의 급여에 1000을 더한 값으로 갱신(UPDATE)하라.
 
-- 사원번호, 갱신된 급여를 출력(SELECT)하라.
 
ACCEPT p_employee_id PROMPT '사원번호를 입력하시오'
 
DECLARE 
 
--    v_sal NUMBER;
 
    v_sal EMPLOYEES.salary%TYPE := 1000; -- 자동 타입 설정
 
BEGIN
 
    UPDATE employees
 
    SET salary = salary + 1000
 
    where employee_id = &p_employee_id;
 
    
 
    select salary into v_sal
 
    from employees
 
    where employee_id = &p_employee_id;
 
    
 
     DBMS_OUTPUT.PUT_LINE('&p_employee_id'|| '번 부서의 급여의 합은 ' 
 
                         || to_char(v_sal, '$999,999,999')); --1번부터 시작한다.
 
                         
 
END;
 
/
 
 
 
ACCEPT p_employee_id PROMPT '사원번호를 입력하시오'
 
DECLARE 
 
    v_sal EMPLOYEES.salary%TYPE := 1000; -- 자동 타입 설정
 
    v_salTotal NUMBER;
 
BEGIN
 
    select salary into v_sal
 
    from employees
 
    where employee_id = &p_employee_id;
 
 
 
    UPDATE employees
 
    SET salary = salary + v_sal
 
    where employee_id = &p_employee_id;
 
    
 
    select salary into v_sal
 
    from employees
 
    where employee_id = &p_employee_id;
 
     DBMS_OUTPUT.PUT_LINE('&p_employee_id'|| '번 부서의 급여의 합은 ' 
 
                         || to_char(v_sal, '$999,999,999')); --1번부터 시작한다.
 
                         
 
END;
 
/
 
 
 
 
 
-- RowType -> 
 
-- 한테이블의 데이터를 한번에 가져올 수 있다.
 
DECLARE
 
--    v_empno employees.employee_id%TYPE:
 
    v_emp EMPLOYEES%ROWTYPE;    -- column명이 자동적으로 변수명이 된다. v_emp.first_name
 
BEGIN
 
    SELECT employee_id, first_name, last_name, salary
 
        into v_emp.employee_id, v_emp.first_name, v_emp.last_name, v_emp.salary
 
    FROM employees
 
    WHERE employee_id = 100;
 
    
 
     DBMS_OUTPUT.PUT_LINE('사원번호' || v_emp.employee_id);
 
     DBMS_OUTPUT.PUT_LINE('사원이름' || v_emp.first_name);
 
     DBMS_OUTPUT.PUT_LINE('사원성' || v_emp.last_name);
 
     DBMS_OUTPUT.PUT_LINE('사원급여' || v_emp.salary);
 
    
 
END;
 
/
 
 
 
-- 이름, 급여, 부서번호
 
-- 사원 추가 -> employees  IT_PROG 이름@naver.com
 
 
 
-- 부서번호가 30일 경우 -> 입력받은 급여에 20%인상해서 
 
-- 부서번호가 60일 경우 -> 입력받은 급여에 10%인상해서 
 
 
 
-- 입력 처리
 
ACCEPT p_name PROMPT '이 름:'
 
ACCEPT p_sal PROMPT '급 여:'
 
ACCEPT p_deptno PROMPT '부서번호:'
 
 
 
DECLARE
 
    v_name employees.last_name%TYPE := '&p_name';   -- 문자열
 
    v_sal employees.salary%TYPE := &p_sal;
 
    v_deptno employees.department_id%TYPE := &p_deptno;
 
BEGIN
 
    DBMS_OUTPUT.PUT_LINE('v_name:' || v_name);
 
    DBMS_OUTPUT.PUT_LINE('v_sal:' || v_sal);
 
    DBMS_OUTPUT.PUT_LINE('v_deptno:' || v_deptno);
 
    
 
    IF v_deptno = 30 THEN
 
        v_sal := v_sal * 1.2;
 
    ELSIF v_deptno = 60 THEN
 
        v_sal := v_sal * 1.1;
 
    END IF;
 
 
 
    INSERT INTO employees(employee_id, last_name, salary, 
 
                            hire_date, department_id, job_id, email)
 
    VALUES(employees_seq.nextval, v_name, v_sal,
 
                            sysdate, 30, 'IT_PROG', v_name || '@naver.com');
 
END;
 
/
 
 
 
SELECT
 
    *
 
FROM employees
 
WHERE last_name = '일';