SQL实验报告

　实验四触发器实验

　（一）

　after

　触发器

　（1 1 ）

　在l l ｉ neitem 表上定义一个 after 触发器, , 当修改列项目e e ｘ tendedprice d ｉ scount

　 x tax 时, , 要把 s orders 表得ｔo o ｔａｌ pri ｃe e 一起修改, , 以保证数据一致性

　C ＲE ＡＴＥ T ＲIGGER

　trig ＿line ｉte ｍ_ ｐr ｉce_ ｕｐdate on line ｉt ｅｍ fo ｒ upda ｔe

　 ａ ａs

　 begin i ｆ (UPDATE(ex ｔend ｅdprice)

　o ｒ UPDATE(tax)

　or UPD ＡT Ｅ(di ｓcou ｎt)) begin

　-— 声明游标变量指向 inserted 表

　d ｅclare

　ｃursor_inserted c ｕrs ｏｒ

　ｒea ｄ_only

　ｏ ｆｏr select ｏｒder ｋey,linenu ｍber,exte ｎd ｅdpr ｉce, ｄｉs ｃoun ｔ, ｔａx

　 from

　in ｓer ｔｅｄ

　 —-息信找查取获量变明声ﻩ 声明变量获取查找信息

　de ｃlare ｏｒｄer ｋｅy in ｔ, ＠ｌinenumb ｅr

　ｉnt,exte ｎd ｅｄｐrice

　ｒeal,diｓ ｓcount real,tax real

　—- 打开游标 ｅpoﻩ ｅn ｃursor_i ｎs ｅrt ｅd

　—-标游取读ﻩ 读取游标

　fe ｔｃｈ

　next

　from cur ｓｏr ＿i ｎs ｅｒte ｄ ｉnt ｏ ＠o ｒｄerkey, ＠lin ｅnumber,ｅ ｅxt ｅnde ｄｐｒice, ＠di ｓcount,t ａx

　ｗ ｗhi ｌe FETC Ｈ_S ＴATUS ＝0 ｎigebﻩ ｎ

　 ﻩ —- 声明一个变量保存重新计算得新价格 ｃｅdﻩﻩ ｅｃl ａre ＠n ｅw_tota ｌpri ｃe

　r ｅal

　ﻩ select ＠n ｅw_t ｏtal ｐrice= ＠ext ｅn ｄedpr ｉce ＊(1 －＠di ｓｃou ｎt) ＊(1 ＋＠tax)

　 —- 用新得总价格变量更新 orders 表得 t ｏt ａlprice ｐｕﻩﻩ ｕｐdate orde ｒs

　se ｔ tot ａlpri ｃe= ｎew_totalprice ｗｈere or ｄerkey=orde ｒｋｅy

　ｅn hctefﻩ ｅxt f ｒo ｍ cur ｓor_i ｎser ｔed int ｏ ＠order ｋe ｙ, ＠ｌi ｎｅnum ｂer, ＠ex ｔｅnde ｄp ｒice,discoun ｔ, ＠tax

　dneﻩｌlaedﻩ ｌocate c ｕr ｓｏr_i ｎser ｔe ｄ ｅ ｅnd end ﻩ(2)在 在 linei ｔｅｍ表上定义一个 aftr er 触发器, , 当增加一项订单明细时, ,

　自动修改 s orders 表得 total ｐ rice, 以保证数据一致性

　CREATE TR ＩGGER tri ｇ_ ｌｉｎｅit ｅｍ＿price_ ｉn ｓert ｏn l ｉｎe ｉｔｅm ｆ ｆor inse ｒｔ

　 a ｓ begin ——向指量变标游明声ﻩ 声明游标变量指向ins ｅｒted 表

　de ｃｌare

　c ｕrsor_inse ｒted

　cursor

　read_ ｏｎl ｙ

　 ﻩ for s ｅle ｃt orderkey,linenumbe ｒ,ex ｔe ｎdedp ｒic ｅ,di ｓｃｏu ｎt,t ａx

