Question

oracle 定义一个存储过程，中间生成一个字符串存储过程，该怎么执行那个字符串存储过程？

create or replace procedure MYORCL.sp_GetRecordByPage(ParatblName in varchar2, -- 表名
ParafldName in varchar2, -- 字段名
ParaPageSize in number := 10, -- 页尺寸
ParaPageIndex in number := 1, -- 页码
ParaIsReCount in number := 0, -- 返回记录总数, 非 0 值则返回
ParaOrderType in number := 0, -- 设置排序类型, 非 0 值则降序
ParastrWhere in varchar2 := '') is -- 查询条件 (注意: 不要加 where)
ParastrSQL varchar2(6000); -- 主语句
ParastrTmp varchar2(100); -- 临时变量
ParastrOrder varchar2(400); -- 排序类型

BEGIN
if ParaOrderType != 0 then
begin
ParastrTmp := '<(select min';
ParastrOrder := ' order by [' + ParafldName + '] desc';
end;
else
ParastrTmp := '>(select max';
ParastrOrder := ' order by [' + ParafldName + '] asc';
end if;

ParastrSQL := 'select top ' + TO_CHAR(ParaPageSize) + ' * from [' +

ParatblName + '] where [' + ParafldName + ']' + ParastrTmp + '([' +
ParafldName + ']) from (select top ' +
TO_CHAR((ParaPageIndex - 1) * ParaPageSize) + ' [' +
ParafldName + '] from [' + ParatblName + ']' + ParastrOrder +
') as tblTmp)' + ParastrOrder;

if ParastrWhere != '' then
ParastrSQL := 'select top ' + TO_CHAR(ParaPageSize) + ' * from [' +
ParatblName + '] where [' + ParafldName + ']' +
ParastrTmp + '([' + ParafldName + ']) from (select top ' +
TO_CHAR((ParaPageIndex - 1) * ParaPageSize) + ' [' +
ParafldName + '] from [' + ParatblName + '] where ' +
ParastrWhere + ' ' + ParastrOrder + ') as tblTmp) and ' +
ParastrWhere + ' ' + ParastrOrder;
end if;
if ParaPageIndex = 1 then
ParastrTmp := '';
if ParastrWhere != '' then
ParastrTmp := ' where ' + ParastrWhere;

ParastrSQL := 'select top ' + TO_CHAR(ParaPageSize) + ' * from [' +
ParatblName + ']' + ParastrTmp + ' ' + ParastrOrder;
end if;
end if;
if ParaIsReCount != 0 then
ParastrSQL := 'select count(*) as Total from [' + ParatblName + ']' +
' where ' + ParastrWhere;
end if;
--这用exec ParastrSQL？
end sp_GetRecordByPage;
execute immediate ParastrSQL吗？

Answer 1

是的
execute immediate sqlStr
但是你的ParastrSQL 拼接方式有问题
ParastrSQL := 'select count(*) as Total from [' + ParatblName + ']' +
' where ' + ParastrWhere;
改为
ParastrSQL := 'select count(*) as Total from ' ||ParatblName||'where'||ParastrWhere;
oracle中用||拼接字符串

追问

get it,3Q,btw,要不要定义一个变量来接收结果execute immediate ParastrSQL into？

追答

这个你可以在ParastrSQL 中Into
如中间变量为 v_count
ParastrSQL := 'select count(*) into'||v_count|| 'from ' ||ParatblName||'where'||ParastrWhere;

Answer 2

正解，但要定义个变量来接收结果
execute immediate ParastrSQL into