你的代码测试如下,这样别人怎么回答你呢? mysql> delimiter // mysql> CREATE PROCEDURE GetRecordByPage( -> IN tblName VARCHAR(255), /*表名*/ -> IN fldName VARCHAR(255), /*主键字段名*/ -> IN PageSize INT, /*页尺寸*/ -> IN PageIndex INT, /*页码*/ -> IN IsReCount BIT, /*返回记录总数,非0值则返回*/ -> IN OrderType BIT, /*设置排序类型,非0值则降序*/ -> IN strWhere VARCHAR(1000) /*WHERE条件(包含WHERE关键字,可为空)*/ -> ) -> NOT DETERMINISTIC -> SQL SECURITY DEFINER -> COMMENT '分页存储过程' -> -> BEGIN -> -> /*定义变量*/ -> DECLARE m_begin_row INT DEFAULT 0; -> DECLARE m_limit_string CHAR(64); -> DECLARE m_ordertype VARCHAR(20); -> -> IF OrderType=0 THEN -> SET m_ordertype='desc'; -> ELSE -> SET m_ordertype='asc'; -> END IF; -> -> /*构造语句*/ -> SET m_begin_row = (PageIndex - 1) * PageSize; -> SET m_limit_string = CONCAT(' LIMIT ', m_begin_row, ', ', PageSize); -> -> SET @COUNT_STRING = CONCAT('SELECT COUNT(*) INTO @ROWS_TOTAL FROM ',tblName, ' ', strWhere); -> SET @MAIN_STRING = CONCAT('SELECT * FROM ', tblName, ' WHERE', strWhere, ' ORDER BY', fldName , m_ordertype , m_limit_string); -> -> /*预处理*/ -> PREPARE count_stmt FROM @COUNT_STRING; -> EXECUTE count_stmt; -> DEALLOCATE PREPARE count_stmt; -> -> PREPARE main_stmt FROM @MAIN_STRING; -> EXECUTE main_stmt; -> DEALLOCATE PREPARE main_stmt; -> -> END; -> // Query OK, 0 rows affected (0.16 sec)mysql> delimiter ; mysql>
你是在命令行下输入? DELIMITER $$ CREATE PROCEDURE GetRecordByPage( IN tblName VARCHAR(255), /*表名*/ IN fldName VARCHAR(255), /*主键字段名*/ IN PageSize INT, /*页尺寸*/ IN PageIndex INT, /*页码*/ IN IsReCount BIT, /*返回记录总数,非0值则返回*/ IN OrderType BIT, /*设置排序类型,非0值则降序*/ IN strWhere VARCHAR(1000) /*WHERE条件(包含WHERE关键字,可为空)*/ ) NOT DETERMINISTIC SQL SECURITY DEFINER COMMENT '分页存储过程'
BEGIN /*定义变量*/ DECLARE m_begin_row INT DEFAULT 0; DECLARE m_limit_string CHAR(64); DECLARE m_ordertype VARCHAR(20); IF OrderType=0 THEN SET m_ordertype='desc'; ELSE SET m_ordertype='asc'; END IF; /*构造语句*/ SET m_begin_row = (PageIndex - 1) * PageSize; SET m_limit_string = CONCAT(' LIMIT ', m_begin_row, ', ', PageSize);
SET @COUNT_STRING = CONCAT('SELECT COUNT(*) INTO @ROWS_TOTAL FROM ', tblName, ' ', strWhere); SET @MAIN_STRING = CONCAT('SELECT * FROM ', tblName, ' WHERE', strWhere, ' ORDER BY', fldName , m_ordertype , m_limit_string); /*预处理*/ PREPARE count_stmt FROM @COUNT_STRING; EXECUTE count_stmt; DEALLOCATE PREPARE count_stmt; PREPARE main_stmt FROM @MAIN_STRING; EXECUTE main_stmt; DEALLOCATE PREPARE main_stmt;
mysql> delimiter //
mysql> CREATE PROCEDURE GetRecordByPage(
-> IN tblName VARCHAR(255), /*表名*/
-> IN fldName VARCHAR(255), /*主键字段名*/
-> IN PageSize INT, /*页尺寸*/
-> IN PageIndex INT, /*页码*/
-> IN IsReCount BIT, /*返回记录总数,非0值则返回*/ -> IN OrderType BIT, /*设置排序类型,非0值则降序*/
-> IN strWhere VARCHAR(1000) /*WHERE条件(包含WHERE关键字,可为空)*/
-> )
-> NOT DETERMINISTIC
-> SQL SECURITY DEFINER
-> COMMENT '分页存储过程'
->
-> BEGIN
->
-> /*定义变量*/
-> DECLARE m_begin_row INT DEFAULT 0;
-> DECLARE m_limit_string CHAR(64);
-> DECLARE m_ordertype VARCHAR(20);
->
-> IF OrderType=0 THEN
-> SET m_ordertype='desc';
-> ELSE
-> SET m_ordertype='asc';
-> END IF;
->
-> /*构造语句*/
-> SET m_begin_row = (PageIndex - 1) * PageSize;
-> SET m_limit_string = CONCAT(' LIMIT ', m_begin_row, ', ', PageSize);
->
-> SET @COUNT_STRING = CONCAT('SELECT COUNT(*) INTO @ROWS_TOTAL FROM ',tblName, ' ', strWhere);
-> SET @MAIN_STRING = CONCAT('SELECT * FROM ', tblName, ' WHERE', strWhere, ' ORDER BY', fldName , m_ordertype , m_limit_string);
->
-> /*预处理*/
-> PREPARE count_stmt FROM @COUNT_STRING;
-> EXECUTE count_stmt;
-> DEALLOCATE PREPARE count_stmt;
->
-> PREPARE main_stmt FROM @MAIN_STRING;
-> EXECUTE main_stmt;
-> DEALLOCATE PREPARE main_stmt;
->
-> END;
-> //
Query OK, 0 rows affected (0.16 sec)mysql> delimiter ;
mysql>
DELIMITER $$
CREATE PROCEDURE GetRecordByPage(
IN tblName VARCHAR(255), /*表名*/
IN fldName VARCHAR(255), /*主键字段名*/
IN PageSize INT, /*页尺寸*/
IN PageIndex INT, /*页码*/
IN IsReCount BIT, /*返回记录总数,非0值则返回*/
IN OrderType BIT, /*设置排序类型,非0值则降序*/
IN strWhere VARCHAR(1000) /*WHERE条件(包含WHERE关键字,可为空)*/
)
NOT DETERMINISTIC
SQL SECURITY DEFINER
COMMENT '分页存储过程'
BEGIN /*定义变量*/
DECLARE m_begin_row INT DEFAULT 0;
DECLARE m_limit_string CHAR(64);
DECLARE m_ordertype VARCHAR(20); IF OrderType=0 THEN
SET m_ordertype='desc';
ELSE
SET m_ordertype='asc';
END IF; /*构造语句*/
SET m_begin_row = (PageIndex - 1) * PageSize;
SET m_limit_string = CONCAT(' LIMIT ', m_begin_row, ', ', PageSize);
SET @COUNT_STRING = CONCAT('SELECT COUNT(*) INTO @ROWS_TOTAL FROM ', tblName, ' ', strWhere);
SET @MAIN_STRING = CONCAT('SELECT * FROM ', tblName, ' WHERE', strWhere, ' ORDER BY', fldName , m_ordertype , m_limit_string); /*预处理*/
PREPARE count_stmt FROM @COUNT_STRING;
EXECUTE count_stmt;
DEALLOCATE PREPARE count_stmt; PREPARE main_stmt FROM @MAIN_STRING;
EXECUTE main_stmt;
DEALLOCATE PREPARE main_stmt;
END;
$$