210601 oracle - PL/SQL

강의목표

1. PL/SQL의 문법을 이해하고 실행할 수 있다. - FOR LOOP

 

============================================================================

[형식]
FOR 변수 IN 초기값..최종값 LOOP
    명령문;
END LOOP;

[형식]
LOOP ~무한 반복 명령~ END LOOP;  -- EXIT로 탈출

ACCEPT 변수 PROMPT '출력문장' -> 사용자 입력라인 값을 읽어서 변수에 저장 %TYPE
===========================================================================
PROCEDURE 프로시져란?
  - 특정 작업을 수행할 수 있고 이름이 있는 PL/SQL 블록으로서 매개변수를 받을 수도 있고
    반복적으로 사용할 수도 있다.

  - 보통 연속실행 또는 구현이 복잡한 트랜잭션을 수행하는 PL/SQL 블록을
    DB에 저장하기 위해서 생성한다.

트랜잭션 : 작업의 단위, 일련의 작업 등을 하나의 프로세서(EXE)로 취하는 것

CREATE PROCEDURE p_name(
                        IN args    -- 실행환경에서 값을 전달
                     OUT args    -- 값을 리턴
                 IN OUT args    -- 전달, 리턴한다.
)
IS
    변수 선언
BEGIN
    구문
END p_name;

예시)
CREATE PROCEDURE update_sal(v_empno in number)
IS
BEGIN
    UPDATE EMP SET SAL = SAL + 100
    WHERE EMPNO = v_empno;
    COMMIT;
END update_sal;
/    -- 생성완료

EXECUTE UPDATE_SAL(7369);    -- 프로시져 실행
EXEC UPDATE_SAL(7369); 


만들어진 STORED PROCEDURE LIST 보기
SELECT * FROM USER_PROCEDURES;


프로시저의 내용까지 확인
SELECT * FROM USER_SOURCE;


SELECT OBJECT_NAME, STATUS
FROM USER_OBJECTS
WHERE OBJECT_TYPE = 'VIEW';    -- WHERE OBJECT_TYPE = 'PROCEDURE';


SELECT OBJECT_NAME, STATUS
FROM USER_OBJECTS
WHERE OBJECT_TYPE = 'PROCEDURE';

============================================================================
FUNCTION 함수는 매개변수로 IN만 가능하다. 그 대신 RETURN이라는 키워드가 존재
그것이 프로시져랑 차이점

var res_sal number;    --  변수 선언

EXECUTE :res_sal := fc_update_sal(7369);    -- 함수 호출 시 리턴하는 값을 대입 후 실행

PRINT RES_SAL;    -- 내용 출력

============================================================================
예제)
type MY_REC is record (v_empno number,
                               v_ename varchar2(30),
                               v_hiredate date);
empr MY_REC;

~~ INTO empr.v_empno, empr.v_ename, empr.v_hiredate
from emp;

============================================================================
Q1) 1에서 10까지 반복하여 TEST1 테이블에 저장하라 (for문 사용)
SET SERVEROUTPUT ON

CREATE TABLE TEST1(bunho NUMBER(3),
                            irum VARCHAR2(10));

BEGIN
  for i in 1..10 loop
    INSERT INTO TEST1 VALUES (i, sysdate);
  end loop;
END;

SELECT * FROM TEST1;


Q2) 1~50까지 출력하자 [ 현재의 i값은 ? 00이다 ]  -> FOR LOOP
DECLARE
    i int := 0;
BEGIN
    FOR i IN 1..50 LOOP
        DBMS_OUTPUT.PUT_LINE('현재의 i 값은?  ' || i || ' 이다');
    END LOOP;
END;
/


Q3) 구구단에서 홀수 단만 출력해보자
DECLARE
    total number := 0;
BEGIN
    FOR i IN 2..9 LOOP
        IF MOD(i, 2) <> 0 THEN
            DBMS_OUTPUT.PUT_LINE(i || ' 단');

            FOR j IN 2..9 LOOP
                total := i*j;
                DBMS_OUTPUT.PUT_LINE(i || ' * ' || j || ' = ' || total);
            END LOOP;
            DBMS_OUTPUT.PUT_LINE('=========================');
        END IF;
    END LOOP;
END;
/


Q4) 1 ~ 50 출력해보자
DECLARE
    i int := 1;
BEGIN
    LOOP
        DBMS_OUTPUT.PUT_LINE('현재 i 값은  ' || i || '  입니다');
        i := i + 1;
        IF i > 50 THEN
            EXIT;
        END IF;
    END LOOP;
END;
/


Q5) EMP 테이블에서 부서번호 10인 사원 평균 급여를 출력하는 익명의 블록을 완성해보자.
SELECT AVG(SAL) FROM EMP WHERE DEPTNO=10 GROUP BY DEPTNO;

DECLARE
    avg01 NUMBER(7) := 0;
    deptno01 NUMBER(7) := 10;
BEGIN
    SELECT AVG(SAL) INTO avg01
    FROM EMP
    WHERE DEPTNO = deptno01
    GROUP BY DEPTNO;
    DBMS_OUTPUT.PUT_LINE(deptno01 || ' 번 부서의 평균 급여는 [ ' || avg01 || ' ] 원 입니다.');
END;
/

Q6) 20번 부서에 [5]명에 대한 평균 급여는 [2175]원이다. 익명으로 풀어보자.
     EMP 테이블에서 20번 부서의 개수, 평균 급여를 구하자.
SELECT COUNT(*), AVG(SAL) FROM EMP WHERE DEPTNO=20 GROUP BY DEPTNO;

