[SQL] 4. SQL

4.2 SQL 개요

비절차적 언어(선언적 언어) - 자신이 원하는 바(what)만 명시

대화식 SQL / 내포된 SQL (C, 코볼)

구성요소 : 데이터 검색, 조작어(삽입, 삭제, 수정) , 정의어, 트랜잭션 제어, 제어어

 

4.3 데이터 정의어와 무결성 제약조건

데이터 정의어 종류

CREATE DOMAIN / TABLE / VIEW / INDEX

ALTER TABLE

DROP DOMAIN / TABLE / VIEW / INDEX

 

스키마 생성&제거

CREATE SCHEMA my_db AUTHORIZATION kim; // 권한부여
DROP SCHEMA my_db RESTRICT; // 제약조건
DROP SCHEMA my_db CASCADE; // 스키마 객체 삭제

 

릴레이션 정의 예시

CREATE TABLE employee
	(EMPNO   NUMBER   NOT NULL,
     EMPNAME CHAR(10) UNIQUE,
     TITLE   CHAR(10) DEFAULT '사원',
     MANAGER NUMBER,
     SALARY  NUMBER   CHECK (SALARY < 600000),
     DNO     NUMBER   CHECK (DNO IN (1,2,3,4,5,6)) DEFAULT 1,
     PRIMARY KEY(EMPNO),
     FOREIGN KEY(MANAGER) REFERENCES EMPLOYEE(EMPNO),
     FOREIGN KEY(DNO)     REFERENCES DEPARTMENT(DEPTNO) 
     		 ON DELETE CASCADE); // 어떤 tuple 삭제하면 관련 다른 tuple 자동삭제

 

무결성 제약조건의 추가 및 삭제

ALTER TABLE STUDENT ADD CONSTRAINT STUDENT_PK PRIMARY KEY(STNO);

ALTER TABLE STUDENT DROP CONSTRAINT STUDENT_PK;

 

그룹화 + HAVING 절

SELECT   DNO, AVG(SALARY) AS AVGSAL, MAX(SALARY) AS MAXSAL
FROM     EMPLOYEE
GROUP BY DNO
HAVING   AVG(SALARY) >= 2500000;

SELECT   TITLE, MIN(SALARY), MAX(SALARY)
FROM     EMPLOYEE
GROUP BY TITLE
HAVING   MAX(SALARY) >= 100000000;

 

삽입, 삭제, 수정

INSERT INTO HIGH_SALARY(ENAME, TITLE, SAL)
SELECT EMPNAME, TITLE, SALARY
FROM   EMPLOYEE
WHERE  SALARY >= 3000000;

DELETE FROM DEPARTMENT
WHERE  DEPTNO = 4;

UPDATE EMPLOYEE
SET    DNO=3, SALARY = SALARY*1.05
WHERE  EMPNO=2106;

 

4.6 트리거(trigger)와 주장(assertion)

트리거(trigger)

• 제약조건을 위반했을 때 수행할 동작 명시
• 명시된 데이터베이스 갱신이 될 때마다 DBMS가 자동적으로 수행하는 문
• 이벤트-조건-동작(ECA) 규칙
  • 이벤트 : 트리거 활성화시키는 사건
  • 조건 : 트리거 활성화되었을 때 수행되는 테스트
  • 동작 : 트리거 활성화 후 조건 참일 때 수행되는 문

 

-- 새로운 사원 입사할 때마다 급여 1500000 미만일 경우 급여 10% 인상
CREATE TRIGGER RAISE_SALARY
AFTER  INSERT ON EMPLOYEE
REFERENCING NEW AS newEmployee
FOR EACH ROW
WHEN   (newEmployee.SALARY <1500000)
UPDATE EMPLOYEE
SET    newEmployee.SALARY = SALARY*1.1
WHERE  EMPNO = newEmployee.EMPNO;

 

주장(assertion)

• 제약조건을 위반하는 연산이 수행되지 않도록 함

 

-- STUDENT 릴레이션에 없는 학생의 학번이 ENROLL 릴레이션에 나타나는 것을 허용 X
CREATE ASSERTION EnrollStudentIntegrity
CHECK  (NOT EXISTS
	   (SELECT *
        FROM   ENROLL
        WHERE  STNO NOT IN
        	   (SELECT STNO FROM STUDENT)));

 

4.7 내포된 SQL

호스트 언어(C, C++, JAVA)에 포함되는 SQL문

호스트 변수 선언

EXEC SQL BEGIN DECLARE SECTION;
	int	no;
    varchar title[10];
EXEC SQL END DECLARE SECTION;

 

정적 / 동적 SQL

// 정적 : C 프로그램에 내포된 완전한 SQL문
EXEC SQL SELECT title    INTO :TITLE
		 FROM   EMPLOYEE WHERE empno = :no;
         
// 동적 : 불완전한 SQL문
strcpy(hostVarSTmtDyn, "UPDATE staff SET salary = salary+1000 WHERE dept=:v");
EXEC SQL PREPARE StmtDyn FROM :hostVarSTmtDyn;
EXEC SQL PREPARE StmtDyn USING :dept;

 

불일치 문제 : 집합 위주의 방식으로 처리하는 SQL문에서 불일치 문제 발생 -> 커서 사용

// 정적인 커서
EXEC SQL BEGIN DECLARE SECTION;
char name[] = "가나다";
char title[10];
EXEC SQL END DECLARE SECTION;

EXEC SQL
	DECLARE title_cursor CURSOR FOR
    SELECT title FROM employee WHERE empname = :name;
    
EXEC SQL OPEN title_cursor;
EXEC SQL FETCH title_cursor INTO :title;

 

WHENEVER : 자동적인 에러 검사와 에러 처리

• WHENEVER <조건><동작>
  • <조건> : NOT FOUND / SQLERROR / SQLWARNING
  • <동작> : CONTINUE / DO{function call | CONTINUE | BREAK}; / STOP