[정보처리 실무] 8장 SQL 응용

072 SQL - DDL

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

  - DDL은 데이터베이스를 구축하거나 수정 할 목적으로 사용하는 언어

  - DDL은 번역한 결과가 데이터 사전(Data Dictionary)이라는 특별한 파일에 여러 개의 테이블로서 저장

 

◈ CREATE TABLE

  - 테이블을 정의하는 명령문

  - 표기 형식

CREATE TABLE 테이블명

(속성명 데이터_타입 [DEFAULT 기본값] [NOT NULL], ...

[PRIMARY KEY (기본키_속성명)],

[UNIQUE (대체키_속성명)],

[FOREIGN KEY (외래키_속성명)],

     [REFERENCES 참조테이블(기본키_속성명)],

     [ON DELETE 옵션]

     [ON UPDATE 옵션]

[CONSTRAINT 제약조건명] [CHECK (조건식)]);)];

  - PRIMARY KEY : 기본키를 사용할 속성 또는 속성의 집합을 지정

  - UNIQUE : 대체키로 사용할 속성 또는 속성의 집합을 지정하는 것

  - FOREIGN KEY ~ REFERENCES ~

   * ON DELETE 옵션 : 참조 테이블의 튜플이 삭제되었을 때 기본 테이블에 취해야 할 사항 지정(NO ACTION, CASCADE, SET NULL, SET DEFAULT)

   * ON UPDATE 옵션 : 참조 테이블에 참조 속성 값이 변경되었을 때 기본 테이블에 취해야할 사항 지정(NO ACTION, CASCADE, SET NULL, SET DEFAULT)

  - CONSTRAINT : 제약 조건의 이름 지정

  - CHECK : 속성 값에 대한 제약 조건 정의

 

◈ CREATE VIEW

  - 뷰를 정의하는 명령문

  - 표기 형식

CREATE VIEW 뷰명[(속성명[, 속성명, ...])]

AS SELECT문;

  - SELECT문을 서브 쿼리로 사용하여 SELECT문의 결과로서 뷰 생성

  - 서브 쿼리인 SELECT문에는 UNION이나 ORDET BY절을 사용할 수 없음

  - 속성명을 기술하지 않으면 SELECT문의 속성명이 자동으로 사용

 

◈ ALTER TABLE

  - 테이블에 대한 정의 변경하는 명령문

  - 표기 형식

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

ALTER TABLE 테이블명 ALTER 속성명 [SET DEFAULT '기본값'];

ALTER TABLE 테이블명 DROP COLUMN 속성명 [CASCADE];

- ADD : 새로운 속성(열) 추가

- ALTER : 특정 속성의 기본값(Default) 변경

- DROP : 특정 속성 삭제

 

◈ DROP TABLE

  - 스키마, 도메인, 기본 테이블, 뷰 테이블, 인덱스를 삭제하는 명령문

  - 표기 형식

DROP TABLE 테이블명 [CASCADE | RESTRICTED];

  - CASCADE : 제거할 요소를 참조하는 다른 모든 개체를 함께 제거

  - RESTRICT : 다른 개체가 제거할 요소를 참조중일 때는 제거 취소

073 SQL - DCL

◈ DCL(Data Control Language, 데이터 제어어)의 개념

  - DCL는 데이터 보안, 무결성, 회복, 병형 제어 등을 정의하는 데 사용하는 언어

  - DCL는 데이터베이스 관리자(DBA)가 데이터 관리를 목적으로 사용

  - DCL에는 GRANT, ROVOKE, COMMIT, ROLLBACK, SAVEPOINT 등이 있음

 

◈ GRANT / REVOKE

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

  - GRANT : 권한 부여를 위한 명령어

  - REVOKE : 권한 취소를 위한 명령어

  - 표기 형식(사용자등급 지정 및 해제)

GRANT 사용자 등급 TO 사용자_ID_리스트;

REVOKE 사용자 등급 FROM 사용자_ID_리스트;

  - 사용자 등급

   * DBA : 데이터 베이스 관리자

   * RESOURCE : 데이터베이스 및 테이블 생성 가능자

   * CONNECT : 단순 사용자

  - 표기 형식(테이블 및 속성에 대한 권한 부여 및 취소)

