SQL 반복문, 커서

2023. 6. 27. 17:06

반복문: SQL문을 반복작으로 여러번 실행하고자 할 때 사용, PL/SQL에서는 다양한 반복문이 사용

BASIC LOOP: 조건없이 반복 작업
FOR LOOP: COUNT를 기본으로 작업
WHILE LOOP: 조건을 기본으로 작업
EXIT: LOOP를 종료

BASIC LOOP: LOOP에서 END LOOP 사이를 계속 반복해서 실행(무한 루프), 여기서 빠져나가려면 EXIT문을 사용, 조건에 따라 루프를 종료할 수 있도록 WHEN 절을 덧붙일 수 있다.

LOOP
	STATEMENT1;
    	STATEMENT2;
    	EXIT [WHEN CONDITION];
END LOOP;

FOR LOOP: 반복되는 횟수가 정해진 반복문을 처리, 사용되는 인덱스는 정수로 자동 선언, 자동적으로 1씩 증가 또는 감소(REVERSE는 1씩 감소)

FOR INDEX_COUNTER IN [REVERSE] LOWER_BOUND..UPPER_BOUND LOOP
	STATEMENT1,
    	STATEMENT2;
END LOOP;

END FOR가 아니라 END LOOP!

WHILE LOOP: 제어 조건이 TRUE인 동안만 일련의 문장을 반복하기 위해 사용

조건은 반복이 시작될 때 체크하게 되어 LOOP내의 문장이 한번도 수행하지 않을 수 있다.

LOOP을 시작할 때 조건이 FALSE이면 반복 탈출

WHILE 조건문 LOOP
	STATEMENT1;
    	STATEMENT2;
END LOOP;

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

사용자가 요청하는 데이터를 데이터베이스 버퍼 캐쉬에서 커서로 복사해 온 후 커서에서 원하는 데이터를 추출(Fetch) 원하는 작업을 하게 된다. 이 메모리 공간을 Private SQL Area.

특정 SQL문장을 처리한 결과를 담고 있는 영역을 가리키는 일종의 포인터.

커서를 사용하면 처리된 SQL문장의 결과 집합에 접근 가능

묵시적 커서: 오라클 내부에 자동으로 생성하는 커서

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

커서열기(OPEN) - 패치(FETCH) - 커서닫기(CLOSE): 3단계로 진행

명시적 커서 선언 (CURSOR)
명시적 커서 오픈 (OPEN)
커서에서 데이터 추출 (FETCH)
커서 사용 종료 (CLOSE)

DECLARE CURSOR(커서 선언): 사용할 커서를 선언부에 직접 정의, 사용할 커서에 이름을 부여하고 이 커서에 대한 쿼리를 선언

CURSOR 커서명 IS 커서에 담고 싶은 내용을 가져오는 서브쿼리(다중행);

CURSOR A1 IS SELECT * FROM DEPARTMENTS;

OPEN CUSOR(커서 열기) : 질의를 수행하고 검색 조건을 충족하는 모든 행으로 구성된 결과 셋(커서 선언 시 기술했던 서브쿼리를 수행해서 데이터를 커서로 가져오는 과정)을 생성하기 위해 CURSOR를 OPEN.

CURSOR는 결과 셋에서 첫번째 행을 가리킨다.

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 WHER C1%NOTFROUND;
END LOOP;

얻어진 여러개의 행에 대한 결과값을 모두 처리하여면 반복문에 FETCH문을 기술

NOTFOUND: 커서의 상태를 알려주는 속성 중에 하나, 커서 영역의 자료가 모두 FETCH 되었다면 TRUE를 되돌린다.

즉 커서 C1 영역의 자료가 모두 FETCH되면 반복문 탈출

커서의 상태

속성	의미
%NOTFOUND	커서 영역의 자료가 모두 FETCH 되었다면 TRUE
%FOUND	커서 영역에 FETCH 되지 않은 자료가 있다면 TRUE
%ISOPEN	커서가 OPEN된 상태이면 TRUE
%ROWCOUNT	커서가 얻어 온 레코드의 개수

CLOSE CURSOR(커서 닫기): CLOSE 문장은 CURSOR를 사용할 수 없게 하고 결과 셋의 정의를 해제

CLOSE CURSOR_NAME;
CLOSE C1;

CURSOR와 FOR LOOP(묵시적으로 CURSOR에서 행을 처리)

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

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.LOCATION_ID);
    END LOOP;
    DBMS_OUTPUT.PUT_LINE(CONCAT('읽어드린 레코드수 : ', C1%ROWCOUNT));
    CLOSE C1;
END;
/

CURSOR와 FOR LOOP(묵시적으로 CURSOR에서 행을 처리.)

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

FOR RECORD_NAME IN CURSOR_NAME LOOP
--명시적 커서의 OPEN, FETCH가 자동적으로 수행됨
	STATEMENT1;
    STATEMENT2;
END LOOP	--루프문을 빠져 나갈 때 자동적으로 커서가 CLOSE 됨.

커서변수: 한 개이상의 쿼리를 연결해 사용 가능, 변수처럼 커서 변수를 함수나 프로시저의 매개변수로 전달

● 커서 변수 선언

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

오라클에서 제공하는 커서 타입(결과 집합이 고정되어 있지 않으므로)인 SYS_REFCURSOR이란 타입을 사용하는 것,

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

커서변수 SYS_REFCURSOR;	--커서변수 선언

