/*- 13_PL_SQL.Sql -*/

/*
 * # PL/SQL ( Oracle Procedural Language extension to SQL )
 * - SQL 문장에서 변수 정의, 조건 처리(IF), 반복 처리(LOOP) 등을 지원.
 *   오라클 자체에 내장되어 있는 절차적 언어로서 SQL 문의 단점을 보완.
 * - DECLARE ~ BEGIN ~ EXCEPTION ~ END 순서를 갖음.
 *   > 선언부 ( DECLARE SECTION )
 *     : PL/SQL 에서 사용되는 변수, 상수를 선언.
 *
 *     실행부 ( EXECUTABLE SECTION )
 *     : 절차적 형식으로 SQL 문을 실행할 수 있도록 제어문, 반복문 등을 기술하는 부분으로 BEGIN으로 시작.
 *
 *     예외처리 ( EXCEPTION SECTION )
 *     : PL/SQL 문이 실행되는 중에 에러가 발생할 수 있는데, 이를 예외라고 함.
 *
 * # PL/SQL 프로그램 작성
 * - PL/SQL 블록 내에서 한 문장이 종료될 때마다 세미콜론(;)을 사용.
 * - END 뒤에 ;을 사용해서 하나의 블록이 끝났다는 것을 알려줌.
 */
 
-- 오라클에서 제공해주는 프로시저를 사용하여 출력해 주는 내용을 화면에 보여주도록 설정.
SET SERVEROUTPUT ON;

-- 메시지 출력
-- > 화면 출력을 위해 PUT_LINE 프로시저를 이용.
--   오라클에서 제공해주는 프로시저로 DBS_OUTPUT 패키지에 위치.
/
BEGIN
    DBMS_OUTPUT.PUT_LINE('hello oracle');
END;
/

/*
 * # 변수 선언
 * - 변수를 선언할 때에는 변수명 다음에 자료형 기술.
 */

-- 변수 선언
VEMPNO NUMBER(4);
VENAME VARCHAR2(10);

-- 변수 값 지정
VEMPNO := 7890;
VENAME := 'TEST';

-- 변수 선언하고 출력
/
DECLARE
    VEMPNO NUMBER(4);
    VENAME VARCHAR2(10);
BEGIN
    VEMPNO := 7890;
    VENAME := 'TEST';
    DBMS_OUTPUT.PUT_LINE('사번 / 이름');
    DBMS_OUTPUT.PUT_LINE(VEMPNO || ' / ' || VENAME);
END;
/

-- 레퍼런스
-- 이전에 선언된 다른 변수 또는 데이터베이스 컬럼에 맞추어 변수 선언
-- '%TYPE' 속성 사용
REMPNO EMP.EMPNO%TYPE; -- REMPNO는 EMP의 EMPNO 자료형에 맞춤.
RENAME EMP.ENAME%TYPE; -- RENAME은 EMP의 ENAME 자료형에 맞춤.
-- REMPNO, RENAME 변수 EMP 테이블의 해당 컬럼 자료형과 크기를 그대로 참조.

/*
 * # PL/SQL SELECT
 * - 테이블의 행에서 질의된 값을 변수에 할당시키기 위해서 SELECT문장을 사용.
 *   PL/SQL의 SELECT문은 INTO절이 필요한데, INTO 절에는 데이터를 저장하는 변수를 작성.
 * - SELECT절에 있는 컬럼은 INTO절에 있는 변수와 1:1 대응을 하기 때문에
 *   갯수, 데이터형, 길이가 일치해야함.
 *   SELECT select_list
 *   INTO variable_name
 *   FROM table_name
 *   WHERE condition;
 */
 
-- SMITH 사원의 사번, 이름 조회

/
DECLARE
    VEMPNO EMP.EMPNO%TYPE;
    VENAME EMP.ENAME%TYPE;
BEGIN
    DBMS_OUTPUT.PUT_LINE('사번 / 이름 ');
    DBMS_OUTPUT.PUT_LINE('--------------');
    
    SELECT EMPNO, ENAME INTO VEMPNO, VENAME FROM EMP WHERE ENAME='SMITH';
    
    DBMS_OUTPUT.PUT_LINE(VEMPNO || ' / ' || VENAME);
    
END;
/

/*
 * # ROWTYPE 레퍼런스 변수
 * - ROW(행) 단위로 참조하는 자료형으로 만들어진 변수.
 * - 특정 테이블의 컬럼의 갯수와 데이터 형식을 모르더라도 지정할 수 있음.
 */
 
/
DECLARE
    REMP EMP%ROWTYPE;
BEGIN
    SELECT * INTO REMP FROM EMP WHERE ENAME='SMITH';
    DBMS_OUTPUT.PUT_LINE('사원번호 : ' || REMP.EMPNO);
    DBMS_OUTPUT.PUT_LINE('사원이름 : ' || REMP.ENAME);
    DBMS_OUTPUT.PUT_LINE('사원급여 : ' || REMP.SAL);
END;
/

/*
 * # IF - THEN - END IF
 * - IF condition THEN -> 조건문
 *        statements;  -> 조건문이 참이면 실행
 *   END IF;   
 */
 
SELECT * FROM DEPT;
--10	ACCOUNTING	NEW YORK
--20	RESEARCH	DALLAS
--30	SALES	    CHICAGO
--40	OPERATIONS	BOSTON
--50	DATABASE	KOR 
SELECT * FROM EMP;
--7369	7499	7521	7566	7654	7698	7782	7839	7844	7900	7902	7934	
 
-- 부서번호를 사용해서 부서명 출력

