데이터베이스 (Database) SQL 추가 기능: 주장, 뷰, 프로그래밍 기법 문양세 강원대학교 IT대학 컴퓨터과학전공

강의 내용 주장(Assertions)으로 일반적인 제약조건 명시 SQL에서 뷰(가상 테이블) 데이터베이스 프로그래밍 내포된 SQL (Embedded SQL) 함수 호출, SQL/CLI [생략] 저장 프로시저와 SQL/PSM [생략] 요약

주장(Assertion)에 의한 제약조건 명시 (1/2) SQL 추가 기능 (주장의 사용은 기본 SQL에는 포함되지 않음) 주장은 관계 DB에서 제약 조건을 명시하기 위해 사용함 (특히, 테이블을 정의(생성)하는 단계에서 주로 사용함) 사용법 명령어: CREATE ASSERTION 포함되는 내용: 제약조건 이름을 가지며, 다음에 키워드 CHECK가 오며, 데이터베이스 상태가 주장을 만족하는 여부(참/거짓)에 따라 조건(condition)이 뒤에 옴

주장(Assertion)에 의한 제약조건 명시 (2/2) SQL 추가 기능 주장의 예제: 사원의 급여가 자신이 근무하는 부서의 관리자의 급여보다 많을 수 없다. CREATE ASSERTION SALARY_CONSTRAINT CHECK (NOT EXISTS ( SELECT * FROM EMPLOYEE E, EMPLOYEE M, DEPARTMENT D WHERE E.SALARY > M.SALARY AND E.DNO=D.NUMBER AND D.MGRSSN=M.SSN))

일반적인 주장의 사용 - 생략 주어진 조건을 위반하는 질의를 명시 질의의 결과가 공집합이 되어야 함 SQL 추가 기능 주어진 조건을 위반하는 질의를 명시 해당 질의(의 결과)를 NOT EXISTS절 내에 포함시킴 질의의 결과가 공집합이 되어야 함 이 질의의 결과가 공집합이 아니면 주장은 위반됨

SQL 트리거 목적: 조건이 발생할 때 데이터베이스를 모니터하기 위해 (제약조건) 행동의 유형을 명시 목적: 조건이 발생할 때 데이터베이스를 모니터하기 위해 (제약조건) 행동의 유형을 명시 트리거는 주장과 유사한 구문으로 표기되며 다음 사항을 포함함 사건 (e.g., 갱신 연산): 어떤 사건이 있으면 검사가 시작됨 조건: 동작을 수행할 조건을 명시함 동작: 주어진 조건이 만족되면 수행

SQL 트리거 예제 SQL 추가 기능 삽입과 갱신 연산을 하는 동안 사원의 월급을 그의 관리자와 비교하기 위한 트리거의 사용 CREATE TRIGGER INFORM_SUPERVISOR BEFORE INSERT OR UPDATE OF SALARY, SUPERVISOR_SSN ON EMPLOYEE FOR EACH ROW WHEN (NEW.SALARY > (SELECT SALARY FROM EMPLOYEE WHERE SSN = NEW.SUPERVISOR_SSN)) …

강의 내용 주장(Assertions)으로 일반적인 제약조건 명시 SQL에서 뷰(가상 테이블) 데이터베이스 프로그래밍 내포된 SQL (Embedded SQL) 함수 호출, SQL/CLI [생략] 저장 프로시저와 SQL/PSM [생략] 요약

SQL에서 뷰의 개념 SQL에서 뷰는 다른 테이블들에서 유도된 “가상” 테이블 뷰에 적용할 수 있는 갱신 연산들은 제한됨 물리적인 형태로 저장되지는 않기 때문임 (다른 테이블에 영향을 주기 때문임) 뷰에 대한 질의는 특별한 제한을 받지 않음 몇 개 연산들을 뷰로 표현하여 사용하는데 편리함

SQL에서 뷰의 명시 뷰를 정의하는 SQL 명령: CREATE VIEW 뷰의 정의는 (가상) 테이블 이름 애트리뷰트 이름들의 목록 함수나 산술 연산들을 적용하거나, 기본 릴레이션의 애트리뷰트 이름과 다른 이름을 사용하고자 할 때 이용 뷰의 내용을 나타내는 질의 사용 예제: 다른 WORKS_ON 테이블의 명시 CREATE VIEW WORKS_ON_NEW AS SELECT FNAME, LNAME, PNAME, HOURS FROM EMPLOYEE, PROJECT, WORKS_ON WHERE SSN=ESSN AND PNO=PNUMBER GROUP BY PNAME;

