2-7장. 데이터 입출력 구현-데이터 조작 프로시저 작성(1)

[ 목차 ]

1. 프로시저

2. 일반 질의문

3. 프로시저 생성, 실행, 삭제

4. SQL의 분류

5. 트랜잭션 제어어 TCL

6. 데이터 검색어 SELECT

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

8. PL/SQL 실행 원리 

9. PL/SQL 장점

10. PL/SQL 구조

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

 

1. 프로시저 = 저장 프로시저

1) 연속된 SQL 문들을 하나로 모아 SQL 서버에 미리 컴파일해서 저장해 놓은 개체로서 프로그램에서 함수와 같은 역할

2) 선택적으로 매개변수를 받아 일련의 질의문을 실행시켜 결과를 돌려주는 것도 가능

3) 자주 사용하는 SQL을 모아 저장해서 재사용성을 높이는 것

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

 

2. 일반 질의문

1) 사용자 또는 응용 프로그램이 실행하고자 하는 SQL문을 DBMS에 전송하고 그 결과를 받음

2) 대량의 복잡한 질의문들이 반복적으로 입력되면 시스템에 부담

3) DBMS에도 처리해야 할 일이 늘어남

4) 프로시저의 차이점

  1] 저장되어 있기에 프로시저 내용은 DBMS에 포함되어 있고 실행 방안도 미리 작성되어 있기에 DBMS 부담 감소

  2] 사용자, 응용프로그램은 쿼리문을 만들 필요 없이 저장된 필요 프로시저 이름과 매개변수 값만 전송 -> SQL문의 단순화

 

3. 프로시저 생성, 실행, 삭제

1) 생성

CREATE PROCEDURE 프로시저명 (IN 받는값 OUT 되돌려주는 값) 
     BEGIN 
                     프로시저로 정의할 SQL문 삽입
     END;

2) 실행

EXCUTE 프로시저명;
EXCUTE 프로시저명 매개변수값;

3) 삭제

DROP PROCEFDURE 프로시저명;

 

4. SQL의 분류

1) DDL 정의어

  1] CRETAE, DROP, ALTER, RENAME, TRUNCATE

  2] RENAME : 이름 변경

  3] TURNCATE : 조건절 부여 없이 테이블의 데이터 모두 삭제

2) DML 조작어

  1] 데이터를 변경, 검색하기 위해 사용하며 트랜잭션 제어어를 활요하여 실행 전 상태로 복귀가능한 명령어

  2] INSERT, UPDATE, DELETE

  3] 변경된 데이터는 버퍼(임시 기억장소)에 저장되며 COMMIT 해야 DB에 반경

3) DCL 제어어

  1] ROLE, REVOKE, GRANT

  2] ROLE : 사용자에게 허가 할 수 있는 권한들의 집합

 

5. 트랜잭션 제어어 TCL

1) 트랜잭션의 DML 작업 단위를 제어하는 명령어

2) COMMIT, ROLLBACK, SAVEPOINT 등

3) COMMIT

  1] 트랜잭션을 완료하여 데이터 변경사항을 최종 반영

  2] COMMIT 이후에 데이터에 대한 변경 사항이 물리적 디스크에 반영

  3] COMMIT이 되면 이전 데이터 복구 불가

  4] 모든 사용자가 변경된 결과 확인 가능

  5] 관련된 행에 대해 잠금이 풀리며 다른 사람들이 조작 가능

4) ROLLBACK

  1] 데이터 변경사항을 이전 상태로 되돌리는 명령

  2] ROLLBACK 이후 데이터 상태는 명령문을 수행하기 전 상태로 돌아감

  3] 관련된 행에 대한 잠금이 풀림

락
1) 데이터 일관성, 무결성을 보장하기 위해 트랜잭션을 수행 중인 데이터에 락을 걸어 다른 트랜잭션에 의해 의도하지 않게 데이터가 변경되지 않도록 수행

2)즉, 트랜잭션을 수행중일 때 다른 트랜잭션이 수행 불가

6) SAVEPOINT

  1] 지정된 특정 시점까지 롤백할 수 있는 명령어

 

6. 데이터 검색어 SELECT

1) ALIAS : 해당 칼럼에 대해서 다른 이름을 부여할 때 사용

 

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

1) 표준 SQL을 기본으로 오라클에서 개발한 데이터 조작 언어

