數(shù)據(jù)庫(kù)系統(tǒng)概念高級(jí)

DB

Sysem數(shù)據(jù)庫(kù)系統(tǒng)概念高級(jí)第1頁(yè)，共70頁(yè)。DB

Sysem本章課程與特色cp

79任務(wù)第4章

.高級(jí)

SQL

71

PPT本章特色先通過(guò)SQL語(yǔ)言對(duì)數(shù)據(jù)庫(kù)的查插刪改獲得直觀感覺(jué)然后在后面從理論上深入（

RDB的RDB設(shè)計(jì)）關(guān)于家庭作業(yè):目的：鞏固知識(shí),發(fā)現(xiàn)問(wèn)題.平時(shí)自己作,不要等到期末才作.第22/頁(yè)7，0共70頁(yè)。DB

Sysem項(xiàng)目驅(qū)動(dòng)目標(biāo)：如何實(shí)現(xiàn)一個(gè)更加靈活、安全和可靠的數(shù)據(jù)庫(kù)：一、特殊數(shù)據(jù)類型及用途二、數(shù)據(jù)正確性的控制方法三、數(shù)據(jù)訪問(wèn)安全的控制方法

主要討論問(wèn)題：關(guān)系數(shù)據(jù)庫(kù)支持哪些特殊數(shù)據(jù)類型什么是數(shù)據(jù)完整性約束有哪些主要的數(shù)據(jù)約束如何有效控制對(duì)數(shù)據(jù)庫(kù)的訪問(wèn)關(guān)系數(shù)據(jù)庫(kù)提供哪些訪問(wèn)控制什么是授權(quán)圖？有何作用第3/3頁(yè)7，0共70頁(yè)。Chapter

4:

Advanced

SQLDB

Sysem第44/頁(yè)7，0共70頁(yè)。SQL

Data

Types

and

SchemasIntegrity

Constraints完整性約束AuthorizationEmbedded

SQLDynamic

SQL授權(quán)嵌入式SQL動(dòng)態(tài)SQL函數(shù)和過(guò)Functions

and

Procedural

Constructs**程化結(jié)構(gòu)Recursive

Queries**Advanced

SQL

Features**遞歸查詢高級(jí)SQL特性Built-in

Data

Types

in

SQL

內(nèi)建類型CP79DB

Sysem第55/頁(yè)7，0共70頁(yè)。date:

Dates,

containing

a

(4

digit)

year,

month

and

dateExample:

date

‘2005-7-27’time:

Time

of

day,

in

hours,

minutes

and

seconds.Example:

time

‘09:00:30’

time

‘09:00:30.75’timestamp:

date

plus

time

of

dayExample:

timestamp

‘2005-7-27

09:00:30.75’interval:period

of

time(時(shí)間段)Example:

interval

‘1’

daySubtracting

a

date/time/timestamp

value

from

another

gives

aninterval

valueInterval

values

can

be

added

to

date/time/timestamp

valuesBuild-in

Data

Types

in

SQL

(Cont.)

內(nèi)建類型CP79DB

Sysem第6/6頁(yè)7，0共70頁(yè)。Can

extract

values

of

individual

fields

from提取date/time/timestampExample:

extract

(year

from

r.starttime)Can

cast

string

types

to

date/time/timestamp轉(zhuǎn)換Example:cast<string-valued-expression>

as

dateExample:cast<string-valued-expression>

as

timeUser-Defined

Types

用戶自定義類型（可視為簡(jiǎn)稱）cp80DB

Sysem第7/7頁(yè)7，0共70頁(yè)。create

type

construct

in

SQL

creates

user-defined

type定義類型create

type

Dollars

as

numeric

(12,2)

finalcreate

domain

construct

in

SQL-92

creates

user-defineddomain

types定義域create

domain

person_namechar(20)

notnull類型和域相似,但域可以約束Types

and

domains

are

similar.

Domains

can

haveconstraints,

such

as

not

null,

specified

on

them.DB

SysemDomain

Constraints

域約束（型和值的約束）cp82Domain

constraints

are

the

most

elementary

form

of

integrconstraint.

They

test

values

inserted

in

the

database,queries

to

ensure

that

the

comparisons

make

sense.New

domains

can

be

created

from

existing

data

typesExample:

create

domain

Dollars

numeric(12,

2)create

domain

Pounds

numeric(12,2)We

