[정보처리기사 실기] 08. SQL 응용

08. SQL 응용

※ ISO/IEC 9075 기준

(1) SQL - DDL

DDL(Data Define Language, 데이터 정의어)

• DB 구조, 데이터 형식, 접근 방식 등 DB를 구축하거나 수정할 목적으로 사용하는 언어
• 번역한 결과가 데이터 사전(Data Dictionary)이라는 특별한 파일에 여러 개의 테이블로 저장된다.
• DDL의 3가지 유형

명령어	기능
CREATE	SCHEMA, DOMAIN, TABLE, VIEW, INDEX를 정의함.
ALTER	TABLE에 대한 정의를 변경하는 데 사용함.
DROP	SCHEMA, DOMAIN, TABLE, VIEW, INDEX를 삭제함.

 

CREATE SCHEMA

• 스키마를 정의하는 명령문
  • 스키마(Schema) : 데이터베이스와 구조와 제약 조건에 관한 전반적인 명세(Specification)를 기술(Description)한 것으로, 데이터 개체(Entity), 속성(Attribute), 관계(Relationship) 및 데이터 조작 시 데이터 값들이 갖는 제약 조건 등에 관해 전반적으로 정의한다.
• 표기 형식

CREATE SCHEMA 스키마명 AUTHORIZATION 사용자_id;

 

예제 : 소유권자의 사용자 ID가 '홍길동'인 스키마 '대학교'를 정의하는 SQL문

CREATE SCHEMA 대학교 AUTHORIZATION 홍길동;

 

CREATE DOMAIN

• 도메인를 정의하는 명령문
  • 도메인(Domain)
    • 하나의 속성이 취할 수 있는 동일한 유형의 원자값들의 집합
    • 특정 속성에서 사용할 데이터의 범위를 사용자가 정의하는 사용자 정의 데이터 타입
    • 예) 학년 속성의 데이터 타입이 정수형이고, 해당 속성에서 취할 수 있는 값의 범위가 1~4까지라면, 1~4라는 범위는 해당 속성에 지정된 정수형의 모든 범위가 아니라 일부분이므로 사용자는 1~4까지의 범위를 해당 속성의 도메인으로 정의해서 사용할 수 있다.
• 표기 형식

CREATE DOMAIN 도메인명 [AS] 데이터_타입
       [DEFAULT 기본값]
       [CONSTRAINT 제약조건명 CHECK (범위값)];

• 데이터 타입 : SQL에서 지원하는 데이터 타입
• 기본값 : 데이터를 입력하지 않았을 때 자동으로 입력되는 값

 

예제 : '성별'을 '남' 또는 '여'와 같이 정해진 1개의 문자로 표현하는 도메인 SEX를 정의하는 SQL문

CREATE DOMAIN SEX CHAR(1)     
       DEFAULT '남'    
       CONSTRAINT VALID-SEX CHECK(VALUE IN ('남', '여'));

 

CREATE TABLE

• 테이블을 정의하는 명령문
  • 테이블(Table)
    • 데이터베이스의 설계 단계에서는 테이블을 주로 릴레이션(Relation)이라 부르고, 조작이나 검색 시에는 테이블(Table)이라고 부른다.
    • 그러나 대부분은 테이블과 릴레이션을 구분 없이 사용함.
• 표기 형식

CREATE TABLE 테이블명
       (속성명 데이터_타입 [DEFAULT 기본값] [NOT NULL], ...
       [, PRIMARY KEY(기본키_속성명, ...)]
       [, UNIQUE(대체키_속성명, ...)]
       [, FOREIGN KEY(외래키_속성명, ...)]
               [REFERENCES 참조테이블(기본키_속성명, ...)]
               [ON DELETE 옵션]
               [ON UPDATE 옵션]
       [, CONSTRAINT 제약조건명] [CHECK(조건식)]);

• 기본 테이블에 포함될 모든 속성에 대하여 속성명과 그 속성의 데이터 타입, 기본값, NOT NULL 여부를 지정한다.
• PRIMARY KEY : 기본키롤 사용할 속성을 지정함.
• UNIQUE
  • 대체키로 사용할 속성을 지정함.
  • 중복된 값을 가질 수 없음.
• FOREIGN KEY ~ REFERENCES ~ : 외래키로 사용할 속성을 지정함.
  
  • ON DELETE 옵션 : 참조 테이블의 튜플이 삭제되었을 때, 기본 테이블에 취해야 할 사항을 지정함.
  • ON UPDATE 옵션 : 참조 테이블의 참조 속성 값이 변경되었을 때, 기본 테이블에 취해야 할 사항을 지정함.
• CONSTRAINT : 제약 조건의 이름을 지정함.
• CHECK : 속성 값에 대한 제약 조건을 정의함.

 

예제 : '이름', '학번', '전공', '성별', '생년월일'로 구성된 <학생> 테이블을 정의하는 SQL문

• 제약 조건
  • '이름'은 NULL이 올 수 없고, '학번'이 기본키이다.
  • '전공'은 <학과> 테이블의 '학과코드'를 참조하는 외래키로 사용된다.
  • <학과> 테이블에서 삭제가 일어나면, 관련된 튜플들의 전공 값을 NULL로 만든다.
  • <학과> 테이블에서 '학과코드'가 변경되면, 전공 값도 같은 값으로 변경한다.
  • '생년월일'은 1980-01-01 이후의 데이터만 저장할 수 있다.
  • 제약 조건의 이름은 '생년월일제약'으로 한다.
  • 각 속성의 데이터 타입은 적당하게 지정한다.
    • 단, '성별'은 도메인 'SEX'를 사용한다.

CREATE TABLE 학생
       (이름 VARCHAR(15) NOT NULL,
       학번 CHAR(8),
       전공 CHAR(5),
       성별 SEX,
       생년월일 DATE,
       PRIMARY KEY(학번),
       FOREIGN KEY(전공) REFERENCES 학과(학과코드)
           ON DELETE SET NULL
           ON UPDATE CASCADE   
       CONSTRAINT 생년월일제약
           CHECK(생년월일) >= '1980-01-01'));

 

CREATE VIEW

• 뷰(View)를 정의하는 명령문
  • 뷰(View)
    • 하나 이상의 기본 테이블로부터 유도되는 이름을 갖는 가상 테이블(Virtual Table)
    • 테이블은 물리적으로 구현되어 실제로 데이터가 저장되지만, 뷰는 물리적으로 구현되지 않는다.
      • 뷰를 생성하면 뷰 정의가 시스템 내에 저장되었다가 SQL 내에서 뷰 이름을 사용하면 실행 시간에 뷰 정의가 대체되어 수행된다.

CREATE VIEW 뷰명[(속성명[, 속성명, ...])]
AS SELECT문;

 

예제 : <고객> 테이블에서 '주소'가 '안산시'인 고객들의 '성명'과 '전화번호'를 '안산고객'이라는 뷰로 정의하시오.

CREATE VIEW 안산고객(성명, 전화번호)
AS SELECT 성명, 전화번호
FROM 고객
WHERE 주소 = '안산시';

 

CREATE INDEX

• 인덱스를 정의하는 명령문
  • 인덱스(Index) : 검색 시간을 단축시키기 위해 만든 보조적인 데이터 구조
• 표기 형식

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

• UNIQUE
  • 사용된 경우 : 중복 값이 없는 속성으로 인덱스를 생성한다.
  • 생략된 경우 : 중복 값을 허용하는 속성으로 인덱스를 생성한다.
• 정렬 여부 지정
  • ASC : 오름차순 정렬
  • DESC : 내림차순 정렬
  • 생략된 경우 : 오름차순으로 정렬
• CLUSTER : 사용하면 인덱스가 클러스터드 인덱스로 설정됨.
  • 클러스터드 인덱스(Clusetered Index)
    • 인덱스 키의 순서에 따라 데이터가 정렬되어 저장되는 방식
    • 실제 데이터가 순서대로 저장되어 있어 인덱스를 검색하지 않아도 원하는 데이터를 빠르게 찾을 수 있다.
    • 하지만, 데이터 삽입, 삭제 발생 시 순서를 유지하기 위해 데이터를 재정렬해야 한다.
  • 넌 클러스터드 인덱스(Non Clustered Index)
    • 인덱스의 키 값만 정렬되어 있을 뿐, 실제 데이터는 정렬되지 않는 방식
    • 데이터를 검색하기 위해서는 먼저 인덱스를 검색하여 실제 데이터의 위치를 확인해야 하므로, 클러스터드 인덱스에 비해 검색 속도가 떨어진다.

 

예제 : <고객> 테이블에서 UNIQUE한 특성을 갖는 '고객번호' 속성에 대해 내림차순으로 정렬하여 '고객번호_idx'라는 이름으로 인덱스를 정의하시오.

CREATE UNIQUE INDEX 고객번호_idx
ON 고객(고객번호 DESC);

 

ALTER TABLE

• 테이블에 대한 정의를 변경하는 명령문
• 표기 형식

ALTER TABLE 테이블명 ADD 속성명 데이터_타입 [DEFAULT '기본값'];
ALTER TABLE 테이블명 ALTER 속성명 [SET DEFAULT '기본값'];
ALTER TABLE 테이블명 DROP COLUMN 속성명 [CASCADE];