GRANT 권한_리스트 ON 테이블명 TO 사용자 [WITH GRANT OPTION];

REVOKE [GRANT OPTION FOR] 권한_리스트 ON 테이블명 FROM 사용자;

  - 권한 종류 : ALL, SELECT, INSERT, DELETE, UPDATE, ALTER 등

  - WITH GRANT OPTION : 부여받은 권한을 다른 사용자에게 다시 부여할 수 있는 권한 부여

  - GRANT OPTION FOR : 다른 사용자에게 권한을 부여할 수 있는 권한을 취소함

  - CASCADE : 권한 취소 시 권한을 부여받았던 사용자가 다른 사용자에게 부여한 권한도 연쇄적으로 취소

 

◈ COMMIT

  - 트랜잭션이 성공적으로 끝나면 데이터베이스가 새로운 일관성 상태를 가지기 위해 변경된 모든 내용을 데이터베이스에 반영하여야 하는데 이때 사용하는 명령어

 

◈ ROLLBACK

  - 아직 COMMIT되지 않은 변경된 모든 내용들을 취소하고 데이터베이스를 이전 상태로 되돌리는 명령어

 

◈ SAVEPOINT

  - 트랜잭션 내에  ROLLBACK 할 위치인 저장점을 지정하는 명령어

  - 저장점을 지정할 때는 이름 부여, ROLLBACK시 저장된 저장점까지 트랜잭션 처리 내용이 취소됨

074 SQL - DML

◈ DML(Data Manipulation Language, 데이터 조작어)의 개념

  - DML은 데이터베이스 사용자가 응용 프로그램이나 질의어를 통해 저장된 데이터를 실질적으로 관리하는데 사용되는 언어

  - DML의 유형

   * SELECT : 테이블에서 튜플 검색

   * INSERT : 테이블에서 새로운 튜플 삽입

   * DELETE : 테이블에서 튜플 삭제

   * UPDATE : 테이블에서 튜플의 내용 갱신

 

◈ 삽입문(INSERT INTO~)

  - 기본 테이블에 새로운 튜플을 삽입할 때 사용

  - 표기 형식

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

VALUES(데이터1, 데이터2, ...);

 

◈ 삭제문(DELETE FROM~)

  - 기본 테이블에 있는 튜플 중에서 특정 튜플을 삭제할 때 사용

  - 표기 형식

DELETE

FROM 테이블명

[WHERE조건];

 

◈ 갱신문(UPDATE~ SET~)

  - 기본 테이블에 있는 튜플들 중에서 특정 튜플의 내용을 변경할 때 상용

  - 표기 형식

UPDATE 테이블명

SET 속성명 = 데이터, [속성명=데이터, .....]

[WHERE 조건];

075 DML - SELECT-1

◈ 일반 형식

SELECT  [PREDICATE] [테이블명.]속성명1, [테이블명.]속성명2, ......,

[그룹함수(속성명)],

[WINDOW함수 OVER(PARTITION BY 속성명1, 속성명2, .... ORDER BY 속성명 3, 속성명4,...)]

FROM 테이블명1, 테이블명2, ...

[WHERE 조건]

[GROUP BY 속성명1, 속성명2, ...]

[HAVING 조건]

[ORDER BY 속성명 [ASC | DESC]];

 1) SELECT절

  - PREDICATE : 불러올 튜플 수를 제한할 명령어 기술

   * ALL : 모든 튜플 검색, 주로 생략

   * DISTINCT : 중복된 튜플이 있으면 그중 첫 번째 한 개만 검색

   * DISTINCTROW : 중복된 튜플을 제거하고 한 개만 검색하지만 선택된 속성의 값이 아닌 튜플 전체를 대상으로 함

  - 속성명 : 검색하여 불러올 속성 또는 속성을 이용한 수식을 지정

   * 기본 테이블을 구성하는 모든 속성을 지정할 때는 '*'를 기술

   * 두 개 이상의 테이블을 대상으로 검색할 때는 '테이블명.속성명' 으로 표현

  - AS : 속성 및 연산의 이름을 다른 제목으로 표시하기 위해 사용

 2) FROM절 : 질의에 의해 검색될 데이터들을 포함하는 테이블명 기술

 3) WHERE절 : 검색할 조건 기술

 4) ORDER BY절 : 특정 속성을 기준으로 정렬하여 검색할 때 사용

  - 속성명 : 정렬의 기준이 되는 속성명 기술

  - [ASC|DESC] : 정렬 방식으로서 'ASC'는 오름차순, 'DESC'는 내림차순, 생략하면 오름차순

 

