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