-
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 ;
MySQL中这样创建存贮过程(procedure)
最新推荐文章于 2023-06-18 10:56:34 发布