/
DECLARE
    VEMPNO EMP.EMPNO%TYPE;
    VENAME EMP.ENAME%TYPE;
    VDEPTNO EMP.DEPTNO%TYPE;
    VDNAME DEPT.DNAME%TYPE;
BEGIN
    SELECT EMPNO,ENAME,DEPTNO INTO VEMPNO,VENAME,VDEPTNO FROM EMP WHERE EMPNO=7698;
    IF(VDEPTNO = 10) THEN
        VDNAME := 'ACCOUNTING';
    END IF;
    IF(VDEPTNO = 20) THEN
        VDNAME := 'RESEARCH';
    END IF;
    IF(VDEPTNO = 30) THEN
        VDNAME := 'SALES';
    END IF;
    IF(VDEPTNO = 40) THEN
        VDNAME := 'OPERATIONS';
    END IF;
    
    DBMS_OUTPUT.PUT_LINE('사원번호 / 사원이름 / 부서명');
    DBMS_OUTPUT.PUT_LINE(VEMPNO|| '  / ' || VENAME || '  / ' || VDNAME );
END;
/
 

 
/*
 * # IF THEN ELSIF ~ ELSE ~ END IF;
 * 
 */
 
 /
DECLARE
    VEMP EMP%ROWTYPE;
    VDNAME DEPT.DNAME%TYPE;
BEGIN
    SELECT * INTO VEMP FROM EMP WHERE EMPNO=7698;
    IF(VEMP.DEPTNO = 10) THEN
        VDNAME := 'ACCOUNTING';
    ELSIF(VEMP.DEPTNO = 20) THEN
        VDNAME := 'RESEARCH';
    ELSIF(VEMP.DEPTNO = 30) THEN
        VDNAME := 'SALES';
    ELSIF(VEMP.DEPTNO = 40) THEN
        VDNAME := 'OPERATIONS';
    END IF;
    
    DBMS_OUTPUT.PUT_LINE('사원번호 / 사원이름 / 부서명');
    DBMS_OUTPUT.PUT_LINE(VEMP.EMPNO|| '  / ' || VEMP.ENAME || '  / ' || VDNAME );
END;
/

/*
 * # BASIC LOOP
 * - 조건없이 반복 작업 실행
 *   LOOP
 *      statement;
 *      .....
 *      EXIT;
 *   END LOOP;
 */
SET SERVEROUTPUT ON;
-- 1~5까지 출력
/
DECLARE
    NO NUMBER := 1;
BEGIN
    LOOP
        DBMS_OUTPUT.PUT_LINE(NO);
        NO := NO+1;
        IF NO > 5 THEN
            EXIT;
        END IF;
    END LOOP;
END;
/

/*
 * # FOR LOOP
 * - FOR index IN 시작값...종료값 LOOP
 *      statement;
 *      .....
 *   END LOOP;
 *   
 *   > index 는 자동 선언되는 BINARY_INTEGER 형 변수. 1씩 증가.
 */
 
 
 
/
DECLARE
BEGIN
    DBMS_OUTPUT.PUT_LINE('- FOR LOOP -');
    FOR N IN 1..5 LOOP
        DBMS_OUTPUT.PUT_LINE(N);
    END LOOP;
END;
/

/*
 * # WHILE LOOP
 * - WHILE condition LOOP
 *      statement;
 *      .....
 *   END LOOP;
 */ 

/
DECLARE
    NO NUMBER := 1;
BEGIN
    DBMS_OUTPUT.PUT_LINE('- WHILE LOOP -');
    WHILE NO <= 5 LOOP
        DBMS_OUTPUT.PUT_LINE(NO);
        NO:=NO+1;
    END LOOP;
END;
/

/* QUIZ */

-- EMP 테이블에 사원이름을 적용해서 해당 사원의 연봉을 구하세요.
-- 커미션이 없으면 0으로 설정해서 연봉을 구합니다.
SELECT * FROM EMP;
/
DECLARE
    VEMP EMP%ROWTYPE;
    SAL NUMBER := 0;
BEGIN
    SELECT * INTO VEMP FROM EMP WHERE ENAME='SMITH';
    IF(VEMP.COMM IS NULL) THEN
        SAL := VEMP.SAL*12;
    ELSIF (VEMP.COMM IS NOT NULL) THEN
        SAL := VEMP.SAL*12+VEMP.COMM;
    END IF;
    DBMS_OUTPUT.PUT_LINE(VEMP.ENAME || ' 의 연봉 : ' || SAL);
END;
/

-- 구구단 7단의 값을 모두 출력하세요
/
DECLARE
BEGIN
    FOR N IN 1..9 LOOP
        DBMS_OUTPUT.PUT_LINE('7 x ' || N || ' = ' || 7*N);
    END LOOP;
END;
/

-- FOR LOOP 를 사용해서 부서번호를 생성합니다
-- 이 값을 SELECT 문에 적용해서 부서정보를 모두 출력하세요
-- > 부서번호, 부서명, 지역

SELECT * FROM DEPT;
/
DECLARE
    VDEPT DEPT%ROWTYPE;
BEGIN
    FOR N IN 1..5 LOOP
        SELECT * INTO VDEPT FROM DEPT WHERE DEPTNO=N*10;
        DBMS_OUTPUT.PUT_LINE('부서번호  : ' || VDEPT.DEPTNO);
        DBMS_OUTPUT.PUT_LINE('부서명   : ' || VDEPT.DNAME);
        DBMS_OUTPUT.PUT_LINE('지역     : ' || VDEPT.LOC);
        DBMS_OUTPUT.PUT_LINE(' ');
    END LOOP;
END;
/