SQL u declare section declare section SQLSTATE EXEC

프로그래밍 환경에서의 SQL (계속) u 선언부(declare section)와 호스트 변수의 사용 – 호스트 변수는 다음과 같이 선언 » 호스트 변수가 선언된 부분: “선언부(declare section)” SQLSTATE 변수도 선언부에 선언 EXEC SQL BEGIN DECLARE SECTION; char studio. Name[50], studio. Addr[256]; char SQLSTATE[6]; EXEC SQL END DECLARE SECTION; 7

프로그래밍 환경에서의 SQL (계속) (예) 이름과 주소를 입력으로 받아, Studio 테이블에 적절한 튜 플을 삽입하는 함수 get. Studio() 를 작성하라. Void 선언부 get. Studio() { EXEC SQL BEGIN DECLARE SECTION; char studio. Name[50], studio. Addr[256]; char SQLSTATE[6]; EXEC SQL END DECLARE SECTION; /* 스튜디오 이름과 주소를 입력하라는 요청을 프린트하고, 이 입력을 studio. Name과 studio. Addr 변수로 읽어 들인다. */ EXEC SQL INSERT INTO Studio (name, address) VALUES (: studio. Name, : studio. Addr); 내장된 SQL 문 } 8

프로그래밍 환경에서의 SQL (계속) void worth. Ranges() { int i, digits, counts[15]; EXEC SQL BEGIN DECLARE SECTION; char worth; char SQLSTATE[6]; EXEC SQL END DECLARE SECTION; EXEC SQL DECLARE exec. Cursor CURSOR FOR SELECT net. Worth FROM Movie. Exec; 16

프로그래밍 환경에서의 SQL (계속) EXEC SQL OPEN exec. Cursor; for (i=0; i < 15; i++) counts[i] = 0; while(1) { EXEC SQL FETCH FROM exec. Cursor INTO : worth; if (NO_MORE_TUPLES) break; digits = 1; #define NO_MORE_TUPLES !(strcmp(SQLSTATE, “ 02000”) while (worth /= 10 > 0) digits++; if (digits <= 14) counts[digits]++; } EXEC SQL CLOSE /* 결과 출력 */ } 17

프로그래밍 환경에서의 SQL (계속) while(1) { EXEC SQL FETCH FROM exec. Cursor INTO : exec. Name, : exec. Addr, : cert. No, : worth; if (NO_MORE_TUPLES) break; if (worth < 1000) EXEC SQL DELETE FROM Movie. Exec WHERE CURRENT OF exec. Cursor; else EXEC SQL UPDATE Movie. Exec SET net. Worth = 2 * net. Worth WHERE CURRENT OF exec. Cursor; } EXEC SQL CLOSE exec. Cursor; } 19

프로그래밍 환경에서의 SQL (계속) u 튜플의 인출 순서: ORDER BY – 명시된 순서로 튜플들을 인출 EXEC SQL DECLARE movie. Star. Cursor CURSOR FOR SELECT title, year, studio. Name, star. Name FROM Movie, Stars. In WHERE title = movie. Title AND year = movie. Year ORDER BY year, title; 20

프로그래밍 환경에서의 SQL (계속) (예) 앞의 예 change. Worth() 에서 마지막 튜플부터 인출하여 튜플들의 리스트를 반대 순서로 얻을 수 있도록 예제를 수정. Void change. Worth () {. . . EXEC SQL DECLARE exec. Cursor SCROLL CURSOR FOR Movie. Exec; EXEC SQL OPEN exec. Cursor; EXEC SQL FETCH LAST FROM exec. Cursor INTO : exec. Name, : exec. Addr, : cert. No, : worth; while(1) {. . . EXEC SQL FETCH PRIOR FROM exec. Cursor INTO : exec. Name, : exec. Addr, : cert. No, : worth; } EXEC SQL CLOSE exec. Cursor; } 25

프로그래밍 환경에서의 SQL (계속) (예) SQL 문장을 스트링으로 입력 받아 실행시키는 C 함수를 작성. void readquery() { EXEC SQL BEGIN DECLARE SECTION; char *query; EXEC SQL END DECLARE SECTION; /* 사용자로부터 질의 입력 대기. malloc 등을 사용하여 호스트 변수 : query 의 공간 확보. : query가 질의의 첫번째 문자를 가리키도록 한다. */ EXEC SQL PREPARE SQLquery FROM : query; EXEC SQL EXECUTE SQLquery; } EXEC SQL EXECUTE IMMEDIATE : query 29

스키마에 저장된 프로시저 (계속) CREATE FUNCTION Band. W(y INT, s CHAR(15)) RETURNS BOOLEAN IF NOT EXISTS( SELECT * FROM Movie WHERE year = y AND studio. Name = s) THEN RETURN TRUE; ELSEIF 1 =< (SELECT COUNT(*) FROM Movie WHERE year = y AND studio. Name = s AND NOT in. Color) THEN RETURN TRUE; ELSE RETURN FALSE; END IF; 41

스키마에 저장된 프로시저 (계속) (예) PSM 에서 단일-행 선택연산(single-row select)의 예. CREATE PROCEDURE Some. Proc(IN studio. Name CHAR(15)) DECLARE pres. Net. Worth INTEGER; SELECT net. Worth INTO pres. Net. Worth FROM Studio, Movie. Exec WHERE pres. C# = cert# AND Studio. name = studio. Name; . . . 43

스키마에 저장된 프로시저 (계속) (예) 다음과 같은 프로시저 Mean. Var(s, mean, variance)를 작성하라. » 입력 인수: 스튜디오 이름 s » 출력 인수: 평균 mean, 분산 variance 스튜디오 s 가 소유한 모든 영화들의 상영시간 평균과 분산 CREATE PROCEDURE Mean. Var( IN s CHAR(15), OUT mean REAL, OUT variance REAL ) DECLARE Not_Found CONDITION FOR SQLSTATE ‘ 02000’; DECLARE Movie. CURSOR FOR SELECT length FROM Movie WHERE studio. Name = s; DECLARE new. Length INTEGER; DECLARE movie. Count INTEGER; 46

스키마에 저장된 프로시저 (계속) BEGIN SET mean = 0. 0; SET variance = 0. 0; SET movie. Count = 0; OPEN Movie. Cursor; movie. Loop: LOOP FETCH Movie. CURSOR INTO new. Length; IF Not_Found THEN LEAVE movie. LOOP END IF; SET movie. Count = movie. Count + 1; SET mean = mean + New. Length; SET variance = variance + new. Length*new. Length END LOOP; SET mean = mean/movie. Count; SET variance = variance/movie. Count – mean*mean CLOSE Movie. Cursor; END; 47

스키마에 저장된 프로시저 (계속) BEGIN SET mean = 0. 0; SET variance = 0. 0; SET movie. Count = 0; FOR movie. Loop AS Movie. Cursor CURSOR FOR SELECT length FROM Movie WHERE studio. Name = s; DO SET movie. Count = movie. Count + 1; SET mean = mean + length; SET variance = variance + length*length END FOR; SET mean = mean/movie. Count; SET variance = variance/movie. Count – mean*mean END; 50

스키마에 저장된 프로시저 (계속) (예) 영화 제목을 입력 받아 영화의 연도를 반환하는 함수 작성. – 입력된 제목의 영화가 없거나 둘 이상 있으면, NULL을 반환. CREATE FUNCTION Get. Year(t VARCHAR(255)) RETURNS INTEGER DECLARE Not_Found CONDITION FOR SQLSTATE ‘ 02000’; DECLARE Too_Many CONDITION FOR SQLSTATE ‘ 21000’; BEGIN DECLARE EXIT HANDLER FOR Not_Found, Too_Many RETURN NULL; RETURN (SELECT year FROM Movie WHERE title = t); END 54

스키마에 저장된 프로시저 (계속) (예) 대화식 SQL 인터페이스에서 PSM 함수를 사용하는 예. » 앞의 함수 Get. Year() 가 스키마에 포함되어 있다고 하자. INSERT INTO Stars. In(movie. Title, movie. Year, star. Name) VALUES(‘Remember the Titans’, Get. Year(‘Remember the Titans’), ‘Denzel Washington’); 56

호출-수준 인터페이스 (계속) (예) 앞의 예 worth. Ranges()를 SQL/CLI로 작성할 경우 초기화 부분. #include sqlcli. h SLQHENV my. Env; SQLHDBC my. Con; SQLHSTMT exec. Stat; SQLRETURN error. Code 1, error. Code 2, error. Code 3; error. Code 1 = SQLAlloc. Handle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &my. Env); if(!error. Code 1) error. Code 2 = SQLAlloc. Handle(SQL_HANDLE_DBC, my. Env, &my. Con); if(!error 2) error. Code 3 = SQLAlloc. Handle(SQL_HANDLE_STMT, my. Con, &exec. Stat) 76

호출-수준 인터페이스 (계속) (예-계속) worth. Ranges()를 SQL/CLI로 작성. » “SELECT net. Worth FROM Movie. Exec; ”을 SQL/CLI 로 표 현 SQLPrepare(exec. Stat, “SELECT net. Worth FROM Movie. Exec”, SQL_NTS); SQLExecute(exec. Stat); *SQLExec. Direct() SQLPrepare() 와 SQLExecute()를 합친 것 SQLExec. Direct(exec. Stat, “SELECT net. Worth FROM Movie. Exec”, SQL_NTS); 80

호출-수준 인터페이스 (계속) #include sqlcli. h void worth. Ranges() { int i, digits, counts[15]; SLQHENV my. Env; SQLHDBS my. Con; SQLHSTMT exec. Stat; 예제를 간단히 하기위해 오류 테스트 하는 코드는 대부분 생략되었음 SQL_INTEGER worth, worth. Info; SQLAlloc. Handle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &my. Env); SQLAlloc. Handle(SQL_HANDLE_DBC, my. Env, &my. Con); SQLAlloc. Handle(SQL_HANDLE_STMT, my. Con, &exec. Stat) 85

호출-수준 인터페이스 (계속) SQLPrepare(exec. Stat, “SELECT net. Worth FROM Movie. Exec”, SQL_NTS); SQLExecute(exec. Stat); SQLBind. Col(exec. Stat, 1, SQL_INTEGER, &worth, sizeof(worth), &worth. Info); while(SQLFetch(exec. Stat) != SQL_NO_DATA) { digits = 1; 튜플이 없음을 나타내는 상수 while(worth /= 10) > 0) digits++; if(digits <= 14) counts[digits]++ } for(i=0; i<15; i++) printf(“digits=%d: number of exec = %dn”, i, counts[i]); } 86

