정보처리기사 실기 - 7. SQL 응용(3) /절차형SQL

NCS 정보처리기사

2) 절차형 SQL 작성하기

- 절차형 SQL : SQL 문의 연속적인 실행이나 조건에 따른 분기, 반복 등의 제어를 활용하여 다양한 기능을 수행하는 데이터베이스 저장 모듈

-> DBMS 엔진에서 직접 실행되며 BEGIN/END의 블록화된 구조를 갖는다.

-> 비즈니스 로직을 캡슐화하여 데이터 관리를 단순화할 수 있다.

-> 작업에 필요한 데이터를 DBMS 내부에서 직접 처리하기 때문에 In/Out Packet이 적다.

-> 이식시 수정 및 재컴파일이 필요함

 

- 구성

• DECLARE : 정의
• BEGIN : 시작점
• END : 종료점

 

- 제어문 종류

IF condition THEN
	statements;
ELSEIF condition THEN
	statements;
ELSE 
	statements;
END IF;

 

- 반복 제어

LOOP
	statements;
EXIT [WHEN condition];
END LOOP;

FOR index_in [REVERSE] 시작값 .. 끝값 LOOP
	statements;
END LOOP;

WHEN condition LOOP
	statements;
END LOOP;

 

- 순차 제어 

• GOTO : 제어가 건너뛰는 곳을 지정하는 레이블과 함께 쓰임
• NULL : 실행을 하지 않음을 의미

 

- 절차형 SQL의 SELECT 문 

SELECT select_list
INTO variable_name | record_name
FROM table
WHERE condition;

 -> 반드시 하나의 행만을 추출해야 오류없이 정상적으로 수행

 -> TOO_MANY_ROWS : 하나 이상의 데이터 행 추출 시

 -> NO_DATA_FOUND : 어떤 데이터도 추출하지 못할 때

 

- 절차형 SQL의 INSERT/UPDATE/DELETE 문

 

- INSERT 문 예시

DECLARE
	v_no shop.shop_no%TYPE;
    v_name shop.shop_name%TYPE;
BEGIN
	SELECT shop_no, shop_name
    INTO v_no, v_name
    FROM shop
    WHERE shop_name = 'MUSIC-STAR';
    INSERT INTO shop(shop_no, shop_name)
    VALUES (v_no+1, v_name);
END;

- UPDATE 문 예시

DECLARE
	v_name shop.shop_name%TYPE;
BEGIN
	v_name := 'PICTURE_STAR';
    UPDATE shop
    SET shop_name = v_name
    WHERE shop_name = 'MUSIC_STAR';
END;

- DELETE 문 예시

BEGIN
	DELETE FROM shop
    WHERE shop_name = 'PICTURE_STAR';
END;

 

- 절차형 SQL의 커서

SQL 처리 결과가 저장된 작업 영역에 이름을 지정하고 저장된 정보를 접근할 수 있게 하는 용도

-> 서버는 명령을 parse하고 실행하기 위한 메모리 영역을 하는데  이 영역을 cursor라 함

-> 커서의 종류 

• 명시적 커서 : 프로그래머가 선언하고 명령하며 블럭의 실행 가능한 부분에서 특정 명령을 통해 조작하는 커서
• 묵시적 커서 : SQL 문이 실행되는 순간 자동으로 열렸다가 닫힘

- 커서 선언

DECLARE CURSOR cursor_name IS SELECT 문;

- 커서 열기

OPEN cursor_name;

- 커서로부터 데이터 패치

FETCH cursor_name INTO variable1, variable2, ...;

 -> 커서의 SELECT 문의 컬럼의 수가 OUTPUT 변수의 수가 동일 해야 함

- 커서닫기

CLOSE cursor_name;

-> 사용을 끝낸 커서는 반드시 닫아주어야 함

 

- FOR 문을 사용하면 커서의 OPEN, FETCH, CLOSE가 자동 발생

-> 따로 기술할 필요 없음

-> 레코드 이름도 자동선언되므로 따로 선언할 필요없음

FOR record_name IN cursor_name LOOP
	statements;
END LOOP;

 

- 커서가 열릴 떄 파라미터 값을 커서에 전달하며, 다른 active set을 원할 때 마다 커서를 따로 선언해야 한다.

CURSOR cursor_name [(parameter_name Datatype, ...)]
	IS
    SELECT statement

 

- WHERE CURRENT OF 커서

-> ROWID를 이용하지 않고도 현재 함조하는 행을 갱신하고 삭제할 수 있으며, FETCH문에 의해 가장 최근에 처리된 행을 참조할 수 있다.

-> FOR UPDATE 절이 커서 선언 질의문 안에 있어야 사용할 수 있다.

DECLARE
	CURSOR emp_list IS
	SELECT empno
    FROM
    WHERE empno = 7934
     FOR UPDATE;
BEGIN
	FOR emplist IN emp_list LOOP
     UPDATE emp
      SET job = 'SALESMAN'
      WHERE CURRENT OF emp_list;
    END LOOP;
END;

 

- FETCH 연산 수행

• FETCH NEXT : 다음 행
• FETCH PRIOR : 이전 행
• FETCH FIRST : 첫번째 행
• FETCH LAST : 마지막 행

 

정보처리기사 실기 - 7. SQL 응용(4) /프로시저/트리거