用 insert into a(codename,oper,addtime) values((select codename from b where code=10 and rownum=1),'admin',getdate())
--oracle insert into a(codename,oper,addtime) select codename 'admin',sysdate from b where code=10 and rownum = 1另:你的mssql写法也是错的.应该为: insert into a(codename,oper,addtime) select top 1 codename ,'admin',getdate() from b where code=10
--oracle(3楼我少了个逗号) insert into a(codename,oper,addtime) select codename, 'admin',sysdate from b where code=10 and rownum = 1另:你的mssql写法也是错的.应该为: insert into a(codename,oper,addtime) select top 1 codename ,'admin',getdate() from b where code=10
declare cursor myc is select * from b; remyc myc%rowtype; begin open myc; fetch myc into remyc; insert into a(codename,oper,addtime) values(remyc.codename,'admin',sysdate); close myc; end; 因为游标第一次是放在表中的首记录,也就是 top 1
在oracle中没有与mssql的top对应的语法,但可以使用伪列rownum来达到,例取t表按begindate升序后的第一条记录: select * from (select t.*,rownum rn from t order by t.begindate) where rn<2; 注意:如果在排序之后取,一定要象上面这样写子查询的 oracle在WEB上分页也是使用上述方式来实现
--oracle insert into a(codename,oper,addtime) select codename 'admin',sysdate from b where code=10 and rownum < 2;貌似这样 ROWNUM 好象不支持=号
insert into a(codename,oper,addtime) select codename 'admin',sysdate from b where code=10 and rownum = 1另:你的mssql写法也是错的.应该为:
insert into a(codename,oper,addtime) select top 1 codename ,'admin',getdate() from b where code=10
insert into a(codename,oper,addtime) select codename, 'admin',sysdate from b where code=10 and rownum = 1另:你的mssql写法也是错的.应该为:
insert into a(codename,oper,addtime) select top 1 codename ,'admin',getdate() from b where code=10
cursor myc is select * from b;
remyc myc%rowtype;
begin
open myc;
fetch myc into remyc;
insert into a(codename,oper,addtime) values(remyc.codename,'admin',sysdate);
close myc;
end;
因为游标第一次是放在表中的首记录,也就是 top 1
select * from (select t.*,rownum rn from t order by t.begindate) where rn<2;
注意:如果在排序之后取,一定要象上面这样写子查询的
oracle在WEB上分页也是使用上述方式来实现
insert into a(codename,oper,addtime) select codename 'admin',sysdate from b where code=10 and rownum < 2;貌似这样 ROWNUM 好象不支持=号