Oracle DB/orcle sql
[ Oracle sql ] 11. PL/SQL
HAyooni
2022. 8. 6. 18:38
1. PL/SQL (Procedural Language Extension to SQL)
오라클 자체에 내장되어있는 절차적 언어, 변수의 정의, 조건처리, 반복처리 등을 지원하여 SQL 단점 보완
2. PL/SQL의 구조
DECLARE
EMP_ID NUMBER;
EMP_NAME VARCHAR2(30);
PI CONSTANT NUMBER := 3.14; --오라클에서 상수는 CONSTANT 로 지정
BEGIN
EMP_ID := 888;
EMP_NAME := '도대담';
DBMS_OUTPUT.PUT_LINE('EMP_ID : ' || EMP_ID);
DBMS_OUTPUT.PUT_LINE('EMP_NAME : ' || EMP_NAME);
DBMS_OUTPUT.PUT_LINE('PI : ' || PI);
END;
/
DECLARE
EMP_ID EMPLOYEE.EMP_ID%TYPE;
EMP_NAME EMPLOYEE.EMP_NAME%TYPE;
BEGIN
SELECT EMP_ID, EMP_NAME
INTO EMP_ID, EMP_NAME
--조회해온 결과를 위 선언한 변수에다가 집어넣기
FROM EMPLOYEE
WHERE EMP_ID = '&EMP_ID';
-- '' 리터럴안에 & 을 사용하여 사용자에게 값을 입력받을 수 있다
DBMS_OUTPUT.PUT_LINE('EMP_ID : ' || EMP_ID);
DBMS_OUTPUT.PUT_LINE('EMP_NAME : ' || EMP_NAME);
END;
/
----------전체정보 저장하기 ROWTYPE----------------
DECLARE
E EMPLOYEE%ROWTYPE;
BEGIN
SELECT * INTO E
FROM EMPLOYEE
WHERE EMP_ID = '&사번';
DBMS_OUTPUT.PUT_LINE('EMP_ID : ' || E.EMP_ID);
DBMS_OUTPUT.PUT_LINE('EMP_NAME : ' || E.EMP_NAME);
DBMS_OUTPUT.PUT_LINE('SALARY : ' || E.SALARY);
END;
/* CONSTANT : 상수 지정
*리터럴 안에 & 을 사용하여 사용자에게 값을 입력받기
*ROWTYPE을 통하여 전체 정보를 변수로 선언하여 저장할 수 있다.
예 ) PL/SQL의 구조
3. IF~THEN~END IF (단일 IF문)의 예시
예 ) EMP_ID를 입력받아 해당 사원의 사번, 이름, 급여, 보너스 출력 이 때, 보너스를 받지 않는 사원은 보너스율 출력 전 '보너스를 지급받지 않는 사원입니다.' 출력
DECLARE
EMP_ID EMPLOYEE.EMP_ID%TYPE;
EMP_NAME EMPLOYEE.EMP_NAME%TYPE;
SALARY EMPLOYEE.SALARY%TYPE;
BONUS EMPLOYEE.BONUS%TYPE;
BEGIN
SELECT EMP_ID, EMP_NAME, SALARY, NVL(BONUS,0)
INTO EMP_ID, EMP_NAME, SALARY, BONUS
FROM EMPLOYEE
WHERE EMP_ID = '&EMP_ID';
DBMS_OUTPUT.PUT_LINE('EMP_ID : ' || EMP_ID);
DBMS_OUTPUT.PUT_LINE('EMP_NAME : ' || EMP_NAME);
DBMS_OUTPUT.PUT_LINE('SALARY : ' ||SALARY);
IF(BONUS = 0)
THEN DBMS_OUTPUT.PUT_LINE('보너스를 지급받지 않는 사원입니다.');
END IF;
DBMS_OUTPUT.PUT_LINE('BONUS : ' || BONUS*100 || '%');
END;
/
4. IF~THEN~ELSE~END IF (IF~ELSE문)의 예시
예) EMP_ID를 입력받아 해당 사원의 사번, 이름, 부서명, 소속 출력하고 TEAM 변수를 만들어 소속이 KO인 사원은 국내팀, 아닌 사원은 해외팀으로 저장
DECLARE
EMP_ID EMPLOYEE.EMP_ID%TYPE;
EMP_NAME EMPLOYEE.EMP_NAME%TYPE;
DEPT_TITLE DEPARTMENT.DEPT_TITLE%TYPE;
NATIONAL_CODE LOCATION.NATIONAL_CODE%TYPE;
TEAM VARCHAR2(10);
BEGIN
SELECT EMP_ID, EMP_NAME, DEPT_TITLE, NATIONAL_CODE
INTO EMP_ID, EMP_NAME, DEPT_TITLE, NATIONAL_CODE
FROM EMPLOYEE
LEFT JOIN DEPARTMENT ON (DEPT_ID = DEPT_CODE)
LEFT JOIN LOCATION ON (LOCAL_CODE = LOCATION_ID)
WHERE EMP_ID = '&EMP_ID';
IF(NATIONAL_CODE = 'KO')
THEN TEAM := '국내팀';
ELSE
TEAM := '해외팀';
END IF;
DBMS_OUTPUT.PUT_LINE('사원 : ' || EMP_ID);
DBMS_OUTPUT.PUT_LINE('이름 : ' || EMP_NAME);
DBMS_OUTPUT.PUT_LINE('부서 : ' || DEPT_TITLE);
DBMS_OUTPUT.PUT_LINE('소속 : ' || TEAM);
END;
/예 ) 사용자에게 사번을 받아와 그 사원의 전체 정보를 VEMP에 저장하고 VEMP를 이용하여 연봉계산(보너스가 있는 사원은 보너스도 포함하여 계산) , 연봉 계산 결과 값은 YSLALRY에 저장, 급여 이름 연봉 (\1,000,000 형식)으로 출력
DECLARE
VEMP EMPLOYEE%ROWTYPE;
YSALARY NUMBER;
BEGIN
SELECT * INTO VEMP
FROM EMPLOYEE
WHERE EMP_ID = '&EMP_ID';
IF (VEMP.BONUS IS NOT NULL)
THEN YSALARY := VEMP.SALARY * (1 + VEMP.BONUS) * 12;
ELSE YSALARY := VEMP.SALARY * 12;
END IF;
--YSALARY := TO_CHAR((VEMP.SALARY+(VEMP.SALARY*NVL(VEMP.BONUS, 0)))*12, 'FML999,999,999');
DBMS_OUTPUT.PUT_LINE('급여 : ' || VEMP.SALARY);
DBMS_OUTPUT.PUT_LINE('이름 : ' || VEMP.EMP_NAME);
DBMS_OUTPUT.PUT_LINE('연봉 : ' || TO_CHAR(YSALARY, 'FML999,999,999'));
END;
/
5. IF~THEN~ELSIF`THEN~ELSE~END IF (IF~ELSE IF~ELSE문)의 예시
예) 점수를 입력받아 SOCRE변수에 저장
-- 90점 이상이면 A, 80점 이상이면 B, 70학점 이상은 C 학점, 60점 이상은 D학점, 그 미만은 F 처리하여 GRADE변수에 저장
-- 출력 : 당신의 점수는 N점이고, 학점은 M학점입니다.
DECLARE
SCORE NUMBER;
GRADE VARCHAR2(1);
BEGIN
SCORE := '&점수';
IF SCORE >= 90
THEN GRADE := 'A';
ELSIF SCORE >= 80
THEN GRADE := 'B';
ELSIF SCORE >= 70
THEN GRADE := 'C';
ELSIF SCORE >= 60
THEN GRADE := 'D';
ELSE GRADE := 'F';
END IF;
DBMS_OUTPUT.PUT_LINE('당신의 점수는 ' || SCORE ||'점이고, 학점은 ' || GRADE || '학점입니다.');
END;
/
6. CASE~WHEN~THEN~DEN (SWITCH문)의 예시
예 ) 사원번호를 입력받아 해당 사원의 사번, 이름, 부서명 출력
DECLARE
EMP EMPLOYEE%ROWTYPE;
DNAME DEPARTMENT.DEPT_TITLE%TYPE;
BEGIN
SELECT * INTO EMP
FROM EMPLOYEE
WHERE EMP_ID = '&EMP_ID';
DNAME := CASE
WHEN EMP.DEPT_CODE = 'D1' THEN '인사관리부'
WHEN EMP.DEPT_CODE = 'D2' THEN '회계관리부'
WHEN EMP.DEPT_CODE = 'D3' THEN '마케팅부'
WHEN EMP.DEPT_CODE = 'D4' THEN '국내영업부'
WHEN EMP.DEPT_CODE = 'D5' THEN '해외영업1부'
WHEN EMP.DEPT_CODE = 'D6' THEN '해외영업2부'
WHEN EMP.DEPT_CODE = 'D7' THEN '해외영업3부'
WHEN EMP.DEPT_CODE = 'D8' THEN '기술지원부'
WHEN EMP.DEPT_cODE = 'D9' THEN '총무부'
ELSE '배정X'
END;
-- 다르게 쓰는 방법
DNAME := CASE EMP.DEPT_CODE
WHEN 'D1' THEN '인사관리부'
WHEN 'D2' THEN '회계관리부'
WHEN 'D3' THEN '마케팅부'
WHEN 'D4' THEN '국내영업부'
WHEN 'D5' THEN '해외영업1부'
WHEN 'D6' THEN '해외영업2부'
WHEN 'D7' THEN '해외영업3부'
WHEN 'D8' THEN '기술지원부'
WHEN 'D9' THEN '총무부'
ELSE '배정X'
END;
DBMS_OUTPUT.PUT_LINE(EMP.EMP_ID || ' ' || EMP.EMP_NAME || ' ' || DNAME);
END;
/
7. 반복문 이용하기
예 ) 1부터 5까지 출력
--------------------BASIC LOOP----------------------------
DECLARE
N NUMBER := 1;
BEGIN
LOOP
DBMS_OUTPUT.PUT_LINE(N);
N := N +1;
-- IF N > 5 THEN EXIT;
-- END IF;
EXIT WHEN N >5;
END LOOP;
END;
/
--------------------FOR LOOP----------------------------
BEGIN
FOR N IN 1..5 -- FOR N IN (더작은 숫자 .. 큰 숫자)
LOOP
DBMS_OUTPUT.PUT_LINE(N);
END LOOP;
END;
/
--------------------WHILE LOOP----------------------------
DECLARE
N NUMBER :=1;
BEGIN
WHILE N <= 5
LOOP
DBMS_OUTPUT.PUT_LINE(N);
N := N +1;
END LOOP;
END;
/
DECLARE
N NUMBER := 5;
BEGIN
WHILE N >=1
LOOP
DBMS_OUTPUT.PUT_LINE(N);
N := N - 1;
END LOOP;
END;
/* REVERSE 를 이용하여 반대로 출력가능
[예제] 반복문을 이용하여 구구단 만들기
---------------------FOR문 구구단 출력 (단, 짝수단 출력)
DECLARE
RESULT NUMBER;
DAN NUMBER;
SU NUMBER;
BEGIN
FOR DAN IN 2..9 LOOP
IF (MOD(DAN, 2) = 0)
THEN
FOR SU IN 1..9 LOOP
RESULT := DAN * SU;
DBMS_OUTPUT.PUT_LINE(DAN || '*'|| SU||'='|| RESULT);
END LOOP;
DBMS_OUTPUT.PUT_LINE('');
END IF;
END LOOP;
END;
/
---------------------WHILE문 (단, 짝수단 출력)
DECLARE
RESULT NUMBER;
DAN NUMBER :=2;
SU NUMBER;
BEGIN
WHILE DAN <=9 LOOP
SU := 1;
IF MOD(DAN, 2) =0
THEN
WHILE SU<=9 LOOP
RESULT := DAN * SU;
DBMS_OUTPUT.PUT_LINE(DAN || ' * ' || SU || ' = ' || RESULT);
SU := SU + 1;
END LOOP;
DBMS_OUTPUT.PUT_LINE('');
END IF;
DAN := DAN+1;
END LOOP;
END;
/
---------------------FOR문 - WHILE문 (단, 짝수단 출력)
DECLARE
SU NUMBER;
BEGIN
FOR DAN IN 2..9 LOOP
IF MOD(DAN, 2) = 0 THEN
SU :=1;
WHILE SU<=9 LOOP
DBMS_OUTPUT.PUT_LINE(DAN || '*' || SU || '=' || DAN*SU);
SU:=SU+1;
END LOOP;
END IF;
END LOOP;
END;
/
---------------------WHILE문 - FOR문 (단, 짝수단 출력)
DECLARE
DAN NUMBER :=2;
BEGIN
WHILE DAN <=9 LOOP
IF MOD(DAN, 2) =0 THEN
FOR SU IN 1..9 LOOP
DBMS_OUTPUT.PUT_LINE(DAN || '*' || SU || '=' || DAN*SU);
END LOOP;
END IF;
DAN := DAN + 1;
END LOOP;
END;
/
8. 예외처리 NO_DATE_FOUND : SELECT문이 데이터 행을 반환
--------------------- 예외----------------------
-- NO_DATE_FOUND : SELECT문이 데이터 행을 반환
DECLARE
NAME VARCHAR(30);
BEGIN
SELECT EMP_NAME INTO NAME
FROM EMPLOYEE
WHERE EMP_ID = 0;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('조회결과가 없습니다.');
END;
/