루프문 – 반복문(LOOP, WHILE, FOR)은 커서를 사용하기 위한 것입니다.
– 반복문 등의 기능을 개발언어로 제공
– loop, while, for 문 제공 – while, for 조건 평가 기준 있음 (조건 충족 시 반복 실행)
–loop는 사용자에게 기준 자체를 설명하라는 메시지를 표시합니다.
– 대부분의 루프 문의 목적은 커서를 사용하는 것입니다.
1. 루프문
– 루프 문의 기본 구조 제공
– 무한 루프 기능
(사용 유형)
리본
반복문;
(조건 EXIT WHEN);–조건이 참이면 루프가 종료됩니다(while과 다름).
(반복되는 진술;)
루프 끝;
. ‘EXIT WHEN 조건’ : ‘조건’이 참이면 루프를 종료합니다.
사용예) 구구단의 6단을 구현하시오.
DECLARE
V_CNT NUMBER:=1; --1~9사시의 수 만들어줌
BEGIN
LOOP
EXIT WHEN V_CNT>9; --9보다 커지면 반복문을 벗어남
DBMS_OUTPUT.PUT_LINE('6 * '||V_CNT||' = '||6*V_CNT);
V_CNT:=V_CNT+1;--1개 더해진 값으로 LOOP으로 다시 돌아감
END LOOP;
END;** 커서
– SELECT 문의 결과 집합
1) 암시적 커서 – 이름 없음(생성, 열기, 패치, 닫기) -> 자동 처리(결과가 바로 출력되므로 접근 불가)
. CURSOR문이 없는 SELECT문의 결과 세트
. 이름이 없기 때문에 재사용할 수 없거나 개발자가 커서 내부에 액세스할 수 없습니다.
(결과 출력시 OPEN, 모든 결과 출력시 즉시 CLOSE)
. 커서속성
-----------------------------------------------------------------
속성 의 미--이름이 없으니까 sql을 씀
-----------------------------------------------------------------
SQL%ISOPEN 커서가 OPEN되었으면 참(true)반환 묵시적 커서는
항상 false 임--항상 거짓임
SQL%NOTFOUND 커서 내에 읽어올(fetch)자료가 있으면 거짓(false)--없지 않으니 자료가 있음
커서 내에 읽어올(fetch)자료가 없으면 참(true)반환--loop
SQL%FOUND 커서 내에 읽어올(fetch)자료가 있으면 참(true)반환
커서 내에 읽어올(fetch)자료가 없으면 거짓(false)반환--while
SQL%ROWCOUNT 커서내의 자료의 수(행의 수) 반환--행 수 반환해줘
-----------------------------------------------------------------
--select문에 의해 나온 결과물을 뷰와 커서라고 한다
2) 명시적 커서
. 특정 이름으로 선언 섹션에 정의된 커서
(사용형식)
CURSOR 커서명((매개변수list)) IS
SELECT 문;--선언부에 기술되어야함
- '매개변수list' : 호출문에 전달해준 커서의 SELECT문에서 사용할 자료의 전달 통로
타입명 변수명(, 타입명 변수명,...)형식으로 사용하며--타입명만 써야해(크기는 제외)
타입명에 크기를 기술하지 않음
- 커서 선언문은 DECLARE ~ BEGIN 사이(선언부)에 기술
- 일반적 커서의 사용은 커서선언 -> OPEN -> FETCH -> CLOSE 순으로--패치는 반복문 안에 기술
--선언제외하고 나머지는 BEGIN문 안에 있어야한다
사용해야함
사용예)부서번호를 전달받아 해당부서의 직원정보를 출력하는 커서를 작성
Alias는 사원번호, 사원명, 입사일이며 입사일 순으로 출력하시오
DECLARE
V_EID HR.EMPLOYEES.EMPLOYEE_ID%TYPE;
V_NAME HR.EMPLOYEES.EMP_NAME%TYPE;
--참조형변수 : 테이블, 컬럼명을 알고 있는 것을 변수에 넣을 때(크기는 몰라)
V_HDATE DATE;
CURSOR CUR_EMP01(HR.DEPARTMENTS.DEPARTMENT_ID%TYPE P_DID) IS
SELECT EMPLOYEE_ID,EMP_NAME,HIRE_DATE
FROM HR.EMPLOYEES
WHERE DEPARTMENT_ID=P_DID
ORDER BY 3;--여기까지가 커서
BEGIN
END;
3) OPEN 문
. 생성된 커서를 사용하려면 OPEN해야 함 – 입력하려면 먼저 OPEN해야 함
. OPEN 문은 실행 영역(BEGIN 블록)에 작성됩니다. 실행 영역에 한 번만 작성하면 됩니다.
(사용형식)
OPEN 커서명((매개변수list))
- '(매개변수list)':커서 선언문의 매개변수에 전달될 값 기술
DECLARE
V_EID HR.EMPLOYEES.EMPLOYEE_ID%TYPE;
V_NAME HR.EMPLOYEES.EMP_NAME%TYPE;
--참조형변수 : 테이블, 컬럼명을 알고 있는 것을 변수에 넣을 때(크기는 몰라)
V_HDATE DATE;
CURSOR CUR_EMP01(HR.DEPARTMENTS.DEPARTMENT_ID%TYPE P_DID) IS
SELECT EMPLOYEE_ID,EMP_NAME,HIRE_DATE
FROM HR.EMPLOYEES
WHERE DEPARTMENT_ID=P_DID
ORDER BY 3;--여기까지가 커서
BEGIN
OPEN CUR_EMP01(50);--P_DID에 전달됨(50이)
END;
4) FETCH 문
. 커서 안의 데이터를 한 줄씩 읽어오는 명령어 – 읽기 개념이 없으므로 한 줄 전체를 가져옴
. 일반적으로 반복문으로 기술
(사용 유형)
변수 목록에서 커서 이름 가져오기;
– INTO 이후 커서를 형성하는 SELECT 문의 SELECT 절에 기술된 컬럼 값을 입력한다.
설명된 변수에 순차적으로 저장
– 커서를 구성하는 SELECT 문의 SELECT 절에 기술된 열의 개수, 순서, 종류
변수의 개수, 순서, 종류가 동일해야 함 – 선택한 문서를 가져오는 중이므로 개수가 동일해야 함
5) CLOSE문
. 사용이 종료된 커서는 반드시 CLOSE되어야함
. CLOSE되지 않은 커서는 다시 OPEN될 수 없음
(사용형식)
CLOSE 커서명;
DECLARE
V_EID HR.EMPLOYEES.EMPLOYEE_ID%TYPE;
V_NAME HR.EMPLOYEES.EMP_NAME%TYPE;
--참조형변수 : 테이블, 컬럼명을 알고 있는 것을 변수에 넣을 때(크기는 몰라)
V_HDATE DATE;
CURSOR CUR_EMP01(P_DID HR.DEPARTMENTS.DEPARTMENT_ID%TYPE) IS--변수명이 앞으로 와야한다
--부서번호를 전달받는다
SELECT EMPLOYEE_ID,EMP_NAME,HIRE_DATE
FROM HR.EMPLOYEES
WHERE DEPARTMENT_ID=P_DID
ORDER BY 3;--여기까지가 커서
BEGIN
OPEN CUR_EMP01(50);--P_DID에 전달됨(50이)
LOOP
FETCH CUR_EMP01 INTO V_EID,V_NAME,V_HDATE;--읽어올 내용
EXIT WHEN CUR_EMP01%NOTFOUND;--데이터가 존재하지 않는다면 빠져나가라
DBMS_OUTPUT.PUT_LINE('사원번호 : '||V_EID);
DBMS_OUTPUT.PUT_LINE('사원명 : '||V_NAME);
DBMS_OUTPUT.PUT_LINE('입사일 : '||V_HDATE);
DBMS_OUTPUT.PUT_LINE('--------------------------------');
END LOOP;
DBMS_OUTPUT.PUT_LINE('사원 수 : '||CUR_EMP01%ROWCOUNT);--반복이 아니니까 반복문 밖에서 사원수 기술
CLOSE CUR_EMP01;--반복문 종료
END;
사용예)마일리지가 많은 5명의 2020년 상반기 구매현황을 조회하시오.
Alias는 회원번호,회원명,마일리지,구매금액합계
1)커서 : 마일리지가 많은 5명의 회원번호,회원명,마일리지를 조회
SELECT A.MEM_ID, A.MEM_NAME, A.MEM_MILEAGE
FROM (SELECT MEM_ID,MEM_NAME,MEM_MILEAGE
FROM MEMBER
ORDER BY 3 DESC) A
WHERE ROWNUM <= 5;
DECLARE
V_MID MEMBER.MEM_ID%TYPE;
V_NAME MEMBER.MEM_NAME%TYPE;
V_MILE NUMBER:=0; --마일리지
V_SUM NUMBER:=0; --구매금액합계
CURSOR CUR_CART01 IS--커서가 먼저 실행(밑에 나온 실행문 만드는 과정)
SELECT A.MEM_ID, A.MEM_NAME, A.MEM_MILEAGE
FROM (SELECT MEM_ID,MEM_NAME,MEM_MILEAGE
FROM MEMBER
ORDER BY 3 DESC) A
WHERE ROWNUM <= 5;
BEGIN
OPEN CUR_CART01;--문 열어보자
LOOP
FETCH CUR_CART01 INTO V_MID,V_NAME,V_MILE;--커서에서 구한 내용이 여기서 들어감(첫줄 읽어봐)
EXIT WHEN CUR_CART01%NOTFOUND;--더이상 없으면 빠져나가라
--구매금액합계
SELECT SUM(A.CART_QTY*B.PROD_PRICE) INTO V_SUM
FROM CART A, PROD B
WHERE A.CART_PROD=B.PROD_ID
AND SUBSTR(A.CART_NO,1,6) BETWEEN '202001' AND '202006'
AND A.CART_MEMBER=V_MID;--식별자 번호 같은 것끼리 뽑아라
--SELECT절을 여기에 쓰려면 선언 변수와 관계도 밝히기
--출력
DBMS_OUTPUT.PUT_LINE(V_MID||' '||TRIM(V_NAME)||' '||--TRIM 앞뒤 공백제거
TO_CHAR(V_MILE,'999,999')||TO_CHAR(V_SUM,'99,999,999'));
DBMS_OUTPUT.PUT_LINE('--------------------------------------');
END LOOP;
CLOSE CUR_CART01;
END;
-----------기본단계
BEGIN
OPEN
LOOP
FETCH
END LOOP;
CLOSE
END;
--------------
2. WHILE 문
– 개발언어에서 WHILE문과 동일한 목적 및 기능 제공
(사용 유형)
WHILE 조건 루프
반복문;
루프 끝;
. ‘조건’: 조건이 참이면 반복
사용예)구구단의 6단을 출력하시오
DECLARE
V_CNT NUMBER:=0;
BEGIN
WHILE V_CNT<9 LOOP
V_CNT:=V_CNT+1;
DBMS_OUTPUT.PUT_LINE('6 * '||V_CNT||' = '||6*V_CNT);
END LOOP;
END;
사용예)회원의 직업을 매개변수로 입력 받아 회원이름과 마일리지를 출력하는
커서를 작성하시오
--1.변수를 먼저 생각하기(최종 출력물)
DECLARE
V_NAME MEMBER.MEM_NAME%TYPE;
V_MILE NUMBER:=0;
CURSOR CUR_MEM01(P_JOB MEMBER.MEM_JOB%TYPE) IS--여기에 입력받은 직업을 넣는다
SELECT MEM_NAME,MEM_MILEAGE
FROM MEMBER
WHERE MEM_JOB=P_JOB;
BEGIN
OPEN CUR_MEM01('학생');--여기에 입력한 값이 위에 전달된다
FETCH CUR_MEM01 INTO V_NAME,V_MILE;
--여기에만 하면 참이라 첫번째만 무한반복(WHILE문 밖에도 존재해야해)
WHILE CUR_MEM01%FOUND LOOP--커서에 남은 자료가 있다면 실행하라
DBMS_OUTPUT.PUT_LINE(V_NAME||TO_CHAR(V_MILE,'99,999'));
FETCH CUR_MEM01 INTO V_NAME,V_MILE;
--그래서 여기에도 해놔야 한다(다음거 읽어봐/WHILE문 안에도 있어야해)
END LOOP;
END;
(커서 : 회원의 직업을 매개변수로 입력 받아 회원이름과 마일리지를 출력)
SELECT MEM_NAME,MEM_MILEAGE
FROM MEMBER
WHERE MEM_JOB=P_JOB;
----여기까지가 커서
3. FOR 문 – 실행할 횟수를 알고 있는 경우/커서와 잘 일치하는 경우
– 실행 횟수를 알고 있거나 중요할 때 사용
– FOR 문으로 커서 실행을 위해 OPEN, FETCH 및 CLOSE 문을 생략합니다.
(FOR 문의 일반적인 사용법)
FOR index IN (REVERSE) START VALUE…END VALUE LOOP–REVERSE는 그 반대입니다(시작 값과 종료 값을 변경할 필요가 없습니다).
진술을 반복하다;
루프 끝;
. 인덱스는 시스템에서 자동으로 선언하는 제어 변수입니다.
. 인덱스에 “시작 값”이 할당된 후 “종료 값”보다 작거나 같으면 반복됩니다.
. 인덱스 값은 자동으로 1 증가(감소: REVERSE)
(커서 FOR 문 형식)
FOR 레코드 이름 IN 커서|SELECT 문 LOOP–레코드 이름 = 인덱스 이름
진술을 반복하다;
루프 끝;
. 레코드 이름은 시스템에서 자동으로 선언한 제어 변수입니다.
. 커서의 첫 번째 행에서 마지막 행까지 순차적으로 레코드 이름에 위치 값 지정
. OPEN, FETCH 및 CLOSE 문을 생략하십시오. 사용하지 않더라도 자동으로 사용됩니다.
. 커서의 열을 참조하려면: ‘recordname.columnname of cursorSELECT 문의 열 이름’
.’cursor|SELECT 문’: 선언 섹션에서 커서를 선언하거나 IN 절에서 IN-LINE 하위 쿼리를 사용합니다.
형식으로 지정할 수 있습니다.
사용예)구구단의 6단을 FOR문을 이용하여 출력하시오
DECLARE
BEGIN
FOR I IN 1..9 LOOP
DBMS_OUTPUT.PUT_LINE('6 * '||I||' = '||6*I);
END LOOP;
END;
DECLARE
BEGIN
FOR I IN 1..9 LOOP
DBMS_OUTPUT.PUT_LINE('3 * '||I||' = '||3*I);
END LOOP;
END;
사용예)직업이 '주부'인 회원이름과 마일리지를 출력하는 커서를 작성하시오
--1.변수를 먼저 생각하기(최종 출력물)
DECLARE
CURSOR CUR_MEM01 IS--FOR문은 입력받지 않는다
SELECT MEM_NAME,MEM_MILEAGE
FROM MEMBER
WHERE MEM_JOB='주부';
BEGIN
FOR REC IN CUR_MEM01 LOOP
DBMS_OUTPUT.PUT_LINE(REC.MEM_NAME||TO_CHAR(REC.MEM_MILEAGE,'99,999'));
END LOOP;
END;
(in-line SUBQUERY 사용)
DECLARE
BEGIN
FOR REC IN (SELECT MEM_NAME,MEM_MILEAGE
FROM MEMBER
WHERE MEM_JOB='주부')
LOOP
DBMS_OUTPUT.PUT_LINE(REC.MEM_NAME||TO_CHAR(REC.MEM_MILEAGE,'99,999'));0
END LOOP;
END;