1. 절차형 SQL

SQL은 구조적 질의어이며 데이터베이스의 모든 작업을 통제하는 비절차적언어이다. 비절차적 언어이기 때문에 사용자는 원하는 데이터를 선언적으로 표현할 뿐이다.

SQL에도 절차 지향적인 프로그램이 가능하도록 DBMS에서 절차형 SQL을 제공한다.

절차형 SQL을 이용하면 SQL문의 연속적인 실행이나 조건에 따른 분기처리를 이용해 특정 기능을 수행하는 저장 모듈을 생성할 수 있다.

절차형 SQL을 통해 생성할 수 있는 모듈에는 프로시저(Procedure), 사용자 정의 함수(User Defined Function), 트리거(Trigger)가 있다.

프로시저, 함수, 트리거 생성은 RESOURCE권한이 있어야 한다.

 

 

2. 프로시저(Procedure, 스토어드 프로시저(stored procedure))

일련의 쿼리를 마치 하나의 함수처럼 실행하기 위한 쿼리의 집합 (일련의 작업 절차를 정리해서 저장한 것)

절차형 SQL을 활용하여 특정 기능을 수행하는 작업을 일종의 트랜잭션 언어로 미리 저장해두고, 필요할 때마다호출을 통해 실행되어 미리 저장해둔 SQL 작업을 수행한다.

여러 프로그램에서 호출하여 사용 가능하고 시스템의 일괄 작업 등 자주 사용되는 복잡한 작업에 주로 사용된다.

 

 

2-1. 프로시저 구성

DECLARE : 프로시저의 명칭, 변수, 인수, 데이터 타입을 정의하는 선언부 (필수)

BEGIN / END : 프로시저의 시작과 종료를 의미 (필수)

CONTROL : 조건문 또는 반복문이 삽입, 순차적 처리

SQL : DML, DCL이 삽입돼, 조회, 추가, 수정, 삭제 작업을 수행

EXCEPTION : 구문 실행 중 예외 발생 시 처리 방법 정의

TRANSACTION : 작업들을 DB에 적용할지 취소하리 결정하는 처리부

​

 

2-2. 프로시저 생성 / 실행

CREATE OR REPLACE PROCEDURE 프로시저 명 

( 입력값 )

AS 혹은 IS

BEGIN

프로시저 내용

END

 

CREATE OR REPLACE로 프로시저가 있으면 대체하고, 없으면 생성한다.

 

 

프로시저 생성

CREATE OR REPLACE PROCEDURE INSERT_STUDENT
(
	IN_SNO IN VARCHAR2,
	IN_SNAME IN VARCHAR2,
	IN_MAJOR_NO IN NUMBER,
	IN_SCORE IN NUMBER
) AS
BEGIN
	INSERT INTO STUDENT VALUES(IN_SNO, IN_SNAME, IN_MAJOR_NO, IN_SCORE);
	COMMIT;
	DBMS_OUTPUT.PUT_LINE('정상적으로 데이터가 등록되었습니다.');
	EXCEPTION
		WHEN OTHERS THEN
			DBMS_OUTPUT.PUT_LINE('데이터등록 작업을 실패하였습니다.');
			ROLLBACK;
			
END INSERT_STUDENT;

특정 테이블에 데이터를 입력하는 프로시저를 프로시저명 "INSERT_STUDENT"로 생성했다.

( ) 안에서 컬럼명 IN 데이터타입으로 입력할 내용을 받는다. 출력할 내용은 컬럼명 OUT 타입으로 설정한다.

AS(IS) BEGIN 이후로 수행할 프로시저 내용을 입력한다.

 

 

프로시저 실행

EXEC 프로시저명

EXEC INSERT_STUDENT('S1234','홍길동',21,3.14);

결과물을 보기 위해서 SERVEROUTPUT 설정 을 ON 으로 한다.

EXEC으로 프로시저를 실행했다. 프로시저를 생성할 때 정의해주었던 컬럼에 대한 모든 내용을 입력해야 한다.

 

 

 

EXCEPTION 처리

EXCEPTION
WHEN 예외명 THEN
예외 처리 작업

 

