Skip to Main Content

Oracle returning a cursor from a function or procedure

BeefStuJul 21 2021

I have the following code, which appears to be working fine.

As you can in the last piece of SQL the date range is hard coded. Is there a way to wrap it in a procedure or function and return a cursor (passing the dates directly to the procedure or function ) then modifying my SQL to reference each day that falls between the date range.

Ie something like this without the hard coded dates

WITH calendar ( start_date, end_date ) AS (
SELECT DATE '2021-07-01', DATE '2021-07-30' FROM DUAL
UNION ALL
SELECT start_date + 1, end_date
FROM calendar
WHERE start_date + 1 <= end_date
)
SELECT start_date AS day
FROM calendar

CREATE OR REPLACE PROCEDURE generate\_dates  
 (  
  p\_start\_date  IN DATE,  
 p\_end\_date   IN DATE  
)  
AS   
    BEGIN  
    END;  
END generate\_dates;  

EXEC generate_dates(
DATE '2021-07-01',
DATE '2021-07-31');

ALTER SESSION SET
NLS_DATE_FORMAT = 'MMDDYYYY HH24:MI:SS';

create table holidays(  
  holiday\_date DATE,  
 holiday\_name VARCHAR2(20)  
);  

INSERT into holidays  
  (holiday\_date,  
   holiday\_name)  
 VALUES  
(  
 TO\_DATE('2021/07/21 00:00:00',   'yyyy/mm/dd hh24:mi:ss'), 'July 21 2021');  

 
Create table employees(  
 employee\_id NUMBER(6),   
 first\_name VARCHAR2(20),  
 last\_name VARCHAR2(20),  
 card\_num VARCHAR2(10),  

work_days VARCHAR2(7)
);

 ALTER TABLE employees  
         ADD ( CONSTRAINT employees\_pk  
       PRIMARY KEY (employee\_id));  


INSERT INTO employees                     
(  
EMPLOYEE\_ID,  
first\_name,   
last\_name,  
card\_num,   
work\_days  

)
WITH names AS (
SELECT 1, 'Jane', 'Doe', 'F123456', 'NYYYYYN' FROM dual UNION ALL
SELECT 2, 'Madison', 'Smith', 'R33432','NYYYYYN'
FROM dual UNION ALL
SELECT 3, 'Justin', 'Case', 'C765341','NYYYYYN'
FROM dual UNION ALL
SELECT 4, 'Mike', 'Jones', 'D564311','NYYYYYN' FROM dual
) SELECT * FROM names;

create table timeoff(  
     seq\_num integer  GENERATED BY DEFAULT AS IDENTITY (START WITH 1) NOT NULL,  
employee\_id NUMBER(6),  
timeoff\_date DATE,  
timeoff\_type VARCHAR2(1),  
 constraint timeoff\_chk check (timeoff\_date=trunc(timeoff\_date, 'dd')),  
  constraint timeoff\_pk primary key (employee\_id, timeoff\_date)  
 );  

INSERT INTO timeoff (EMPLOYEE_ID,TIMEOFF_DATE,TIMEOFF_TYPE
)
WITH dts AS (
SELECT 1, to_date('20210726 00:00:00','YYYYMMDD HH24:MI:SS'),'V' FROM dual UNION ALL
SELECT 2, to_date('20210726 00:00:00','YYYYMMDD HH24:MI:SS'),'V' FROM dual UNION ALL
SELECT 2, to_date('20210727 00:00:00','YYYYMMDD HH24:MI:SS'),'V' FROM dual )
SELECT * FROM dts;

        CREATE TABLE  emp\_attendance(      
 seq\_num integer  GENERATED BY DEFAULT AS IDENTITY (START WITH 1) NOT NULL,  
  employee\_id NUMBER(6),  
  start\_date DATE,  
  end\_date DATE,  
  week\_number NUMBER(2),  
  create\_date DATE DEFAULT SYSDATE  
   );  

create table absences(  
seq\_num integer  GENERATED BY DEFAULT AS IDENTITY (START WITH 1) NOT NULL,  
employee\_id NUMBER(6),  
absent\_date DATE,  
constraint absence\_chk check (absent\_date=trunc(absent\_date, 'dd')),  
  constraint absence\_pk primary key (employee\_id, absent\_date)  
);  


INSERT INTO emp\_attendance (    EMPLOYEE\_ID, START\_DATE,END\_DATE,WEEK\_NUMBER)  
WITH dts AS (   
SELECT 1, to\_date('20210728 13:10:00','YYYYMMDD HH24:MI:SS'),  
to\_date('20210728 23:15:00','YYYYMMDD HH24:MI:SS'), 30  FROM dual UNION ALL   
SELECT 2, to\_date('20210728 12:10:10','YYYYMMDD HH24:MI:SS'),  
to\_date('20210728 20:15:01','YYYYMMDD HH24:MI:SS'), 30  FROM dual)  

SELECT * FROM dts;

SELECT e.employee_id,
c.day
FROM employees e
INNER JOIN (
WITH calendar ( start_date, end_date ) AS (
SELECT DATE '2021-07-01', DATE '2021-07-30' FROM DUAL
UNION ALL
SELECT start_date + 1, end_date
FROM calendar
WHERE start_date + 1 <= end_date
)
SELECT start_date AS day
FROM calendar
) c
PARTITION BY ( e.employee_id )
ON (SUBSTR(e.work_days, TRUNC(c.day) - TRUNC(c.day, 'IW') + 1, 1) = 'Y')
WHERE NOT EXISTS (
SELECT 1
FROM holidays h
WHERE c.day = h.holiday_date
)
AND NOT EXISTS(
SELECT 1
FROM timeoff t
WHERE e.employee_id = t.employee_id
AND t.timeoff_date = c.day
)
ORDER BY
e.employee_id,
c.day

Comments
Post Details
Added on Jul 21 2021
16 comments
262 views