Oracle PL/SQL PL00 (Procedural extension to Structured Query Language)

PL이란?

ACCEPT p_name PROMPT '이 름'
ACCEPT p_salary PROMPT '이 름'
ACCEPT p_deptno PROMPT '이 름'

SQL을 확장한 순차적인 언어이다.

SQL문을 간단히 접근하기 위하여 작성해 놓은 함수라고 볼 수 있다.

proceduer, fuction, trigger 3가지 종류가 있다.

 

PROCEDURE - INSERT, DELETE, UPDATE

FUNCTION - SELECT

TRIGER - UTIL

 

구조
1.선언부 : 사용할 변수, 상수를 선언, 초기화
2.실행부 : 실제 처리할 Query 부분이나 제어문등을 활용. 실제 처리
3.예외처리부 : 각종 오류 부분 처리

PL 구조 : 기본

SET SERVEROUTPUT ON
-- 워크시트 내에서 한번만 실행 시켜두면 다시 실행하지 않아도 된다.

BEGIN
DBMS_OUTPUT.PUT_LINE('HELLO PL');
END;
/
-- SET SERVEROUTPUT OFF

DECLARE
-- (선언부)
message VARCHAR2(10);
BEGIN
-- (실행부)
message := 'HELLO PL';
DBMS_OUTPUT.PUT_LINE('message = ' || message);
-- (예외처리부)
END;
/

PL 구조 : IF 조건문

DECLARE
counter INTEGER;
BEGIN
counter := 1;
counter := counter+1;

IF counter IS NULL THEN
DBMS_OUTPUT.PUT_LINE('result: counter is NULL');
ELSIF counter IS NOT NULL THEN
DBMS_OUTPUT.PUT_LINE('result: counter is NOT NULL');
ELSE
DBMS_OUTPUT.PUT_LINE('result: counter = 1');
END IF;

END;
/

PL 구조 : FOR LOOP 문

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;
/


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;
/

PL 구조 : WHILE LOOP문

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;
DBMS_OUTPUT.PUT_LINE('v_count = ' || v_count);
END LOOP;

DBMS_OUTPUT.PUT_LINE('v_total = ' || v_total);

END;
/

PL 구조 : GOTO 문

DECALRE
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;
/

PL 구조 : 예외처리

DECLARE
counter INTEGER;

BEGIN
counter := 10;
counter := counter / 0;
EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('EXCEPTION exist');

END;
/

PL 구조 : VARRAY (VARIABLE ARRAY)

DECLARE
-- int[] varray_test = new int[3];

TYPE varray_test IS VARRAY(3) OF INTEGER;
varr varray_test;

BEGIN
varr := varray_test(111, 222, 333);

DBMS_OUTPUT.PUT_LINE('varr(1) ='||varr(1));
DBMS_OUTPUT.PUT_LINE('varr(2) ='||varr(2));
DBMS_OUTPUT.PUT_LINE('varr(3) ='||varr(3));

END;
/

PL 구조 : 예제

hr계정에서 이름, 급여, 부서번호를 입력받아서 사원을 추가하고,

부서번호가 30번 일 경우 입력받은 급여에 20% 인상하여 입력.

부서번호가 60번 일 경우 입력받은 급여에 10% 인상하여 입력 되게 작성하여라.

ACCEPT p_name PROMPT '이 름'
ACCEPT p_salary PROMPT '급 여:'
ACCEPT p_deptno PROMPT '부서번호:'

DECLARE
v_name employees.last_name%TYPE := '&p_name'; 
v_salary employees.salary%TYPE := &p_salary;
v_deptno employees.department_id%TYPE := &p_deptno;
BEGIN

DBMS_OUTPUT.PUT_LINE('사원번호' || v_name);
DBMS_OUTPUT.PUT_LINE('급여' || v_salary);
DBMS_OUTPUT.PUT_LINE('부서번호' || v_deptno);

IF v_deptno = 30 THEN
v_salary := v_salary*1.2;
ELSIF v_deptno = 60 THEN
v_salary := v_salary*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_salary, SYSDATE, v_deptno, 'IT_PROG', v_name||'@naver.com');

-- COMMIT;
ROLLBACK;

END;
/