　 from inｓ ｓerte ｄ

　-- 声明变量获取查找信息 ｅdﻩ ｅclare ＠orde ｒk ｅy

　int, ＠ｌｉn ｅｎumber int,e ｘten ｄedp ｒice

　real, ＠discount

　real, ＠ta ｘ ｒｅal －—ﻩ － 打开游标

　 open cursor_i ｎs ｅrte ｄ －-ﻩ － 读取游标

　fe ｔc ｈ

　nex ｔ

　f ｒom

　cu ｒｓｏｒ_ins ｅrted

　into

　＠o ｒd ｅrkey,li ｎen ｕｍbｅ ｅｒ ｒ, ｅx ｔen ｄed ｐric ｅ,dis ｃount,tax ｉhwﻩ ｉle ＠＠FE ＴCH_ ＳTATU Ｓ＝0 ｅbﻩ ｅgin

　 -—格价新得算计新重存保量变个一明声ﻩﻩ 声明一个变量保存重新计算得新价格

　ｃedﻩ ｃlare ＠n ｅw_tot ａｌprice ｒｅａl

　ｃelesﻩ ｃt new ＿to ｔa ｌprice= ＠extend ｅdp ｒｉce ＊( １-d ｉs ｃｏunt) ＊(1+tax) —-新更量变格价总得新用ﻩﻩ 用新得总价格变量更新orders 表得totalpri ｃe

　ﻩ u ｐｄa ｔe

　or ｄｅｒs s ｅt

　t ｏta ｌpric ｅ=total ｐric ｅ+ ＠ｎｅw_ ｔota ｌｐr ｉce ｗ ｗｈ ｈe ｒe o ｒderke ｙ＝orderkey

　ｔｅfﻩ ｅｔch next from ｃｕrsor_ ｉns ｅrte ｄ ｉnto o ｒder ｋey, ＠l ｉnen ｕｍber,e ｘｔendeｄ ｄp ｒic ｅ, ＠disc ｏu ｎt, ＠t ａｘ

　ｅ ｅnd ａｅｄﻩ ｄｅａll ｏca ｔe cu ｒsor_in ｓerted en ｄ

　 (3)

　在ｌ in ｅ ite ｍ表上定义一个ａ ft ｅｒ触发器, , 当删除一项订单明细记录时, ,

　自动修改 orders 表得 tot ａ lprice, 以保证数据一致性

　CREATE ＴRI ＧG ＥR ｔrig ＿ｌine ｉｔem_price_de ｌet ｅ

　on line ｉtem fo ｒ de ｌｅt ｅ

　Ａ ＡS begin

　- －声明游标变量指向delet ｅd 表

　de ｃlar ｅ curso ｒ_d ｅleted ｃursor

　ｒe ａd ＿on ｌｙ

　ｅs

　rofﻩﻩ ｅle ｃｔ ｏrd ｅｒk ｅｙ,line ｎｕmbe ｒ,extende ｄp ｒｉce,discoun ｔ, ｔａx

　from

　del ｅｔｅｄ －ﻩ －－ 声明变量获取查找信息

　ｄｅｃlare ord ｅｒｋｅy ｉnt,linenum ｂe ｒ

　int,extendedp ｒi ｃe r ｅａl,discou ｎt real,ta ｘ ｒｅal －—ﻩ －

　打开游标 ｅpoﻩ ｅn c ｕrso ｒ_ ｄeleted

　 ——标游取读ﻩ 读取游标 ｅfﻩ ｅtch next ｆr ｏm cur ｓｏｒ＿delete ｄ in ｔo

　order ｋe ｙ,l ｉnenumb ｅｒ,ext ｅnｄ ｄedprice, ＠di ｓｃｏｕn ｔ,tax

　wh ｉｌｅ ＠FETCH_STATUS=0

　begi ｎ

　 -— 声明一个变量保存重新计算得新价格

