4장 - SQL활용 | 섹션26. 프로시저(Procedure), 섹션27. 트리거(Trigger), 섹션28. 사용자 정의 함수, 섹션29. DBMS 접속 기술

2024년도 시나공 필기 책 내용 정리

---

섹션26. 프로시저(Procedure)

 

1. 프로시저(Procedure)의 개요

 

• 절차형 SQL을 활용하여 특정 기능을 수행하는 일종의 트랜잭션 언어로 호출을 통해 실행되어 미리 저장해 놓은 SQL작업을 수행
• 프로시저를 만들어 데이터베이스에 저장하면 여러 프로그램에서 호출하여 사용할 수 있음
• 데이터베이스에 저장되어 수행되니 때문에 스토어드 프로시저라고도 불림
• 시스템의 일일 마감 작업, 일괄 작업 등에 주로 사용됨

프로시저 구성도

https://velog.io/@alpaka206/86.-%ED%94%84%EB%A1%9C%EC%8B%9C%EC%A0%80Procedure / 출처

• DECLARE : 프로시저의 명칭, 변수, 인수, 데이터 타입을 정의하는 선언부
• BEGIN / END : 프로시저의 시작과 종료를 의미
• CONTROL : 조건문 또는 반복문이 삽입되어 순차적으로 처리됨
• SQL : DML, DCL이 삽입되어 데이터 관리를 위한 조회, 추가, 수정, 삭제 작업을 수행
• EXCEPTION : BEGIN ~ END 안의 구문 실행 시 예외가 발생하면 이를 처리하는 방법을 정의
• TRANSACTION : 수행된 데이터 작업들을 DB에 적용할지 취소할지를 결정하는 처리여부

---

2. 프로시저 생성

CREATE [OR REPLACE] PROCEDURE 프로시저명(파라미터)
[지역변수 선언]
BEGIN
    프로시저 BODY;
END;

• OR REPLACE: 선택적인 예약어, 이 예약어를 사용하면 동일한 프로시저 이름이 이미 존재하는 경우 기존의 프로시저를 대체할 수 있음
• 프로시저명 : 생성하려는 프로시저의 이름을 지정
• 파라미터
  
  
  • IN : 호출 프로그램이 프로시저에게 값을 전달할 때 지정
  • OUT : 프로시저가 호출 프로그램에게 값을 반환할 때 지정
  • INOUT : 호출 프로그램이 프로시저에게 값을 전달하고, 프로시저 실행 후 호출 프로그램에 값을 반환할 때 지정
  • 매개변수명 : 호출 프로그램으로부터 전달받은 값을 저장할 변수의 이름을 지정
  • 자료형 : 변수의 자료형을 지정
• 프로시저 BODY
  
  • 프로시저의 코드를 기록하는 부분
  • BEGIN에서 시작하여 END로 끝나며 BEGIN과 END 사이에는 적어도 하나의 SQL문이 있어야 함

# 사원번호를 입력받아 해당 사원의 지급방식을 S로 변경하는 프로시저를 생성 - 오라클 예제
CREATE OR REPLACE PROCEDURE emp_change_s(i_사원번호 IN INT)
IS # 변수를 사용하지 않으므로 예약어만 입력 
BEGIN
    UPDATE 급여 SET 지급방식 = 'S' WHERE 사원번호 = i_사원번호;
    EXCEPTION # 예외문
        WHEN PROGRAM_ERROR THEN
            ROLLBACK;
    COMMIT;
END;

---

3. 프로시저 실행

 

• EXECUTE 명령어(줄여서 EXEC) 또는 CALL 명령어를 사용하여 실행

EXECUTE 프로시저명;
EXEC 프로시저명;
CALL 프로시저명;

---

4. 프로시저 제거

DROP PROCEDURE 프로시저명;

---

섹션27. 트리거(Trigger)

 

1. 트리거(Trigger)의 개요

 

