高級SQL(Chapter 5)
使用其他語言調用SQL
動態(tài)SQL
運行時以字符串形式構建SQL查詢→提交查詢→把結果存入程序變量中
JDBC(for Java)5.1.1
使用時，Java程序必須引用java.sql.*
驅動程序的加載
Class.forName(String driver);
driver：一個實現(xiàn)了java.sql.Driver接口的實體類
- 連接到數(shù)據(jù)庫
Connection con=DriverManager.getConnection(String url, String userid, String passwd);
url：指明服務器所在的主機名稱及端口等其他信息
- 向數(shù)據(jù)庫中傳遞SQL語句
Statement stmt=conn.createStatement();//創(chuàng)建statement
stmt.excuteUpdate(String expression);//實現(xiàn)非查詢語句（更新，刪除，插入等）冬殃，以字符串形式作為參數(shù)节值。返回整數(shù)表達被操作的元組數(shù)
ResultSet rset=stmt.excuteQuery(String expression);//實現(xiàn)查詢较坛，返回結果集捐迫，存在ResultSet類型的對象rset中
- 對結果集的操作
while(rset.next()){
System.out.println(rset.getString("dept_name")+" "+rset.getFloat(2));
}
rset.next()查看集合中是否還剩下未取出的元組，有的話就取出沟沙，沒有就返回false
getString和getFloat是把結果集中的東西轉化成String或Float
參數(shù)“dept_name”表示提取屬性名為dept_name的值
參數(shù)“2”表示提取第二個屬性的值
- 完成操作之后記得關閉
stmt.close();
conn.close();
- 預備語句
感覺有點像模板函數(shù)一樣，某些地方可以到時候再替換
除此以外壁榕，出于安全和規(guī)范考慮（防sql注入）矛紫，預備語句是首選
- 可更新結果集
對結果集中元組的更新將引起數(shù)據(jù)庫中的更新
- 事務
默認情況下每一句sql語句都被當成一個自動提交的事務，可以使用
conn.setAutoCommit(false/true);
打開或關閉這種自動提交牌里，關閉之后通過以下方式進行顯式提交或回滾
conn.commit();
conn.rollbacck();
- 其他
getBlob();//返回的不是大對象本身含衔，而是指針一樣的東西
getClob();
setBlob();
ODBC(for C, C++)5.1.2
嵌入式SQL5.1.3
將SQL查詢嵌入到其他語言（稱為宿主語言）中，使用宿主語言寫出的程序可以通過嵌入式sql的語法訪問數(shù)據(jù)庫
一個使用嵌入式sql的程序在編譯前必須用特殊的預處理器進行處理
為了使預處理器識別嵌入式sql請求二庵，使用如下語句
EXEC SQL <嵌入式sql語句>
- 連接到數(shù)據(jù)庫
EXEC SQL connect to <server> user <user_name> using <password>;
- 使用宿主語言變量
變量聲明要這樣放
EXEC SQL BEGIN DECLARE SECTION;
int credit_amount;
EXEC SQL END DECLARE SECTION;
使用的時候要在前面加上冒號以區(qū)分sql變量和宿主變量
- 聲明游標
EXEC SQL
declare c cursor for
select Id,name
from student
where tot_credit>:credit_amount;//:credit_amount是宿主語言變量贪染，所以前面加了個冒號
用open語句來執(zhí)行查詢，并把結果存放在臨時關系中
EXEC SQL open c;
使用fetch語句把結果元組賦值給宿主語言的變量催享，要求一個變量對應一個屬性
EXEC SQL fetch c into :si,:sn;
fetch語句每次只會取出一條結果杭隙，并將游標后移一個，所以可以通過循環(huán)取出所有結果
當結果已經(jīng)被全部取出因妙，SQLSTATE被置為“02000”痰憎，意味不再有數(shù)據(jù)
使用如下close語句刪除臨時關系
EXEC SQL close c;
函數(shù)和過程
聲明和調用SQL函數(shù)
- 格式
eg.給定一個系名，返回該系中的教師數(shù)目
create function dept_count (dept_name varchar(20))
returns integer #聲明返回的類型
begin
declare d_count integer;
select count(*) into d_count
from instructor
where instructor.dept_name=dept_name#這里為什么不需要用dept_count.dept_name
return d_count;
end
- 支持以關系為返回結果的函數(shù)攀涵，稱為表函數(shù)（table functions）
eg.
create function instructor_of(dept_name varchar(20))
returns table(
ID varchar(5),
name varchar(20),
dept_name varchar(20),
salary numeric(8,2))
return table
(select ID,name,dept_name,salary
from instructor
where instructor.dept_name=instructor_of.dept_name);#使用函數(shù)的參數(shù)時要用函數(shù)名instructor_of作為前綴
- 調用的時候直接用
聲明和調用過程（procedure）
create proocedure dept_count(in dept_name varchar(20),out d_count integer)
begin
select count(*) into d_count
from instructor
where instructor.dept_name=dept_count.dept_name
end
- 調用的時候用call
declare d_count integer;
call dept_count('Physics',d_count);
其他語句
- while語句
while 布爾條件 do
語句序列;
end while
- repeat語句
repeat
語句序列;
until 布爾表達式
end repeat
- for循環(huán)
declare n integer default 0;//默認值為0
for r as
select budget from department
where dept_name='music'
do
set n=n-r.budget
end for
//leave相當于break
//iterate相當于continue
- if語句
if ...
then ...
elseif ...
then ...
else ...
end if
- 異常條件（Exception）
declare out_of_classroom_seats condition
declare exit handler for out_of_classroom_seats
begin
......//這里的語句可以執(zhí)行signal out_of_classroom_seats來引發(fā)異常
end
SQL中用其他語言定義函數(shù)
效率高铣耘，可跨數(shù)據(jù)庫
- Function
create fuction dept_count(dept_name varchar(20))
returns integer
language C
external name '/user/avi/bin/dept_count'
- Procedure
create procedure dept_count_proc(in dept_name varchar(20),out count integer)
language C
external name '/user/avi/bin/dept_count_proc'
觸發(fā)器（Trigger）
格式
delimiter @ #聲明分隔符@
create trigger timeslot_check1 after insert on section
for each row #在插入的每一行上進行迭代
begin
if(NEW.time_slot_id #可以用OLD和NEW來表示操作發(fā)生前后的表
NOT IN(
SELECT
time_slot_id
FROM
time_slot))
then
rollback;
end if;
end @ #結束以@為分隔符的時光
delimiter ; #恢復以；作為分隔符的時光
設為無效
alter trigger ... disable;
高級聚集特性
排序
select ID, rank() over (order by(GPA) desc) as s_rank # rank函數(shù)對所有在order by屬性上相等的元組賦予相同的名次
from student_grades
order by s_rank;
空值null的存在可能會影響排序以故，可以通過設定nulls first或nulls last指定
select ID, rank() over (order by GPA desc nulls first) as s_rank