　ﻩ declare ＠ne ｗ_ ｔo ｔａlpric ｅ ｒｅal

　ﻩ ｓ ｓelec ｔ ＠new_t ｏｔａlpr ｉce= ＠exte ｎｄe ｄpri ｃe*(1- ＠disco ｕnt) ＊(1+ ｔax)

　－ﻩ －— 用新得总价格变量更新orders 表得tot ａlp ｒice ｕﻩﻩ ｕpd ａte

　ｏｒｄers set t ｏtalpri ｃｅ=to ｔalpr ｉｃe- ＠new ＿totalp ｒｉce ｗh ｅr ｅ order ｋey= ＠ord ｅｒkey

　 fetch

　n ｅｘt f ｒom ｃｕｒsor_inse ｒt ｅd in ｔo orderkey, ＠line ｎｕｍbe ｒ,extendｅ ｅｄ ｄprice, ＠d ｉscou ｎｔ,t ａx ｄｎeﻩ ｎｄ

　ｄ ｄeal ｌo ｃat ｅ cur ｓor_inse ｒted ｅ ｅnd ( (4 4) ) 验证 up ｄ at ｅ触发器

　— －查瞧 号订单得 to ｔa a ｌ pr ｉ ce

　selec ｔ

　＊

　ｆｒo o ｍ

　o o ｒ ders where

　orde ｒ key=1 ８ 30;

　— －查瞧明细表得相关信息

　se ｌ ect *

　ｆ ro ｍ

　lin ｅi i ｔe e ｍ

　ｗ here or ｄe e ｒ key=183 ０

　and ｌ ine ｎｕm m ｂe e ｒ =1;

　—— 验证 e update 触发器

　updat ｅ

　lineitem set ｔ ax=tax+0 、 05

　ｗｈｅ re orderkey=1830;

　（二）

　i i ｎ stead

　ｏｆ

　触发器

　（ （1）

　）

　在 在 lineit ｅm 表上定义一个ｉns ｔｅad o ｆ ｕpda ｔｅ触 发器, 当修改明细表中得数量 量quan ｔi ｔy 时, 应先检查供应表par ｔsupp 得ａv ａi ｌqty 就是否足够, 不足够则拒绝执行, 否则执行并修改相应数值以保证数据一致性 于 由于 in ｓtead

　of 触发器更新某个表会使得该表上其她不满足更新列不能更新,因 因用 此逆向思维使用 a ｆter 触发器实现相同效果 即先更新 qu ａｎtity, 再比较ａv ａilｑ ｑｔ ｔy, 如果满足更新数量, 就修改ｐartsupp 得 表得 a ｖailqty, 如果不满足, 则把ｌineitem得ｑｕantity 更新之后得数据重新修改回来 ｃ ｃreate trigge ｒ trig_lin ｅｉt ｅm_quanti ｔy_ ｕpda ｔe

　on

　li ｎeit ｅm f ｏr upda ｔe

　as begin if UPDATE(qu ａnt ｉｔｙ) ｂ ｂegin ——向指别分量变标游明声ﻩ 声明游标变量分别指向 i ｎserted 表与 d ｅl ｅｔed 表 ｅｄﻩ ｄｅclare c ｕｒso ｒ_inser ｔed

　cur ｓor

　r ｅad_on ｌy

　for

　ｔcelesﻩﻩ ｔ

　orde ｒkey, ｐartk ｅy,s ｕppkey,lin ｅnum ｂer, ｑｕａntit ｙ fr ｏm i ｎserted

　ｄｅcl ａｒe

　cursor_de ｌeted c ｕrs ｏｒ

　rea ｄ_ ｏnl ｙ

　f ｏr select quantity

　fr ｏm deleted

　 -—息信找查取获量变明声ﻩ 声明变量获取查找信息

　decl ａｒe

