CREATE PROCEDURE [dbo].[sp_paging] (
@Tname varchar(255),/*表名*/
@kname varchar(255),/*主键名*/
@columns varchar(500),/*select的列名*/
@whereText varChar(1000),/*查询条件*/
@orderText varChar(100),/*排序条件*/
@startIndex int,/*取的数目开始值*/
@endIndex int/*取的数目的结束值*/
)
AS
BEGIN
SET NOCOUNT ON;
declare @sqlstring varchar(1500)
set rowcount @endIndex
DECLARE @tableid varchar(20)
DECLARE @idType varchar(20)
select @tableid=id from sysobjects where name=@Tname
select @idType=xtype from sysColumns where id=@tableid and LOWER(name)='id'
/*如果主键Id为varchar时,则临时表的nid为varchar,否则为int*/
if @idType='167'
begin
/*得到一张有顺序Id的临时表,用户分页*/
select @sqlstring = 'declare @indextable table(id int identity(1,1),nid varchar(50))
insert into @indextable(nid)
select '+@kname+' from '+@Tname+' where '+@whereText+' order by '+@orderText
+' select '+@columns+' from '+@Tname+' t, @indextable o where t.'+@kname+' =o.nid
and o.id between '+convert(varchar,@startIndex)
+' and '+convert(varchar,@endIndex) +' order by o.id'
end
else
begin
select @sqlstring = 'declare @indextable table(id int identity(1,1),nid int)
insert into @indextable(nid)
select '+@kname+' from '+@Tname+' where '+@whereText+' order by '+@orderText
+' select '+@columns+' from '+@Tname+' t, @indextable o where t.'+@kname+' =o.nid
and o.id between '+convert(varchar,@startIndex)
+' and '+convert(varchar,@endIndex) +' order by o.id'
end
exec(@sqlstring) SET NOCOUNT OFF;
END
@Tname varchar(255),/*表名*/
@kname varchar(255),/*主键名*/
@columns varchar(500),/*select的列名*/
@whereText varChar(1000),/*查询条件*/
@orderText varChar(100),/*排序条件*/
@startIndex int,/*取的数目开始值*/
@endIndex int/*取的数目的结束值*/
)
AS
BEGIN
SET NOCOUNT ON;
declare @sqlstring varchar(1500)
set rowcount @endIndex
DECLARE @tableid varchar(20)
DECLARE @idType varchar(20)
select @tableid=id from sysobjects where name=@Tname
select @idType=xtype from sysColumns where id=@tableid and LOWER(name)='id'
/*如果主键Id为varchar时,则临时表的nid为varchar,否则为int*/
if @idType='167'
begin
/*得到一张有顺序Id的临时表,用户分页*/
select @sqlstring = 'declare @indextable table(id int identity(1,1),nid varchar(50))
insert into @indextable(nid)
select '+@kname+' from '+@Tname+' where '+@whereText+' order by '+@orderText
+' select '+@columns+' from '+@Tname+' t, @indextable o where t.'+@kname+' =o.nid
and o.id between '+convert(varchar,@startIndex)
+' and '+convert(varchar,@endIndex) +' order by o.id'
end
else
begin
select @sqlstring = 'declare @indextable table(id int identity(1,1),nid int)
insert into @indextable(nid)
select '+@kname+' from '+@Tname+' where '+@whereText+' order by '+@orderText
+' select '+@columns+' from '+@Tname+' t, @indextable o where t.'+@kname+' =o.nid
and o.id between '+convert(varchar,@startIndex)
+' and '+convert(varchar,@endIndex) +' order by o.id'
end
exec(@sqlstring) SET NOCOUNT OFF;
END
解决方案 »
- oracle数据导出遇到的问题(从linux向window导)
- sum中出现then 1 else 0的的含义
- sql oracle 表连接动态条件差异问题
- 一个复杂ORACLE查询
- 数据库崩溃恢复后报错:ORA-01086 savepoint 'beginaction' never established
- 新手提问 关于 Oracle 导入导出
- 我找oracle软件,谁有下载。
- SQL文的where中有哪些特殊字符需要做处理?
- 不通过Oracle Client安装程序如何实现ODBC驱动/SqlNet的安装和配置?急...分可加...来者有分
- 关于ORACLE8.17的启动问题???????????????????
- 一个临时表查询问题:invalid use of an aggegate function
- ORACLE字符集问题 ORA-01756: quoted string not properly terminated
1、注意语法的不同
2、函数不同,即有可能是Oracle没有的
c_Tname varchar2,--/*表名*/
c_kname varchar2,--/*主键列名*/
c_columns varchar2,--/*select的列名*/
c_indextable varchar2,
c_whereText varChar,--/*查询条件*/
c_orderText varChar,--/*排序条件*/
c_startIndex number,--/*取的数目开始值*/
c_endIndex number, --/*取的数目的结束值*/
C_CUR SYS_REFCURSOR --返回结果集
)
AS
c_sqlstring varchar2(1500);
c_tableid varchar2(20);
c_idType varchar2(20);
BEGIN
select data_type into c_idType from all_tab_columns where table_name=c_Tname and LOWER(name)='id'
/*如果主键Id为varchar时,则临时表的nid为varchar,否则为int*/
if c_idType='VARCHAR2' or c_idType='VARCHAR' or c_idType='CHAR' or c_idType='NUMBER' then
c_sqlstring:='select '||c_kname' from '||c_Tname||' where '||c_whereText||' order by '||c_orderText
||' union all select cname from (select '||c_columns||' cname,row_number() over(order by 1) rn from '||c_Tname||') where
rn betwen '||c_startIndex||' and '||c_endIndex ;
END IF;
open C_CUR for c_sqlstring;
end;