◈ 하위 질의

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

ex1) '취미'가 "나이트댄스"인 사원의 '이름'과 '주소' 검색

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

ex2) 취미활동을 하지 않는 사원들 검색

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

076 DML - SELECT-2

◈ 일반 형식

SELECT [PREDICATE] [테이블명.]속성명1, [테이블명.]속성명2, ......,

[그룹함수(속성명)],

[WINDOW함수 OVER(PARTITION BY 속성명1, 속성명2, .... ORDER BY 속성명 3, 속성명4,...)]

FROM 테이블명1, 테이블명2, ...

[WHERE 조건]

[GROUP BY 속성명1, 속성명2, ...]

[HAVING 조건]

[ORDER BY 속성명 [ASC | DESC]];

  - 그룹함수 : GROUP BY절에 지정된 그룹별로 속성의 값 집계

   * COUNT(속성명) : 그룹별 튜플 수

   * AVG(속성명) : 그룹별 평균

   * SUM(속성명) : 그룹별 합계

   * AVG(속성명) : 그룹별 평균

   * MAX(속성명) : 그룹별 최대값

   * MIN(속성명) : 그룹별 최소값

   * STDDEV(속성명) : 그룹별 표준편차

   * VARIANCE(속성명) : 그룹별 분산

  - WINDOW 함수 : GROUP BY절을 이용하지 않고 속성의 값 집계

   * ROW_NUMBER( ) : 윈도우별로 각 레코드에 대한 일련 번호 반환

   * RANK( ) : 윈도우별로 순위 반환, 공동 순위 반영

   * DENSE_RANK( ) : 윈도우별로 순위 반환, 공동 순위 무시하고 순위 부여

  - GROUP BY절 : 특정 속성을 기준으로 그룹화하여 검색할 때 허용. 그룹합수와 함께 사용

  - HAVING절 : GROUP BY와 함께 사용, 그룹에 대한 조건 지정

077 프로시저(Procedure)

◈ 프로시저(Procedure)의 개요

  - 프로시저란 절차형 SQL을 활용하여 특정 기능을 수행하는 일종의 트랜잭션 언어로 호출을 통해 실행되어 미리 저장해 놓은 SQL작업을 수행

  - 프로시저는 데이터베이스에 저장되어 수행되기 때문에 스토어드(Stored) 프로시저라고도 불림

  - 프로시저는 시스템의 일일 마감, 일괄(Batch) 작업 등에 주로 사용

 

◈ 프로시저 생성

  - 프로시저를 생성하기 위해서는 CREATE PROCEDURE 명령어를 사용

  - 표기 형식

CREATE [OR REPLACE] PROCEDURE 프로시저명(파라미터)

[지역변수 선언]

BEGIN

    프로시저 BODY;

END;

 

◈ 프로시저 실행

  - 프로시저를 실행하기 위해서는 EXECUTE 명령어 또는 CALL 명령어를 사용

  - 표기 형식

EXECUTE 프로시저명;

EXEC 프로시저명;

CALL 프로시저명;

 

◈ 프로시저 제거

  - 프로시저를 제거하기 위해서는 DROP PROCEDURE 명령어를 사용

  - 표기 형식

DROP PROCEDURE 프로시저명;

078 트리거(Trigger)

◈ 트리거(Trigger)의 개요

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

  - 트리거는 데이터베이스에 저장되며, 데이터 변경 및 무결성 유지, 로그 메시지 출력 등의 목적으로 사용

 

