[49일차][오라클] 반복문, 커서, 프로시저

반복문

BASIC LOOP문

LOOP
	statement1;
    statement2;
    EXIT [WHEN condition];
END LOOP;

 

FOR LOOP문

- 반복횟수가 정해진 반복문

- 사용되는 인덱스는 정수로 자동 선언되며 자동덕으로 1씩 증가 또는 감소 한다. REVERSE는 1씩 감

FOR index_counter IN[REVERSE] lower_bound..upper_bounc LOOP
	statement1,
    statement2;
END LOOP;

예제

DECLARE
	vdepartments departments%ROWTYPE;
BEGIN
	DBMS_OUTPUT.PUT_LINE('부서번호 / 부서명 / 지역명');
    DBMS_OUTPUT.PUT_LINE('------------------------------');
    -- 변수cnt는 1부터 1씩 증가하다가 27에 도달하면 반복문에서 벗어난다
    FOR cnt IN 1..27 LOOP
    	select * into vdepartments from departments
        where department_id = 10*cnt;
        DBMS_OUTPUT.PUT_LINE(vdepartments.department_id || '/' || vdepartments.department_name|| '/' || vdepartments.location_id);
	END LOOP;        
END;
/

WHILE LOOP문

WHILE 조건문 LOOP
	statement1;
    statement2;
END LOOP;

예제)

---

커서

오라클 서버에서는 SQL문을 실행할 때마다 처리(Parse,Excution)를 위한 메모리 공간(커서)을 사용한다.

커서란 특정 SQL문장을 처리한 결과를 담고 있는 영역을 가리키는 일종의 포인터로 커서를 사용하면 처리된 SQL문장의 결과 집합에 접근할 수 있다. 

커서는 커서열기 - 패치- 커서닫기 3단계로 진행된다

- 묵시적 커서: 오라클 내부애서 자동으로 생성

- 명시적 커서: 사용자가 직접정의 해서 사용

명시적 커서

DECLARE --선언부
	CURSOR cursor_name IS statement; -- 커서 선언
BEGIN
	OPEN cusor_name; -- 커서 열기
    
    --커서로 부터 데이터를 읽어와 변수에 저장
    FECTCH cursor_name INTO variable_name;
    CLOSE cusor_name;  --커서 닫기
END;

DECLARE CURSOR(커서 선언)

사용할 커서를 선언부에 직접 정의해야한다. 사용할 커서에 이름을 부여하고 이 커서에 대한 쿼리를 선언해야한다.

CURSOR 커서명 IS 커서에 담고 싶은 내용을 가져오는 서브쿼리;
CURSOR C1 IS SELECT * FROM DEPARTMENTS;

 

OPEN CURSOR(커서열기)

질의를 수행하고 검색 조건을 충족하는 모든 행으로 구성된 결과 셋을 생성하기위해 CURSOR를 OPEN한다

OPEN 커서명;
OPEN C1;

 

FETCH CURSOR(패치 단계에서 커서 사용)

정의한 커서를 열고 난 후에야 SELECT문의결과로 반환되는 로우에 접근할 수 있다. 

결과 집합의 로수 수는 보통 1개 이상이므로 전체 로우에 접근하기 위래서는 반복문이 사용된다.

- FETCH문은 결과 셋에서 로우단위로 데이터를 읽어들임

- FETCH 후에 CURSOR는 결과 셋에서 다음 행으로 이동

FETCH 커서명 INTO 변수들
INTO{variable1[,variable2,...]}

LOOP
	FETCH C1 INTO vdepartments.department_id, vdepartments.department_name,
    vdepartments.location_id;
    EXIT WHEN C1%NOTFOUND;
END LOOP;

-- 커서로 결과 가져오기
DECLARE
    vdepartments departments%ROWTYPE;
    CURSOR C1 --커서 이름
    IS
    SELECT department_id,department_name, location_id
    FROM departments; -- 부서 테이블의 전체 내용을 조회
    
BEGIN
    DBMS_OUTPUT.PUT_LINE('부서번호 / 부서명 / 지역명');
    DBMS_OUTPUT.PUT_LINE('-----------------------------');
    
    OPEN C1;
    --오픈한 C1 커서가 SELECT문에 의해 검색된 한개의 행의 정보를 읽어온다
        LOOP -- 읽어온 정보를 INTO뒤에 기술한 변수에 저장
            FETCH C1 INTO vdepartments.department_id, vdepartments.department_name,vdepartments.location_id;
            EXIT WHEN C1%NOTFOUND;
            DBMS_OUTPUT.PUT_LINE(vdepartments.department_id || ' ' || 
            RPAD (vdepartments.department_name,20) || ' ' || vdepartments.department_id);
        END LOOP;
        DBMS_OUTPUT.PUT_LINE(CONCAT('읽어드린 레코드 수: ',C1%ROWCOUNT));
    CLOSE C1;