• 데이터베이스 시스템에서 데이터의 삽입(Insert), 갱신(Update), 삭제(Delete) 등의 이벤트(Event)가 발생할 때마다 관련 작업이 자동으로 수행되는 절차형 SQL
• 데이터베이스에 저장되며 데이터 변경 및 무경성 유지, 로그 메세지 출력 등의 목적으로 사용됨
• 트리거의 구문에는 DCL(데이터 제어어)를 사요할 수 없으며 DCL이 포함된 프로시저나 함수를 호출하는 경우에도 오류가 발생함
• 트리거에 오류가 있는 경우 트리거가 처리하는 데이터에도 영향을 미치므로 트리거를 생성할 때 세심한 주의가 필요함

---

2. 트리거의 구성

 

• 선언, 이벤트, 시작, 종료로 구성되며 시작과 종료 구문 사이에는 제어(CONTROL), SQL, 예외(EXCEPTION)가 포함됨

https://velog.io/@alpaka206/87.-%ED%8A%B8%EB%A6%AC%EA%B1%B0Trigger / 출처

• DECLARE : 트리거의 명칭, 변수 및 상수, 데이터 타입을 정의하는 선언부
• EVENT : 트리거가 실행되는 조건을 명시
• BEGIN / END : 트리거의 시작과 종료를 의미
• CONTROL : 조건문 또는 반복문이 삽입되어 순차적으로 처리됨
• SQL : DML문이 삽입되어 데이터 관리를 위한 조회, 추가, 수정, 삭제 작업을 수행
• EXCEPTION : BEGIN ~ END 안의 구문 실행 시 예외가 발생하면 이를 처리하는 방법을 정의

---

3. 트리거의 생성

CREATE [OR REPLACE] TRIGGER 트리거명 동작시기 동작 ON 테이블명
[REFERENCING NEW | OLD AS 테이블명]
[FOR EACH ROW [WHEN 조건식]]
BEGIN
    트리거 BODY;
END;

• OR REPLACE : 선택적인 예약어, 이 예약어를 사용하면 동일한 트리거 이름이 이미 존재하는 경우 기존의 트리거 대체할 수 있음
• 동작시기 : 트리거가 실행될 때를 지정, AFTER(테이블이 변경된 후) 와 BEFORE(테이블이 변경되기 전)가 있음
• 동작 : 트리거가 실행되게 할 작업의 종류를 지정
  
  
  • INSERT : 테이블에 새로운 튜플을 삽입할 때
  • DELETE : 테이블의 튜플을 삭제할 때
  • UPDATE : 테이블의 튜플을 수정할 때
• NEW | OLD : 트리거가 적용될 테이블의 별칭을 지정
  
  
  • NEW : 추가되거나 수정에 참여할 튜플들의 집합(테이블)을 의미
  • OLD : 수정되거나 삭제 전 대상이 되는 튜플들의 집합(테이블)을 의미
• FOR EACH ROW : 각 튜플마다 트리거를 적용한다는 의미
• WHEN 조건식 : 선택적인 예약어, 트리거를 적용할 튜플의 조건을 지정
• 트리거 BODY: 트리거의 본문코드를 입력하는 부분으로 BEGIN 으로시작해서 END로 끝나며 적어도 하나 이상의 SQL 문이 있어야 오류가 발생하지 않음

/* <학생> 테이블에 새로운 튜플이 삽입될 때, 삽입되는 튜플에 학년 정보가 누락됐으면
학년 필드에 신입생을 치환하는 트리거를 학년정보_tri라는 이름으로 정의 */
CREATE TRIGGER 학년정보_tri BEFORE INSERT ON 학생
REFERENCING NEW AS new_table
FOR EACH ROW
WHEN (new_table.학년 IS NULL)
BEGIN
    :new_table.학년 :='신입생';
END;

---

4. 트리거의 제거

DROP TRIGGER 트리거명;

---

섹션28. 사용자 정의 함수

 

1. 사용자 정의 함수의 개요

 

• 프로시저와 유사하게 SQL을 사용하여 일련의 작업을 연속적으로 처리하며 종료 시 처리 결과를 단일 값으로 반환하는 절차형 SQL
• 데이터베이스에 저장되어 SELET, INSERT, DELETE, UPDATE 등 DML문의 호출에 의해 실행됨
• 예약어 RETURN을 통해 값을 반환하기 때문에 출력 파라미터가 없음
• INSERT, DELETE, UPDATE를 통한 테이블 조작은 할 수 없고 SELECT를 통환 조회만 할 수 있음
• 프로시저를 호출하여 사용할 수 없음
• SUM(), AVG() 등의 내장 함수처럼 DML문에서 반환값을 활용하기 위한 용도로 사용됨