　＠ｑu ａn ｔity ＿dif ｆ_lineitem i ｎｔ,q ｕanti ｔy_pa ｒtsup ｐ i ｎt ｃedﻩ ｃlare suppkey int, ＠ｐar ｔke ｙ in ｔ,o ｒd ｅrkey i ｎt,

　ｕnenilﻩﻩ ｕm ｂer int ,qty ＿inserted ｉn ｔ , ＠qty_delete ｄ int

　- － 打开游标 ｒuc nepoﻩ ｒsor_in ｓert ｅd ｐｏﻩ ｏｐen cur ｓｏｒ_d ｅleted

　 -—量变给赋值数标游取读ﻩ 读取游标数值赋给变量 ｆﻩ ｆet ｃｈ next from

　cu ｒsor_ ｉｎsert ｅd

　 ﻩ ｉ ｉnto or ｄe ｒk ｅy,pa ｒtkey, ＠suppk ｅy, ＠ｌine ｎu ｍber, ＠qty ＿ｉns ｅrte ｄ

　f ｅt ｃｈ

　ｎｅxt from

　cursor ＿d ｅｌeted

　int ｏ q ｔy ＿de ｌete ｄ

　whi ｌe fe ｔch_st ａtus=0 ｇｅbﻩ ｅｇin

　- －计算订单明细修改时, 订购数量得变化值 inserte ｄ表项-d ｅｌｅt ｅｄ表项

　 ｓ ｓel ｅct quantity_d ｉf ｆ_ ｌi ｎｅit ｅm= ＠q ｔy ＿in ｓe ｒｔe ｄ—＠ ＠q ｔｙ_deletｅ ｅd

　ﻩ －－从ｐarts ｕpp 表获取ａva ｉlq ｔｙ值, 注意ｐartsupp 表得主键为(partk ｅｙ,suppkｅ ｅy) ｔcelesﻩﻩ ｔ ＠ｑuanti ｔｙ＿p ａr ｔｓupp ＝av ａilq ｔy fro ｍ pa ｒｔsu ｐｐ

　ｗﻩﻩ ｗh ｅr ｅ suppkey= ｓuppke ｙ

　and part ｋey= ＠par ｔk ｅｙ

　 -—断判始开ﻩﻩ 开始判断

　ｇｅｂﻩ ｂｅｇi ｎ

　 ｆiﻩ ｆ quant ｉty ＿d ｉff_ ｌｉn ｅite ｍ＝0

　ﻩ

　ﻩ p ｒin ｔ " 更新得数量与原表中得值相同, 不需要更新"

　e ｌs ｅ if ＠quantit ｙ_d ｉｆｆ＿lin ｅitem 〈=q ｕantity_partsupp

　ﻩ

　be ｇｉn

　ﻩ ｐuﻩﻩ ｐd ａte ｐａrtsupp

　s ｅt avail ｑty= ａvailqty- ＠ｑua ｎtit ｙ_d ｉff_li ｎe ｉｔem

　ﻩ ｐus erehwﻩ ｐpkey=suppkey

　ａnd

　p ａrtkey= ＠par ｔke ｙ

　ﻩ

　ﻩ p ｒint " 两个表都更新成功’ ﻩ

　ﻩﻩｎeﻩ ｎd

　els ｅ

　ｉgebﻩﻩ ｉn

　ﻩ ｕﻩﻩ ｕpdate ｌi ｎei ｔem

　set

　ｑuantit ｙ＝quantity+ ＠quanti ｔｙ_diff_linei ｔem

　whe ｒe o ｒd ｅｒke ｙ＝＠orde ｒｋe ｙ and ｌi ｎｅｎu ｍber= ＠lｉ ｉne ｎｕｍber

　 ﻩ

　p ｒi ｎt ＇更新失败"

　ﻩﻩ ｅｎｄ

　ﻩ e ｎd ｅfﻩﻩ ｅtch ne ｘt

　fr ｏｍ c ｕrso ｒ_i ｎｓｅrted

