Oracle表多列模糊查询存储过程改造需求

Got it, let's fix up your stored procedure to support searching across all columns in the APPLICATION_MASTER table instead of just APPLICATIONNAME.

The Modified Procedure #

Here's the updated code that checks every column for a match with the input text:

PROCEDURE GET_SEARCH_DATA ( 
    P_INPUTTEXT IN NVARCHAR2, 
    P_RETURN OUT SYS_REFCURSOR 
) AS 
BEGIN 
    OPEN P_RETURN FOR 
        SELECT APP_MST_ID, APPLICATIONNAME, PROJECTNO, VSS_FOLDER_LOC 
        FROM APPLICATION_MASTER 
        WHERE 
            -- Check each character column directly
            APPLICATIONNAME LIKE '%' || P_INPUTTEXT || '%'
            OR PROJECTNO LIKE '%' || P_INPUTTEXT || '%'
            OR VSS_FOLDER_LOC LIKE '%' || P_INPUTTEXT || '%'
            -- Convert numeric column to string to avoid type mismatch (adjust if APP_MST_ID is not numeric)
            OR TO_CHAR(APP_MST_ID) LIKE '%' || P_INPUTTEXT || '%';
END;

Key Details to Note #

• Data Type Compatibility: If APP_MST_ID is a numeric type (like NUMBER), we use TO_CHAR() to convert it to a string before applying the LIKE condition—this prevents type mismatch errors. If it's already a character column, you can remove the TO_CHAR() wrapper.
• Handling NULLs: If any column might have NULL values and you want to treat them as empty strings for matching, wrap the column in NVL() like this: NVL(APPLICATIONNAME, '') LIKE '%' || P_INPUTTEXT || '%'
• Performance Tip: Full-column LIKE queries can slow down on large tables. If performance becomes an issue, consider using Oracle's Text Indexing (like creating a CONTEXT index) to optimize keyword searches across multiple columns.

内容的提问来源于stack exchange，提问作者Nadeem