access oracle mysql数据库教程_数据库Oracle与SqlServer与Access

1,时间: Access:now() SqlServer:getdate() oracle:sysdate() 2,游标： sqlserver: declare @id int declare rs cursor for select PdaId from Biz_Pda where PdaState = ' 1 ' or PdaState = ' 2 ' if @iPda = 1 begin open rs fetch next from rs into @id

1,时间:

Access:now()

SqlServer:getdate()

oracle:sysdate()

2,游标：

sqlserver:

declare@idintdeclarerscursorforselectPdaIdfromBiz_PdawherePdaState='1'orPdaState='2'if@iPda=1beginopenrs

fetchnextfromrs

into@idwhile@@fetch_status=0begin--执行的操作endclosers

deallocaters

end

oracle:

declare r_cert certrecord%rowtype;

cursorcsisselect*fromcertrecordorderbyid;

beginopencs;

loop

fetchcsintor_cert;

exitwhencs%notfound;--没有数据退出--执行的操作endloop;

commit;--提交数据closecs;

end;

3,查询后赋给单个变量

sqlserver:

select@parameter=t.aafromtable

oracle:

selectt.aaintoparameterfromtable

4,函数：

字符转换：

sqlserver:str(..)

oracle:to_char(..)

access:cstr(..)

字符截取：([]为可选)

sqlserver:substring(s,start,length)

oracle:substr(s,start[,length])

access:Mid(s,start[,length])

注：sqlserver和oracle中start可从0或1开始，二者结果是一样的，access只能从1开始

5,case

在oracle,sqlserver都有case语句：

oracle:

Codeselectc.userid,

a.unitcode,a.crid,a.isck,a.iszk,a.iszt,a.iszh,a.iswbz,

(casewhen(selectt.rylxfromt_caijirecord twheret.crid=a.crid)=0then1else0end) iscjk,

(casewhen(selectt.rylxfromt_caijirecord twheret.crid=a.crid)=1then1else0end) isczk,

c.idcardnum,c.receivetimefromcertrecord cinnerjoina_certrecord aonc.id=a.crid

SqlServerSELECTUserPassportIDasPassportID,

UserID,

GrantorID,CaseObjectTypeIDWhen1thenObjectIDEndasTerminalID,

Type, Flag, GrantDate, Validate, InvalidateFromUserPassport

Access:

AccessselectOperationNo,iif(IsMadeFile=1,'sended',iif(IsMadeFile=0,'nosend'))asIsSendfromT_OperationHistoryselectOperationNo,switch(IsMadeFile=1,'sended',IsMadeFile=0,'nosend')asIsSendfromT_OperationHistory

本文原创发布php中文网，转载请注明出处，感谢您的尊重！