cannot

assign

or

compare

a

value

of

type

Dollars

to

a

valof

type

Pounds.不同類型不能比較或賦值However,we

can

convert

type

as

below

類型轉(zhuǎn)換(cast

r.A

as

Pounds)(Should

also

multiply

by

the

dollar-to-pound

conversion-rate)第88/頁(yè)7，0共70頁(yè)。DB

SysemLarge-Object

Types大對(duì)象類型cp82Large

objects

(photos,

videos,

CAD

files,

etc.)

are

stored

asobject:二進(jìn)制大對(duì)象類型blob:

binary

large

object

--

object

is

a

large

collection

of

uninterbinary

data

(whose

interpretation

is

left

to

an

application

outsidedatabase

system)字符大對(duì)象類型clob:

character

large

object

--

object

is

a

large

collection

of

charWhen

a

query

returns

a

large

object,a

pointer

is

returned

rather

thalarge

object

itself.用指針?lè)祷?第/9頁(yè)7，0共70頁(yè)。Integrity

ConstraintsDB

Sysem第1100/頁(yè)7，0共70頁(yè)。完整性約束（值和型）cp82Integrity

constraints

guard

against

accidental

damage

todatabase,

by

ensuring

that

authorized

changes

to

thedatabase

do

not

result

in

a

loss

of

data

consistency.A

checking

account

must

have

a

balance

greater

than$10,000.00A

salary

of

a

bank

employee

must

be

at

least

$4.00

anhourA

customer

must

have

a

(non-null)

phone

number這些約束常常與語(yǔ)義或常識(shí)有關(guān)Constraints

on

a

Single

RelationDB

Sysem第1111/頁(yè)7，0共70頁(yè)。單表上約束cp82not

nullprimary

keyuniquecheck

(P

),

where

P

is

a

predicateNot

Null

Constraint

cp82DB

Sysem1第21/2頁(yè)7，0共70頁(yè)。Declare

branch_name

for

branch

is

not

nullbranch_name

char(15)

not

nullDeclare

the

domain

Dollars

to

be

not

nullcreate

domain

Dollars

numeric(12,2)

not

nullThe

Unique

Constraint

cp82DB

Sysem第1133/頁(yè)7，0共70頁(yè)。unique

(

A1,

A2,

…,

Am)表明這些屬性構(gòu)成候選關(guān)鍵字The

unique

specification

states

that

the

attributesA1,

A2,

…

Amform

a

candidate

key.Candidate

keys

are

permitted

to

be

null

(in

contrast

to

prikeys).候選碼可為空DB

SysemThe

check

clause檢查子句cp83check

(P

),

where

P

is

a

predicateExample: Declare

branch_name

as

the

primary

key

for

branchand

ensure

that

the

values

of

assets

are

non-negative.create

table

branch(branch_namechar(15),branch_citychar(30),assetsinteger,primary

key(branch_name),check

(assets>=

0))資產(chǎn)評(píng)估不能為負(fù)數(shù)1第41/4頁(yè)7，0共70頁(yè)。DB

SysemThe

check

clause

(Cont.)

cp83The

check

clause

in

SQL-92

permits

domains

to

be

restricted:Use

check

clause

to

ensure

that

an

hourly_wage

domain

allowonly

values

greater

than

a

specified

value.create

domain

hourly_wage

numeric(5,2)constraint

value_test

check(value

>

=

4.00)The

domain

has

a

constraint

that

ensures

that

the

hourly_wage

is

grethan

4.00The

clause

constraint

value_test

is

optional;

useful

to

indicateconstraint

an

update

violated.Constraint

子句可選,命名約束每小時(shí)工資不低于4

美元第1155/頁(yè)7，0共70頁(yè)。DB

SysemReferential

Integrity參照完整性

cp84比喻If

長(zhǎng)青區(qū)公安局的王兵為大學(xué)路派出所所長(zhǎng)（外關(guān)鍵字）Then 大學(xué)路派出所的記錄（元組）必須存在）皮之不存，毛（外關(guān)鍵字）將焉附？Ensures

that

a

value

that

appears

in

one

relation

for

a

given

attributes

also

appears

for

a

certain

set

of

attributes

in

relation.Example:If

“Perryridge”is

a

branch

name

appearing

in

onethe

tuples

in

the

account

relation,then

there

exists

a

tuple

