set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
goALTER proc [dbo].[QueryPagedTable]
@PageSize int,
@PageNo int,
@Table sysname,
@Filter nvarchar(1000),
@Order nvarchar(500)
as
begin IF (LEN(@Order) > 0)
SET @Order = ' ORDER BY ' + @Order
ELSE
SET @Order = ' ORDER BY ID '
declare @newSql Nvarchar(max)
set @newSql=''
declare @beginPage as nvarchar(100) --开始数字
set @beginPage=(@PageNo*@PageSize)-(@pageSize-1)
declare @endPage nvarchar(100)
set @endPage=@beginPage+@PageSize-1
set @newSql='select * from (
SELECT Row_Number() over('+ @order +') as rowNum,* from '+@Table+' as aa where '+@Filter+') as yy
where rowNum BETWEEN '+ @beginPage+' and '+@endPage+ @Order
print @newSql
exec (@newSql)
end
--转换
create or replace procedure QueryPagedTable(
p_pagesize int,
p_pageno int,
p_table varchar2,
p_filter varchar2,
p_order in out varchar2
)
is
v_sql varchar2(4000) := '';
v_begPage varchar2(100) := '';
v_endPage varchar2(100) := '';
type cur_type is ref cursor;
cur cur_type;
v_cols varchar2(4000):='';
v_col varchar2(30):='';
v_sqlcol varchar2(4000):='';
begin
if (length(p_order)>0) then
p_order := ' ORDER BY '|| p_order;
else
p_order := ' ORDER BY ID';
end if;
v_begPage := p_pageno*p_pagesize-(p_pagesize-1);
v_endPage := to_number(v_begPage)+p_pagesize-1;
v_sqlcol := 'select column_name from user_tab_columns where table_name=upper('''||p_table||''')';
open cur for v_sqlcol;
loop
fetch cur into v_col;
exit when cur%notfound;
v_cols := v_cols||','||v_col;
end loop;
close cur;
v_sql := 'select * from (
SELECT Row_Number() over('|| p_order ||') rn'||v_cols||' from '||p_table||' aa where '||p_filter||') yy
where rn BETWEEN '|| v_begPage ||' and '|| v_endPage || p_order;
dbms_output.put_line(v_sql);
execute immediate v_sql;
end;
/
--测试
declare
v_pagesize int := 10;
v_pageno int := 2;
v_table varchar2(50) := 'emp';
v_filter varchar2(1000) := 'empno=7369';
v_order varchar2(500) := 'empno';
begin
QueryPagedTable(v_pagesize,v_pageno,v_table,v_filter,v_order);
end;
/
--结果
/*
select * from (
SELECT Row_Number() over( ORDER BY empno)
rn,EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO from emp aa where empno=7369)
yy
where rn BETWEEN 11 and 20 ORDER BY empno PL/SQL 过程已成功完成。
*/
p_pagesize int,
p_pageno int,
p_table varchar2,
p_filter varchar2,
p_order in out varchar2
)
is
v_sql varchar2(4000) := '';
v_begPage varchar2(100) := '';
v_endPage varchar2(100) := '';
type cur_type is ref cursor;
cur cur_type;
v_cols varchar2(4000):='';
v_col varchar2(30):='';
v_sqlcol varchar2(4000):='';
begin
if (length(p_order)>0) then
p_order := ' ORDER BY '|| p_order;
else
p_order := ' ORDER BY ID';
end if;
v_begPage := p_pageno*p_pagesize-(p_pagesize-1);
v_endPage := to_number(v_begPage)+p_pagesize-1;
v_sqlcol := 'select column_name from user_tab_columns where table_name=upper('''||p_table||''')';
open cur for v_sqlcol;
loop
fetch cur into v_col;
exit when cur%notfound;
v_cols := v_cols||','||v_col;
end loop;
close cur;
v_sql := 'select * from (
SELECT Row_Number() over('|| p_order ||') rn'||v_cols||' from '||p_table||' aa where '||p_filter||') yy
where rn BETWEEN '|| v_begPage ||' and '|| v_endPage || p_order;
dbms_output.put_line(v_sql);
execute immediate v_sql;
end;
/declare
v_pagesize int := 10;
v_pageno int := 2;
v_table varchar2(50) := 'emp';
v_filter varchar2(1000) := 'empno=7369';
v_order varchar2(500) := 'empno';
begin
QueryPagedTable(v_pagesize,v_pageno,v_table,v_filter,v_order);
end;
报错:
ORA-00936:缺失表达式
--execute immediate v_sql;
v_table varchar2(50) := 'info'; 不行v_table varchar2(50) := '"info"';也不行
我手动新建的表,用你写的这个就完全通过,我想问一下,上面的这种情况有解决办法吗?