　i ｎｔo ＠ｏrd ｅｒｋe ｙ, ＠partkey,s ｕpp ｋey, ＠linenumb ｅr, ＠qt ｙ_ ｉns ｅrt ｅd

　f ｅｔch ne ｘｔ from ｃurso ｒ＿de ｌeted into ＠qt ｙ_d ｅｌet ｅd ｄｎeﻩ ｎｄ

　d ｅallocat ｅ cur ｓor ＿i ｎserte ｄ

　dealloc ａt ｅ cursor_de ｌe ｔed ｅ ｅnd ｅ ｅnd (2)在 在 l ｉｎｅite ｍ表上定义一个 instea ｄ of in ｓｅrt 触发器, 当插入明细表中一条记录 时 时, 应先检查供应表ｐar ｔｓupp 得 得 ava ｉl ｑt ｙ就是否足够 qu ａｎｔi ｔy 得数量 c ｒｅa ｔe t ｒig ｇer ｔri ｇ_lineitem_q ｕa ｎti ｔy_ ｉｎsert

　on

　line ｉtem ｉ ｉnstead of ｉnser ｔ as ｂ ｂegin

　-— 声明游标变量指向 inserte ｄ表

　d ｅclar ｅ cur ｓｏｒ＿inserte ｄ ｃur ｓor

　rea ｄ_ ｏｎｌy f ｏr

　ｓﻩﻩ ｓelect or ｄｅrk ｅy,pa ｒｔkey,sup ｐk ｅy, ｌin ｅn ｕmber,q ｕantity

　f ｒｏm ins ｅr ｔed

　-—

　声明变量获取查找信息

　dec ｌare ｑｕａntity int, ＠av ａilq ｔy i ｎt, ＠suppkey

　ｉn ｔ, ＠partkey

　iｎ ｎｔ ｔ, ＠o ｒderkey ｉnt, ＠ｌinenu ｍber ｉnt

　-—标游开打ﻩ 打开游标 ｃ nepoﻩ ｃurs ｏｒ_ins ｅｒted －ﻩ －— 读取游标

　f ｅtc ｈ ｎext ｆro ｍ ｃｕrsor_insert ｅｄ ｉnt ｏ ＠ｏｒｄe ｒkey,partkey,＠ ＠ｓ ｓuppkey, ＠linenumber,qu ａｎtity

　wh ｉｌe ＠＠FETCH_S ＴATUS= ０ ｉgebﻩ ｉn

　-- 为变量赋值

　ａ tcelesﻩ ａvailqtｙ ｙ

　＝ ＝av ａi ｌqt ｙ fr ｏm

　ｐartsupp

　 ｗﻩ ｗhe ｒe suppkey ＝＠ｓu ｐｐk ｅy and part ｋe ｙ= ｐartke ｙ

　 ﻩ if ＠quant ｉty 〈= ＠avail ｑt ｙ

　 -— 如果可以更新

　ｂﻩ ｂegin ／ﻩﻩﻩ ／*将 将 insert ｅd 表中得记录插入到明细表＊/

　ﻩ ｓniﻩ ｓert

　i ｎto l ｉneite ｍ select ＊

　from i ｎserted

　ｒo=yekredro

　erehwﻩﻩ ｒderk ｅy ａnd

　linenumb ｅr = ＠linenumber ＊/ﻩﻩ新更时同ﻩ ＊同时更新 part ｓｕpp 表得数量＊/

　ﻩﻩ ｕ ｕpd ａｔe pa ｒｔsup ｐ ｓet a ｖailqty=availqty- ＠quanti ｔy

　ﻩ

　ｅrehwﻩ ｅ sup ｐｋe ｙ= ＠sup ｐkey and partkey=part ｋey

　 ｐ ｐr ｉnt ’pａ ａrts ｕｐp 表有足够得货物可以满足 lin ｅｉｔem 得ｑuan ｔitｙ ｙ, 插入成功’

　 ｅnd

　 ｅlse

　begin

　ﻩﻩ p ｒｉnｔ ｔ

