star902 于 2013.10.25 09:09 提问

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