8장 SQL 응용

프로시저

SQL을 사용하여 작성한 일련을 작업을 저장해두고 호출하여 원할 때 마다 실행할 수 있도록 하는 절차형 SQL

 

정의

CREATE [OR REPLACE] PROCEDURE 프로시저이름(변수명 IN 변수타입)
IS
	변수선언
BEGIN
	sql문
    EXCEPTION
    	WHEN ,,, THEN
    		ROLLBACK;
    COMMIT;
END;

CREATE OR REPLACE emp_change(id IN int)
IS
BEGIN
	UPDATE 급여 SET 지급박식 = 'S' WHERE 사원번호 = id;
    EXCEPTION
    	WHEN PROGRAM_ERROR THEN
        	ROLLBACK;
    COMMIT;
END;

 

실행 / 삭제

EXECUTE 프로시저명(변수);

DROP PROCEDURE 프로시저명;

 

트리거

삽입, 갱신, 삭제의 이벤트가 발생하면 특정 작업이 자동으로 동작하도록 하는 절차형 sql

CREATE [OR REPLACE] TRIGGER 트리거명 [작업시점][작업종류] ON 테이블명
REFERENCING [OLD | NEW] as 임시테이블명
FOR EACH ROW
[WHEN 조건식]
BEGIN
	sql문;
END;

CREATE OR REPLACE TRIGGER 학년정보_tri BEFORE INSERT on 학생
REFERENCING NEW as 임시테이블명
FOR EACH ROW
WHEN (임시테이블명.학년 is null)
BEGIN
	:임시테이블명.학년 := '신입생';
END;

 

삭제

DROP TRIGGER 트리거명;

 

사용자 정의 함수

프로시저와 비슷하게 sql을 사용하여 일련의 작업들을 연속적으로 처리하지만, 종료 시 처리 결과로 단일 값만을 반환하는 절차형 SQL

 

CREATE [OR REPLACE] FUNCTION 함수명 (변수명 IN 변수타입)
RETURN 리턴타입
IS
	변수선언
BEGIN
	사용자 정의 함수 body;
    RETURN 반환값;
END;

CREATE OR REPLACE Get_S_성별(i_성별코드 IN int)
RETURN VARCHAR2
IS
BEGIN
	IF i_성별코드 = 1 THEN
    	RETURN '남자';
    ELSE
    	RETURN '여자';
END;

 

실행

DML에서 속성값이 놓일 부분에 사용 가능

 

삭제

DROP FUNCTION 함수명;

 

제어문

절차형 sql의 진행 순서를 변경하기 위해 사용하는 명령문

 

IF문

DECLARE
	변수선언 및 초기화;
BEGIN
	IF 조건 THEN;
    	실행문장;
    ELSE
    	실행문장;
    END IF;
END;

DECLARE
	a INT := 10;
BEGIN
	IF a >= 10 THEN
    	DBMS_OUTPUT.PUT_LINE('True');
    ELSE
    	DBMS_OUTPUT.PUT_LINE('False');
    END IF;
END;

 

LOOP문

DECLARE
	변수선언 및 초기화;
BEGIN
	LOOP
    	실행할 문장;
    EXIT WHEN 조건;
    END LOOP;
END;

DECLARE
	i INT := 0;
    sum INT := 0;
BEGIN
	LOOP
    	i := i + 1;
    	sum := sum + i;
    EXIT WHEN i >= 10;
    END LOOP;
END;

 

DECLARE
	i INT := 0;
    sum INT := 0;
BEGIN
	LOOP
    	i := i + 1;
        IF mod(i, 2) = 0 THEN
    		sum := sum + i;
        END IF;
    EXIT WHEN i >= 10;
    END LOOP;
END;

 

커서

쿼리문의 처리결과가 저장되어 있는 저장공간을 가리키는 포인터

동일한 쿼리가 반복 수행되어 DB자원을 낭비시키는 상황을 방지하고자 함

 

명시적 커서

사용자가 직접 정의해서 사용하는 커서

선언 -> 열기 -> 패치 -> 닫기 순서가 지켜져야 함

 

DECLARE
	CURSOR 커서명(변수명 변수타입, ..)
    IS
    	변수선언
    select sql문;
    
BEGIN
	OPEN 커서명(변수..);
    LOOP
    	FETCH 커서명 INTO 변수;
        변수로 진행..
    EXIT WHEN 커서명%NOTFOUND;
    END LOOP;
    CLOSE 커서명;
END;

DECLARE
	p_name employee.name%TYPE;
	CURSOR cur_name(ff INT)
    IS
    select name from employee where id >= ff;
    
BEGIN
	OPEN cur_name(20);
    LOOP
    	FETCH cur_name INTO p_name;
        DBMS_OUTPUT.PUT_LINE(p_name);
    EXIT WHEN cur_name%NOTFOUND;
    END LOOP;
    CLOSE cur_name;
END;