SQL의 예외(EXCEPTION)처리는 위와 같이 한다. 여러가지 예외명을 입력하고 싶으면 OR로 연결하고, OTHERS는 모든 EXCEPTION을 포함하는 것으로 WHEN OTHERS THEN 단독으로 사용하면 된다.

 

 

 

프로시저 생성

CREATE OR REPLACE PROCEDURE INSERT_STUDENT
(
	IN_SNO IN VARCHAR2, //IN : 입력할 내용
	IN_SNAME IN VARCHAR2,
	IN_MAJOR_NO IN NUMBER,
	IN_SCORE IN NUMBER, //컬럼 데이터타입은 테이블 내의 데이터타입과 동일해야한다.
    	OUT_RESULT OUT NUMBER //OUT : 출력할 내용
) AS
BEGIN
//수행할 프로시저 내용
	INSERT INTO STUDENT VALUES(IN_SNO, IN_SNAME, IN_MAJOR_NO, IN_SCORE);
	COMMIT; 
    OUT_RESULT := 1; //OUT_RESULT에 1값을 입력한다.
    DBMS_OUTPUT.PUT_LINE('정상적으로 데이터가 등록되었습니다.');
	EXCEPTION
		WHEN OTHERS THEN //오류 EXCEPTION 생겼을 때 수행할 작업 
			DBMS_OUTPUT.PUT_LINE('데이터등록 작업을 실패하였습니다.');
			ROLLBACK;
            OUT_RESULT := 0; //OUT_RESULT에 0값을 입력한다.
			
END INSERT_STUDENT;

 

위의 예시에서 출력값, EXCEPTION을 추가로 설정한 프로시저를 생성했다.

해당 프로시저는 IN_SNO, IN_SNAME, IN_MAJOR_NO, IN_SCORE, OUT_RESULT 5개의 항목으로 구성되었다.

입력받은 내용을 STUDENT 테이블에 INSERT하고 COMMIT하는 작업을 입력했다.

EXCEPTION처리후 END 하였다. END 뒤에는 프로시저명이 없어도 된다.

 

 

 

프로시저 실행

SET SERVEROUTPUT ON;
DECLARE
    RESULT NUMBER;
BEGIN
    INSERT_STUDENT('BBaB1','AAAA',21,1.1,RESULT);
    DBMS_OUTPUT.PUT_LINE(RESULT);
END;

DBMS_OUTPUT.PUT_LINE 값을 보이기 위해서 SERVEROUTPUT를 ON으로 설정한다. 기본은 OFF로 되어있기 때문에 설정을 따로 해주어야 한다.

출력값이 있기 때문에 EXEC로 실행할 수 없다. DECLARE으로 출력될 데이터를 정의한다.

BEGIN 이후 실행할 프로시저를 입력하면 된다. INSERT_STUDENT 프로시저에는 5가지 항목이 모두 입력되어야 하고, 출력될 다섯번째 컬럼에는 DECLARE에서 정의한 이름을 넣어주면 된다.

위에서 설정한 프로시저에서 5번째 OUT_RESULT가 RESULT로 정의 된 것이다.

 

프로시저는 값을 RETURN할 수 있지만, 필수는 아니다.

 

 

 

3. 사용자 정의 함수

절차형 SQL을 활용하여 일련의 SQL 처리를 수행하고, 수행 결과를 단일 값으로 반환할 수 있는 절차형 SQL

RETURN문을 이용해 원하는 값을 반환할 수  있다.

 

 

3-1. 사용자 정의 함수 생성 / 실행

함수 생성

CREATE OR REPLACE FUNCTION 함수명

( 입력값 )

AS 혹은 IS

반환값 정의

BEGIN

프로시저 내용.... RETURN 

END

 

CREATE OR REPLACE FUNCTION GET_GRADE (
SCORE IN NUMBER
) RETURN VARCHAR2
AS 
RESULT VARCHAR2(1 BYTE) := 'F';
BEGIN
    IF SCORE >= 90 THEN
    RESULT := 'A';
    ELSIF SCORE >=80 THEN
    RESULT := 'B';
    ELSIF SCORE >= 70 THEN
    RESULT := 'C';
    ELSIF SCORE >= 60 THEN
    RESULT := 'D';
    ELSE
    RESULT := 'F';
    END IF;
    RETURN RESULT;
