21.09.04

Q1) 만들어진 프로시저 리스트를 확인하자
	
	SELECT * FROM USER_PROCEDURES;

Q2) 프로시저 내용까지 확인하고 싶다
	
	SELECT * FROM USER_SOURCE;

Q3) 부서번호 20번인 사원의 사원번호, 이름, 봉급을 구하는 프로시저 만들자 _EX01

	SELECT EMPNO, ENAME, SAL 
	FROM EMP 
	WHERE DEPTNO = 20;
       -----------------------------------------
커서 : 하나 이상의 SELECT를 담는 객체 (하나 x , 여러개...?)
   - 커서의 선언 방법 : 변수처럼 사용된다.
                        ROW객체를 하나 이상 관리하는 객체(IS로 값을 담아서 출발)
                        FETCH : 해당 커서가 가지고 있는 데이터를 ROW단위로 읽음 (LOOP와 함께 사용) / 
                                프로그램의 NEXT()라는 메소드(WHILE , FOR과 함께 사용)

	CREATE OR REPLACE PROCEDURE EX01
	AS
	V_EMPNO EMP.EMPNO%TYPE;
	V_ENAME EMP.ENAME%TYPE;
	V_SAL NUMBER(7,2);

	CURSOR EMP_CURSOR IS -- 1. 커서 선언 : 하나 이상의 ROW를 담는 객체 CURSOR user_name is ~~~

	SELECT EMPNO, ENAME, SAL 
	FROM EMP 
	WHERE DEPTNO = 20;

	BEGIN
	OPEN EMP_CURSOR ; -- 2. 커서를 시작

	LOOP
		FETCH EMP_CURSOR INTO V_EMPNO , V_ENAME, V_SAL; -- 3. 변수를 대입
		EXIT WHEN EMP_CURSOR%ROWCOUNT > 5 OR EMP_CURSOR%NOTFOUND; // 언제 탈출할까?

		DBMS_OUTPUT.PUT_LINE (V_EMPNO || ' ' || V_ENAME || ' ' || V_SAL); // 탈출 후 출력
	END LOOP;
	CLOSE EMP_CURSOR; -- 4. 커서 종료
	END EX01;

Q4) EX01의 프로시저를 실행해보자
	
	SET SERVEROUTPUT ON
	EXECUTE EX01;

Q5) 사원테이블에서 사원의 이름과 봉급을 출력하자 _ EX02 프로시저로 만들어보자

	SELECT ENAME,SAL
	FROM EMP;
      ---------------------
	CREATE OR REPLACE PROCEDURE EX02
	AS
	V_ENAME EMP.ENAME%TYPE;
	V_SAL EMP.SAL%TYPE;

	CURSOR EMP_CURSOR IS

	SELECT ENAME,SAL
	FROM EMP;

	BEGIN
	OPEN EMP_CURSOR ;

	LOOP
		FETCH EMP_CURSOR INTO V_ENAME,V_SAL;
		EXIT WHEN EMP_CURSOR%ROWCOUNT > 14 OR EMP_CURSOR%NOTFOUND;

		DBMS_OUTPUT.PUT_LINE ( V_ENAME || ' ' || V_SAL|| ' ' ||EMP_CURSOR%ROWCOUNT);
	END LOOP;
	CLOSE EMP_CURSOR;
	END EX02;

  	---- 프로시저 실행
	SET SERVEROUTPUT ON
	EXECUTE EX02;

Q6) 프로시저를 실행 할 때 커서에 부서번호를 전달해서 해당 부서번호의 정보를 출력하는 프로시저를 만들어 보자. _EX03

	SELECT EMPNO, ENAME, SAL
	FROM EMP
	WHERE DEPTNO = 10; -- V_DEPTNO 커서의 매개변수로 내부실행 하고 싶다.

	SELECT EMPNO, ENAME, SAL
	FROM EMP
	WHERE DEPTNO = 10; -- V_DEPTNO 커서의 매개변수로 내부실행 하고 싶다.

	---------------------

CREATE OR REPLACE PROCEDURE EX03 AS 

    V_EMPNO EMP.EMPNO%TYPE;
    V_ENAME EMP.ENAME%TYPE;
    V_SAL NUMBER(7,2);
    
    CURSOR EMP_CURSOR (V_DEPTNO NUMBER) IS -- 커서의 매개변수를 선언할 수 있다.
    SELECT EMPNO,ENAME,SAL
    FROM EMP
    WHERE DEPTNO = V_DEPTNO;
    
BEGIN
    OPEN EMP_CURSOR(10);
    LOOP
        FETCH EMP_CURSOR INTO V_EMPNO, V_ENAME, V_SAL;
        EXIT WHEN EMP_CURSOR%ROWCOUNT > 5 OR EMP_CURSOR%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE ( V_EMPNO || ' ' || V_ENAME|| ' ' || V_SAL);
    END LOOP;
    CLOSE EMP_CURSOR;
    
    OPEN EMP_CURSOR(20);
    LOOP
        FETCH EMP_CURSOR INTO V_EMPNO, V_ENAME, V_SAL;
        EXIT WHEN EMP_CURSOR%ROWCOUNT > 5 OR EMP_CURSOR%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE ( V_EMPNO || ' ' || V_ENAME|| ' ' || V_SAL);
    END LOOP;
    CLOSE EMP_CURSOR;
    
END EX03;

	---- 프로시저 실행
	SET SERVEROUTPUT ON
	EXECUTE EX03;

Q7) 명시적 커서 SQL%를 사용해서 프로시저를 호출할 때 사원번호를 입력해서 봉급을 1.1% 인상시키자 EX04

    선언하는 커서  : CURSOR USER_NAME IS 구문;
    명시적 커서 : SQL%