ibranch

relation

for

branch

“Perryridge”.毛將嫣附？1第61/6頁(yè)7，0共70頁(yè)。DB

SysemReferential

Integrity參照完整性cp84Primary

and

candidate

keys

and

foreign

keys

can

be

specifiedpart

of

the

SQL

create

table

statement:The

primary

key

clause

lists

attributes

that

comprise

the

prikey.The

unique

key

clause

lists

attributes

that

comprise

a

candidaThe

foreign

key

clause

lists

the

attributes

that

comprise

the

foreand

the

name

ofthe

relation

referenced

bythe

foreign

key.

By

defauforeign

key

references

the

primary

key

attributes

of

the

reference第1717/頁(yè)7，0共70頁(yè)。DB

SysemReferential

Integrity

in

SQL

–

Example

cp84create

table

customer(customer_name

char(20),customer_street

char(30),customer_citychar(30),primary

key

(customer_name

))create

table

branch(branch_namebranch_cityassetschar(15),char(30),numeric(12,2),primary

key

(branch_name

))keykey1第81/8頁(yè)7，0共70頁(yè)。Referential

Integrity

in

SQL

–

Example

(Cont.)

cp84create

table

account(account_numberbranch_namebalancechar(15),integer,create

table

depositor(customer_nameaccount_numberprimary

key

(customer_name,

account_number),foreign

key

(account_number

)

references

account,foreign

key

(customer_name

)

references

customer

)foreign

keychar(10)?,派出所所長(zhǎng)primary

key

(account_number),foreign

key

(branch_name)

references

bra?nfcorhei)gnkey派出所所長(zhǎng)keych?kaery(20),

char(10),DB

Sysem第1919/頁(yè)7，0共70頁(yè)。Assertions

（斷言，即謂詞）cp86An

assertion

is

a

predicate

expressing

a

condition

that

wewish

the

database

always

to

satisfy.An

assertion

in

SQL

takes

the

formcreate

assertion

<assertion-name>

check

<predicateWhen

an

assertion

is

made,

the

system

tests

it

for

validitytests

it

again

on

every

update

that

may

violate

the

asserThis

testing

may

introduce

a

significant

amount

of

overhead;

henassertions

should

be

used

with

great

care.Assertingfor

all

X,

P(X)is

achieved

in

a

round-about

fashion

usingnot

exists

X

such

that

not

P(X)DB

Sysem第2020/頁(yè)7，0共70頁(yè)。Assertion

Example

cp86每筆貸款有一個(gè)貸款人，其存款至少$1000.00，下面是等價(jià)的表達(dá)沒(méi)有一筆貸款沒(méi)有貸款人，其存款至少$1000.00create

assertion

balance_constraint

check(not

exists

(select

*from

loanwhere

not

exists

(select

*from

borrower,

depositor,

accountwhere

loan.loan_number

=

borrower.loan_numberand

borrower.customer_name

=

depositor.customer_nameand

depositor.account_number

=

account.account_numband

account.balance

>=

1000)))存款人存有1000$DB

Sysem第2211/頁(yè)7，0共70頁(yè)。DB

SysemAssertion

Example

cp86所有貸款總和小于所有存款總和下面是等價(jià)命題沒(méi)有“貸款總和大于存款總和”create

assertion

sum_constraint

check(not

exists

(select

*from

branchwhere

(select

sum(amount

)from

loanwhere

loan.branch_name

=branch.branch_name

)>=

(select

sum

(amount

)from

accountwhere

loan.branch_name

=branch.branch_name

)))貸款總和存款總和2第222/頁(yè)7，0共70頁(yè)。DB

SysemAuthorization

授權(quán)（誰(shuí)，對(duì)什么，可作什么）cp86Forms

of

authorization

on

parts

ofthe

database:數(shù)據(jù)庫(kù)Read

-

allows

reading,

but

not

modification

of

data.Insert

-

allows

insertion

of

new

data,

but

not

modification

of

existing

data.Update

-

allows

modification,

but

not

deletion

of

data.Delete

-

allows

deletion

of

data.Forms

of

authorization

to

modify

the

database

schema

(covered

in

Chapter

8):視圖

Index

-allows

creation

and

deletion

of

indices.Resources

-

allows

creation

of

new

relations.Alteration

-

allows

addition

or

deletion

of

attributes

in

a

relation.Drop

