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
oracle:
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