Function using dateadd to return status pl sql

Question

I have a table named Borrowing with columns id, borrow_date and duration. I want to create a function to check the status. If someone wants to borrow the book, they give 2 parameters (v_book and v_date). v_book is the book id they want to borrow and v_date is the date they want to borrow. This function checks whether the book can be borrowed or not. Example, user input v_book=100, and v_date='5-Jan-2020'. But in the table, the book with id 100, the borrow_date is '4-Jan-2020' and the duration is 3 days. So January 4th plus 3 days is January 7th. So that means the book cannot be borrowed by January 5th. This is my code so far and I still got an error in the dateadd. I need to write the function using Oracle PL/SQL. Any idea? Thanks!

CREATE OR REPLACE FUNCTION check_status (v_book INT, v_date DATE) RETURN VARCHAR2;
v_duration INT;
v_borrow date;
BEGIN
SELECT duration INTO v_duration FROM Borrowing WHERE id = v_book;
SELECT borrow_date INTO v_borrow FROM Borrowing WHERE id = v_book;
SELECT DATEADD(day, v_duration, v_borrow) AS DateAdd;
IF(v_date<DateAdd) THEN
RETURN 'False';
ELSE RETURN 'True';
END IF;
END;
/
DECLARE
m_book INT:=205;
m_date DATE:='5-JAN-2020';
BEGIN
if(check_status(m_book,m_date)='True') then
dbms_output.put_line('You may borrow the book');
else then dbms_output.put_line('book not available');
END;
/

Unrelated, but: the first two SELECT statements can be combined into one — user330315
– user330315, Commented Dec 4, 2020 at 12:51

Boneist · Accepted Answer · 2020-12-04 12:13:59Z

1

dateadd isn't an Oracle function.

If you want to add days to a date in Oracle, you simply add the number of days to the date.

E.g. 2 days from now would be sysdate + 2.

N.B. if you are assigning a date to a DATE variable, please explicitly convert strings into dates first, e.g.

m_date DATE := to_date('05/01/2020', 'dd/mm/yyyy');

By forcing a string into a DATE variable, you're forcing an implicit conversion, which uses the NLS_DATE_FORMAT parameter of your session as the format of your string, e.g. Oracle will do the following behind the scenes:

m_date DATE := to_date('5-JAN-2020', <NLS_DATE_FORMAT>);

If your NLS_DATE_FORMAT doesn't match the string you've passed in, you'll get an error. By explicitly converting, you've made your code able to run on any session, regardless of the NLS_DATE_FORMAT setting.

answered Dec 4, 2020 at 12:13

Boneist

23.6k1 gold badge28 silver badges42 bronze badges

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

Comments

MT0 · Accepted Answer · 2020-12-04 12:48:23Z

Apart from the many syntax errors, your function will not work as a book can be borrowed many times and you need to check all the times that it has been borrowed to make sure none of them overlap with the instant you want to start borrowing.

You want to check something like this:

CREATE OR REPLACE FUNCTION check_status (
  v_book BORROWING.ID%TYPE,
  v_date DATE
) RETURN VARCHAR2
IS
  v_is_borrowed NUMBER(1,0);
BEGIN
  SELECT COUNT(*)
  INTO   v_is_borrowed
  FROM   Borrowing
  WHERE  id = v_book
  AND    borrow_date <= v_date
  AND    borrow_date + duration > v_date;

  RETURN CASE v_is_borrowed WHEN 0 THEN 'True' ELSE 'False' END;
END;
/

Which, for the sample data:

CREATE TABLE borrowing( id, borrow_date, duration ) AS
SELECT 205, DATE '2020-01-01', 31 FROM DUAL UNION ALL
SELECT 205, DATE '2020-02-10', 10 FROM DUAL;

Then:

BEGIN
  IF check_status( 205, DATE '2020-01-05' ) = 'True' THEN
    dbms_output.put_line('You may borrow the book');
  ELSE
    dbms_output.put_line('book not available');
  END IF;
END;
/

Outputs:

book not available

db<>fiddle here

Popeye · Accepted Answer · 2020-12-04 12:57:35Z

0

You can simplify your function code as follows:

CREATE OR REPLACE FUNCTION check_status (v_book INT, v_date DATE) RETURN VARCHAR2
IS -- this was missing in your code
v_cnt number:= 0;
BEGIN
SELECT count(1)
INTO v_cnt
 FROM Borrowing
WHERE id = v_book
AND v_date between borrow_date and borrow_date + duration ;

IF(cnt > 0) THEN
RETURN 'False';
ELSE RETURN 'True';
END IF;
END;
/

Also, you can call this function using the SELECT query as follows:

Select check_status(205, date'2020-01-05')
From dual;

Please note how dates are created in oracle.