-

allows

deletion

of

relations.第2233/頁(yè)7，0共70頁(yè)。DB

SysemAuthorization

Specification

in

SQL

授權(quán)語(yǔ)句cp86The

grant

statement

is

used

to

confer

authorizationgrant

<privilege

list>（可作什么動(dòng)作？）（對(duì)什么對(duì)象？）on

<relation

name

or

view

name>to

<user

list>（誰(shuí)？）<user

list>

is:a

user-idpublic,

which

allows

all

valid

users

the

privilege

grantedA

role

(more

on

this

in

Chapter

8)Granting

a

privilege

on

a

view

does

not

imply

granting

any

privileges

on

the

underlying

relations.The

grantor

of

the

privilege

must

already

hold

the

privilespecified

item

(orbe

thedatabase

administrator).能授須是已經(jīng)被授權(quán)的或管理員2第424/頁(yè)7，0共70頁(yè)。DB

SysemPrivileges

in

SQL

授權(quán)的動(dòng)作cp86select:

allows

read

access

to

relation,or

the

ability

to

quethe

viewExample:

grant

users

U1,

U2,

and

U3

select

authorization

on

the

branrelation:grant

select

on

branch

to

U1,

U2,

U3insert:

the

ability

to

insert

tuplesupdate:

the

ability

to

update

using

the

SQL

update

statementdelete:

the

ability

to

delete

tuples.all

privileges:

used

as

a

short

form

for

all

the

allowable

prmore

in

Chapter

8第2525/頁(yè)7，0共70頁(yè)。DB

SysemRevoking

Authorization

in

SQL 剝奪權(quán)利cp86The

revoke

statement

is

used

to

revoke

authorization.revoke

<privilege

list>on

<relation

name

or

view

name>

from

<user

list>Example:on

branch

from

U1,

U2,

U3revoke

select剝奪 動(dòng)作權(quán)力 操作對(duì)象 用戶<privilege-list>

may

be

all

to

revoke

all

privileges

the

rmay

hold.第2266/頁(yè)7，0共70頁(yè)。DB

SysemRevoking

Authorization

in

SQL 剝奪權(quán)利cp86If

<revokee-list>includes

public,all

users

lose

the

priexceptthosegranted

it

explicitly.除明確被授權(quán)的外全收回If

the

same

privilege

was

granted

twice

to

the

same

user

bydifferent

grantees,the

user

may

retain

the

privilege

arevocation.被賦予兩次的繼續(xù)保留All

privileges

that

depend

on

the

privilege

being

revokedalso

revoked.連帶權(quán)利一起收回2第72/7頁(yè)7，0共70頁(yè)。DB

SysemP.105

4.7P.104

4.2P.233

8.16預(yù)習(xí)下次課內(nèi)容(4章)項(xiàng)目驅(qū)動(dòng)目標(biāo)：如何在應(yīng)用編程中實(shí)現(xiàn)對(duì)數(shù)據(jù)庫(kù)的訪問(wèn)！一、SQL嵌入高級(jí)語(yǔ)言的方法二、SQL過(guò)程編程的方法主要討論問(wèn)題：什么是嵌入式SQL實(shí)現(xiàn)嵌入式SQL存在哪些難題實(shí)現(xiàn)嵌入式SQL的關(guān)鍵技術(shù)什么是SQL過(guò)程，有何用途SQL函數(shù)與過(guò)程有何不同SQL過(guò)程的編寫(xiě)有哪些關(guān)鍵技術(shù)練習(xí)

4：第28頁(yè)，共70頁(yè)。Embedded

SQL

嵌入式SQL

cp87DB

Sysem第2299/頁(yè)7，0共70頁(yè)。主語(yǔ)言，host

language，Pascal,PL/I,Fortran,C,and

Cobol.EXEC

SQL

<embedded

SQL

statement

>

END-EXEC嵌入開(kāi)始 嵌入結(jié)束Note:

this

varies

by

language.

E.g.

theJava

embedding

uses#

SQL

{

….

}

;Example

Query

cp88EXEC

SQL集合declare

c

cursor

forselect

customer-name,

customer-cityfrom

depositor,

customer,

accountwhere

depositor.customer-name

=customer.customer-nameand

depositor

account-number

=account.account-numberaccount.balance

>

:XandEND-EXECFromwithinahostlanguage,findthenamesandcitiesofcustomerswithmorethantheX賬戶，連接條件dollars

