SQLD - SQL 활용 [절차형 SQL]

1.   절차형 SQL 개요

-       일반적인 개발 언어처럼 SQL에도 절차 지향적인 프로그램이 가능하도록 DBMS 벤더별로 절차형 SQL 제공

n  Oracle : PL(Procedural Language)/SQL

n  DB2 : SQL/PL

n  SQL Server : T-SQL

-       절차형 SQ을 이용하면 SQL문의 연속적인 실행이나 조건에 따른 분기처리를 이용하여 특정 기능을 수행하는 저장 모듈 생성가능

-       절차형 SQL을 이용하여 만들수 있는 저장모듈

n  Procedure

n  User Defined Function

n  Trigger

 

2.   PL/SQL 개요

가.  PL/SQL 특징

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

-       PL/SQL을 이용하여 다양한 저장 모듈(Stored Module)을 개발할 수 있다.

-       저장모듈이란? PL/SQL문장을 데이터베이스 서버에 저장하여 사용자와 애플리케이션 사이에서 공유할 수 있도록 만든 일종의 SQL 컴포넌트 프로그램이며, 독립적으로 실행되거나 다른 프로그램으로부터 실행될 수 있는 완전한 실행 프로그램이다.

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

-       PL/SQL의 특징

n  PL/SQL은 Block구조로 되어 있어 각 기능별로 모듈화가 가능하다.

n  변수, 상수 등을 선언하여 SQL 문장 간 값을 교환한다.

n  IF, LOOP 등의 절차형 언어를 사용하여 절차적인 프로그램이 가능하도록 한다.

n  DBMS 정의 에러나 사용자 정의 에러를 정의하여 사용할 수 있다.

n  PL/SQL은 Oracle에 내장되어 있으므로 Oracle과 PL/SQL을 지원하는 어떤 서버로도 프로그램을 옮길 수 있다.

n  PL/SQL은 응용 프로그램의 성능을 향상시킨다.

n  PL/SQL은 여러 SQL문장을 Block으로 묶고 한 번에 Block 전부를 서버로 보내기 때문에 통신량을 줄일 수 있다.

 

나.  PL/SQL 구조

-       DECLARE : BEGIN ~ END 절에서 사용할 변수와 인수에 대한 정의 및 데이터 타입을 선언하는 선언부 이다.

-       BEGIN ~ END : 개발자가 처리하고자 하는 SQL문과 여러 가지 비교문, 제어문을 이용하여 필요한 로직을 처리하는 실행부이다.

-       EXCEPTION : BEGIN ~ END 절에서 실행되는 SQL문이 실행될 때 에러가 발생하면 그 에러를 어떻게 처리할 것인지를 정의하는 예외 처리부이다.

 

다.  PL/SQL 기본 문법(Syntax)

-       Procedure 생성

CREATE OR REPLACE Procedure Procedure_name (argument1 mode data_type1, argument2 mode date_type2, … …) IS AS … … BEGIN … … EXCEPTION … … END;/

-       Procedure 삭제
DROP Procedure Procedure_name

-       CREATE 명령어로 데이터베이스 내에 프로시저를 생성할 수 있다.

-       OR REPLACE절은 데이터베이스 내에 같은 이름의 프로시저가 있을 경우, 기존의 프로시저를 무시하고 새로운 내용을 덮어쓰기 하겠다는 의미이다.

-       mode부분에 지정할 수 있는 매개변수의 유형 3가지

n  IN : 운영체제에서 프로시저로 전달될 변수의 MODE

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

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

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

 

3.   T-SQL 개요

가.  T-SQL 특징

T-SQL은 근본적으로 SQL Server를 제어하기 위한 언어이다.

-       변수 선언 기능, 전역변수(시스템 함수)와 지역변수가 있다.

n  지역변수는 사용자가 자신의 연결 시간동안만 사용하기 위해 만들어지는 변수이다.

n  전역변수는 이미 SQL 서버에 내장된 값이다.

-       데이터 유형(Data Type)을 제공한다. 즉 int, float, varchar 등의 자료형을 의미한다.

-       연산자(Operator), 산술연산자(+, -, *, /)와 비교연산자(=, <, >, <>) 논리연산자(and, or, not) 사용이 가능하다.

-       흐름 제어 기능 IF-ELSE와 WHILE, CASE-THEN 사용이 가능하다.

-       주석 기능, 한줄주석 : -- 뒤의 내용은 주석범위
               주석 : /* 내용 */ 형태를 사용하며, 여러 줄도 가능하다.

 

나.  T-SQL 구조

