软讯网络 > 冲浪宝典 > 网络资源 > MySQL中这样创建存贮过程(procedure)

【标题】：MySQL中这样创建存贮过程(procedure)
【关键字】：MySQL,procedure
【来源】：http://blog.csdn.net/iqueen/archive/2006/06/12/792306.aspx

MySQL中这样创建存贮过程(procedure)

DROP PROCEDURE IF EXISTS sp_unit_by_sec_class;
delimiter //
CREATE PROCEDURE sp_unit_by_sec_class(DICT_ID INT,ORDER_BY INT)
BEGIN
IF (ORDER_BY = 1)
THEN
SELECT * FROM
(
SELECT e.unit_id,e.unit_price,e.cr_date,e.USER_NAME,e.unit_memo,e.unit_status
FROM t_units e
WHERE e.dict_id = DICT_ID
UNION
SELECT c.unit_id,d.drop_fee-d.grow_fee,d.drop_date,d.USER_NAME,drop_memo,c.unit_status
FROM t_units c
RIGHT JOIN t_drop d ON c.unit_id = d.unit_id
WHERE c.dict_id = DICT_ID
UNION
SELECT a.unit_id,b.grow_fee,b.grow_date,b.USER_NAME,b.grow_memo,2
FROM t_units a
RIGHT JOIN t_grow b ON a.unit_id = b.unit_id
WHERE a.dict_id = DICT_ID
) t1 ORDER BY 1,3;
ELSE
SELECT * FROM
(
SELECT e.unit_id,e.unit_price,e.cr_date,e.USER_NAME,e.unit_memo,e.unit_status
FROM t_units e
WHERE e.dict_id = DICT_ID
UNION
SELECT c.unit_id,d.drop_fee-d.grow_fee,d.drop_date,d.USER_NAME,drop_memo,c.unit_status
FROM t_units c
RIGHT JOIN t_drop d ON c.unit_id = d.unit_id
WHERE c.dict_id = DICT_ID
UNION
SELECT a.unit_id,b.grow_fee,b.grow_date,b.USER_NAME,b.grow_memo,2
FROM t_units a
RIGHT JOIN t_grow b ON a.unit_id = b.unit_id
WHERE a.dict_id = DICT_ID
) t1 ORDER BY 3,1;
END IF;
END
;
//
delimiter ;