[DB] ch8 뷰,인덱스, PL/SQL

박소미·2023년 11월 28일

DB

목록 보기

8/14

8.1 뷰(view)

데이터베이스는 조직에서 사용하는 데이터들을 한 곳에 모아 저장하고 공유하는데 각 부서의 관점에서 테이블을 다룰 수 있도록 하는 수단이 뷰(view)이다. (필요한 데이터베이스만 추출)
이미 만들어진 테이블로 또다른 테이블 추가 생성

-- 문법
CREATE VIEW 뷰 이름
AS 뷰를 정의하는 SELECT문;
-- EX
CREATE VIEW emp_high_salary
AS SELECT empno, ename, deptno, sal
   FROM emp
   WHERE sal >= 2500;

현장에서 뷰가 사용되는 세가지 경우

1.각 부서에서 필요한 정보만 뷰로 정의하여 테이블을 만들고 싶은 경우

2.조직의 보안상 민감한 정보를 감추는데 사용한다.

3.다수의 테이블을 조인하는 복잡한 질의문이 사용될 때 미리 뷰를 만들어 놓으면 코딩 작업이 수월해진다.

✅ 뷰에 대해서도 INSERT, UPDATE, DELETE 연산이 가능한가?
case by case로 가능하지만 뷰는 조회용으로만 사용하는 것이 바람직하다.

8.2 인덱스 (INDEX)

정보를 빠르게 찾기위해 인덱스를 이용한다.

(1) 순차 탐색

첫 번째 튜플부터 차례로 비교하는 방법
간단하지만 검색 속도가 느리다.

(2) 이진 탐색

빠른 검색 방법 중 하나
비교 대상 컬럼을 기준으로 정렬되어 있어야 하는데 튜플의 수가 많아질수록 정렬 상태를 유지하는데 비용이 많이 든다.

(3) 인덱스의 이용

검색 기준 컬럼을 추출하여 정렬된 상태로 저장하되 원 테이블의 튜플을 찾아가기 위한 링크 정보를 함께 저장

인덱스 지정

-- 문법
CREATE [UNIQUE] INDEX 인덱스 이름 ON 테이블명(컬럼명);

-- 인덱스 설정 예시
CREATE INDEX idx_emp_ename ON emp (ename);

-- 인덱스 삭제 예시
DROP INDEX idx_emp_ename;

✅ CREATE INDEX와 CREATE UNIQUE INDEX

CREATE INDEX는 중복되는 값(이름)이 저장되는 경우에 사용
CREATE UNIQUE INDEX는 중복이 없는 (주민등록번호) 경우 사용
UNIQUE INDEX의 검색 성능이 더 좋음

✅ 기본키와 인덱스

대부분 DBMS에서는 기본키 컬럼에 자동적으로 인덱스 생성
기본키가 검색의 기준이기 때문

인덱스에 대한 검토

인덱스 파일이 많으면 인덱스를 재정렬하는데 시간 소모가 커서 DBMS 성능 저하 시킴
인덱스는 필요한 컬럼에 대해서만 지정
테이블의 튜플의 수가 적은 경우 인덱스 효과가 발휘되지 않음

8.3 PL/SQL 프로시저

PROCEDUER

데이터베이스에서는 테이블, 뷰와 같이 데이터에 관련된 요소 뿐만 아니라 프로그램도 저장이 가능하다.
오라클에서는 SQL을 확장한 PL/SQL 프로그래밍 언어를 사용하여 저장 가능 프로그램을 생성한다.
PL/SQL로 생성할 수 있는 저장 프로그램에는 프로시저, 함수, 트리거가 있다.

-- 문법
CREATE PROCEDURE 프로시저 이름 (매개변수 매개변수의 자료형)
AS
[변수 선언]
BEGIN
프로그램 내용
END;

✅ 프로시저 주의점

문법 오류를 수정하기 전에 잘못 생성된 프로시저를 먼저 삭제한 후에 작업을 해야한다.
CREATE PROCEDURE 대신에 REPLACE PROCEDURE 명령문을 이용한다.
이런 번거로움을 해결하기위해 코드 첫줄을
CREATE OR REPLACE PROCEDURE p_emp_sel(eid number) 로 변경하면 편리하다.
p_emp_sel이 없으면 새로 생성하고, 이미 존재하면 기존 것을 새로 만드는 것으로 대체하라는 뜻
생성된 프로시저의 실행은 EXECUTE 또는 EXEC 명령어를 이용

SET SERVEROUTPUT ON ; --프로시저의 실행 결과가 화면에 나타나도록 설정
EXEC p_emp_sel(7698) ;

프로시저 작성에 사용되는 문법
1. 변수의 선언, 변수에 값 저장하기

AS
  pi NUMBER(5,2) ; --변수 선언
