CREATE PROCEDURE get_supplier_rank(IN user_type_cd VARCHAR(6),IN supplier_id INT,OUT rank INT)
BEGIN
set @mycnt = 0;
SELECT a.rank FROM (
SELECT t.id,(@mycnt := @mycnt + 1) as rank FROM suppliers t INNER JOIN user_points ut on t.id=ut.user_id where ut.user_type_cd=user_type_cd ORDER BY ut.points desc )
a where a.id=supplier_id ORDER BY id;
BEGIN
set @mycnt = 0;
SELECT a.rank FROM (
SELECT t.id,(@mycnt := @mycnt + 1) as rank FROM suppliers t INNER JOIN user_points ut on t.id=ut.user_id where ut.user_type_cd=user_type_cd ORDER BY ut.points desc )
a where a.id=supplier_id ORDER BY id;
END
查询所有的存储过程:
SHOW PROCEDURE STATUS
查询存储过程的内容:
show create procedure get_cart_rank;
show create procedure 存储过程;
删除存储过程:
drop PROCEDURE get_supplier_rank;
drop PROCEDURE 存储过程名;