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?

I'm new to plsql and I'm trying to solve this. I didn't understand what an Output parameter meant. I know what a traditional Function is (in C & Java) which takes 0 or more input parameters and return a value or just void. But I'm trying to get my head around PLSQL functions. Thanks! — user6123723
– user6123723, Commented Oct 1, 2013 at 8:23
Maybe this can help you start docs.oracle.com/cd/E11882_01/appdev.112/e25519/… . When you'll have a specific problem, we'll all be glad to help — A.B.Cade
– A.B.Cade, Commented Oct 1, 2013 at 8:33

schurik · Accepted Answer · 2013-10-01 08:38:43Z

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

answered Oct 1, 2013 at 8:38

schurik

7,9582 gold badges26 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

the_slk Over a year ago

"Do not use OUT and IN OUT for function parameters." Oracle Database PL/SQL Language Reference: docs.oracle.com/cd/E11882_01/appdev.112/e25519/…

A.B.Cade Over a year ago

@schurik, it's not "so bad" but this is the meaning of a function - "get some inputs and return one". This allows it to be called in a sql statement for example, if you add an OUT param it cannot sqlfiddle.com/#!4/a62d7/1 . You could however do this in a procedure

the_slk · Accepted Answer · 2013-10-01 08:43:33Z

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;

Collectives™ on Stack Overflow

Writing a PLSQL Oracle function to query a table

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related