Oracle PL/SQL for DBAs

Improving Performance

Let’s assume that our requirements have changed again. (That’s not surprising in a typical organization, is it?) Now I have to set up the policy in such a way that all employees and departments will be visible to a user who is a manager; otherwise, only the employees of the user’s department are visible. To accommodate this requirement, my policy function might look like this.

    CREATE OR REPLACE FUNCTION authorized_emps (
       p_schema_name   IN   VARCHAR2,
       p_object_name   IN   VARCHAR2
    )
       RETURN VARCHAR2
    IS
       l_deptno       NUMBER;
       l_return_val   VARCHAR2 (2000);
       l_mgr          BOOLEAN;
       l_empno        NUMBER;
       l_dummy        CHAR (1);
    BEGIN
       IF (p_schema_name = USER)
       THEN
          l_return_val := NULL;
       ELSE
          SELECT DISTINCT deptno, empno
                     INTO l_deptno, l_empno
                     FROM hr.emp
                    WHERE ename = USER;

          BEGIN
             SELECT '1'
               INTO l_dummy
               FROM hr.emp
              WHERE mgr = l_empno AND ROWNUM < 2;

             l_mgr := TRUE;
          EXCEPTION
             WHEN NO_DATA_FOUND
             THEN
                l_mgr := FALSE;
             WHEN OTHERS
             THEN
                RAISE;
          END;

          IF (l_mgr)
          THEN
             l_return_val := NULL;
          ELSE
             l_return_val := 'DEPTNO = ' || l_deptno;
          END IF;
       END IF;

       RETURN l_return_val;
    END;

Look at the complexity in selecting the data. This complexity will surely add to the response time (and, of course, in your real-world applications, the logic will be considerably more complex). Can I simplify the code and improve performance?

I certainly can. Look at the first requirement—checking to see if the employee is a manager. In the above code, we checked the EMP table for that information, but the fact that an employee is a manager ...