这个是用sqlserver2000写的存储过程,现在要改成oracle,请各位大侠帮帮忙啊。
还有在程序里要怎么调用oracle的存储过程呢?
CREATE proc [dbo].[proc_SightInfo]
@start int,@count int,@order varchar(50)
as
declare @sql varchar(200)
set @sql = 'select top '+convert(varchar(20),@count)+' * from sightInfo'
+' where sightId not in (select top '+convert(varchar(20),@start)+' sightId from view_sightInfo order by '+@order+') order by '+@order
exec (@sql)
GO
还有在程序里要怎么调用oracle的存储过程呢?
CREATE proc [dbo].[proc_SightInfo]
@start int,@count int,@order varchar(50)
as
declare @sql varchar(200)
set @sql = 'select top '+convert(varchar(20),@count)+' * from sightInfo'
+' where sightId not in (select top '+convert(varchar(20),@start)+' sightId from view_sightInfo order by '+@order+') order by '+@order
exec (@sql)
GO
解决方案 »
- 如何导出Oracle用户时,导出指定表【test用户中有个t_log表,我想导出除t_log外的所有表】
- 求教一个创建视图的sql
- BCP API是否能将数据导入到Oracle?
- IMP-00037:字符集标记不明 导入DMP数据时报错,为什么?
- 如何减小已经创建表占用的表空间大小?
- 100分,64位数据库 32位客户端 错误12154
- 求C++ OTL 连接oracle11g的字符串
- 在PL/SQL中怎样执行存储过程
- 有关oracle表外键的问题 在线等待... ...
- 给电信作一个系统,每天要生成几万条记录,该怎么存储?
- 获取同一个transaction中的所有SQL
- ORA-16535:CRS is preventing execution of a broker operation
v_sql varchar2(400);
begin
v_sql := 'select * from (select sightinfo.*, rownum rn from sightinfo where sightId not in (select sightid from (select sightid, rownum rn from view_sightInfo order by '||v_order||') where rn <= '||to_char(v_start)||') order by '||v_order||') where rn <= '||to_char(v_count);
dbms_output.put_line(v_sql);
open v_cur for v_sql;
end;
/SQL> declare
2 v_start number := 10;
3 v_count number := 2;
4 v_order varchar2(100) := 'sightid asc';
5 v_cur sys_refcursor;
6 begin
7 proc_sightinfo(v_start, v_count, v_order, v_cur);
8 end;
9 /下面是打印出来的sql
select * from (select sightinfo.*, rownum rn from sightinfo where sightId not in
(select sightid from (select sightid, rownum rn from view_sightInfo order by
sightid asc) where rn <= 10) order by sightid asc) where rn <= 2
CREATE or replace procedure proc_SightInfo (start1 int,count1 int,order1 varchar2 )
as
v_sql varchar2(200);
begin
v_sql := 'select t.* from (select a.*,rownum rm from sightInfo a where rownum<=' ||(start1+count1)||' order by '||order1||') t
where t.rm>'||start1;
execute immediate v_sql;
end;