[정보처리기사 실기] SQL 응용 _ 응용 및 절차형 SQL

응용 SQL

1) 뷰 정의

CREATE VIEW 뷰_이름[(속성목록)] AS SELECT문;

 

예제) 학생 테이블에서 '컴퓨터' 학과의 학번, 이름, 학년을 'V_COM' 뷰로 정의

CREATE VIEW V_COM(학번, 이름, 학년)
AS SELECT 학번, 이름, 학년
      FROM 학생
      WHERE 학과 = '컴퓨터';

---

2) 인덱스 정의

CREATE [UNIQUE] INDEX 인덱스명
ON 테이블명(속성명 [ASC | DESC] [,속성명 [ASC | DESC]])
[CLUSTER];

 

예제) emp 테이블에서 sano 속성을 내림차순 정렬해 sano_idx 이름으로 인덱스 생성

Create index sano_idx on emp (sano desc);

---

 3) GRANT

- 사용자에게 권한 부여하는 명령

GRANT 권한 ON 데이터 객체 TO 사용자 [WITH GRNAT OPTION];

- WITH GRANT OPTION 주면 다른 사용자에게 권한 부여 가능

 

예제 ①) DBA가 사용자1에게 학생 릴레이션에 대한 갱신 권한과 권한 부여권 부여

DBA : GRANT UPDATE ON 학생 TO 사용자1 WITH GRANT OPTION;

 

예제 ②) DBA가 모든 사용자에게 학생 릴레이션에 대한 SELECT 권한 부여

DBA : GRANT SELECT ON 학생 TO PUBLIC;

---

4) REVOKE

- 권한 취소

REVOKE 권한 [GRANT OPTION FOR] ON 데이터 객체 FROM 사용자 {CASCADE, RESTRICT};

 

- CASCADE : 부여된 하위권한 연쇄적으로 취소

- RESTSRICT : 하위권한 없을 때 사용자 권한만 취소

 

예제) DBA가 사용자2에게 부여한 삭제 권한 및 하위 권한도 취소하는 경우

DBA : REVOKE DELETE ON 학생 FROM 사용자2 CASCADE

---

절차형 SQL

-일반적인 개발 언어처럼 SQL 언어에서도 조건문, 반복문 이용해 절차적 프로그램 가능하도록 하는 언어

- DBMS 벤더별로 PL/SQL (Oracle), T-SQL (SQL Server) 등 절차형 SQL 제공

 

절차형 SQL 종류

프로시저 (procedure)	일련 쿼리들을 하나의 함수처럼 실행하기 위한 쿼리 집합
사용자 정의 함수	일련 SQL 처리 수행하고, 수행 결과를 단일 값으로 변환
트리거 (Trigger)	데이터베이스 시스템에서 삽입, 갱신, 삭제 등 이벤트가 발생할 때 관련 작업이 자동으로 수행되게 하는 절차형 SQL

---

1) 프로시저 (Procedure) = 저장 프로시저 (Stored procedure)

 

- 여러 개 SQL 명령문 하나의 프로시저 이름으로 미리 컴파일해 SQL 서버에 미리 저장해두고 필요 시

호출해 사용하는 기법

- 호출 시 컴파일 없이 즉시 재사용 가능하므로 처리속도 빠름

- 매개변수와 Return 값만 사용하므로 네트워크 트래픽 줄일 수 있음

(이 때 반환되는 Return 값은 무조건 정수 값)

- 반복 처리하는 일괄 작업, 일일 마감 업무 등에 사용

 

프로시저 구성

---

프로시저 처리

---

2) 사용자 정의 함수

- 함수명으로 SQL 구문 정의하고, 필요 시 함수 호출 통해 작업 처리하고 결과는 하나의 값으로 반환됨

- 사용자가 직접 정의하고 작성

- DML문 (Select 등)에서 호출하여 실행됨

- 출력 매개변수 없고 RETURN문 통해 값 반환

- 정수값 이외에도 여러 유형 데이터 값 반환 가능

- DML문에서 반환값 활용하기 위해 사용

 

사용자 정의 함수 구성

 

---

3) 트리거 (Trigger)

- DBMS가 미리 정해놓은 조건 충족되거나 특정 테이블에 삽입, 삭제, 갱신 등 이벤트 발생 시 관련 작업을 자동으로

수행하는 절차형 SQL

- 데이터베이스 시스템에서 삽입, 갱신, 삭제 등 이벤트 발생할 때마다 관련 작업이 자동으로 수행되는 절차형 SQL

- 데이터 변경 및 무결성 유지, 로그 메시지 출력 등에 사용

- 데이터 제어어 (DCL) 사용 불가 (COMMIT, ROLLBACK 등)

- 트리거 오류는 데이터에 영향 주므로 세심한 주의 요구

- 트리거 작성 문법은 프로시저나 사용자 정의 함수와 기본적으로 같지만 EVENT 명령으로 이벤트 인식 한다는 점,

IN / OUT 없다는 점 다름

 

트리거 구성요소

 

동작 시기

- 트리거가 실행될 때

• AFTER : 테이블 변경 후 트리거 실행
• BEFORE : 테이블 변경 전 트리거 실행

동작 옵션

- 트리거가 실행되어야 할 작업 종류

- INSERT, DELETE, UPDATE

---

예제) <직원> 테이블에 새로운 튜플 삽입 시 부서 정보 누락됐으면 '부서' 속성에 '신입' 저장하는 트리거 작성 (트리거명 : 직원정보_trigg)

CREATE TRIGGER 직원정보_trigg

BEFORE INSERT ON 직원

FOR EACH ROW WHEN (직원.부서 IS NULL)

BEGIN

                   :직원.부서: = '신입';

END;

---

쿼리 성능 최적화

- 사용자 질의 효율적으로 실행되는 동등한 질의로 바꾸는 것으로 옵티마이저 (최적화기)에서 핵심적 기능 수행

- 최적화기는 사용자가 요청한 질의에 대해 실행 계획 세우고 비용 (필요한 자원) 계산해 최적의 질의 처리 경로 탐색

---

옵티마이저 종류

항목	RBO	CBO
최적화 기준	규칙 기반	비용 기반
성능 기준	개발자 경험	최적화기의 예측
특징	실행 계획 예측 용이	성능 통계치 정보 활용예측 복잡
고려사항	인덱스 구조, 연산자, 조건 형식 등	튜플 수, 블록 수 , 컬럼 값 분포 등

 

데이터베이스 관리 시스템 (DBMS)에서 쿼리 실행 시 사용되는 옵티마이저 (Optimizer)

작성된 SQL이 가장 효율적으로 수행되도록 최적의 경로 찾아주는 모듈

---

중간 파일 크기 줄이는 방법

- SELECT 가능한 한 먼저 실행시켜 튜플 수 줄이기

- PROJECT 가능한 한 일찍 실행해 속성 수 줄이기

- JOIN 연산은 가능한 늦게 실행해 튜플 수 줄이기