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 @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
while @@fetch_status = 0
begin
-- 执行的操作
end
close rs
deallocate rs
end
declare
r_cert certrecord % rowtype;
cursor cs is select * from certrecord order by id;
begin
open cs;
loop
fetch cs into r_cert;
exit when cs % notfound; -- 没有数据退出
-- 执行的操作
end loop;
commit ; -- 提交数据
close cs;
end ;
3,查询后赋给单个变量
sqlserver:
select @parameter = t.aa from table
oracle:select t.aa into parameter from table
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:
Code
select c.userid,
a.unitcode,a.crid,a.isck,a.iszk,a.iszt,a.iszh,a.iswbz,
( case
when ( select t.rylx from t_caijirecord t where t.crid = a.crid) = 0 then 1
else 0
end ) iscjk,
( case
when ( select t.rylx from t_caijirecord t where t.crid = a.crid) = 1 then 1
else 0
end ) isczk,
c.idcardnum,c.receivetime
from certrecord c inner join
a_certrecord a on c.id = a.crid
SqlServer
SELECT
UserPassportID as PassportID,
UserID,
GrantorID,
Case ObjectTypeID
When 1 then ObjectID
End
as TerminalID,
Type, Flag, GrantDate, Validate, Invalidate
From UserPassport
Access:
Access
select OperationNo,iif(IsMadeFile = 1 , ' sended ' ,iif(IsMadeFile = 0 , ' nosend ' )) as IsSend from T_OperationHistory
select OperationNo,switch(IsMadeFile = 1 , ' sended ' ,IsMadeFile = 0 , ' nosend ' ) as IsSend from T_OperationHistory
查看更多关于数据库Oracle与SqlServer与Access的详细内容...