Temporary Table in PLSQL
- 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