欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  数据库

数据库Oracle与SqlServer与Access

程序员文章站 2022-05-21 17:33:35
...

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:

数据库Oracle与SqlServer与Accessdeclare @id int
数据库Oracle与SqlServer与Access
declare rs cursor for select PdaId from Biz_Pda where PdaState='1' or PdaState='2'
数据库Oracle与SqlServer与Access
if @iPda=1
数据库Oracle与SqlServer与Access
begin
数据库Oracle与SqlServer与Access
open rs
数据库Oracle与SqlServer与Access
fetch next from rs
数据库Oracle与SqlServer与Access
into @id
数据库Oracle与SqlServer与Access
while @@fetch_status=0
数据库Oracle与SqlServer与Access
begin
数据库Oracle与SqlServer与Access
--执行的操作
数据库Oracle与SqlServer与Access
end
数据库Oracle与SqlServer与Access
close rs
数据库Oracle与SqlServer与Access
deallocate rs
数据库Oracle与SqlServer与Access
end

oracle:

数据库Oracle与SqlServer与Accessdeclare
数据库Oracle与SqlServer与Access r_cert certrecord
%rowtype;
数据库Oracle与SqlServer与Access
cursor cs is select * from certrecord order by id;
数据库Oracle与SqlServer与Access
begin
数据库Oracle与SqlServer与Access
open cs;
数据库Oracle与SqlServer与Access loop
数据库Oracle与SqlServer与Access
fetch cs into r_cert;
数据库Oracle与SqlServer与Access
exit when cs%notfound;--没有数据退出
数据库Oracle与SqlServer与Access
--执行的操作
数据库Oracle与SqlServer与Access
end loop;
数据库Oracle与SqlServer与Access
commit;--提交数据
数据库Oracle与SqlServer与Access
close cs;
数据库Oracle与SqlServer与Access
end;


3,查询后赋给单个变量
sqlserver:

数据库Oracle与SqlServer与Accessselect @parameter=t.aa from table

oracle:

数据库Oracle与SqlServer与Accessselect 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:

数据库Oracle与SqlServer与Access数据库Oracle与SqlServer与AccessCode
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

数据库Oracle与SqlServer与Access数据库Oracle与SqlServer与AccessSqlServer
SELECT
UserPassportID
as PassportID,
UserID,
GrantorID,
Case ObjectTypeID
When 1 then ObjectID
End
as TerminalID,
Type, Flag, GrantDate, Validate, Invalidate
From UserPassport

Access:

数据库Oracle与SqlServer与Access数据库Oracle与SqlServer与AccessAccess
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