21 lines
1023 B
MySQL
21 lines
1023 B
MySQL
|
DELIMITER $$
|
|||
|
|
|
|||
|
|
CREATE PROCEDURE `sp_get_resource_by_id`(
|
|||
|
|
IN p_resource_id BIGINT
|
|||
|
|
)
|
|||
|
|
COMMENT '자산 ID로 상세 정보 조회'
|
|||
|
|
BEGIN
|
|||
|
|
SELECT
|
|||
|
|
r.resource_id, r.category_id, rc.category_name, r.resource_code, r.manufacturer,
|
|||
|
|
r.resource_name, r.serial_num, r.spec_value, r.spec_unit, r.user_id,
|
|||
|
|
CASE WHEN u.user_id IS NOT NULL AND u.display_name IS NOT NULL AND u.display_name != '' AND u.account_name IS NOT NULL THEN CONCAT(u.display_name, ' [', u.account_name, ']') WHEN u.display_name IS NOT NULL AND u.display_name != '' THEN u.display_name ELSE u.account_name END AS user_display_name, -- 사용자 표시 이름 로직 변경
|
|||
|
|
g.group_name, r.comments, r.purchase_date, r.register_date, r.update_date, r.is_locked -- 컬럼명 변경 반영
|
|||
|
|
FROM resource_info r
|
|||
|
|
LEFT JOIN resource_category rc ON r.category_id = rc.category_id
|
|||
|
|
LEFT JOIN user_info u ON r.user_id = u.user_id
|
|||
|
|
LEFT JOIN group_info g ON u.group_id = g.group_id
|
|||
|
|
WHERE r.resource_id = p_resource_id;
|
|||
|
|
END
|
|||
|
|
$$
|
|||
|
|
|
|||
|
|
DELIMITER ;
|