-       DECLARE : BEGIN ~ END 절에서 사용될 변수와 인수에 대한 정의 및 데이터 타입을 선언하는 선언부이다.

-       BEGIN ~ END : 개발자가 처리하고자 하는 SQL문과 여러 가지 비교문, 제어문을 이용하여 필요한 로직을 처리하는 실행부이다. T-SQL에서는 BEGIN, END 문을 반드시 사용해야 하는 것은 아니지만, 블록 단위로 처리하고자 할 때는 반드시 작성해야 한다.

-       ERROR 처리 : BEGIN ~ END절에서 실행되는 SQL문이 실행될 때 에러가 발생하면 그 에러를 어떻게 처리할 것인지를 정의하는 예외 처리부이다.

 

다.  T-SQL 기본 문법(Syntax)

-       Procedure 생성
CREATE Procedure schema_name.Procedure_name @parameter1 data_type1 mode, @parameter2 data_type2 mode, … … WITH AS … … BEGIN … … ERROR 처리 … … END;

-       Procedure 삭제
DROP Procedure schema_name.Procedure_name;

-       mode부분에 지정할 수 있는 매개변수(@parameter)의 유형 4가지

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

n  DEFAULT : 지정된 매개변수가 프로시저를 호출할 당시 지정되지 않을 경우 지정된 기본값으로 처리한다. 즉, 기본 값이 지정되어 있으면 해당 매개변수를 지정하지 않아도 프로시저가 지정된 기본 값으로 정상적으로 수행이 된다.

n  OUT, OUTPUT : 프로시저에서 처리된 결과 값을 EXECUTE문 호출 시 반환한다.

n  READONLY : 자주 사용되지는 않는다. 프로시저 본문 내에서 매개 변수를 업데이트하거나 수정할 수 없음을 나타낸다. 매개변수 유형이 사용자 정의 테이블 형식인 경우 READONLY를 지정해야한다.

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

n  RECOMPILE : 데이터베이스 엔진에서 현재 프로시저의 계획을 캐시하지 않고 프로시저가 런타임에 컴파일된다. 데이터베이스 엔진에서 저장 프로시저 안에 있는 개별 쿼리에 대한 계획을 삭제하려 할 때 RECOMPILE 쿼리 힌트를 사용한다.

n  ENCRYPTION : CREATE PROCEDURE문의 원본 텍스트가 알아보기 어려운 형식으로 변환된다. 변조된 출력은 SQL Server의 카탈로그 뷰 어디에서도 직접 표시되지 않는다. 원본을 볼 수 있는 방법이 없기 때문에 반드시 원본은 백업을 해두어야 한다.

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

4.   Procedure의 생성과 활용

주의해야할 몇가지 문법적 요소

1.     PL/SQL 및 T-SQL에서는 다양한 변수가 있다. SCALAR 변수는 사용자의 임시 데이터를 하나만 저장할 수 있는 변수이며 거의 모든 형태의 데이터 유형을 지정할 수 있다.

2.     PL/SQL에서 사용하는 SQL 구문은 대부분 지금까지 살펴본 것과 동일하게 사용할 수 있지만 SELECT 문장은 다르다. PL/SQL에서 사용하는 SELECT 문장은 결과 값이 반드시 있어야 하며, 그 결과 역시 반드시 하나여야 한다. 조회 결과가 없거나 하나 이상인 경우에는 에러를 발생시킨다. T-SQL에서는 결과 값이 없어도 에러가 발생하지 않는다.

3.     T-SQL을 비롯하여 일반적으로 대입 연산자는 “=”을 사용하지만 PL/SQL에서는 “:=”를 사용한다.

4.     에러 처리를 담당하는 EXCEPTION에는 WHEN ~ THEN 절을 사용하여 에러의 종류별로 적절히 처리한다. OTHERS를 이용하여 모든 에러를 처리할 수 있지만 정확하게 에러를 처리하는 것이 좋다.

 

5.   User Defined Function의 생성과 활용

-       User Defined Function은 Procedure처럼 절차형 SQL을 로직과 함께 데이터베이스 내에 저장해 놓은 명령문의 집합을 의미한다.

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

-       즉, Function은 Procedure와는 달리 SQL문장에서 특정 작업을 수행하고 반드시 수행 결과 값을 리턴한다

 

6.   Trigger의 생성과 활용

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

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

 

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

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

프로시저	트리거
CREATE Procedure 문법사용	CREATE Trigger 문법사용
EXECUTE 명령어로 실행	생성 후 자동으로 실행
COMMIT, ROLLBACK 실행 가능	COMMIT, ROLLBACK 실행 안됨