BEGIN
  pi := 3.14;  --변수에 값 저장
END

--변수의 선언과 초기값의 저장을 동시에 하는 것도 가능
AS
  pi NUMBER(5,2) := 3.14;
BEGIN
END

2.입력 변수(매개 변수)의 선언

입력 변수(매개 변수)는 프로시저의 외부에서 프로시저 안으로 값을 전달할 때 사용

CREATE PROCEDURE p_emp_sel(eid number)
--프로시저에서 매개 변수의 자료형을 선언할 때 자리수는 제외하고 표현한다.
-- number(10,2) x 컴파일 에러

✅ 입력 변수의 유형

입력 변수 성격에 따라 IN, OUT, INOUT 으로 구분
IN 타입은 읽기 전용으로 변수에 저장된 값을 읽을 수는 있지만 변경하는 것은 불가능하다.
OUT 타입 입력 변수는 쓰기 가능
INOUT 타입 변수는 읽기 쓰기 모두 가능
입력 변수 타입을 지정 안 하면 자동으로 IN 타입 지정

--타입을 지정한 입력 변수 정의 예
CREATE OR REPLACE PROCEDURE p_odd_sum(in_value IN number)

화면에 작업결과 출력하기
작업 결과나 변수의 내용을 출력하려면 DBMS_OUTPUT.PUT_LINE() 함수 사용

DBMS_OUTPUT.PUT_LINE(eid || ' ' || v_ename|| ' ' || v_job) ;
-- ||는 출력할 문자열을 연결 

-- 실행 오류시 에러메시지 출력
EXCEPTION
  WHEN OTHER THEN
    DBMS_OUTPUT.PUT_LINE(SQLERRM);

비교문

-- 문법
IF 비교조건 THEN
  비교조건 만족시 실행할 명령문 1;
  ..
  비교조건 만족시 실행할 명령문 n;
END IF



IF 비교조건 THEN
  비교조건 만족시 실행할 명령문 1;
  ..
  비교조건 만족시 실행할 명령문 n;
ELSE
  비교조건 만족시 실행할 명령문 1;
  ..
  비교조건 만족시 실행할 명령문 
END IF

--ex
IF avg_salary < 3000 THEN
  UPDATE emp
  SET sal = sal * 1.1;
END IF;

5.반복문
오라클에서는 반복문에 많이 이용되는 FOR문, WHILE문 LOOP문을 제공

FOR 증감 변수 IN 초기값 .. 최종값 LOOP
  명령문 1;
  ..
  명령문 n;
END LOOP;

--ex) 1~100의 합계를 계산하여 출력하는 코드
AS
  v_sum NUMBER := 0;
BEGIN
  FOR i IN 1..100 LOOP
    v_sum := v_sum + i;
  END LOOP;
  DBMS_OUTPUT.PUTLINE('sum :' || v_sum);
END

프로시저의 장점

네트워크의 부하를 줄일 수 있다.
프로시저가 호출될 때 저장된 정보를 재활용하므로 일반 SQL에 비해 처리 시간이 단축된다.
프로시저의 실행권한만을 부여함으로써 보안 문제를 해결
공통 SQL문은 프로시저로 정의하여 공유함으로써 번거로움과 실수를 줄임
유지보수가 용이

프로시저의 단점

프로시저 작성 문법이 DBMS 제품마다 다르기 때문에 DBMS 제품을 변경하면 프로시저를 수정하거나 재작성해야 한다.
외부 응용프로그램과 프로시저에 나뉘어서 구현되어 있다면 통합적으로 수정해야 하는데 수정 절차가 복잡함
프로시저를 남발하는 경우 전체 정보 시스템의 관리나 유지보수가 더 어려워짐

8. PL/SQL 함수

함수(function)는 프로시저와 마찬가지로 DB에 저장되어 사용되는 미니 프로그램
내장 함수 : SUM( ), AVG( ), MAX( ), MIN( ) 등등
프로시저와 다른 점은 사용자 정의 함수는 반드시 SQL문 안에서 사용되어야하며 반환(return)값이 있음

--문법
CREATE FUNCTION 함수 이름(입력변수 리스트)
  RETURN 반환 값의 자료형
AS
  변수 선언;
BEGIN 
  처리내용;
  RETURN 반환값;
END;

--ex) 급여액을 입력하면 높은 연봉인지 낮은 연봉인지를 알려주는 사용자 정의 함수(2,500 넘으면 높은 연봉)
CREATE OR REPLACE FUNCTION f_grade (in_value number)
  RETURN varchar2
AS
  v_grade varchar2(20) ;
BEGIN
  v_grade := 'Low salary' ;
  IF in_value > 2500 THEN
    v_grade := 'High salary' ;
  END IF ;
  RETURN v_grade ;
END ;