구분	프로시저	사용자 정의 함수
반환값	없거나 1개 이상	1개
파라미터	입/출력 가능	입력만 가능
사용 가능 명령문	DML, DCL	SELECT
호출	프로시저, 사용자 정의 함수	사용자 정의 함수
사용 방법	실행문	DML에 포함

---

2. 사용자 정의 함수의 구성

 

• 프로시저와 유사하며 프로시저의 구성에서 RETURN만 추가하면 됨

https://lipcoder.tistory.com/363 / 출처

• DECLARE : 사용자 정의 함수의 명칭, 변수, 인수 데이터 타입을 정의하는 선언부
• BEGIN / END : 사용자 정의 함수의 시작과 종료를 의미
• CONTROL : 조건문 또는 반복문이 삽입되어 순차적으로 처리
• SQL : SELECT 문이 삽입되어 데이터 조회 작업울 수행
• EXCEPTION : BEGIN ~ END 안의 구문 실행 시 예외가 발생하면 이를 처리하는 방법을 정의
• RETURN : 호출 프로그램에 반환할 값이거나 변수를 정의

---

3. 사용자 정의 함수 생성

CREATE [OR REPLACE] FUNCTION 사용자 정의 함수명(파라미터)
[지역변수 선언]
BEGIN
    사용자 정의 함수 BODY;
    RETURN 반환값;
END;

• OR REPLACE : 선택적인 예약어, 동일한 사용자 정의 함수의 이름이 이미 존재하는 경우 기존의 사용자 정의 함수를 대체
• 파라미터 : IN(호출프로그램이 함수에 값을 전달), 매개변수명(호출 프로그램으로부터 전달받은 값을 저장할 변수의 이름을 지정), 자료형(변수의 자료형을 지정)
• 사용자 정의 함수 BODY
  
  
  • 사용자 정의 함수의 코드를 기록하는 부분
  • BEGIN에서 시작하여 END로 끝나며 BEGIN과 END사이에는 적어도 하나의 SQL문이 있어야 함
• RETURN반환값 : 반환할 값이나 반환할 값이 저장된 변수를 호출 프로그램에 돌려줌

# i_성별코드를 입력받아 1이면 남자 2면 여자를 반환하는 사용자 정의 함수 Get_S_성별을 정의
CREATE FUNCTION Get_S_성별(i_성별코드 IN INT)
RETURN VARCHAR2 # Oracle에서는 가변 문자 자료형 설정시 안정성을 위해 VARCHAR2를 권고
IS
BEGIN
    IF i_성별코드 = 1 THEN  # IF문
        RETURN '남자';
    ELSE 
        RETURN '여자';
    END IF;
END;

---

4. 사용자 정의 함수의 실행

 

• DML에서 속성명이나 값이 놓일 자리를 대체하여 사용

SELECT 사용자정의 함수명 FROM 테이블명;
INSERT INTO 테이블명(속성명) VALUES (사용자정의 함수명);
DELETE FROM 테이블명 WHERE 속성명 = 사용자정의 함수명;
UPDATE 테이블명 SET 속성명 = 사용자 정의 함수명;

/* <사원> 테이블을 출력하되 성별코드는 앞에서 사용자 정의 함수'Get_S_성별에
값을 전달하여 반환받은 값으로 대체하여 출력 */
SELECT 이름, Get_S_성별(성별코드) FROM 사원;

---

5. 사용자 정의 함수 제거

DROP FUNCTION 사용자 정의 함수명;

---

섹션29. DBMS 접속 기술

 

1. DBMS 접속의 개요

 

• 사용자가 데이터를 사용하기 위해 응용 시스템을 이용하여 DBMS에 접근하는 것을 의미
• 응용 시스템은 사용자로부터 매개 변수를 전달받아 SQL을 실행하고 DBMS로부터 전달받은 결과를 사용자에게 전달하는 매개체 역할을 수행
• 인터넷을 통해 구동되는 웹 응용 프로그램은 웹 응용 시스템을 통해 DBMS에 접근
• 웹 응용 시스템은 웹 서버와 웹 애플리케이션 서버(WAS)로 구성되며 서비스 규모가 작은 경우 웹 서버와 웹 애플리케이션 서버를 통합하여 하나의 서버만으로 운용 할 수 있음