END;
/

 

CLOSE CURSOR(커서닫기)

CLOSE문장은 CURSOR를 사용할 수 없게 하고 결과 셋의 정의를 해제한다.

 

 

 

 

묵시적 커서

LOOP애서 각 반복마다 CURSOR를 열고 행을 인출(FETCH)하고 모든 행이 처리되면 자동으로 CURSOR가 CLOE되므로 사용하기 편리하다.

 

FOR record_name IN cursor_name LOOP
--명시적 커서의 OPEN,FETCH가 자동적으로 수행됨
	statement1;
    statement2;
END LOOP --루프문을 빠져 나갈 때 자동적으로 커서가 close됨

-- 묵시적 커서 형식1
DECLARE
    vdepartments departments%ROWTYPE;
    CURSOR C1
    IS
    SELECT *FROM departments;
BEGIN
    DBMS_OUTPUT.PUT_LINE('부서번호 / 부서명 / 지역명');
    DBMS_OUTPUT.PUT_LINE('-----------------------------'); 
    FOR vdepartments IN C1 LOOP
         DBMS_OUTPUT.PUT_LINE(vdepartments.department_id || ' ' || 
         RPAD (vdepartments.department_name,20) || ' ' || vdepartments.department_id); 
    END LOOP;
END;
/

-- 묵시적 커서 형식2
DECLARE
    vdepartments departments%ROWTYPE;
BEGIN
    DBMS_OUTPUT.PUT_LINE('부서번호 / 부서명 / 지역명');
    DBMS_OUTPUT.PUT_LINE('-----------------------------'); 
    FOR vdepartments IN (SELECT * FROM departments) LOOP --커서 정의부분을 for문에서 직접사용
         DBMS_OUTPUT.PUT_LINE(vdepartments.department_id || ' ' || 
         RPAD (vdepartments.department_name,20) || ' ' || vdepartments.department_id); 
    END LOOP;
END;
/

커서 예제

DECLARE
    vrandomid employees.department_id%Type; -- 임의의 부서번호
    vsalarystring VARCHAR2(20); -- 급여등급
    -- 커서 선언시 매개변수를 명시할 수 있다. CURSOR커서명(매개변수) IS 쿼리문
    CURSOR cur_employees(vdepartment_id employees.department_id%TYPE)
    IS
    SELECT salary, first_name FROM employees WHERE department_id = vdepartment_id;
BEGIN
    -- 임의의 부서번호를 얻어 출력
    SELECT ROUND (DBMS_RANDOM.VALUE(10,270),-1) INTO vrandomid FROM DUAL;
    DBMS_OUTPUT.PUT_LINE('부서번호: '||vrandomid);
    
    -- 그 부서번호가 120에서 270번까지 소속된 사원이 없기에 제어
    IF vrandomid between 120 and 270 THEN
        DBMS_OUTPUT.PUT_LINE(vrandomid || ' 부서에 해당사원이 없습니다.');
        RETURN; -- 블록 종료
    END IF;    
    
    DBMS_OUTPUT.PUT_LINE('사원명 / 급여 / 급여수준');
    DBMS_OUTPUT.PUT_LINE('-------------------------------');
    
    FOR vemployees IN cur_employees(vrandomid) LOOP
        IF vemployees.salary BETWEEN 1 AND 6000 THEN
            vsalarystring := '낮음';
        ELSIF vemployees.salary BETWEEN 6001 AND 10000 THEN
            vsalarystring := '중간';
        ELSIF vemployees.salary BETWEEN 10001 AND 20000 THEN
            vsalarystring := '높음';
        ELSE 
            vsalarystring := '최상위';
        END IF;
        DBMS_OUTPUT.PUT_LINE(RPAD(vemployees.first_name,6) || '/' || RPAD(vemployees.salary,5)|| '/' || vsalarystring);
    END LOOP;
END;
/

 

커서변수

한 개 이상의 쿼리를 연결해 사용 할 수 있으며, 변수 처럼 커서 변수를 함수나 프로시저의 매개변수로 전달 할 수 있다.

 

