[SOLVED] How to Create oracle PL/SQL function to store/retrieve data from table

Issue

i am beginner so i am confusing about what should i write in return type.

and how to write function to select all records from particular ID

2   shadik  27  25-05-14    4000    pakistan
1   AKSHAY  28  30-04-20    2000    INDIA
3   GAURANG 25  06-05-20    4000    USA
4   NIRAV   23  16-11-14    1000    CANADA
5   VEER    29  26-12-19    5000    DUBAI

Solution

A simple option is to return refcursor; you don’t have to declare any type for that.

Here’s an example based on Scott’s sample schema. Function returns all rows from the EMP table whose DEPTNO column matches value passed as function’s IN parameter.

SQL> create or replace function f_test (par_deptno in number)
  2    return sys_refcursor
  3  is
  4    rc sys_refcursor;
  5  begin
  6    open rc for select * from emp
  7                where deptno = par_deptno;
  8    return rc;
  9  end;
 10  /

Function created.

SQL> select f_test(10) from dual;

F_TEST(10)
--------------------
CURSOR STATEMENT : 1

CURSOR STATEMENT : 1

     EMPNO ENAME      JOB              MGR HIREDATE        SAL       COMM     DEPTNO
---------- ---------- --------- ---------- -------- ---------- ---------- ----------
      7782 CLARK      MANAGER         7839 09.06.81       2450                    10
      7839 KING       PRESIDENT            17.11.81       5000                    10
      7934 MILLER     CLERK           7782 23.01.82       1300                    10


SQL>

Answered By – Littlefoot

Answer Checked By – Katrina (BugsFixing Volunteer)