[SQLD] SQL활용 - 절차형 SQL

절차형 SQL

- 절차형 SQL 개요

SQL 언어에서도 일반적인 개발 언어처럼 절차 지향적인 프로그램이 가능하도록 DBMS 벤더별로 PL(Procedural Language) / SQL(Oracle), SQL/PL(DB2), T-SQL(SQL Server) 등의 절차형 SQL을 제공하고 있다. 이는 조건에 따른 분기처리를 이용해 특정 기능을 수행하는 저장 모듈을 생성할 수 있고, 절차형 SQL을 통해 생성할 수 있는 모듈인 Procedure, User Defined Function, Trigger에 대해서 간단하게 살펴보도록 한다.

- PL/SQL 개요

ORACLE의 PL/SQL은 BLOCK 구조로 되어있고, 블럭 내에서는 DML문장과 QUERY 문장, 그리고 절차형 언어(IF/LOOP)등을 사용할 수 있으며, 절차적 프로그래밍을 가능하게 하는 트랜잭션 언어.

이러한 PL/SQL을 이용하여 다양한 저장모듈 개발 가능.  저장모듈이란 PL/SQL문장을 데이터베이스 서버에 저장하여 사용자와 어플리케이션 사이에 공유할 수 있도록 만든 일종의 SQL 컴포넌트 프로그램이며, 완전한 실행 프로그램이다.

 

※ Oracle 저장 모듈에는 Procedure, User Defined Function, Trigger가 있다

- PL/SQL 특징

- Block 구조로 되어있어 기능별로 모듈화 가능

- 변수, 상수 등을 선언하여 SQL 문장 간 값 교환 가능

- IF, LOOF 등의 절차형 언어를 사용하여 절차적인 프로그램이 가능하도록 함

- DBMS 정의 에러나 사용자 정의 에러를 정의하여 사용 가능

- PL/SQL은 Oracle에 내장되어있어서, 이를 지원하는 어떤 서버로도 프로그램을 옮길 수 있음

- PL / SQL은 응용 프로그램의 성능을 향상시킴

- 이는 SQL 문장을 여러 블록으로 묶고 한 번에 블록 전부를 서버로 보내기 때문에 통신량을 줄일 수 있음

 

PL/SQL 엔진 : 프로그램 문장 처리

SQL Statement Executor : SQL 문장 처리

- PL/SQL 구조 및 문법

CREATE [OR REPLACE] PROCEDURE [Procedure_name]
( argument1 [mode] data_type1,
  argument2 [mode] data_type2,
... ... )

IS [AS]
...
BEGIN
...
EXCEPTION
...
END;
/

- 프로시저는 절차적인 언어를 이용하여 작성한 프로그램 모듈, 필요할 때 호출하여 실행 가능

- OR REPLACE는 디비내에 같은 이름의 프로시저가 있을 경우, 기존 것을 무시하고 대체하는 것

- Argument는 프로시저가 호출될 때 처리한 결과 값을 운영체제로 리턴시킬 매개변수를 지정할 때 사용

- [mode]에 가능한 값은 3가지

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

  OUT : 프로시저에 운영체제로 전달

  INOUT : 둘다 가능

- / : 데이터베이스에게 프로시저를 컴파일 하라는 명령

 

- T-SQL 개요

T-SQL은 근본적으로 SQL Server를 제어하기 위한 언어로, SQL에 약간의 기능을 더 추가해 보완적으로 만든 것이다. 이를 이용하여 다양한 저장모듈을 개발할 수 있다.

 

- T-SQL 특징

- 전역변수 선언은 @@, 지역변수 선언은 @

- 전역변수는 이미 SQL 서버에 내장된 값, 지역변수는 사용자의 연결시간동안만 사용하기위해 만들어짐

- int, float, varchar 등의 자료형 사용 가능

- 연산자 사용 가능

- IF-ELSE, WHILE, CASE-THEN 등의 흐름 제어 기능

- 주석 기능은 '--, /**/' 으로 사용 가능

 

- T-SQL 구조 및 문법

 

CREATE Procedure [schema_name.]Procedure_name
@parameter1 datatype1 [mode],
@prarmeter2 datatype2 [mode],
...
WITH<proc_option>
AS
...
BEGIN
...
ERROR
...
END;

- 프로시저의 변경이 필요한 경우 오라클은 CREATE OR REPLACE와 같이 하나의 구문으로 처리하지만, SQL Server의 경우 CREATE 구문을 ALTER 구문으로 변경하여 수행해야한다. 

- [mode] 부분에 적용할 수 있는 매개변수의 유형은 4가지가 있다.

  VARYING : 결과집합이 출력 매개변수로 사용되도록 지정한 CURSOR 매개변수에만 적용

  DEFAULT : 지정된 기본값으로 처리

  OUT, OUTPUT : 처리된 결과 값을 EXECUTE 문 호출 시 반환

  READONLY : 자주 사용되진 않지만, 매개변수를 업데이트하거나 수정할 수 없음

