1

How to store values ​​in the pl / sql procedure in the Oracle database

 2 years ago
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.
neoserver,ios ssh client

How to store values ​​in the pl / sql procedure in the Oracle database

advertisements

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;
/


About Joyk


Aggregate valuable and interesting links.
Joyk means Joy of geeK