begin
declare sTemp varchar(1000);
declare sSql varchar(4000);
declare sOrder varchar(1000);
declare s_primary_field varchar(100); IF currpage<1 THEN
SET currpage=1;
END IF; if asc_field = 1 then
set sOrder = concat(' order by ', order_field, ' desc ');
set sTemp = '<(select min';
else
set sOrder = concat(' order by ', order_field, ' asc ');
set sTemp = '>(select max';
end if; if currpage = 1 then
if sCondition <> '' then
set sSql = concat('select sql_calc_found_rows ', fieldname, ' from ', tablename, ' where ');
set sSql = concat(sSql, sCondition, sOrder, ' limit ?');
else
set sSql = concat('select sql_calc_found_rows ', fieldname, ' from ', tablename, sOrder, ' limit ?');
end if;
else
if instr(primary_field,'.')=0 then
set s_primary_field = primary_field;
else
set s_primary_field = substr(primary_field,inStr(primary_field,'.')+1,char_length(primary_field));
end if;
if sCondition <> '' then
set sSql = concat('select sql_calc_found_rows ', fieldname, ' from ', tablename);
set sSql = concat(sSql, ' where ', sCondition, ' and ', primary_field, sTemp);
set sSql = concat(sSql, '(tabtemp.', s_primary_field, ')', ' from (select ');
set sSql = concat(sSql, ' ', primary_field, ' from ', tablename, ' where ', sCondition,sOrder);
set sSql = concat(sSql, ' limit ', (currpage-1)*pagesize, ') as tabtemp)', sOrder);
set sSql = concat(sSql, ' limit ?');
else
set sSql = concat('select sql_calc_found_rows ', fieldname, ' from ', tablename);
set sSql = concat(sSql, ' where ', primary_field, sTemp);
set sSql = concat(sSql, '(tabtemp.', s_primary_field, ')', ' from (select ');
set sSql = concat(sSql, ' ', primary_field, ' from ', tablename, sOrder);
set sSql = concat(sSql, ' limit ', (currpage-1)*pagesize, ') as tabtemp)', sOrder);
set sSql = concat(sSql, ' limit ?');
end if;
end if;
set @iPageSize = pagesize;
set @sQuery = sSql;
PREPARE stmt FROM @sQuery;
EXECUTE stmt USING @iPageSize;
SET oRowsTotal=FOUND_ROWS(); IF currpage!=1 THEN
SET oRowsTotal=oRowsTotal+(currpage-1)*pagesize;
END IF;
end
这时Mysql中的分页存储过程,现在需要把它改成Oracle支持的分页存储过程。
因为我对Oracle不熟悉,所以麻烦各位高手改改啊!
declare sTemp varchar(1000);
declare sSql varchar(4000);
declare sOrder varchar(1000);
declare s_primary_field varchar(100); IF currpage<1 THEN
SET currpage=1;
END IF; if asc_field = 1 then
set sOrder = concat(' order by ', order_field, ' desc ');
set sTemp = '<(select min';
else
set sOrder = concat(' order by ', order_field, ' asc ');
set sTemp = '>(select max';
end if; if currpage = 1 then
if sCondition <> '' then
set sSql = concat('select sql_calc_found_rows ', fieldname, ' from ', tablename, ' where ');
set sSql = concat(sSql, sCondition, sOrder, ' limit ?');
else
set sSql = concat('select sql_calc_found_rows ', fieldname, ' from ', tablename, sOrder, ' limit ?');
end if;
else
if instr(primary_field,'.')=0 then
set s_primary_field = primary_field;
else
set s_primary_field = substr(primary_field,inStr(primary_field,'.')+1,char_length(primary_field));
end if;
if sCondition <> '' then
set sSql = concat('select sql_calc_found_rows ', fieldname, ' from ', tablename);
set sSql = concat(sSql, ' where ', sCondition, ' and ', primary_field, sTemp);
set sSql = concat(sSql, '(tabtemp.', s_primary_field, ')', ' from (select ');
set sSql = concat(sSql, ' ', primary_field, ' from ', tablename, ' where ', sCondition,sOrder);
set sSql = concat(sSql, ' limit ', (currpage-1)*pagesize, ') as tabtemp)', sOrder);
set sSql = concat(sSql, ' limit ?');
else
set sSql = concat('select sql_calc_found_rows ', fieldname, ' from ', tablename);
set sSql = concat(sSql, ' where ', primary_field, sTemp);
set sSql = concat(sSql, '(tabtemp.', s_primary_field, ')', ' from (select ');
set sSql = concat(sSql, ' ', primary_field, ' from ', tablename, sOrder);
set sSql = concat(sSql, ' limit ', (currpage-1)*pagesize, ') as tabtemp)', sOrder);
set sSql = concat(sSql, ' limit ?');
end if;
end if;
set @iPageSize = pagesize;
set @sQuery = sSql;
PREPARE stmt FROM @sQuery;
EXECUTE stmt USING @iPageSize;
SET oRowsTotal=FOUND_ROWS(); IF currpage!=1 THEN
SET oRowsTotal=oRowsTotal+(currpage-1)*pagesize;
END IF;
end
这时Mysql中的分页存储过程,现在需要把它改成Oracle支持的分页存储过程。
因为我对Oracle不熟悉,所以麻烦各位高手改改啊!
可以的话,麻烦了啊
2)动态SQl常量和变量之间使用 || 连接
3)instr函数用法
instr(string1, string2 [, start_position [, nth_appearance]])
string1 源字符串,要在此字符串中查找。
string2 要在string1中查找的字符串.
start_position 代表string1 的哪个位置开始查找。此参数可选,如果省略默认为1. 字符串索引从1开始。如果此参数为正,从左到右开始检索,如果此参数为负,从右到左检索,返回要查找的字符串在源字符串中的开始索引。
nth_appearance 代表要查找第几次出现的string2. 此参数可选,如果省略,默认为 1.如果为负数系统会报错。注意:
如果String2在String1中没有找到,instr函数返回0.