정보처리기사 13일차

2. 소프트웨어 개발

 2.1 데이터 입출력 구현

  2.1.4 데이터 조작 프로시저 작성

---

1. 프로시저

데이터 조작을 위해서 사용하는 언어를 SQL이라고 하는데 SQL은 일정한 데이터 집합으로부터 보다 쉽게 자료를 검색하고 입력, 수정, 삭제와 같은 조작을 할 수 있도록 고안된 언어를 말한다.

프로시저는 이러한 SQL을 이용하여 만들어진 데이터를 조작하는 프로그램이다. 프로시저는 데이터베이스 내부에 저장되고 일정한 조건이 되면 자동으로 수행된다.

 

1.1 SQL 분류

 

1. 데이터 정의어 (DDL)

데이터를 저장하고 있는 테이블 등의 구조를 생성하고 변경하기 위하여 사용되는 명령어 들을 의미한다.

Create, Drop, Rename, Alter, Truncate 등이 있다.

 

2. 데이터 조작이 (DML)

데이터베이스에 있는 데이터를 변경하거나 검색하기 위하여 사용되는 명령어들을 말하며, 이 명령어는 트랜잭션 제어 어를 활용하여 실행 전 상태로 복귀 가능한 명령어이다.

Insert, Update, Delete 등이 있다.

 

3. 데이터 제어어 (DCL)

사용자별로 데이터베이스에 접근할 수 있는 권한을 부여하거나 회수하는 명령어들을 말한다.

Role, Grant, Revoke 등이 있다.

 

1.2 트랜잭션 제어어 (TCL)

 

1. 트랜잭션 제어어란?

트랜잭션 제어 어는 트랜잭션의 DML 작업 단위를 제어하는 명령어이다.

Commit, Rollback, Savepoint 등이 있다.

 

2. Commit 

트랜잭션을 완료하여 데이터 변경사항을 최종 반영하는 명령어이다.

Commit이후에는 데이터에 대한 변경 사항이 물리적 디스크에 반영된다.

Commit이 되면 이전 데이터는 복구할 수 없다.

모든 사용자가 변경된 결과를 볼 수 있다.

관련된 행에 대해 잠금이 풀리며 다른 사용자들이 조작할 수 있다.

 

3. Rollback

데이터 변경사항을 이전 상태로 되돌리는 명령어이다.

Rollback이후 데이터 상태는 명령문을 수행하기 전 상태로 되돌려진다.

관련된 행에 대한 잠금이 풀리게 된다.

 

4. Savepoint

지정된 특정 시점까지 Rollback 할 수 있는 명령어이다.

Savepoint A; 이렇게 명령할 경우 A지점까지 Rollback이 된다.

 

1.3 데이터 검색어(Select)

구분	내용
문법	Select [distinct] {*, column [alias], .} From table_name [Where condition] [Group by column] [Having condition] [Order by{column, expression] [Asc | Desc] ] ;
항목설명	distinct : 중복되는 행을 제거하는 옵션 * : 테이블의 모든 Column을 출력 alias : 해당 column에 대해서 다른 이름을 부여할 때 사용 table_name : 질의 대상 테이블 명 Where : 조건을 만족하는 행들만 검색, condition은 column, 표현식, 상수 및 비교연산자를 혼합하여 사용한다 Group by : 그루핑하고자 하는 단위 지정 Having : 그룹핑한 결과값에 대한 조건 검색 Order by : 질의 결과 정렬을 위한 옵션

1.4 절차형 데이터 조작 프로시저 PL/SQL

 

1. PL/SQL 개요

표준 SQL을 기본으로 Oracle에서 개발한 데이터 조작 언어이다. Oracle 기반의 모든 프로시저 작성에 사용되며 표준 SQL의 확장 기능이 우수하다.

 

2. PL/SQL 사용 시 장점

Compile이 필용 벗어 Script 생성 및 변경 후 바로 실행이 가능하다.

프로그램 개발의 모듈화가 가능하다.

변수, 상수 등 식별자를 선언하여 해당 식별자를 SQL과 절차적인 프로그램에서 사용할 수 있다.

IF문 Loop문 등 절차적 언어 구조로 된 프로그램 작성이 가능하다.

Explicit Cursor를 이용한 Multi-row 처리가 가능하다.

Exception 처리 루틴을 이용하여 Error처리가 가능하다.

네트워크 부하를 줄여 프로그램의 성능 향상을 기대할 수 있다.

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

 

