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

박진우·2022년 6월 8일
0

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-ELSEWHILE, 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는 데이터베이스 보안의 적용, 유효하지 않은 트랜잭션의 예방, 업무 규칙 자동 적용 제공 등에 사용될 수 있다.


◽ 프로시저와 트리거의 차이

  • 프로시저 : BEGIN ~ END 절 내에 COMMIT, ROLLBACK과 같은 트랜잭션 종료 명령어를 사용 가능

  • 데이터베이스 트리거는 BEGIN ~ END 절 내에 COMMIT, ROLLBACK사용할 수 없다.


0개의 댓글