END GET_GRADE;
SELECT GET_GRADE(90) FROM DUAL;

함수는 반환값이 꼭 필요하다. 반환값을 정의하고, RETURN으로 내보내 주어야 한다.

 

 

함수 실행

SELECT GET_GRADE(90) FROM DUAL;

 임시테이블 DUAL로 함수를 실행해볼 수 있다.

 

 

 

3-2. 반복문

LOOP / END LOOP 사이 반복문은 LOOP, WHILE, FOR을 사용할 수 있다.

 

1) LOOP

CREATE OR REPLACE FUNCTION TOTAL_NUM (
NUM IN NUMBER
)RETURN NUMBER
AS 
TOTAL NUMBER :=0;
I NUMBER :=1;
BEGIN 
LOOP 
TOTAL := TOTAL+I;
I := I+1;
EXIT WHEN I>NUM;
END LOOP;
RETURN TOTAL;
END TOTAL_NUM;
SELECT TOTAL_NUM(30) FROM DUAL;

EXIT WHEN으로 반복문을 끝낸다. 

 

 

2) WHILE

CREATE OR REPLACE FUNCTION TOTAL_NUM (
NUM IN NUMBER
)RETURN NUMBER
AS 
TOTAL NUMBER :=0;
I NUMBER :=1;
BEGIN 
WHILE(I<=NUM)
LOOP
TOTAL := TOTAL+I;
I := I+1;
END LOOP;
RETURN TOTAL;
END TOTAL_NUM;
SELECT TOTAL_NUM(30) FROM DUAL;

WHILE은 ()안에 조건을 넣어주고 LOOP를 시작한다. 조건이 거짓이되면 반복문이 종료된다.

 

 

3) FOR

CREATE OR REPLACE FUNCTION TOTAL_NUM (
NUM IN NUMBER
)RETURN NUMBER
AS 
TOTAL NUMBER :=0;
I NUMBER :=1;
BEGIN 
FOR I IN 1 .. NUM
LOOP
TOTAL := TOTAL+I;
END LOOP;
RETURN TOTAL;
END TOTAL_NUM;
SELECT TOTAL_NUM(30) FROM DUAL;

FOR I IN 1 ..NUM : I가 1에서 NUM까지 반복문을 돌린다는 뜻이다.

 

 

 

4. 트리거(Trigger)

DB 시스템에서 삽입(INSERT), 수정(UPDATE), 삭제(DELETE)의 이벤트가 발생할 때마다 자동 수행되는 절차형 SQL

 

 

4-1. 트리거 구성

DECLARE : 프로시저와 동일

EVENT : 실행되는 조건 명시

BEGIN / END : 프로시저와 동일

CONTROL : 프로시저와 동일

SQL : 프로시저와 같지만 DCL은 삽입될 수 없다. ( COMMIT, ROLLBACK...)

EXCEPTION : 프로시저와 동일

 

 

 

4-2 트리거 생성

CREATE OR REPLACE TRIGGER INSERT_STUDENT 
BEFORE INSERT ON STUDENT
FOR EACH ROW
BEGIN
   INSERT INTO LOG(NO, KIND, CONTENT) VALUES (LOG_SEQ.NEXTVAL,'INSERT',:NEW.SNO||'-'||:NEW.SNAME||'-'||:NEW.MAJOR_NO||'-'||:NEW.SCORE);
END;

STUDENT 테이블에 INSERT 작업이 수행되면 실행할 트리거를 생성했다.

INSERT작업이 수행되면 LOG테이블에 값을 INSERT하도록했고, 이런 트리거로 작업 로그를 기록, 관리 할수있다.

 

 

 

 

'DBMS > Oracle' 카테고리의 다른 글

인덱스 index : 생성, 재설정, 삭제  (0) 2022.06.14
서브쿼리 (SUBQUERY) / 뷰(VIEW)  (0) 2022.05.23
SQL 함수  (0) 2022.05.20
제약조건 CONSTRAINT 종류, 생성, 수정, 조회, 삭제, 활성/비활성  (0) 2022.05.20
시퀀스 SEQUENCE - 생성, 기본설정값, 조회  (0) 2022.05.20

티스토리툴바