SQL语句转换成orcle语句的问题！
如题，
目前工作需要使用的SQL处理程序，大体步骤是利用sql的外部bat命令将ACCESS数据库导入sql数据库，进行处理后转出到excle文件。我想转换成orcle能使用的，请帮忙看看如何修改呢
语句如下：
use work
SELECT s1,s2,s3,s4,s5,s6,s7,s10,s11
INTO t1
FROM OPENDATASOURCE ('Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\c.mdb";User ID=Admin;Password=' )...t1 where s10 like '20120202%'
go
use work
SELECT *
INTO t2
FROM OPENDATASOURCE ('Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\c.mdb";User ID=Admin;Password=' )...t2
where rq>'2012-2-2 00:00:00.000'and rq<'2012-2-2 23:59:59.000'
/*注意此处如果日期为单数,那么前面不用加0,比如2009-8-9*/
go
use work
insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 5.0;HDR=YES;DATABASE=c:\证书查询数据.xls',sheet1$)(单位名称,联系人,联系电话,查询单位,查询时间)
select sqz,lxr,lxdh,cxdw,rq from work..t2
go
use work
SELECT *
INTO doublecert_update_policy
FROM OPENDATASOURCE ('Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\c.mdb";User ID=Admin;Password=' )...doublecert_update_policy
go
use work
SELECT *
INTO bill
FROM OPENDATASOURCE ('Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\c.mdb";User ID=Admin;Password=' )...bill
go
use work
select sn,count(sn) as numcf into cftbl
from bill
group by sn
having count(sn)>1
order by sn
use work
SELECT substring(s10,15,32)as sn32,t1.* into tbl
FROM t1
go
use work
alter table tbl
add payamount float null
go
use work
update tbl set payamount =
(
select top 1 payamount from bill
where tbl.sn32=bill.sn
)
go
use work
select * into cfcheck
from tbl join cftbl
on tbl.sn32=cftbl.sn
go
use work
SELECT tbl.s1,s2,s3,s4,s5,s6,s7,s10,payamount into tbl01
FROM tbl
where tbl.s11 like '%yes%'
go
use work
alter table tbl01
add cert_status nvarchar(255) null
go
use work
alter table tbl01
add signcertsn nvarchar(255) null
go
use work
alter table tbl01
add newcertsn nvarchar(255) null
go
use work
update tbl01 set newcertsn=
(
select top 1 newcertsn from doublecert_update_policy
where tbl01.s7=doublecert_update_policy.signcertsn
)
go
use work
update tbl01 set signcertsn =
(
select top 1 signcertsn from doublecert_update_policy
where tbl01.s7=doublecert_update_policy.signcertsn
)
go
use work
update work..tbl01 set cert_status =
(
select top 1 cert_status from ra..cert
where work..tbl01.newcertsn=ra..cert.certsn
)
go
use work
alter table tbl01
add signcertsnisnull nvarchar(255) null
go
use work
update tbl01 set signcertsnisnull='yes'
from tbl01
where tbl01.signcertsn is null and newcertsn is null
go
use work
select * into tbl03
from tbl01
where tbl01.newcertsn is null
go
use work
alter table tbl03
add countofupdatedays decimal(9) null
go
use work
alter table tbl03
add updateflag decimal(9) null
go
use work
update tbl03 set countofupdatedays='396'
go
use work
update tbl03 set updateflag='1'
go
use work
INSERT INTO ra..doublecert_update_policy (signcertsn,countofupdatedays,updateflag)
SELECT s7,countofupdatedays,updateflag
FROM tbl03
where work..tbl03.signcertsnisnull='yes'
go
use work
insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 5.0;HDR=YES;DATABASE=c:\网上数据表单.xls',tbl01$)(单位名称,微机编码,寄送发票地址,邮政编码,收票人姓名,收票人电话,订单号,payamount,签名序列号,newcertsn,证书状态)
select s1,s2,s3,s4,s5,s6,s10,payamount,s7,newcertsn,cert_status from work..tbl01
go
use work
insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 5.0;HDR=YES;DATABASE=c:\网上已缴费未成功单.xls',tbl03$)(单位名称,微机编码,寄送发票地址,邮政编码,收票人姓名,收票人电话,订单号,newcertsn,payamount,签名序列号,signcertsnisnull)
select s1,s2,s3,s4,s5,s6,s10,newcertsn,payamount,s7,signcertsnisnull from work..tbl03
go
if exists( select 1 from work..cfcheck)
begin
EXEC master..xp_cmdshell 'bcp "SELECT * FROM work..cfcheck" queryout C:\重复信息1111111.xls -c -q -E'
end
use work
select
(select substring(max(s10),1,8) from tbl01) as c1,
(select count(cxdw) from t2 ) as c2,
(select count(s1) from tbl01 ) as c3,
(select count(s1) from tbl01 )-(select count(s1) from tbl03) as c4,
(select count(s1) from tbl01 where payamount like '%260%') as c5,
(select count(s1) from tbl03) as c6
into t33
go
use work
insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 5.0;HDR=YES;DATABASE=c:\网上更新统计工作表.xls',sheet2$)(日期,证书查询数量,网上已缴费用户数量,实际缴费并更新的用户数量,包含社保更新用户数量,缴费但未更新用户数量)
select c1,c2,c3,c4,c5,c6 from work..t33
go
/*use work
drop table tbl
drop table doublecert_update_policy
drop table tbl01
drop table tbl03
drop table t1
drop table t2
drop table t33
drop table bill
drop table cfcheck
drop table cftbl
go*/
[解决办法]
你的代码是SQL SERVER的代码，你要转成ORACLE的代码要根据ORACLE的语法的规则进行转换。一般的SQL语言如果你是遵从SQL 1999或SQL 2006标准的就不用转换，可以直接使用于ORACLE。
use workSELECT s1,s2,s3,s4,s5,s6,s7,s10,s11INTO t1FROM OPENDATASOURCE ('Microsoft.Jet.OLEDB.4.0', 'Data Source="c:\c.mdb";User ID=Admin;Password=' )...t1 where s10 like '20120202%'gouse workSELECT *INTO t2FROM OPENDATASOURCE ('Microsoft.Jet.OLEDB.4.0', 'Data Source="c:\c.mdb";User ID=Admin;Password=' )...t2 where rq>'2012-2-2 00:00:00.000'and rq<'2012-2-2 23:59:59.000'/*注意此处如果日期为单数,那么前面不用加0,比如2009-8-9*/gouse workinsert into OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 5.0;HDR=YES;DATABASE=c:\证书查询数据.xls',sheet1$)(单位名称,联系人,联系电话,查询单位,查询时间)select sqz,lxr,lxdh,cxdw,rq from work..t2go
[解决办法]
你这里面涉及到跨库操作,在oracle里面是针对用户操作.