2018-11-15.

Presentation on theme: "2018-11-15."— Presentation transcript:

1

2 Chapter 8 SQL 추가 기능: 주장, 뷰 및 프로그래밍 기법들
Chapter 8 SQL 추가 기능: 주장, 뷰 및 프로그래밍 기법들 Copyright © 2004 Pearson Education, Inc.

3 Chapter Outline 9.1 주장(Assertions)으로 일반적인 제약조건 명시 9.2 SQL에서 뷰(가상 테이블)
9.3 데이터베이스 프로그래밍 9.4 내포된 SQL 9.5 함수 호출, SQL/CLI 9.6 저장 프로시저와 SQL/PSM 9.7 요약

4 이 장의 목표 주장을 통해 일반적인 제약조건의 명시 뷰를 정의하는 SQL의 기능(가상 테이블)
범용 프로그래밍 언어를 이용하여 프로그램에서 데이터베이스를 접근하고 조작하기 위한 여러 기법들 (e.g., Java)

5 주장으로 제약조건 명시 일반적 제약조건: 기본 SQL에서는 8장에서 설명한 범주에 속하지 않음
Mechanism: CREAT ASSERTION components include: 제약조건 이름을 가지며, 다음에 키워드 CHECK가 오며, 데이터베이스 상태가 주장을 만족하는 여부(참/거짓)에 따라 조건(condition)이 뒤에 옴

6 주장 : 예제 사원의 급여가 자신이 근무하는 부서의 관리자의 급여보다 많으면 안된다”
CREAT 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))

7 일반적인 주장의 사용 주어진 조건을 위해하는 질의를 명시 질의의 결과가 공집합이 되어야 함 NOT EXISTS절 내에 포함시킴
이 질의의 결과가 공집합이 아니면 주장은 위배됨

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

9 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)) INFORM_SUPERVISOR (NEW.SUPERVISOR_SSN,NEW.SSN;

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

11 SQL에서 뷰의 명시 뷰를 정의하는 SQL 명령: CREATE VIEW 뷰의 정의는 (가상) 테이블 이름
애트리뷰트 이름들의 목록 함수나 산술 연산들을 적용하거나, 기본 릴레이션의 애트리뷰트 이름과 다른 이름을 사용하고자 할 때 이용 뷰의 내용을 나타내는 질의

12 SQL에서 뷰: 예제 다른 WORKS_ON 테이블의 명시 CREATE TABLE WORKS_ON_NEW AS
SELECT FNAME, LNAME, PNAME, HOURS FROM EMPLOYEE, PROJECT, WORKS_ON WHERE SSN=ESSN AND PNO=PNUMBER GROUP BY PNAME;

13 가상 테이블의 사용 새로이 생성된 테이블(뷰)에서 SQL 질의를 명시함 어떤 뷰가 더 이상 필요하지 않으면 뷰를 제거함
SELECT FNAME, LNAME FROM WORKS_ON_NEW WHERE PNAME=‘Seena’; 어떤 뷰가 더 이상 필요하지 않으면 뷰를 제거함 DROP WORKS_ON_NEW;

14 효율적인 뷰 구현 질의수정(query modification) 방식
뷰에 대한 질의를 기본 테이블들에 대한 질의로 변환하여 처리 단점: 복잡한 질의로 정의된 뷰들은 비효율적 특히 짧은 시간 내에 뷰에 많을 질의가 적용될 때

15 효율적인 뷰 구현 뷰의 실체화(view materialization) 임의 뷰 테이블을 물리적으로 생성하고 유지하는 방식
가정: 뷰에 다른 질의들이 사용됨 문제점: 기본 테이블이 갱신되면 뷰 테이블도 변경해야 함 해결방법: 오버헤드가 적은 점진적 갱신(incremental update)기법 필요

16 뷰의 갱신 집단함수를 사용하지 않는 단일 뷰의 갱신 조인을 포함하는 뷰의 갱신
뷰의 갱신은 단일 기본 테이블에 대한 갱신으로 사상될 수 있음 조인을 포함하는 뷰의 갱신 기본 릴레이션들에 대한 갱신 동작으로 사상될 수 있음 (항상 가능한 것은 아님)

17 갱신할 수 없는 뷰 그룹화와 집단함수를 사용하여 정의된 뷰는 갱신할 수 없음
일반적으로 다수의 테이블을 조인하여 정의한 뷰는 갱신할 수 없음 WITH CHECK OPTION: SQL에서 뷰의 갱신이 가능하려면 뷰 정의에 이 절을 추가해야 함 뷰를 갱신할 수 있는 가를 검사할 수 있고 뷰의 갱신을 위한 실행 계획을 찾아낼 수 있음

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

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

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

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

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

23 예제: 언어 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;

24 데이터베이스 연결을 위한 SQL 명령문 연결(Connection) 현재 활성화된 연결을 다른 것으로 변경가능
다중 연결은 불가능하며 한 개의 연결만 활성화 CONNECT TO server-name AS connection-name AUTHORIZATION user-account-info; 현재 활성화된 연결을 다른 것으로 변경가능 SET CONNECTION connection-name; 해제(Disconnection) DISCONNECT connection-name;

25 내포된 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 }