◈ 트리거의 생성

  - CREATE TRIGGER 명령어를 사용

  - 표기 형식

CREATE [OR REPLACE] TRIGGER 트리거명 동작시기 동작

ON 테이블명 [REFERENCING NEW | ODF AS 테이블명]

[FOR EACH ROW [WHEN 조건식]]

BEGIN

     트리거 BODY;

END;

 

◈ 트리거의 삭제

  - DROP TRIGGER 명령어를 사용

  - 표기 형식

DROP TRIGGER 트리거명;

 

079 사용자 정의 함수

◈ 사용자 정의 함수의 개요

- 프로시저와 유사하게 SQL을 사용하여 일련의 작업을 연속적으로 처리하며 종료 시 처리 결과를 단일값으로 반환하는 절차형 SQL

- 사용자 정의 함수는 데이터베이스에 저장되어 SELECT, INSERT, DEETE, UPDATE 등 DML문의 호출에 의해 실행

- RETURN을 통해 값을 반환하기 때문에 출력 파라미터가 없음

 

◈ 사용자 정의 함수 생성

  - CREATE FUNCTION 명령어를 사용

  - 표기 형식

CREATE [OR REPLACE] FUNTION 사용자 정의 함수명(파라미터)

[지역변수 선언]

BEGIN

     사용자 정의 함수 BODY;

     RETURN 반환값;

END;

 

◈ 사용자 정의 함수 실행

  - DML에서 속성명이나 값이 놓일 자리를 대체하여 사용

  - 표기 형식

SELECT 사용자 정의 함수명 FROM 테이블명;

INSERT INTO 테이블명(속성명) VALUES (사용자 정의 함수명);

DELETE FROM 테이블명 WHERE 속성명 = 사용자 정의 함수명;

UPDATE 테이블명 SET 속성명 = 사용자 정의 함수명;

 

◈ 사용자 정의 함수 제거

  - DROP FUNCTION 명령어 사용

  - 표기 형식

DROP FUNCTION 사용자 정의 함수명;

 

080 제어문

◈ 조건문

 1) if

  - 조건에 따라서 실행할 문장을 달리함

 2) switch

  - 조건에 따라서 분기할 곳을 달리함

 

◈ 반복문

 1) loop

  - 원칙적으로 무한히 반복하기 때문에 exit를 통해 loop 연산을 중지 시켜야함

 2) While loop

  - 조건이 참인 경우 반복

 3) for loop

  - 미리 정해진 횟수만큼 반복

 

081 커서(Cursor)

◈ 커서의 개요

  - 쿼리문의 처리 결과가 저장되어 있는 메모리 공간을 가리키는 포인터

  - 커서는 내부에서 자동으로 생성되어 사용되는 묵시적 커서와 사용자가 직접 정의해서 사용하는 명시적 커서가 있음

  - 커서의 수행은 열기(Open), 패치(Fetch), 닫기(Close)의 세 단계로 진행

 

◈ 묵시적 커서(Implict Cursor)

  - DBMS 자체적으로 열리고 패치되어 사용이 끝나면 닫히지만 커서의 속성을 조화하여 사용된 쿼리 정보를 열람하는 것이 가능  - 커서의 속성   * SQL%FOUND : 쿼리 수행의 결과가 패치된 튜플 수가 1개 이상이면 TRUE   * SQL%NOTFOUND : 쿼리 수행의 결과로 패치된 튜플 수가 0개이면 TRUE   * SQL%ROWCOUNT : 쿼리 수행의 결과로 패치된 튜플 수를 반환   * SQL%ISOPEN : 커서가 열린 상태이면 TRUE

 

◈ 명시적 커서(Explicit Cursor)  - 사용자가 직접 정의해서 사용하는 커서  - 주로 절차형SQL에서 SELECT문의 결과로 반환되는 여러 튜플들을 제어하기 위해 사용  - 커서는 기본적으로 열기-패치-닫기 순으로 이루어지며 명시적 커서로 사용하기 위해서는 열기 전에 선언을 해야함