in

some

account.找出存款多于X元的X

是變量結(jié)果是集合，游標(biāo)c指向其中當(dāng)前元組DB

Sysem第3300/頁(yè)7，0共70頁(yè)。Embedded

SQL

(Cont.)

cp88The

open

statement

causes

the

query

to

be

evaluatedEXEC

SQL

open

c

END-EXEC把查詢結(jié)果元組放進(jìn)主語(yǔ)言用fetch

，（可循環(huán)調(diào)用）The

fetch

statement

causes

the

values

of

one

tuple

in

the

querto

be

placed

on

host

language

variables.EXEC

SQL

fetch

c

into

:cn,

:cc

END-EXECRepeated

calls

to

fetch

get

successive

tuples

in

the

query.DB

Sysem第3311/頁(yè)7，0共70頁(yè)。Embedded

SQL

(Cont.)

cp88通訊區(qū)SQLCA，SQL與主語(yǔ)言共享，互通信息A

variable

called

SQLSTATE

in

the

SQL

Communication

Area(SQLCA)

gets

set

to

‘02000’

to

indicate

no

more

data

isavailable結(jié)束，刪除臨時(shí)結(jié)果The

close

statement

causes

the

database

system

to

delete

thetemporary

relation

that

holds

the

result

of

the

query.EXEC

SQL

close

c

END-EXECNote:不同開(kāi)發(fā)公司，不同的版本，語(yǔ)句有所不同DB

Sysem3第23/2頁(yè)7，0共70頁(yè)。Updates

Through

Cursors

通過(guò)游標(biāo)更新光標(biāo)

C

指向結(jié)果集合中的當(dāng)前元組declare

ccursor

forselect

*from

accountwhere

branch-name

=

‘Perryridge’for

update

//以上準(zhǔn)備好查詢結(jié)果集合,下面更新Toupdatetupleatthecurrentlocationofcursorupdate

account//余額加100set

balance

=

balance

+

100where

current

of

cDB

Sysem第3333/頁(yè)7，0共70頁(yè)。Dynamic

SQL動(dòng)態(tài)SQL

cp90Allows

programs

to

construct

and

submit

SQL

queries

at

run

ti邊運(yùn)行，邊提交(late

bounding)Example

dynamic

SQL＋

C.char

*

sqlprog

=

“update

accountset

balance

=

balance

*

1.05where

account-number

=

?”EXEC

SQL

prepare

dynprog

from

:sqlprog;char

account

[10]

=

“A-101”;EXEC

SQL

execute

dynprog

using

:account;The

dynamic

SQL

program

contains

“?”,

which

is

a

place

holdfor

a

value

that

is

provided

when

the

SQL

program

is

execute占位元DB

Sysem第3434/頁(yè)7，0共70頁(yè)。ODBC中間件，連接件cp90Open

DataBase

Connectivity(ODBC)

standardstandard

for

application

program

to

communicate

with

a

database

serapplication

program

interface

(API)

toopen

a

connection

with

a

database,開(kāi)放式連接send

queries

and

updates,傳送查詢和更新get

back

results.

得到結(jié)果Applications

such

as

GUI,

spreadsheets,

etc.

can

use

ODBCDB

Sysem3第535/頁(yè)7，0共70頁(yè)。ODBC

(Cont.)

2

cp90Each

database

system

supporting

ODBC

provides

a“driver”

library

that

must

be

linked

with

the

client

program.支持

ODSC的軟件廠商提供自己的驅(qū)動(dòng)程序When

client

program

makes

an

ODBC

API

call,

the

code

in

thelibrary

communicates

with

the

server

to

carry

out

therequested

action,

and

fetch

results.客戶程序調(diào)用ODBC,庫(kù)函數(shù)與服務(wù)器通訊，執(zhí)行命令ODBC

program

first

allocates

an

SQL

environment,

then

adatabase

connection

handle.ODBC在內(nèi)存中建立SQL環(huán)境及DB連接句柄.DB

Sysem3第636/頁(yè)7，0共70頁(yè)。ODBC

(Cont.)

2

cp90Each

database

system

supporting

ODBC

provides

a“driver”

library

that

must

be

linked

with

the

client

program.支持

ODSC的軟件廠商提供自己的驅(qū)動(dòng)程序When

client

program

