【正文】 and = and Name=Name建立存儲過程實現(xiàn)嵌套查詢/*建立名為“嵌套查詢1”的存儲過錯，用來查詢某類藥品的銷售量*/create proc 嵌套查詢1KindExplanation varchar(12)asselect Number from sellChild where MedicineCode In(select MedicineCode from MedID where KindExplanation=KindExplanation)/*建立名為“嵌套查詢2”的存儲過錯，用來查詢某個供應商提供的商品*/create proc 嵌套查詢2FirmName varchar(16)asselect MedicineName from MedInfor where FirmCode In (select FirmCode from FirmInfor where FirmName=FirmName)/*建立存儲過程實現(xiàn)集合查詢*//*建立名為“集合查詢1”的存儲過錯，用來查詢提供某類商品的供應商數(shù)*/create proc 集合查詢1MedicineName varchar(8)asselect avg(FirmCode) from FrimInfor where FirmCode in(select FirmCode from MedInfor,FrimInfor where = and MedicineName=MedicineName)/*建立名為“集合查詢2”的存儲過錯，用來查詢藥品種類數(shù)*/create proc 集合查詢2asselect count(MedKindeCode) from MedID/*視圖建立*//*1，由藥品類別表“MedID”建立一個視圖，該視圖由藥品類別表的所有列構(gòu)成*/create view 藥品類別(藥品類別代碼,類別說明)asselect* from MedID/*2，由客戶信息表“GueInfor”建立一個視圖，該視圖由客戶信息表的所有列構(gòu)成*/create view 客戶信息(客戶編碼,客戶名稱,聯(lián)系人,聯(lián)系電話,所在城市)asselect* from GueInfor/*3，由供應商信息表“FirmInfor”建立一個視圖，該視圖由供應商信息表的所有列構(gòu)成*/create view 供應商信息(供應商編碼,供應商名稱,聯(lián)系人,聯(lián)系電話,所在城市)asselect* from FirmInfor/*建立INSERT觸發(fā)器*/create trigger MedID_insert on MedIDfor insertas if(select count(*)from MedID_med,insertedwhere =)=0rollback transaction/*建立DELETE觸發(fā)器*/ create trigger delete_MedID on MedIDfor deleteasselect* from MedIDdeclare MedKindeCode char(10) select MedKindeCode=MedKindeCode from deleteddelete from MedIDwhere MedKindeCode=MedKindeCodeselect*from MedID*/建立UPDATE觸發(fā)器*/ create trigger MedID_update on MedIDfor updateasif update(MedKindeCode)beginraiserror(39。,39。,39。create proc MedInfor_delete_procMedicineName varchar(8)asdelete from MedInfor where MedicineName=MedicineNamecreate proc GueInfor_delete_procGuestCode char(10) asdelete from GueInforwhere GuestCode=GuestCodecreate proc FirmInfor_delete_procFirmCode char(10)asdelete from FirmInforwhere FirmCode=FirmCodecreate proc WorkInfor_delete_procWorkNo char(10)asdelete from WorkInforwhere WorkNo=WorkNocreate proc sellMain_delete_procSaleNo intasdelete from sellMainwhere SaleNo=SaleNocreate proc sellChild_delete_procSaleNo intasdelete from sellChildwhere SaleNo=SaleNo/*修改數(shù)據(jù)的存儲過程*/ create proc MedID_update_procMedKindeCode char(10),KindExplanation varchar(12),MedKindeCode1 char(10)asupdate MedID set MedKindeCode=MedKindeCode,KindExplanation=KindExplanation where MedKindeCode=MedKindeCode1exec MedID_update_proc 39。,300/*刪除數(shù)據(jù)的存儲過程*/ create proc MedID_delete_procMedKindeCode char(10)asdelete from MedId where MedKindeCode=MedKindeCodeexec MedID_delete_proc 39。,39。,15,39。,39。,39。,210exec sellChild_proc 39。,39。,14,39。,39。,39。,100exec sellChild_proc 39。,39。,5,39。,39。,39。,1200alter proc sellChild_procSaleNo int,MedicineCode char(6),MedicineName varchar(32),Price Money,Number Int,Uint char(8),Amount Moneyasinsert into sellChild(SaleNo,MedicineCode,MedicineName,Price,Number,Uint,Amount)values(SaleNo,MedicineCode,MedicineName,Price,Number,Uint,Amount)exec sellChild_proc 39。,39。,39。,800exec sellMain_proc 39。,39。,39。,1500exec sellMain_proc 39。,39。,39。,1000exec sellMain_proc 39。,39。,39。create proc sellMain_procSaleNo int,WorkNo char(10),SaleDate DateTime,Amount Moneyasinsert into sellMain(SaleNo,WorkNo,SaleDate,Amount)values(SaleNo,WorkNo,SaleDate,Amount)exec sellMain_proc 39。,39。,39。,39。,39。,39。exec WorkInfor_proc 39。,39。,39。,39。,39。,39。exe