VIEW의 이용 및 삭제 SQL 추가 기능 새롭게 생성된 가상 테이블(뷰)에 대해 SQL 질의를 사용할 수 있음 SELECT FNAME, LNAME FROM WORKS_ON_NEW WHERE PNAME=‘Seena’; SELECT의 경우 일반적인 테이블과 구분 없이 사용 가능 뷰의 삭제: DROP을 사용하여 더 이상 필요하지 않은 뷰를 제거함 DROP WORKS_ON_NEW;

VIEW의 구현 질의수정(query modification) 방식 뷰의 실체화(view materialization) SQL 추가 기능 질의수정(query modification) 방식 뷰에 대한 질의를 기본 테이블들에 대한 질의로 변환하여 처리 단점: 복잡한 질의로 정의된 뷰들은 비효율적  특히 짧은 시간 내에 뷰에 많을 질의가 적용될 때 뷰의 실체화(view materialization) 임의의 뷰 테이블을 물리적으로 생성하고 유지하는 방식 가정: 뷰에 다른 질의들이 사용됨 문제점: 기본 테이블이 갱신되면 뷰 테이블도 변경해야 함 해결방법: 오버헤드가 적은 점진적 갱신(incremental update)기법 필요

뷰의 갱신 집단함수를 사용하지 않은 단일 뷰의 갱신 조인을 포함하는 뷰의 갱신 갱신할 수 없는 뷰 SQL 추가 기능 집단함수를 사용하지 않은 단일 뷰의 갱신 뷰의 갱신은 단일 기본 테이블에 대한 갱신으로 사상될 수 있음 조인을 포함하는 뷰의 갱신 기본 릴레이션들에 대한 갱신 동작으로 사상될 수 있음 (항상 가능한 것은 아님) 갱신할 수 없는 뷰 그룹화와 집단함수를 사용하여 정의된 뷰는 갱신할 수 없음 일반적으로 다수의 테이블을 조인하여 정의한 뷰는 갱신할 수 없음

강의 내용 주장(Assertions)으로 일반적인 제약조건 명시 SQL에서 뷰(가상 테이블) 데이터베이스 프로그래밍 내포된 SQL (Embedded SQL) 함수 호출, SQL/CLI [생략] 저장 프로시저와 SQL/PSM [생략] 요약

데이터베이스 프로그래밍 목적 필요성 응용 프로그램에서 데이터베이스 접근 대화식 인터페이스와 다름 SQL 추가 기능 목적 응용 프로그램에서 데이터베이스 접근 대화식 인터페이스와 다름 필요성 대화식 인터페이스는 충분하게 편리하지 않음 대부분 데이터베이스 연산은 응용 프로그램을 통해 통해 실행 오늘날 많은 응용에서 웹 인터페이스를 제공

데이터베이스 프로그래밍 접근 내포(내장)된 명령문 데이터베이스 함수 라이브러리 새로운 언어의 개발 SQL 추가 기능 내포(내장)된 명령문 데이터베이스 명령문은 호스트 프로그래밍 언어 내에 내포시키는 방법 데이터베이스 함수 라이브러리 호스트 프로그램에서 데이터베이스 호출을 위해 사용하는 방법 응용 프로그래밍 인터페이스(API: Application Programming Inteface) 새로운 언어의 개발 데이터베이스 모델 및 질의어와 호환 가능하도록 개발

임피던스 불일치 데이터베이스 모델과 호스트 프로그래밍 언어 사이의 차이점 때문에 발생하는 문제를 언급할 때 사용하는 용어 SQL 추가 기능 데이터베이스 모델과 호스트 프로그래밍 언어 사이의 차이점 때문에 발생하는 문제를 언급할 때 사용하는 용어 데이터 타입의 호환성 문제  각 언어에 대한 바인딩하는 것이 필요 대표적 예: set vs. record-at-a-time processing 개별 애트리뷰트 값들을 추출하기 위해 질의 결과 내에 있는 반복하는 루프 기능이 필요

데이터베이스 프로그래밍의 동작 순서 SQL 추가 기능 클라이언트 프로그램은 데이터베이스 서버에 대한 연결을 설정(connect)하거나 개방(open) 클라이언트 프로그램은 데이터베이스에서 질의나 갱신을 사용함 데이터베이스 접근이 더 이상 필요할 필요가 없을 때, 클라이언트 프로그램은 연결 종료(close, disconnect)해야 함