makes

an

ODBC

API

call,

the

code

in

thelibrary

communicates

with

the

server

to

carry

out

therequested

action,

and

fetch

results.客戶程序調(diào)用ODBC,庫(kù)函數(shù)與服務(wù)器通訊，執(zhí)行命令ODBC

program

first

allocates

an

SQL

environment,

then

adatabase

connection

handle.ODBC在內(nèi)存中建立SQL環(huán)境及DB連接句柄.DB

Sysem第3377/頁(yè)7，0共70頁(yè)。ODBC

(Cont.)

3 連接函數(shù)及其參數(shù)cp90Opens

database

connection

using

SQLConnect().ParametersSQLConnect:

連接庫(kù)connection

handle,連接句柄the

server

to

which

to

connect

服務(wù)器用戶標(biāo)識(shí)the

user

identifier,password

口令Must

also

specify

types

of

arguments:參數(shù)類型SQL_NTS

denotes

previous

argument

is

a

null-terminatedstring.SQL_NTS表示0位數(shù)串DB

Sysem第3388/頁(yè)7，0共70頁(yè)。ODBC

Code

代碼例cp90int

ODBCexample()

//C函數(shù){

RETCODE

error;HENV

env; //

environment

環(huán)境

HDBC conn;

/*

database

connection

*///連接SQLAllocEnv(&env);

//分配環(huán)境SQLAllocConnect(env,

&conn);

連接連接服務(wù)器用戶，指定口令，類型SQLConnect(conn,

"",

SQL_NTS,

"avi",

SQL_NTS,"avipasswd",

SQL_NTS);實(shí)際工作{….Do

actual

work…}善后工作：SQLDisconnect(conn);SQLFreeConnect(conn);//斷開(kāi)連接//釋放空間SQLFreeEnv(env); //釋放環(huán)境空間}DB

Sysem第3399/頁(yè)7，0共70頁(yè)。ODBC

Code

代碼例cp90int

ODBCexample()//C函數(shù){ RETCODE

error;HENV

env; //

environment

環(huán)境

HDBC conn;

/*

database

connection

*///連接SQLAllocEnv(&env);

//分配環(huán)境SQLAllocConnect(env,

&conn);

連接連接服務(wù)器用戶，指定口令，類型SQLConnect(conn,

"",

SQL_NTS,

"avi",

SQL_NTS,"avipasswd",

SQL_NTS);實(shí)際工作{….Do

actual

work…}善后工作：SQLDisconnect(conn);SQLFreeConnect(conn);//斷開(kāi)連接//釋放空間SQLFreeEnv(env);

//釋放環(huán)境空間}DB

Sysem第4400/頁(yè)7，0共70頁(yè)。DB

SysemODBC

Code

(Cont.)

cp90SQLExecDirect函數(shù)把SQL命令送給數(shù)據(jù)庫(kù)Result

tuples

are

fetched

using

SQLFetch()SQLFetch()函數(shù)把結(jié)果元組取出（給主語(yǔ)言）SQLBindCol()連接C的變量和查詢中的屬性變量A,SQLBindCol()的參數(shù)ODBC變量,屬性位置t類型轉(zhuǎn)換

from

SQL

to

C.變量地址對(duì)變長(zhǎng)度類型（如數(shù)組）自定最大長(zhǎng)度，位置，?串長(zhǎng)為負(fù)，表示空串4第14/1頁(yè)7，0共70頁(yè)。ODBC

Code

(Cont.)例Main

body

of

program

程序主體char

branchname[80];//支行名稱float

balance;余額;HSTMT

stmt; int

lenOut1,

lenOut2SQLAllocStmt(conn,

&stmt);

分配語(yǔ)句空間char

*

sqlquery

=

“select

branch_name,

sum

(balance)from

accountgroup

by

branch_name”;

字符串?dāng)?shù)組error=SQLExecDirect(stmt,sqlquery,SQL_NTS);錯(cuò)誤號(hào)及內(nèi)容if

(error

==

SQL_SUCCESS)

{SQLBindCol(stmt,

1,

SQL_C_CHAR,

branchname

,

80,

&lenOut1);0

,

&lenOut2);SQLBindCol(stmt,

2,

SQL_C_FLOAT,

&balance,while

(SQLFetch(stmt)

>=

SQL_SUCCESS)

{printf

("

%s

%g\n",

branchname,

balance);}}SQLFreeStmt(stmt,

SQL_DROP);變量說(shuō)明如果上面無(wú)錯(cuò)， 則連接DB

