[SQLD] 28. 절차형 SQL

제 8절 절차형 SQL

1. 절차형 SQL 개요

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

2. PL / SQL 개요

가. PL / SQL 특징

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

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

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

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

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

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

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

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

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

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

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

- SQL Statement Executor : SQL 문장 처리

나. PL / SQL 구조

다. PL / SQL 기본 문법

CREATE [OR REPLACE] Procedure [Procedure_name]

( argument1 [mode] data_type1,

 argument2 [mode] data_type2,

... ... )

IS [AS]

...

BEGIN

...

EXCEPTION

...

END;

/

다음은 생성된 프로시저를 삭제하는 명령어이다.

DROP Procedure [Procedure_name];

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

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

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

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

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

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

  INOUT : 둘다 가능

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

3. T-SQL 개요

가. T-SQL 특징

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

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

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

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

- 연산자 사용 가능

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

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

나. T-SQL 구조

다. T-SQL 기본 문법

CREATE Procedure [schema_name.]Procedure_name

@parameter1 datatype1 [mode],

@prarmeter2 datatype2 [mode],

...

WITH<proc_option>

AS

...

BEGIN

...

ERROR

...

END;

다음은 프로시저를 삭제하는 명령이다.

DROP Procedure [schema_name.]Procedure_name;

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

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

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

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

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

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

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

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

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

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

4. Procedure의 생성과 활용

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

오라클(PL/SQL)

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_DEPTNO 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;

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를 이용하여 에러처리가 가능하지만, 웬만하면 정확하게 처리하는게 좋음

기능 테스트 (오라클)

SELECT * FROM DEPT;                

variable rslt varchar2(30);

EXECUTE p_DEPT_insert(10, 'dev', 'seoul', :rslt);

print rslt;

테이블조회

프로시듈을 실행한 겨로가 값을 받을 변수를 선언

존재하는 데이터를 가지고 프로시저 실행

출력(이미 존재하는 데이터)

기능 테스트(SQL Server, SQL Server MANAGEMENT STUDIO 이용)

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

테이블 조회

프로시저 실행한 결과 값을 받을 변수 선언

존재하는 데이터를 가지고 프로시저 실행

출력(이미 존재하는 데이터)

5. User Defined Function의 생성과 활용

  User Defined Function은 Procedure 처럼 절차형 SQL을 로직과 함께 데이터베이스 내에 저장해 놓은 명령문의 집합을 의미한다. SUM, SUBSTR 등의 함수는 벤더에서 미리 만들어둔 내장함수이고, 사용자가 별도의 함수를 만들 수 있다. 

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

예제) ABS 사용자 함수 만들고 사용하기

오라클

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

사용하기 - 오라클

SELECT SCHE__DATE 경기일자,

HOMETEAM_ID || '-' || AWAYTEAM_ID 팀들,

HOME_SCORE || '-' || AWAY_SCORE SCORE,

UTIL_ABS(HOME_SCORE - AWAY_SCORE) 점수차

FROM SCHEDULE

WHERE GUBUN = 'Y'

AND SCHE_DATE BETWEEN '20120801' AND '20120831'

ORDER BY SCHE_DATE;

사용하기 - SQL Server

SELECT SCHE__DATE 경기일자,

HOMETEAM_ID + '-' + AWAYTEAM_ID AS 팀들,

HOME_SCORE + '-' + AWAY_SCORE AS SCORE,

dbo.UTIL_ABS(HOME_SCORE - AWAY_SCORE) AS 점수차

FROM SCHEDULE

WHERE GUBUN = 'Y'

AND SCHE_DATE BETWEEN '20120801' AND '20120831'

ORDER BY SCHE_DATE;

6. Trigger의 생성과 활용

  트리거란 특정한 테이블에 INSERT, UPDATE, DELETE와 같은 DML문이 수행되었을 때, 데이터베이스에서 자동으로 동작하도록 작성된 프로그램이다. 즉, 사용자가 직접 호출하여 사용하는 것이 아니고 데이터베이스에서 자동적으로 수행하게 된다. 

  트리거는 테이블, 뷰, 데이터베이스 작업을 대상으로 정의할 수 있으며, 전체 트랜잭션 작업에 대해 발생되는 트리거와 각 행에 대해서 발생되는 트리거가 있다. 

예제) 트리거를 사용하여 주문한 건이 입력될 때마다, 일자별 상품별로 판매수량과 판매금액을 집계하여 집계자료를 보관하도록 한다.

오라클

CREATE OR REPLACE Trigger SUMMARY_SALES

AFTER INSERT

ON ORDER_LIST

FOR EACH ROW

DECLARE

o_date ORDER_LIST.order_data%TYPE;

o_prod ORDER_LIST.product%TYPE;

BEGIN

o_data := :NEW.order_data;

o_prod:= :NEW.product;

UPDATE SALES_PER_DATE

SET qty = qty + :NEW.qty,

amount = amount + :NEW.amout

WHERE sale_data = o_date

AND product = o_prod;

IF SQL%NOTFOUND then

INSERT INTO SALES_PER_DATE

VALUES(o_date, o_prod, :NEW_qty, :NEW.amout);

end if;

END;

/

SQL Server

CREATE Trigger dbo.SUMMARY_SALES

ON ORDER_LIST

AFTER INSERT

AS

DECLARE
    @o_date DATETIME, @o_prod INT, @qty int, @amount int

BEGIN

SELECT @o_date = order_date, @o_prod = product, @qty=qty, @amount = amout

FROM inserted

UPDATE SALES_PER_DATE

SET qty = qty + @qty,

amount = amount + @amout

WHERE sale_date = @o_date

AND product = @_prod;

IF @@ROWCOUNT = 0

INSERT INTO SALES_PER_DATE

VALUES(@o_date, @o_prod, @qty, @amount)

END;

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

프로시저는 BEGIN~END 절 내에 COMMIT, ROLLBACK과 같은 트랜잭션 종료 명령어를 사용할 수 있지만, 데이터베이스 트리거는 BEGIN~END 절 내에 사용할 수 없다.