top of page

Temporary Table in PLSQL

  • Writer: Rumesh Aponso (RMAX)
    Rumesh Aponso (RMAX)
  • Nov 22, 2024
  • 1 min read
DECLARE
   --table row type
   TYPE customer_order_tab_type IS TABLE OF customer_order_tab%ROWTYPE INDEX BY BINARY_INTEGER;
   customer_order_tab_ customer_order_tab_type;
   
   -- table column type
   TYPE customer_no_type IS TABLE OF customer_order_tab.customer_no%TYPE INDEX BY BINARY_INTEGER;
   customer_no_type_tab_ customer_no_type;
   
   n BINARY_INTEGER := 0;
   
   CURSOR get_order_details IS
      SELECT *
      FROM   customer_order_tab
      WHERE  ROWNUM <= 10;
      
   CURSOR get_order_cust_name_details IS
      SELECT customer_no
      FROM   customer_order_tab
      WHERE  ROWNUM <= 10;
   
   CURSOR get_order_details_2 IS
      SELECT *
      FROM   customer_order_tab
      WHERE  ROWNUM <= 20;
      
   CURSOR get_order_cust_name_details_2 IS
      SELECT customer_no
      FROM   customer_order_tab
      WHERE  ROWNUM <= 20;
BEGIN
   n := 0;
   FOR rec_ IN get_order_details
   LOOP
      n := n + 1;
      customer_order_tab_(n) := rec_;
      Dbms_Output.Put_Line('order_no = ' || rec_.order_no);
   END LOOP;
   
   n := 0;
   FOR rec_ IN get_order_cust_name_details
   LOOP
      n := n + 1;
      customer_no_type_tab_(n) := rec_.customer_no;
      Dbms_Output.Put_Line('customer_no = ' || rec_.customer_no);
   END LOOP;
   
   -- ename_tab.DELETE(3);       -- delete element 3
   -- ename_tab.DELETE(5, 5);    -- delete element 5
   -- ename_tab.DELETE(20, 30);  -- delete elements 20 through 30
   -- ename_tab.DELETE(-15, 0);  -- delete elements -15 through 0
   -- ename_tab.DELETE;          -- delete entire PL/SQL table
   customer_order_tab_.delete();
   customer_no_type_tab_.delete();
   
   n := 0;
   FOR rec_ IN get_order_details_2
   LOOP
      n := n + 1;
      customer_order_tab_(n) := rec_;
      Dbms_Output.Put_Line('order_no = ' || rec_.order_no);
   END LOOP;
   
   n := 0;
   FOR rec_ IN get_order_cust_name_details_2
   LOOP
      n := n + 1;
      customer_no_type_tab_(n) := rec_.customer_no;
      Dbms_Output.Put_Line('customer_no = ' || rec_.customer_no);
   END LOOP;
END;

Comments


Copyright © 2025 RMAXOneNote

  • Online CV
  • LinkedIn
  • Youtube
  • GitHub
  • Blogger
bottom of page