- WITH 부분에 지정할 수 있는 옵션은 3가지가 있다.

  RECOMPILE : 현재 프로시저의 계획을 캐시하지 않고 프로시저가 런타임에 컴파일 됨

  ENCRYPTION : CREATE PROCEDURE 문의 원본 텍스트가 알아보기 어려운형식으로 변환됨

  EXECUTE AS : 해당 프로시저를 실행할 보안 컨텍스트를 지정

- Procedure의 생성과 활용

 

EX) SCOTT 유저가 소유하고 있는 DEPT 테이블에서 새로운 부서를 등록하는 Procedure를 작성한다.

 

<Oracle>

CREATE OR REPLACE PROCEDURE p_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 DEPT
 WHERE DEPTNO=v_DETPNO AND ROWNUM=1;
 IF CNT>0 THEN
 v_result :='이미 등록된 부서번호이다';
 else
 INSERT INTO DEPT(DEPTNO, DNAME, LOC)
 VALUES(v_DEPTNO, v_dname, v_loc);
 COMMIT;
 v_result:='입력 완료!!';
 end if;
EXCEPTION
  WHEN OTHERS THEN
  ROLLBACK;
  v_result := 'ERROR 발생!';
END;

* SELECT COUNT(*) INTO CNT FROM DEPT -> 데이터베이스에서 수행된 결과 값을 변수에 저장

 

 

<SQL Server(T-SQL)>

CREATE Procedure dbo.p_DEPT_insert
  @v_DEPTNO int,
  @v_dname varchar(30),
  @v_loc varchar(30),
  @v_result varchar(100) OUTPUT
AS
DECLARE @cnt int
SET @cnt = 0
BEGIN
  SELECT @cnt=COUNT(*)
  FROM DEPT
  WHERE DEPTNO = @v_DEPTNO
  IF @cnt >0
    BEGIN
    SET @v_result = '이미 등록된 부서번호이다'
    RETURN
    END
  ELSE
  BEGIN
  BEGIN TRAN
  INSERT INTO DEPT (DEPTNO, DNAME, LOC)
  VALUES (@v_DEPTNO, @v_dname, @v_loc)
  IF @@ERROR <> 0
    BEGIN
    ROLLBACK
    SET @v_result = 'ERROR 발생'
    RETURN
    END
  ELSE
    BEGIN
    COMMIT
    SET @v_result = '입력 완료!'
    RETURN
    END
  END
END

 

1. cnt 변수는 SCALAR 변수, 사용자의임시 데이터를 하나만 저장 할 수 있는 변수, 모든 데이터 유형 가능

2. PL / SQL 에서 사용하는 SELECT 문은 결과값이 반드시 있어야 하고, 하나여야한다. T-SQL은 결과 값이 없어도 에러가 발생하지 않는다.

3. T-SQL은 대입 연산자 '='를 사용하지만, PL/SQL은 대입연산자 ':='를 사용

4. OTHERS를 이용하여 에러처리가 가능하지만, 웬만하면 정확하게 처리하는게 좋음

 

 

다음으로 지금까지 작성한 프로시저를 실행하여 기능을 테스트한 과정이다.

 

<Oracle>

SELECT * FROM DEPT;                
variable rslt varchar2(30);
EXECUTE p_DEPT_insert(10, 'dev', 'seoul', :rslt);
print rslt;

 

<SQL Server>

SELECT * FROM DEPT;
DECALRE @v_result VARCHAR(100)
EXECUTE dbo.p_DEPT_insert 10, 'dev', 'seoul', @v_result = @v_result OUTPUT
SELECT @v_result AS RSLT

 

 

- User Defined Function의 생성과 활용

User Defined Functiond은 Procedure 처럼 절차형 sql을 로직과 함께 데이터베이스 내에 저장해 놓은 명령문 집합

SUM. SUBSTR 등의 함수는 벤더에서 미리 만들어둔 내장함수이고, 사용자가 별도의 함수를 만들 수 있다.

 

※ Function이 Procedure와 다른점은 RETURN을 이용하여 하나의 값을 반드시 되돌려 줘야한다.

 

EX. ABS 사용자 함수 만들고 사용하기

 

<Oracle>

CREATE OR REPLACE Function UTIL_ABS(v_input in number)
return NUMBER
IS
  v_return number :=0;
BEGIN
  if v_input < 0 then
  v_return := v_input * -1;
  else 
  v_return := v_input;
  end if;
  RETURN v_return;
END;

 

<SQL Server>

CREATE Function dbo.UTIL_ABS(@v_input int)
  RETURNS int
AS
BEGIN
  DECLARE @v_return int
  SET @v_return = 0
  IF @v_input < 0
  SET @v_return = @v_intput * -1
  ELSE
  SET @v_return = @v_input
  RETURN @v_return;
END

 

- Trigger의 생성과 활용

트리거란 특정한 테이블에 insert, update, delete와 같은 dml이 수행되었을 때, 데이터베이스에서 자동으로 동작하도록 작성된 프로그램

 

[SQLD] 트리거(TRIGGER) 예제

 

 

 

- 프로시저와 트리거의 차이점

프로시저는 begin~end절 내에 commit, rollback과 같은 트랜잭션 종료 명령어가 사용 가능하지만,

데이터베이스 트리거는 begin~end 절 내에 사용할 수 없다.