CODEWITHSUNDEEP
oracle-databaseplsqlrecord
Pirogov
Pirogov

Reputation: 37

PL/SQL Records. Can i put in records few values?

I'm working in Apex Oracle PL/SQL and trying to understand how records work. There is piece of code. I can't understand why i'm getting exception "no data found". And can i put few rows in records?

create table testProducts 
    (
        prod_id number not null,
         group_id number (5) not null,
        prod_name varchar2(50) not null,
        constraint fk_group_id foreign key (group_id)
        references testGroups(group_id)
    )



declare
    type mr is record (
        prod_Id testProducts.prod_Id%type
    );
    obj_mr mr;

    maxd number;

egin
    select max(prod_Id) into maxd from testProducts;
    for i in 10..maxd loop
        select testProducts.prod_id into obj_mr from testProducts where 
        testProducts.prod_Id = i;
        dbms_output.put_line(obj_mr.prod_id);
       
    end loop;
end;

Upvotes: 0

Views: 40

Answers (1)

EJ Egyed
EJ Egyed

Reputation: 6094

To resolve your immediate issues, you are getting a no data found error because you are starting looping at prod_id 10 and going up sequentially to the maximum prod_id that exists in the table. If you have any gaps in your IDs then you will get the error. You can resolve it with some error handling like this:

DECLARE
    TYPE mr IS RECORD
    (
        prod_Id      testProducts.prod_Id%TYPE,
        GROUP_ID     testProducts.GROUP_ID%TYPE,
        prod_name    testProducts.prod_name%TYPE
    );
    
    obj_mr   mr;

    maxd     NUMBER;
BEGIN
    SELECT MAX (prod_Id) INTO maxd FROM testProducts;

    FOR i IN 10 .. NVL (maxd, 1)
    LOOP
        BEGIN
            SELECT prod_id, GROUP_ID, prod_name
              INTO obj_mr
              FROM testProducts
             WHERE prod_Id = i;

            DBMS_OUTPUT.put_line (obj_mr.prod_id || '|' || obj_mr.GROUP_ID || '|' || obj_mr.prod_name);
        EXCEPTION
            WHEN NO_DATA_FOUND
            THEN
                DBMS_OUTPUT.put_line ('prod_id does not exist: ' || i);
        END;
    END LOOP;
END;
/

To answer your question about multiple rows, no a RECORD type can not have multiple rows, but a TABLE type can. If you define a TABLE type of your RECORD type, you can then select multiple rows in to that TABLE type. See the code below for an example of how to do that.

DECLARE
    TYPE mr IS RECORD
    (
        prod_Id      testProducts.prod_Id%TYPE,
        GROUP_ID     testProducts.GROUP_ID%TYPE,
        prod_name    testProducts.prod_name%TYPE
    );

    TYPE mr_t IS TABLE OF mr;

    obj_mr_t   mr_t;
BEGIN
      SELECT prod_id, GROUP_ID, prod_name
        BULK COLLECT INTO obj_mr_t
        FROM testProducts
    ORDER BY prod_id;

    FOR i IN 1 .. obj_mr_t.COUNT
    LOOP
        DBMS_OUTPUT.put_line (
            obj_mr_t (i).prod_id || '|' || obj_mr_t (i).GROUP_ID || '|' || obj_mr_t (i).prod_name);
    END LOOP;
END;
/

Upvotes: 2

Related Questions