230803 SQL 강의

--EXIT
--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;
/

---

--FOR 문

--FOR index IN [REVERSE]  최소값 .. 최대값 LOOP
--    처리문장들;
--END LOOP;

--- index는 1씩 증가하는 자동선언 정수형 변수(최소, 최대값)
--- REVERSE 가 사용될 경우 1씩 감소
--- IN 다음에 SELECT 문, CURSOR 문이 올 수 있다.
--- SELECT, CURSOR 를 사용한 경우 index는 레코드타입 	변수

BEGIN
  FOR i IN 1..10 LOOP 
    DBMS_OUTPUT.PUT_LINE( 'i = ' || i );
  END LOOP;  
END;
/

---

DECLARE
  TYPE starcraft IS VARRAY(20) OF VARCHAR2(12); -- Type 선언
  v_star starcraft;
BEGIN
  v_star := starcraft('Terran','Protos');
  v_star.EXTEND; 
  v_star(3) := 'Zerg'; 
  v_star.EXTEND;
  v_star(4) := '홍길동족';
  DBMS_OUTPUT.PUT_LINE('스타크래프트 종족 : '||  v_star.COUNT);
  FOR i IN v_star.FIRST..v_star.LAST LOOP
      DBMS_OUTPUT.PUT_LINE ( i || '번째 종족 : ' || v_star(i));
  END LOOP;
END;
/

---

--TABLE Type을 사용하여 분류테이블정보 담기
DECLARE
  TYPE lprod_nm_table IS TABLE OF VARCHAR2(40)
      INDEX BY PLS_INTEGER;

  t_lprod_nm lprod_nm_table;
BEGIN  
  FOR  l_list IN (SELECT lprod_id, lprod_nm FROM lprod) LOOP
      t_lprod_nm(l_list.lprod_id) := l_list.lprod_nm;
      DBMS_OUTPUT.PUT_LINE (l_list.lprod_id || ' = ' || l_list.lprod_nm  );
  END LOOP;
  DBMS_OUTPUT.PUT_LINE ('갯수 = ' || t_lprod_nm.COUNT); 
  
  DBMS_OUTPUT.PUT_LINE ('============================'); 
  
  FOR i IN t_lprod_nm.FIRST..t_lprod_nm.LAST LOOP
	IF t_lprod_nm.EXISTS(i) THEN
  	   DBMS_OUTPUT.PUT_LINE ( i || ' ' || t_lprod_nm(i));
	END IF;
  END LOOP;
  DBMS_OUTPUT.PUT_LINE ('갯수 = ' || t_lprod_nm.COUNT); 
END;
/

---

--미리 정의된 예외인 경우

DECLARE 
   v_name varchar2(20);
BEGIN
   SELECT  lprod_nm  INTO v_name FROM lprod WHERE lprod_gu = 'P201'; 
   DBMS_OUTPUT.PUT_LINE ('분류명 =' || v_name);

EXCEPTION 
        WHEN  NO_DATA_FOUND  THEN 
            DBMS_OUTPUT.PUT_LINE ( '해당 정보가 없습니다.');
        WHEN  TOO_MANY_ROWS  THEN 
            DBMS_OUTPUT.PUT_LINE (' 한개 이상의 값이 나왔습니다. ');
        WHEN  OTHERS  THEN  
            DBMS_OUTPUT.PUT_LINE ( '기타 에러 :' || SQLERRM  ); 
END;
/

--정의되지 않은 예외인 경우
--   - 선언부에 예외의 이름을 지정한다.
--   - PRAGMA 를 기술하고 EXCEPTION_INIT 으로 예외이름과 에러번호를 컴파일러에게 등록한다. 
--     (Pragma 는 실행될 때 처리되지 않는 명령문임을 알려주는 예약어)
--   - EXCEPTION 영역에 해당 예외 처리

DECLARE 
   exp_reference EXCEPTION;
   PRAGMA EXCEPTION_INIT(exp_reference, -2292);
BEGIN
   DELETE FROM lprod WHERE lprod_gu = 'P101';
   DBMS_OUTPUT.PUT_LINE ('분류 삭제');
EXCEPTION 
     WHEN exp_reference THEN
           DBMS_OUTPUT.PUT_LINE ( '삭제 불가 :' || SQLERRM );
     WHEN OTHERS THEN
          DBMS_OUTPUT.PUT_LINE ( SQLCODE  ||  ' ' || SQLERRM  );
END;
/

--사용자 정의 예외인 경우
--   - 선언부에 예외의 이름을 지정한다.
--   - 실행부에서 RAISE문장으로 명시적으로 발생
--   - EXCEPTION 영역에 해당 예외 처리
ACCEPT p_lgu PROMPT '등록하려는 분류코드 입력 :'
DECLARE 
   exp_lprod_gu EXCEPTION;
   v_lgu VARCHAR2(10) := UPPER('&p_lgu');
BEGIN
   IF v_lgu IN ('P101','P102','P201','P202') THEN
        RAISE exp_lprod_gu;
   END IF;
   DBMS_OUTPUT.PUT_LINE (v_lgu || '는 등록 가능');
EXCEPTION 
     WHEN exp_lprod_gu THEN
           DBMS_OUTPUT.PUT_LINE ( v_lgu ||  '는 이미 등록된 코드 입니다.');
END;
/

---

---

--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 -- EXTRACT() : 날짜정보 추출 함수
        GROUP BY buy_prod ORDER BY buy_prod ASC;
BEGIN  
  OPEN UpRemain_cur;
  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 
SELECT DISTINCT(MEM_JOB) FROM MEMBER;

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_id, 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;
/

--위 예시에서 마일리지가 3000이상인 사람만 출력하게 하시오.
SELECT mem_id, mem_name, mem_mileage
  FROM member 
 WHERE mem_mileage >= 3000
 ORDER BY mem_name ASC;

BEGIN   
  FOR mem_rec IN (SELECT mem_id, mem_name, mem_mileage
                    FROM member 
                   WHERE mem_mileage >= 3000
                   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;
/

---

--상품코드를 매개변수(parameter)로 하여 재고수량 ADD

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;
/

SELECT prod_id, prod_totalstock 
  FROM prod 
 WHERE prod_id = 'P102000006';

EXECUTE usp_prod_totalstock_update('P102000006', 500);

---

-- 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; 
/

--OUT 매개변수 예제 1 (cont.) -> 전체를 블록한 후 스크립트 실행
--- 실행
VAR mem_name VARCHAR2(20)
VAR mem_like VARCHAR2(20)
EXECUTE usp_MemberID ('a001', :mem_name, :mem_like);
PRINT mem_name
PRINT mem_like;
/

---