호출-수준 인터페이스 (계속) (예) 앞의 예 get. Studio() 에서 내장된 SQL로 작성된 INSERT 문장 을 SQL/CLI 로 작성하라. » 스튜디오 이름과 주소를 입력 받아, Studio 테이블에 삽입. /* 호스트변수 studio. Name 과 studio. Addr 에 입력을 받음 */ SQLPrepare(my. Stat, “INSERT INTO Studio(name, address) VALUES(? , ? )”, SQL_NTS); SQLBind. Parameter(my. Stat, 1, . . . , studio. Name, . . . ); SQLBind. Parameter(my. Stat, 2, . . . , studio. Addr, . . . ); SQLExecute(my. Stat); 89

JDBC (계속) u SQL 문장의 실행: execute. Query(), execute. Update() 메소드 *JDBC 에서 사용되는 용어 “update” ü 질의가 아닌 모든 문장들을 말함 ü (예) INSERT, DELETE, UPDATE, CREATE TABLE 등 – execute. Query(Q) » Statement 타입 객체에 적용 » Result. Set 타입 객체 반환 질의 Q 의 결과 튜플들 – execute. Query() » Prepared. Statement 타입 객체에 적용 » Result. Set 타입 객체 반환 94

JDBC (계속) (예) 연결 객체 my. Con이 있으며, 다음 질의를 수행하고자 한다. SELECT net. Worth FROM Movie. Exec; – 질의 준비 없이 실행 Statement exec. Stat = my. Con. create. Statement(); Result. Set Worths = exec. Stat. execute. Query( “SELECT net. Worth FROM Movie. Exec”); – 질의 준비 후 실행 Prepared. Statement exec. Stat = my. Con. create. Statement( “SELECT net. Worth FROM Movie. Exec”); Result. Set Worths = exec. Stat. execute. Query(); 96

