【正文】 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ǔ)過程實(shí)現(xiàn)單表查詢*/ /*建立名為“單表查詢1”的存儲(chǔ)過程，用來查詢某種藥品的信息*/ create proc 單表查詢1@MedicineCode char(6)ASselect *from MedInfor where MedicineCode=@MedicineCode /*建立名為“單表查詢2”的存儲(chǔ)過程，用來查詢某個(gè)客戶的信息*/ create proc 單表查詢2@GuestCode char(10)ASselect *from GueInfor where GuestCode=@GuestCode /*建立名為“單表查詢3”的存儲(chǔ)過程，用來查詢某個(gè)員工的信息*/ create proc 單表查詢3@WorkNo char(10)ASselect *from WorkInfor where WorkNo=@WorkNo /*建立名為“單表查詢4”的存儲(chǔ)過程，用來查詢某個(gè)供應(yīng)商的信息*/ create proc 單表查詢4@FirmCode char(10)ASselect *from FirmInfor where FirmCode=@FirmCode /*建立名為“單表查詢5”的存儲(chǔ)過程，用來查詢某個(gè)藥品代碼對應(yīng)的藥品類型的信息*/ create proc 單表查詢5@MedKindeCode char(10)ASselect *from MedID where MedKindeCode=@MedKindeCode/*建立存儲(chǔ)過程實(shí)現(xiàn)連接查詢*/ /*建立名為“連接查詢1”的存儲(chǔ)過程，用來查詢某個(gè)藥品名稱對應(yīng)的藥品類型的信息*/ create proc 連接查詢1@MedicineName varchar(8)asselect MedicineName ,KindExplanationfrom MedInfor,MedIDwhere = and MedicineName=@MedicineName/*建立名為“連接查詢2”的存儲(chǔ)過程，用來查詢某個(gè)供應(yīng)商提供的藥品類型*/ create proc 連接查詢2@FirmName varchar(16)asselect FirmName,KindExplanationfrom MedInfor,MedID,FirmInforwhere = and = and FirmName=@FirmName/*建立名為“連接查詢3”的存儲(chǔ)過程，用來查詢某個(gè)銷售員銷售某種藥品的數(shù)量*/create proc 連接查詢3@Name varchar(12),@MedicineName varchar(8)asselect name ,from WorkInfor,sellChild,MedInfor,sellMainwhere = and = and = and name=@Name and =@MedicineName/*建立名為“連接查詢4”的存儲(chǔ)過程，用來查詢某類藥品的銷售量*/create proc 連接查詢4@KindExplanation varchar(12)asselect KindExplanation,from sellChild,MedID,MedInfor where = and = and KindExplanation=@KindExplanation/*建立名為“連接查詢5”的存儲(chǔ)過程，用來查詢某個(gè)員工銷售的藥品類型*/create proc 連接查詢5@Name varchar(12)asselect Name,KindExplanationfrom sellChild,MedID,MedInfor,sellMain,WorkInfor where = and = and = and = and Name=@Name建立存儲(chǔ)過程實(shí)現(xiàn)嵌套查詢/*建立名為“嵌套查詢1”的存儲(chǔ)過錯(cuò)，用來查詢某類藥品的銷售量*/create proc 嵌套查詢1@KindExplanation varchar(12)asselect Number from sellChild where MedicineCode In(select MedicineCode from MedID where KindExplanation=@KindExplanation)/*建立名為“嵌套查詢2”的存儲(chǔ)過錯(cuò)，用來查詢某個(gè)供應(yīng)商提供的商品*/create proc 嵌套查詢2@FirmName varchar(16)asselect MedicineName from MedInfor where FirmCode In (select FirmCode from FirmInfor where FirmName=@FirmName)/*建立存儲(chǔ)過程實(shí)現(xiàn)集合查詢*//*建立名為“集合查詢1”的存儲(chǔ)過錯(cuò)，用來查詢提供某類商品的供應(yīng)商數(shù)*/create proc 集合查詢1@MedicineName varchar(8)asselect avg(FirmCode) from FrimInfor where FirmCode in(select FirmCode from MedInfor,FrimInfor where = and MedicineName=@MedicineName)/*建立名為“集合查詢2”的存儲(chǔ)過錯(cuò)，用來查詢藥品種類數(shù)*/create proc 集合查詢2asselect count(MedKindeCode) from MedID/*視圖建立*//*1，由藥品類別表“MedID”建立一個(gè)視圖，該視圖由藥品類別表的所有列構(gòu)成*/create view 藥品類別(藥品類別代碼,類別說明)asselect* from MedID/*2，由客戶信息表“GueInfor”建立一個(gè)視圖，該視圖由客戶信息表的所有列構(gòu)成*/create view 客戶信息(客戶編碼,客戶名稱,聯(lián)系人,聯(lián)系電話,所在城市)asselect* from GueInfor/*3，由供應(yīng)商信息表“FirmInfor”建立一個(gè)視圖，該視圖由供應(yīng)商信息表的所有列構(gòu)成*/create view 供應(yīng)商信息(供應(yīng)商編碼,供應(yī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 transactionend.（.....）成立于2004年，專注于企業(yè)管理培訓(xùn)。提供60萬企業(yè)管理資料下載，詳情查看：...../提供5萬集管理視頻課程下載，詳情查看：...../zz/提供2萬GB高清管理視頻課程硬盤拷貝，詳情查看：...../shop/2萬GB高清管理視頻課程目錄下載：...../高清課程可提供免費(fèi)體驗(yàn)，如有需要請于我們聯(lián)系。咨詢電話：020.值班手機(jī)：.網(wǎng)站網(wǎng)址：....