edited Dec 4, 2020 at 12:57

answered Dec 4, 2020 at 12:44

Popeye

36k4 gold badges12 silver badges31 bronze badges

4 Comments

MT0 Over a year ago

This doesn't work if v_date is 2020-01-10 and there is a reservation for the book starting from 2020-02-01 as you never check the lower bound of the reservation, only the upper bound.

Popeye Over a year ago

Lower bound can not be checked as there is no duration available with function as an input for checking status.

MT0 Over a year ago

Of course you can check it, you check if v_date is between borrow_date and borrow_date + duration. There does not need to be an input duration if the OP is asking about a particular time instant; they may want to extend their function to include that but it is not what the question is asking.

Popeye Over a year ago

And what if status check is for starting with v_date to lets sqy 100 daya(duration). I think function itself should have duration as an input parameter else it will be logically incorrect and produces bugs.. but yes, I have updated answer according to your suggestion. Thanks..

Abra · Accepted Answer · 2020-12-04 13:12:41Z

Create the database table.

create table BORROWING (ID number(3), BORROW_DATE date, DURATION number(2));

Insert a sample row.

insert into BORROWING values (100, to_date('04-01-2021','DD-MM-YYYY'), 3);

Create the function. (I changed the names and types slightly.)

create or replace function CHECK_STATUS(P_BOOK BORROWING.ID%type,
                                        P_DATE BORROWING.BORROW_DATE%type)
return boolean
is
  L_DUMMY number(1);
begin
  select 1
    into L_DUMMY
    from BORROWING
   where ID = P_BOOK
     and P_DATE between BORROW_DATE and (BORROW_DATE + DURATION);
  return false;
exception
  when NO_DATA_FOUND then
    return true;
end;
/

If the desired borrow date for the desired book falls within a period where the book is already borrowed, then the function returns false.

Test the function. (Again changed the names and types.)

declare
  L_BOOK BORROWING.ID%type;
  L_DATE BORROWING.BORROW_DATE%type;
begin
  L_BOOK := 100;
  L_DATE := to_date('05-01-2021','DD-MM-YYYY');
  if CHECK_STATUS(L_BOOK, L_DATE) then
    DBMS_OUTPUT.PUT_LINE('You may borrow the book.');
  else
    DBMS_OUTPUT.PUT_LINE('Book not available.');
  end if;
end;
/

Of-course the function only checks that the book is available on the intended borrow date. The function does not check whether the book can be borrowed for the intended duration. For that, you would need to check the intended duration also.

Refer to this db<>fiddle

Barbaros Özhan · Accepted Answer · 2020-12-04 14:46:30Z

You can put all statements into one SELECT Statement during the creation of the function

CREATE OR REPLACE FUNCTION check_status(
                                        i_book Borrowing.Id%type, 
                                        i_date Borrowing.Borrow_Date%type
                                       )
  RETURN VARCHAR2 IS
    o_borrowed VARCHAR2(5);
BEGIN
  SELECT DECODE(SIGN(COUNT(*)),0,'True','False')
    INTO v_borrowed
    FROM Borrowing
   WHERE id = i_book
     AND i_date BETWEEN borrow_date AND borrow_date + duration - 1;
   
  RETURN o_borrowed;   
END;
/

and then revoke such as

DECLARE
  v_book     Borrowing.Id%type := 205;
  v_date     Borrowing.Borrow_Date%type := date'2020-01-05';
BEGIN
  IF check_status(v_book, v_date) = 'True' THEN
    dbms_output.put_line('You may borrow the book');
  ELSE
    dbms_output.put_line('book not available');
  END IF;
END;
/

where there's no predefined function called DATEADD() in Oracle database.

Alternatively, you can create a PROCEDURE as

CREATE OR REPLACE PROCEDURE check_status(
                                         i_book     Borrowing.Id%type, 
                                         i_date     Borrowing.Borrow_Date%type,
                                         o_borrowed OUT VARCHAR2
                                        ) IS
BEGIN
  SELECT DECODE(SIGN(COUNT(*)),0,'You may borrow the book','book not available')
    INTO o_borrowed
    FROM Borrowing
   WHERE id = i_book
     AND i_date BETWEEN borrow_date AND borrow_date + duration - 1;   
END;
/

and print the description which you want out directly as

DECLARE
  v_book     Borrowing.Id%type := 205;
  v_date     Borrowing.Borrow_Date%type := date'2020-01-05';
  v_borrowed VARCHAR2(50);
BEGIN
  check_status(v_book, v_date,v_borrowed);
  dbms_output.put_line(v_borrowed);
END;
/

Collectives™ on Stack Overflow

Function using dateadd to return status pl sql

5 Answers 5

Comments

Comments

4 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

Comments

4 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related