JDBC (계속) (예) 매개변수가 없는 비질의(nonquery)를 실행하는 예. » Stars. In 테이블에 튜플 삽입. – 문장 준비 없이 실행 Statement star. Stat = my. Con. create. Statement(); star. Stat. execute. Update( “INSERT INTO Stars. In VALUES(‘Remember the Titans’, 2000, ‘Denzel Washington’)” ); 97

JDBC (계속) – 문장 준비 후 실행 Prepared. Statement star. Stat = my. Con. create. Statement( “INSERT INTO Stars. In VALUES(‘Remember the Titans’, 2000, ‘Denzel Washington’)” ); star. Stat. execute. Update(); 98

JDBC (계속) (예) 아래와 같이 앞에서 나온 질의 예에서, Statement exec. Stat = my. Con. create. Statement(); Result. Set Worths = exec. Stat. execute. Query( “SELECT net. Worth FROM Movie. Exec”); – Result. Set Worths 에 있는 튜플들을 하나씩 접근하여 처리. while(Worths. next()) { worth = Worths. get. Int(1); /* worth 를 처리 */ 100

JDBC (계속) (예) 앞의 예 get. Studio() 에서 내장된 SQL로 작성된 INSERT 문장 을 JDBC 로 작성하라. » 스튜디오 이름과 주소를 입력 받아, Studio 테이블에 삽입. Prepared. Statement studio. Stat = my. Con. create. Statement( “INSERT INTO Studio(name, address) VALUES(? , ? )”); /* 지역 변수 studio. Name 과 studio. Addr 의 값을 사용자로부 터 입력 받음. */ studio. Stat. set. String(1, studio. Name); studio. Stat. set. String(2, studio. Addr); studio. Stat. execute. Update(); 103

SQL에서의 트랜잭션 106 (계속) Void choose. Seat() { /* 항공편, 날짜, 좌석번호를 입력 받음 */ 시간 EXEC SQL SELECT occupied INTO : occ FROM Flights 사용자 1이 좌석이 WHERE flt. Num = : flight 빈 것을 발견 AND flt. Date = : date 사용자 2가 좌석이 AND flt. Seat = : seat; 빈 것을 발견 if (!occ) { EXEC SQL UPDATE Flights SET occupied = TRUE 사용자 1이 좌석 예약 WHERE flt. Num = : flight 사용자 2가 AND flt. Date = : date 좌석 예약 AND flt. Seat = : seat; /* 사용자에게 좌석 배정 통보 */ } else /* 가용하지 않다는 것을 통보 */ }

SQL에서의 트랜잭션 (계속) void transfer() { /* 두 계좌번호와 이체할 금액 입력 */ EXEC SQL SELECT balance INTO : balance 1 FROM Accounts WHERE acct. No = : acct 1; if (balance 1 >= amount) { EXEC SQL UPDATE Accounts SET balance = balance + : amount WHERE acct. No = : acct 2; (예) Fund transfer 이 부분에서 H/W나 S/W 붕 괴(crash)가 발생하면? EXEC SQL UPDATE Accounts SET balance = balance - : amount WHERE acct. No = : acct 1; EXEC SQL COMMIT } else /* 이체를 위한 자금이 부족하다는 메시지 출력*/ } EXEC SQL ROLLBACK 108

SQL에서의 트랜잭션 u (계속) SQL 2에서의 네 가지 고립성 수준(isolation level) n 비완료된-읽기 (read-uncommitted) SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; » 손상가능 읽기를 허용 n 완료된-읽기 (read-committed) SET TRANSACTION ISOLATION LEVEL READ COMMITTED; » 손상가능 데이터 (즉, 비완료된 데이터)의 읽기를 금지 118

SQL에서의 트랜잭션 n (계속) 반복가능-읽기 (repeatable-read) SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; – 같은 질의가 반복 수행되면 같은 결과를 생성 – 팬텀(phantom) 튜플 문제를 제외하면 직렬화가능과 같다. n 직렬화가능 (serializable) SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; – SQL 2 의 디폴트 120

SQL에서 보안과 사용자 권한 (계속) (예) Movie와 Studio 테이블을 포함하는 Movie. Schema 가 janeway 에 의해 생성되었다고 할 때, 연산-특권 부여의 예. GRANT SELECT, INSERT ON Studio To kirk, picard WITH GRANT OPTION; GRANT SELECT ON Movie TO kirk, picard WITH GRANT OPTION; GRANT SELECT, INSERT ON Studio TO sisko; GRANT SELECT ON MOVIE To sisko; picard GRANT SELECT, INSERT(name) ON Studio TO sisko; GRANT SELECT ON Movie TO sisko 130 janeway kirk

SQL에서 보안과 사용자 권한 (계속) (예) 앞의 예를 Janeway SELECT on Movie ** 부여 다이어그램 으로 나타내자. Janeway SELECT on Studio ** Janeway INSERT on Studio ** Kirk SELECT on Movie * Picard SELECT on Movie * Kirk SELECT on Studio * Picard SELECT on Studio * Kirk INSERT on Studio * Picard INSERT on Studio * Sisko INSERT(name) on Studio 132 Janeway INSERT on Movie ** Sisko SELECT on Movie Sisko SELECT on Studio Sisko INSERT on Studio

SQL에서 보안과 사용자 권한 (계속) Janeway SELECT on Movie ** Janeway INSERT on Movie ** Janeway SELECT on Studio ** Kirk SELECT on Movie * (예) janeway가 picard에게 부여했던 연산-특권들을 해제. Kirk SELECT on Studio * REVOKE SELECT, INSERT ON Studio FROM picard CASCADE; REVOKE SELECT ON Movie FROM picard CASCADE; Kirk INSERT on Studio * Sisko INSERT(name) on Studio 135 Janeway INSERT on Studio ** Sisko SELECT on Movie Sisko SELECT on Studio