DECLARE
    DNO NUMBER := 20;
    CNT NUMBER := 0;
    AVG_SAL NUMBER := 0;
BEGIN
    SELECT COUNT(*), AVG(SAL) INTO CNT, AVG_SAL
              FROM EMP WHERE DEPTNO=DNO GROUP BY DEPTNO;
    DBMS_OUTPUT.PUT_LINE('20번 부서 ' || CNT || ' 명에 대한 평균 급여는 ' || AVG_SAL || ' 원 입니다.');
END;
/


Q7) 사번을 입력해서 이름을 리턴받자.
ACCEPT INPUT_VAL
7902

DECLARE
    MY_NAME EMP.ENAME%TYPE;
BEGIN
    SELECT ENAME  INTO MY_NAME
    FROM EMP
    WHERE EMPNO = &INPUT_VAL;
    DBMS_OUTPUT.PUT_LINE('현재의 결과는 ' || MY_NAME);
END;
/


Q8) 30번 부서의 인원수는 [6]이고 평균급여 [777]이다. [D]등급이다.
조건 : 2500 이상이면 'A'
         2000 이상이면 'B'
         1500 이상이면 'C'
         1000 이상이면 'D'
                             'F'

ACCEPT DNO

DECLARE
    CNT NUMBER := 0;
    AVG_SAL NUMBER := 0;
    GRADE CHAR(1) := '';
BEGIN
    SELECT COUNT(*), AVG(SAL) INTO CNT, AVG_SAL
    FROM EMP 
    WHERE DEPTNO=&DNO 
    GROUP BY DEPTNO;

    IF AVG_SAL >= 2500 THEN GRADE := 'A';
        ELSIF AVG_SAL >= 2000 THEN GRADE := 'A';
        ELSIF AVG_SAL >= 1500 THEN GRADE := 'C';
        ELSIF AVG_SAL >= 1000 THEN GRADE := 'D';
        ELSE GRADE := 'F';
    END IF;

    DBMS_OUTPUT.PUT_LINE(&DNO || '부서의 인원수는 ' || CNT || '명 이다.');
    DBMS_OUTPUT.PUT_LINE('평균 급여는 ' || ROUND(AVG_SAL) || ' 원 이고, ' || GRADE || ' 등급이다.');
END;
/


Q9) 부서번호를 입력받아 사원의 번호, 이름 급여, 직업을 출력하는 프로시져 만들기
CREATE OR REPLACE PROCEDURE EX_PRO05( P_EMPNO IN EMP.EMPNO%TYPE)
AS
    V_EMP EMP%ROWTYPE;    -- 한 줄의 레코드 타입이 지정된다.
BEGIN
    SELECT EMPNO, ENAME, SAL, JOB INTO V_EMP.EMPNO, V_EMP.ENAME, V_EMP.SAL, V_EMP.JOB
    FROM EMP
    WHERE EMPNO = P_EMPNO;

    DBMS_OUTPUT.PUT_LINE(V_EMP.EMPNO);
    DBMS_OUTPUT.PUT_LINE(V_EMP.ENAME);
    DBMS_OUTPUT.PUT_LINE(V_EMP.SAL);
    DBMS_OUTPUT.PUT_LINE(V_EMP.JOB);
END EX_PRO05;


Q10) 사번을 입력받아서 월급에 100을 더하는 함수를 작성해보자
CREATE OR REPLACE FUNCTION FC_UPDATE_SAL(v_empno emp.empno%type)
RETURN NUMBER 
AS
    v_sal emp.sal%type;
BEGIN
    UPDATE EMP SET SAL = SAL + 100
    WHERE EMPNO = v_empno;
    COMMIT;

    SELECT SAL INTO v_sal
    FROM EMP
    WHERE EMPNO = v_empno;

    RETURN v_sal;
END FC_UPDATE_SAL;

var res_sal number;    --  변수 선언

EXECUTE :res_sal := fc_update_sal(7369);    -- 함수 호출 시 리턴하는 값을 대입 후 실행

PRINT RES_SAL;    -- 내용 출력


Q11) 사번을 입력받아 연봉을 계산(SAL*12 + COMM)해서 출력하는 함수를 작성하고 다음 문장을 실행
SELECT EMPNO, EX_GET_SAL(EMPNO) FROM EMP;

CREATE OR REPLACE FUNCTION EX_GET_SAL (V_ID EMP.EMPNO%TYPE)
RETURN NUMBER 
AS 
    GET_SAL NUMBER := 0;
BEGIN
    SELECT SAL*12+NVL(COMM,0) INTO GET_SAL
    FROM EMP
    WHERE EMPNO = V_ID;
    RETURN GET_SAL;
END EX_GET_SAL;


Q12) 사번을 입력받아 사원번호 사원이름 입사일을 출력하는 프로시져를 만들고 다음 문장을 실행
EXEC EX_PRO06(7369);

CREATE OR REPLACE PROCEDURE EX_PRO06 (ENO NUMBER)
AS 
    E_NAME VARCHAR2(20) := ' ';
    E_DATE DATE := SYSDATE;
BEGIN
    SELECT ENAME, HIREDATE INTO E_NAME, E_DATE
    FROM EMP
    WHERE EMPNO = ENO;
    
    DBMS_OUTPUT.PUT_LINE('사원번호 : ' || ENO);
    DBMS_OUTPUT.PUT_LINE('사원이름 : ' || E_NAME);
    DBMS_OUTPUT.PUT_LINE('입사일 : ' || E_DATE);
END EX_PRO06;