26 내포된 SQL C 프로그래밍 예 커서(반복자)는 여러 튜플들을 처리하기 위해 필요
FETCH 명령어는 다음 튜플로 커서를 이동할 때 사용 CLOSE CURSOR는 질의 결과에 대한 처리를 종료했음을 나타냄

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

28 동적 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;

29 Java에서 내포된 SQL 명령 SQLJ: Java 내에 내포된 SQL의 표준 SQLJ는 Java에서 SQL 문장으로 변환
JDBC 인터페이스를 통해 실행되야 함 클래스 라이브러리(예;java.sql)를 가져오는 것이 필요함

30 Java 데이터베이스 연결 JDBC: Java에서 함수 호출을 사용하여 SQL 데이터베이스를 접근하는 데 사용
JDBC 함수를 이용한 Java 프로그램을 사용하려면 JDBC 드라이버를 설치하는 것이 필요 JDBC는 여러 데이터베이스를 연결하기 위한 프로그램임

31 JDBC 데이터베이스 접근 단계 JDBC 라이브러리(java.sql.*) 가져오기
JDBC 드리이버를 로드: Class.forname(“oracle.jdbc.driver.OracleDriver”) 적절한 변수들의 선언 연결 객체 생성 (getConnection 이용) Statement 클래스에서 문장 객체를 생성: PreparedStatment CallableStatement

32 JDBC 데이터베이스 접근 단계 (cont.)
물음표(“?”)기호는 문장 매개변수들을 나타냄 실행시에 매개변수를 C 변수에 바인딩할 때 결정 모든 매개변수들이 프로그램 변수에 바인드 JDBC의 executeQuery 함수를 사용하여 (객체에 의해 참조된)SQL 문장 실행 질의 결과 처리(ResultSet 타입의 객체에 리턴) ResultSet은 이차원 배열이나 테이블과 비슷