● 커서변수의 사용

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

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

OPEN 커서변수명 FOR SELECT 문;

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

커서를 구성하는 쿼리에 커서 변수까지 연결했으니 커서 변수에 결과 집합ㄴ을 가져오는 패치 작업이 남았는데, 이때도 FETCH문 사용

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

● 커서변수의 사용

DECLARE
    VFIRST_NAME EMPLOYEES.FIRST_NAME%TYPE;
    --TYPE EMPLOYEESCURSOR IS REF CURSOR;   --커서 타입 선언
    --VEMPLOYEES EMPLOYEESCURSOR;   --커서 변수 선언
    VEMPLOYEES SYS_REFCURSOR;    --오라클 서버가 제공하는 커서타입(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;
/

ORACLE SUBPROGRAM

익명 블록	서브 프로그램
이름이 지정되지 않은 PL/SQL 블록	이름이 지정된 PL/SQL 블록
매번 사용시마다 컴파일	최초 실행될 때 한번만 컴파일
데이터베이스에 저장되지 않습니다.	데이터베이스에 저장
다른 응용프로그램에서 호출 불가	다른 응용프로그램에서 호출 가능
값 반환X	함수일 경우 값 RETURN, RETURN이 없는 함수 존재X
파라미터 사용X	파라미터 사용O

1) 저장 프로시저(Stored Procedure: 스토어드 프로시저)

프로시저 : 지정된 특정 처리를 실행하는 서브 프로그램의 한 유형.

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

CREATE [OR REPLACE] PROCEDURE PRCEDURE_NAME
(매개변수1 [IN(MODE)] 자료형,	--MODE에 따라 프로시저를 호출 시 값을 전달 받거나 프로시저 호출 후
매개변수2 [OUT(MODE)] 자료형...)	--해당 매개 변수 값을 받아 사용, IN/OUT/IN OUT,IN은 생략 가능
--OUT은 생략 불가능
IS
	LOCAL_VARIABLE DECLARATION	--변수선언
BEGIN
	STATEMENT1;
END [PRCEDURE_NAME];

USER_SOURCE: 저장 프로시저를 작성한 후 사용자가 저장 프로시저가 생성되었는지 확인

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

변수명 모드 자료형

--프로시저 생성               프로시저명 ( 변수명         입력             자료형)
CREATE OR REPLACE PROCEDURE EMPPROC02 (VDEPARTMENT_ID IN EMPLOYEES.DEPARTMENT_ID%TYPE)
IS
    CURSOR C1
    IS
    SELECT * FROM EMPLOYEES WHERE DEPARTMENT_ID = VDEPARTMENT_ID;
BEGIN
    DBMS_OUTPUT.PUT_LINE('사원번호 / 사원명 / 급여');
    DBMS_OUTPUT.PUT_LINE('---------------------------------------');
    FOR VEMPLOYEES IN C1 LOOP
        DBMS_OUTPUT.PUT_LINE(VEMPLOYEES.EMPLOYEE_ID||' / '||VEMPLOYEES.FIRST_NAME||' / ' ||
                            VEMPLOYEES.SALARY);
    END LOOP;
END;
/
SHOW ERROR;

--프로시저 실행
--[형식]
--EXECUTE 또는 EXEC 프로시저;
--위 코드는 주석과 함께 명시할 수 없다.
--부서번호가 90
EXECUTE EMPPROC02(90);

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

● IN MODE 매개변수: 실행환경에서 서브 프로시저로 값 전달.

부서별로 SALARY 인상. 부서코드가 10이면 10% 인상, 20이면 20%인상, 나머지 동결.

CREATE OR REPLACE PROCEDURE EMPPROC_INMODE
(VDEPARTMENT_ID IN EMPLOYEES01.DEPARTMENT_ID%TYPE)
IS
BEGIN
    UPDATE EMPLOYEES01 SET SALARY = DECODE(VDEPARTMENT_ID, 10, SALARY*1.1, 20, 
    SALARY*1.2, SALARY)
    WHERE DEPARTMENT_ID = VDEPARTMENT_ID;
    COMMIT;
    DBMS_OUTPUT.PUT_LINE('수정이 완료되었습니다.');
END EMPPROC_INMODE;
/

PreparedStatement

Connection 객체의 prepareStatement() 메소드를 사용하여 객체 생성

SQL문장이 미리 컴파일 되고 실행 시간동안 인수 값을 위한 공간을 확보한다는 점에서 Statement와 다름

각각의 인수에 대해 위치홀더(?)를 사용하여 SQL문장을 정의할 수 있게 함

PreparedStatement pstmt = null;
try{
	String query = "INSERT INTO member(id, password, name)
    				VALUES(?,?,?)";	//?: 바인딩 변수
    pstmt = conn.prepareStatement(query);
    pstmt.setString(1, "javauser");
    pstmt.setString(2, "java1234");
    pstmt.setString(3, "홍길동");
}catch(SQLException e){e.printStackTrace();}

'DataBase+SQL' 카테고리의 다른 글

PROCEDURE TRIGGER (0)	2023.06.29
PreparedStatement (0)	2023.06.28
PL/SQL (0)	2023.06.26
MVIEW, 시퀀스 (0)	2023.06.23
뷰(VIEW) (0)	2023.06.22

Campanula

SQL 반복문, 커서

'DataBase+SQL' 카테고리의 다른 글

+ Recent posts

티스토리툴바