[2020 정보처리기사 실기 - SQL 응용] 2. 절차형 SQL 작성하기

 

 

* 절차형 SQL

 

1) 절차형 SQL

- SQL 문의 연속적인 실행인 ㅏ조건에 따른 분기, 반복 등의 제어를 활용하여 다양한 기능을 수행하는 DB저장 모듈

   반복 또는 자주 수행하는 DB작업을 효율적으로 수행할 수 있으며, 잘 정의된 절차형 SQL은 SW개발 생산성을 높임

- 특징

   DBMS 엔진에서 직접 실행되며 BEGIN/END의 블록화된 구조

   조건문 반복문 등 단일 SQL문장으로 실행하기 어려운 연속적 작업처리 가능

   비즈니스 로직으 캡슐화하여 데이터 관리를 단순화

   작업에 필요한 데이터를 DBMS 내부에서 직접 처리해서 IN OUT PACKET이 적음

   타절차형 언어에 비해 작업의 효율성은 낮음, DBMS 벤더별로 문법차이가 존재

- 기본구성요소

   DECLARE : 대상이 되는 프로시저, 사용자 정의함수 등을 정의

   BEGIN : 프로시저, 사용자 정의함수가 실행되는 시작점

   END : 프로시저, 사용자 정의함수가 실행되는 종료점

 

2) 절차형 SQL 제어문

- 종류 

   IF-THEN-ELSE 조건 제어

   LOOP, FOR-LOOP,WHILE-LOOP 반복 제어

   GOTO(건너 뛸 곳 지정), NULL(실행하지 않음을 나타냄) 순차제어

- 절차형 SQL의 SELECT 문 : SELECT INTO FROM WHERE

                                                     반드시 하나의 행만 추출해야 오류가 없음

                                                     TOO_MANY_ROWS 하나 이상의 데이터 행 추출, NO_DATA_FOUND 데이터가 없음

                                                     다수개의 데이터 행을 하나씩 추출할 때 명시적 커서를 사용해야함

- 절차형 SQL의 INSERT/UPDATE/DELETE문 : 형식은 SQL과 동일

- 절차형 SQL의 커서 

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

   SQL 명령을 실행시키면 서버는 명령을 parse하고 실행하기 위한 메모리 영역을 open하는데 이 영역을 cursor라 함

   명시적 커서 : 프로그래머가 선언하고 명령하며 블럭의 실행 가능한 부분에서 특정 명령을 통해 조작하는 커서

                            DECLARE(이름이 있는 SQL 영역 생성) EMPTY(현재 데이터 행의 존재여부 검사하고 레코드가 없으면 FETCH)

                            OPEN(커서 활성화) FETCH(현재 데이터 행을 해당 변수에 넘김) CLOSE(커서가 사용한 자원 헤제)

   묵시적 커서 : sql 문이 실행되는 순간 자동으로 열렸다가 닫힘, 오라클은 모든 DML과 SELECT문에 묵시적 커서 생성

   오라클 묵시적 커서 : SQL %ROWCOUNT, SQL %FOUND, SQL %NOTFOUND, SQL %ISOPEN

 

 

*프로시저 및 호출문 작성

 

1)프로시저

- 매개변수를 받을 수 있고 반복해서 사용할 수 있는 이름이 있는 SQL 블록으로 연속 실행 또는 구현이 복잡한 트랜잭션 수행하는 SQL 블록을 DB에 저장하기 위해 생성

- 프로시저 호출을 통해 실행, 일련의 SQL 작업을 포함하는 DML을 수행한다

- 일일 마감작업 또는 일련의 배치 작업등을 프로시저로 활용하여 관리하고 주기적으로 수행

- 프로시저 구성

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

  BEGIN/END : 프로시저의 시작과 종료를 표현하는 필수 요소, 프로시저 내부에서 블록을 구분하는데 사용

  CONTROL : 순차실행, 조건분기, 반복수행 등 SQL 문장의 수행순서를 조정하는데 사용

  SQL : DQL(SELECT) DML(INSERT UPDATE DELETE)를 주로 사용, 경우에 따라 DDL 사용

  EXCEPTION : BEGIN/END절에 실행되는 SQL문이 실행될 때 예외나 오류 발생 경우 예외처리 방법 정의

  TRANSACTION : 프로시저에서 수행된 DML 수행 내역의 DBMS의 적용 또는 취소 여부를 결정하는 처리부(COMMIT/ROLLBACK)

 

