절차형 SQL

개요

• 특징
  - SQL에도 절차 지향적인 프로그램이 가능하도록 제공
  - SQL문의 연속적인 실행이나 조건에 따른 분기처리를 이용하여 특정 기능을 수행하는 모듈 생성 가능(Procedure, User Defined Function, Trigger)
  
  
• 종류
  - Oracle : PL/SQL
  - DB2 : SQL/PL
  - SQL Server : T-SQL

PL/SQL

• 특징
  - 절차적 프로그래밍을 가능하게 하는 트랜잭션 언어
  - Block 구조로 되어있고 Block 내에는 DML 문장과 Query문장, 그리고 절차형 언어(IF, LOOP) 등을 사용
  - 다양한 저장모듈(Stored Module) 개발 가능
  - 변수, 상수 등을 선언하여 SQL문장 간 값을 교환
  - Oracle에 내장되어 있으므로 Oracle 및 PL/SQL을 지원하는 다른 서버로 프로그램을 옮길 수 있음
  - 응용프로그램의 성능을 향상
  - 서버의 통신량을 줄일 수 있음(Block 단위로 보내기 때문)
  
  
• 저장모듈
  - PL/SQL문장을 데이터베이스 서버에 저장하여 유저와 애플리케이션 사이에서 공유할 수 있도록 만든 일종의 SQL 컴포넌트 프로그램
  - 독립적으로 실행되거나 다른 프로그램으로 부터 실행될 수 있는 완전한 실행 프로그램
  
  
• 구조
  
  - DECLARE
  > BEGIN ~ END에서 사용할 변수와 인수에 대한 정의, 데이터 타입 선언
  
  - BEGIN ~ END
  > 처리하고자 하는 SQL 문과 로직이 정의
  
  - EXCEPTION
  > 에러가 발생하면 그 에러를 어떻게 처리할 것인지를 정의
  
  
• 코드
  - Procedure 생성
  >
  CREATE OR REPLACE PROCEDURE 프로시저이름 OR REPLACE : 같은 이름의 프로시저가 있을 경우, 덮어 쓴다는 의미
      IN argument                                                  -- IN : OS에서 프로시저로 전달될 변수의 MODE
      OUT argument                                               -- OUT : 프로시저에서 처리된 결과가 OS로 전달되는 MODE
      IN OUT argument                                           -- IN OUT : IN과 OUT 기능을 동시에 수행하는 MODE
  IS
      변수의선언
  BEGIN
      PL/SQL Block
      EXCEPTION
  END;
  
  - Procedure 삭제
  >
  DROP PROCEDURE 프로시저이름

T-SQL

• 특징
  - SQL Server를 제어하기 위한 언어
  - 데이터 유형을 제공 (int, float, varchar 등)
  - 산술, 논리, 비교 등 연산자 사용 가능
  - 흐름 제어 기능 사용 가능 (IF-ELSE, WHILE, CASE-THEN)
  - 주석 기능 제공
  - 변수 선언 기능, 전역변수와 지역변수가 있음
  > 지역변수 : 자신의 연결 시간동안만 사용하기 위해 만들어지는 변수
  > 전역변수 : 이미 SQL 서버에 내장된 값
  
  
• 구조
  
  - DECLARE
  > BEGIN ~ END에서 사용할 변수와 인수에 대한 정의, 데이터 타입 선언
  
  - BEGIN ~ END
  > 처리하고자 하는 SQL 문과 로직이 정의
  
  - EXCEPTION
  > 에러가 발생하면 그 에러를 어떻게 처리할 것인지를 정의
  
  
• 코드
  - Procedure 생성
  >
  CREATE PROCEDURE 스키마이름.프로시저이름
      @parameter1 data_type1 [VARYING결과 집합 | DEFAULT | OUT | BEADONLY],
      @parameter2 data_type2 [mode],
  ...
  WITH [Option RECOMPILE | ENCRYPTIONCREATE | EXECUTE AS]
  AS
  ...
  BEGIN
  ...
  ERROR 처리
  ...
  END;
  
  - Procedure 삭제
  >
  DROP PROCEDURE 스키마이름.프로시저이름;
  
  

프로시저 예시 코드

• 테이블
  -
  SELECT * FROM dong_dept;
  
  
  
• 프로시저 생성
  -
  CREATE OR REPLACE PROCEDURE dong_dept_insert
  (
      v_deptno in number,
      v_dname in varchar2,
      v_loc in varchar2,
      v_result out varchar2
  )
  IS
      cnt number := 0;
  BEGIN
      SELECT COUNT(*) INTO CNT
      FROM dong_dept   
      WHERE DEPTNO = v_DEPTNO
      AND ROWNUM = 1;
      if cnt > 0 then
          v_result := '등록된 부서번호';
      else
          insert into dong_dept(deptno, dname, loc) values(v_deptno, v_dname, v_loc);
          commit;
          v_result := '입력완료';
      end if;
  EXCEPTION
      when others then
      rollback;
      v_result := '에러발생';
  END;


• 프로시저 실행
  -
  variable rslt varchar2(100);                               
  exec dong_dept_insert(10, 'dong', 'daegu', :rslt);   
  print rslt;
  
  
  exec dong_dept_insert(50, 'new dong', 'daegu', :rslt);
  print rslt;
  
  
  
• 프로시저 결과
  

User Defined Function

• 특징
  - Procedure처럼 절차형 SQL을 로직과 함께 DB내에 저장해 놓은 명령문의 집합
  - Procedure와 다른 점은 RETURN을 사용해서 하나의 값을 반드시 되돌려 줘야 한다는 것
  - Function은 항상 작업 수행 결과 값을 리턴함

Trigger

• 특징
  - 특정한 테이블에 INSERT, UPDATE, DELETE와 같은 DML문이 수행되었을 때, 데이터베이스에 자동으로 동작하도록 작성된 프로그램
  - 테이블과 뷰, 데이터베이스 작업을 대상으로 정의할 수 있으며, 전체 트랜잭션 자업에 대해 발생되는 Trigger와 각 행에 대해서 발생되는 Trigger가 있다.
  
  
• Procedure와 차이점
  

Procedure	Trigger
Create Procedure	Create Trigger
Execute	생성 후 자동으로 실행
Commit, Rollback 실행 가능	Commit, Rollback 실행 안됨