8장 SQL 응용

8장 SQL 응용

 

DDL (Data Denfinition Language)

 

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

  CREATE SCHEMA SA AUTORIZATION 강냉;​

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

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

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

  CREATE TABLE 동아리
      (이름 VARCHAR(15) NOT NULL,
      PRIMARY KEY 학번 CHAR(9),
      전공 VARCHAR(10) NOT NULL,
      성별 SEX,
      생년월일 DATE,
      전화번호 VARCHAR(30),
      FOREIGN KEY(전공) REFERENCES 학과(학과코드)
          ON DELETE SET NULL
          ON UPDATE CASCADE /*NO ACTION / CASCADE / SET NULL / SET DEFAULT*/
      CONSTRAINT 생년월일제약
          CHECK(생년월일<'2001-01-01'));​

  /*다른 테이블로 테이블 정의하기*/ 
  CREATE TABLE 프로그래밍 AS SELECT 학번, 이름, 학년, 전공 FROM 동아리;

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

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

• CREATE [UNIQUE] INDEX 인덱스명
  ON 테이블명(속성명 [ASC|DESC][,속성명 [ASC|DESC]])
  [CLUSTER]; (사용하면 인덱스가 클러스터드 인덱스로 설정)
  

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

• ALTER TABLE 테이블명 ADD 속성명 데이터_타입 [DEFAULT '기본값']; (속성 추가)
  ALTER TABLE 테이블명 ALTER 속성명 [SET DEFAULT '기본값']; (속성 변경)
  ALTER TABLE 테이블명 DROP COLUMN 속성명 [CASCADE]; (속성 제거)
• DROP SCHEMA 스키마명 [CASCADE | RESTRICTED]; 
  DROP DOMAIN 도메인명 [CASCADE | RESTRICTED];
  DROP TABLE 테이블 명 [CASCADE | RESTRICTED];
  DROP VIEW 뷰명 [CASCADE | RESTRICTED]; (RESTRICETED는 다른 개체가 제거할 요소 참조 중일 때 제거 취소)
  DROP INDEX 인덱스명 [CASCADE | RESTRICTED];
  DROP CONSTRAINT 제약조건명;

---

DCL (Data Control Language)

 

• GRANT 사용자등급 TO 사용자_ID_리스트 [IDENTIFIED BY 암호];
  REVOKE 사용자등급 FROM 사용자_ID_리스트;
  
  • 사용자등급
    • DBA : DB 관리자
    • RESOURCE : DB 및 테이블 생성 기능자
    • CONNECT : 단순 사용자
• GRANT 권한_리스트 ON 개체 TO 사용자 [WITH GRANT OPTION];
  REVOKE [GRANT OPTION FOR] 권한_리스트 ON 개체 FROM 사용자 [CASCADE];
  • 권한_리스트
    • ALL, SELECT, INSERT, DELETE, UPDATE, ALTER
    • WITH GRAN OPTION : 부여받은 권한을 다른 사용자에게 다시 부여할 수 있는 권한 부여
    • GRANT OPTION FOR : 다른 사용자에게 권한 부여할 수 있는 권한 취소
• COMMIT : 트랜잭션(DB에서 하나의 논리적 기능을 수행하기 위한 일련의 연산 집합)이 성공적으로 끝나면 변경된 모든 내용을 DB에 반영하기 위해 사용하는 명령
• ROLLBACK : 아직 COMMIT 되지 않은 변경된 모든 내용들을 취소하고 이전 상태로 되돌리는 명령
  ROLLBACK TO 세이브포인트명;
• SAVEPOINT : ROLLBACK 할 위치 저장점을 지정하는 명령어
  SAVEPOINT 세이브포인트명;

---

DML (Data Manipulation Language)

 

• INSERT INTO 테이블명([속성명1, 속성명2, ...])
  VALUES (데이터1, 데이터2, ...);
• DELETE
  FROM 테이블명
  [WHERE 조건];
• UPDATE 테이블명
  SET 속성명 = 데이터[, 속성명 = 데이터, ...]
  [WHERE 조건];
• SELECT [PREDICATE][테이블명,]속성명 [AS 별칭][, [테이블명,]속성명, ...]
  [, 그룹함수(속성명) [AS 별칭]]
  [, Window함수 OVER (PARTITION BY 속성명1, 속성명2, ...
                        ORDER BY 속성명3, 속성명 4, ...)]
  FROM 테이블명[, 테이블명, ...]
  [WHERE 조건]
  [GROUP BY 속성명, 속성명, ...]
  [HAVING 조건]
  [ORDER BY 속성명 [ASC | DESC]];
  • PREDICATE : 불러올 튜플 수를 제한할 명령어 기술
    • DISTINCT : 중복된 튜플이 있으면 한 개만 검색
    • DISTINCTROW : 중복된 튜플 제거 한 개만 검색하지만 속성의 값이 아닌 튜플 전체를 대상으로 함.
  • 조건 연산자
    • 비교 연산자 : = / <> / > / < / >= / <=
    • 논리 연산자 : NOT / AND / OR
    • LIKE 연산자 : 대표 문자를 이용해 지정된 속성 값이 문자 패턴과 일치하는 튜플 검색
      • %(모든 문자 대표) / _(문자 하나 대표. 찾는 문자 길이를 알 때 사용.) / #(숫자 하나 대표)
  • GROUP 함수
    • COUNT(), SUM(), AVG(), MAX(), MIN(), STDDEV(), VARIANCE()
    • ROLLUP() : 인수로 주어진 속성들을 대상으로 그룹별 소계를 구하는 함수. n+1 레벨까지 하위에서 상위 레벨 순으로 데이터 집계
    • CUBE : 인수로 주어진 속성을 대산으로 모든 조합의 그룹별 소계를 구하는 함수. n^2 레벨까지 상위 레벨에서 하위 레벨 순으로 데이터 집계
  • WINDOW 함수
    • ROW_NUMBER() : 각 레코드에 대한 일련 번호 반환
    • RANK() : 순위 반환, 공동 순위 반영
    • DENSE_RANK() : 순위 반환, 공동 순위 미반영

