Microsoft PowerPoint SQL 추가 기능

Transcription

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

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

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

4 주장 (Assertion) 에의한제약조건명시 (2/2) 주장의예제 : 사원의급여가자신이근무하는부서의관리자의급여보다많을수없다. 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)) Page 4

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

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

7 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)) Page 7

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

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

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

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

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

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

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

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

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

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

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

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

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

21 예제 : 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; Page 21

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

23 내포된 SQL C 프로그래밍예제 loop = 1; while (loop) { } prompt ( Enter SSN:, ssn); EXEC SQL END-EXECEXEC 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); Page 23

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

25 동적 SQL 목적 : 실행시새로운 SQL 문장수행 프로그램은실행시에키보드로부터 SQL 문장을작성 마우스로클릭한연산에대해동적으로 SQL 질의를생성 동적수정문을포함하는것은비교적간단하지만동적질의는훨씬 더복잡함 검색된애트리뷰트의타입과개수를프로그램의컴파일시간을알수없음 Page 25

26 동적 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; Page 26

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