자격증/정보처리기사

[정보처리기사] 실기 요약 정리 SQL 응용

2023. 7. 20. 13:49
목차
  1. 1. DDL
  2. 2. DCL
  3. 3. DML
  4. 4. DML - SELECT
  5. 5. DML - JOIN
  6. 6. 프로시저 (출제될 확률이 적습니다.)
  7. 7. 트리거(Trigger) (출제될 확률이 적습니다.)
  8. 트리거
  9. 8. 사용자 정의 함수
  10. 9. 제어문
  11. 10. 커서
  12. 11. DBMS 접속
  13. 12. ORM(Object-Relational Mapping)
  14. 13. 쿼리 성능

1. DDL


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

  • DB 구조, 데이터 형식, 접근 방식 등 DB를 구축하거나 수정할 목적으로 사용하는 언어
  • 번역한 결과가 데이터 사전이라는 특별한 파일에 여러 개의 테이블로 저장된다.
  • DDL의 3가지 유형
    • CREATE : SCHEMA, DOMAIN, TABLE, VIEW,INDEX를 정의한다.
    • ALTER : TABLE에 대한 정의를 변경하는 데 사용한다.
    • DROP : SCHEMA, DOMAIN, TABLE, VIEW, INDEX를 삭제한다.

CREATE SCHEMA

  • 스키마를 정의하는 명령문
  • 스키마
    • 데이터베이스와 구조와 제약 조건에 관한 전반적인 명세를 기술한 것
    • 데이터 개체, 속성, 관계 및 조작 시 데이터 값들이 갖는 제약 조건 등에 관해 전반적으로 정의한다.
  • 표기 형식
CREATE SCHEMA 스키마명 AUTHORIZATION 사용자_id;

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

CREATE DOMAIN

  • 도메인을 정의하는 명령문
  • 도메인
    • 하나의 속성이 취할 수 있는 동일한 유형의 원자값들의 집합
    • 특정 속성에서 사용할 데이터의 범위를 사용자가 정의하는 사용자 정의 데이터 타입
  • 표기 형식
CREATE DOMAIN 도메인명 [AS] 데이터_타입
    [DEFAULT 기본값]
    [CONSTRAINT 제약조건명 CHECK (범위값)];
  • 데이터타입 : SQL에서 지원하는 데이터 타입
  • 기본값 : 데이터를 입력하지 않았을 때 자동으로 입력되는 값
예제 : '성별'을 '남' 또는 '여'와 같이 정해진 1개의 문자로 표현하는 도메인 SEX를 정의하는 SQL문
CREATE DOMAIN SEX CHAR(1)
	DEFAULT '남'
	CONSTRAINT VALID-SEX CHECK (VALUE IN ('남', '여'));

CREATE 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 : 속성 값에 대한 제약 조건을 정의함

CREATE VIEW

  • 뷰를 정의하는 명령문
  • 뷰
    • 하나 이상의 기본 테이블로부터 유도되는 이름을 갖는 가상 테이블
    • 테이블은 물리적으로 구현되어 실제로 데이터가 저장되지만, 뷰는 물리적으로 구현되지 않는다.
      • 뷰를 생성하면 뷰 정의가 시스템 내에 저장되었다가 SQL 내에서 뷰 이름을 사용하면 실행 시간에 뷰 정의가 대체되어 수행된다.
  • 표기형식
CREATE VIEW 뷰명[(속성명[, 속성명, ...])]
AS SELECT문;
예제 : <고객> 테이블에서 '주소'가 안산시인 고객들의 '성명'과 '전화번호'를 '안산고객'이라는 뷰로 정의하시오.
CREATE VIEW 안산고객(성명, 전화번호)
AS SELECT 성명, 전화번호
FROM 고객
WHERE 주소 = '안산시';

CREATE INDEX

  • 인덱스를 정의하는 명령문
    • 인덱스 : 검색 시간을 단축시키기 위해 만든 보조적인 데이터 구조
  • 표기형식
