How to store values in the pl / sql procedure in the Oracle database
source link: https://www.codesd.com/item/how-to-store-values-in-the-pl-sql-procedure-in-the-oracle-database.html
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
How to store values in the pl / sql procedure in the Oracle database
I want to add values from select query to some variable/attribute after loop iteration. A value holder like Arraylist in Java.
After the loop terminates I need to delete the values in variable/attribute.
How to achieve this in pl/sql programming.
FOR FEH IN (SELECT ID FROM T_FEH_SG WHERE ZESG_ID IS NOT NULL) LOOP
FOR UWBFEH IN (SELECT * FROM T_GRPN_J_FERF WHERE FEH_ID = FEH.ID) LOOP
// Here some code to add values from select query based on some conditions
END LOOP;
// Here I need to delete the added values.
END LOOP;
Here if I delete the stored values then the values should be deleted from the actual table as well.
Since you actually don't need any data from the T_FEHLERFALL_SG table, you could just get rid of the first loop making a single SELECT (using JOIN, EXISTS or IN, depending on the data you're fetching).
There's also an important question to be answered - do you need to perform any logic before inserting the data into the array? What are you planning to do with the values stored in the array before clearing it?
Anyway, here are a few options you might find useful (also, I used the declared CURSOR, since it provides better code readability and as far as I know, it's better managed by the optimizers and can be reused quickly fetching the result from SGA - you can read also about Result cache and Oracle Concepts - SGA).
DECLARE
TYPE t_uwbfeh_tab IS TABLE OF T_UWB_GRUPPEN_J_FEHLERF%ROWTYPE INDEX BY PLS_INTEGER; -- empirically learned (and from our DB Oracle consultant) arrays indexed by PL/SQL types (especially PLS_INTEGER) react noticeably faster even for a ~1k-element arrays
-- declare the PL/SQL TABLE variable and initialize it
l_uwbfeh_arr t_uwbfeh_tab := NEW t_uwbfeh_tab();
CURSOR c_fetch_data
SELECT
UWBFEH.*
FROM
T_FEHLERFALL_SG FEH
,T_UWB_GRUPPEN_J_FEHLERF UWBFEH
WHERE
FEH.ZESG_ID IS NOT NULL
AND FEH.ID = UWBFEH.FEHLERF_ID;
BEGIN
-- If you don't need to do anything with the data in the array before inserting it into the array
OPEN c_fetch_data;
FETCH c_fetch_data BULK COLLECT INTO l_uwbfeh_arr;
CLOSE c_fetch_data;
-- If you really need to do some logic not doable with the 'smart' select statement
FOR UWBFEH IN c_fetch_data
LOOP
-- extend an array - let it store one more value
l_uwbfeh_arr.EXTEND;
-- do some logic with your values (here I just print out the FEHLERF_ID)
DBMS_OUTPUT.PUT_LINE('FEHLERF_ID: ' || UWBFEH.FEHLERF_ID);
-- insert the value into the array
l_uwbfeh_arr(l_uwbfeh_arr.LAST) := UWBFEH;
END LOOP;
-- If you need to do something with the data fetched in the array
IF l_uwbfeh_arr.COUNT > 0 THEN
FOR idx IN l_uwbfeh_arr.FIRST .. l_uwbfeh_arr.LAST
LOOP
-- access the rows as follows (here I just print out the FEHLERF_ID)
DBMS_OUTPUT.PUT_LINE('FEHLERF_ID: ' || l_uwbfeh_arr(idx).FEHLERF_ID);
END LOOP;
END IF;
-- If you don't actually need any data to be stored in the array and need to manipulate only on the fetched rows
-- you could skip fetching the data into the array and execute the logic in the following loop
FOR UWBFEH IN c_fetch_data
LOOP
-- here do the logic (here I just print out the FEHLERF_ID)
DBMS_OUTPUT.PUT_LINE('FEHLERF_ID: ' || UWBFEH.FEHLERF_ID);
END LOOP;
-- to delete all the data stored in the array, simply use the following line of code
l_uwbfeh_arr.DELETE;
END;
/
Recommend
About Joyk
Aggregate valuable and interesting links.
Joyk means Joy of geeK