DAY31
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