这样试试？

CREATE FUNCTION max_resource_code(userId int)
    RETURN varchar2 as
    DECLARE
        resourceNum varchar2;
    BEGIN
        SELECT RIGHT(CONCAT('0000',COALESCE(MAX(RIGHT(resource_code,5)),0) + 1),5) 
          INTO
               resourceNum
          FROM c_resource
         WHERE creator = userId ;
        
        RETURN CONCAT('U',RIGHT(CONCAT('000',userId),5),'S',resourceNum);
    END

CREATE FUNCTION max_resource_code(userId int) RETURN varchar2 as
DECLARE resourceNum varchar2;
BEGIN
SELECT RIGHT(CONCAT('0000',COALESCE(MAX(RIGHT(resource_code,5)),0) + 1),5) INTO resourceNum
FROM c_resource WHERE creator = userId ;

RETURN CONCAT('U',RIGHT(CONCAT('000',userId),5),'S',resourceNum);

END