Writing a PLSQL Oracle function to query a table

Question

I have the following table

CREATE TABLE Book
(
book_id      INTEGER NOT NULL ,
isbn         VARCHAR2 (20) NOT NULL,
tittle       VARCHAR2 (100) NOT NULL ,
shelf_letter CHAR (1) NOT NULL ,
call_number  INTEGER ,
no_of_copies INTEGER NOT NULL ,

) ;

I need to write a function to retrieve book_id, title,call_number, shelf_letter, no_of_copies for a given isbn.

Input parameters: isbn

Output parameters: title, no_of_copies,call_number,shelf_letter.

Return book_id if the query is successful and -1 if not.

How can I properly write this function?

Answer 1

create OR replace FUNCTION get_book_id 
(
  p_isbn            IN VARCHAR2
, po_title          OUT VARCHAR2
, po_no_of_copies   OUT NUMBER
, po_call_number    OUT NUMBER
, po_shelf_letter   OUT NUMBER
)
RETURN NUMBER
IS
  v_book_id NUMBER;
BEGIN
  BEGIN 
    SELECT 
        book_id
      , title         
      , no_of_copies  
      , call_number   
      , shelf_letter  
    INTO 
        v_book_id
      , po_title         
      , po_no_of_copies  
      , po_call_number   
      , po_shelf_letter  
    FROM book
    WHERE isbn = 'p_isbn'
    ;
  EXCEPTION 
    WHEN NO_DATA_FOUND THEN
    v_book_id := -1;
  END; 

  RETURN v_book_id;
END;
/

Answer 2

DECLARE
    TYPE book_info_rec IS RECORD
    (
        book_id      NUMBER(1)
    ,   title        VARCHAR2(30)
    ,   call_number  NUMBER(1)
    ,   shelf_letter VARCHAR2(30)
    ,   no_of_copies NUMBER(1)
    );

    l_book  book_info_rec;

    FUNCTION get_book_info(isbn_in IN VARCHAR2) RETURN book_info_rec
    AS
        l_book_info  book_info_rec;
    BEGIN
        SELECT  1
        ,       'A Book'
        ,       2
        ,       'A'
        ,       3
        INTO    l_book_info
        FROM    DUAL
        WHERE   dummy = isbn_in;

        RETURN l_book_info;
    END;
BEGIN
    l_book := get_book_info('X');

    DBMS_OUTPUT.PUT_LINE
    (
              l_book.book_id
    || ' ' || l_book.title
    || ' ' || l_book.call_number
    || ' ' || l_book.shelf_letter
    || ' ' || l_book.no_of_copies
    );
END;