Day30

Q1) 만들어진 프로시저 리스트를 확인

SELECT* FROM user_procedures;

---

Q2) 프로시저 내용까지 확인

SELECT*FROM USER_SOURCE;

---

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

커서의 선언방법 : 변수처럼 사용. row객체를 하나 이상 관리하는 객체

CREATE OR REPLACE PROCEDURE EX01
 AS
    v_empno emp.empno%type;
    v_ename emp.ename%type;
    v_sal number(7,2) := 0;
    
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 number(7,2) := 0;
    
cursor emp_cursor is --(1. 커서 선언)
select ename, sal
from emp;
    
Begin
OPEN emp_cursor ; -- (2. 커서를 시작)
loop
FETCH emp_cursor into v_ename, v_sal ; --(3.변수 대입)
exit when emp_cursor%rowcount > 5 or emp_cursor%notfound;
        
DBMS_OUTPUT.PUT_LINE(v_ename ||' '|| v_sal);
end loop;
close emp_cursor; -- (4.커서 종료)
 end EX02;

---

Q6) 프로시저를 실행 할 때 부서번호를 전달해서 해당 부서번호를 출력하는 프로시저 만들기 EX03

SELECT EMPNO, ENAME, SAL
FROM EMP
WHERE DEPTNO =10; -- V_DEPTNO (커서의 매개변수로 선언)

SELECT EMPNO, ENMAE,SAL
FROM EMP
WHERE DEPTNO=20; -- V_DEPTNO (커서의 매개변수로 선언)

EXEC EX03;


-- **프로시저를 실행 할 때 부서번호를 전달해서 해당 부서번호를 출력하는 프로시저 만들기 EX03**

CREATE OR REPLACE PROCEDURE EX03 AS 
V_EMPNO emp.empno%TYPE;
V_ENAME emp.ename%TYPE;
V_SAL EMP.SAL%TYPE;

cursor emp_cursor(V_DEPTNO NUMBER) is --(1. 커서 선언)
    select EMPNO, ename, sal
    from emp
    WHERE DEPTNO=V_DEPTNO;

BEGIN
 OPEN emp_cursor(10) ;  --(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.커서 종료)
 
OPEN emp_cursor(20) ; -- 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 EX03;

---

Q7) 선언하는 커서 cursor_user_name is 구문;

• 명시적 커서 SQL% 를 사용해서 프로시저를 호출할 때 사원번호를 입력해서 봉급을 1.1% 인상시키자 EX04
• 명시적 커서 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);

---

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(20);

---

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

 SELECT * FROM EMP;
 
 
 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 = 선언부에 있는 정의 된 식별에 해당하는 실제 코드로 구현되는 부분

삭제

• 선언과 몸체 모두 삭제 : DROP PACKAGE PACKAGE_NAME;
• 몸체 부분만 삭제 : DROP PACKAGE BODY PACKAGE_NAME;

EMP 테이블에서 총 급여의 합계와 평균 급여를 구하는 MY_PACKAGE 를 만들어서 실행 해보자.

--spec
CREATE OR REPLACE PACKAGE my_package
is
procedure my_sum;
procedure my_avg;
end my_package;

--body
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 SUM(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;