游標(biāo)、存儲(chǔ)過程、觸發(fā)器實(shí)驗(yàn)題

1、倉(cāng)庫(kù)倉(cāng)庫(kù)號(hào)城市面積WH1北京370WH2上海500WH3廣州200WH4武漢400供應(yīng)商供應(yīng)商號(hào)供應(yīng)商名地址G1微軟廣州G3大連實(shí)德北京職工倉(cāng)庫(kù)號(hào)職工號(hào)工資WH2E11220WH1E31210WH2E41250WH3E61230WH1E71250訂購(gòu)單職工號(hào)供應(yīng)商號(hào)訂購(gòu)單號(hào)訂購(gòu)日期金額E1G1D31995-2-20500E2G3D22010-12-25500訂購(gòu)單明細(xì)訂購(gòu)單號(hào)訂購(gòu)單名單價(jià)負(fù)責(zé)人D1四大皆空230登記費(fèi)D2數(shù)字機(jī)564斯蒂芬D3士大夫235斯蒂D4史蒂夫456發(fā)動(dòng)機(jī)D5而感到345方的3實(shí)驗(yàn)八1、createtriggerwage_triggeron職工forupdateasde

2、clarewage_newint,wage_oldintselectwage_new=工資frominsertedselectwage_old=Xfromdeletedifwage_new>wage_old*1.06beginraiserror（'工資不可超過6%',16,1）rollbacktransactionend2、CREATETRIGGERdinggou_triggeron訂購(gòu)單forinsert,updateasdeclarewageintselectwage=訂購(gòu)單金額frominsertedifwage<3000beginif（selectcount

3、（*）frominsertedi,供應(yīng)商g,職工z,倉(cāng)庫(kù)cwherei.供應(yīng)商號(hào)=g.供應(yīng)商號(hào)andi.職工號(hào)=z.職工號(hào)andz.倉(cāng)庫(kù)號(hào)=c.倉(cāng)庫(kù)號(hào)andc.城市=g.地址）=0beginraiserror（'當(dāng)訂購(gòu)單金額小于3000時(shí)，必須使用本地供應(yīng)商',16,1）rollbacktransactionendend實(shí)驗(yàn)七、1、createprocedurewage_procedurewage1int,wage2intasselect倉(cāng)庫(kù).倉(cāng)庫(kù)號(hào),城市,職工號(hào),工資from倉(cāng)庫(kù),職工where倉(cāng)庫(kù).倉(cāng)庫(kù)號(hào)=職工.倉(cāng)庫(kù)號(hào)and工資>wage1and工資<wage2

4、declarewage1int,wage2int/此句可有可無(wú)executewage_procedure1200,12402、createprocedurebook2_proceduredatedatetimeasselect訂購(gòu)單.訂購(gòu)單號(hào),供應(yīng)商.*from供應(yīng)商,訂購(gòu)單where供應(yīng)商.供應(yīng)商號(hào)=訂購(gòu)單.供應(yīng)商號(hào)and訂購(gòu)日期>datedeclaredatedatetimeexecutebook_procedure''實(shí)驗(yàn)六、1、declaresnochar(10),singleint,instructorchar(10)declareaccount_cursorCursorforselect訂購(gòu)單.訂購(gòu)單號(hào),單價(jià),負(fù)責(zé)人from訂購(gòu)單join訂購(gòu)單明細(xì)on訂購(gòu)單.訂購(gòu)單號(hào)=訂購(gòu)單明細(xì).訂購(gòu)單號(hào)orderby單價(jià)openaccount_cursorfetchfromaccount_cursorintosno,single,instructorwhilefetch_status=0beginprintsno+convert(char(10),single)+''+instructorfetchfromaccount_cu