star902 2013-10-25 01:09 采纳率: 0%
浏览 851

orcal 中存储过程 这样写对吗

create or replace procedure PageList(tbName IN VARCHAR(255),tbFieldsIN VARCHAR(1000), orderField IN VARCHAR(255),orderType IN INT,strWhere IN VARCHAR(1000),pageSize IN INT,pageIndex IN INT,pageRecord OUT INT)
is
BEGIN

/*定义变量*/
DECLARE m_begin_row  INT DEFAULT 0;
DECLARE m_limit_string  CHAR(64);
DECLARE m_order_string  CHAR(128);
DECLARE orderStr        CHAR(64);
DECLARE whereStr        VARCHAR(1000);

/*构造语句*/
if (orderType=1) then
set orderStr = 'asc';
else
set orderStr = 'desc';
end if;

if (strWhere<>'') then
set whereStr = CONCAT(' where ',strWhere,' ');
else
set whereStr = ' ';
end if;

SET m_begin_row = (pageIndex - 1) * pageSize;
SET m_limit_string = CONCAT(' LIMIT ', m_begin_row, ', ', pageSize);
SET m_order_string = CONCAT(' order by ',orderField,' ',orderStr);

SET @COUNT_STRING = CONCAT('SELECT COUNT(*) INTO @ROWS_TOTAL FROM ', tbName, ' ', whereStr);
SET @MAIN_STRING = CONCAT('SELECT ', tbFields, ' FROM ', tbName, '  ', whereStr, '  ', m_order_string, m_limit_string);

/*预处理*/
PREPARE count_stmt FROM @COUNT_STRING;
EXECUTE count_stmt;
DEALLOCATE PREPARE count_stmt;
SET pageRecord = @ROWS_TOTAL;

PREPARE main_stmt FROM @MAIN_STRING;
EXECUTE main_stmt;
DEALLOCATE PREPARE main_stmt;

END

  • 写回答

0条回答

    报告相同问题?

    悬赏问题

    • ¥15 这个电路是如何实现路灯控制器的,原理是什么,怎么求解灯亮起后熄灭的时间如图?
    • ¥15 matlab数字图像处理频率域滤波
    • ¥15 在abaqus做了二维正交切削模型,给刀具添加了超声振动条件后输出切削力为什么比普通切削增大这么多
    • ¥15 ELGamal和paillier计算效率谁快?
    • ¥15 file converter 转换格式失败 报错 Error marking filters as finished,如何解决?
    • ¥15 ubuntu系统下挂载磁盘上执行./提示权限不够
    • ¥15 Arcgis相交分析无法绘制一个或多个图形
    • ¥15 关于#r语言#的问题:差异分析前数据准备,报错Error in data[, sampleName1] : subscript out of bounds请问怎么解决呀以下是全部代码:
    • ¥15 seatunnel-web使用SQL组件时候后台报错,无法找到表格
    • ¥15 fpga自动售货机数码管(相关搜索:数字时钟)