커서변수 선언 

TYPE 커서_타입명 IS REF CURSOR; --커서 타입선언(생략가능)
커서_변수명 커서_타입명; --커서변수 선언

오라클에서 제공하는 커서타입인 SYS_REFCURSOR란 타입을 사용한다.

SYS_REFCURSOR를  사용할 때는 별도로 커서 타입을 선언할 필요없이 다음과 같이 커서 변수만 선언하면 된다.

커서변수 SYS_REFCUSOR: --커서변수 선언

 

커서변수 사용

1. 커서 변수와 커서 정의 쿼리문 연결

커서를 정의하는 쿼리가 있어야하는데 커서변수와 쿼리문을 연결할 때 다음과 같이 OPEN...FOR구문을 사용한다.

OPEN 커서변수명 FOR SELECT문;

2. 커서 변수에서 결과 집합 가져오기

커서 변수에 결과 집합을 가져오는 패치 작업으로 FETCH문을 사용한다.

FETCH 커서변수명 INTO 변수1,변수2,....;

예제)

DECLARE
    vfirst_name employees.first_name%TYPE;
    --TYPE employeescursor IS REF CURSOR; --커서 타입 선언
    --vemployees employeescurcor; --커서 변수선언
    vemployees SYS_REFCURSOR; --오라클 서버가 제공하는 커서 타입
BEGIN
    --커서 변수를 사용한 커서 정의 및 오픈
    OPEN vemployees FOR SELECT first_name FROM employees WHERE department_id =90;
    
    --Loop문
    Loop    
        --커서 변수를 사용해 결과 집합을 EMPNAME 변수에 할당
            FETCH vemployees INTO vfirst_name;
            EXIT WHEN vemployees%NOTFOUND;
            DBMS_OUTPUT.PUT_LINE('사원명: '|| vfirst_name);-- 사원명 출력
    END LOOP;
END;
/

예제) 임의의 문자 하나를 얻어 그 문자가 포함되 사원명으로 사원번호, 이름,급여를 출력

DECLARE
   vword VARCHAR2(1);
   vemployees employees%ROWTYPE; 
    CURSOR C1 (vword VARCHAR2)
   IS
   SELECT employee_id, first_name,salary
   FROM employees
   WHERE LOWER(first_name) LIKE '%' || vword || '%';
  
BEGIN
    -- 임의의 문자 변수저장
    SELECT DBMS_RANDOM.STRING('L',1) INTO vword FROM DUAL;   
    DBMS_OUTPUT.PUT_LINE('임의의 문자: '||vword);
    
    --명시적
    OPEN C1(vword);
    
    LOOP
        FETCH C1 INTO vemployees.employee_id,vemployees.first_name,vemployees.salary;
        EXIT WHEN C1%NOTFOUND;
        --출력코드
        DBMS_OUTPUT.PUT_LINE(vemployees.first_name);
    END LOOP;

END;
/

---

프로시저

저장 프로시저

저장프로지서는 자주 사용되는 쿼리문을 모듈화시켜서 필요할때 마다 호출하여 사용

 

프로시저 생성

CREATE [OR REPLACE] PROCEDURE procesure_name -- or replace:먼저 생성된 프로시저를 변경
(	
	매개변수1[in/out/in out] 자료형, --mode에 따라 프로시저를 호출시 매개변수값을 받아 사용
    매개변수2[in/out/in out] 자료형....
)
IS
 	local_variable declaration
BEGIN
 	statement1;
END[procedure_name];

프로시저 실행

EXEC 프로시저명;
EXEC EMPPROC;

 

매개변수

매개변수는 프로시저 이름 뒤에 ()를 기술하여 그 내부에 매개변수를 정의한다.

형식은 변수명 모드 자료형으로 기술한다.

IN 모드	OUT 모드	IN OUT 모드
기본모드	명시적으로 지정해야한다	명시적으로 지정해야 한다
값이 서브 프로그램에 전달됨	값이 호출환경에 반환됨	값이 서브 프로그램에도 전달되고 호출환경에도 반환됨
실제 파라미터가 리터럴, 표현식, 상수 또는 초기화된 변수가 될 수 있다.	변수만 사용 가능	변수만 사용 가능
기본값을 할당할 수 있음	기본값 할당 불가	기본값 할당 불가

IN MODE 매개변수

실행환경에서 서브 프로시저에 값을 전달한다.