CREATE [UNIQUE] INDEX 인덱스명
ON 테이블명(속성명 [ASC|DESC][,속성명 [ASC|DESC]]
[CLUSTER];
  • UNIQUE
    • 사용된 경우 : 중복 값이 없는 속성으로 인덱스 생성
    • 생략된 경우 : 중복 값을 허용하는 속성으로 인덱스 생성
  • 정렬 여부 지정
    • ASC : 오름차순 정렬
    • DESC : 내림차순 정렬
    • 생략된 경우 : 오름차순으로 정렬됨
  • CLUSTER : 사용하면 인덱스가 클러스터드 인덱스로 설정된다.
    • 클러스터드 인덱스
      • 인덱스 키의 순서에 따라 데이터가 정렬되어 저장되는 방식
      • 실제 데이터가 순서대로 저장되어 있어 인덱스를 검색하지 않아도 원하는 데이터를 빠르게 찾을 수 있다.
      • 하지만 데이터 삽입, 삭제 발생 시 순서를 유지하기 위해 데이터를 재졍렬해야 한다.
    • 넌 클러스터드 인덱스
      • 인덱스의 키 값만 정렬되어 있을 뿐, 실제 데이터는 정렬되지 않는 방식
      • 데이터를 검색하기 위해서는 먼저 인덱스를 검색하여 실제 데이터의 위치를 확인해야 하므로, 클러스터드 인덱스에 비해 검색 속도가 떨어진다.
예제 : <고객> 테이블에서 UINQUE한 특성을 갖는 '고객번호' 속성에 대해 내림차순으로 정렬하여 '고객번호_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 : 특정 속성을 삭제할 때 사용된다.
예제 : <학생> 테이블의 '학번' 필드의 데이터 타입과 크기를 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. DCL


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

  • 데이터의 보안, 무결성, 회복, 병행 제어 등을 정의하는데 사용하는 언어
  • 데이터베이스 관리자(DBA)가 데이터 관리를 목적으로 사용한다.
  • DCL의 종류
    • COMMIT : 명령에 의해 수행된 결과를 실제 물리적 디스크로 저장
    • ROLLBACK : 데이터베이스 조작작업이 비정상적으로 종료되었을 때 원래 상태로 복구
    • GRANT : 데이터베이스 사용자에게 사용권한을 부여
    • REVOKE : 데이터베이스 사용자의 사용권한을 취소

GRANT / REVOKE

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

사용자 등급 지정 및 해제

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

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

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 : 권한 취소 시 권한을 부여받았던 사용자가 다른 사용자에게 부여한 권한도 연쇄적으로 취소

COMMIT

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

ROLLBACK

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

SAVEPOINT

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

TCL(Transaction Control Language)

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

3. DML


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

  • 저장된 데이터를 실질적으로 관리하는데 사용되는 언어
  • 데이터베이스 사용자와 데이터베이스 관리 시스템 간의 인터페이스를 제공한다.
  • DML의 유형
    • SELECT : 테이블에서 튜플을 검색
    • INSERT : 테이블에 새로운 튜플을 삽입
    • DELETE : 테이블에서 튜플을 삭제
    • UPDATE : 테이블에서 튜플의 내용을 갱신

삽입문(INSERT INTO ~ )

  • 새로운 튜플을 삽입할 때 사용한다.
  • 일반 형식
INSERT INTO 테이블명([속성명1, 속성명2, ...])
VALUES(데이터1, 데이터2);
  • 대응하는 속성과 데이터는 개수와 데이터 유형이 일치해야 한다.
  • 기본 테이블의 모든 속성을 사용할 때는 속성명을 생략할 수 있다.
  • SELECT 문을 사용하여 다른 테이블의 검색 결과를 삽입할 수 있다.

삭제문(DELETE FROM ~ )

  • 특정 튜플을 삭제할 때 사용한다.
  • 일반 형식
DELETE FROM 테이블명 [WHERE 조건];
  • 모든 레코드를 삭제할 때 WHERE절을 생략한다.
  • 모든 레코드를 삭제하더라도 테이블 구조는 남아 있기에 디스크에서 테이블을 완전히 제거하는 DROP과는 다르다.

갱신문(UPDATE ~ SET ~)

  • 특정 튜플의 내용을 변경할 때 사용한다.
  • 일반 형식
UPDATE 테이블명
SET 속성명 = 데이터[, 속성명=데이터, ...]
[WHERE 조건];

4. DML - SELECT


SELECT 일반 형식

SELECT [DISTINCT] [테이블명.]속성명 [AS 별칭][, [테이블명.]속성명, ...]
FROM 테이블명[, 테이블명, ...]
[WHERE 조건]
[GROUP BY 속성명, 속성명, ...]
[HAVING 조건]
[ORDER BY 속성명 [ASC|DESC]];
  • SELECT 절
    • PREDICATE : 검색할 튜플 수를 제한하는 명령어를 기술한다.
      • DISTINCT : 중복된 튜플이 있으면, 그 중 첫번째 한 개만 표시한다.
    • 속성명 : 검색하여 불러올 속성 또는 속성을 이용한 수식을 지정한다.
    • AS : 속성이나 연산의 이름을 다른 이름으로 표시하기 위해 사용한다.
  • FROM 절 : 검색할 데이터가 들어있는 테이블 이름을 기술한다.
  • WHERE 절 : 검색할 조건을 기술한다.
  • ORDER BY 절 : 데이터를 정렬하여 검색할 때 사용한다.
    • 속성명 : 정렬의 기준이 되는 속성명을 기술한다.
    • [ASC | DESC] : 정렬 방식
      • ASC : 오름차순
      • DESC : 내림차순
      • 생략하면 오름차순으로 정렬
  • 그룹 함수 : GROUP BY절에 지정된 그룹별로 속성의 값을 집계할 함수를 기술한다.
    • WINDOW 함수 : GROUP BY 절을 이용하지 않고, 속성의 값을 집계할 함수를 기술한다.
      • PARTITION BY : WINDOW함수의 적용 범위가 될 속성을 지정한다.
      • ORDER BY : PARTITION안에서 정렬 기준으로 사용할 속성을 지정한다.
    • GROUP BY 절
      • 특정 속성을 기준으로 그룹화하여 겁색할 때 사용한다.
      • 일반적으로 GROUP BY절은 그룹 함수화 함께 사용된다.
    • HAVING 절 : GROUP BY와 함께 사용되며, 그룹에 대한 조건을 지정한다.

조건 연산자

  • 비교 연산자
    • = : 같다
    • <> : 같지 않다
    • > : 크다
    • < : 작다
    • >= : 크거나 같다
    • <= : 작거나 같다
  • 논리 연산자
    • NOT, AND, OR
  • LIKE 연산자
    • 대표 문자를 이용해 지정된 속성의 값이 문자 패턴과 일치하는 튜플을 검색하기 위해 사용
      • % : 모든 문자를 대표
      • _ : 문자 하나를 대표
      • # : 숫자 하나를 대표

기본 검색

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

조건 지정 검색

  • WHERE 절에 조건을 지정하여 조건에 만족하는 튜플만 검색한다.
SELECT *
FROM 사원
WHERE 부서 = '기획';

정렬 검색

  • ORDER BY절에 특정 속성을 지정하여 지정된 속성으로 자료를 정렬하여 검색한다.
SELECT TOP 2 *
FROM 사원
ORDER BY 주소 DESC;

하위 질의

  • 조건절에 주어진 질의를 먼저 수행하여 그 검색 결과를 조건절의 피연산자로 사용한다.
SELECT 이름, 주소
FROM 사원
WHERE 이름 = (SELECT 이름
		FROM 여가활동
    		WHERE 취미 = '게임');

복수 테이블 검색

  • 여러 테이블을 대상으로 검색을 수행한다.
SELECT 사원.이름, 사원.부서, 여가활동.취미
FROM 사원, 여가활동
WHERE 여가활동.경력 >= 10 AND 사원.이름 = 여가활동.이름;

그룹 함수

  • GROUP BY 절에 지정된 그룹별로 속성의 값을 집계할 때 사용된다.
  • 종류
    • COUNT : 튜플의 수를 구하는 함수
    • SUM : 합계를 구하는 함수
    • AVG : 평균을 구하는 함수
    • MAX : 최대값을 구하는 함수
    • MIN : 최솟값을 구하는 함수
    • STDDEV : 표준편차를 구하는 함수
    • VARIANCE : 분산을 구하는 함수
    • ROLLUP : 그룹별 소계를 구하는 함수, 하위 레벨에서 상위 레벨 순으로 데이터가 집계된다.
    • CUBE : ROLLUP과 유사하지만 인수로 주어진 속성을 대상으로 모든 조합의 그룹별 소계를 구한다. 상위레벨에서 하위레벨 순으로 데이터가 집계된다.

WINDOW 함수

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

WINDOW 함수 이용 검색

  • 함수의 인수로 지정한 속성을 범위로 하여 속성의 값을 집계한다.
예제 : <상여금> 테이블에서 '상여내역'별로 '상여금'에 대한 일련 번호를 구하시오( 단, 순서는 내림차순이며 속성은 'NO'로 할것)
SELECT 상여내역, 상여금, ROW_NUMVER() OVER (PARTITION BY 상여내역 ORDER BY 상여금 DESC) AS NO
FROM 상여금;

그룹 지정 검색

  • GROUP BY 절에 지정한 속성을 기준으로 자료를 그룹화 하여 검색한다.
예제 : <상여금> 테이블에서 '부서'별 '상여금'의 평균을 구하시오.
SELECT 부서, AVG(상여금) AS 평균
FROM 상여금
GROUP BY 부서;

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

  • 집합 연산자를 사용하여 2개 이상의 테이블의 데이터를 하나로 통합한다.
  • 표기 형식
SELECT 속성명1, 속성명2, ...
FROM 테이블명
UNION | UNION ALL | INTERSECT | EXCEPT
SELECT 속성명1, 속성명2, ...
FROM 테이블명
[ORDER BY 속성명 [ASC | DESC]];
  • 2개의 SELECT 문에 기술한 속성들은 개수와 데이터의 유형이 서로 동일해야한다.
  • 집합 연산자의 종류
    • UNION : 합집합, 중복된 행은 한 번만 출력한다.
    • UNION ALL : 합집합, 중복된 행도 그대로 출력한다.
    • INTERSECT : 교집합
    • EXCEPT : 차집합

5. DML - JOIN


JOIN

  • 2개의 릴레이션에서 연관된 튜플들을 결합하여, 하나의 새로운 릴레이션을 반환한다.
  • 종류
    • INNER JOIN
      • 일반적으로 EQUI JOIN과 NON-EQUI JOIN으로 구분된다.
        • 조건이 없는 INNER JOIN을 수행하면 CROSS JOIN과 동일한 결과를 덕을 수 있다.
          • CROSS JOIN(교차 조인)
            • 조인하는 두 테이블에 있는 튜플들의 순서쌍을 결과로 반환한다.
            • 반환되는 테이블의 행의 수는 두 테이블의 행 수를 곱한 것과 같다.
    • OUTER JOIN
      • JOIN 조건에 만족하지 않는 튜플도 결과로 출력하기 위한 JOIN 방법
        • LEFT OUTER JOIN
        • RIGHT OUTER JOIN
        • FULL OUTER JOIN

EQUI JOIN

  • JOIN  대상 테이블에서 공통 속성을 기준으로 '='비교에 의해 같은 값을 가지는 행을 연결하여 결과를 생성하는 JOIN방법이다.
  • 중복된 속성을 제거하여 같은 속성을 한 번만 표기하는 방법을 NATURAL JOIN이라고 한다.
  • EQUI JOIN 에서 연결고리가 되는 공통 속성을 JOIN속성이라고 한다.
WHERE 절을 이용한 표기 형식
SELECT [테이블명1.]속성명, [테이블명2.]속성명, ...
FROM 테이블명1, 테이블명2, ...
WHERE 테이블명1.속성명 = 테이블명2.속성명;

NATURAL JOIN 절을 이용한 표기 형식
SELECT [테이블명1.]속성명, [테이블명2.]속성명, ...
FROM 테이블명1 NATURAL JOIN 테이블명2;

JOIN ~ USING 절을 이용한 표기 형식
SELECT [테이블명1.]속성명, [테이블명2.]속성명, ...
FROM 테이블명1 JOIN 테이블명2 USING(속성명);

NON-EQUI JOIN

  • JOIN 조건에 '='를 제외한 나머지 비교 연산자를 사용하는 JOIN 방법
  • 표기 형식
SELECT [테이블명1.]속성명, [테이블명2.]속성명, ...
FROM 테이블명1, 테이블명2, ...
WHERE (NON-EQUI JOIN 조건);

LETF 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와 RIGHT를 합쳐 놓은 것
  • 표기 형식
SELECT [테이블명1.]속성명, [테이블명2.]속성명, ...
FROM 테이블명1 FULL OUTER JOIN 테이블명2
ON 테이블명1.속성명 = 테이블명2.속성명;

6. 프로시저 (출제될 확률이 적습니다.)


프로시저

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

프로시저의 구성

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

프로시저 생성

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

프로시저 실행

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

프로시저 제거

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

7. 트리거(Trigger) (출제될 확률이 적습니다.)


트리거

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

트리거의 구성

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

트리거의 생성

  • CREATE TRIGGER 명령어를 사용한다.
  • 표기 형식
CREATE [OR REPLACE] TRIGGER 트리거명 [동작시기 옵션][동작 옵션] ON 테이블명
REFERNCING [NEW | OLD] AS 테이블명
FOR EACH ROW
[WHEN 조건식]
BEGIN
	트리거 BODY;
END;
  • OR REPLACE
    • 선택적인 예약어
    • 동일한 트리거의 이름이 이미 존재하는 경우, 기존의 트리거를 대체할 수 있다.
  • 동작시기 옵션 : 트리거가 실행될 때를 지정한다.
    • AFTER : 테이블이 변경된 후에 트리거가 실행
    • BEFORE : 테이블이 변경되기 전에 트리거가 실행
  •  동작 옵션 : 트리거가 실행되게 할 작업의 종류를 지정
    • INSERT : 새로운 튜플을 삽입할 때
    • DELETE : 튜플을 삭제할 때
    • UPDATE : 튜플을 수정할 때
  • NEW | OLD : 트리거가 적용될 테이블의 별칭을 지정
    • NEW : 추가되거나 수정에 참여할 튜플들의 집합을 의미
    • OLD : 수정되거나 삭제 전 대상이 되는 튜플들의 집합을 의미
  • FOR EACH ROW : 각 튜플마다 트리거를 적용한다는 의미
  • WHEN 조건식
    • 선택적인 예약어
    • 트리거를 적용할 튜플의 조건을 지정
  • 트리거 BODY
    • 트리거의 본문 코드를 입력하는 부분
    • 적어도 하나 이상의 SQL문이 있어야 한다.

트리거의 제거

  • DROP TRIGGER 명령어를 사용한다.
  • 표기 형식
DROP TRIGGER 트리거명;

8. 사용자 정의 함수


사용자 정의 함수

  • 프로시저와 유사하지만, 종료 시 처리 결과로 단일값만을 변환하는 절차형 SQL이다.
  • DML 문의 호출에 의해 실행된다
  • 예약어 RETURN을 통해 단일 값을 반환하며, 출력 파라미터가 없다.

사용자 정의 함수의 구성

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

사용자 정의 함수 생성

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

사용자 정의 함수 실행

  • 사용자 정의 함수는 DML에서 속성명이나 값이 놓일 자리를 대체하여 사용된다.
  • 표기 형식
SELECT 사용자 정의 함수명 FROM 테이블명;
INSERT INTO 테이블명(속성명) VALUES(사용자 정의 함수명);
DELETE FROM 테이블명 WHERE 속성명 = 사용자 정의 함수명;
UPDATE 테이블명 SET 속성명 = 사용자 정의 함수명;

사용자 정의 함수 제거

  • DROP FUNCTION 명령어를 사용한다.
  • 표기 형식
DROP FUCTION 사용자 정의 함수명;

9. 제어문


제어문

  • 절차형 SQL의 진행 순서를 변경하기 위해 사용하는 명령문
  • IF, LOOP등이 있다.

IF문

  • 조건에 따라 실행할 문장을 달리하는 제어문
  • 조건이 참일때
IF 조건 THEN
	실행할 문장1;
    	실행할 문장2;
    	    ...
END IF;
  • 조건이 참일 때와 거짓일 때 실행할 문장이 다르다.
IF 조건 THEN
	실행할 문장1;
ELSE
	실행할 문장2;
END IF;

LOOP 문

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

10. 커서


커서(Cursor)

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

묵시적 커서

  • DBMS에 의해 내부에서 자동으로 생성되어 사용되는 커서
  • 커서의 속성을 조회하여 사용된 쿼리정보를 열람하는 것이 가능하다.
  • 속성의 종류
    • SQL%FOUND : 쿼리 수행의 결과로 패치된 튜플 수가 1개 이상이면 TRUE
    • SQL%NOTFOUND : 쿼리 수행의 결과로 패치된 튜플 수가 0개면 TRUE
    • SQL%ROWCOUNT : 쿼리 수행의 결과로 패치된 튜플 수를 반환
    • SQL%ISOPEN
      • 커서가 열린 상태면 TRUE
      • 묵시적 커서는 항상 자동으로 닫혀서 항상 FALSE

명시적 커서

  • 사용자가 직접 정의해서 사용하는 커서
  • 커서는 기본적으로 열기, 패치, 닫기 순으로 이뤄지기에 명시적 커서를 사용하기 위해서는 열기 단계 전에 선언해야 한다.

선언(Declare) 형식

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

열기 형식

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

패치 형식

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

닫기 형식

CLOSE 커서명;

11. DBMS 접속


DBMS 접속

  • 응용 시스템을 이용하여 DBMS에 접근하는 것

DBMS 접속 기술

  • DBMS에 접근하기 위해 사용하는 API 또는 API 사용을 편리하게 도와주는 프레임워크
  • DBMS 접속 기술의 종류
    • JDBC
      • JAVA 언어로 다양한 종류의 데이터베이스에 접속할 때 사용하는 표준 API
    • ODBC
      • 개발 언어에 관계없이 데이터베이스에 접근하기 위한 표준 개방형 API
    • MYBATIS
      • JDBC 코드를 단순화하여 사용할 수 있는 SQL MAPPING 기반 오픈 소스 접속 프레임워크

동적 SQL(DYNAMIC SQL)

  • SQL 구문을 동적으로 변경하여 처리할 수 있는 SQL 처리 방식
  • 사용자로부터 SQL문의 일부 또는 전부를 입력받아 실행할 수 있다.
  • 정적 SQL에 비해 속도가 느리지만, 상황에 따라 다양한 조건을 첨가하는 등 유연한 개발이 가능하다.

12. ORM(Object-Relational Mapping)


ORM

  • 객체(OBJECT)와 관계형 데이터베이스(RELATIONAL DATABASE)의 데이터를 연결(MAPPING)하는 기술

ORM 프레임 워크

  • ORM을 구현하기 위한 여러 기능들을 제공하는 소프트웨어
  • ORM 프레임워크의 종류
    • Java : JPA, Hibernate, EclipseLink, DataNucleus, Ebean 등
    • C++ : ODB, QxOrm 등
    • Python : Django, SQLAchemy, Storm 등
    • .NET : NHibernate, DatabaseObjects, Dapper등
    • PHP : Doctrine, Propel, RedBean 등

13. 쿼리 성능


쿼리 성능 최적화

  • 데이터 입출력 애플리케이션의 성능 향상을 위해 SQL 코드를 최적화 하는것이다.
  • 쿼리 성능을 최적화하기 전에 성능 축정 도구인 APM을 사용하여 최적화할 쿼리를 선정해야 한다.
    • APM(Application Performance Management / Monitioring)
      • 애플리케이션의 성능관리를 위해 접속자, 자원 현황, 트랜잭션 수행 내역, 장애 진단 등 다양한 모니터링 기능을 제공하는 도구

옵티마이저

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

실행 계획

  • DBMS의 옵티마이저가 수립한 SQL 코드의 실행절차와 방법
  • EXPLAIN 명령어를 통해 확인할 수 있으며, 그래픽이나 텍스트로 표현된다.

쿼리 성능 최적화 방법

  • SQL문이 더 빠르고 효율적으로 작동하도록 SQL코드와 인덱스를 재구성하는 것을 의미
    • SQL 코드 재구성
      • WHERE 절 추가
      • WHERE 절에 연산자 사용 제한
      • IN을 EXISTS로 대체
      • 힌트로 액세스 경로 및 조인 순서 변경
    • 인덱스 재구성
      • 조회되는 속성과 조건을 고려하여 인덱스 구성
      • 인덱스 추가 및 기존 인덱스의 열 순서 변경
      • 테이블을 참조하는 다른 SQL문으로의 영향 고려
      • IOT 구성 고려
      • 불필요한 인덱스 제거
  • EXISTS
    • 데이터의 존재 여부가 확인되면 검색이 종료 되므로 IN보다 처리 속도가 빠르다.
  • 힌트
    • SQL문에 추가되어 테이블 접근 순서를 변경하거나, 인덱스 사용을 강제하는 등의 실행계획에 영향을 줄 수 있는 문장이다.
  • IOT(Index-Organized Table)
    • 인덱스 안에 테이블 데이터를 직접 삽입하여 저장함으로써 주소를 얻는 과정이 생략되어 빠른 조회가 가능하다.
저작자표시 (새창열림)

'자격증 > 정보처리기사' 카테고리의 다른 글

[정보처리기사] 실기 요약 정리 프로그래밍 언어 활용(추후에 포스팅)  (3) 2023.07.21
[정보처리기사] 실기 요약 정리 소프트웨어 개발 보안 구축  (2) 2023.07.20
[정보처리기사] 실기 요약 정리 애플리케이션 테스트 관리  (3) 2023.07.20
[정보처리기사] 실기 요약 정리 화면 설계  (10) 2023.07.19
[정보처리기사] 실기 요약 정리 인터페이스 구현  (8) 2023.07.18
  1. 1. DDL
  2. 2. DCL
  3. 3. DML
  4. 4. DML - SELECT
  5. 5. DML - JOIN
  6. 6. 프로시저 (출제될 확률이 적습니다.)
  7. 7. 트리거(Trigger) (출제될 확률이 적습니다.)
  8. 트리거
  9. 8. 사용자 정의 함수
  10. 9. 제어문
  11. 10. 커서
  12. 11. DBMS 접속
  13. 12. ORM(Object-Relational Mapping)
  14. 13. 쿼리 성능
'자격증/정보처리기사' 카테고리의 다른 글
  • [정보처리기사] 실기 요약 정리 프로그래밍 언어 활용(추후에 포스팅)
  • [정보처리기사] 실기 요약 정리 소프트웨어 개발 보안 구축
  • [정보처리기사] 실기 요약 정리 애플리케이션 테스트 관리
  • [정보처리기사] 실기 요약 정리 화면 설계
윤규헌
윤규헌
윤규헌
Yoon's Computer
윤규헌
전체
오늘
어제
  • 분류 전체보기 (19)
    • Project (0)
      • Mini Project (0)
      • Final Project (0)
    • 배운것들 정리 (6)
      • AWS (4)
      • Django (1)
      • 웹 (1)
    • 일기 (2)
    • 자격증 (11)
      • 정보처리기사 (11)

블로그 메뉴

  • 홈
  • 태그
  • 방명록

공지사항

인기 글

태그

  • HTTPS
  • route53
  • 요구사항 확인
  • 소프트웨어 개발 보안 구축
  • 부트캠프
  • SQL 응용
  • 엔코아
  • 정보처리기사 실기
  • 인터페이스 구현
  • 응용 SW 기초 기술 활용
  • 통합 구현
  • 데이터 입/출력 구현
  • 플레이데이터
  • RDS
  • 애플리케이션 테스트 관리
  • 웹 프레임워크
  • 20기
  • 프로그래밍 언어 활용
  • aws
  • 서버 프로그램 구현
  • 화면 설계

최근 댓글

최근 글

hELLO · Designed By 정상우.
윤규헌
[정보처리기사] 실기 요약 정리 SQL 응용
상단으로

티스토리툴바

단축키

내 블로그

내 블로그 - 관리자 홈 전환
Q
Q
새 글 쓰기
W
W

블로그 게시글

글 수정 (권한 있는 경우)
E
E
댓글 영역으로 이동
C
C

모든 영역

이 페이지의 URL 복사
S
S
맨 위로 이동
T
T
티스토리 홈 이동
H
H
단축키 안내
Shift + /
⇧ + /

* 단축키는 한글/영문 대소문자로 이용 가능하며, 티스토리 기본 도메인에서만 동작합니다.