3.커서
- 오라클 명령에 영향 받은 행들의 집합
- SELECT문인 경우 결과 집합
1)묵시적 커서
. 이름이 없는 커서
. 자동생성되며 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되면 다시 OPEN될 수 있다
(사용형식)
CLOSE 커서명;
SELECT * FROM LPROD --이름이 없는, 선언되지 않은 커서 = 묵시적 커서
4.반복명령
- 개발언어의 반복문과 동일 기능 제공
- LOOP,WHILE,FOR 문이 제공됨
1)LOOP 문
. 조건이 없는 단순한 무한 루프기능을 제공
. EXIT 문을 이용하여 반복을 벗어날 수 있음
. 모든 반복명령의 기본 구조로 사용
(사용형식)
LOOP
반복시킬 명령문(들);
[EXIT WHEN 조건;]
END LOOP;
- 'EXIT WHEN 조건' : 조건이 참(true)이면 LOOP 문을 벗어남
사용예)구구단의 7단을 LOOP문으로 구성하시오
DECLARE
V_RES NUMBER:=0; --곱한 값(사용 안해도 되는 변수)
V_CNT NUMBER:=1; --1~9까지 변화되는 값
BEGIN
LOOP
EXIT WHEN V_CNT>9;--구구단은 9까지 돌아가야 하니까
V_RES:=7*V_CNT;
DBMS_OUTPUT.PUT_LINE(7||'*'||V_CNT||'='||V_RES);
V_CNT:=V_CNT+1; -- 1씩 증가
END LOOP;
END;반복문이 쓰여지는 경우 -> 커서를 수행하기 위함(커서를 수행하기 위해 반복문이 제공됨)
SELECT문의 결과 = 뷰 = 커서
SELECT * FROM LPROD --이름이 없는, 선언되지 않은 커서 = 묵시적 커서
커서는 1번행을 출력할 때 OPEN되고 마지막행을 출력하고 CLOSE됨
=>이미 나온 결과는 CLOSE되었기 때문에 출력된 값에 접근 불가
한명 한명의 정보를 꺼내서 데이터 분석을 할 때 커서가 필요
묵시적 커서는 OPEN되자마자 CLOSE됨, 그래서 우리가 쓰는 것은 명시적 커서
명시적커서의 처음 POINT는 첫 행에 있음, 가리키는 것은 커서의 이름.
커서 내에 접근할 자료가 있는지 없는지 판별해야함 -> 이때 사용되는 것이 커서 속성
커서명%FOUND - 커서 내에 아직 접근할 자료가 남아있으면 true / 없으면 false
커서명%NOTFOUND - FOUND의 반대 자료가 없으면 true / 남아있으면 false
FETCH : 명령을 꺼내와서 읽어오는 것(SELECT문과 비슷)
한 번 FETCH가 실행될 때마다 한 행씩 데이터가 꺼내짐
OEPN되었던 커서는 CLOSE되어야 다른 프로세스가 접근할 수 있음.
LOOP의 EXIT WHEN 조건은 조건이 만족될 때 빠져나옴(자바의 BREAK와 비슷)
FOR의 조건은 조건이 만족될 때 반복
커서사용예)2005년 6월 매입상품별 2005년도 판매현황을 조회하시오
--최종적으로 구해야하는 것 : 판매현황 / 매입자료에서 가져와야 할 자료는 뭘까 생각해보기
--커서에서 뭘 출력해볼까, 어떤 커서에서 가져올 것인가 생각 => 제품코드(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; --매개변수 없으니까 OPEN만 시키면 됨.
LOOP
FETCH CUR_BUYPROD INTO V_PID;
--커서가 산출해주는 자료: BUY_PROD(열 1개)를 INTO다음의 변수에 대입(상품코드를 V_PID에 대입)
EXIT WHEN CUR_BUYPROD%NOTFOUND; --이 커서에 더 이상 데이터가 없으면 LOOP를 빠져나가라
SELECT SUM(CART_QTY),SUM(CART_QTY*PROD_PRICE)
INTO V_CNT,V_SUM
FROM CART,PROD
WHERE CART_NO LIKE '2005%' --날짜가 2005년도 6월에 판매된 제품중
AND CART_PROD=V_PID
--CART_PROD가 현재 커서가 가리키는 상품코드와 같은 제품을 찾아라(단가 꺼내오려고)
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 '시도입력 : '--사용자에게 입력받은 값이 P_CITY에 배정됨
DECLARE
CURSOR CUR_MEM(P_SIDO MEMBER.MEM_ADD1%TYPE) IS --P_SIDO는 MEM_ADD1과 같은 타입으로 변수선언
SELECT MEM_ID,MEM_NAME
FROM MEMBER
WHERE SUBSTR(MEM_ADD1,1,2)=SUBSTR(P_SIDO,1,2);
--입력받은 P_SIDO의 앞 두글자와 주소의 앞 두글자가 같은 사람의
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'); --OPEN커서로 받아서 커서로 전달 / 입력한 시도가 꺼내져 옴
LOOP
FETCH CUR_MEM INTO V_MID,V_MNAME;
--커서에 의해 산출된 내용(회원번호,이름)을 변수 V_MID와 V_MNAME에 저장
EXIT WHEN CUR_MEM%NOTFOUND;
SELECT MEM_ADD1||' '||MEM_ADD2 INTO V_ADDR
--MEM_ADD1와 MEM_ADD2를 합친 것을 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; --더 이상 데이터가 없으면 CLOSE
END; 2)WHILE문
. 개발언어의 WHILE문과 같은 기능 제공
. 반복 수행전 조건을 체크하여 반복 수행여부 결정 --조건문의 결과가 false면 수행X
(사용형식)
WHILE 조건 LOOP
반복처리문(들);
:
END LOOP;
. 조건이 만족하면(true) 반복 수행
사용예) 구구단의 7단을 출력
DECLARE
V_CNT NUMBER:=1;
BEGIN
WHILE V_CNT<=9 LOOP
DBMS_OUTPUT.PUT_LINE(7||''||V_CNT||'='||7V_CNT);
V_CNT:=V_CNT+1;
END LOOP;
END;사용예)2005년 금액을 기준으로 가장 많은 상품을 구매한 회원 5명의 회원명,주소를 출력하는 익명블록을 작성하시오
--커서로 만들 부분 : 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;
(익명블록)
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--결과가 있으면 돌아야 하니까 FOUND
/ FETCH를 해야 자료가 있는지 없는지 검증됨
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; --WHILE문에는 FETCH가 두 번 나와야 함.
END LOOP;
CLOSE CUR_CART01;
END; 3)FOR 문
. 반복횟수를 정확히 알고 있거나 반복횟수가 중요한 경우 사용
(일반적 FOR문 사용형식) --1씩 증감만 있음, 제어변수는 선언할 필요 없음
FOR 제어변수 IN [REVERSE] 초기값..최종값 LOOP --역순출력(REVERSE)
반복처리문(들);
:
END LOOP;
사용예)구구단의 7단을 출력
DECLARE
BEGIN
FOR I IN 1..9 LOOP --1부터 9까지 1씩증가로 I에 배정됨
DBMS_OUTPUT.PUT_LINE(7||''||I||'='||7I);
END LOOP;
END;
(역순)
DECLARE
BEGIN
FOR I IN REVERSE 1..9 LOOP --1부터 9까지 1씩증가로 I에 배정됨
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문이 불필요
(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--결과가 있으면 돌아야 하니까 FOUND / FETCH를 해야 자료가 있는지 없는지 검증됨
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; --WHILE문에는 FETCH가 두 번 나와야 함.
END LOOP;
CLOSE CUR_CART01;
END;
(FOR 문)
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 --FOR문 사용, OPEN,FETCH,CLOSE 필요없음
SELECT MEM_NAME,MEM_ADD1||' '||MEM_ADD2
INTO V_MNAME,V_ADDR
FROM MEMBER
WHERE MEM_ID=REC.CID; --CID는 커서에 들어있는 A.CART_MEMBER
DBMS_OUTPUT.PUT_LINE(REC.CID||' '||V_MNAME||' '||V_ADDR);
END LOOP;
END;
(FOR 문)
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; --CID는 커서에 들어있는 A.CART_MEMBER
DBMS_OUTPUT.PUT_LINE(REC.CID||' '||V_MNAME||' '||V_ADDR);
END LOOP;
END;Stored Procedure(저장 프로시져 : Procedure)
- 미리 컴파일된 형태로 저장
- 모든 사용자 간 처리 루틴 공유:캡슐화, 일관성 제공
- 데이터베이스 내부 구조에 대한 보안 기능 제공
- 서버데이터 보호 기능 제공
- 반환 값이 없음
(사용형식)
CREATE [OR REPLACE] PROCEDURE 프로시져명[( --매개변수 있으면 ()있어야하고 없으면 없어도 됨.
변수명 모드 타입명 [:=|DEFAULT 값,] --값 지정 가능: 사용자가 매개변수를 =으로 만들어놓는 것
:
변수명 모드 타입명 [:=|DEFAULT 값,])]
IS|AS --익명블록의 DECLARE와 같음
선언부
BEGIN
실행부 [EXCEPTION
예외처리;
]
END;
. '모드' : 매개변수의 역할 선언으로 IN, OUT, INOUT 중 하나 기술
. '타입명' : 크기를 지정해서는 안됨, 타입명만 써야함
(실행)
EXEC|EXECUTE 프로시져명[(매개변수list)];
