CODEWITHSUNDEEP
oracle-databaseplsqlrecord
Ed_
Ed_

Reputation: 69

PL SQL Record Operations

Hi I am working on an academic assignment and need some help setting up records:

Write a PL/SQL block to print information about a publisher.

  • Declare a PL/SQL record based on the structure of the bk_publishers table.
  • In the declarative section, use the %ROWTYPE attribute and declare the variable publisher_record of type bk_publisher.
  • In the executable section, get all the information from the bk_publishers table by using publ_id and put it in your record. Display the publ_id and publ_name from the record using a cursor for loop.

Reference Database Chart

So far I have been able to write a block that outputs the contents, but I don't know how to get the contents of the record to print.

Any insights would be very helpful! Thanks

SET SERVEROUTPUT ON
SET VERIFY OFF


DECLARE
TYPE bk_record IS RECORD
    (publ_id bk_publishers.publ_id%TYPE, 
    publ_name bk_publishers.publ_name%TYPE);
publisher_record bk_publishers%ROWTYPE;

CURSOR bk_cur IS
    SELECT * FROM bk_publishers;

BEGIN

    OPEN bk_cur;
    FETCH bk_cur INTO publisher_record;
    CLOSE bk_cur;

    FOR publ_no in bk_cur
    LOOP
        DBMS_OUTPUT.PUT_LINE(publ_no.publ_id || ' ' || publ_no.publ_name);
    END LOOP;

END;
/

Upvotes: 0

Views: 193

Answers (1)

Kaushik Nayak
Kaushik Nayak

Reputation: 31676

A simple RECORD variable can hold the contents of a single row, so you have to display column values of individual rows within a loop.

DECLARE
     TYPE bk_record IS RECORD ( publ_id  bk_publishers.publ_id%TYPE,
     publ_name          bk_publishers.publ_name%TYPE );
     publisher_record   bk_publishers%rowtype;
     CURSOR bk_cur IS SELECT *
                      FROM bk_publishers;
BEGIN
     OPEN bk_cur;
     LOOP
          FETCH bk_cur INTO publisher_record;
          EXIT WHEN bk_cur%notfound;  --Condition to exit the loop.
          dbms_output.put_line(publisher_record.publ_id 
                     || ' ' || publisher_record.publ_name);
     END LOOP;

     CLOSE bk_cur;
END;
/

Upvotes: 1

Related Questions