4-8강 SQL 응용-절차형 SQL 작성(1)

과목4. 데이터베이스 구축, 8강. SQL 응용-절차형 SQL작성(1)

 

[ 목차 ]

1. 절차형 SQL

2. 절차형 SQL 특징

3. 절차형 SQL 구성

4. 절차형 SQL 제어문

5. 프로시저

6. 프로시저의 구성

7. 프로시저 정의 문법

 

1. 절차형 SQL

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

2) 반복 또는 자주 수행하는 DB 작업을 효율적으로 수행할 수 있음

3) 잘 정의된 절차형 SQL은 소프트웨어 개발 생산성을 높임

 

2. 절차형 SQL 특징

1) DBMS 엔진에서 직접 실행되며 BEGIN/END의 BLOCK화된 구조를 지님

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

3) 비즈니스 로직을 캡슐화하여 데이터 관리를 단순화할 수 있음

4) 작업에 필요한 데이터를 DBMS 내부에서 직접 처리하기 때문에 IN/OUT PACKER이 적음

5) 타절차형 언어에 비해 작업의 효율성은 낮은 편

6) DBMS 벤더별로 약간의 문법 차이가 존재해 이식시 수정 및 재컴파일이 필요

 

3. 절차형 SQL 구성

1) 절차형 SQL 종류와 DBMS 벤더마다 약간의 차이가 존재

2) 기본 구성 요소

  1] DECLARE : 대상이 되는 프로시저, 사용자 정의함수 등을 정의, 대상의 이름 및 변수 등을 선언

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

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

절차형 SQL
1) 프로시저 : 특정 기능을 수행하는 일종의 트랜잭션 언어로 호출을 통해 실행되어 미리 저장해 놓은 SQL 작업을 수행

2) 트리거 : DBMS에서 데이터의 입력, 갱신, 삭제 등의 이벤트가 발생할 때마다 관련 작업이 자동으로 수행

3) 사용자 정의 함수 : 프로시저와 유사하게 SQL을 사용하여 일련의 작업을 연속적으로 처리하며 종료 시 예약어 RETURN을 사용하여 처리 결과를 단일값으로 반환

DECLARE

BEGIN
 
    LOOP

END;

4. 절차형 SQL 제어문

1) 오라클 PL/SQL의 IF-THEN-ELSE- END IF 문

  1] IF문은 조건에 만족할 경우와 그렇지 않을 경우 각각에 대해 실행할 문장을 선택 가능(=선택 제어문)

  2] 조건이 많을 경우 ELSE IF를 이용해 조건을 더 추가

  3] ex.

2) 오라클 PL/SQL의 반복제어문 LOOP, FOR-LOOP, WHILE-LOOP

  1] 종료조건을 만족하지 않을 경우 무조건 LOOP안의 문장들을 반복수행

  2] FOR-ROOP는 지정된 횟수만큼 LOOP 안의 문장들을 반복수행

  3] WHILE-LOOP는 WHILE 문에 명시된 조건을 만족하는 동안 LOOP 안의 문장들을 반복 수행

  5] LOOP 

          statements;

            ...

          EXIT [WHEN condition];

      END LOOP;

  6] FOR index in 시작값..끝값 LOOP

          statements;

            ...

      END LOOP;

  7] WHILE condition LOOP

            statements;

            ...

      END LOOP;

3) 오라클 PL/SQL의 순차제어 GOTO, NULL

  1] GOTO : 제어가 건너뛰는 곳을 지정하는 레이블과 함께 사용

  2] NULL : 실행을 하지 않음을 의미

4) 오라클 PL/SQL의 SELECT문

  1] INTO절을 이용해 SELECT한 값을 변수에 할당

  2] 반드시 하나의 행만을 추출해야 함

  3] 추출되는 데이터 행이 없거나 두 개 이상일 경우 예외가 발생

  4] TOO_MANY_ROWS : 두 개 이상의 데이터 추출

  5] NO_DATA_FOUND : 데이터를 추출하지 못할 때

5) 오라클 PL/SQL의 INSERT/UPDATE/DELETE문

  1] INSERT

  2] UPDATE

  3] DELETE

6) 오라클 PL/SQL의 커서

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

  2] SQL 명령 실행 시 서버는 명령을 PARSE하고 실행하기 위한 메모리 영역을 OPEN하는데 이를 CURSOR라 지칭

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

  4] 묵시적 커서 : SQL문이 실행되는 순간 자동으로 열렸다 닫히며, 오라클은 모든 DML과 PL/SSQL SELECT문에서 생성

 

5. 프로시저

1) 매개변수를 받을 수 있고 반복해서 사용할 수 있는 이름이 있는 SQL 블록(블록단위로 묶어 한번에 처리)

2) 연속 실행 또는 구현이 복잡한 트랜잭션을 수행하는 SQL 블록을 DB에 저장ㄹ하기 위해 생성

3) 프로시저 호출을 통해 실행하며 일련의 SQL 작업을 포함하는 데이터 조작어DML를 수행

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

 

6. 프로시저의 구성

 

7. 프로시저 정의 문법

1) CREATE 명령어로 DBMS 내에 생성

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

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

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

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

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

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

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

6) MS사의 T-SQL의 프로시저 작성 문법