Extract(Read) > Transform(Test&Apply) > Load(Learn) Blog

October 26, 2008

Qualifying PL/SQL code example to improve readability and NOT to invalidate

Filed under: Oracle How To — H.Tonguç Yılmaz @ 8:27 pm

-- a good example for qualifying
PACKAGE scope_demo
IS
   g_global   NUMBER;

   PROCEDURE set_global (number_in IN NUMBER);
END scope_demo;

PACKAGE BODY scope_demo
IS
   PROCEDURE set_global (number_in IN NUMBER)
   IS
      l_salary  NUMBER := 10000;
     l_count   PLS_INTEGER;
   BEGIN

      <<local_block>>
      DECLARE
         l_inner   PLS_INTEGER;
      BEGIN
         SELECT COUNT (*)
           INTO set_global.l_count
           FROM employees e
          WHERE e.department_id = local_block.l_inner
            AND e.salary > set_global.l_salary;
      END local_block;

      scope_demo.g_global := set_global.number_in;
   END set_global;
END scope_demo; 

For a closer look of the advantages of qualifying please check Qualifying All References Thread on Best Practice PL/SQL with Steven Feuerstein of OTN.

The Rubric Theme. Create a free website or blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 80 other followers