　＇ ＇pa ｒt ｓup ｐ表没有足够得货物可以满足 l ｉneitem 得 得 q ｕａntity,插入失败’

　dneﻩｆﻩﻩ ｆetc ｈ next ｆrom ｃｕrso ｒ_ins ｅrt ｅd in ｔｏ ＠ord ｅrkey, ｐａrtkey, ｓuppkey, ＠lｉ ｉnenumbe ｒ, ｑu ａntity ｅﻩ ｅn ｄ

　deall ｏcat ｅ ｃｕrsor_ ｉｎserted end ( ３)在 在 line ｉte ｍ表上定义一个 inste ａd of del ｅte 触发器,当 当 删除 明细表中记录 时 时, 同时改变表 供应表 partsupp 得ａva ｉl ｑtｙ ｙ 数值 c ｒe ａte trigge ｒ tri ｇ_ ｌin ｅit ｅm_quanti ｔy_ ｄel ｅte o ｎ line ｉｔem inste ａd of ｄel ｅte as be ｇｉｎ

　—- 声明游标变量指向ｄeleted 表

　de ｃla ｒｅ curs ｏｒ_ ｄｅl ｅted c ｕｒｓor

　ｒead ＿only for

　 ｌｅsﻩﻩ ｅｌect or ｄerke ｙ, ｐar ｔｋey,sup ｐkey, ｌinenumbe ｒ,quan ｔity ｆr ｏm deleted －ﻩ －- 声明变量

　decl ａre s ｕpp ｋｅy i ｎt, ｐar ｔke ｙ

　int, ＠orde ｒkey int,linen ｕmb ｅr

　int, ＠quａ ａnt ｉt ｙ int

　 —-标游开打ﻩ 打开游标

　open

　cursor_deleted

　-—标游取读ﻩ 读取游标

　ｆ ｆetch ｎext fr ｏｍ

　curs ｏr ＿del ｅt ｅd

　ｉn ｔo ＠ord ｅrkey , ＠partkey,s ｕppkｅ ｅy, ＠lin ｅn ｕmber,q ｕa ｎtity

　 whi ｌｅ

　Ｆ ＦET ＣH_ ＳTATUS=0 ｉgebﻩ ｉn

　 ＊/＊除删ﻩ ＊删除＊/

　ﻩ de ｌet ｅ from

　lineite ｍ where linenu ｍber= ｌｉne ｎumber and o ｒｄe ｒｋey =orｄ ｄer ｋey

　 */新更时同ﻩ 同时更新 pa ｒt ｓupp 表得数量*/

　 u ｐdate

　parts ｕｐｐ ｓe ｔ

　availqt ｙ＝a ｖａi ｌq ｔy+quant ｉｔy

　 ﻩ where s ｕppke ｙ= ＠s ｕｐpkey a ｎd ｐａr ｔk ｅy= ＠p ａr ｔkey

　ﻩ p ｒin ｔ

　’ 删除成功, 并且已经把货物数量归还到 p ａr ｔsup ｐ里" ｆﻩﻩ ｆetc ｈ ne ｘｔ fr ｏｍ

　curso ｒ＿ｄel ｅted

　into ＠ｏrd ｅr ｋe ｙ

　,p ａr ｔｋey,＠ ＠suppkey, ＠lin ｅnu ｍber,quanti ｔy ｎeﻩ ｎd ｅdﻩ ｅa ｌloc ａte ｃursor_ ｄelete ｄ end (4) 验证 update 触发器 -- 查瞧ｌi ｎeit ｅm 得ｑuan ｔit ｙ select ＊

　fr ｏm

　lin ｅit ｅm ｗhe ｒe or ｄｅrk ｅy ＝1830

　and li ｎenum ｂer=1;

　—— 查询ｐartsupp 表得ava ｉlqt ｙ se ｌｅｃt ＊ from ｐart ｓupp w ｈｅre suppk ｅy ＝(select

　supp ｋｅy fr ｏm

　ｌin ｅ

　item w ｈｅre ｏrd ｅｒkey=18 ３０)

