这个是用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的to_date函数问题?
- 关于OCIServerAttach接口的问题
- 求一存储过程的写法(简单逻辑处理)
- 请帮忙写个触发器:每分钟、从视图中提取一记录写入表,谢谢!分不够了,谢谢
- 救命啊!关于查询的问题!
- 表中记录仅7万余条,日增不超过350条,但前台应用程序还是慢!!小妹急等!!
- 在windows 2000 server 安装Oracle9i 企业版,但每次连接速度很慢,不知为什么?(我的机器配置很高,不应该是性能问题)
- Oracle中,使用hash建立分区的表,如何知道数据在哪一个分区?
- Hibernate 报错: org.hibernate.HibernateException: Errors in named queries
- 获取同一个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;