웹 응용 시스템의 구조

https://1-day-1-coding.tistory.com/31 / 출처

• 사용자는 웹 서버에 접속하여 데이터를 주고 받음
• 웹 서버는 많은 수의 서비스 요청을 처리하기 때문에 사용자가 대용량의 데이터를 요청하면 직접 처리하지 않고 WAS에게 해당 요청을 전달함
• WAS는 수신한 요청을 트랜잭션 언어로 변환한 후 DBMS에 전달하여 데이터를 받고 받은 데이터는 처음 요청한 웹 서버로 다시 전달되어 사용자에게까지 도달됨

---

2. DBMS 접속 기술

 

• DBMS에 접근하기 위해 사용하는 API 또는 API의 사용을 편리하게 도와주는 프레임워크 등을 의미
• JDBC(Java DataBase Connectivity)
  
  
  • Java 언어로 다양한 종류의 데이터베이스에 접속하고 SQL문을 수행할 때 사용되는 표준 API임
  • 1997년 2월 썬 마이크로시스템에서 출시
  • Java SE(Standard Edition)에 포함되어 있으며 JDBC 클래스는 java, sql, javax.sql에 포함되어있음
  • 접속하려는 DBMS에 대한 드라이버가 필요함
• ODBC(Open DataBase Connectivity)
  
  • 데이터베이스에 접근하기 위한 표준 개방형 API로 개발 언어에 관계없이 사용 할 수 있음
  • 1992년 9월 마이크로소프트에서 출시
  • 프로그램 내 ODBC 문장을 사용하여 MS-ACCESS, DBase, DB2, Excel, Text 등 다양한 데이터베이스에 접근할 수 있음
  • 접근하려는 DBMS에 맞는 드라이버가 필요하지만 접속하려는 DBMS의 인터페이스를 알지 못하더라도 ODBC 문장을 사용하여 SQL을 작성하면 ODBC에 포함된 드라이버 관리자가 해당 DBMS의 인터페이스에 맞게 연결해 주므로 DBMS의 종류를 몰라도 됨
• MyBatis
  
  • JDBC코드를 단순화 하여 사용할 수 있는 SQL Mapping 기반 오픈 소스 접속 프레임워크
  • 데이터베이스에 접속하려면 다양한 메소드를 호출하고 해제해야 하는데, MyBatis는 이를 간소화 했고 접속 기능을 강화하였음
  • SQL문장을 분리하여 XML파일을 만들고 Mapping을 통해 SQL을 실행
  • SQL을 거의 그대로 사용할 수 있어 SQL 친화적인 국내 환경에 적합하여 많이 사용됨

---

3. 동적 SQL(Dynamic SQL)

 

• 개발 언어에 삽입되는 SQL 코드를 문자열 변수에 넣어 처리하는 것으로 조건에 따라 SQL 구문을 동적으로 변경하여 처리할 수 있음
• 사용자로부터 SQL문의 일부 또는 전부를 입력받아 실행할 수 있음
• 값이 입력되지 않을 경우 사용하는 NVL함수를 사용할 필요가 없음
• 응용프로그램 수행 시 SQL이 변형될 수 있으므로 프리컴파일 할 때 구문 분석, 접근 권한 확인 등을 할 수 없음
• 정적 SQL에 비해 속도가 느리지만 상황에 따라 다양한 조건을 첨가하는 등 유연한 개발이 가능함

* NVL함수: NVL(A,B) 형태의 함수로 A가 NULL 인경우 B를 반환하고 NULL이 아닌경우에는 A를 반환

	정적 SQL(Static SQL)	동적 SQL(Dynamic SQL)
SQL 구성	커서(Cursor)를 통한 정적 처리	문자열 변수에 담아 동적 처리
개발 패턴	커서의 범위 안에서 반복문을 활용하여 SQL 작성	NVL 함수 없이 로직을 통해 SQL 작성
실행 속도	빠름	느림
사전 검삭	가능	불가능

 

* 4장 핵심요약 패스 - 중요하지 않다고 판단