报错信息:
异常信息: ORA-06550: 第 1 行, 第 38 列: PLS-00103: 出现符号 ":"在需要下列之一时: ( - + case mod new not null avg count current exists max min prior sql stddev sum variance execute forall merge time timestamp interval date pipe <一个带有字符集说明的可带引号的字符串文字> <一个可带引号的 SQL 字符串> 符号 "(在 ":" 继续之前已插入。 ORA-06550: 第 1 行, 第 62 列: PLS-00103: 出现符号 ":"在需要下列之一时: ( - + case mod new not null avg count current exists max min prior sql stddev sum variance execute forall merge t ORA-06550:
存储过程代码:
CREATE OR REPLACE PROCEDURE GetRecordFromPageNew
(
tblName in varchar, -- 表名
RetColumns in varchar, -- 需要返回的列,默认为全部
Key in varchar, -- 主键
Orderfld in varchar, -- 排序字段名,order by
PageSize in number, -- 页尺寸
PageIndex in number, -- 页码
strWhere in varchar, -- 查询条件 (注意: 不要加 where)
ret out sys_refcursor
)
is strSQL varchar(2000); -- 主语句 begin
begin
if strWhere is not null then
strSQL := 'select count(*) as Total from '+ tblName + ' where ' + strWhere;
else
strSQL := 'select count(*) as Total from ' + tblName ;
end if;
end ;
begin
if PageIndex = 1 then
begin
if strWhere is not null then
strSQL :='select * from ( select ' + RetColumns + ' from '+tblName+' where '+strWhere+' order by '+Orderfld+') where rownum<= '+PageSize;
else
strSQL :='select * from ( select ' + RetColumns + ' from '+tblName+' order by '+Orderfld+ ') where rownum<= '+PageSize;
end if;
end;
end if;
end;
if PageIndex != 1 then
begin
if strWhere is not null then
strSQL :='select * from ( select '+RetColumns+' from '+tblName+' where '+Key+' not in ( select * from ( select '+Key+' from '+tblName+' where '+strWhere+' order by '+Orderfld+') where rownum<= '+((PageIndex-1)*PageSize)+' ) and '+strWhere+' order by '+Orderfld+') where rownum<= '+PageSize;
else
strSQL :='select * from ( select '+RetColumns+' from '+tblName+' where '+Key+' not in (select * from ( select '+Key+' from '+tblName+' order by '+Orderfld+' ) where rownum<= '+((PageIndex-1)*PageSize)+') order by '+Orderfld+') where rownum<= '+PageSize;
end if;
end;
end if;
open ret for strSQL;
end GetRecordFromPageNew;================
麻烦各位,谢谢。折腾一下午了
异常信息: ORA-06550: 第 1 行, 第 38 列: PLS-00103: 出现符号 ":"在需要下列之一时: ( - + case mod new not null avg count current exists max min prior sql stddev sum variance execute forall merge time timestamp interval date pipe <一个带有字符集说明的可带引号的字符串文字> <一个可带引号的 SQL 字符串> 符号 "(在 ":" 继续之前已插入。 ORA-06550: 第 1 行, 第 62 列: PLS-00103: 出现符号 ":"在需要下列之一时: ( - + case mod new not null avg count current exists max min prior sql stddev sum variance execute forall merge t ORA-06550:
存储过程代码:
CREATE OR REPLACE PROCEDURE GetRecordFromPageNew
(
tblName in varchar, -- 表名
RetColumns in varchar, -- 需要返回的列,默认为全部
Key in varchar, -- 主键
Orderfld in varchar, -- 排序字段名,order by
PageSize in number, -- 页尺寸
PageIndex in number, -- 页码
strWhere in varchar, -- 查询条件 (注意: 不要加 where)
ret out sys_refcursor
)
is strSQL varchar(2000); -- 主语句 begin
begin
if strWhere is not null then
strSQL := 'select count(*) as Total from '+ tblName + ' where ' + strWhere;
else
strSQL := 'select count(*) as Total from ' + tblName ;
end if;
end ;
begin
if PageIndex = 1 then
begin
if strWhere is not null then
strSQL :='select * from ( select ' + RetColumns + ' from '+tblName+' where '+strWhere+' order by '+Orderfld+') where rownum<= '+PageSize;
else
strSQL :='select * from ( select ' + RetColumns + ' from '+tblName+' order by '+Orderfld+ ') where rownum<= '+PageSize;
end if;
end;
end if;
end;
if PageIndex != 1 then
begin
if strWhere is not null then
strSQL :='select * from ( select '+RetColumns+' from '+tblName+' where '+Key+' not in ( select * from ( select '+Key+' from '+tblName+' where '+strWhere+' order by '+Orderfld+') where rownum<= '+((PageIndex-1)*PageSize)+' ) and '+strWhere+' order by '+Orderfld+') where rownum<= '+PageSize;
else
strSQL :='select * from ( select '+RetColumns+' from '+tblName+' where '+Key+' not in (select * from ( select '+Key+' from '+tblName+' order by '+Orderfld+' ) where rownum<= '+((PageIndex-1)*PageSize)+') order by '+Orderfld+') where rownum<= '+PageSize;
end if;
end;
end if;
open ret for strSQL;
end GetRecordFromPageNew;================
麻烦各位,谢谢。折腾一下午了
sqlserver 代码不会有 create or replace
CREATE OR REPLACE PROCEDURE GetRecordFromPageNew(tblName in varchar, -- 表名
RetColumns in varchar, -- 需要返回的列,默认为全部
Key in varchar, -- 主键
Orderfld in varchar, -- 排序字段名,order by
PageSize in number, -- 页尺寸
PageIndex in number, -- 页码
strWhere in varchar, -- 查询条件 (注意: 不要加 where)
ret out sys_refcursor ) is strSQL varchar(2000); -- 主语句
begin
begin
if strWhere is not null then
strSQL := 'select count(*) as Total from ' || tblName || ' where ' ||
strWhere;
else
strSQL := 'select count(*) as Total from ' || tblName;
end if;
end;
begin
if PageIndex = 1 then
begin
if strWhere is not null then
strSQL := 'select * from ( select ' || NVL(RetColumns,'*') || ' from ' ||
tblName || ' where ' || strWhere || ' order by ' ||
Orderfld || ') where rownum<= ' || PageSize;
else
strSQL := 'select * from ( select ' || NVL(RetColumns,'*') || ' from ' ||
tblName || ' order by ' || Orderfld ||
') where rownum<= ' || PageSize;
end if;
end;
else
begin
if strWhere is not null then
strSQL := 'select * from ( select ' || NVL(RetColumns,'*')||' from ' ||
tblName || ' where ' || Key ||
' not in ( select * from ( select ' || Key || ' from ' ||
tblName || ' where ' || strWhere || ' order by ' ||
Orderfld || ') where rownum<= ' ||
((PageIndex - 1) * PageSize) || ' ) and ' || strWhere ||
' order by ' || Orderfld || ') where rownum<= ' ||
PageSize;
else
strSQL := 'select * from ( select ' || NVL(RetColumns,'*') || ' from ' ||
tblName || ' where ' || Key ||
' not in (select * from ( select ' || Key || ' from ' ||
tblName || ' order by ' || Orderfld ||
' ) where rownum<= ' || ((PageIndex - 1) * PageSize) ||
') order by ' || Orderfld || ') where rownum<= ' ||
PageSize;
end if;
end;
end if;
end;
dbms_output.put_line(strSQL);
open ret for strSQL;
end GetRecordFromPageNew;