• ADD : 새로운 속성(열)을 추가할 때 사용한다.
• ALTER : 특정 속성의 Default 값을 변경할 때 사용한다.
• DROP COLUMN : 특정 속성을 삭제할 때 사용한다.

 

예제 1 : <학생> 테이블에 최대 3문자로 구성되는 '학년' 속성을 추가하시오.

ALTER TABLE 학생 ADD 학년 VARCHAR(3);

 

예제 2 : <학생> 테이블의 '학번' 필드의 데이터 타입과 크기를 VARCHAR(10)으로 하고, NULL 값이 입력되지 않도록 변경하시오.

ALTER TABLE 학생 ALTER 학번 VARCHAR(10) NOT NULL;

 

DROP

• 스키마, 도메인, 기본 테이블, 뷰 테이블, 인덱스, 계약 조건 등을 제거하는 명령문
• 표기 형식

DROP SCHEMA 스키마명 [CASCADE | RESTRICT];
DROP DOMAIN 도메인명 [CASCADE | RESTRICT];
DROP TABLE 테이블명 [CASCADE | RESTRICT];
DROP VIEW 뷰명 [CASCADE | RESTRICT];
DROP INDEX 인덱스명 [CASCADE | RESTRICT];
DROP CONSTRAINT 제약조건명;

• CASCADE : 제거할 요소를 참조하는 다른 모든 개체를 함께 제거한다.
• RESTRICT : 다른 개체가 제거할 요소를 참조 중일 때는 제거를 취소한다.

 

예제 : <학생> 테이블을 제거하되, <학생> 테이블을 참조하는 모든 데이터를 함께 제거하시오.

DROP TABLE 학생 CASCADE;

 

 

(2) SQL - DCL

DCL(Data Control Language, 데이터 제어어)

• 데이터의 보안, 무결성, 회복, 병행 제어 등을 정의하는 데 사용하는 언어
• 데이터베이스 관리자(DBA)가 데이터 관리를 목적으로 사용한다.
• DCL의 종류

명령어	기능
COMMIT	명령에 의해 수행된 결과를 실제 물리적 디스크로 저장하고, 데이터베이스 조작 작업이 정상적으로 완료되었음을 관리자에게 알려줌.
ROLLBACK	데이터베이스 조작 작업이 비정상적으로 종료되었을 때, 원래의 상태로 복구함.
GRANT	데이터베이스 사용자에게 사용 권한을 부여함.
REVOKE	데이터베이스 사용자의 사용 권한을 취소함.

 

GRANT / REVOKE

• 데이터베이스 관리자가 데이터베이스 사용자에게 권한을 부여하거나 취소하기 위한 명령어
• GRANT : 권한 부여를 위한 명령어
• REVOKE : 권한 취소를 위한 명령어

 

사용자 등급 지정 및 해제

GRANT 사용자등급 TO 사용자_ID_리스트 [IDENTIFIED BY 암호];
REVOKE 사용자등급 FROM 사용자_ID_리스트;

 

예제 1 : 사용자 ID가 "NABI" 인 사람에게 데이터베이스 및 테이블을 생성할 수 있는 권한을 부여하는 SQL문

GRANT RESOURCE TO NABI;

 

예제 2 : 사용자 ID가 "STAR" 인 사람에게 단순히 데이터베이스에 있는 정보를 검색할 수 있는 권한을 부여하는 SQL문

GRANT CONNECT TO STAR;

 

테이블 및 속성에 대한 권한 부여 및 취소

GRANT 권한_리스트 ON 개체 TO 사용자 [WITH GRANT OPTION];
REVOKE [GRANT OPTION FOR] 권한_리스트 ON 개체 FROM 사용자 [CASCADE];

• 권한 종류 : ALL, SELECT, INSERT, DELETE, UPDATE, ALTER 등
• WITH GRANT OPTION : 부여받은 권한을 다른 사용자에게 다시 부여할 수 있는 권한을 부여함.
• GRANT OPTION FOR : 다른 사용자에게 권한을 부여할 수 있는 권한을 취소함.
• CASCADE : 권한 취소 시, 권한을 부여 받았던 사용자가 다른 사용자에게 부여한 권한도 연쇄적으로 취소함.

 

예제 3 : 사용자 ID가 "NABI" 인 사람에게 <고객> 테이블에 대한 모든 권한과 다른 사람에게 권한을 부여할 수 있는 권한까지 부여하는 SQL문

GRANT ALL ON 고객 TO NABI WITH GRANT OPTION;

 

예제 4 : 사용자 ID가 "STAR"인 사람에게 부여한 <고객> 테이블에 대한 권한 중, UPDATE 권한을 다른 사람에게 부여할 수 있는 권한만 취소하는 SQL문

REVOKE GRANT OPTION FOR UPDATE ON 고객 FROM STAR;

 

COMMIT

• 트랜잭션 처리가 정상적으로 완료된 후, 트랜잭션이 수행한 내용을 데이터베이스에 반영하는 명령
• COMMIT 명령을 실행하지 않아도 DML문이 성공적으로 완료되면 자동으로 COMMIT 되고, DML이 실패하면 자동으로 ROLLBACK이 되도록 Auto Commit 기능을 설정할 수 있다.

 

ROLLBACK

• 변경되었으나 아직 COMMIT 되지 않은 모든 내용들을 취소하고 데이터베이스를 이전 상태로 되돌리는 명령어
• 트랜잭션 전체가 성공적으로 끝나지 못하면, 일부 변경된 내용만 데이터베이스에 반영되는 비일관성(Inconsistency) 상태가 될 수 있기 때문에 일부분만 완료된 트랜잭션은 롤백(Rollback) 되어야 한다.

 

SAVEPOINT

• 트랜잭션 내에 ROLLBACK 할 위치인 저장점을 지정하는 명령어
• 저장점을 지정할 때는 이름을 부여한다.
• ROLLBACK 할 때, 지정된 저장점까지의 트랜잭션 처리 내용이 모두 최소된다.

 

 

예제 1 : <사원> 테이블에서 '사원번호'가 40인 사원의 정보를 삭제한 후, COMMIT을 수행하시오.

DELETE FROM 사원 WHERE 사원번호 = 40;
COMMIT;

• DELETE 명령을 수행한 후, COMMIT 명령을 수행하였으므로 DELETE 명령으로 삭제된 레코드는 이후 ROLLBACK 명령으로 되돌릴 수 없다.

 

예제 2 : <사원> 테이블에서 '사원번호'가 30인 사원의 정보를 삭제하시오.

DELETE FROM 사원 WHERE 사원번호 = 30;

• DELETE 명령을 수행한 후, COMMIT 명령을 수행하지 않았으므로, DELETE 명령으로 삭제된 레코드는 이후 ROLLBACK 명령으로 되돌릴 수 있다.

 

예제 3 : SAVEPOINT 'S1'을 설정하고, '사원번호'가 20인 사원의 정보를 삭제하시오.

SAVEPOINT S1;
DELETE FROM 사원 WHERE 사원번호 = 20;

 

 

예제 4 : SAVEPOINT 'S2'를 설정하고, '사원번호'가 10인 사원의 정보를 삭제하시오.

SAVEPOINT S2;
DELETE FROM 사원 WHERE 사원번호 = 10;

 

 

예제 5 : SAVEPOINT 'S2'까지 ROLLBACK을 수행하시오.

ROLLBACK TO S2;

• ROLLBACK이 적용되는 시점을 'S2'로 지정했기 때문에 [예제 5]의 ROLLBACK에 의해 <사원> 테이블의 상태는 [예제 4]의 작업을 수행하기 전으로 되돌려진다.

 

예제 6 : SAVEPOINT 'S1'까지 ROLLBACK을 수행하시오.

ROLLBACK TO S1;

• ROLLBACK이 적용되는 시점을 'S1'으로 지정했기 때문에 [예제 6]의 ROLLBACK에 의해 <사원> 테이블의 상태는 [예제 3]의 작업을 수행하기 전으로 되돌려진다.

 

예제 7 : SAVEPOINT 없이 ROLLBACK 을 수행하시오.

ROLLBACK;

• '사원번호'가 40인 사원의 정보를 삭제한 후, COMMIT을 수행했으므로, [예제 7]의 ROLLBACK이 적용되는 시점은 [예제 1]의 COMMIT 이후 새롭게 작업이 수행되는 [예제 2]의 작업부터이다.

 

TCL(Transaction Control Language)

• COMMIT, ROLLBACK, SAVEPOINT는 트랜잭션을 제어하는 용도로 사용되므로 TCL(Transaction Control Language)로 분류하기도 한다.
• 하지만 기능을 제어하는 명령이라는 공통점으로 DCL의 일부로 분류하기도 한다.

 

 

(3) SQL - DML

DML(Data Manipulation Language, 데이터 조작어)

• 데이터베이스 사용자가 저장된 데이터를 실질적으로 관리하는데 사용되는 언어
• 데이터베이스 사용자와 데이터베이스 관리 시스템 간의 인터페이스를 제공한다.
• DML의 유형