33 Java에서 내포된SQL: 예제 ssn = readEntry(“Enter a SSN: “); try {
#sql{select FNAME< LNAME, ADDRESS, SALARY into :fname, :lname, :address, :salary from EMPLOYEE where SSN = :ssn}; } catch (SQLException se) { System.out.println(“SSN does not exist: “,+ssn); return; System.out.println(fname+“ “+lname+… );

34 SQLJ에서 다중 튜플 SQLJ는 반복자의 두가지 타입을 제공
명명된 반복자(named iterator): 질의 결과에 연관될 때 결과에 있는 애트리뷰트의 이름과 타입들의 목록을 표시 위치 반복자(positional iterator): 질의 결과에 애트리뷰트 타입들의 목록만 표시 FETCH 연산은 질의 결과에서 다음 튜플들을 검색 fetch iterator-variable into program-variable

35 함수 호출을 이용한 데이터베이스 프로그래밍 내포된 SQL은 정적 데이터베이스 프로그래밍을 제공
함수 호출을 이용한 데이터베이스 프로그래밍 내포된 SQL은 정적 데이터베이스 프로그래밍을 제공 API: 함수 라이브러리를 이용한 동적 데이터베이스 프로그래밍 특징: 전처리기를 필요하지 않음(많은 융통성 제공) 단점: SQL 명령에 대해 구문 검사 및 여타 검사를 실행시에 해야 함

36 SQL Call Level Interface
여러 데이터베이스들을 한 응용 프로그램에서 좀더 쉽게 접근하기 위해 제공 적절한 라이브러리들 (e.g., sqlcli.h for C) 설치하거나 이용가능해야 함 SQL 문장들은 동적으로 만들어져 호출 함수 내에 문자열 매개변수로 전달됨

37 SQL/CLI의 구성요소 환경 레코드(environment record) 연결 레코드(connection record)
하나 이상의 데이터베이스 연결을 추적하고 환경 정보를 유지하는 데 사용 연결 레코드(connection record) 특정 데이터베이스 연결을 위해 필요한 정보를 유지 문장 레코드(statement record) 한 SQL 문장을 위해 필요한 정보를 유지 설명 레코드(description record) 튜플이나 매개변수들에 관한 정보를 유지

38 SQL/CLI를 통한 C 프로그램 작성 단계 SQL/CLI libraries을 포함 레코드 핸들 변수들을 선언
(called: SQLHSTMT, SQLHDBC, SQLHENV, SQLHDEC) 환경 레코드 SQLAllocHandle 사용하여 설정 연결 레코드 SQLAllocHandle 사용하여 설정 문장 레코드 SQLAllocHandle 사용하여 설정

39 SQL/CLI를 통한 C 프로그램 작성 단계 (con’t.)
SQL/CLI 함수 SQLPrepare를 사용하여 준비 SQL/CLI 함수 SQLBindParameter를 사용하여 매개변수들을 프로그램 변수에 바인딩 SQLExecute를 사용하여 SQL 문장을 실행 SQLBindCol 함수를 사용하여 질의 결과 내의 C 프로그램 변수에 바인드 열의 값을 C 변수에 검색하기 위해 SQLFetch를 사용

40 데이터베이스 저장 프로시저 프로시저/함수(모듈들)을 데이터베이스 서버에 저장하고 실행하는 것이 유용 장점들:
프로시저가 여러 응용에서 필요로 할 경우, 여러 응용에서 이를 호출할 수 있음(중복된 노력 감소) 서버에서의 실행은 통신 비용을 감소 뷰의 모델링 능력을 높일 수 있음

41 저장 프로시저의 구성 저장 프로시저 저장 함수 프로시저나 함수 호출
CREATE PROCEDURE procedure-name (params) local-declarations procedure-body; 저장 함수 CREATE FUNCTION fun-name (params) RETRUNS return-type function-body; 프로시저나 함수 호출 CALL procedure-name/fun-name (arguments);

42 SQL 지속적 저장 모듈 SQL/PSM: SQL + 저장 프로시저/함수 + 추가적인 프로그래밍 구조 포함
예., 조건(분기) 문장과 반복 로프 문장 SQL의 능력을 높일 수 있음

43 SQL/PSM: 예제 CREATE FUNCTION DEPT_SIZE (IN deptno INTEGER)
RETURNS VARCHAR[7] DECLARE TOT_EMPS INTEGER; SELECT COUNT (*) INTO TOT_EMPS FROM SELECT EMPLOYEE WHERE DNO = deptno; IF TOT_EMPS > 100 THEN RETURN “HUGE” ELSEIF TOT_EMPS > 50 THEN RETURN “LARGE” ELSEIF TOT_EMPS > 30 THEN RETURN “MEDIUM” ELSE RETURN “SMALL” ENDIF;

44 요약 주장(assertion)은 추가적인 제약조건 명시 트리거는 주장의 특별한 종류
적절한 조건이 발생할 때 행동의 유형을 정의 뷰는 임시적(가상적)인 테이블을 생성하는데 편리한 수단임

45 요약(con’t.) 데이터베이스는 상호작용하는 데이터베이스를 통해 접근됨
그렇지만, 데이터베이스에서 데이터는 대부분 응용 프로그램들을 통해 조작됨 데이터베이스 프로그래밍의 여러 방법들: 내포된 SQL(embedded SQL) 동적 SQL(dynamic SQL) 저장 프로시저와 함수