2)프로시저 작성

- CREATE 명령어로 DBMS내에 프로시저 생성

- OR REPLACE 명령은 기존 프로시저 존재 시 현재 컴파일하는 내용으로 덮어쓴다는 의미

   동명의 프로시저가 존재할 때 CREATE 명령문만 사용하면 컴파일 에러 발생

- PARAMETER는 외부에서 프로시저 호출 시 변수 입력 또는 출력할 수 있음

- MODE는 변수의 입력 또는 출력을 구분하여 사용

   IN : 운영 체제에서 프로시저로 전달되는 MODE

   OUT : 프로시저에서 처리된 결과가 운영체제로 전달되는 MODE

   INOUT : IN과 OUT의 두가지 기능을 동시에 수행하는 MODE

 

 

* 사용자 정의 함수 및 호출쿼리 작성

 

1) 사용자 정의 함수

-함수 개념 : 매개 변수를 받을 수 있고 데이터와 관련된 복잡한 계산 등을 수행하며 해당 동작의 결과를 반환하는 사용자 정의함수를 DBMS에 정의할 수 있다

 

 

*트리거

 

1) 트리거

- 데이터베이스에 특정한 변경이 가해졌을때 발생할 때마다 DBMS가이드에 대응해서 자동적으로 호출하는 일종의 프로시저

- 프로시저와 함수는 그 실행이 외부적인 실행 명령에 의해 이루어지는데

   트리거 실행은 트리거링 사건에 의해 내부적으로 이루어짐

- 트리거를 이벤트-조건-동작(ECA)규칙이라고도 부름

- 트리거 구성 

   EVENT : 트리거를 가동하는 사건(INSERT, UPDATE, DELETE 등)

   CONDITION : 트리거 수행여부를 결정하는 검사조건 (WHEN 등)

   ACTION : 트리거가 가동될 때 수행하는 작업 (BEGIN- END 블록)

- 트리거 용도 

   테이블 새성시 제약조건으로 정의할 수 없는 복잡한 요구사항에 대한 제약조건을 생성하는 데 사용

   테이블 정의시 표현할 수 없는 기업의 비즈니스 규칙들을 시행하는 역할

   테이블의 데이터에 생기는 작업을 감시, 보안 통제용으로 트리거를 활용

 

2) 트리거 작성

- 오라클 PL/SQL 

   trigger_event : INSERT, DELETE, UPDATE 중 한 개 이상을 기술

   BEFORE : INSERT, UPDATE, DELETE문이 실행되기 전 트리거를 실행

   AFTER : INSERT, UPDATE, DELETE 문이 실행된 후 트리거 실행

   FOR EACH ROW : 행 트리거 정의문

   행 트리거의 컬럼 값 참조 연산자 : " : old", " : new"

   INSERT : new.column_name

   UPDATE : old.column_name, new.column_name

   DELETE : old.column_name

- SQL Server T-SQL

   SQL server ㅡ리거의 컬럼 값 참조

   새로 추가되는 레코드와 기존 레코드는 논리적인 테이블 INSERTED, DELETED에 각각 저장됨

   INSERT TRIGGER , DELETE TRIGGER, UPDATE TRIGGER(INSERTED, DELTED 테이블 함께 사용)

   DML 트리거 : 트리거에 정의된 테이블 또는 뷰에 영향을 주는 DML 실행 시 자동으로 적용되는 특별한 유형의 저장 프로시저

   DDL 트리거 : 다양한 DDL 이벤트에 대한 응답으로 실행

                            DB 스키마에 대한 특정 변경 작업을 방지하려는 경우 또는 특정 작업이 수행되게 하려는 경우

                            데이터베이스 스키마의 변경 내용이나 이벤트를 기록하려는 경우 사용

   LOGON 트리거 : LOGON 이벤트에 대한 응답으로 저장 프로시저 실행

 

---

(이 포스팅은 쿠팡 파트너스 활동의 일환으로, 이에 따른 일정액의 수수료를 제공받을 수 있습니다.)