[SQLD] 2과목 SQL 기본 및 활용 -절차형 SQL

박진우·2022년 6월 8일

2과목 PL/SQL SQLD TRIGGER procedure tsql 절차형 SQL 트리거 프로시저

SQLD

목록 보기

17/21

💡 절차형 SQL

절차형 SQL의 개요

SQL에도 절차 지향적인 프로그램이 가능하도록 DBMS 벤더별로
PL(Procedural Language)/SQL(Oracle), SQL/PL(DB2), T-SQL(SQL Server) 등의 절차형 SQL을 제공하고 있다.

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

저장 모듈[Stored Module]

SQL문을 데이터베이스 서버에 저장해 사용자와 애플리케이션 사이에 공유할 수 있도록 만든 SQL 컴포넌트 프로그램

독립적으로 실행되거나 다른 프로그램으로부터 실행될 수 있는 완전한 실행 프로그램
오라클의 저장모듈에는 Procedure, User Defined Function, Trigger가 있다.

◽ PL/SQL(Oracle)

◾PL/SQL의 특징

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

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

◾PL/SQL의 구조

DECLARE

· 필수

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

· 선언: 인수 및 상수등 정의 및 데이터 타입 선언.

BEGIN ~ END

· 필수

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

· 실행: 목적한 SQL실행 및 필요로직

EXCEPTION

· 선택

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

· 에러처리: 실행 중 발생하는 에러 처리

◾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] date_type2,
... ... )

IS [AS]

... ...
BEGIN

... ...
EXCEPTION

... ...
END;
/

CREATE로 생성한 프로시저는 DB에 저장된다.

자주 실행하는 로직을 절차적인 언어로 작성한 프로그램 모듈이므로, 필요할 때 호출하여 실행할 수 있다.
[OR REPLACE] 는 이미 프로시저가 존재하는 경우, 기존 프로시저에 덮어쓰기를 하겠다는 의미이다.
Argument의 mode에는 3가지가 들어올 수 있다. (IN, OUT, INOUT)
- IN : OS에서 프로시저로 전달될 변수
- OUT : 프로시저에서 처리된 결과가 OS로 전달될 변수
- INOUT : IN, OUT 모드를 동시에 수행
'/' 는 DB에게 프로시저를 컴파일하라는 명령어.

프로시저를 삭제하는 명령어

DROP Procedure [Procedure_name];

◽ T/SQL(SQL Server)

T-SQL은 근본적으로 SQL Server를 제어하기 위한 언어로서, MS 사에서 ANSI/ISO 표준의 SQL 에 약간의 기능을 추가해 보완적으로 만든 것 이다.

◾T/SQL의 특징

변수 선언 기능 @@ 이라는 전역변수(시스템함수)와 @이라는 지역변수가 있다.
지역변수는 사용자가 자신의 연결 시간 동안만 사용하기 위해 만들어지는 변수이며 전역변수는 이미 SQL서버에 내장된 값이다.
데이터 유형(Data Type)을 제공한다. 즉, int, float, varchar 등의 자료형 의미
연산자(Operator) 산술연산자(+,-,*,/)와, 비교연산자(=,<,>,<>), 논리연산자(and, or, not) 사용이 가능하다.
흐름 제어 기능 IF-ELSE와 WHILE, CASE-THEN 사용이 가능하다.
주석 기능 한줄주석( – ), 범위주석(/* */)

◾T/SQL의 구조

DECLARE : BEGIN ~ END 절에서 사용될 변수와 인수에 대한 정의 및 데이터 타입을 선언하는 선언부이다.
BEGIN ~ END : 개발자가 처리하고자 하는 SQL문과 비교문, 제어문을 이용하여 필요한 로직을 처리하는 실행부이다.
ERROR 처리 : BEGIN ~ END 절에서 실행되는 SQL문이 실행될 때 에러가 발생하면 그 에러를 어떻게 처리할 것이지를 정의하는 예외 처리부이다.
T-SQL에서는 BEGIN, END 문을 반드시 사용해야하는 것은 아니다.
블록 단위로 처리하고자 할 때는 반드시 작성 해야 한다.

