游標(biāo)、存儲過程、觸發(fā)器實驗題

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

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

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

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