MySQL存储过程总结示例

CreatePROCEDURE`mysqltestuser_Select_PageAble`(
_WhereClauseVARCHAR(2000),--查找条件
_OrderByVARCHAR(2000),--排序条件
_PageSizeINT,--每页记录数
_PageIndexINT,--当前页码
_DoCountBIT--标志:统计数据/输出数据
)
NOTDETERMINISTIC
SQLSECURITYDEFINER
COMMENT''
BEGIN
--定义key字段临时表
DropTABLEIFEXISTS_TempTable_KeyID;--删除临时表,如果存在
CreateTEMPORARYTABLE_TempTable_KeyID
(
useridINT
)TYPE=HEAP;

--构建动态的sql，输出关键字key的id集合
--查找条件
SET@sql='SelectuseridFROMmysqltestuser';
IF(_WhereClauseisNOTNULL)AND(_WhereClause<>'')THEN
SET@sql=concat(@sql,'Where',_WhereClause);
ENDif;

IF(_OrderByisNOTNULL)AND(_OrderBy<>'')THEN
SET@sql=concat(@sql,'orDERBY',_OrderBy);
ENDIF;

--准备id记录插入到临时表
set@sql=concat('insertinto_TempTable_KeyID(userid)',@sql);
PREPAREstmtFROM@sql;
EXECUTEstmt;
DEALLOCATEPREPAREstmt;
--key的id集合[end]

--下面是输出
IF(_DoCount=1)then--统计
BEGIN
SelectCOUNT(*)ASRecordCountFROM_TempTable_KeyID;
END;
ELSE--输出记录集
BEGIN
--计算记录的起点位置
SET@startPoint=ifnull((_PageIndex-1)*_PageSize,0);
SET@sql='SelectA.*
FROMmysqltestuserA
INNERJOIN_TempTable_KeyIDB
ONA.userid=B.userid';

SET@sql=CONCAT(@sql,"LIMIT",@startPoint,",",_PageSize);
PREPAREstmtFROM@sql;
EXECUTEstmt;
DEALLOCATEPREPAREstmt;
END;
ENDIF;

DropTABLE_TempTable_KeyID;
END;

DROPPROCEDUREIFEXISTSpr_pager;
CREATEPROCEDUREpr_pager(

INp_table_nameVARCHAR(1024),/*表名*/
INp_fieldsVARCHAR(1024),/*查询字段*/
INp_page_sizeINT,/*每页记录数*/
INp_page_nowINT,/*当前页*/
INp_order_stringVARCHAR(128),/*排序条件(包含ORDER关键字,可为空)*/
INp_where_stringVARCHAR(1024),/*WHERE条件(包含WHERE关键字,可为空)*/
OUTp_out_rowsINT/*输出记录总数*/

)
NOTDETERMINISTIC
SQLSECURITYDEFINER
COMMENT'分页存储过程'

BEGIN

/*定义变量*/
DECLAREm_begin_rowINTDEFAULT0;
DECLAREm_limit_stringCHAR(64);

/*构造语句*/
SETm_begin_row=(p_page_now-1)*p_page_size;
SETm_limit_string=CONCAT('LIMIT',m_begin_row,',',p_page_size);

SET@COUNT_STRING=CONCAT('SELECTCOUNT(*)INTO@ROWS_TOTALFROM',p_table_name,'',p_where_string);
SET@MAIN_STRING=CONCAT('SELECT',p_fields,'FROM',p_table_name,'',p_where_string,'',p_order_string,m_limit_string);

/*预处理*/
PREPAREcount_stmtFROM@COUNT_STRING;
EXECUTEcount_stmt;
DEALLOCATEPREPAREcount_stmt;
SETp_out_rows=@ROWS_TOTAL;

PREPAREmain_stmtFROM@MAIN_STRING;
EXECUTEmain_stmt;
DEALLOCATEPREPAREmain_stmt;

END;