【正文】 GLink,GLinkTell=@GLinkTell,City=@Citywhere GuestCode=@GuestCodecreate proc FirmInfor_update_proc@FirmCode1 char(10),@FirmName varchar(16),@Link varchar(12),@LinkTell varchar(11),@City varchar(8),@FirmCode char(10)asupdate FirmInforset FirmCode=@FirmCode1,FirmName=@FirmName,Link=@Link,LinkTell=@LinkTell,City=@Citywhere FirmCode=@FirmCodecreate proc WorkInfor_update_proc@WorkNo1 char(10),@Name varchar(12),@UserRegName char(6),@Password char(10),@Position char(10),@Power Int,@WorkNo char(10)asupdate WorkInforset WorkNo=@WorkNo1,Name=@Name,UserRegName=@UserRegName,Password=@Password,Position=@Position,Power=@Powerwhere WorkNo=@WorkNocreate proc sellMain_update_proc@SaleNo1 int,@WorkNo char(10),@SaleDate DateTime,@Amount Money,@SaleNo intasupdate sellMainset SaleNo=@SaleNo1,WorkNo=@WorkNo,SaleDate=@SaleDate,Amount=@Amountwhere SaleNo=@SaleNocreate proc sellChild_update_proc@SaleNo1 int,@MedicineCode char(6),@MedicineName varchar(32),@Price Money,@Number Int,@Uint char(8),@Amount Money,@SaleNo intasupdate sellChildset SaleNo=@SaleNo1,MedicineCode=@MedicineCode,MedicineName=@MedicineName,Price=@Price,Number=@Number,Amount=@Amountwhere SaleNo=@SaleNo/*建立存儲(chǔ)過(guò)程實(shí)現(xiàn)單表查詢(xún)*/ /*建立名為“單表查詢(xún)1”的存儲(chǔ)過(guò)程，用來(lái)查詢(xún)某種藥品的信息*/ create proc 單表查詢(xún)1@MedicineCode char(6)ASselect *from MedInfor where MedicineCode=@MedicineCode /*建立名為“單表查詢(xún)2”的存儲(chǔ)過(guò)程，用來(lái)查詢(xún)某個(gè)客戶的信息*/ create proc 單表查詢(xún)2@GuestCode char(10)ASselect *from GueInfor where GuestCode=@GuestCode /*建立名為“單表查詢(xún)3”的存儲(chǔ)過(guò)程，用來(lái)查詢(xún)某個(gè)員工的信息*/ create proc 單表查詢(xún)3@WorkNo char(10)ASselect *from WorkInfor where WorkNo=@WorkNo /*建立名為“單表查詢(xún)4”的存儲(chǔ)過(guò)程，用來(lái)查詢(xún)某個(gè)供應(yīng)商的信息*/ create proc 單表查詢(xún)4@FirmCode char(10)ASselect *from FirmInfor where FirmCode=@FirmCode /*建立名為“單表查詢(xún)5”的存儲(chǔ)過(guò)程，用來(lái)查詢(xún)某個(gè)藥品代碼對(duì)應(yīng)的藥品類(lèi)型的信息*/ create proc 單表查詢(xún)5@MedKindeCode char(10)ASselect *from MedID where MedKindeCode=@MedKindeCode/*建立存儲(chǔ)過(guò)程實(shí)現(xiàn)連接查詢(xún)*/ /*建立名為“連接查詢(xún)1”的存儲(chǔ)過(guò)程，用來(lái)查詢(xún)某個(gè)藥品名稱(chēng)對(duì)應(yīng)的藥品類(lèi)型的信息*/ create proc 連接查詢(xún)1@MedicineName varchar(8)asselect MedicineName ,KindExplanationfrom MedInfor,MedIDwhere = and MedicineName=@MedicineName/*建立名為“連接查詢(xún)2”的存儲(chǔ)過(guò)程，用來(lái)查詢(xún)某個(gè)供應(yīng)商提供的藥品類(lèi)型*/ create proc 連接查詢(xún)2@FirmName varchar(16)asselect FirmName,KindExplanationfrom MedInfor,MedID,FirmInforwhere = and = and FirmName=@FirmName/*建立名為“連接查詢(xún)3”的存儲(chǔ)過(guò)程，用來(lái)查詢(xún)某個(gè)銷(xiāo)售員銷(xiāo)售某種藥品的數(shù)量*/create proc 連接查詢(xún)3@Name varchar(12),@MedicineName varchar(8)asselect name ,from WorkInfor,sellChild,MedInfor,sellMainwhere = and = and = and name=@Name and =@MedicineName/*建立名為“連接查詢(xún)4”的存儲(chǔ)過(guò)程，用來(lái)查詢(xún)某類(lèi)藥品的銷(xiāo)售量*/create proc 連接查詢(xún)4@KindExplanation varchar(12)asselect KindExplanation,from sellChild,MedID,MedInfor where = and = and KindExplanation=@KindExplanation/*建立名為“連接查詢(xún)5”的存儲(chǔ)過(guò)程，用來(lái)查詢(xún)某個(gè)員工銷(xiāo)售的藥品類(lèi)型*/create proc 連接查詢(xún)5@Name varchar(12)asselect Name,KindExplanationfrom sellChild,MedID,MedInfor,sellMain,WorkInfor where = and = and = and = and Name=@Name建立存儲(chǔ)過(guò)程實(shí)現(xiàn)嵌套查詢(xún)/*建立名為“嵌套查詢(xún)1”的存儲(chǔ)過(guò)錯(cuò)，用來(lái)查詢(xún)某類(lèi)藥品的銷(xiāo)售量*/create proc 嵌套查詢(xún)1@KindExplanation varchar(12)asselect Number from sellChild where MedicineCode In(select MedicineCode from MedID where KindExplanation=@KindExplanation)/*建立名為“嵌套查詢(xún)2”的存儲(chǔ)過(guò)錯(cuò)，用來(lái)查詢(xún)某個(gè)供應(yīng)商提供的商品*/create proc 嵌套查詢(xún)2@FirmName varchar(16)asselect MedicineName from MedInfor where FirmCode In (select FirmCode from FirmInfor where FirmName=@FirmName)/*建立存儲(chǔ)過(guò)程實(shí)現(xiàn)集合查詢(xún)*//*建立名為“集合查詢(xún)1”的存儲(chǔ)過(guò)錯(cuò)，用來(lái)查詢(xún)提供某類(lèi)商品的供應(yīng)商數(shù)*/create proc 集合查詢(xún)1@MedicineName varchar(8)asselect avg(FirmCode) from FrimInfor where FirmCode in(select FirmCode from MedInfor,FrimInfor where = and MedicineName=@MedicineName)/*建立名為“集合查詢(xún)2”的存儲(chǔ)過(guò)錯(cuò)，用來(lái)查詢(xún)藥品種類(lèi)數(shù)*/create proc 集合查詢(xún)2asselect count(MedKindeCode) from MedID/*視圖建立*//*1，由藥品類(lèi)別表“MedID”建立一個(gè)視圖，該視圖由藥品類(lèi)別表的所有列構(gòu)成*/create view 藥品類(lèi)別(藥品類(lèi)別代碼,類(lèi)別說(shuō)明)asselect* from MedID/*2，由客戶信息表“GueInfor”建立一個(gè)視圖，該視圖由客戶信息表的所有列構(gòu)成*/create view 客戶信息(客戶編碼,客戶名稱(chēng),聯(lián)系人,聯(lián)系電話,所在城市)asselect* from GueInfor/*3，由供應(yīng)商信息表“FirmInfor”建立一個(gè)視圖，該視圖由供應(yīng)商信息表的所有列構(gòu)成*/create view 供應(yīng)商信息(供應(yīng)商編碼,供應(yīng)商名稱(chēng),聯(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。you can not modify this column39。,16,1)rollback transactione