DB 0706

EXIT [label][WHEN 조건]
:WHEN 을 사용하여 조건에 따라서 반복문을 빠져 나감

EXIT WHEN 을 사용해 1부터 10까지 더하기
DECLARE
 v_sum NUMBER := 0;
 v_var   NUMBER := 1;
BEGIN
 LOOP
   v_sum := v_sum + v_var;
   v_var   := v_var + 1;
   EXIT  WHEN  v_var > 10 ;
 END LOOP;
 DBMS_OUTPUT.PUT_LINE('1 부터 10 까지의 합 = ' || v_sum);
END;
/

CURSOR 문

:SELECT 문에서 생성된 결과 집합에 대해 개별적인 행 단위 작업을 가능하게 함

• Query결과를 읽거나 수정, 삭제할 수 있도록 해주는 개념
• SELECT문의 Query결과를 먼저 정의한 후 이를 바탕으로 첫레코드~마지막 레코드까지 액세스
• 선택된 행들은 서버상에서 개별적으로 처리
• 개발자가 PL/SQL 블록에서 수동으로 제어

사용

DECLARE ... CURSOR ... : 선언부에 커서를 선언
OPEN : 커서에 대한 메모리 할당 및 정보를 저장, 입력변수에 대한 바인드작업
FETCH

• 현재 행을 읽어 들인 후 변수에 저장
• 커서 속성을 이용하여 행이 존재하는지 검사
• 처리할 행인 있다면 FETCH 반복
  CLOSE
• 사용을 마친 커서는 반드시 닫아야 함
• 커서를 닫고 버퍼용으로 사용중인 메모리를 반환
• 커서를 다시 열수는 있지만 데이터를 FETCH하거나 갱신, 삭제할 수 없음

커서 속성

%ISOPEN : 커서가 열린 상태이면 TRUE
NOTFOUND : SQL문장이 어떠한 영향을 미치지 않았다면 TRUE
%FOUND : SQL문장이 하나 이상의 영향을 미쳤다면 TRUE
%ROWCOUNT : SQL 문장에 의해 영향을 받은 행의 수

활용

-- 2020년도 및 상품별 총 입고수량을 출력하는 커서
DECLARE
    v_prod VARCHAR2(30);
    v_qty NUMBER(10, 0);
    
    CURSOR UpRemain_cur IS
        SELECT buy_prod, SUM(buy_qty) FROM buyprod
        WHERE EXTRACT(YEAR FROM buy_date) = 2020
        GROUP BY buy_prod ORDER BY buy_prod ASC;
BEGIN
    OPEN UpRemain_cur;
    FETCH UpRemain_cur INTO v_prod, v_qty;
    FETCH UpRemain_cur INTO v_prod, v_qty;
    WHILE (UpRemain_cur%FOUND) LOOP
        DBMS_OUTPUT.PUT_LINE( UpRemain_cur%ROWCOUNT || ' 번째 상품 = ' || v_prod || ' 입고수량 = '
                                || v_qty || '입니다.');
        FETCH UpRemain_cur INTO v_prod, v_qty;
    END LOOP;
    CLOSE UpRemain_CUR;
END;
/

-- 직업을 변수로 받아 이름 회원명과 마일리지를 출력하는 커서
DECLARE
    v_name VARCHAR2(30);
    v_mileage NUMBER(10);
    
    CURSOR member_cur (v_job VARCHAR2) IS
        SELECT mem_name, mem_mileage FROM member
        WHERE mem_job = v_job
        ORDER BY mem_name ASC;
BEGIN
    OPEN member_cur('주부');
    LOOP
        FETCH member_cur INTO v_name, v_mileage;
        EXIT WHEN member_cur%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(member_cur%ROWCOUNT || '번째 ' || v_name || ', ' || v_mileage);
    END LOOP;
    CLOSE member_cur;
END;
/

FOR LOOP를 이용하는 CURSOR

: 반복하는 동안 커서를 자동으로 OPEN하고 모든 행이 처리되면 자동으로 커서를 CLOSE

Subquery를 이용한 FOR LOOP

: 서브쿼리를 사용하여 커서를 선언하지 않아도 동일한 결과

!!
--직업을 입력받아서 FOR LOOP를 이용하는 CURSOR 
    
ACCEPT p_job PROMPT '직업을 입력하세요 :'

DECLARE 
  v_name VARCHAR2(30);
  v_mileage  NUMBER(10);
  CURSOR member_cur  IS
    SELECT mem_name, mem_mileage 
      FROM member
    WHERE mem_job = '&p_job' 
    ORDER BY mem_name ASC;
BEGIN   

  FOR mem_rec IN member_cur LOOP    
     DBMS_OUTPUT.PUT_LINE( member_cur%ROWCOUNT || '번째 ' 
                           || mem_rec.mem_name || ', ' || mem_rec.mem_mileage );
  END LOOP;

END;
/

--Subquery를 이용한 FOR LOOP
    
BEGIN   
  FOR mem_rec IN (SELECT mem_name, mem_mileage
                                  FROM member ORDER BY mem_name ASC)  LOOP    
      DBMS_OUTPUT.PUT_LINE( mem_rec.mem_id || ', '                                                    ||  mem_rec.mem_name || ', '                                                    || mem_rec.mem_mileage );  
  END LOOP;