Sysem第4422/頁(yè)7，0共70頁(yè)。More

ODBC

Features特色Prepared

Statement

預(yù)備語(yǔ)句SQL

statement

prepared:

compiled

at

the

database

編譯有占位元，如insert

into

account

values(?,?,?)

，可循環(huán)使用Metadata

features

元特征可找?guī)熘兴嘘P(guān)系可找結(jié)果中列的名稱和類型默認(rèn)但各語(yǔ)句是可自動(dòng)提交的事務(wù)By

default,

statement

is

a

transaction，committed

auto.開(kāi)/關(guān)自動(dòng)提交特性SQLSetConnectOption(conn,

SQL_AUTOCOMMIT,

0)}SQLTransact(conn,

SQL_COMMIT)

提交SQLTransact(conn,

SQL_ROLLBACK)回滾DB

Sysem第4433/頁(yè)7，0共70頁(yè)。ODBC

Conformance

Levels一致性水平Conformance

levels

specify

subsets

of

the

functionality

defithe

standard.CoreLevel

1

需要支持原查詢Level

2

需要送/取數(shù)組的能力.SQL

Call

Level

Interface

(CLI)類似ODBC，有小區(qū)別.DB

Sysem第4444/頁(yè)7，0共70頁(yè)。JDBC的功能和模塊cp91JDBC

is

a

Java

API——DBJDBC

支持查插刪改JDBC

支持元數(shù)據(jù)查詢Model

for

communicating

with

the

database:有下列模塊Open

a

connection 打開(kāi)一個(gè)連接Create

a

“statement”

object 建立語(yǔ)句對(duì)象Execute

queries

using

the

Statement

object

to

send

queries

and

fetch執(zhí)行查詢，發(fā)詢，取結(jié)果Exception

mechanism

to

handle

errors能處理例外，少死機(jī)DB

Sysem4第545/頁(yè)7，0共70頁(yè)。JDBC

Code程序例cp91public

static

void

JDBCexample(String

dbid,

String

userid,

String

passwd){try

{Class.forName

(“oracle.jdbc.driver.OracleDriver”);//構(gòu)造類"jdbc:oracle:thin:@aura.bell-Connection

conn

=

DriverManager.getConnection(

:2000:bankdb",

userid,

passwd);Statement

stmt

=

conn.createStatement();…Do

Actual

Work

實(shí)際工作….stmt.close();善后conn.close();}catch

(SQLException

sqle)

{System.out.println("SQLException

:

"

+

sqle);}

}口令 庫(kù)標(biāo)識(shí) 用戶標(biāo)識(shí)例外處理建立連接建立語(yǔ)句 對(duì)象DB

Sysem4第646/頁(yè)7，0共70頁(yè)。JDBC

Code

(Cont.)

cp91Update

to

databasetry

