제 제 8 8 장 장 SQL SQL 추가 추가 기능 기능 : : 주장 주장 , , 뷰 뷰 및 및 프로그래밍 프로그래밍 기법들 기법들
8.1 주장(Assertion)으로 일반적인 제약조건 명시 8.2 SQL에서 뷰(가상 테이블)
8.3 데이터베이스 프로그래밍: 이슈와 기법들 8.4 내포된 SQL, 동적 SQL 및 SQLJ
8.5 함수 호출을 이용한 데이터베이스 프로그래밍:SQL/CLI와 JDBC
8.6 데이터베이스 저장 프로시저와 SQL/PSM
8.7 요약
8.1 8.1 주장 주장 (Assertion) (Assertion) 으로 으로 일반적인 일반적인 제약조건 제약조건 명시 명시
SQL에서는 선언적 주장으로 확장된 제약조건을 명시할 수 있음
선언적 주장은 DDL의 CREATE ASSERTION문을 이용함
각 주장문은 제약조건 이름을 가지며, SQL 질의의 WHERE 절과 유사한 조건 형태로 지정됨예) "종업원 봉급이 부서 관리자 봉급보다 많으면 안된다"라는 제약 조건 CREATE ASSERTION SALARY_CONSTRAINT
CHECK (NOT EXISTS (SELECT *
FROM EMPLOYEE E, EMPLOYEE M, DEPARTMENT D WHERE E.SALARY＞M.SALARY AND
E.DNO=D.DNUMBER AND D.MGRSSN=M.SSN)) ;
제약조건 이름 SALARY_CONSTRAINT는 참조, 수정, 삭제하기 위해 사용됨
키워드 CHECK에 명시된 조건은 데이터베이스 상태가 만족해야 할 조건임 SQL에서는 ASSERT문과 유사한 TRIGGER 문을 사용할 수 있음
TRIGGER는 조건과 그 조건이 만족되는 경우에 취할 행동을 명시함 (21장에서 자세하게 설명 )8.2 SQL
8.2 SQL 에서 에서 뷰 뷰 ( ( 가상 가상 테이블 테이블 ) )
8.2.1 SQL에서 뷰의 개념
8.2.2 SQL에서 뷰의 명시
8.2.3 뷰의 구현과 갱신
8.2.1 SQL
8.2.1 SQL 에서 에서 뷰의 뷰의 개념 개념
뷰 (views)
SQL에서는 SELECT 명령의 결과인 투플들의 집합을 뷰로 지정할 수 있음
뷰는 다른 테이블 (혹은 이전에 정의한 뷰)에서 유도된 단일 테이블임
사용자는 전체 데이타베이스 보다는 자신이 관심을 가지고 있는 일부 데이타베이스를 뷰로 정의하여 사용할 수 있음
뷰는 가상적인 테이블로서 기본 테이블과는 달리 물리적인 형태로 저장되지 않음
뷰의 튜플들은 실제로 저장되지 않았으므로 뷰에 적용할 수 있는 갱신 연산들은 제한됨
뷰에 대한 질의는 기본 테이블에 대한 질의와 동일하게 작성할 수 있음8.2.2 SQL
8.2.2 SQL 에서 에서 뷰의 뷰의 명시 명시
뷰의 생성
CREATE VIEW 문장을 사용하여 뷰를 생성함
뷰는 뷰이름, 애트리뷰트 이름 리스트, 뷰의 내용을 지정하는 질의로 구성
뷰의 애트리뷰트 이름 리스트를 생략하는 경우는 묵시적으로 기본 테이블의 애트리뷰트 이름과 동일하게 지정됨(예) (1) CREATE VIEW WORKS_ON1
AS SELECT FNAME, LNAME, PNAME, HOURS FROM
EMPLOYEE, PROJECT, WORKS_ONWHERE
SSN=ESSN AND PNO=PNUMBER ;(2) CREATE VIEW DEPT_INFO ( DEPT_NAME, NO_OF_EMPS, TOTAL_SAL)
AS SELECT
DNAME, COUNT (*), SUM (SALARY)FROM
DEPARTMENT, EMPLOYEEWHERE
DNUMBER=DNOGROUP BY
DNAME ;8.2.2 SQL
8.2.2 SQL 에서 에서 뷰의 뷰의 명시 명시 (cont.) (cont.)
SQL에서 FROM절에 뷰가 대신 사용될 수 있음
예) 'ProjectX'에 참여하는 모든 종업원들의 성과 이름을 구하라 (WORKS_ON1은 뷰 임) SELECT PNAME, FNAME, LNAME
FROM WORKS_ON1
WHERE PNAME='ProjectX’ ;
뷰의 장점
SQL에서 뷰를 사용하면 질의 작성이 단순해짐9 위 질의를 기본 릴레이션을 이용하여 작성하는 경우 두 개의 조인을 사용해야 함
뷰의 최신성9 뷰는 SQL 질의에서 참조될 때마다 기본 테이블을 이용하여 계산되므로 항상 최신의 정보를 제공함
뷰에 보안기법과 권한부여 메커니즘를 적용하여 사용자 권한/보안을 더욱더 세밀하게 할 수 있음 뷰의 삭제
뷰는 DROP VIEW 명령을 사용하여 제거됨
8.2.3
8.2.3 뷰의 뷰의 구현과 구현과 갱신 갱신
뷰의 구현 방식 – 두가지
질의수정(query modification) 방식9 뷰에 대한 질의를 기본 테이블들에 대한 질의로 변환하여 처리하는 방식 9 질의 변환 시간과 노력이 문제임
뷰의 실체화(view materialization) 방식9 처음 질의에서 뷰에 대한 요구가 있을 때 임시 뷰 테이블을 물리적으로 생성하고, 뷰에 다른 질의들이 이어질 것이라는 가정 하에 테이블을 생성하여 유지하는 방식 9 기본 테이블이 변경되면 뷰도 변경해야 하므로 오버헤드가 적은 점진적
갱신(incremental update) 기법이 필요함
9 점진적 갱신은 기본 테이블중에서 변경이 발생하면 뷰 테이블 내에 삽입,삭제, 갱신할 투플을 결정함
뷰의 갱신
하나의 기본 테이블에서 정의된 뷰의 갱신은 기본 릴레이션들에 대한 갱신으로 매핑할 수 있음
조인을 이용하여 정의한 뷰의 갱신은 모호한 문제가 발생함Î 일반적으로 다수의 테이블 상에서 조인을 사용한 뷰들은 갱신할 수 없음
그룹화와 집단 함수를 사용한 뷰는 갱신할 수 없음8.2.3
8.2.3 뷰의 뷰의 구현과 구현과 갱신 갱신 (cont.) (cont.)
예) 뷰 WORKS_ON1에서 'John Smith'의 PNAME 애트리뷰트를'ProductX'에서 'ProductY'로 갱신하라 UPDATE WORKS_ON1
SET PNAME='ProjectY’
WHERE LNAME='Smith' AND FNAME='John' AND PNAME='ProductX’ ; Î 이 질의는 기본 릴레이션에 대한 갱신으로 변환하는데 모호함이 있음
방법 1 : 'John Smith'와 관련된 PROJECT의 'ProductX' 튜플을 'ProductY' 튜플의 PNO로 갱신함 - 요구한 갱신과 가장 유사함
UPDATE WORKS_ON
SET PNO = (SELECT PNUMBER FROM PROJECT WHERE PNAME='ProductY’ ) WHERE ESSN IN (SELECT SSN FROM EMPLOYEE
WHERE LNAME='Smith' AND FNAME='John’ ) AND PNO IN (SELECT PNUMBER FROM PROJECT
WHERE PNAME='ProductX’ ) ;
방법 2 : PROJECT 릴레이션에서 'ProductX' 튜플의 이름을 'ProductY'로 갱신함
UPDATE PROJECT
SET PNAME='ProductY’
WHERE PNAME='ProductX’ ;
8.3 8.3 데이터베이스 데이터베이스 프로그래밍 프로그래밍 : : 이슈와 이슈와 기법들 기법들
목적:
응용 프로그램에서 데이터베이스 접근
(대화식 인터페이스와 다름) 필요성:
대화식 인터페이스는 충분하게 편리하지 않음
대부분 데이터베이스 연산은 응용 프로그램을 통해 통해 실행
오늘날, 웹 인터페이스를 이용8.3.1
8.3.1 데이터베이스 데이터베이스 프로그래밍을 프로그래밍을 위한 위한 접근 접근 방법들 방법들
내포된 명령문
데이터베이스 명령문은 호스트 프로그래밍 언어 내에 내포시키는 방법 데이터베이스 함수 라이브러리
호스트 프로그램에서 데이터베이스 호출을 위해 사용하는 방법
응용 프로그래밍 인터페이스(API: Application Programming Interface) 새로운 언어의 개발
데이터베이스 모델 및 질의어와 호환 가능하도록 개발8.3.2
8.3.2 임피던스 임피던스 불일치 불일치
데이터베이스 모델과 호스트 프로그래밍 언어 사이의 차이점 때문에 발생하는 문제를 언급할 때 사용하는 용어
데이터 타입과 호환성 타입; 각 언어에 대한 바인딩하는 것이 필요
set vs. record-at-a-time processing9 데이타베이스는 테이블 형태의 질의 결과를 출력하나 호스트 프로그래밍 언어는 투플 단위로 처리 : 커서 또는 반복 변수를 사용
9 개별 애트리뷰트 값들을 추출하기 위해 질의 결과 내에 있는 반복하는 루프 기능이 필요
8.3.3
8.3.3 데이터베이스 데이터베이스 프로그래밍에서 프로그래밍에서 전형적인 전형적인 동작 동작 순서 순서
1. 클라이언트 프로그램은 데이터베이스 서버에 대한 연결을 설정하거나 개방함
2. 클라이언트 프로그램은 데이터베이스에서 질의나 갱신을 사용함 3. 데이터베이스 접근이 더 이상 필요할 필요가 없을 때,
클라이언트 프로그램은 연결 종료해야함
8.4 내포된 8.4 내포된 SQL, 동적 SQL, 동적 SQL 및 SQL 및 SQLJ SQLJ
8.4.1 내포된 SQL에서 단일 투플의 검색
8.4.2 내포된 SQL에서 커서를 사용하여 다중 투플의 검색 8.4.3 동적 SQL을 사용하여 실행 시점에 질의 명시
8.4.4 SQLJ: JAVA에서 내포된 SQL 명령
8.4.5 SQLJ에서 반복자를 사용한 다중 투플 검색
8.4.1
8.4.1 내포된 내포된 SQL SQL 에서 에서 단일 단일 투플의 투플의 검색 검색
대부분 SQL 문장들은 COBOL, C, Java와 같은 범용 호스트 프로그래밍 언어에 내포될 수 있음
내포된 SQL 문장은 EXEC SQL 와 END-EXEC (또는
세미콜론(;))에 의해 호스트 프로그래밍 언어 문장으로 구분함
공유 변수들은 SQL 문장 내에서 사용될 때는 콜론(:)을 그 앞에 붙임 DECLARE 내에 변수들은 SQL 구문에서(콜론(:)을 붙여 사용) 공유될 수 있거나 사용됨
SQLCODE 는 데이터베이스와 프로그램 사이에 오류와 예외 조건들을 전달하기 위해 사용
(예제) int loop;
EXEC SQL BEGIN DECLARE SECTION;
varchar dname[16], fname[16], …;
char ssn[10], bdate[11], …;
int dno, dnumber, SQLCODE, …;
8.4.1
8.4.1 내포된 내포된 SQL SQL 에서 에서 단일 단일 투플의 투플의 검색 검색 (cont.) (cont.)
데이타베이스에 연결하기
연결(Connection)9 다중 연결은 불가능하며 한 개의 연결만 활성화
CONNECT TO <서버 이름> AS <연결 이름>
AUTHORIZATION <사용자 계정과 암호>;
현재 활성화된 연결을 다른 것으로 변경 SET CONNECTION <연결 이름>;
해제(Disconnection)DISCONNECT <연결 이름>;
SQLCODE와 SQLSTATE를 사용하여 프로그램과 DBMS 사이에 대화하기
SQLCODE와 SQLSTATE는 데이타베이스 질의문이 수행된 후에 반환되는 error code를 보관함8.4.1
8.4.1 내포된 내포된 SQL SQL 에서 에서 단일 단일 투플의 투플의 검색 검색 (cont.) (cont.)
내포된 SQL 프로그래밍 예제 (그림 8.3)
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 }
8.4.2
8.4.2 내포된 내포된 SQL에서 SQL 에서 커서를 커서를 사용하여 사용하여 다중 다중 투플의 투플의 검색 검색
커서는 여러 투플로 이루어진 질의 결과로부터 한 투플을 가르키는 포인터로 간주할 있음
커서(반복자)는 여러 튜플들을 처리하기 위해 필요
FETCH 명령어는 다음 튜플로 커서를 이동할 때 사용
CLOSE CURSOR는 질의 결과에 대한 처리를 종료했음을 나타냄
8.4.3
8.4.3 동적 동적 SQL을 SQL 을 사용하여 사용하여 실행 실행 시점에 시점에 질의 질의 명시 명시
목적: 실행 시 새로운 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;
8.4.4
8.4.4 SQLJ:Java SQLJ:Java 에서 에서 내포된 내포된 SQL SQL 명령 명령
SQLJ: Java 내에 내포된 SQL의 표준
SQLJ의 번역기는 일반적으로 SQL문장을 JAVA로 변환
JDBC 인터페이스를 통해 실행함8.4.5 SQLJ
8.4.5 SQLJ에서 에서 반복자를 반복자를 사용한 사용한 다중 다중 튜플 튜플 검색 검색
SQLJ는 반복자의 두가지 타입을 제공
명명된 반복자(named iterator): 질의 결과에 연관될 때 결과에 있는 애트리뷰트의 이름과 타입들의 목록을 표시9 Next 연산으로 다음 투플을 검색함
위치 반복자(positional iterator): 질의 결과에 애트리뷰트 타입들의 목록만 표시9 FETCH 연산으로 다음 투플을 검색함
fetch <반복자 변수> into <프로그램 변수>
8.5 8.5 함수 함수 호출을 호출을 이용한 이용한 데이터베이스 데이터베이스 프로그래밍:SQL/CLI 프로그래밍 :SQL/CLI와 와 JDBC JDBC
내포된 SQL은 정적 데이터베이스 프로그래밍을 제공
API: 함수 라이브러리를 이용한 동적 데이터베이스 프로그래밍
특징: 전처리기를 필요하지 않음(많은 융통성 제공)
단점: SQL 명령에 대해 구문 검사 및 여타 검사를 실행시에 해야 함
종류:9 SQL/CLI(Call Level Interface): SQL의 표준으로 ODBC를 기반으로 개발됨 9 JDBC(Java Database Connectivity)
데이타베이스
데이타베이스 프로그래밍 프로그래밍
SQL 표준의 한 부분
여러 데이터베이스들을 한 응용 프로그램에서 좀더 쉽게 접근하기 위해 제공
DBMS업체자 제공하는 라이브러리를 사용함
SQL 문장들은 동적으로 만들어져 호출 함수 내에 문자열
매개변수로 전달됨
데이타베이스
데이타베이스 프로그래밍 프로그래밍 (cont.) (cont.)
호스트 프로그램과 데이타베이스간의 상호 동작에 관한 정보를 유지하는 자료 구조:
환경 레코드(environment record)9 하나 이상의 데이터베이스 연결을 추적하고 환경 정보를 유지하는 데 사용
연결 레코드(connection record)9 특정 데이터베이스 연결을 위해 필요한 정보를 유지
문장 레코드(statement record)9 한 SQL 문장을 위해 필요한 정보를 유지
설명 레코드(description record)9 투플이나 매개변수들에 관한 정보를 유지
데이타베이스
데이타베이스 프로그래밍 프로그래밍 (cont.) (cont.)
SQL/CLI를 통한 C 프로그램 작성 단계
1.
SQL/CLI libraries을 포함2.
레코드 핸들 변수들을 선언(called:SQLHSTMT, SQLHDBC, SQLHENV, SQLHDEC)3.
환경 레코드 SQLAllocHandle 사용하여 설정4.
연결 레코드 SQLAllocHandle 사용하여 설정5.
문장 레코드 SQLAllocHandle 사용하여 설정6.
SQL/CLI 함수 SQLPrepare를 사용하여 준비7.
SQL/CLI 함수 SQLBindParameter를 사용하여 매개변수들을 프로그램 변수에 바인딩8.
SQLExecute를 사용하여 SQL 문장을 실행9.
SQLBindCol 함수를 사용하여 질의 결과 내의 C 프로그램 변수에 바인드10.열의 값을 C 변수에 검색하기 위해 SQLFetch를 사용
8.5.2
8.5.2 JDBC:Java JDBC:Java 프로그래밍을 프로그래밍을 위한 위한 SQL 함수 SQL 함수 호출 호출
JDBC: Java에서 함수 호출을 사용하여 SQL 데이터베이스를 접근하는 데 사용
JDBC 함수를 이용한 Java 프로그램을 사용하려면 JDBC 드라이버를 설치하는 것이 필요
JDBC는 여러 데이터베이스를 연결하기 위한 프로그램임
8.5.2
8.5.2 JDBC:Java JDBC:Java 프로그래밍을 프로그래밍을 위한 위한 SQL 함수 SQL 함수 호출 호출
JDBC 데이타베이스 접근 단계
1.
JDBC 라이브러리(java.sql.*) 가져오기2.
JDBC 드리이버를 로드: Class.forname(“oracle.jdbc.driver.OracleDriver”)3.
적절한 변수들의 선언4.
연결 객체 생성 (getConnection 이용)5.
Statement 클래스에서 문장 객체를 생성:6.
물음표(“?”)기호는 문장 매개변수들을 나타냄9 실행시에 매개변수를 C 변수에 바인딩할 때 결정
7.
모든 매개변수들이 프로그램 변수에 바인드함8.
JDBC의 executeQuery 함수를 사용하여 (객체에 의해 참조된)SQL 문장 실행9.
질의 결과 처리(ResultSet 타입의 객체에 리턴)9 ResultSet 객체는 내포된 SQL의 커서나 SQLJ의 반복자와 유사함
8.6 8.6 데이터베이스 데이터베이스 저장 저장 프로시저와 프로시저와 SQL/PSM SQL/PSM
저장 프로시저: 데이타베이스 서버에 저장된 프로그램 모듈
SQL/PSM(SQL/Persistent Stored Modules): 저장 프로시저를
작성하는데 사용되는 데이타베이스 프로그래밍 언어 중의 하나임
8.6.1
8.6.1 데이타베이스 데이타베이스 저장 저장 프로시저와 프로시저와 함수 함수
프로시저/함수(모듈들)을 데이터베이스 서버에 저장하고 실행하는 것이 유용
장점들:
프로시저가 여러 응용에서 필요로 할 경우, 여러 응용에서 이를 호출할 수 있음(중복된 노력 감소)
서버에서의 실행은 클라이언트로의 데이터 통신 비용을 감소시킬 수 있음
복잡한 유도된 데이터를 계산하거나 복잡한 제약조건을 검사할 때에 사용할 수 있음 저장 프로시저의 일반적인 형식
저장 프로시저CREATE PROCEDURE <프로시저이름> (<매개변수>)
<지역적인 선언>
<프로시저 몸체>;
저장 함수CREATE FUNCTION <함수이름> (<매개변수>) RETRUNS <리턴 타입>
<지역적인 선언>
<함수몸체>;
프로시저나 함수 호출8.6.2 SQL/PSM:지속적 저장 모듈을 명시할 수 있는 SQL의 확장
SQL/PSM:
지속적 저장 모듈을 작성할 수 있는 SQL 표준의 한 부분 SQL + 저장 프로시저/함수 + 추가적인 프로그래밍 구조 포함
예., 조건(분기) 문장과 반복 로프 문장
SQL의 능력을 높임(예제)
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;