END;
/

Stored Procedure

: 서버에 저장된 미리 컴파일 된 SQL문장들

• 저장 프로시져를 처음 수행될 때 문법을 검사하고 컴파일 됨
  :컴파일된 버전은 프로시져 캐시에 저장되므로 이후에 호출될 때 빠르게 수행 가능

• 클라이언트간 처리 루틴 공유
  :모든 응용 프로그램에서 사용할 수 있도록 기능을 캡슐화 하므로 일관성있는 데이터 변경을 보장

• 데이터베이스 내부 구조 보안 : VIEW와 동일한 개념

• 서버 보호, 자료 무결성(Integrity)권한 구현
  :VIEW사용 시 Table을 직접 Access하는 것이 아니라 View에 대한 권한만 주어 Table의 특정Column과 Record만 Access하도록 제한 가능
  :저장함수도 이와 같은 개념으로 서버 데이터를 보호하는데 사용 가능

• Query처리 속도 향상
  저장함수 안에 지정된 모든 SQL구문을 하나의 Batch로
  인식하여 한꺼번에 분석, 최적화 시키고 실행.
  각각의 SQL구문을 Client로부터 받아서 매번 분석, 최적화,
  실행과정을 반복하는 것에 비해 처리속도가 현저히 향상된다

• Network Traffic 감소
  Client에서 Server로 보내야 할 SQL구문을 Server가 미리 저장.
  고로 Client에서 대량의 SQL구문을 보내는 대신에
  Stored Procedure의 이름과 매개변수(Parameter)만 보내면 되므로
  Network Traffic 이 그만큼 줄어드는 효과
  

Procedure 구문

-- 상품코드를 매개변수(parameter)로 하여 재고수량  ADD
SELECT prod_totalstock, prod_id  FROM PROD;

CREATE OR REPLACE PROCEDURE usp_prod_totalstock_update
    (v_prod_id IN prod.prod_id%TYPE,
    v_qty IN prod.prod_totalstock%TYPE)
IS
BEGIN
    UPDATE prod
        SET prod_totalstock = prod_totalstock + v_qty
     WHERE prod_id = v_prod_id;
    DBMS_OUTPUT.PUT_LINE('정상적으로 업데이트 되었습니다.');
    COMMIT;
    
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('예외 발생: ' || SQLERRM);
        ROLLBACK;
END;

--Procedure 실행
EXECUTE usp_prod_totalstock_update('P102000006', 500);
--EXEC usp_prod_totalstock_update('P102000006', 200);

--확인
SELECT prod_id, PROD_totalstock
    FROM prod
 WHERE prod_id = 'P102000006';

OUT 매개변수의 간단 출력

VAR 문을 통하여 변수선언
변수를 사용할때는 ":" 을 사용, PRINT 문으로 변수의 값을 출력

--OUT 매개변수 예제 1>회원아이디를 입력받아 이름과 취미를  OUT 매개변수로 처리
CREATE OR REPLACE PROCEDURE usp_MemberID
    (p_mem_id IN member.mem_id%TYPE,
     p_mem_name OUT member.mem_name%TYPE,
     p_mem_like OUT member.mem_like%TYPE)
IS
BEGIN
    SELECT mem_name, mem_like
        INTO p_mem_name, p_mem_like
     FROM member
    WHERE mem_id = p_mem_id;
END;
VAR mem_name VARCHAR2(20);
VAR mem_like VARCHAR2(20);
EXECUTE usp_MemberID ('a001', :mem_name, :mem_like);
PRINT mem_name
PRINT mem_like;
/

-- OUT 매개변수 예제 2>
CREATE OR REPLACE PROCEDURE usp_MemberCartTop
    (p_year IN VARCHAR2,
     p_amt OUT NUMBER,
     p_mem_name OUT member.mem_name%TYPE)
IS
    v_year VARCHAR2(5);
BEGIN
    v_year := (p_year || '%');
    SELECT mem_name, mem_amt INTO p_mem_name, p_amt
     FROM (
        SELECT mem_name, SUM(prod_price * cart_qty) mem_amt
            FROM member, cart, prod
         WHERE cart_no LIKE v_year
            AND cart_member = mem_id
            AND cart_prod = prod_id
        GROUP BY mem_name
        ORDER BY SUM(prod_price * cart_qty) DESC
        )
    WHERE ROWNUM <= 1;
END;
/
--  OUT 매개변수 예제 2 (cont.)
VAR send_member VARCHAR2
VAR send_amt NUMBER
EXEC usp_MemberCartTop('2020', :send_amt, :send_member);
PRINT send_member
PRINT send_amt;

User Defined Function

:Function은 Procedure가 갖는 장점은 동일

-반환값 O
: 즉, 일반 오라클 내장함수처럼 사용할 수 있음
: 자주 반복되는 subquery, 복잡한 계산식을 사용자가 만들어서 일반 함수처럼 사용 가능
-반환할 데이터 타입을 RETURN 으로 선언, 실행영역에서 RETURN 문이 있어야 한