Infrastructure Software

Also asked at OraFAQ Forum: SQL & PL/SQL » Creating a function

Hi,

Odigitrium wrote:
I ask for help. Can not figure out what and how.
Often needs to use small 'select' that return any from the database table.
Needs a function that will output the result of the simple query. Usually these are simple selections with one variable (as below).
For example, in the current query need's to be able to substitute sydate by variable. And the big request to help to write a correct function for such query.
I would like to call the function something like: get_empnos2('10-sep-18')
CREATE OR REPLACE FUNCTION get_empnos2
 RETURN SYS_REFCURSOR
IS
 l_rc SYS_REFCURSOR;
BEGIN
 OPEN l_rc
 FOR select 
 next_day(sysday - (level - 1) * 7, 'SUN' ) + 1 - 14 BEGIN_OF_WEEK 
 ,next_day(sysday - (level - 1) * 7, 'SUN' ) END_OF_WEEK 
from dual 
connect by level <= 3;
 RETURN l_rc;
END;

You can add an argument to the function like this:

CREATE OR REPLACE FUNCTION get_empnos2
(  dt    DATE  DEFAULT SYSDATE
)
RETURN SYS_REFCURSOR
--  get_empnos2 finds the first and last days of 3
--  overlapping periods, each 2 weeks long (Monday to Sunday)
--  starting 1 week apart.  The periods are returned in
--  descending order (that is, latest dates first), and
--  the first period returned includes the given dt.
IS
    l_rc   SYS_REFCURSOR;
BEGIN
    OPEN l_rc  FOR
        SELECT  NEXT_DAY ( dt - ((LEVEL - 1) * 7)
                         , 'SUN'
                         ) - 13  AS begin_period
        ,       NEXT_DAY ( dt - ((LEVEL - 1) * 7)
                         , 'SUN'
                         )       AS end_period  
        FROM    dual 
        CONNECT BY  LEVEL <= 3;
    RETURN  l_rc;
END  get_empnos2;
/

When I call the function like this:

VARIABLE  r  REFCURSOR
EXEC  :r := get_empnos2 (DATE '2018-09-01');
PRINT  :r

I get these results:

BEGIN_PERIOD END_PERIOD
------------ ------------
20-Aug-2018  02-Sep-2018
13-Aug-2018  26-Aug-2018
06-Aug-2018  19-Aug-2018

I don't know how you plan to use this function, but you almost certainly want the arguent to be a DATE, not a string like '10-sep-18'.

The function above has a default value for the argument, so you can call it with or without an argument.

:r := get_empnos2;

does exactly the same thing as

:r := get_empnos2 (SYSDATE);

As written, the function depends on your NLS settings.  You can fix that by using TRUNC instead of NEXT_DAY.

Put some comments in your function, describing what it does.  The comments I wrote describe what your query actually does.  Do they describe what you wanted the query to do?

First of all '10-sep-18' is string, not date and results in implicit conversion and might fail depending on session NLS_DATE_FORMAT. Also, 'SUN' is also NLS dependent. Also, it is not clear what you are trying to return. I'll assume you want to get beginning and end dates for the week date belongs to and two previous weeks where week starts Monday. If so:

CREATE OR REPLACE

  FUNCTION get_empnos2(

                       p_dt DATE

                      )

    RETURN SYS_REFCURSOR

    IS

        l_rc SYS_REFCURSOR;

    BEGIN

        OPEN l_rc

          FOR select  trunc(p_dt - (level - 1) * 7,'IW')     BEGIN_OF_WEEK,

                      trunc(p_dt - (level - 1) * 7,'IW') + 6 END_OF_WEEK

                from  dual

                connect by level <= 3;

        RETURN l_rc;

END;

/

Function created.

SQL> variable v_cur refcursor

SQL> exec :v_cur := get_empnos2(date '2018-09-10')

PL/SQL procedure successfully completed.

SQL> print v_cur

BEGIN_OF_ END_OF_WE

--------- ---------

10-SEP-18 16-SEP-18

03-SEP-18 09-SEP-18

27-AUG-18 02-SEP-18

SQL>

SY.

It would help if you told us what you tried and what didn't work. (It would also help to know what you plan to do with this function... having the end of week on the Sunday that is 13 days later than the Monday labeled begin of week, instead of 6 days later, is pretty odd... but only you know if that is a mistake or if you really meant that.)

In principle, your question is easy:  The function declaration should be get_empnos2(p_date date) - although, if the function returns a refcursor pointing to a table with two columns, begin of week and end of week, why is the function called get_empnos2? What does your function have to do with employee numbers?

Then write p_date everywhere in the function body instead of sysdate.

And then, there is the issue of how you CALL the function. If you want the function to take a date variable (instead of sysdate) then you must call the function with a date input, not with a string input. This is the very common issue of using the proper data type. '10-sep-18' is a string, not a date. Call the function like this:

get_empnos2(to_date('10-sep-18', 'dd-mon-yy'))

Or, better yet, call it with to_date('10-sep-2018', 'dd-mon-yyyy'))  -   get in the habit of always using four-digit year, never two-digit.

Needs a function that will output the result of the simple query. Usually these are simple selections with one variable (as below).

Ok - then the FIRST STEP is to read the Oracle doc about functions.

Have you done that step yet?

Just do a SIMPLE search for 'oracle 12c function' and you will get links to the Oracle doc that explains:

1. what functions are

2. how to create them

3. how to use them

4. examples that you can try

Once you have done that if you still have questions or a problem then let us know:

WHAT you did

HOW you did it

WHAT results you got