CODEWITHSUNDEEP
sqloracle-databasefunction
user1009073
user1009073

Reputation: 3238

Oracle SQL - Creating Function for Where Clause

Oracle 12c. Somewhat contrived example, but easier to explain... Assume I have a series of tables, each based on a separate product...I have a table for CARS, a different table for BOATS, a different table for PLANES, etc. There are approx 15 of these tables. (Remember, somewhat contrived example...;) Each table has a set of identical columns about the rep who sold this item, and the reps manager, and his manager, etc for 4 levels.

A normal query will be to see all activity for the logged on person, or any activity in that person's hierarchy. What I am doing now is

enter image description here

I would like to be able to use a database function so that I could say something like

select * 
from boats 
where in_hierarchy(PERSON_LOGGED_IN) = true;

It appears that each function wants to query the database for the values it uses, not get passed those values from whatever row is the current row in the query.

Is it possible to write a COMMON function to check all the hierarchy based columns, that I can use on the BOATS table, as well as the CARS, PLANES and other tables?

Upvotes: 1

Views: 83

Answers (1)

Joel
Joel

Reputation: 2257

Assuming that each of your tables has a similar structure (i.e. every table has columns similar to rep, mgr, mgr2, mgr3, mgr4, etc). You could create a function that checks all of those columns dynamically if they exist and return either a true or false condition.

The following function takes in the value you want to search for - the PERSON_LOGGED_IN (assumed to be an integer key value for the purpose of the answer) and a table schema and name to look into (schema name is included to ensure duplicate tables names in different schemas don't conflict).

We then get a list of all columns in the specified table that match your set of criteria (rep, mgr, mgr2, etc). The benefit of this is that if you have different columns in different tables, you can specify all of them that could match and only those that actually exist in the table will be checked against.

After the matching columns are determined, then we just check the number of rows that match and return either a 1 or 0. I'm returning a numeric value because plain SQL doesn't recognize boolean values like PL/SQL does.

Your final query would become:

SELECT *
FROM BOATS
WHERE IN_HIERARCHY(PERSON_LOGGED_IN, 'SCHEMA.BOATS') = 1

And here's the function:

CREATE OR REPLACE FUNCTION IN_HIERARCHY (
    PERSON_LOGGED_IN IN PLS_INTEGER,
    TABLE_SCHEMA_AND_NAME IN VARCHAR2
) RETURN PLS_INTEGER
IS
    MATCHING_COLUMNS VARCHAR2(200);
    NUMBER_OF_ROWS_FOUND PLS_INTEGER := 0;
BEGIN
    select LISTAGG(COLUMN_NAME, ',') WITHIN GROUP (ORDER BY COLUMN_NAME)
    INTO MATCHING_COLUMNS
    from all_tab_columns
    where OWNER = regexp_substr(TABLE_SCHEMA_AND_NAME,'[^.]+', 1, 1)
    AND TABLE_NAME = regexp_substr(TABLE_SCHEMA_AND_NAME,'[^.]+', 1, 2)
    and COLUMN_NAME in ('REP', 'MGR', 'MGR2', 'MGR3', 'MGR4');

    EXECUTE IMMEDIATE
        'SELECT COUNT(*)
         FROM ' || TABLE_SCHEMA_AND_NAME || '
         WHERE :PERSON_LOGGED_IN IN (' || MATCHING_COLUMNS || ')'
    INTO NUMBER_OF_ROWS_FOUND
    USING PERSON_LOGGED_IN;

    IF NUMBER_OF_ROWS_FOUND > 0 THEN
        RETURN 1;
    ELSE
        RETURN 0;
    END IF;
END;
/

Side note: I don't personally know of a way to get the list of tables in the from clause from within this function based on the statement that's executing it, but if you can somehow find a way to determine that, you could theoretically also eliminate the table name argument from the function as well. However, it's probably not worth it and the actual where clause would likely be less clear to the reader.

Then again, as @kordirko pointed out, if you're really just interested in simplifying the query and making it more readable, then something like this would be a lot simpler:

SELECT *
FROM BOATS
WHERE PERSON_LOGGED_IN IN (REP, MGR, MGR2, MGR3, MGR4)

Upvotes: 1

Related Questions