mysql记录存储过程信息_mysql查看存储过程工作记录20130711

查询数据库教程中的存储过程：

示例一：(直接查询某一个库中的存储过程)mysql> select `name` from mysql.proc where db = 'xy_db_gm' and `type` = 'PROCEDURE';

+-------------------------------------------------+

| name |

+-------------------------------------------------+

| xy_proc_ActivityCodesBatchAdd |

| xy_proc_ActivityCodesUpdate |

| xy_proc_IncomeRecordQueryByDateInterval |

| xy_proc_LoginRecordQueryByDateInterval |

| xy_proc_LoginUsersAdd |

| xy_proc_LoginUsersQueryByDateInterval |

| xy_proc_LoginUsersQueryLastRecordTime |

| xy_proc_LogoutRecordAdd |

| xy_proc_MartRecordAdd |

| xy_proc_MartRecordQueryByDateInterval |

| xy_proc_OnlineUsersAdd |

| xy_proc_OperatorRecordAdd |

| xy_proc_PayRecordAdd |

| xy_proc_PayRecordQueryByDateInterval |

| xy_proc_PayRecordQueryByDateIntervalOrId |

| xy_proc_RegisterUsersAdd |

| xy_proc_UserAdd |

| xy_proc_UserDelete |

| xy_proc_UserExistsUsername |

| xy_proc_UserFind |

| xy_proc_UserQueryAll |

| xy_proc_UserUpdatePassword |

| xy_proc_UserUpdateRoleId |

+-------------------------------------------------+

52 rows in set (0.00 sec)

方法二：(查看某一个存储过程的具体内容)mysql> select body from mysql.proc where specific_name = 'xy_proc_Register';

+-----------------------------------------------------------------------------------------------------------------+

| body |

+-----------------------------------------------------------------------------------------------------------------+

| BEGIN

SELECT `AreaId`, max(`Time`) as `LastTime` FROM `xy_tbl_registerusers_day`

group by `AreaId`;

END |

+-----------------------------------------------------------------------------------------------------------------+

1 row in set (0.00 sec)

方法三：(查看数据库里所有存储过程+内容)mysql> show procedure status;

方法四：(查看当前数据库里存储过程列表)mysql> use xy_db_activity;

Database changed

mysql> select specific_name from mysql.proc ;

+-----------------------------------------------------+

| specific_name |

+-----------------------------------------------------+

| proc_Inner_DropTestUserData |

| proc_Inner_DropUserData |

| proc_Inner_InitData |

| proc_Inner_InitTestData |

| Proc_MySQL_Warmup |

| xy_prco_CheckInRewardDelete |

| xy_proc_ChallengeInfoGet |

| xy_proc_ChallengeInfoUpdate |

| xy_proc_ChallengeRankingListGet |

| xy_proc_ChallengeRankingListUpdate |

| xy_proc_XingHunUpdate |

| xy_proc_XingTuSingleInfoGet |

| xy_proc_XingTuSingleInfoUpdate |

| xy_proc_XiuLianInfoGet |

| xy_proc_XiuLianInfoUpdate |

| xy_proc_XiuLianMsoulsGet |

| xy_proc_GroupGetList |

| xy_proc_GroupMemberDelete |

| xy_proc_GroupMemberFindGroup |

| xy_proc_GroupMemberGetList |

| xy_proc_GroupMemberUpdate |

| test_genroobs |

+-----------------------------------------------------+

320 rows in set (0.00 sec)

查看存储过程或函数的创建代码 ：

show create procedure your_proc_name;

show create function your_func_name;

删除存储过程：

drop procedure your_proc_name;

查看一个库的函数select `name` from mysql.proc where db = 'xy_db_config' and `type` = 'FUNCTION'