how to create procedure/function from sql query using oracle?

Question

i have an interesting situation

here is my code

SELECT :dateSend, t.senderPostindex, t.recipientPostindex, d.shipment_days FROM TABLE t
INNER JOIN shipment_days d on d.first_index = t.senderIndex and d.second_index = t.recipientPostindex
WHERE t.senderPostindex = variablesenderPostindex AND t.recipientPostindex = variablerecipientPostindex

Basically, i need procedure like getShipmentDays('01001', '02031', '2020/05/09 15:40:00') and it have to return me

• dateSend: 2020/05/09 15:40:00
• senderPostindex: 01001
• recipientPostindex: 02031
• shipment_days: 5

I was trying to create this function, but honestly it's hard for me to understand it, could someone help me with that please? p.c. dateSend is a bind variable and i dont'e have it

Answer 1

One option is to create a procedure with OUT parameters.

As you didn't post test case, I created one by myself.

SQL> create table taby as
  2    select 1 senderindex, 1 recipientpostindex, 1 senderpostindex from dual;

Table created.

SQL> create table shipment_days as
  2    select 5 shipment_days, 1 first_Index, 1 second_index from dual;

Table created.

Your query converted to a procedure:

SQL> create or replace procedure getshipmentdays
  2    (par_date    in out date,
  3     par_varsend in     varchar2,
  4     par_varrec  in     varchar2,
  5     --
  6     par_sendix  out    taby.senderpostindex%type,
  7     par_recix   out    taby.recipientpostindex%type,
  8     par_days    out    shipment_days.shipment_days%type
  9    )
 10  as
 11  begin
 12    select par_date,
 13           t.senderpostindex,
 14           t.recipientpostindex,
 15           d.shipment_days
 16      into par_date,
 17           par_sendix,
 18           par_recix,
 19           par_days
 20      from taby t join shipment_days d on d.first_index = t.senderindex
 21                                      and d.second_index = t.recipientpostindex
 22      where t.senderpostindex = par_varsend
 23        and t.recipientpostindex = par_varrec;
 24  end;
 25  /

Procedure created.

Testing: as there are several OUT parameters, you have to declare variables to accept their values.

SQL> set serveroutput on;
SQL> declare
  2    l_date    date := date '2020-06-09';
  3    l_sendix  taby.senderpostindex%type;
  4    l_recix   taby.recipientpostindex%type;
  5    l_days    shipment_days.shipment_days%type;
  6  begin
  7    getshipmentdays(l_date, 1, 1, l_sendix, l_recix, l_days);
  8    dbms_output.put_line('date = ' || l_date     ||', '||
  9                         'senderPostindex = '    || l_sendix ||', '||
 10                         'recipientpostindex = ' || l_recix  ||', '||
 11                         'shipment_days = '      || l_days
 12                        );
 13  end;
 14  /
date = 09.06.20, senderPostindex = 1, recipientpostindex = 1, shipment_days = 5

PL/SQL procedure successfully completed.

SQL>

---

Another option is to create a function which returns refcursor:

SQL> create or replace function fgetshipmentdays
  2    (par_date    in  date,
  3     par_varsend in  varchar2,
  4     par_varrec  in  varchar2
  5    )
  6  return sys_refcursor
  7  as
  8    l_rc sys_refcursor;
  9  begin
 10    open l_rc for
 11    select par_date,
 12           t.senderpostindex,
 13           t.recipientpostindex,
 14           d.shipment_days
 15      from taby t join shipment_days d on d.first_index = t.senderindex
 16                                      and d.second_index = t.recipientpostindex
 17      where t.senderpostindex = par_varsend
 18        and t.recipientpostindex = par_varrec;
 19    return l_rc;
 20  end;
 21  /

Function created.

SQL> var rc refcursor
SQL> exec :rc := fgetshipmentdays(date '2020-06-09', 1, 1);

PL/SQL procedure successfully completed.

SQL> print rc

:B3      SENDERPOSTINDEX RECIPIENTPOSTINDEX SHIPMENT_DAYS
-------- --------------- ------------------ -------------
09.06.20               1                  1             5

SQL>

---

Or, as you wished, to use a table function.

Create types first:

SQL> create type t_sd_row as object
  2    (datum  date,
  3     sendix varchar2(10),
  4     recix  varchar2(10),
  5     days   number
  6    );
  7  /

Type created.

SQL> create type t_sd_tab as table of t_sd_row;
  2  /

Type created.

Function:

SQL> create or replace function getshipmentdays
  2    (par_date    in  date,
  3     par_varsend in  varchar2,
  4     par_varrec  in  varchar2
  5    )
  6  return t_sd_tab as
  7    l_tab t_sd_tab := t_sd_tab();
  8  begin
  9    select t_sd_row(par_date,
 10                    t.senderpostindex,
 11                    t.recipientpostindex,
 12                    d.shipment_days
 13                   )
 14      bulk collect into l_tab
 15      from taby t join shipment_days d on d.first_index = t.senderindex
 16                                      and d.second_index = t.recipientpostindex
 17      where t.senderpostindex = par_varsend
 18        and t.recipientpostindex = par_varrec;
 19    return l_tab;
 20  end;
 21  /

Function created.

Testing:

SQL> select * from table(getshipmentdays(date '2020-06-08', 1, 1));

DATUM            SENDIX     RECIX            DAYS
---------------- ---------- ---------- ----------
08/06/2020 00:00 1          1                   5

SQL>