1. 절차형 SQL 개요

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

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

 

2. PL/SQL 개요

 

.  특징

블럭구조로 되어있고, DML문장, QUERY문장, 절차형 언어(IF, LOOP)등이 사용하여 절차적 프로그래밍 기능을 하게 트랜잭션 언어

저장모듈 : PL/SQL 문장을 데이터 베이스 서버에 저장하여 사용자와 어플리케이션 사이에 공유할 있도록 만든 SQL 컴포넌트 프로그램

 독립적으로 또는 프로그램으로부터 실행 있는 완전한 실행 프로그램

 

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

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

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

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

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

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

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

 


프로그램 문장은 PL/SQL 엔진이 처리하고

SQL 문장은 ORACLE 서버의 SQL STATEMENT EXECUTOR 실행하도록 작업 분리하여 처리

 

. 구조


  • DECLARE : BEGIN ~ END 절에서 사용될 변수와 인수에 대한 정의 및 데이터 타입을 선언하는 선언부
  • BEGIN ~ END : 개발자가 처리하고자 하는 SQL문과 여러 가지 비교문, 제어문을 이용하여 필요한 로직을 처리하는 실행부
  • EXCEPTION : BEGIN ~ END 절에서 실행되는 SQL문이 실행될 때 에러가 발생하면 그 에러를 어떻게 처리할 것이지를 정의하는 예외 처리부

 

. 기본문법

CREATE [OR REPLACE] Procedure [Procedure_name]

( argument1 [mode] data_type1, argument2 [mode] date_type2, ... ... )

IS [AS] ... ...

BEGIN ... ...

EXCEPTION ... ...

END;

/

  • OR REPLCAE : 같은 이름의 프로시저가 있을 경우 덮어쓰기
  • ARGUMENT : 프로시저 안으로 어떤 값이 들어오거나 혹은 처리한 결과값을 리턴시킬 매개 변수 저장
  • MODE : 매개변수 3가지 유형 IN, OUT, INOUT
  • / : 마지막으로 데이터 베이스에게 프로시저 컴파일하라는 명령어

 

프로시저 삭제

DROP Procedure [Procedure_name];

 

3. T-SQL 개요

 

. 특징

SQL SERVER 제어하기 위한 언어, ANSI표준에 약간의 기능을 추가해 보완적으로 만든

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

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

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

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

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

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

 

. 구조


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

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

      T-SQL에서는 BEGIN, END 문을 반드시 사용해야하는 것은 아니지만 블록 단위로 처리하고자 할 때는 반드시 작성

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

 

. 기본 문법

CREATE Procedure [schema_name.]Procedure_name

@parameter1 data_type1 [mode],

@parameter2 date_type2 [mode],

... ...

WITH 

AS ... ...

BEGIN ... ...

ERROR 처리 ... ...

END;

 

  • 변경이 필요한 경우는 ALTER 구분으로 변경
  • @PARAMETER 값이 들어오거나 프로시저에서 처리한 값을 리턴 시킬 매개 변수 지정할 사용
  • MODE 부분의 매개변수 4가지 유형

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

② DEFAULT : 지정된 매개변수가 프로시저를 호출할 당시 지정되지 않을 경우 지정된 기본값으로 처리

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

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

④ READONLY : 자주 사용되지는 않음 프로시저 본문 내에서 매개 변수를 업데이트하거나 수정할 수 없음

   매개 변수 유형이 사용자 정의 테이블 형식인 경우 READONLY를 지정

  • WITH 부분 3가지 옵션

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

    엔진에서 저장 프로시저 안에 있는 개별 쿼리에 대한 계획을 삭제하려 할 때 RECOMPILE 쿼리 힌트를 사용

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

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

 원본을 볼 수 있는 방법이 없기 때문에 반드시 원본은 백업 필요

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

 

 

프로시저 삭제 명령

DROP Procedure [schema_name.]Procedure_name;

 

4. PROCEDURE 생성과 활용

 

주의해야할 문법적 요소

  1. 다양한 변수의 유형
  2. SQL SELECT 문장이 다름, 결과값이 반드시 있어야 하며 하나 있어야함 (T-SQL 결과값이 없어도 에러 미발생)
  3. 대입 연사자 ":="사용 ( 일반적으로 "=" 사용)
  4. 예외처리부에서 WHEN ~ THEN 사용하여 종류별 처리 가능

 

5. USER DEFINED FUNCTION 생성과 활용

절차형 SQL 로직과 함께 데이터베이스 내에 저장해 놓는 명령문의 집합

PROCEDURE 다른점은 RETURN 사용해서 하나의 값을 반드시 되돌려 주어야

SQL 문장에서 특정 작업을 수행하고 반드시 수행결과 값을 리턴해야함

 

6. TRIGGER 생성과 활용

특정한 테이블에 INSERT, UPDATE, DELETE 같은 DML문이 수행되었을 , 자동으로 동작하도록 작성된 프로그램

사용자가 직접호출하는 것이 아니라 자동적으로 수행하게

테이블과 , 데이터베이스 작업을 대상으로 정의할 있음

전체 트랜잭션작업에 대해 발생되는 TRIGGER, 행에 대해서 발생되는 TRIGGER 있음

데이터베이스 보안의 적용, 유효하지 않은 트랜잭션 예방, 업무규칙 자동적용제공 등에 사용가능하다.

 

7. PROCEDURE TRIGGER 차이

PROCEDURE

TRIGGER

CREATE PROCEDURE 문법 사용

CREATE TIGGER 문법사용

EXECUTE 명령어로 실행

생성 자동 실행

COMMIT, ROLLBACK 실행가능

COMMIT, ROLLBACK 실행불가


'자격증 공방 > SQLD' 카테고리의 다른 글

제 5-2절 인덱스 기본  (0) 2018.07.17
제 5-1절 옵티마이저와 실행계획  (0) 2018.07.17
제 4-7절 DCL  (0) 2018.07.11
제 4-6절 윈도우 함수(WINDOW 함수)  (0) 2018.07.11
제 4-5절 그룹함수  (0) 2018.07.11

+ Recent posts