강의 내용 주장(Assertions)으로 일반적인 제약조건 명시 SQL에서 뷰(가상 테이블) 데이터베이스 프로그래밍 내포된 SQL (Embedded SQL) 함수 호출, SQL/CLI [생략] 저장 프로시저와 SQL/PSM [생략] 요약

내포된 SQL (내장된 SQL) SQL 추가 기능 대부분 SQL 문장들은 COBOL, C, Java와 같은 범용 호스트 프로그래밍 언어에 내포될 수 있음 내포된 SQL 문장은 EXEC SQL 와 END-EXEC (또는 세미콜론(;))에 의해 호스트 프로그래밍 언어 문장으로 구분함 공유 변수들은 SQL 문장 내에서 사용될 때는 콜론(:)을 그 앞에 붙임

예제: C에서 변수 선언 DECLARE 내에 변수들은 SQL 구문에서(콜론(:)을 붙여 사용) 공유될 수 있거나 사용됨 SQLCODE 는 데이터베이스와 프로그램 사이에 오류와 예외 조건들을 전달하기 위해 사용 int loop; EXEC SQL BEGIN DECLARE SECTION; varchar dname[16], fname[16], …; char ssn[10], bdate[11], …; int dno, dnumber, SQLCODE, …; EXEC SQL END DECLARE SECTION;

데이터베이스 연결을 위한 SQL 명령문 SQL 추가 기능 연결(connection): 프로그램과 DBMS 서버를 연결하는 역할 수행 (다중 연결은 불가능하며 한 개의 연결만 활성화) CONNECT TO server-name AS connection-name AUTHORIZATION user-account-info; 현재 활성화된 연결을 다른 것으로 변경가능 SET CONNECTION connection-name; 해제(Disconnection) DISCONNECT connection-name;

내포된 SQL C 프로그래밍 예제 loop = 1; while (loop) { prompt (“Enter SSN: “, ssn); EXEC SQL select FNAME, LNAME, ADDRESS, SALARY into :fname, :lname, :address, :salary from EMPLOYEE where SSN == :ssn; if (SQLCODE == 0) printf(fname, …); else printf(“SSN does not exist: “, ssn); prompt(“More SSN? (1=yes, 0=no): “, loop); END-EXEC }

커서 관리 커서(반복자)는 여러 튜플들을 처리하기 위해 필요 사용법 (간략한 버전이며, 호스트 언어에 따라 달라질 수 있음) SQL 추가 기능 커서(반복자)는 여러 튜플들을 처리하기 위해 필요 사용법 (간략한 버전이며, 호스트 언어에 따라 달라질 수 있음) OPEN CURSOR: 질의 결과의 첫 번째 투플에 커서 위치 시킴 FETCH: 다음 튜플로 커서를 이동할 때 사용 CLOSE CURSOR: 질의 결과에 대한 처리를 종료했음을 나타냄

동적 SQL 목적: 실행 시 새로운 SQL 문장 수행 동적 수정문을 포함하는 것은 비교적 간단하지만 동적 질의는 훨씬 더 복잡함 검색된 애트리뷰트의 타입과 개수를 프로그램의 컴파일 시간을 알 수 없음

동적 SQL의 예 EXEC SQL BEGIN DECLARE SECTION; varchar sqlupdatestring[256]; EXEC SQL END DECLARE SECTION; … prompt (“Enter update command:“, sqlupdatestring); EXEC SQL PREPARE sqlcommand FROM :sqlupdatestring; EXEC SQL EXECUTE sqlcommand;

강의 내용 주장(Assertions)으로 일반적인 제약조건 명시 SQL에서 뷰(가상 테이블) 데이터베이스 프로그래밍 내포된 SQL (Embedded SQL) 함수 호출, SQL/CLI [생략] 저장 프로시저와 SQL/PSM [생략] 요약

요약 주장(assertion)을 사용한 추가적인 제약조건 명시 SQL 트리거: 주장의 특별한 종류 적절한 조건이 발생할 때 행동의 유형을 정의 뷰는 임시적(가상적) 테이블을 생성하는데 편리한 수단 데이터베이스 프로그래밍 내포된 SQL (Embedded SQL) 동적 SQL (Dynamic SQL)