--명시적 커서 SQL%를 사용해서 프로시저로 사번의 값을 받아 출력 월급을 인상하고 급여가 인상된 사원 수 출력해보자

	CREATE OR REPLACE PROCEDURE EX04 (P_EMPNO IN EMP.EMPNO%TYPE) -- 매개변수는 아무거나 해도 가능
	AS 
  		V_SAL EMP.SAL%TYPE;
   		V_UPDATE_CNT NUMBER;   
	BEGIN
   		SELECT SAL INTO V_SAL
    		FROM EMP
    		WHERE EMPNO = P_EMPNO;
    
    		IF SQL%FOUND THEN -- 명시적 커서가 있다면 다음을 수행하자
    		DBMS_OUTPUT.PUT_LINE('검색한 데이터가 존재한다 '|| V_SAL);
    
   		 UPDATE EMP
    		SET SAL = SAL*1.1
    		WHERE EMPNO = P_EMPNO;
    
   		 V_UPDATE_CNT :=SQL%ROWCOUNT;
    		DBMS_OUTPUT.PUT_LINE('급여가 인상된 사원의 수' || V_UPDATE_CNT);
    		END IF;
	END EX04;

	---- 프로시저 실행
	 EXEC EX04(7900); -> 사원번호는 하나라 LOOP X

Q8) 선언하는 커서 CURSOR USER_NAME IS 구문;를 이용해서 EX05 프로시저를 만들어 보자
     - 부서번호에 해당하는 봉급을 가져와서 1.1% 인상된 금액으로 수정하고 사번,이름,봉급을 출력해보자 FOR문 사용

CREATE OR REPLACE PROCEDURE EX05(P_DEPTNO IN EMP.DEPTNO%TYPE) AS

    CURSOR EMP_CURSOR IS
    SELECT EMPNO, ENAME, SAL
    FROM EMP
    WHERE DEPTNO = P_DEPTNO
    FOR UPDATE; -- 오라클에서 필요
    
BEGIN

    FOR ABC IN EMP_CURSOR LOOP
    UPDATE EMP
    SET SAL = SAL*1.1
    WHERE CURRENT OF EMP_CURSOR; -- 현재 커서의 ROW를 가져와서 수정하겠다.
    DBMS_OUTPUT.PUT_LINE(ABC.EMPNO || ' ' || ABC.ENAME || ' ' || ABC.SAL);
    END LOOP;

END EX05;

	---- 프로시저 실행
	 EXEC EX05(30);

Q9) EX06을 실행해서 EMP 테이블을 전체 내용을 리턴하더라
	
	SELECT * FROM EMP; 


* 프로시저 생성할 때 변수 추가 -> 모드 IN : 입력만 , OUT : 출력만 IN OUT : 입력 출력 모두

	CREATE OR REPLACE PROCEDURE EX06 (MY_RES OUT SYS_REFCURSOR)
	 AS 
	BEGIN
 		 OPEN MY_RES FOR
   		 SELECT * FROM EMP;
	END EX06;


----실행
    VAR RES REFCURSOR;
    EXEC EX06(:RES) -- 시스템 변수는 앞에 :
    PRINT :RES;
    
Q10) EX07을 실행해서 직업을 입력하면 사원의 전체 정보를 리턴하더라
	
	CREATE OR REPLACE PROCEDURE EX07 (M_JOB IN EMP.JOB%TYPE,M_RES OUT SYS_REFCURSOR)
	AS 

	BEGIN
 	 OPEN M_RES FOR
  	SELECT * FROM EMP WHERE JOB = M_JOB;
   
	END EX07;

-- 실행
    VAR RES REFCURSOR;
     EXEC EX07('SALESMAN',:RES);
     PRINT :RES;

Q11) 패키지 : 연관성이 높은 함수나 프로시저를 하나의 그룹으로 묶어서 관리하고 사용하는 것
    
      - PACKAGE = SPEC + BODY
	SPEC = 해당 패키지에 사용될 함수나 프로시저, 변수 등에 대한 정의를 선언
	BODY = 선언부에 있는 정의된 식별에 해당하는 실제 코드로 구현되는 부분

      - 삭제
	1) 선언과 몸체 모두 삭제 : DROP PACKAGE package_name;
  	2) 몸체 부분만 삭제 : DROP PACKAGE BODY package_name;

  	EMP테이블에서 총 급여의 합계와 평균 급여를 구하는 MY_PACKAGE를 만들어서 실행 해보자
	
	CREATE OR REPLACE PACKAGE my_package
     	IS
         PROCEDURE MY_SUM;
         PROCEDURE MY_AVG;
         END MY_PACKAGE; -- 선언부 SPEC


      CREATE OR REPLACE PACKAGE BODY my_package
      IS 
       PROCEDURE MY_SUM IS 
       CURSOR E_SUM IS SELECT SUM(NVL(SAL,0)) FROM EMP;
       TOTAL_SUM NUMBER;
       BEGIN
        OPEN E_SUM;
            FETCH E_SUM INTO TOTAL_SUM;
            DBMS_OUTPUT.PUT_LINE('봉급의 합계 :' || TOTAL_SUM);
            CLOSE E_SUM;
         END MY_SUM;   
         
            PROCEDURE MY_AVG IS 
       CURSOR E_AVG IS SELECT AVG(NVL(SAL,0)) FROM EMP;
       TOTAL_AVG NUMBER;
       BEGIN
        OPEN E_AVG;
            FETCH E_AVG INTO TOTAL_AVG;
            DBMS_OUTPUT.PUT_LINE('봉급의 평균 :' || TOTAL_AVG);
            CLOSE E_AVG;
         END MY_AVG;   
  END MY_PACKAGE; --바디 BODY