数据库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
  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

转载于:https://www.cnblogs.com/noahsky/archive/2008/01/25/1052560.html