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

来源：互联网发布：为什么要java序列化编辑：程序博客网时间：2024/06/09 14:37

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 ;