2) 오라클 기반의 모든 프로시저 작성에서 사용

3) 여러 SQL 문자을 블록으로 묶고 한번에 블록 전체를 서버로 전송하는 방법

 

8. PL/SQL 실행 원리 

1) DBA(DB관리자)가 PL/SQL이 포함된 블록을 실행

2) 오라클 서버내 메모리에 있는 PL/SQL 엔진이 블록을 받음

3) 해당 블록에 있던 모든 SQL 문장들이 오라클 서버 프로세스에게 전달되어 수행

4) PL/SQL 엔진은 해당 SQL문장이 수행되어 결과가 돌아오는 것을 대기

5) 결과를 받고 나머지 PL/SQL 문장 실행

 

9. PL/SQL 장점

1) 컴파일이 필요없어 스크립트 생성 및 변경 후 바로 실행 가능(즉, 실행 파일이 따로 불필요0

2) 변수, 상수 등 식별자들 선언하여 해당 식별자를 SQL과 절차적 프로그램에서 사용 가능

3) IF, LOOP문 등 절차적 언어로 구성된 프로그램 작성이 가능

4) EXPLICIT CURSOR를 이용한 멀티-로우 처리 가능(EX. 커서를 이용해 복수 튜플 처리 가능)

5) EXCEPTION 처리 루틴을 이용하여 에처 처리 가능

6) 네트워크 부하를 줄여 프로그램의 성능 향상 기대 가능

7) 여러 SQL문장을 블록으로 묶고 한번에 전달하기 때문에 통신량 감소 가능

8) 프로그램 개발의 모듈화가 가능

 

10. PL/SQL 구조

1) 논리적 블록으로 나누게 하는 구조화된 블록  언어

2) 선언부 DECLARE : 실행부에서 참조할 모든 변수, 상수, 서커, 예외를 선언

3) 실행부 BEGIN/END : BEGIN과 END 사이에 기술되는 영역으로 데이터베이스를 처리할 SQL문과 PL/SQL 블록을 기술

4) 예외 처리브 EXCEPTION : 실행부에서 에러가 발생했을 때 수행될 문장을 기술

 

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

1) STORED FUNCTION

  1] 값을 계산하고 결과값을 반환하기 위해 많이 사용

  2[ 대부분 구성이 프로시저와 유사하지만 IN 파라미터만 사용 가능

  3] 반드시 반환될 값의 데이터 타입을 RETURN 문에 선언해야 함

  4] PL/SQL 블록 내에서 리턴문을 통해서 반드시 값을 반환해야 함

2) STORED PROCEDURE

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

  2] 매개 변수를 받을 수 잇고 반복적으로 사용할 수 잇는 오브젝트

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

  4] CRETE OR REPLACE 구문을 사용해 생성

  5] IS 로 PL/SQL의 블록을 시작

  6] LOCAL 변수는 IS와 BEGIN 사이에 선언

3) STORED PACKAGE

  1] 오라클 데이터베이스에 저장되어 있는 서로 관련있는 PL/SQL 프로시저와 함수들의 집합

  2] 선언부와 본문 두 부분으로 구분

4) TRIGGER

  1] INSERT, UPDATE, DELETE문이 테이블에 대해 행해질 때 묵시적으로 실행되는 프로시저

  2] 테이블과는 별도로 데이터베이스에 저장

  3] 뷰에 대해서가 아닌 테이블에 관해서만 정의 가능

  4] 이벤트-조건-동작 규칙을 따라 ECA라고 부름

  5] DBMS_PUTPUT.PUT_LINE을 출력하기 위해 'SET SERVEROUTPUT ON'을 사용

트리거
1) 특정 테이블의 데이터에 변경(이벤트)가 가해졌을 때 연쇄적으로 수행되는 저장 프로시져
2) 조건을 만족하거나 어떤 동작이 수행되면 자동으로 수행되는 동작
3) DML이 테이블에 행해질 때 묵시적으로 수행되는 프로시저로 데이터베이스의 무결성을 유지하기 위한 일반적이고 강력한 도구
4) 업무 프로세스를 알지 못해도 자동으로 처리 가능
5) 프로세스가 집중될 경우 오버헤드가 발생해 시스템 성능이 저하할 수 잇있기에 데이터사용량을 분석해서 결정해야 함