How to Create oracle PL/SQL function to store/retrieve data from table-CodePudding

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

CodePudding user response：

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>

CodePudding user response：

Though this is a bit more custom you can return rows from a function using a type.


create or replace type myRecordType
 as object
 ( x int,
 y date,
 z varchar2(25)
 )
 

create or replace type myTableType
 as table of myRecordType;
/

create or replace function my_function return myTableType
 as
 some_data myTableType := myTabletype();
 begin
 for i in 1 .. 10 loop
 some_data.extend;
 some_data(i) :=
 myRecordType( i, sysdate i, 'record ' || i );
 end loop;
 return some_data;
 end;
 /

select * from TABLE ( cast( my_function() as mytableType ) )
 /

X    Y    Z
1    09-FEB-22    record 1
2    10-FEB-22    record 2
3    11-FEB-22    record 3
4    12-FEB-22    record 4
5    13-FEB-22    record 5
6    14-FEB-22    record 6
7    15-FEB-22    record 7
8    16-FEB-22    record 8
9    17-FEB-22    record 9
10    18-FEB-22    record 10