---

프로시저

 

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

 

• 생성
  • CREATE [OR REPLACE] PROCEDURE 프로시저명(매개변수명 파라미터 자료형)
    [지역변수 선언]
    BEGIN
        프로시저 BODY; (하나의 SQL문이 있어야 함)
    END;
    
    • OR REPLCAE : 기존에 프로시저가 존재할 경우, 대체
    • 파라미터
      • IN : 호출 프로그램이 프로시저에게 값을 전달할 때
      • OUT : 프로시저가 호출 프로그램에게 값을 반환할 때
      • INOUT : 값을 전달하고 반환 둘 다 함.
• 실행
  • EXECUTE 프로시저명;
    EXEC 프로시저명;
    CALL 프로시저명;
    세 개 중 아무거나 선택하여 실행
• 제거
  • DROP PROCEDURE 프로시저명;

---

트리거

 

이벤트가 발생할 때마다 관련 작업이 자동으로 수행되는 절차형 SQL

 

• 생성
  • CREATE [OR REPLACE] TRIGGER 트리거명 [동작시기 옵션][동작 옵션] ON 테이블명
    REFERENCING [NEW | OLD] AS 테이블명
    FOR EACH ROW
    [WHEN 조건식]
    BEGIN
        트리거 BODY;
    END;
    • 동작시기 옵션 : AFTER / BEFORE
    • 동작 옵션 : INSERT / DELETE / UPDATE
    • NEW : 추가되거나 수정에 참여할 튜플 집합 의미 / OLD : 수정되거나 삭제 전 대상이 되는 튜플 집합 의미
    • WHEN 조건식 : 트리거를 적용할 튜플 조건 지정
• 제거
  • DROP TRIGGER 트리거명;

---

사용자 정의 함수 

 

• 생성
  • CREATE [OR REPLACE] FUNCTION 사용자 정의 함수명(매개변수명 파라미터 자료형) (파라미터는 IN만 사용)
    [지역변수 선언]
    BEGIN
        사용자 정의 함수 BODY;
        RETURN 반환값;
    END;
  • PROCEDURE와 거의 유사. RETURN만 따로 있는 형태
• 실행
  • SELECT 사용자 정의 함수명 FROM 테이블;
    INSERT INTO 테이블명(속성명) VALUES (사용자 정의 함수명);
    DELETE FROM 테이블명 WHERE 속성명 = 사용자 정의 함수명;
    UPDATE 테이블명 SET 속성명 = 사용자 정의 함수명;

---

제어문

 

IF 조건1 THEN
    실행할 문장1;

ELSIF 조건2 THEN
    실행할 문장2;

ELSE
    실행할 문장3;

ENDIF;

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

END LOOP;

FOR 변수 IN 초기값..종료값

LOOP 
    실행할 문장;
END LOOP;

WHILE 조건

LOOP 
    실행할 문장;
END LOOP;

CONTINUE WHEN 조건;

GOTO 레이블;

《레이블》
실행할 문장;

---

커서

 

커서란? 쿼리문 처리 결과가 저장되어 있는 메모리 공간을 가리키는 포인터.

 

• 묵시적 커서(Implicit Cursor) : DBMS 자체적으로 열리고 패치되어 사용이 끝나면 닫힘.
  • 속성
    • SQL%FOUND : 패치 된 튜플 수가 1개 이상이면 TRUE
    • SQL%NOTFOUND : 패치된 튜플 수가 0개면 TRUE
    • SQL%ROWCOUNT : 패치된 튜플 수 반환
    • SQL%ISOPEN : 커서가 열린 상태면 TRUE
• 명시적 커서(Explicit Cursor) : 사용자가 직접 정의해 사용하는 커서
  • 선언 형식
    • CURSOR 커서명 (매개변수1, 매개변수2, ...)
      IS
      
      SELECT 문;
  • 열기 형식
    • OPEN 커서명(매개변수1, 매개변수2, ...);
  • 패치 형식
    • FETCH 커서명 INTO 변수1, 변수2 ...;
  • 닫기 형식
    • CLOSE 커서명;