组合查询的时候就遇到了存储进程,那时候觉得高大上,但是仔细1看,分为俩部份,第1部份是参数,第2部份是sql语句。在结账和下机的时候遇到了对多个表的增删改查,学习了1下,觉得对1些简单的存储进程也有点得心应手。
存储进程概况:
存储进程(Stored Procedure)是1种在数据库中存储复杂程序,以便能在外部程序调用的1种数据库对象,他可以视为数据库中的1种函数或子程序。说的明白点,也就是对很多条的sql函数进行了封装,然后在我们的重构D层进行了调用。更加的增强了sql语言的灵活性和利用性。
要写存储进程了,我们了解1下存储进程的格式
create procedure sp_name
@[参数名] [类型],@[参数名] [类型]
as
begin
………
end
详细的了解,请查看超链接http://baike.haosou.com/doc/2045557⑵164405.html
存储进程小试:
我们在结账的时候,点击结账按钮,我们要对student_info,recharge_info,cancelcash_info的状态进行更新,还要对Daycheck_info表进行插入。所以用存储进程可以很轻松的解决这多的sql语言的问题。
新建存储进程:
存储进程――右击新建存储进程。然后在数据库的右面打开了存储进程的代码框,就在里面写代码啦。我们写的代码应当是CREATE PROCEDURE,然落后行履行,然后保存存储进程,对存储进程命名,关闭sql server,重新打开,就会看到我们写的代码,在结账的单击事件下,我写的代码具体以下:
— Author: <李芬>
— Create date: <Create Date,,>
— Description: <Description,,>
— =============================================
ALTER PROCEDURE[dbo].[proc_AlterAccount]
— Add the parameters for the stored procedure here
@userID varchar(10),
@RemainCash nchar(10),
@RechargeCash nchar(10),
@ConsumeCash nchar(10),
@CancelCsh nchar(10),
@AllCash nchar(10),
@Date nchar(50)
AS
BEGIN
update student_info set ischeck ='已结账' where userID =@userID and ischeck='未结账'
update recharge_info set Status ='已结账' where User_ID =@userID and Status='未结账'
update cancelcash_info set statue ='已结账' where UserID =@userID and statue='未结账'
select * from Daycheck_info where Date =@Date
insert into Daycheck_info values (@RemainCash ,@RechargeCash ,@ConsumeCash ,@CancelCsh,@AllCash,@userID,@Date )
END
</span>
在这段代码中,我们可以很清楚的看到存储进程的结构,同时在最后1个参数的后面,也就是as的前面是没有那个逗号的,写的时候注意。重新打开就出现了下面我的存储进程啦。
D层调用存储进程:
''' 对结账的单击事情
''' </summary>
''' <param name="Eaccount"></param>
''' <returns></returns>
''' <remarks></remarks>
Public Function Iaccount(Eaccount As DayCheckinfo) As Boolean Implements Iaccount.Iaccount
Dim strSql As String
Dim Erecharge As New Entity.Rechargeinfo
strSql = "proc_AlterAccount"
Dim paras As SqlParameter() = {New SqlParameter("@userID", Eaccount.UserID),
New SqlParameter("@RemainCash", Eaccount.RemainCash),
New SqlParameter("@RechargeCash", Eaccount.RechargeCash),
New SqlParameter("@ConsumeCash", Eaccount.ConsumeCash),
New SqlParameter("@CancelCsh", Eaccount.CancelCash),
New SqlParameter("@AllCash", Eaccount.AllCash),
New SqlParameter("@Date", Eaccount.DDate)}
Dim dt As Boolean
dt = Sqlhelper.SqlHelper.ExecuteNoQuery(strSql, CommandType.StoredProcedure, paras)
Return dt
End Function</span>
由于我的sqlhelp仅仅封装了4个方法,分别为查询(有参数、没有参数),非查询(有参数、没有参数),所以在写存储进程的时候,要把查询的写成1个存储进程,非查询的写成1个,不要混了,避免出现没法调用sqlhelp方法的问题。
甚么新事物,都看着很难,因而不去做,还走老路。因而就和时期脱节啦。却不知看着难的事情做起来不1定难,甚么都需要1个进程。加油。。。。。
波比源码 » 存储过程小试――结账
order generic levaquin 250mg levofloxacin 500mg us
avodart 0.5mg tablet buy flomax 0.2mg online cheap brand zofran 4mg
aldactone pills valtrex 1000mg cheap fluconazole 100mg pills
ampicillin 250mg brand erythromycin brand purchase erythromycin pill
fildena sale brand bimatoprost order methocarbamol 500mg for sale
order suhagra 50mg pill order estradiol without prescription order estradiol 1mg pill
buy lamotrigine online cheap prazosin order purchase retin
generic tadalis 10mg purchase tadalis for sale order voltaren sale
accutane 20mg generic order isotretinoin 20mg generic buy zithromax 250mg
indocin 75mg drug indocin 75mg pills order amoxicillin 250mg for sale
tadalafil over counter male dysfunction sildenafil 25 mg
arimidex pills Buy branded viagra buy generic viagra
purchase deltasone without prescription buying drugs from canada sildenafil india
order accutane 20mg pill zithromax 500mg usa purchase stromectol
order doxycycline sale clomiphene drug purchase furosemide sale
order generic catapres 0.1 mg antivert 25mg pills tiotropium bromide 9 mcg pill
fosamax without prescription order ibuprofen pill order pepcid 20mg online cheap
purchase tacrolimus generic buy labetalol 100mg for sale urso 150mg price
brand imdur 20mg buy generic lanoxin 2250 mg purchase micardis
purchase bupropion buy zyban quetiapine 50mg drug
molnupiravir price lansoprazole 15mg over the counter prevacid brand
order zoloft 50mg generic Pharmacy viagra order viagra 50mg
oral imuran sildenafil 50mg for sale viagra in usa
order naltrexone 50 mg generic cheap aripiprazole buy aripiprazole 20mg online
purchase luvox generic cymbalta 40mg cost order glucotrol 5mg pills
purchase zithromax without prescription cost gabapentin 100mg cheap neurontin 600mg
buy cialis 5mg online order clomipramine 50mg generic anafranil drug
order itraconazole 100mg online cheap buy itraconazole 100 mg without prescription buy tinidazole 300mg without prescription
order glucophage 500mg for sale buy tadalafil 5mg buy generic tadalafil 40mg
olanzapine 10mg generic diovan order buy diovan generic
order amlodipine without prescription cialis next day tadalafil oral
buy generic prilosec 20mg essay helper casino games online
order generic metoprolol 100mg order lopressor 100mg pills vardenafil without prescription
order levitra 10mg for sale buy levitra online cheap medrol 16 mg pills
write essay service viagra order online real viagra 100mg
buy triamcinolone 4mg sale purchase loratadine pills purchase clarinex
order dapoxetine 90mg cytotec 200mcg ca synthroid 150mcg without prescription
xenical 60mg cost buy xenical 120mg online order zovirax 400mg
order zyloprim 100mg online cheap purchase ezetimibe without prescription purchase ezetimibe sale
order domperidone 10mg sale buy domperidone without prescription flexeril uk
cozaar cost cost cozaar topamax 100mg uk
generic imitrex 25mg avodart us brand dutasteride
colchicine usa gloperba drug best online poker sites
oral zantac 300mg celecoxib 200mg brand buy celecoxib 200mg pill
cialis 20mg pills order cialis 5mg ciprofloxacin 1000mg without prescription
order generic flagyl 400mg buy augmentin 375mg pill cost sulfamethoxazole
price of cialis brand cialis 10mg brand name viagra
order desyrel 100mg buy desyrel 100mg generic sildenafil 100mg usa
sildenafil pills 200mg oral lamotrigine 200mg buy lamictal 200mg without prescription
casino slots gambling modafinil 200mg us order provigil generic
brand prednisone 20mg minipress 1mg over the counter buy vermox generic
purchase fildena generic order budesonide without prescription order rhinocort sale