3. PL/SQL 구조

PL/SQL은 프로그램을 논리적인 블록으로 나누게 하는 구조화된 블록 언어로서 다음과 같은 블록 구조로 구성된다.

 

선언부 : 실행부에서 참조할 모든 변수, 상수, Cursor, Exception을 선언한다.

실행부 : Begin과 End사이에 기술되는 영역으로, 데이터베이스 데이터를 처리할 SQL 문과 PL/SQL 블록을 기술한다.

예외 처리부 : 실행부에서 에러가 발생했을 경우 수행될 문장을 기술한다.

 

4. PL/SQL 처리절차

PL/SQL로 작성된 Block을 Oracle서버로 보내면 PL/SQL엔진이 SQL문과 Non SQL문을 구분한다.

Non SQL문은 PL/SQL엔진내의 Procedural Statement Executor가, SQL문은 SQL Statement Executor가 처리하게 된다.

Non SQL문은 Client환경에서, SQL문은 서버에서 실행된다.

따라서, PL/SQL문을 사용하게 되면 서버 작업량을 줄이게 되므로 네트워크 부하를 감소시켜 수행성능을 증가시키는 장점이 있다.

 

1.5 PL/SQL을 활용한 저장형 객체 활용

PL/SQL로 작성할 수 있는 저장형 객체로는 Stored Function, Stored Procedure, Stored Package, Trigger 등이 있다.

 

1. Stored Function

보통값을 계산하고 결과값을 반환하기 위해서 많이 사용한다.

대부분 구성이 프로시저와 유사하지만 IN 파라미터만 사용할 수 있다.

반드시 반환될 값의 데이터 타입을 Return문에 선언해야 한다.

PL/SQL블록 내에서 Return 문을 통해서 반드시 값을 반환해야 한다.

ㅛ를받아서정해진

2. Stored Procedure

특정 작업을 수행할 수 있는 이름이 있는 PL/SQL블록이다.

매개 변수를 받을 수 있고, 반복적으로 사용할 수 있는 Object이다.

보통 연속 실행 또는 구현이 복잡한 트랜잭션을 수행하는 PL/SQL 블록을 DB에 저장하기 위해 생성된다.

CREATE OR REPLACE 구문을 사용하여 생성한다.

IS 로 PL/SQL 블록을 시작한다.

LOCAL 변수는 IS와 BEGIN 사이에 선언한다.

 

3. Store Package

패키지는 Oracle 데이터베이스에 저장되어 있는 서로 관련있는 PL/SQL 프로시저와 함수들의 집합이다.

패키지는 선언부와 본문 두부분으로 나누어 진다.

 

4. Trigger

INSERT, UPDATE, DELETE 문이 TABLE에 대해 행해질 때 묵시적으로 수행되는 PROCEDURE이다.

Trigger는 TABLE과는 별도로 데이터베이스에 저장된다.

Trigger는 VIEW에 대해서가 아니라 TABLE에 관해서만 정의될수 있다.

DBMS_OUTPUT.PUT_LINE을 출력하기 위해 'set serveroutput on'을 사용한다.

 

2. 프로그램 디버깅

만들어진 데이터베이스 프로시저에 대한 개발자 스스로 검증을 해보아야하는데 , 이를 프로그램 디버깅 이라고 한다. 프로시저 디버깅은 만들어진 프로시저가 정해진 입력 자료를 받아서 정해진 출력을 출력하는지에 과한 확인 과정이다.

 

2.1 SQL*Plus 개요

 

1. SQL과 SQL*Plus 차이점

SQL은 데이터를 조작하는 표준 언어인 반면 SQL*Plus는 이러한 SQL을 DBMS 서버에 전송하여 처리할 수 있도록 하는 Oracle에서 제공하는 도구이다.

 

3. 단위 테스트 도구

Oracle DBMS는 모든 데이터조작 프로시저에 대한 테스트 환경으로 SQL*Plus 라는 도구를 제공하므로, 개발자는 데이터 조작 프로시저 테스트를 위해 해당 도구 활용을 위한 SQL*Plus에대한사전지식이필요한다.

 

3.1 PL/SQL 테스트

 

1. DBMS_OUTPUT 패키지 활용

메시지를 버퍼에 저장하고 버퍼로부터 메시지를 읽어오기 위한 인터페이스를 제공하는 패키지인 DBMS_OUTPUT을 코드에 포함한다.