PL/SQL REF CURSOR: Return Multiple Columns and Rows Easily

DECLARE
     TYPE ref_emp_cur IS REF CURSOR;
     var_emp_cur ref_emp_cur;
     varr emp%ROWTYPE;
BEGIN
    OPEN var_emp_cur
    FOR select * FROM Emp;
    LOOP
    FETCH  var_emp_cur INTO varr;
    EXIT WHEN var_emp_cur%NOTFOUND;
    dbms_output.put_line(varr.empno||' - '|| varr.ename ||' - '|| varr.sal);
    END LOOP;
   CLOSE var_emp_cur;
END;

PL/SQL: How to Return Multiple Values from a Function

CREATE OBJECT-TYPE

   CREATE TYPE HZ_PARTY_OBJ IS OBJECT (PARTY_ID NUMBER,PARTY_NUMBER VARCHAR2(30),PARTY_NAME VARCHAR2(360));
 

TABLE TYPE

   CREATE TYPE HZ_PARTY_TAB IS TABLE OF HZ_PARTIES_OBJ;
 
CREATE FUNCTION

CREATE OR REPLACE FUNCTION GET_PARTIES
  RETURN HZ_PARTY_TAB
   IS
   L_HZ_PARTY_TAB  := HZ_PARTY_TAB();
    N INTEGER := 0;
BEGIN 
   FOR R IN (SELECT PARTY_ID,PARTY_NUMBER,PARTY_NAME FROM HZ_PARTIES)
   LOOP
      L_HZ_PARTY_TAB.EXTEND;
      N := N +1 ;
      L_HZ_PARTY_TAB(N) := HZ_PARTY_OBJ(R.PARTY_ID,R.PARTY_NUMBER,R.PARTY_NAME);
      END LOOP;
      RETURN L_HZ_PARTY_TAB;
END;

PL/SQL: Return Multiple Rows Using REF CURSOR Explained

DECLARE
    type ref_cursor is ref cursor;
    v_party ref_cursor;
    v_party_name  hz_parties.party_name%type;
BEGIN
    open v_party for select party_name from hz_parties;
    loop
     fetch v_party into v_party_name;
       dbms_output.put_line(v_party_name);
     end loop;
      close v_party;
END;

PL/SQL Procedure Basics: How to Create and Use Procedures

create or replace procedure proc1
is
  l_id integer;
begin
  select employee_id
  into l_id
  from employees
  where first_name = 'Suriya Parithy';
  dbms_output.put_line(l_id);
end;

exec proc1;

PL/SQL: How to SELECT Multiple Rows with Examples

declare
          type party_id_type is table of hz_parties.party_id%type;
          type party_name_type is table of hz_parties.party_name%type;
           party_id_tab   party_id_type;
           party_name_tab party_name_type;
begin
     SELECT party_id,party_name
     BULK COLLECT INTO party_id_tab, party_name_tab
     FROM hz_parties;
   
    FOR i IN party_id_tab.FIRST..party_id_tab.LAST
    LOOP
       DBMS_OUTPUT.PUT_LINE ('party_id: '||party_id_tab(i));
       DBMS_OUTPUT.PUT_LINE ('party_name: '||party_name_tab(i));
    END LOOP;
end;