10번 인서트
DECLARE
V_CNT NUMBER(2) := 0;
BEGIN
WHILE V_CNT <= 9 --조건이 참이면 계속 LOOP
LOOP
V_CNT := V_CNT +1;
INSERT INTO TBL_LOOPTEST
VALUES(V_CNT, 'KH' || V_CNT);
END LOOP;
END;
ROLLBACK;
SELECT * FROM TBL_LOOPTEST;
---------------------------------------------------------------------------------------------------
기본 LOOP과제 WHILE LOOP로 바꾸기
CREATE OR REPLACE PROCEDURE
P_HAP(V_START NUMBER
,V_END NUMBER
,V_FLAG NUMBER)
IS
V_CNT NUMBER;
V_SUM NUMBER := 0;
V_HOLJAK VARCHAR2(4);
BEGIN
IF V_FLAG NOT IN (0,1) THEN
DBMS_OUTPUT.PUT_LINE(
'세번째 인자값은 0(짝수), 1(홀수)만 가능합니다.'
);
ELSE
IF
( V_FLAG = 0 AND MOD(V_START,2) = 0 ) OR
( V_FLAG = 1 AND MOD(V_START,2) != 0 )
THEN
V_CNT := V_START;
ELSE
V_CNT := V_START + 1;
END IF;
WHILE V_CNT <= V_END
LOOP
V_SUM := V_SUM + V_CNT;
V_CNT := V_CNT + 2;
END LOOP;
IF V_FLAG = 0 THEN V_HOLJAK := '짝수';
ELSE V_HOLJAK := '홀수';
END IF;
DBMS_OUTPUT.PUT_LINE(
V_START || '부터 ' ||
V_END || '까지 ' ||
V_HOLJAK || '의 합은 ' ||
V_SUM || ' 입니다.');
END IF;
END;
---------------------------------------------------------------------------------------------------
WHILE문
일반적인 프로그래밍 언어에서 대표적인 반복문을 꼽으라면 WHILE문과 FOR문을 들 수 있다. 오라클에서도 역시 이 두 문장을 제공하는데, 먼저 WHILE문에 대해 살펴 보자.
WHILE 조건 LOOP 처리문; END LOOP;WHILE문은 LOOP문과 비슷하지만 WHILE 다음에 조건을 붙여 해당 조건에 만족할 때만 루프를 돌면서 로직을 처리한다. WHILE문을 사용해 구구단 3단을 출력해 보자.
입력
DECLARE vn_base_num NUMBER := 3; vn_cnt NUMBER := 1; BEGIN WHILE vn_cnt <= 9 -- vn_cnt가 9보다 작거나 같을 때만 반복 처리 LOOP DBMS_OUTPUT.PUT_LINE (vn_base_num || '*' || vn_cnt || '= ' || vn_base_num * vn_cnt); vn_cnt := vn_cnt + 1; -- vn_cnt 값을 1씩 증가 END LOOP; END;결과
3*1= 3 3*2= 6 3*3= 9 3*4= 12 3*5= 15 3*6= 18 3*7= 21 3*8= 24 3*9= 27WHILE 다음에 조건 처리를 한 부분만 제외하면 다른 부분은 LOOP문과 같지만, LOOP문과 달리 vn_cnt 값이 9보다 작거나 같은 경우라는 조건을 주었다는 점을 눈여겨 보자. LOOP문에서는 루프를 빠져 나가는 조건을 주었지만, WHILE문에서는 루프를 수행하는 조건을 준 것이다.
WHILE문에서도 루프를 돌다가 특정 조건에 부합하면 EXIT를 써서 루프를 빠져나올 수 있다.
입력
DECLARE vn_base_num NUMBER := 3; vn_cnt NUMBER := 1; BEGIN WHILE vn_cnt <= 9 -- vn_cnt가 9보다 작거나 같을 때만 반복 처리 LOOP DBMS_OUTPUT.PUT_LINE (vn_base_num || '*' || vn_cnt || '= ' || vn_base_num * vn_cnt); EXIT WHEN vn_cnt = 5; -- vn_cnt 값이 5가 되면 루프 종료 vn_cnt := vn_cnt + 1; -- vn_cnt 값을 1씩 증가 END LOOP; END;결과
3*1= 3 3*2= 6 3*3= 9 3*4= 12 3*5= 15신간 소식 구독하기
뉴스레터에 가입하시고 이메일로 신간 소식을 받아 보세요.
- 오라클 명령에 영향받은 행들의 집합
- 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;사용예) 거주지 도시를 키보드로 입력받아 그 거주지에 살고 있는 회원의 정보를 출력하시오
출력할 회원정보는 회원번호, 회원명, 주소이다.
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 문이 불필요