◆ Accessing SQL from a Programming Language
◇ JDBC 와 ODBC --- Dynamic SQL
- API
- 공통된 특징
: Connect with DB server [연결]
: Send SQL commands to the DB server [질의]
: Fetch tuples of result one-by-one into program variables [질의결과]
- ODBC : C, C++, C#, Visual Basic
- JDBC : Java
◇ JDBC
- Java API for communicating with database systems supporting SQL
- 다양한 기능 지원 (예) 질의, 질의 결과, 데이터 업데이트..
- metadata 지원 (예) column name, column 개수, 데이터 수 ...
- 데이터베이스와의 통신 모델 :
연결 / 질의생성 / 질의 전송 및 결과 받음 / 예외 처리 메커니즘
- code 예제
-------------------------------------------------------------------------------------------------
publicstatic void JDBCexample(Stringdbid,String userid,String passwd)
{
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection conn = DriverManager.getConnection(
"jdbc:oracle:thin:@db.yale.edu:2000:univdb",userid,passwd);
Statement stmt= conn.createStatement();
… Do Actual Work ….
stmt.close();
conn.close();
}
catch (SQLExceptionsqle){
System.out.println("SQLException: " + sqle);
}
}
try{
stmt.executeUpdate(
"insert into instructorvalues(’77987’, ’Kim’, ’Physics’, 98000)");
} catch (SQLException sqle)
{
System.out.println("Could not insert tuple." + sqle);
}
ResultSet rset = stmt.executeQuery(
"select dept_name, avg (salary)
frominstructor
groupby dept_name");
while (rset.next()) {
System.out.println(rset.getString("dept_name") + " "+
rset.getFloat(2));
}
-------------------------------------------------------------------------------------------------
- prepared statement
(ex) PreparedStatement pStmt = conn.prepareStatement("insert into instructor values(?,?,?,?)");
-> '?' 물음표 : 후에 제공될 값들을 위해 자리를 확보.
(확보된 자리에 실제값들을 채우면서 반복적으로 수행)
- 주의사항 : 사용자로부터 값을 받아 그것을 쿼리를 통해 더할 때만 preparedStmt 이용
- SQL injection
: 다음과 같은 질의가 있다고 하자.
" select * from instructor where name = '"+name+"' "
사용자가 이름을 입력하는대신에, 다음과 같은 문자열을 입력했다고 하자.
X' or 'Y' = 'Y
그러면, 결과는 질의는 다음과 같다.
" select * from instructor where name='"+"X' or 'Y' = 'Y "+"' "
이 질의는 다음을 의미 한다.
- select * from instructor where name = 'X' or 'Y' = 'Y'
- 이 질의는 항상 참이 된다 'Y' = 'Y' 때문.
: 또한, 사용자는 다음과 같이 쓸 수도 있다.
X'; update instructor set salary = salary + 10000; --
오래된 시스템에서는 세미콜론으로 여러 개의 구문 실행 가능!
: Prepared statement는 사용자의 입력을 파라미터로 하므로, 내부적으로 다음과 같이 사용
" select * from instructor where name 'X\' or \'Y\' = \'Y'
- metadata features
● ResultSet metadata
: (예) ResultSet rs를 얻고자 질의를 실행한 후,
-------------------------------------------------------------------------------------------------
lResultSetMetaData rsmd = rs.getMetaData();
for(int i = 1; i <= rsmd.getColumnCount(); i++) {
System.out.println(rsmd.getColumnName(i));
System.out.println(rsmd.getColumnTypeName(i));
}
-------------------------------------------------------------------------------------------------
: 어떻게 유용한지?
- 결과의 열의 개수, 명시된 열의 이름, 명시된 열의 타입
- 결과의 스키마에 대한 정보 없이도 질의를 수행할 수 있음
(참고)
resultset metadata
database metadata
● database metadata
: query를 통하지 않고, 온전한 DB의 정보를 얻음 -- DB browsing에 유용
: (예)
-------------------------------------------------------------------------------------------------
nDatabaseMetaData dbmd = conn.getMetaData();
ResultSet rs = dbmd.getColumns(null, "univdb", "department","%");
//Arguments to getColumns: Catalog, Schema-pattern,Table-pattern,
// and Column-Pattern
// Returns: One row for eachcolumn; row has a number of attributes
// such as COLUMN_NAME,TYPE_NAME
while(rs.next()) {
System.out.println(rs.getString("COLUMN_NAME"),
rs.getString("TYPE_NAME");
-------------------------------------------------------------------------------------------------
: 어떻게 유용한지 ?
- 데이터베이스 브라우저 만들 때
- transaction control in JDBC (상세한 내용은 다음학기에)
: by default, each SQL statement is treated as a separate transaction that is committed automatically --- bad idea for transactions with multiple updates
: can turn off automatic commit on a connection
conn.setAutoCommit(false);
: transactions must be committed or rolled back explicitly
conn.commit();
conn.rollback();
* DB 는 항상 되돌아가려는 성질
: conn.setAutoCommit(true); turns on automatic commit
- 기타 JDBC 특징
: functions와 procedures 호출
: handling large object types
- getBlob(), and getClob() --- getString() 메쏘드와 유사하지만 Blob, Clob을 각각 반환
- getByte()를 통해 이러한 객체들로부터 데이터를 얻을 수 있음.
- associate an open stream with Java Blob or Clob object to update large object
blob.setBlob(int parameterIndex, InputStream inputStream);
- SQLJ
: JDBC는 전체적으로 dynamic하고, 컴파일타임에 error를 잡아낼 수 없음
: SQLJ ; embedded SQL in Java ... 프로그래밍 언어에서 제공하는 SQL 문
deptInfoIter iter = null;
#sql iter = { select dept_name, avg(salary) from instructor
group by dept name };
while(iter.next()) {
String deptName = iter.dept_name();
int avgSal = iter.avgSal();
System.out.println(deptName + " " + avgSal);
}
iter.close();
◇ ODBC
- ODBC를 지원하는 데이터베이스 시스템들은 '드라이버' 라이브러리를 제공한다
... 이는 반드시 클라이언트 프로그램과 링크되어야 한다.
- 클라이언트 프로그램이 ODBC API를 호출할 때, 라이브러리에 있는 코드와 서버가 통신하여, 요청한 액션을 수행하고 결과를 가져온다.
- ODBC 코드
{
RETCODE error;
HENV env; /* environment */
HDBC conn; /* database connection */
SQLAllocEnv(&env);
SQLAllocConnect(env, &conn);
SQLConnect(conn, “db.yale.edu", SQL_NTS, "avi", SQL_NTS, "avipasswd", SQL_NTS); /* SQL 환경과 데이터베이스 접속 핸들*/
{ …. Do actual work … }
SQLDisconnect(conn);
SQLFreeConnect(conn);
SQLFreeEnv(env);
// 연결 끊는것은 연결한 순서의 역순으로
}
- ODBC에서 정의하는 타입 : RETCODE, HENV, HDBC
- SQLExecDirect : 프로그램이 DB로 질의 보낼 때 사용
- SQLFetch() : 결과 가져오기
- SQLBindCol() : C 언어 변수를 쿼리 결과의 속성과 묶는다(bind) ... JDBC의 ResultSet
- ODBC 코드
char deptname[80];
float salary;
int lenOut1, lenOut2;
HSTMT stmt;
char * sqlquery = "select dept_name, sum (salary)
frominstructor
group by dept_name";
SQLAllocStmt(conn, &stmt);
error = SQLExecDirect(stmt, sqlquery, SQL NTS);
if (error == SQL SUCCESS) {
SQLBindCol(stmt, 1, SQL C CHAR, deptname , 80, &lenOut1);
SQLBindCol(stmt, 2, SQL C FLOAT, &salary, 0 ,&lenOut2);
while (SQLFetch(stmt) == SQL SUCCESS) {
printf (" %s %g\n", deptname, salary);
}
}
SQLFreeStmt(stmt, SQL DROP);
- ODBC Prepared Statement
lSQLstatement prepared: compiled at the database
- More ODBC feature
: metadata features
- DB내의 모든 릴레이션 찾기
- 쿼리 결과에 대한, 또는 어떤 릴레이션에 대한 column의 이름과 타입 찾기
: 기본적으로 commit은 자동적으로
- ODBC Conformance Levels ... 어느 Level 까지 쓸지!
: Core -> Level1 -> Level2 ... (갈수록 더 복잡)
- ADO.NET
: ODBC를 사용하는데, Visual basic을 위한 ODBC 정도?
◇ Embedded SQL --- PL 내에 내장된 SQL
- host language : embedded sql을 제공하는 언어
- EXEC SQL 구문은 embedded SQL 요청을 전처리기가 구분하기 위해 사용
(예) EXEC SQL <임베디드 SQL 구문 > END_EXEC
- cursor 사용
- 굳이 임베디드 SQL 을 사용하는 이유 ?
---> 컴파일시간에 에러 캐치 가능 (jdbc,odbc는 실행시간에만 캐치)
- open / fetch / close
EXECSQL open c END_EXEC
EXECSQLfetch cinto:si, :sn END_EXEC
EXECSQL close c END_EXEC
- cursor를 통한 업데이트 --- 자세히 몰라도 됨
◆ Functions and Procedures
: DBMS안에서 제공~ SQL에서 제공하는 모듈(module language) (PL에서도 제공하긴 하지만...)
: 자주 사용하는 것은 function/procedure 로!
: DB내에 프로시저 저장 가능
: 프로시저는 call statement 를 이용하여 실행
: (참고)
procedure : return value가 없거나 하나 이상
∪
function : return value가 only one(1)
: (참고) 몇몇 DB 는 결과로 릴레이션을 반환하는 table-valued functions를 지원
◇ SQL functions --- 사용자의 편의를 위해 --- 자주 쓰는 것!
(예제)
createfunction dept_count (dept_name varchar(20)) // input parameter
returnsinteger // return type
begin
declare d_count integer;
select count (* )into d_count
from instructor
where instructor.dept_name = dept_name
return d_count;
end
(사용법)
selectdept_name, budget
from department
wheredept_count(dept_name )> 12
◇ Table Functions --- relation에 대한 functions --- table을 리턴
(예제)
createfunctioninstructors_of(dept_namechar(20)
returnstable( ID varchar(5),
namevarchar(20),
dept_namevarchar(20),
salarynumeric(8,2)) // 리턴 형식
returntable
(selectID,name, dept_name, salary
frominstructor
where instructor.dept_name = instructors_of.dept_name)
(사용법)
select *
from table (instructors_of (‘Music’))
◇ SQL Procedure -- (procedure ---리턴이 없거나 1개 이상)
: input 및 output 명시
create procedure dept_count_proc (in dept_name varchar(20),
out d_count integer)
begin
select count(*)into d_count
from instructor
where instructor.dept_name = dept_count_proc.dept_name
end
: 호출 시 call 이용
declare d_count integer;
calldept_count_proc(‘Physics’, d_count);
: 1999 - overloading 허용! ( overloading -- 함수명만 같은 프로세저/함수가 하나이상 존재 가능)
(참고) 오버라이딩 ... 상속~ 부모 메쏘드 재정의
◇ Procedure constraints ( 프로시저 제약조건 )
- compound stmt : begin ... end
- while / repeat-until / for loop / if-then-else / case
- signaling of exception condition, and declaring handlers for exceptions...
:(예제)
declareout_of_classroom_seats condition
declare exit handler for out_of_classroom_seats
begin
…
.. signalout_of_classroom_seats
end
: 여기서 핸들러는 exit -- 이것은 에워싼 begin...end문이 종료되게 유발한다. (???)
◇ security with external language routines
: 보안 문제를 대처하기 위해 ! sandbox 기술 사용 ... 정해진 부분에서만 사용할수 있게한다.
◆ Triggers
: executed automatically by the system as a side effect of a modification to the DB.
: 데이터베이스 수정시 DBMS가 자동으로 변경해줌
(참고) constraint .. 지켜야하는 rule - integrity constraint / referential integrity constraints
◇ trigger 매커니즘
- trigger가 실행될 조건(condition) 명시
- trigger가 실행되었을 때 수행할 action 명시
◇ trigger ... 참조 무결성 검사에 많이 사용
(예) integrity constraint 검사 위해 trigger 사용
create trigger timeslot_check1afterinsert on section
referencing new row as nrow
foreach row
when (nrow.time_slot_id notin (
select time_slot_id
from time_slot))/* time_slot_id notpresent in time_slot */ /*time_slot과 section은 참조하는사이*/
begin
rollback /* 참조 무결성이 깨질 때*/
end;
(예)
createtrigger timeslot_check2afterdelete on timeslot
referencing old row as orow
foreach row
when (orow.time_slot_id notin (
select time_slot_id
from time_slot)
/* last tuple for time slot id deletedfrom timeslot */
and orow.time_slot_id in(
select time_slot_id
from section))/* and time_slot_id stillreferenced from section*/
begin
rollback
end;
◇ Trigger event and Actions in SQL
: insert, delete, or update --- 트리거 이벤트 발생 -- 즉, DB에 변화가 생길 때
: 업데이트에 관한 trigger 는 특정 attributes로 제한 될 수 있다.
: * referencing old row as : for delete and update
* referencing new row as : for insert and update
: 이벤트 없이 trigger가 활성 될 수도 있다.
createtrigger setnull_trigger beforeupdate of takes
referencingnew row as nrow
for each row
when (nrow.grade= ‘ ‘)
begin atomic
set nrow.grade =null;
end;
◇ Trigger to Maintain credits_earned value --- 이해하기
◇ Statement Level Triggers
- for each row ----> for each statement
- 임시 테이블(transition tables)을 지칭하기 위해 referencing old table 또는 referencing new table 사용
◇ Trigger를 사용하지 말아야 하는 경우
- 초창기 trigger 사용이유 :
요약 데이터 유지
change(delta) relation에 대한 변화를 저장하기 위해 데이터베이스를 복사할 때
- 오늘날 더 나은 방법 :
materialized view 이용
내장된 지원모듈을 이용
- 많은 경우에, encapsulation facilities가 trigger를 대신하여 사용될 수 있다.
fields를 업데이트 하기 위해 메쏘드 정의
trigger를 통하지 않고, update 메쏘드의 일부분으로 action 수행
- trigger의 예상치못한 실행으로 인한 위험성
backup copy로 부터 데이터 로드
remote site에서 중복된 업데이트
trigger execution can be disabled before such actions
- trigger로 인한 기타 위험성
error leading to failure of critical transactions that set off the trigger
cascading execution
◆ Recursive Queries
◇ recursion 예제
with recursive c_prereq(course_id, prereq_id) as (
select course_id, prereq_id
from prereq
union
select prereq.prereq_id, c_prereq.course_id
from prereq, c_prereq
where prereq.course_id = c_prereq.prereq_id
)
select ∗
from c_prereq;
◇ recursion의 파 워
- transitive closure queries를 쓸 수 있다 - only recursion/ iteration 에서만 가능
- recursive views는 monotonic 해야한다.
◆ Advancd Aggregation Features
◇ Ranking
- order by와 함께 사용
(예)
- 다음과 같은 릴레이션이 있다고 하자.
student_grades(ID, GPA)
- 잘못된 랭크 사용
select ID, rank() over (order by GPA desc) as s_rank
from student_grades
- 올바른 랭크 사용 : 결과를 순서화 하여 보여줌
select ID, rank() over (order by GPA desc) as s_rank
from student_grades
order by s_rank
- 참고 --- dense_rank : rank사이의 갭을 없앤다. 기존에 1등 2명이고 그 담에 3등이면, 1등2명, 그담엔 2등-
- 데이터의 일부분과 함께 사용할 수도 있다
(ㅇㅖ)
select ID, dept_name,
rank () over (partition by dept_name order by GPA desc)
as dept_rank
from dept_grades
order by dept_name, dept_rank;
- 기타 랭킹 함수
(n개의 투플, r 순위 : (r-1)/(n-1)로 표현)
4 fraction of tuples with preceding values
rank ( ) over (order by GPA desc nulls last) as s_rank
from student_grades (null 값을 나중에)
- ntile(n) : 같은 수를 가지는 n개의 버킷으로 분할하여 버킷 번호를 반환
(예) select ID, ntile(4) over (order by GPA desc) as quartile
from student_grades;
// 히스토그램 구축에 용이
◇ Windowing
- random variation을 부드럽게 나타내기 위해 사용!
- ex) moving average
- window specification
lsales(date, value) 라는 릴레이션이 존재할 때
select date, sum(value) over
(order by date between rows 1 preceding and 1 following)
from sales
- 기타 예
lbetween rows unbounded preceding and current
lrows unbounded preceding
lrange between 10 preceding and current row
lrange interval 10 day preceding
- partition에서도 사용
l“Find total balance of each account after each transaction on the account”
select account_number, date_time,
sum (value) over
(partition by account_number
order by date_time
rows unbounded preceding)
as balance
from transaction
order by account_number, date_time
'Computers > Databases' 카테고리의 다른 글
| seoul accord - day 2. SQL 기본 (0) | 2012.10.21 |
|---|---|
| seoul accord - day1 (0) | 2012.10.21 |
| ch4. intermediate SQL (0) | 2012.10.21 |
| ch3. introduction to SQL (0) | 2012.10.21 |
| ch2. intro to relational model (0) | 2012.10.21 |