1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

how do i call this function

Discussion in 'SQL PL/SQL' started by Mohamed_Khateeb, Mar 16, 2014.

  1. Mohamed_Khateeb

    Mohamed_Khateeb Active Member

    Messages:
    6
    Likes Received:
    0
    Trophy Points:
    55
    i made a function contains trunc(date) between from_date and To_date

    how can i execute this function and pass two dates
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Without knowing HOW you wrote the function I can't tell you how to call it. I can give an example, though, of how, in general, to call a function:


    Code (SQL):

    SQL> --
    SQL> -- Create the function
    SQL> --
    SQL> CREATE OR REPLACE FUNCTION days_bet_dts(pdt1 IN DATE, pdt2 IN DATE)
      2  RETURN NUMBER
      3  IS
      4          vdays   NUMBER:=0;
      5  BEGIN
      6          vdays := trunc(greatest(pdt1, pdt2)) - trunc(least(pdt1, pdt2));
      7
      8          RETURN vdays;
      9  END;
     10  /


    FUNCTION created.


    SQL>
    SQL> --
    SQL> -- Show any errors
    SQL> --
    SQL> SHOW errors



    No errors.



    SQL>
    SQL> --
    SQL> -- Call function from SQL*Plus
    SQL> --
    SQL>
    SQL> SELECT days_bet_dts(sysdate, sysdate-40) FROM dual;


    DAYS_BET_DTS(SYSDATE,SYSDATE-40)
    --------------------------------
                                  40


    SQL>
    SQL> --
    SQL> -- Call function from PL/SQL
    SQL> --
    SQL> DECLARE
      2          v_days NUMBER:=0;
      3  BEGIN
      4          v_days:=days_bet_dts(to_date('01/01/0001', 'MM/DD/YYYY'), to_date('10/31/1517','MM/DD/YYYY'));
      5
      6          dbms_output.put_line('Days between the two dates; '||v_days);
      7  END;
      8  /
    Days BETWEEN the two dates; 554022


    PL/SQL PROCEDURE successfully completed.


    SQL>
     
     
  3. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    Hi.
    Give an example of the necessary output.... write, what result needs to be received

    to check period entrances during the period?

    Code (SQL):


    CREATE OR REPLACE FUNCTION fn_check_period
    (p_check_date1 DATE ,
     p_check_date2 DATE,
     p_period_from  DATE,
     p_period_to    DATE
    )
    RETURN varchar2
    AS
    BEGIN
         RETURN CASE WHEN p_check_date1>=p_period_from AND p_check_date2<=p_period_to THEN 'Checked' ELSE 'No checked' END;
    END;
    /
    SET serveroutput ON
    BEGIN
      dbms_output.put_line(fn_check_period(DATE '2013-01-01',DATE '2013-12-01',DATE '2013-01-01',DATE '2013-12-10') );
    END ;
    /

    SQL>

    Checked

     
     
  4. Mohamed_Khateeb

    Mohamed_Khateeb Active Member

    Messages:
    6
    Likes Received:
    0
    Trophy Points:
    55
    Thanks All for your Support, I do it