　and partk ｅｙ

　=(s ｅlec ｔ part ｋey from lin ｅite ｍ ｗh ｅr ｅ

　order ｋey ＝18 ３０ a ｎd linenu ｍｂer=1)

　－- - 更新数量过大

　 —— 更新得值与原值相同

　 －- - 更新到+ + ２ 00 数量, , 成功

　ｕpdate

　ｌi ｎeitem set

　ｑuant ｉty

　=q ｕanti ｔy+ ２00

　where order ｋey=1830

　and lin ｅn ｕmber ＝ １;

　 -- 更新 +2 ００ 成功后ｌ ineite ｍ得 quanti ｔy y 变化

　—— 更新+200 成功后par ｔs ｕpp 表得a ｖa ｉlqty 变化

　实验到此。所有创建得触发器结果

　 删除触发器

　dr ｏｐ trigger ｔri ｇ_li ｎei ｔem_de ｌete; 实验六存储过程实验

　（1 1 ）

　定义无参数 得存储过程并执行

　更新所有订单总价

　goﻩ

　CREATE

　ＰROCED ＵＲＥ Ｐro ｃ＿Ｃa ｌTotalP ｒi ｃe

　 AS BEGIN

　up ｄate

　o ｒders ｓet to ｔalprice ＝

　 (sele ｃｔ sum( ｅｘte ｎdedprice*(1-discount)*(1+ ｔax))

　ﻩ fr ｏｍ li ｎei ｔem

　ｏ

　erehwﻩ ｏrders 、ｏrderkey= ｌi ｎeite ｍ。o ｒderke ｙ) END

　ｇo 执行此存储过程

　exec P ｒｏ c_CalTotal Ｐｒｉc c ｅ; ;

　（2 2 ）

　定义有参数得存储 过程并执行

　更新给定订单号得总价

　ｇ ｇo create ｐro ｃedu ｒｅ P ｒo_Cal ＴotalP ｒｉｃe ４Ｏrd ｅｒ ＠id_ ｏrder

　ｉntege ｒ ａ ａs be ｇin

　ｕ ｕp ｄatｅ ｅ

　ｏ ｏrders ｓet ｔｏtal ｐrice=(

　ｌesﻩ ｌect SU Ｍ(ext ｅn ｄed ｐrice*(1 －di ｓc ｏunt)*(1 ＋ｔax))

　ﻩ fr ｏm li ｎeit ｅm

　ﻩ whe ｒe

　orde ｒs 。or ｄerke ｙ=lin ｅｉte ｍ。orderkey) end g ｏ 执行此存储过程 ｅｘe ｃ P ｒｏ_ ＣalTota ｌPr ｉce ４Order ＠ｉd_ ｏrder=2; （ （3）

　）

　定义有局部变量得存储过程 更新某一个顾客所有订单得总价 g ｏ cr ｅate

　procedure Proc_Ca ｌTot ａlPr ｉｃｅ４Ｃusto ｍe ｒ n ａｍe ＿cus ｔo ｍer varc ｈａr( ５0) ａs

　ｄｅc ｌａｒｅ ｃus ｔk ｅy ＿c ｕｓto ｍer i ｎt b ｅgin ｔcelesﻩ ｔ

　cus ｔke ｙ＿ｃu ｓｔｏmer=cu ｓt ｋey

　ｆｒｏm

　cu ｓto ｍer

　where name ＿customer ＝RTRI Ｍ(n ａｍe)

　u ｐdate order ｓ ｓet to ｔａlp ｒi ｃe=( ｔcelesﻩﻩ ｔ S ＵM(exte ｎde ｄp ｒｉｃe ＊(1 －ｄis ｃount)*(1+tax)) ｒfﻩﻩ ｒom ｌi ｎeite ｍ

　ｗﻩ ｗher ｅ orders 、order ｋｅy=lineitem 。or ｄｅｒｋe ｙ

　 ﻩ and

　o ｒde ｒs 、ｃust ｋey= ＠c ｕｓｔｋey ＿cust ｏmer) ﻩe ｎd go 执行此存储过程

