Need this to run and look like this output. Please Help me

set SERVEROUTPUT ON;
DECLARE
    FirstName Customers.first_name%TYPE;
    SurName Customers.surname%TYPE;
    PetP pets.pet_price%TYPE;
    SalesQty Pet_Sales.sales_quantity%TYPE;
    
    Cursor info_info is
    select Customer.First_Name, Customer.Surname,PetP.pet_type, PetS.Sales_Quantity
        from Customers Customer , Pets PetP, Pet_Sales PetS
        where Customer.Customer_ID = PetS.Customer_ID
        and PetP.Pet_ID = PetS.Pet_ID
        and petp.pet_price >=1000;
Begin
    for rec in info_info
        loop
            FirstName := rec.First_Name;
            SurName := rec.Surname;
            PetP := rec.pet_price;
            SalesQty := rec.Sales_Quantity;
            dbms_output.put_line ('First Name:'||FirstName);
            dbms_output.put_line ('Surname: '||SurName);
            dbms_output.put_line ('Amount '||SalesQty||' '||PetPrice);
            dbms_output.put_line ('-------------------------');
        end loop;
end;

1 answer

  • answered 2021-10-12 18:47 Littlefoot

    Well, with slight modifications, your code actually returns expected output - at least, I think so. I don't know what you mean by saying that it doesn't (you should explain it).

    You don't have to declare any variables - use cursor variable and column names returned by the select statement.

    As you didn't provide any sample data, I'll presume that your query returns correct result and use hardcoded values (just to make cursor return something):

    SQL> set serveroutput on;
    SQL> declare
      2    /* Presuming your query returns correct result, I won't change it.
      3       I'm commenting it out, though, as I don't have your tables. I'll use
      4       hardcoded values instead
      5
      6    Cursor info_info is
      7      select Customer.First_Name, Customer.Surname,PetP.pet_type, PetS.Sales_Quantity
      8          from Customers Customer , Pets PetP, Pet_Sales PetS
      9          where Customer.Customer_ID = PetS.Customer_ID
     10          and PetP.Pet_ID = PetS.Pet_ID
     11          and petp.pet_price >=1000;
     12    */
     13    cursor info_info is
     14      select 'Patrick' first_name, 'Smith' surname,  700 amount from dual union all
     15      select 'Steven'            , 'Hewson'       ,  570        from dual union all
     16      select 'Barry'             , 'Goodwin'      , 2500        from dual;
     17  begin
     18    for rec in info_info loop
     19      dbms_output.put_line ('First Name: '|| rec.first_name);
     20      dbms_output.put_line ('Surname   : '|| rec.surname);
     21      dbms_output.put_line ('Amount    : '|| rec.amount);
     22      dbms_output.put_line ('-------------------------');
     23    end loop;
     24  end;
     25  /
    

    which results in

    First Name: Patrick
    Surname   : Smith
    Amount    : 700
    -------------------------
    First Name: Steven
    Surname   : Hewson
    Amount    : 570
    -------------------------
    First Name: Barry
    Surname   : Goodwin
    Amount    : 2500
    -------------------------
    
    PL/SQL procedure successfully completed.
    
    SQL>
    

How many English words
do you know?
Test your English vocabulary size, and measure
how many words do you know
Online Test
Powered by Examplum