> # 3. 커서
- 오라클 명령에 영향받은 행들의 집합
- SELECT 문인 경우 결과 집합 --뷰
1) 묵시적 커서 --커서를 생성한게 아니라 sql을 해석해서 실행됨
- 이름이 없는 커서
- 자동생성되며 OPEN과 동시에 CLOSE되기 때문에 커서 내의 데이터 접근이 허용되지 않음
- 커서속성
속성 의미
SQL%ISOPEN 커서가 OPEN 되었으면 TRUE (항상 FALSE)
SQL%FOUND SQL명령의 결과로 하나 이상 행이 반환되었으면 TRUE
SQL%NOTFOUND SQL명령의 결과로 하나의 행도 반환되지 않았으면 TRUE
SQL%ROWCOUNT SQL명령으로 영향받은 행의 개수2)명시적 커서
선언문에 의하여 생성되는 커서
커서의 사용은 선언 => OPEN=>FETCH=>CLOSE 단계를 거쳐야 함
(단, FOR문에서 사용시에는 예외)
(1) 커서선언
- DECLARE 선언부에서 선언
(선언형식)
CURSOR 커서명[(데이터타입 변수명,...)] IS
SELECT 문;
- 매개변수는 OPEN문에서 값을 배정함
(2) 커서 OPEN
- 커서사용을 위해 반드시 필요
- 모든 커서는 사용전에 OPEN 되고 사용 후 CLOSE 되어야 함
- 실행영역에서 기술
(사용형식)
OPEN[(expr[,expr,...])];
- expr: 커서 선언문에 전달할 값을 지정
(3) 커서 FETCH
- 커서내의 데이터를 읽어오는 역할 수행(행단위)
- 실행영역 내의 반복문 안에 위치
- 반복문 밖에서 기술
(사용형식)
FETCH 커서명 INTO 변수명[,변수명,...]
(4) 커서 CLOSE
- OPEN되어 사용이 종료된 커서는 반드시 CLOSE되어야함
- CLOSE 되지 않은 커서는 재 OPEN 될 수 없음
(사용형식)
CLOSE 커서명;
4. 반복명령
- 개발언어의 반복문과 동일 기능 제공
- LOOP, WHILE, FOR 문이 제공됨
1) LOOP 문 --자바의 DO문
- 조건이 없는 단순한 무한 루프기능을 제공
- EXIT 문을 이용하여 반복을 벗어날 수 있음
- 모든 반복명령의 기본 구조로 사용
(사용형식)
LOOP
반복시킬 명령문(들);
[EXIT WHEN 조건;]
END LOOP;
- 'EXIT WHEN 조건' : 조건이 참(true)면 LOOP 문을 벗어남
사용예) 구구단의 7단을 LOOP문으로 구성하시오
DECLARE
V_RES NUMBER:=0;
V_CNT NUMBER:=1;
BEGIN
LOOP
EXIT WHEN V_CNT >9;
V_RES:=7*V_CNT;
DBMS_OUTPUT.PUT_LINE(7||'*'||V_CNT||'='||V_RES);
V_CNT:=V_CNT+1;
END LOOP;
END;
커서 사용예) 2005년 6월 매입상품별 판매현황을 조회하시오
1) 2005년 6월 매입상품 조회 => 커서
SELECT DISTINCT BUY_PROD
FROM BUYPROD
WHERE BUY_DATE BETWEEN TO_DATE('20050601') AND TO_DATE('20050630'); 2) 익명블록
DECLARE
V_PID PROD.PROD_ID%TYPE; --상품코드
V_PNAME PROD.PROD_NAME%TYPE; --상품명
V_CNT NUMBER:=0; --판매수량합계
V_SUM NUMBER:=0; --판매금액합계
CURSOR CUR_BUYPROD IS
SELECT DISTINCT BUY_PROD
FROM BUYPROD
WHERE BUY_DATE BETWEEN TO_DATE('20050601') AND TO_DATE('20050630');
BEGIN
OPEN CUR_BUYPROD;
LOOP
FETCH CUR_BUYPROD INTO V_PID;
EXIT WHEN CUR_BUYPROD%NOTFOUND;
SELECT SUM(CART_QTY), SUM(CART_QTY*PROD_PRICE)
INTO V_CNT, V_SUM
FROM CART, PROD
WHERE CART_NO LIKE '200506%'
AND CART_PROD=V_PID
AND CART_PROD=PROD_ID;
SELECT PROD_NAME INTO V_PNAME
FROM PROD
WHERE PROD_ID=V_PID;
DBMS_OUTPUT.PUT_LINE('상품코드 : ' ||V_PID);
DBMS_OUTPUT.PUT_LINE('상품명 : ' ||V_PNAME);
DBMS_OUTPUT.PUT_LINE('판매수량 : ' ||V_CNT);
DBMS_OUTPUT.PUT_LINE('판매금액 : ' ||V_SUM);
DBMS_OUTPUT.PUT_LINE('------------------');
END LOOP;
END;
사용예) 거주지 도시를 키보드로 입력받아 그 거주지에 살고 있는 회원의 정보를 출력하시오
출력할 회원정보는 회원번호, 회원명, 주소이다.
ACCEPT P_CITY PROMPT '시도 입력: '
DECLARE
CURSOR CUR_MEM(P_SIDO MEMBER.MEM_ADD1%TYPE ) IS -- OPEN 에서 받은거
SELECT MEM_ID, MEM_NAME
FROM MEMBER
WHERE SUBSTR(MEM_ADD1,1,2)=SUBSTR(P_SIDO,1,2);
V_MID MEMBER.MEM_ID%TYPE;
V_MNAME MEMBER.MEM_NAME%TYPE;
V_ADDR VARCHAR2(200);
V_RESULT VARCHAR2(500);
BEGIN
OPEN CUR_MEM('&P_CITY');
LOOP
FETCH CUR_MEM INTO V_MID, V_MNAME;
EXIT WHEN CUR_MEM%NOTFOUND;
SELECT MEM_ADD1||' '||MEM_ADD2 INTO V_ADDR
FROM MEMBER
WHERE MEM_ID=V_MID;
V_RESULT:=V_MID||' ' || V_MNAME||' ' ||V_ADDR;
DBMS_OUTPUT.PUT_LINE(V_RESULT);
END LOOP;
CLOSE CUR_MEM;
END;
2)WHILE 문
-
개발언어의 WHILE문과 같은 기능 제공
-
반복 수행전 조건을 체크하여 반복 수행여부 결정
(사용형식)
WHILE 조건 LOOP
반복처리문(들);
:
END LOOP;- 조건이 만족하면(TRUE) 반복 수행
사용예) 구구단의 7단을 출력
DECLARE
V_CNT NUMBER:=1;
BEGIN
WHILE V_CNT<=9 LOOP
DBMS_OUTPUT.PUT_LINE(7||'*'||V_CNT||'='||7*V_CNT);
V_CNT:=V_CNT+1;
END LOOP;
END;사용예) 2005년 금액을 기준으로 가장 많은 상품을 구매한 회원 5명의 회원명, 주소를 출력하는 익명블록을 작성하시오
(가장 많은 상품을 구매한 회원 5명=>커서)
SELECT A.CART_MEMBER
FROM (SELECT CART_MEMBER,
SUM(CART_QTY*PROD_PRICE)
FROM CART, PROD
WHERE CART_PROD=PROD_ID
AND CART_NO LIKE '2005%'
GROUP BY CART_MEMBER
ORDER BY 2 DESC)A
WHERE ROWNUM<=5;
(익명블록-WHILE)
DECLARE
CURSOR CUR_CART01 IS
SELECT A.CART_MEMBER
FROM (SELECT CART_MEMBER,
SUM(CART_QTY*PROD_PRICE)
FROM CART, PROD
WHERE CART_PROD=PROD_ID
AND CART_NO LIKE '2005%'
GROUP BY CART_MEMBER
ORDER BY 2 DESC)A
WHERE ROWNUM<=5;
V_MID MEMBER.MEM_ID%TYPE;
V_MNAME MEMBER.MEM_NAME%TYPE;
V_ADDR VARCHAR2(200);
BEGIN
OPEN CUR_CART01;
FETCH CUR_CART01 INTO V_MID;
WHILE CUR_CART01%FOUND LOOP --커서에 데이터가 있으면 돔
SELECT MEM_NAME, MEM_ADD1||' '||MEM_ADD2
INTO V_MNAME, V_ADDR
FROM MEMBER
WHERE MEM_ID = V_MID;
DBMS_OUTPUT.PUT_LINE(V_MID||' '||V_MNAME||' '||V_ADDR);
FETCH CUR_CART01 INTO V_MID;
END LOOP;
CLOSE CUR_CART01;
END;
3) FOR 문
- 반복횟수를 정확히 알고 있거나 반복횟수가 중요한 경우 사용
(일반적 FOR 문 사용형식)
FOR 제어변수 IN [REVERSE] 초기값..최종값 LOOP
반복처리문(들);
:
END LOOP;
사용예) 구구단의 7단을 출력
DECLARE
BEGIN
FOR I IN 1..9 LOOP --I는 1~9까지 증가하면서 반복
DBMS_OUTPUT.PUT_LINE(7||'*'||I||'='||7*I);
END LOOP;
END;(역순)
DECLARE
BEGIN
FOR I IN REVERSE 1..9 LOOP --I는 9~1까지 증가하면서 반복
DBMS_OUTPUT.PUT_LINE(7||'*'||I||'='||7*I);
END LOOP;
END; (커서를 위한 FOR 문 사용형식)
FOR 레코드명 IN 커서명|커서선언문 LOOP
반복처리문(들);
:
END LOOP;
- '레코드명' : 시스템에서 제공되며 커서의 각 행을 지칭한다
ex)커서의 컬럼이 a1, a2이고 레코드명이 REC인경우 커서컬럼 참조는 REC.a1 REC.a2 와 같이 사용=>변수사용이 불필요
- 커서명|커서선언문: 커서를 IN-LINE 서브쿼리 형식으로 직접 정의할 수 있음
- FOR문에 사용되는 커서는 OPEN, FETCH, CLOSE 문이 불필요
DECLARE
CURSOR CUR_CART01 IS
SELECT A.CART_MEMBER AS CID
FROM (SELECT CART_MEMBER,
SUM(CART_QTY*PROD_PRICE)
FROM CART, PROD
WHERE CART_PROD=PROD_ID
AND CART_NO LIKE '2005%'
GROUP BY CART_MEMBER
ORDER BY 2 DESC)A
WHERE ROWNUM<=5;
V_MNAME MEMBER.MEM_NAME%TYPE;
V_ADDR VARCHAR2(200);
BEGIN
FOR REC IN CUR_CART01 LOOP
SELECT MEM_NAME, MEM_ADD1||' '||MEM_ADD2
INTO V_MNAME, V_ADDR
FROM MEMBER
WHERE MEM_ID =REC.CID;
DBMS_OUTPUT.PUT_LINE(REC.CID||' '||V_MNAME||' '||V_ADDR);
END LOOP;
END;
DECLARE
V_MNAME MEMBER.MEM_NAME%TYPE;
V_ADDR VARCHAR2(200);
BEGIN
FOR REC IN (SELECT A.CART_MEMBER AS CID
FROM (SELECT CART_MEMBER,
SUM(CART_QTY*PROD_PRICE)
FROM CART, PROD
WHERE CART_PROD=PROD_ID
AND CART_NO LIKE '2005%'
GROUP BY CART_MEMBER
ORDER BY 2 DESC)A
WHERE ROWNUM<=5)
LOOP
SELECT MEM_NAME, MEM_ADD1||' '||MEM_ADD2
INTO V_MNAME, V_ADDR
FROM MEMBER
WHERE MEM_ID =REC.CID;
DBMS_OUTPUT.PUT_LINE(REC.CID||' '||V_MNAME||' '||V_ADDR);
END LOOP;
END;