sam andrew
asked on
Create Function which returns records from multiple tables.
I'm new to Oracle functions. Need help for the following task.
I need to write a function which accepts 2 parameters and it would return records from 2 different tables by joining them.
For Example:
Employee - EMP table
empno||empname||deptno||st
==========================
1 Sam 1 July 18th 2017
2 Andrew 2 July 10th 2017
Department - Dept Table
deptno||deptname
================
1 Arts
2 Science
I need a function - DepartmentEmployees( Start_Date, End_Date ) this function returns all the employees whose join date is in between the Start_Date and End_Date. with the selective no. of columns like empno, empname, deptname, stardate
something like this query
select * from emp e join dept d on e.deptno=d.deptno where Start_date >= e.startdate and End_Date <=e.startdate.
so if I do this query select * from DepartmentEmployees('07/09
empno||empname||deptname||
==========================
1 Sam Arts July 18th 2017
2 Andrew Science July 10th 2017
I need to write a function which accepts 2 parameters and it would return records from 2 different tables by joining them.
For Example:
Employee - EMP table
empno||empname||deptno||st
==========================
1 Sam 1 July 18th 2017
2 Andrew 2 July 10th 2017
Department - Dept Table
deptno||deptname
================
1 Arts
2 Science
I need a function - DepartmentEmployees( Start_Date, End_Date ) this function returns all the employees whose join date is in between the Start_Date and End_Date. with the selective no. of columns like empno, empname, deptname, stardate
something like this query
select * from emp e join dept d on e.deptno=d.deptno where Start_date >= e.startdate and End_Date <=e.startdate.
so if I do this query select * from DepartmentEmployees('07/09
empno||empname||deptname||
==========================
1 Sam Arts July 18th 2017
2 Andrew Science July 10th 2017
ASKER
thank you..that helps,
can you provide a working example for the DepartmentEmployees function which I have specified in my question.
can you provide a working example for the DepartmentEmployees function which I have specified in my question.
Not as you defined the requirement.
I can create a function that returns a ref cursor and I can create a pipelined function. I cannot create a function that works the way you defined the requirement.
I believe your requirements need to change.
I can create a function that returns a ref cursor and I can create a pipelined function. I cannot create a function that works the way you defined the requirement.
I believe your requirements need to change.
ASKER
Can you please give me the example for pipelined function?
thank you
thank you
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I GOT EXACTLY WHAT i NEEDED
Getting a function to return rows is also easy but requires some additional setup. You would need to create a TYPE for the returned row. then you create a PIPELINED function to return the rows one by one.
There is an example in the online documentation for pipelined functions:
http://docs.oracle.com/database/122/LNPLS/plsql-optimization-and-tuning.htm#LNPLS916
If you can provide more information about your specific requirements, we can probably provide a working example.