{stmt.executeUpdate("insert

into

account

values("A-9732",

"Perryridge",

1200)");}

catch

(SQLException

sqle)

{System.out.println("Could

not

insert

tuple.

"

+

sqle);}Execute

query

and

fetch

and

print

results

查詢打印ResultSet

rset

=

stmt.executeQuery("select

branch_name,

avg(balance)from

accountgroup

by

branch_name");while

(rset.next())

{System.out.println(rset.getString("branch_name")

+

"

"

+

rset.getFloat(2));}插入一行DB

Sysem4第747/頁(yè)7，0共70頁(yè)。JDBC

Code

Details

cp91rs.getString(1)Dealing

with

Null

valuesint

a

=

rs.getInt(“a”);if

(rs.wasNull())Systems.out.println(“Got

null

value”);Getting

result

fields:獲得結(jié)果的字段rs.getString(“branchname”)

and欲取字段號(hào)，欲取字段名當(dāng)上以操作結(jié)果串為空時(shí)，其值為真，打印一個(gè)信息DB

Sysem4第84/8頁(yè)7，0共70頁(yè)。Prepared

Statement

準(zhǔn)備語(yǔ)句（即存儲(chǔ)程序）

cp91一次開(kāi)發(fā)，多次使用PreparedStatement

pStmt

=

conn.prepareStatement(insert

into

account

values(?,?,?)”);

pStmt.setString9732");pStmt.setString(2,

"Perryridge");pStmt.setInt(3,

1200);pStmt.executeUpdate();pStmt.setString(1,

"A-9733");pStmt.executeUpdate();NOTE:

If

value

to

be

stored

in

database

contains

a

single

quote

or

other

special

character,

preparework

fine,

but

creating

a

string

and

executing

it

directly

would

result

in

a

syntax

error!通配符號(hào)對(duì)象成員函數(shù)DB

Sysem4第84/9頁(yè)7，0共70頁(yè)。Procedural

Extensions

and

Stored

Procedures存儲(chǔ)過(guò)程DB

Sysem5第050/頁(yè)7，0共70頁(yè)。SQL

provides

a

module

languagePermits

definition

of

procedures

in

SQL,

with

if-then-else

statfor

and

while

loops,

etc.more

in

Chapter

9Stored

ProceduresCan

store

procedures

in

the

databasethen

execute

them

using

the

call

statementpermit

external

applications

to

operate

on

the

database

withoutknowing

about

internal

detailsThese

features

are

covered

in

Chapter

9

(Object

RelationalDatabases)Functions

and

Procedures

cp95DB

Sysem第5511/頁(yè)7，0共70頁(yè)。SQL:1999

supports

functions

and

proceduresFunctions/procedures

can

be

written

in

SQL

itself,

or

in

an

exteprogramming

languageFunctions

are

particularly

useful

with

specialized

data

types

simages

and

geometric

objectsExample:

functions

to

check

if

polygons

overlap,

or

to

compareimages

for

similaritySome

database

systems

support

table-valued

functions,

which

creturn

a

relation

as

a

resultSQL:1999

also

supports

a

rich

set

of

imperative

constructsincludingLoops,

if-then-else,

assignmentMany

databases

have

proprietary

procedural

extensions

tothat

differ

from

SQL:1999SQL

Functions

（用戶自定義函數(shù)）

cp95DB

Sysem第5522/頁(yè)7，0共70頁(yè)。Define

a

function

that,

given

the

name

of

a

customer,

returns

the

coof

the

number

of

accounts

owned

by

the

customer.create

function

account_count

(customer_namevarchar(20))returns

integerbegindeclare

a_count

integer;select

count

(*

)

into

a_countfrom

depositorwhere

depositor.customer_name

=

customer_namereturn

a_count;end

下頁(yè)有調(diào)用的例子SQL

Functions

cp95Find

the

name

and

address

of

each

customer

that

has

more

thone

account.select

customer_name,

customer_street,

customer_cityfrom

customerwhere

account_count

(customer_name

)

>

1調(diào)用自定 義函數(shù)DB

Sysem第5353/頁(yè)7，0共70頁(yè)。DB

SysemTable

Functions

表函數(shù)

以表為返回結(jié)果cp95SQL:2003

added

functions

that

return

a

relation

as

a

result查出指定客戶的賬號(hào)（涉嫌貪污？）create

function

accounts_of

(customer_name

char(20)returns

table

(

account_number

char(10),branch_name

char(15)balance

numeric(12,2))return

table(select

account_number,

branch_name,

balancefrom

account

Awhere

D.customer_name=ccounts_of.customer_nameand

D.account_number

=

A.account_number

))自定義函數(shù)5第45/4頁(yè)7，0共70頁(yè)。DB

SysemTable

Functions

(cont’d)

表函數(shù)

以表為返回結(jié)果Usageselect

*from

table

(accounts_of

(‘Smith’))調(diào)用自定 義函數(shù)5第555/頁(yè)7，0共70頁(yè)。SQL

Procedures過(guò)程(自學(xué)skip)

cp96DB

Sysem第5566/頁(yè)7，0共70頁(yè)。The

author_count

function

could

instead

be

written

as

proc計(jì)算過(guò)程create

procedure

account_count_proc

(in

title

varchaout

a_count

integer)beginselect

count(author)

into

a_countfrom

depositorwhere

depositor.customer_name

=account_count_proc.customer_nameendSQL

Procedures過(guò)程cp96DB

Sysem第5757/頁(yè)7，0共70頁(yè)。Procedures

can

be

invoked

either

from

an

SQL

procedure

or

fembedded

SQL,

using

the

call

statement.declare

a_count

integer;調(diào)用call

account_count_proc(

‘Smith’,