명령문	기능
SELECT	테이블에서 튜플을 검색함.
INSERT	테이블에서 새로운 튜플을 삽입함.
DELETE	테이블에서 튜플을 삭제함.
UPDATE	테이블에서 튜플의 내용을 갱신함.

 

삽입문(INSERT INTO ~)

• 기본 테이블에 새로운 튜플을 삽입할 때 사용한다.
• 일반 형식

INSERT INTO 테이블명([속성명1, 속성명2, ...])
VALUES (데이터1, 데이터2, ...);

• 대응하는 속성과 데이터는 개수와 데이터 유형이 일치해야 한다.
• 기본 테이블의 모든 속성을 사용할 때는 속성명을 생략할 수 있다.
• SELECT 문을 사용하여 다른 테이블의 검색 결과를 삽입할 수 있다.

 

 

예제 1 : <사원> 테이블에 (이름 - 홍승현, 부서 - 인터넷)을 삽입하시오.

INSERT INTO 사원(이름, 부서) VALUES('홍승현', '인터넷');

 

예제 2 : <사원> 테이블에 ('장보고', '기획', #05/03/73#, '홍제동', 90)을 삽입하시오.

INSERT INTO 사원 VALUES('장보고', '기획', #05/03/73#, '홍제동', 90);

 

예제 3 : <사원> 테이블에 있는 편집부의 모든 튜플을 편집부원(이름, 생일, 주소, 기본급) 테이블에 삽입하시오.

INSERT INTO 편집부원(이름, 생일, 주소, 기본급)
SELECT 이름, 생일, 주소, 기본급
FROM 사원
WHERE 부서 = '편집';

 

삭제문(DELETE FROM ~)

• 기본 테이블에 있는 튜플들 중에서 특정 튜플(행)을 삭제할 때 사용한다.
• 일반 형식

DELETE
FROM 테이블명
[WHERE 조건];

• 모든 레코드를 삭제할 때는 WHERE 절을 생략한다.
• 모든 레코드를 삭제하더라도 테이블 구조는 남아 있기 때문에 디스크에서 테이블을 완전히 제거하는 DROP 과는 다르다.
  • DELETE 문은 테이블 구조나 테이블 자체는 그대로 남겨 두고, 테이블 내의 튜플들만 삭제한다.
  • 테이블을 완전히 제거하기 위해서는 DROP 문을 사용해야 한다.

 

 

 

예제 1 : <사원> 테이블에서 "임꺽정"에 대한 튜플을 삭제하시오.

DELETE
FROM 사원
WHERE 이름 = "임꺽정";

 

예제 2 : <사원> 테이블에서 "인터넷" 부서에 대한 모든 튜플을 삭제하시오.

DELETE 
FROM 사원
WHERE 부서 = '인터넷';

 

예제 3 : <사원> 테이블의 모든 레코드를 삭제하시오.

DELETE
FROM 사원;

 

갱신문(UPDATE ~ SET ~)

• 기본 테이블에 있는 튜플들 중에서 특정 튜플(행)의 내용을 변경할 때 사용한다.
• 일반 형식

UPDATE 테이블명
SET 속성명 = 데이터[, 속성명=데이터, ...]
[WHERE 조건];

 

예제 1 : <사원> 테이블에서 "홍길동"의 '주소'를 "수색동"으로 수정하시오.

UPDATE 사원
SET 주소 = '수색동'
WHERE 이름 = '홍길동';

 

예제 2 : <사원> 테이블에서 "황진이"의 '부서'를 "기획부"로 변경하고, '기본급'을 5만원 인상시키시오.

 

UPDATE 사원
SET 부서 = '기획', 기본급 = 기본급 + 5
WHERE 이름 = '황진이';

 

 

(4) DML - SELECT-1

일반 형식

SELECT [PREDICATE] [테이블명.]속성명 [AS 별칭][, [테이블명.]속성명, ...]       /* ※ */
[, 그룹함수(속성명) [AS 별칭]]
[, WINDOW함수 OVER (PARTITION BY 속성명1, 속성명2, ...
              ORDER BY 속성명3, 속성명4, ...)]
FROM 테이블명[, 테이블명, ...]                                              /* ※ */
[WHERE 조건]                                                             /* ※ */
[GROUP BY 속성명, 속성명, ...]
[HAVING 조건]
[ORDER BY 속성명 [ASC | DESC]];                                           /* ※ */

 

• SELECT 절
  • PREDICATE : 검색할 튜플 수를 제한하는 명령어를 기술함.
    • DISTINCT : 중복된 튜플이 있으면, 그 중 첫 번째 한 개만 표시함.
  • 속성명 : 검새하여 불러올 속성(열) 또는 속성을 이용한 수식을 지정함.
  • AS : 속성이나 연산의 이름을 다른 이름으로 표시하기 위해 사용함.
• FROM 절 : 검색할 데이터가 들어있는 테이블 이름을 기술함.
• WHERE 절 : 검색할 조건을 기술함.
• ORDER BY 절 : 데이터를 정렬하여 검색할 때 사용함.
  • 속성명 : 정렬의 기준이 되는 속성명을 기술함.
  • [ASC | DESC] : 정렬 방식
    • ASC : 오름차순
    • DESC : 내림차순
    • 생략하면 오름차순으로 정렬됨.

 

조건 연산자

비교 연산자

연산자	=	<>	>	<	>=	<=
의미	같다	같지 않다	크다	작다	크거나 같다	작거나 같다

 

논리 연산자

• NOT, AND, OR

 

LIKE 연산자

• 대표 문자를 이용해 지정된 속성의 값이 문자 패턴과 일치하는 튜플을 검색하기 위해 사용된다.

대표 문자	%	_	#
의미	모든 문자를 대표함.	문자 하나를 대표함.	숫자 하나를 대표함.

 

 

기본 검색

• SELECT 절에 원하는 속성을 지정하여 검색한다.

 

예제 1 : <사원> 테이블의 모든 튜플을 검색하시오.

SELECT * FROM 사원;
SELECT 사원.* FROM 사원;
SELECT 이름, 부서, 생일, 주소, 기본급 FROM 사원;
SELECT 사원.이름, 사원.부서, 사원.생일, 사원.주소, 사원.기본급 FROM 사원;

 

 

예제 2 : <사원> 테이블에서 '주소'만 검색하되, 같은 '주소'는 한 번만 출력하시오.

SELECT DISTINCT 주소
FROM 사원;

 

 

예제 3 : <사원> 테이블에서 '기본급'에 특별수당 10을 더한 월급을 "XX부서의 XXX의 월급 XXX" 형태로 출력하시오.

SELECT 부서 + '부서의' AS 부서2, 이름 + '의 월급' AS 이름2, 기본급 + 10 AS 기본급2
FROM 사원;

 

 

조건 지정 검색

• WHERE 절에 조건을 지정하여 조건에 만족하는 튜플만 검색한다.

 

예제 1 : <사원> 테이블에서 '기획' 부의 모든 튜플을 검색하시오.

SELECT *
FROM 사원
WHERE 부서 = '기획';

 

 

예제 2 : <사원> 테이블에서 "기획" 부서에 근무하면서 "대흥동"에 사는 사람의 튜플을 검색하시오.

SELECT *
FROM 사원
WHERE 부서 = '기획' AND 주소 = '대흥동';

 

 

예제 3 : <사원> 테이블에서 '부서'가 "기획"이거나 "인터넷"인 튜플을 검색하시오.

SELECT *
FROM 사원
WHERE 부서 = '기획' OR 부서 = '인터넷';

 

 

예제 4 : <사원> 테이블에서 성이 '김'인 사람의 튜플을 검색하시오.

SELECT *
FROM 사원
WHERE 이름 LIKE "김%";

 

예제 5 : <사원> 테이블에서 '생일'이 '01/01/69' 에서 '12/31/73' 사이인 튜플을 검색하시오.

SELECT *
FROM 사원
WHERE 생일 BETWEEN #01/01/69# AND #12/31/73#;

 

예제 6 : <사원> 테이블에서 '주소'가 NULL인 튜플을 검색하시오.

SELECT *
FROM 사원
WHERE 주소 IS NULL;

• NULL이 아닌 값을 검색할 때는 NOT NULL 을 사용하면 된다.

 

정렬 검색

• ORDER BY 절에 특정 속성을 지정하여 지정된 속성으로 자료를 정렬하여 검색한다.

 

예제 1 : <사원> 테이블에서 '주소'를 기준으로 내림차순 정렬시켜 상위 2개 튜플만 검색하시오.

SELECT TOP 2 *
FROM 사원
ORDER BY 주소 DESC;

 

예제 2 : <사원> 테이블에서 '부서'를 기준으로 오름차순 정렬하고, 같은 '부서'에 대해서는 '이름'을 기준으로 내림차순 정렬시켜서 검색하시오.

SELECT *
FROM 사원
ORDER BY 부서 ASC, 이름 DESC;

 

하위 질의

• 조건절에 주어진 질의를 먼저 수행하여 그 검색 결과를 조건절의 피연산자로 사용한다.

 

예제 1 : '취미'가 "나이트댄스"인 사원의 '이름'과 '주소'를 검색하시오.

SELECT 이름, 주소
FROM 사원
WHERE 이름 = (SELECT 이름 FROM 여가활동 WHERE 취미 = '나이트댄스');

 

예제 2 : 취미 활동을 하지 않는 사원들을 검색하시오.

SELECT *
FROM 사원
WHERE 이름 NOT IN (SELECT 이름 FROM 여가활동);

 

예제 3 : 취미 활동을 하는 사원들의 부서를 검색하시오.

SELECT 부서
FROM 사원
WHERE EXISTS (SELECT 이름 FROM 여가활동 WHERE 여가활동.이름 = 사원.이름);

• exists ( ) 는 하위 질의로 검색된 결과가 존재하는지 확인할 때 사용한다.

 

복수 테이블 검색

• 여러 테이블을 대상으로 검색을 수행한다.

 

예제 : '경력'이 10년 이상인 사원의 '이름', '부서', '취미', '경력'을 검색하시오.

SELECT 사원.이름, 사원.부서, 여가활동.취미, 여가활동.경력
FROM 사원, 여가활동
WHERE 여가활동.경력 >= 10 AND 사원.이름 = 여가활동.이름;

 

 

(5) DML - SELECT-2

일반 형식

SELECT [PREDICATE] [테이블명.]속성명 [AS 별칭][, [테이블명.]속성명, ...]       /* ※ */
[, 그룹함수(속성명) [AS 별칭]]
[, WINDOW함수 OVER (PARTITION BY 속성명1, 속성명2, ...                      /* ※ */
              ORDER BY 속성명3, 속성명4, ...)]
FROM 테이블명[, 테이블명, ...]                                              
[WHERE 조건]                                                             
[GROUP BY 속성명, 속성명, ...]                                             /* ※ */
[HAVING 조건]                                                            /* ※ */
[ORDER BY 속성명 [ASC | DESC]];

• 그룹함수 : GROUP BY 절에 지정된 그룹별로 속성의 값을 집계할 함수를 기술함.
• WINDOW 함수 : GROUP BY 절을 이용하지 않고, 속성의 값을 집계할 함수를 기술함.
  • PARTITION BY : WINDOW 함수의 적용 범위가 될 속성을 지정함.
  • ORDER BY : PARTITION 안에서 정렬 기준으로 사용할 속성을 지정함.
• GROUP BY 절
  • 특정 속성을 기준으로 그룹화하여 검색할 때 사용함.
  • 일반적으로 GROUP BY 절은 그룹 함수와 함께 사용됨.
• HAVING 절 : GROUP BY와 함께 사용되며, 그룹에 대한 조건을 지정함.

 

그룹 함수

• GROUP BY 절에 지정된 그룹별로 속성의 값을 집계할 때 사용된다.

함수	기능
COUNT(속성명)	그룹별 튜플 수를 구하는 함수
SUM(속성명)	그룹별 합계를 구하는 함수
AVG(속성명)	그룹별 평균을 구하는 함수
MAX(속성명)	그룹별 최대값을 구하는 함수
MIN(속성명)	그룹별 최소값을 구하는 함수
STDDEV(속성명)	그룹별 표준편차를 구하는 함수
VARIANCE(속성명)	그룹별 분산을 구하는 함수
ROLLUP(속성명, 속성명, ...)	- 인수로 주어진 속성을 대상으로 그룹별 소계를 구하는 함수 - 속성의 개수가 $n$ 개이면 $n+1$ 레벨까지, 하위 레벨에서 상위 레벨 순으로 데이터가 집계됨.
CUBE(속성명, 속성명, ...)	- ROLLUP과 유사한 형태이지만, CUBE는 인수로 주어진 속성을 대상으로 모든 조합의 그룹별 소계를 구함. - 속성의 개수가 $n$ 개이면 $2^{n}$ 레벨까지, 상위 레벨에서 하위레벨 순으로 데이터가 집계됨.

 

WINDOW 함수

• GROUP BY 절을 이용하지 않고, 함수의 인수로 지정한 속성의 값을 집계한다.
• 함수의 인수로 지정한 속성이 집계할 범위가 되는데, 이를 윈도우(WINDOW)라고 부른다.
• WINDOW 함수
  • ROW_NUMBER() : 윈도우별로 각 레코드에 대한 일련번호를 반환한다.
  • RANK() : 윈도우별로 순위를 반환하며, 공동 순위를 반영한다.
  • DENSE_RANK() : 윈도우별로 순위를 반환하며, 공동 순위를 무시하고 순위를 부여한다.

 

WINDOW 함수 이용 검색

• GROUP BY 절을 이용하지 않고, 함수의 인수로 지정한 속성을 범위로 하여 속성의 값을 집계한다.

 

 

예제 1 : <상여금> 테이블에서 '상여내역' 별로 '상여금'에 대한 일련 번호를 구하시오. (단, 순서는 내림차순이며 속성명은 'NO'로 할 것)

SELECT 상여내역, 상여금, ROW_NUMBER() OVER (PARTITION BY 상여내역 ORDER BY 상여금 DESC) AS NO
FROM 상여금;

 

예제 2 : <상여금> 테이블에서 '상여내역'별로 '상여금'에 대한 순위를 구하시오. (단, 순서는 내림차순이며, 속성명은 '상여금순위'로 하고, RANK() 함수를 이용할 것)

SELECT 상여내역, 상여금, RANK() OVER (PARTITION BY 상여내역 ORDER BY 상여금 DESC) AS 상여금순위
FROM 상여금;

 

그룹 지정 검색

• GROUP BY 절에 지정한 속성을 기준으로 자료를 그룹화하여 검색한다.

 

예제 1 : <상여금> 테이블에서 '부서'별 '상여금'의 평균을 구하시오.

SELECT 부서, AVG(상여금) AS 평균
FROM 상여금
GROUP BY 부서;

 

예제 2 : <상여금> 테이블에서 부서별 튜플 수를 검색하시오.

SELECT 부서, COUNT(*) AS 사원수
FROM 상여금
GROUP BY 부서;

 

예제 3 : <상여금> 테이블에서 '상여금'이 100 이상인 사원이 2명 이상인 '부서'의 튜플 수를 구하시오.

SELECT 부서, COUNT(*) AS 사원수
FROM 상여금
WHERE 상여금 >= 100
GROUP BY 부서
HAVING COUNT(*) >= 2;

 

예제 4 : <상여금> 테이블의 '부서', '상여내역' 그리고 '상여금'에 대해 부서별 상여내역별 소계와 전체 합계를 검색하시오. (단, 속성명은 '상여금합계'로 하고, ROLLUP 함수를 사용할 것)

SELECT 부서, 상여내역, SUM(상여금) AS 상여금합계
FROM 상여금
GROUP BY ROLLUP(부서, 상여내역);

 

예제 5 : <상여금> 테이블의 '부서', '상여내역' 그리고 '상여금'에 대해 부서별 상여내역별 소계와 전체 합계를 검색하시오. (단, 속성명은 '상여금합계'로 하고, CUBE 함수를 사용할 것)

SELECT 부서, 상여내역, SUM(상여금) AS 상여금합계
FROM 상여금
GROUP BY CUBE(부서, 상여내역);

 

집합 연산자를 이용한 통합 질의

• 집합 연산자를 사용하여 2개 이상의 테이블의 데이터를 하나로 통합한다.
• 표기 형식

SELECT 속성명1, 속성명2, ...
FROM 테이블명
UNION | UNION ALL | INTERSECT | EXCEPT
SELECT 속성명1, 속성명2, ...
FROM 테이블명
[ORDER BY 속성명 [ASC | DESC]];

• 2개의 SELECT 문에 기술한 속성들은 개수와 데이터 유형이 서로 동일해야 한다.
• 집합 연산자의 종류(통합 질의의 종류)

집합 연산자	설명	집합 종류
UNION	- 두 SELECT 문의 조회 결과를 통합하여 모두 출력함. - 중복된 행은 한 번만 출력함.	합집합
UNION ALL	- 두 SELECT 문의 조회 결과를 통합하여 모두 출력함. - 중복된 행도 그대로 출력함.	합집합
INTERSECT	두 SELECT 문의 조회 결과 중, 공통된 행만 출력함.	교집합
EXCEPT	첫 번째 SELECT 문의 조회 결과에서 두 번째 SELECT 문의 조회 결과를 제외한 행을 출력함.	차집합

 

 

예제 1 : <사원> 테이블과 <직원> 테이블을 통합하는 질의문을 작성하시오. (단, 같은 레코드가 중복되어 나오지 않게 하시오.)

SELECT *
FROM 사원
UNION
SELECT *
FROM 직원;

 

예제 2 : <사원> 테이블과 <직원> 테이블에 공통으로 존재하는 레코드만 통합하는 질의문을 작성하시오.

SELECT *
FROM 사원
INTERSECT
SELECT *
FROM 직원;

 

 

(6) DML - JOIN

JOIN

• 2개의 릴레이션에서 연관된 튜플들을 결합하여, 하나의 새로운 릴레이션을 반환한다.
• 일반적으로 FROM 절에 기술하지만, 릴레이션이 사용되는 곳 어디에나 사용할 수 있다.
• 크게 INNER JOIN 과 OUTER JOIN 으로 구분된다.

 

INNER JOIN

• 일반적으로 EQUI JOIN 과 NON-EQUI JOIN 으로 구분된다.
  • 조건이 없는 INNER JOIN 을 수행하면 CROSS JOIN 과 동일한 결과를 얻을 수 있다.
    • CROSS JOIN(교차 조인)
      • 조인하는 두 테이블에 있는 튜플들의 순서쌍을 결과로 반환한다.
      • 교차 조인의 결과로 반환되는 테이블의 행의 수는 두 테이블의 행 수를 곱한 것과 같다.

EQUI JOIN

• JOIN 대상 테이블에서 공통 속성을 기준으로 '='(equal) 비교에 의해 같은 값을 가지는 행을 연결하여 결과를 생성하는 JOIN 방법
• EQUI JOIN에서 JOIN 조건이 '='일 때 동일한 속성이 2번 나타나게 되는데, 이 중 중복된 속성을 제거하여 같은 속성을 한 번만 표기하는 방법을 NATURAL JOIN 이라고 한다.
• EQUI JOIN 에서 연결 고리가 되는 공통 속성을 JOIN 속성이라고 한다.
• WHERE 절을 이용한 EQUI JOIN 의 표기 형식

SELECT [테이블명1.]속성명, [테이블명2.]속성명, ...
FROM 테이블명1, 테이블명2, ...
WHERE 테이블명1.속성명 = 테이블명2.속성명;

• NATURAL JOIN 절을 이용한 EQUI JOIN 의 표기 형식

SELECT [테이블명1.]속성명, [테이블명2.]속성명, ...
FROM 테이블명1 NATURAL JOIN 테이블명2;

• JOIN ~ USING 절을 이용한 EQUI JOIN 의 표기 형식

SELECT [테이블명1.]속성명, [테이블명2.]속성명, ...
FROM 테이블명1 JOIN 테이블명2 USING(속성명);

 

 

예제 1 : <학생> 테이블과 <학과> 테이블에서 '학과코드' 값이 같은 튜플을 JOIN 하여 '학번', '이름', '학과코드', '학과명'을 출력하는 SQL문을 작성하시오.

/* 방법 1 */
SELECT 학번, 이름, 학생.학과코드, 학과명
FROM 학생, 학과
WHERE 학생.학과코드 = 학과.학과코드;

/* 방법 2 */
SELECT 학번, 이름, 학생.학과코드, 학과명
FROM 학생 NATURAL JOIN 학과;

/* 방법 3 */
SELECT 학번, 이름, 학생.학과코드, 학과명
FROM 학생 JOIN 학과 USING(학과코드);

 

NON-EQUI JOIN

• JOIN 조건에 '=' 조건이 아닌 나머지 비교 연산자, 즉 >, <, <>, >=, <= 연산자를 사용하는 JOIN 방법
• 표기 형식

SELECT [테이블명1.]속성명, [테이블명2.]속성명, ...
FROM 테이블명1, 테이블명2, ...
WHERE (NON-EQUI JOIN 조건);

 

예제 2 : <학생> 테이블과 <성적등급> 테이블을 JOIN 하여 각 학생의 '학번', '이름', '성적', '등급'을 출력하는 SQL문을 작성하시오.

SELECT 학번, 이름, 성적, 등급
FROM 학생, 성적등급
WHERE 학생.성적 BETWEEN 성적등급.최저 AND 성적등급.최고;

 

OUTER JOIN

• 릴레이션에서 JOIN 조건에 만족하지 않는 튜플도 결과로 출력하기 위한 JOIN 방법
• LEFT OUTER JOIN, RIGHT OUTER JOIN, FULL OUTER JOIN 이 있다.

 

LEFT OUTER JOIN

• INNER JOIN의 결과를 구한 후, 우측 항 릴레이션의 어떤 튜플과도 맞지 않는 좌측 항의 릴레이션에 있는 튜플들에 NULL 값을 붙여서 INNER JOIN의 결과에 추가한다.
• 표기 형식

/* 방법 1 */
SELECT [테이블명1.]속성명, [테이블명2.]속성명, ...
FROM 테이블명1 LEFT OUTER JOIN 테이블명2
ON 테이블명1.속성명 = 테이블명2.속성명;

/* 방법 2 */
SELECT [테이블명1.]속성명, [테이블명2.]속성명, ...
FROM 테이블명1, 테이블명2
WHERE 테이블명1.속성명 = 테이블명2.속성명(+);

 

RIGHT OUTER JOIN

• INNER JOIN의 결과를 구한 후, 좌측 항 릴레이션의 어떤 튜플과도 맞지 않는 우측 항의 릴레이션에 있는 튜플들에 NULL 값을 붙여서 INNER JOIN 의 결과에 추가한다.
• 표기 형식

/* 방법 1 */
SELECT [테이블명1.]속성명, [테이블명2.]속성명, ...
FROM 테이블명1 RIGHT OUTER JOIN 테이블명2
ON 테이블명1.속성명 = 테이블명2.속성명;

/* 방법 2 */
SELECT [테이블명1.]속성명, [테이블명2.]속성명, ...
FROM 테이블명1, 테이블명2
WHERE 테이블명1.속성명(+) = 테이블명2.속성명;

 

FULL OUTER JOIN

• LEFT OUTER JOIN 과 RIGHT OUTER JOIN 을 합쳐 놓은 것
• INNER JOIN의 결과를 구한 후, 좌측 항의 릴레이션의 튜플들에 대해 우측 항의 릴레이션의 어떤 튜플과도 맞지 않는 튜플들에 NULL 값을 붙여서 INNER JOIN 의 결과에 추가한다.
  • 그리고 유사하게 우측 항의 릴레이션의 튜플들에 대해 좌측 항의 릴레이션의 어떤 튜플과도 맞지 않는 튜플들에 NULL 값을 붙여서 INNER JOIN 의 결과에 추가한다.
• 표기 형식

SELECT [테이블명1.]속성명, [테이블명2.]속성명, ...
FROM 테이블명1 FULL OUTER JOIN 테이블명2
ON 테이블명1.속성명 = 테이블명2.속성명;

 

예제 1 : <학생> 테이블과 <학과> 테이블에서 '학과코드' 값이 같은 튜플을 JOIN 하여 '학번', '이름', '학과코드', '학과명'을 출력하는 SQL문을 작성하시오. 이때, '학과코드'가 입력되지 않은 학생도 출력하시오.

/* 방법 1 */
SELECT 학번, 이름, 학생.학과코드, 학과명
FROM 학생 LEFT OUTER JOIN 학과
ON 학생.학과코드 = 학과.학과코드;

/* 방법 2 */
SELECT 학번, 이름, 학생.학과코드, 학과명
FROM 학생, 학과
ON 학생.학과코드 = 학과.학과코드(+);

• 해설
  • INNER JOIN 을 하면 '학과코드'가 입력되지 않은 "박치민"은 출력되지 않는다.
  • 그러므로 JOIN 구문을 기준으로 왼쪽 테이블, 즉 <학생>의 자료는 모두 출력되는 LEFT JOIN 을 사용한 것이다.
  • 다음과 같이 JOIN 구문을 기준으로 테이블의 위치를 교환하여 RIGHT JOIN 을 사용해도 결과는 같다.

/* 방법 1 */
SELECT 학번, 이름, 학생.학과코드, 학과명
FROM 학과 RIGHT OUTER JOIN 학생
ON 학과.학과코드 = 학생.학과코드;

/* 방법 2 */
SELECT 학번, 이름, 학생.학과코드, 학과명
FROM 학과, 학생
ON 학과.학과코드(+) = 학생.학과코드;

 

 

예제 2 : <학생> 테이블과 <학과> 테이블에서 '학과코드' 값이 같은 튜플을 JOIN 하여 '학번', '이름', '학과코드', '학과명'을 출력하는 SQL 문을 작성하시오. 이때 '학과코드'가 입력 안 된 학생이나 학생이 없는 '학과코드'도 모두 출력하시오.

SELECT 학번, 이름, 학과.학과코드, 학과명
FROM 학생 FULL OUTER JOIN 학과
ON 학생.학과코드 = 학과.학과코드;

• 해설
  • FULL OUTER JOIN 을 하면 JOIN 구문으로 연결되지 않는 자료도 모두 출력된다.
  • "박치민"은 '학과코드'가 없고, "eng"는 <학생> 테이블에 등록되지 않아서 연결고리가 없지만, FULL OUTER JOIN 을 했으므로 모두 출력된다.

 

 

(7) 프로시저(Procedure)

프로시저(Procedure)

• SQL을 사용하여 작성한 일련의 작업을 저장해두고 호출을 통해 원할 때마다 저장한 작업을 수행하도록 하는 절차형 SQL
• 데이터베이스에 저장되어 수행되기 때문에 스토어드(Stored) 프로시저라고도 불린다.
• 시스템의 일일 마감 작업, 일괄(Batch) 작업 등에 주로 사용된다.

 

프로시저의 구성도

• DECLARE : 프로시저의 명칭, 변수, 인수, 데이터 타입을 정의하는 선언부
• BEGIN / END : 프로시저의 시작과 종료
• CONTROL : 조건문 또는 반복문이 삽입되어 순차적으로 처리됨.
• SQL : DML, DCL 이 삽입되어 데이터 관리를 위한 조회, 추가, 수정, 삭제 작업을 수행함.
• EXCEPTION : BEGIN ~ END 안의 구문 실행 시 예외가 발생하면 이를 처리하는 방법을 정의함.
• TRANSACTION : 수행된 데이터 작업들을 DB에 적용할지 취소할지를 결정하는 처리부

 

프로시저 생성

• 프로시저를 생성하기 위해 CREATE PROCEDURE 명령어를 사용한다.
• 표기 형식

CREATE [OR REPLACE] PROCEDURE 프로시저명(파라미터)
[지역변수 선언]
BEGIN
    프로시저 BODY;
END;

• OR REPLACE
  • 선택적인(Optional) 예약어
  • 이 예약어를 사용하면 동일한 프로시저 이름이 이미 존재하는 경우, 기존의 프로시저를 대체할 수 있다.
• 프로시저명 : 생성하려는 프로시저의 이름을 지정
• 파라미터 : 프로시저 파라미터로는 다음과 같은 것들이 올 수 있다.
  • IN : 호출 프로그램이 프로시저에게 값을 전달할 때 지정
  • OUT : 프로시저가 호출 프로그램에게 값을 반환할 때 지정
  • INPUT : 호출 프로그램이 프로시저에게 값을 전달하고, 프로시저 실행 후 호출 프로그램에 값을 반환할 때 지정
  • 매개변수명 : 호출 프로그램으로부터 전달받은 값을 저장할 변수의 이름을 지정
  • 자료형 : 변수의 자료형을 지정
• 프로시저 BODY
  • 프로시저의 코드를 기록하는 부분
  • BEGIN 에서 시작하여 END 로 끝나며, BEGIN과 END 사이에는 적어도 하나의 SQL 문이 있어야 한다.

 

예제 : '사원번호'를 입력받아 해당 사원의 '지급방식'을 "S"로 변경하는 프로시저를 생성하시오.

CREATE OR REPLACE PROCEDURE emp_change_s(i_사원번호 IN INT)
IS
BEGIN
    UPDATE 급여 SET 지급방식 = 'S' WHERE 사원번호 = i_사원번호;
    EXCEPTION
        WHEN PROGRAM_ERROR THEN
            ROLLBACK;
    COMMIT;
END;

 

프로시저 실행

• 프로시저를 실행하기 위해서는 EXECUTE 명령어 또는 CALL 명령어를 사용하며, EXECUTE 명령어를 줄여서 EXEC 로 사용하기도 한다.
• 표기 형식

EXECUTE 프로시저명;
EXEC 프로시저명;
CALL 프로시저명;

 

예제 : '사원번호' 32를 인수로 하여 위에서 생성된 emp_change_s 프로시저를 실행하시오.

EXECUTE emp_change_s(32);

 

프로시저 제거

• 프로시저를 제거하기 위해서 DROP PROCEDURE 명령어를 사용한다.
• 표기 형식

DROP PROCEDURE 프로시저명;

 

예제 : 위에서 생성된 프로시저 emp_change_s를 제거하시오.

DROP PROCEDURE emp_change_s;

 

 

(8) 트리거(Trigger)

트리거(Trigger)

• 데이터베이스 시스템에서 데이터의 삽입(Insert), 갱신(Update), 삭제(Delete) 등의 이벤트(Event)가 발생할 때 관련 작업이 자동으로 수행되게 하는 절차형 SQL
• 트리거는 데이터베이스에 저장되며, 데이터 변경 및 무결성 유지, 로그 메시지 출력 등의 목적으로 사용된다.
• 트리거의 구문에는 DCL(데이터 제어어)을 사용할 수 없으며, DCL이 포함된 프로시저나 함수를 호출하는 경우에 오류가 발생한다.

 

트리거의 구성도

• DECLARE : 트리거의 명칭, 변수 및 상수, 데이터 타입을 정의하는 선언부
• EVENT : 트리거가 실행되는 조건을 명시
• BEGIN / END : 트리거의 시작과 종료
• CONTROL : 조건문 또는 반복문이 삽입되어 순차적으로 처리됨.
• SQL : DML 문이 삽입되어 데이터 관리를 위한 조회, 추가, 수정, 삭제 작업을 수행함.
• EXCEPTION : BEGIN ~ END 안의 구문 실행 시 예외가 발생하면 이를 처리하는 방법을 정의함.

 

트리거의 생성

• 트리거를 생성하기 위해서는 CREATE TRIGGER 명령어를 사용한다.
• 표기 형식

CREATE [OR REPLACE] TRIGGER 트리거명 [동작시기 옵션][동작 옵션] ON 테이블명
REFERENCING [NEW | OLD] AS 테이블명
FOR EACH ROW
[WHEN 조건식]
BEGIN
    트리거 BODY;
END;

• OR REPLACE
  • 선택적인(Optional) 예약어
  • 이 예약어를 사용하면 동일한 트리거 이름이 이미 존재하는 경우, 기존의 트리거를 대체할 수 있음. 
• 동작시기 옵션 : 트리거가 실행될 때를 지정함.
  • AFTER : 테이블이 변경된 후에 트리거가 실행됨.
  • BEFORE : 테이블이 변경되기 전에 트리거가 실행됨.
• 동작 옵션 : 트리거가 실행되게 할 작업의 종류를 지정함.
  • INSERT : 테이블에 새로운 튜플을 삽입할 때 트리거가 실행됨.
  • DELETE : 테이블의 튜플을 삭제할 때 트리거가 실행됨.
  • UPDATE : 테이블의 튜플을 수정할 때 트리거가 실행됨.
• NEW | OLD : 트리거가 적용될 테이블의 별칭을 지정함.
  
  • NEW : 추가되거나 수정에 참여할 튜플들의 집합(테이블)을 의미함.
  • OLD : 수정되거나 삭제 전 대상이 되는 튜플들의 집합(테이블)을 의미함.
• FOR EACH ROW : 각 튜플마다 트리거를 적용한다는 의미임.
• WHEN 조건식 
  • 선택적인(Optional) 예약어
  • 트리거를 적용할 튜플의 조건을 지정함.
• 트리거 BODY 
  • 트리거의 본문 코드를 입력하는 부분
  • BEGIN으로 시작해서 END로 끝나는데, 적어도 하나 이상의 SQL문이 있어야 함.
    • 그렇지 않을 겨우 오류가 발생함.

 

예제 :  <학생> 테이블에 새로운 튜플이 삽입될 때, 삽입되는 튜플에 학년 정보가 누락 됐으면 '학년' 속성에 "신입생"을 저장하는 트리거를 '학년정보_tri'라는 이름으로 정의 하시오.

CREATE TRIGGER 학년정보_tri BEFORE INSERT ON 학생
REFERENCING NEW AS new_table
FOR EACH ROW
WHEN (new_table.학년 IS NULL)
BEGIN
    :new_table.학년 := '신입생';    /* NEW 또는 OLD로 지정된 테이블 이름 앞에는 콜론(:)이 들어감. */
END                               /* A := B -> A에 B를 저장하라는 의미로, '='가 아닌 ':='를 사용함. */

 

트리거의 제거

• 트리거를 제거하기 위해 DROP TRIGGER 명령어를 사용한다.
• 표기 형식

DROP TRIGGER 트리거명;

 

예제 : '학년정보_tri' 라는 트리거를 제거하는 SQL문을 작성하시오.

DROP TRIGGER 학년정보_tri;

 

 

(9) 사용자 정의 함수

사용자 정의 함수

• 프로시저와 유사하게 SQL을 사용하여 일련의 작업을 연속적으로 처리하지만, 종료 시 처리 결과로 단일값만을 변환하는 절차형 SQL
• 데이터베이스에 저장되어 SELECT, INSERT, DELETE, UPDATE 등 DML 문의 호출에 의해 실행된다.
• 예약어 RETURN 을 통해 단일값을 반환하며, 출력 파라미터가 없다.

 

사용자 정의 함수의 구성도

• DECLARE : 사용자 정의 함수의 명칭, 변수 및 상수, 데이터 타입을 정의하는 선언부
• BEGIN / END : 사용자 정의 함수의 시작과 종료
• CONTROL : 조건문 또는 반복문이 삽입되어 순차적으로 처리됨.
• SQL : SELECT 문이 삽입되어 데이터 조회 작업을 수행함.
• EXCEPTION : BEGIN ~ END 안의 구문 실행 시 예외가 발생하면 이를 처리하는 방법을 정의함.
• RETURN : 호출 프로그램에 반환할 값이나 변수를 정의함.

 

사용자 정의 함수 생성

• 사용자 정의 함수를 생성하기 위해 CREATE FUNCTION 명령어를 사용한다.
• 표기 형식

CREATE [OR REPLACE] FUNCTION 사용자 정의 함수명(파라미터)
[지역변수 선언]
BEGIN
    사용자 정의 함수 BODY;
    RETURN 반환값;
END;

• OR REPLACE
  • 선택적인(Optional) 예약어
  • 이 예약어를 사용하면 동일한 사용자 정의 함수 이름이 이미 존재하는 경우, 기존의 사용자 정의 함수를 대체할 수 있다.
• 파라미터 : 사용자 정의 함수의 파라미터로는 다음과 같은 것들이 올 수 있다.
  • IN : 호출 프로그램이 사용자 정의 함수에게 값을 전달할 때 지정
  • 매개변수명 : 호출 프로그램으로부터 전달받은 값을 저장할 변수의 이름을 지정
  • 자료형 : 변수의 자료형을 지정
• 사용자 정의 함수 BODY
  • 사용자 정의 함수의 코드를 기록하는 부분
  • BEGIN 에서 시작하여 END 로 끝나며, BEGIN과 END 사이에는 적어도 하나의 SQL 문이 있어야 한다.
• RETURN 반환값 : 반환할 값이나 반환할 값이 저장된 변수를 호출 프로그램으로 돌려준다.

 

예제 : 'i_성별코드'를 입력 받아 1이면 "남자"를, 2면 "여자"를 반환하는 사용자 정의 함수를 'Get_S_성별' 이라는 이름으로 정의하시오.

CREATE FUNCTION Get_S_성별(i_성별코드 IN INT)
RETURN VARCHAR2    /* 안정성을 위해 VARCHAR 대신 VARCHAR2로 사용하는 것이 권장됨. (Oracle) */
IS
BEGIN
    IF i_성별코드 = 1 THEN
        RETURN '남자';
    ELSE
        RETURN '여자';
    END IF;
END;

 

사용자 정의 함수 실행

• 사용자 정의 함수는 DML 에서 속성명이나 값이 놓일 자리를 대체하여 사용된다.
• 표기 형식

SELECT 사용자 정의 함수명 FROM 테이블명;
INSERT INTO 테이블명(속성명) VALUES(사용자 저의 함수명);
DELETE FROM 테이블명 WHERE 속성명 = 사용자 정의 함수명;
UPDATE 테이블명 SET 속성명 = 사용자 정의 함수명;

 

예제 : 다음의 <사원> 테이블을 출력하되, '성별코드'는 앞에서 사용자 정의 함수 'Get_S_성별'에 값을 전달하여 반환받은 값으로 대체하여 출력하시오.

SELECT 이름, Get_S_성별(성별코드) FROM 사원;

 

사용자 정의 함수 제거

• 사용자 정의 함수를 제거하기 위해서 DROP FUNCTION 명령어를 사용한다.
• 표기 형식

DROP FUNCTION 사용자 정의 함수명;

 

예제 : 위에서 생성된 사용자 정의 함수 'Get_S_성별'을 제거하시오. 

DROP FUNCTION Get_S_성별;

 

 

(10) 제어문

제어문

• 위에서 아래로 차례대로 실행되는 절차형 SQL의 진행 순서를 변경하기 위해 사용하는 명령문
• 제어문에는 IF, LOOP 등이 있다.

 

IF 문

• 조건에 따라 실행할 문장을 달리하는 제어문

형식 1 : 조건이 참일 때만 실행한다.

IF 조건 THEN
    실행할 문장1;
    실행할 문장2;
        ...
END IF;

 

예제 : x가 10보다 크면 화면에 "true"를 출력하기

DECLARE
    x INT := 20;
BEGIN
    IF x > 10 THEN
        DBMS_OUTPUT.PUT_LINE('true');
    END IF;
END;

*DBMS_OUTPUT.PUT_LINE(데이터) 는 '데이터'에 넣은 변수나 값을 화면에 출력해주는 함수이다.

 

형식 2 : 조건이 참일 때와 거짓일 때 실행할 문장이 다르다.

IF 조건 THEN
    실행할 문장1;
ELSE
    실행할 문장2;
END IF;

 

예제 : x가 10보다 크면 화면에 "true"를 출력하고, 아니면 "false"를 출력하기

DECLARE
    x INT := 10;
BEGIN
    IF x > 10 THEN
        DBMS_OUTPUT.PUT_LINE('true');
    ELSE
        DBMS_OUTPUT.PUT_LINE('false');
    END IF;
END;

 

LOOP 문

• 조건에 따라 실행할 문장을 반복 수행하는 제어문
• 형식

LOOP
    실행할 문장;
    EXIT WHEN 조건;
END LOOP;

 

예제 : 1부터 10까지의 합을 구하는 절차형 SQL을 PL/SQL로 구현하시오.

DECLARE
    i INT := 0;
    i_sum INT := 0;
BEGIN
    LOOP
        i := i + 1;
        i_sum := i_sum + i;
        EXIT WHEN i >= 10;
    END LOOP;
END;

 

 

(11) 커서(Cursor)

커서(Cursor)

• 쿼리문의 처리 결과가 저장되어 있는 메모리 공간을 가리키는 포인터(Pointer)
• 커서의 수행은 열기(Open), 패치(Fetch), 닫기(Close)의 세 단계로 진행된다.

 

묵시적 커서(Implicit Cursor)

• DBMS에 의해 내부에서 자동으로 생성되어 사용되는 커서
• 커서의 속성을 조회하여 사용된 쿼리 정보를 열람하는 것이 가능하다.
• 수행된 쿼리문의 정상적인 수행 여부를 확인하기 위해 사용된다.
• 속성의 종류

SQL%FOUND	쿼리 수행의 결과로 패치(Fetch)된 튜플 수가 1개 이상이면 TRUE
SQL%NOTFOUND	쿼리 수행의 결과로 패치(Fetch)된 튜플 수가 0개이면 TRUE
SQL%ROWCOUNT	쿼리 수행의 결과로 패치(Fetch)된 튜플 수를 반환
SQL%ISOPEN	- 커서가 열린(Open) 상태이면 TRUE - 묵시적 커서는 자동으로 생성된 후 자동으로 닫히기 때문에 항상 FALSE

 

명시적 커서(Explicit Cursor)

• 사용자가 직접 정의해서 사용하는 커서
• 쿼리문의 결과를 저장하여 사용함으로써 동일한 쿼리가 반복 수행되어 데이터베이스 자원이 낭비되는 것을 방지한다.
• 커서는 기본적으로 '열기(Open) - 패치(Fetch) - 닫기(Close)' 순으로 이루어지며, 명시적 커서를 사용하기 위해서는 열기(Open) 단계 전에 선언(Declare) 해야 한다.

 

선언(Declare) 형식

• (매개변수1, 매개변수2, ...) 는 SELECT 문의 WHERE 절에 사용할 수 있으며, 생략이 가능하다.
• 커서는 SELECT 문의 실행 결과가 저장된 곳의 시작 위치를 가리킨다.

CURSOR 커서명(매개변수1, 매개변수2, ...)
IS
SELECT문;

 

열기(Open) 형식

• 커서를 사용하기 전에 반드시 적는다.

OPEN 커서명(매개변수1, 매개변수2, ...);

 

패치(Fetch) 형식

• 커서명과 커서에 저장된 튜플들의 각 속성과 같은 자료형을 가진 변수를 적고 데이터를 가져온다.

FETCH 커서명 INTO 변수1, 변수2, ...;

 

닫기(Close) 형식

• 사용된 커서는 메모리 해제를 위해 반드시 닫아야 한다.

CLOSE 커서명;

 

예제 : 다음은 <employee> 테이블로부터 id가 20보다 크거나 같은 튜플의 name을 출력하는 절차형 SQL을 PL/SQL로 구현한 것이다.

DECLARE
    p_name employee.name%TYPE;
    CURSOR cur_name(ff INT)
    IS
    SELECT name FROM employee WHERE id >= ff;
    
BEGIN
    Open cur_name(20);
    LOOP
        FETCH cur_name INTO p_name;
        EXIT WHEN cur_name%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(p_name);
    END LOOP;
    CLOSE cur_name;
END;

 

 

(12) DBMS 접속(Connection)

DBMS 접속

• 사용자가 데이터를 사용하기 위해 응용 시스템을 이용하여 DBMS에 접근하는 것
• 응용 시스템은 사용자로부터 매개 변수를 전달받아 SQL을 실행하고, DBMS로부터 전달받은 결과를 사용자에게 전달하는 매개체 역할을 수행한다.
• 인터넷을 통해 구동되는 웹 응용 프로그램은 웹 응용 시스템을 통해 DBMS에 접근한다.
• 웹 응용 시스템은 웹 서버와 웹 애플리케이션 서버(WAS)로 구성된다.

 

DBMS 접속 기술

• DBMS에 접근하기 위해 사용하는 API 또는 API의 사용을 편리하게 도와주는 프레임워크 등
• DBMS 접속 기술의 종류

접속 기술	특징
JDBC (Java DataBase Connectivity)	- Java 언어로 다양한 종류의 데이터베이스에 접속할 때 사용하는 표준 API - 1997년 2월 썬 마이크로시스템에서 출시 - 접속하려는 DBMS에 대한 드라이버가 필요함.
ODBC (Open DataBase Connectivity)	- 개발 언어에 관계 없이 데이터베이스에 접근하기 위한 표준 개방형 API - 1992년 9월 마이크로소프트에서 출시 - 접속하려는 DBMS의 인터페이스에 관계 없이 ODBC 문장을 사용하여 접속이 가능함.
MyBatis	- JDBC 코드를 단순화하여 사용할 수 있는 SQL Mapping 기반 오픈 소스 접속 프레임워크 - SQL 문장을 분리하여 XML 파일을 만들고, Mapping을 통해 SQL을 실행함.

 

동적 SQL(Dynamic SQL)

• 다양한 조건에 따라 SQL 구문을 동적으로 변경하여 처리할 수 있는 SQL 처리 방식
• 사용자로부터 SQL 문의 일부 또는 전부를 입력받아 실행할 수 있다.
• 응용 프로그램 수행 시 SQL이 변형될 수 있으므로, 프리컴파일(Precompile) 할 때 구문 분석, 접근 권한 확인 등을 할 수 없다.
  • 프리컴파일(Precompile)
    • 고급 언어를 기계어로 번역하는 컴파일(Compile) 전에 수행하는 작업
    • 필요한 라이브러리를 불러오거나 코드에 삽입된 SQL 문을 DB와 연결하는 작업을 수행함.
• 정적 SQL에 비해 속도가 느리지만, 상황에 따라 다양한 조건을 첨가하는 등 유연한 개발이 가능하다.

 

 

(13) SQL 테스트

SQL 테스트

• SQL이 작성 의도에 맞게 원하는 기능을 수행하는지 검증하는 과정
• 단문 SQL은 SQL 코드를 직접 실행한 후 결과를 확인하는 것으로, 간단히 테스트가 가능하다.
• 절차형 SQL은 테스트 전에 생성을 통해 구문 오류(Syntax Error)나 참조 오류의 존재 여부를 확인한다.
• 정상적으로 생성된 절차형 SQL은 디버깅을 통해 로직을 검증하고, 결과를 통해 최종적으로 확인한다.

 

단문 SQL 테스트

• DDL, DML, DCL 이 포함되어 있는 SQL과 TCL을 테스트하는 것으로, 직접 실행하여 결과물을 확인한다.
• DDL 이 작성된 개체는 DESCRIBE 명령어를 이용하여 속성, 자료형, 옵션 등을 확인할 수 있다.

DESC [개체명];    /* DESCRIBE = DESC, 개체의 정보를 확인할 때 사용하는 명령어 */

• DML 로 변경한 데이터는 SELECT 문으로 데이터의 정상적인 변경 여부를 확인할 수 있다.
• DCL 로 설정된 사용자 권한은 사용자 권한 정보가 저장된 테이블을 조회하여 확인할 수 있다.

 

예제 : MySQL에서 <학생> 테이블의 정보를 확인하는 SQL 문을 작성하시오.

DSEC 학생;

• Field : 속성명
• Type : 자료형
• Null : Null 허용 여부
• Key : 기본키(Primary Key)와 유니크(Unique) 옵션의 여부
• Default : 기본값
• Extra : 그 외 기타 옵션

 

절차형 SQL 테스트

• 프로시저, 사용자 정의 함수, 트리거 등의 절차형 SQL은 디버깅을 통해 기능의 적합성 여부를 검증하고, 실행을 통해 결과를 확인하는 테스트를 수행한다.
• 많은 코드로 구성된 절차형 SQL의 특성상 오류 및 경고 메시지가 상세히 출력되지 않으므로 SHOW 명령어를 통해 오류 내용을 확인하고 문제를 수정한다.

SHOW ERRORS;

• 데이터베이스에 변화를 줄 수 있는 SQL 문은 주석으로 처리하고, 출력문을 이용하여 화면에 출력하여 확인한다.
  • 주석(Comment)
    • 사람만 볼 수 있으며, 컴파일 되지는 않음.
    • # (MySQL), -- (Oracle), /* */ (DBMS 공용)
• 디버깅이 완료되면 출력문을 삭제하고, 주석 기호를 삭제한 후 절차형 SQL을 실행하여 결과를 검토한다.

 

 

(14) ORM(Object-Relational Mapping)

ORM(Object-Relational Mapping)

• 객체 지향 프로그래밍의 객체(Object)와 관계형 데이터베이스(Relational DataBase)의 데이터를 연결(Mapping)하는 기술
• 객체지향 프로그래밍에서 사용할 수 있는 가상의 객체지향 데이터베이스를 만들어 프로그래밍 코드와 데이터를 연결한다.
• ORM으로 생성된 가상의 객체지향 데이터베이스는 프로그래밍 코드 또는 데이터베이스와 독립적이므로 재사용 및 유지보수가 용이하다.

 

ORM 프레임워크

• ORM을 구현하기 위한 구조와 구현을 위해 필요한 여러 기능들을 제공하는 소프트웨어
• ORM 프레임워크의 종류

기반 언어	ORM 프레임워크
Java	JPA, Hibernate, EclipseLink, DataNucleus, Ebean 등
C++	ODB, QxOrm 등
Python	Django, SQLAlchemy, Storm 등
.NET	NHibernate, DatabaseObjects, Dapper 등
PHP	Doctrine, Propel, RedBean 등

 

ORM의 한계

• 프레임워크가 자동으로 SQL을 작성하기 때문에 의도대로 SQL이 작성되었는지 확인해야 한다.
• 객체지향적인 사용을 고려하고 설계된 데이터베이스가 아닌 경우, 프로젝트가 크고 복잡해질수록 ORM 기술을 적용하기 어렵다.
• 기존의 기업들은 ORM을 고려하지 않은 데이터베이스를 사용하고 있기 때문에 ORM에 적합하게 변환하려면 많은 시간과 노력이 필요하다.

 

 

(15) 쿼리 성능 최적화

쿼리 성능 최적화

• 데이터 입·출력 애플리케이션의 성능 향상을 위해 SQL 코드를 최적화하는 것
• 쿼리 성능을 최적화하기 전에 성능 측정 도구인 APM 을 사용하여 최적화할 쿼리를 선정해야 한다.
  • APM(Application Performance Management/Monitoring) : 애플리케이션의 성능 관리를 위해 접속자, 자원 현황, 트랜잭션 수행 내역, 장애 진단 등 다양한 모니터링 기능을 제공하는 도구
• 최적화 할 쿼리에 대해 옵티마이저가 수립한 실행 계획을 검토하고 SQL 코드와 인덱스를 재구성한다.

 

옵티마이저(Optimizer)

• 작성된 SQL이 가장 효율적으로 수행되도록 최적의 경로를 찾아 주는 모듈
• RBO(Rule Based Optimizer)와 CBO(Cost Based Optimizer) 두 종류가 있다.
  • RBO : 데이터베이스 관리자(DBA)가 사전에 정의해둔 규칙에 의거하여 경로를 찾는 규칙 기반 옵티마이저
  • CBO : 입출력 속도, CPU 사용량, 블록 개수, 개체의 속성, 튜플 개수 등을 종합하여 각 DBMS 마다 고유의 알고리즘에 따라 산출되는 '비용'으로 최적의 경로를 찾는 비용 기반 옵티마이저
• RBO와 CBO의 차이점

	RBO	CBO
최적화 기준	규칙에 정의된 우선순위	액세스 비용
성능 기준	개발자의 SQL 숙련도	옵티마이저의 예측 성능
특징	실행 계획 예측이 쉬움	성능 통계치 정보 활용, 예측이 복잡함.
고려 사항	개발자의 규칙 이해도, 규칙의 효율성	비용 산출 공식의 정확성

 

실행 계획(Execution Plan)

• DBMS의 옵티마이저가 수립한 SQL 코드의 실행 절차와 방법
• EXPLAIN 명령어를 통해 확인할 수 있으며, 그래픽이나 텍스트로 표현된다.
• 실행 계획에는 요구사항들을 처리하기 위한 연산 순서가 적혀 있으며, 연산에는 조인, 테이블 검색, 필터, 정렬 등이 있다.

 

쿼리 성능 최적화 방법

• 쿼리 성능 최적화는 실행 계획에 표시된 연산 순서, 조인 방식, 테이블 조회 방법 등을 참고하여 SQL문이 더 빠르고 효율적으로 작동하도록 SQL 코드와 인덱스를 재구성하는 것을 의미한다.

SQL 코드 재구성	- WHERE 절 추가 - WHERE 절에 연산자 사용 제한 - IN 을 EXISTS 로 대체 - 힌트로 액세스 경로 및 조인 순서 변경
인덱스 재구성	- 조회되는 속성과 조건을 고려하여 인덱스 구성 - 인덱스 추가 및 기존 인덱스의 열 순서 변경 - 테이블을 참조하는 다른 SQL문으로의 영향 고려 - IOT(Index-Organized Table) 구성 고려 - 불필요한 인덱스 제거

*EXISTS는 서브 쿼리의 모든 데이터를 확인하는 IN과 달리, 데이터의 존재 여부가 확인되면 검색이 종료되므로 IN 보다 처리 속도가 빠르다.

*힌트(Hint) : SQL 문에 추가되어 테이블 접근 순서를 변경하거나, 인덱스 사용을 강제하는 등의 실행 계획에 영향을 줄 수 있는 문장

*IOT(Index-Organized Table) : 일반적으로 인덱스가 있는 테이블을 조회할 때, 인덱스를 검색하여 주소를 얻으면 주소를 다시 찾아가는 과정을 거치지만, IOT는 인덱스 안에 테이블 데이터를 직접 삽입하여 저장함으로써 주소를 얻는 과정이 생략되어 더욱 빠르게 조회가  가능하다.