◾T/SQL의 기본문법

CREATE Procedure [schema_name.]Procedure_name
@parameter1 data_type1 [mode],
@parameter2 date_type2 [mode],
... ...
WITH
AS
... ...
BEGIN
... ...
ERROR 처리
... ...
END;

CREATE TABLE 명령어로 테이블을 생성하듯 CREATE 명령어로 데이터베이스 내에 프로시저를 생성한다.
프로시저의 변경이 필요할 경우 Oracle은 [CREATE OR REPLACE]와 같이 하나의 구문으로 처리한다.
SQL Server는 CREATE 구문을 ALTER 구문으로 변경한다.
@parameter는 프로시저 호출 시 프로시저로 어떤 값이 들어오거나 처리한 결과 값을 리턴 시킬 매개 변수를 지정한다.

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

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

  1️⃣ RECOMPILE데이터베이스 엔진에서 현재 프로시저의 계획을 캐시하지 않고 프로시저가 런타임에 컴파일 된다.

  데이터베이스 엔진에서 저장 프로시저 안의 개별 쿼리에 대한 계획을 삭제할 때 RECOMPILE 쿼리 힌트를 사용한다.

  2️⃣ ENCRYPTIONCREATE PROCEDURE 문의 원본 텍스트가 알아보기 어려운 형식으로 변환된다.

  변조된 출력은 SQL Server의 카탈로그 뷰 어디에서도 직접 표시되지 않는다.

  원본을 볼 수 있는 방법이 없기 때문에 반드시 원본은 백업을 해두어야 한다.

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

▪️ [mode] 부분에 지정할 수 있는 매개 변수(@parameter)4가지 유형

1️⃣ VARYING결과 집합이 출력 매개 변수로 사용되도록 지정합니다. CURSOR 매개변수에만 적용된다.

2️⃣ DEFAULT지정된 매개변수가 프로시저를 호출할 당시 지정되지 않을 경우 지정된 기본값으로 처리한다.

    ➡️ 즉, 기본 값이 지정되어 있으면 해당 매개 변수 지정하지 않아도 프로시저가 지정된 기본 값으로 정상적으로 수행한다.

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

4️⃣ READONLY : 자주 사용 X

◽ Procedure의 생성과 활용

◽ User Defined Function의 생성과 활용

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

◽ 트리거(Trigger)

DML문 수행 시, 데이터베이스에 의해 자동으로 호출되고 수행되도록 작성된 저장 프로그램

방아쇠라는 사전적 의미를 가진 Trigger는 특정 조건을 만족하면 저절로 실행된다는 의미이다.
테이블과 뷰, 데이터베이스 작업을 대상으로 정의 가능하다.
데이터베이스에 로그인하는 작업도 정의 가능하다.
행 트리거[테이블의 행이 각각 실행]와 문장 크리거[INSERT, UPDATE, DELETE문이 한 번만 수행]

◽ Trigger의 생성과 활용

특정한 테이블에 INSERT, UPDATE DELETE와 같은 DML문이 수행되었을 때, 데이터베이스에서 자동으로 동작하도록 작성된 프로그램
즉 사용자가 직접 호출하여 사용하는 것이 아니고 데이터베이스에서 자동적으로 수행하게 된다.
Trigger는 테이블과 뷰, 데이터베이스 작업을 대상으로 정의할 수 있으며, 전체 트랜잭션 작업에 대해 발생되는 Trigger와 각 행에 대해서 발생되는 Trigger가 있다.
Trigger는 데이터베이스에 의해 자동 호출되지만 결국 INSERT, UPDATE, DELETE 문과 하나의 트랜잭션 안에서 일어나는 일련의 작업들이라 할 수 있다.
Trigger는 데이터베이스 보안의 적용, 유효하지 않은 트랜잭션의 예방, 업무 규칙 자동 적용 제공 등에 사용될 수 있다.