查询数据库教程中的存储过程:
示例一:(直接查询某一个库中的存储过程)
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'