　e ｘec Pro ｃ_CalT ｏt ａlPr ｉc ｅ4C ｕｓtome ｒ name_custom ｅr=" 艾锦亮’; 查瞧结果 u ｐdate

　ｌinei ｔe ｍ set

　ex ｔende ｄｐric ｅ＝1 ０0 , ｔa ｘ＝0.25 ｗｈere orderkey="1 ８30’; s ｅlect ＊ ｆrom

　l ｉneite ｍ

　w ｈere ｏrder ｋey ＝＇183 ０’;

　s ｅlect ＊ fr ｏm or ｄers ｗh ｅｒe ｃustke ｙ= (sele ｃt ｃｕs ｔk ｅy from custo ｍer ｗhe ｒe name=’ 艾锦亮’);

　（4 ）

　定义一个带输出参数得存储过程 更新某个顾客得所有得订单总价, 并输出总价 ｇｏ crea ｔe proc ｅdure Proc_C ａｌT ｏtalP ｒi ｃe4Custo ｍe ｒ2

　 ｎa ｍe_ ｃu ｓt ｏme ｒ va ｒch ａr( ５0), ｔotalpr ｉｃe_ord ｅr real out ｐut

　as —— 声明一个变量存储 ｎame 对应得顾客编号 d ｅclar ｅ ＠c ｕs ｔkey ＿cus ｔomer integer

　 —— 为该变量赋值

　se ｌec ｔ c ｕstke ｙ_cu ｓto ｍer ＝cu ｓtkey

　from ｃus ｔome ｒ w ｈere n ａme_ ｃustomer=R ＴRIM(n ａｍe) begi ｎ －ﻩ －－ 更改订单总价

　up ｄate

　o ｒd ｅrs

　ｓet t ｏｔａlp ｒｉｃe=(

　 se ｌec ｔ ＳUM(ext ｅｎｄedprice*(1-dis ｃｏunt)*( １+tax))

　ｆﻩ ｆr ｏｍ l ｉn ｅitem ｒｅhwﻩﻩ ｅｒe

　orders 。o ｒｄerk ｅy=li ｎｅｉt ｅｍ.ord ｅｒk ｅｙ

　、sredro dnaﻩ 、c ｕstkey ＝＠custkey_ ｃustome ｒ)

　-— 为返回变量赋值

　 select

　ｔotalprice_order=total ｐri ｃe fro ｍ orders

　 ﻩ w ｈere ｃustk ｅy= ＠c ｕstkey_c ｕstom ｅr

　ﻩ

　e ｎd go —- 执行带有输出参数得存储过程要声明输出参数变量 declare t ｏｔａlp ｒice re ａl ; exe ｃ Ｐroc_ Ｃａl Ｔota ｌPrice4C ｕstomer2

　" 艾锦亮 ＇, ＠to ｔalpri ｃe

　output; -- 在屏幕上输出返回值结果

　ｓelec ｔ t ｏtalpri ｃe;

　实验到此, , 所有结果显示

　 （5 5 ）

　修改存储过程名

　ｅｘec sp_renam ｅ ’Pr ｏ＿Cal ＴｏｔａｌPrice4Ord ｅｒ",

　’C ａｌＴotalPr ｉce4 Ｏr ｄｅｒ＇;

　(6) 编译存储过程

　exe ｃ sp_ ｒe ｃompile ’CalTotalPriｃ ｃe ４Order’;

　(7) 删除存储过程

　ｄ ｄrop

　ｐroc ｅｄure ＣalT ｏtalPric ｅ４Orde ｒ; ｅ ｅxe ｃ ｓp_h ｅlptext CalT ｏt ａlPrice4 Ｏｒder;

推荐访问:实验 报告