Forum Stats

  • 3,757,475 Users
  • 2,251,231 Discussions
  • 7,869,837 Comments

Discussions

How to fetch multiple records from the table using stored procedure

Albert Chao
Albert Chao Member Posts: 60 Green Ribbon
edited Sep 3, 2021 1:44PM in SQL & PL/SQL
create table employees_x (emp_id number, emp_name varchar2(50), hire_date date, department_id number) ;

insert into employees_x values(1,'ALex',TO_DATE('21-06-07','DD-MM-YY'),10);
insert into employees_x values(2,'Duplex',TO_DATE('21-06-08','DD-MM-YY'),20);
insert into employees_x values(3,'Charles',TO_DATE('21-06-09','DD-MM-YY'),30);
insert into employees_x values(4,'Demon',TO_DATE('21-06-06','DD-MM-YY'),40);							

Requirement : I want to take Hire_date as an input and display all the records of the employees who has joined post the hire_date using stored procedure.


Example : Hire_date is 21-06-07 then output should be

2,Duplex, '21-06-08',20

3,Charles, '21-06-09',30

Code : 

CREATE OR REPLACE PROCEDURE display_records (
  joindate IN DATE
) AS
  lv_id  VARCHAR2(50);
  lv_name   NUMBER;
  lv_deptid NUMBER;
BEGIN
  SELECT
    first_name,
    salary,
    department_id
  INTO
    lv_id,
    lv_name,
    lv_deptid
  FROM
    hr.employees
  WHERE
    hire_date > joindate;
Exception
when no_data_found then
dbms_output.put_line('No record found after'||joindate);

END;



Best Answers

  • BluShadow
    BluShadow Member, Moderator Posts: 41,383 Red Diamond
    Accepted Answer

    Ok, so it depends what you mean by "display".

    Oracle PL/SQL runs on the database server, so it has nowhere to display anything. It's not like T-SQL in SQL Server that acts like a scripting language and will display results of selects as it goes along.

    If you want PL/SQL to return data then you'll typically need a function rather than a procedure, and from that you can either return a collection type containing all the data (though that's not ideal) or you can create your function as a pipelined function that pipe's the rows of data back out from whatever query you have inside it, and then that function is called from within a SQL statement as if the function is a table.

    e.g. (one of my old examples)...

    SQL> CREATE OR REPLACE TYPE myemp AS OBJECT
      2  ( empno    number,
      3    ename    varchar2(10),
      4    job      varchar2(10),
      5    mgr      number,
      6    hiredate date,
      7    sal      number,
      8    comm     number,
      9    deptno   number
     10  )
     11  /
    
    Type created.
    
    SQL> CREATE OR REPLACE TYPE myrectable AS TABLE OF myemp
      2  /
    
    Type created.
    
    SQL> CREATE OR REPLACE FUNCTION pipedata(p_min_row number, p_max_row number) RETURN myrectable PIPELINED IS
      2    v_obj myemp := myemp(NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
      3  BEGIN
      4    FOR e IN (select *
      5              from (
      6                    select e.*
      7                          ,rownum rn
      8                    from (select * from emp order by empno) e
      9                   )
     10              where rn between p_min_row and p_max_row)
     11    LOOP
     12      v_obj.empno    := e.empno;
     13      v_obj.ename    := e.ename;
     14      v_obj.job      := e.job;
     15      v_obj.mgr      := e.mgr;
     16      v_obj.hiredate := e.hiredate;
     17      v_obj.sal      := e.sal;
     18      v_obj.comm     := e.comm;
     19      v_obj.deptno   := e.deptno;
     20      PIPE ROW (v_obj);
     21    END LOOP;
     22    RETURN;
     23  END;
     24  /
    
    Function created.
    
    SQL> select * from table(pipedata(1,5));
    
         EMPNO ENAME      JOB               MGR HIREDATE                    SAL       COMM     DEPTNO
    ---------- ---------- ---------- ---------- -------------------- ---------- ---------- ----------
          7369 SMITH      CLERK            7902 17-DEC-1980 00:00:00        800                    20
          7499 ALLEN      SALESMAN         7698 20-FEB-1981 00:00:00       1600        300         30
          7521 WARD       SALESMAN         7698 22-FEB-1981 00:00:00       1250        500         30
          7566 JONES      MANAGER          7839 02-APR-1981 00:00:00       2975                    20
          7654 MARTIN     SALESMAN         7698 28-SEP-1981 00:00:00       1250       1400         30
    


    In more recent versions of the database you could even use SQL Macros (I'll let you look that one up)

  • Jan Gorkow
    Jan Gorkow Member Posts: 133 Gold Badge
    Accepted Answer

    Hi @User_3GK1M ,

    another option is to use a refcursor:

    CREATE OR REPLACE PROCEDURE display_records (joindate  IN   DATE,
                           records    OUT SYS_REFCURSOR)
    IS
      v_check  NUMBER;
    BEGIN
      SELECT CASE
           WHEN NOT EXISTS
               (SELECT 1
                FROM employees_x
                WHERE employees_x.hire_date > display_records.joindate)
           THEN
            0
           ELSE
            1
         END
       INTO v_check
       FROM DUAL;
    
      IF v_check = 0
      THEN
       dbms_output.put_line (
         'No record found after ' || TO_CHAR (joindate, 'DD-MM-YY'));
      END IF;
    
      OPEN display_records.records FOR
       SELECT employees_x.emp_id,
           employees_x.emp_name,
           employees_x.hire_date,
           employees_x.department_id
        FROM employees_x
        WHERE employees_x.hire_date > display_records.joindate;
    END display_records;
    /
    

    In sqlplus the call would look like this:

    Best regards

    Jan

Answers

  • User_H3J7U
    User_H3J7U Member Posts: 462 Bronze Trophy

    PL/SQL Language reference

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,030 Red Diamond

    hi, @User_3GK1M

    Would you like to get answers that work? Make sure the INSERT statements you post for the sample data work, too. Test (and, if necessary, fix) your statements before you post them.

    Also,

    create table Employees (emp_id number, emp_name varchar2(50), hire_date date, department_id number) ;
    insert into Employees values(1,'ALex','21-06-07',10),
    

    don't try to insert string values (such as '21-06-07') into DATE columns (such as hire_date). Use TO_DATE or DATE literals. Always use 4 digits for the year.

  • Albert Chao
    Albert Chao Member Posts: 60 Green Ribbon

    @Frank Kulash I have done the modifications to the original question. Please suggest

  • BluShadow
    BluShadow Member, Moderator Posts: 41,383 Red Diamond
    Accepted Answer

    Ok, so it depends what you mean by "display".

    Oracle PL/SQL runs on the database server, so it has nowhere to display anything. It's not like T-SQL in SQL Server that acts like a scripting language and will display results of selects as it goes along.

    If you want PL/SQL to return data then you'll typically need a function rather than a procedure, and from that you can either return a collection type containing all the data (though that's not ideal) or you can create your function as a pipelined function that pipe's the rows of data back out from whatever query you have inside it, and then that function is called from within a SQL statement as if the function is a table.

    e.g. (one of my old examples)...

    SQL> CREATE OR REPLACE TYPE myemp AS OBJECT
      2  ( empno    number,
      3    ename    varchar2(10),
      4    job      varchar2(10),
      5    mgr      number,
      6    hiredate date,
      7    sal      number,
      8    comm     number,
      9    deptno   number
     10  )
     11  /
    
    Type created.
    
    SQL> CREATE OR REPLACE TYPE myrectable AS TABLE OF myemp
      2  /
    
    Type created.
    
    SQL> CREATE OR REPLACE FUNCTION pipedata(p_min_row number, p_max_row number) RETURN myrectable PIPELINED IS
      2    v_obj myemp := myemp(NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
      3  BEGIN
      4    FOR e IN (select *
      5              from (
      6                    select e.*
      7                          ,rownum rn
      8                    from (select * from emp order by empno) e
      9                   )
     10              where rn between p_min_row and p_max_row)
     11    LOOP
     12      v_obj.empno    := e.empno;
     13      v_obj.ename    := e.ename;
     14      v_obj.job      := e.job;
     15      v_obj.mgr      := e.mgr;
     16      v_obj.hiredate := e.hiredate;
     17      v_obj.sal      := e.sal;
     18      v_obj.comm     := e.comm;
     19      v_obj.deptno   := e.deptno;
     20      PIPE ROW (v_obj);
     21    END LOOP;
     22    RETURN;
     23  END;
     24  /
    
    Function created.
    
    SQL> select * from table(pipedata(1,5));
    
         EMPNO ENAME      JOB               MGR HIREDATE                    SAL       COMM     DEPTNO
    ---------- ---------- ---------- ---------- -------------------- ---------- ---------- ----------
          7369 SMITH      CLERK            7902 17-DEC-1980 00:00:00        800                    20
          7499 ALLEN      SALESMAN         7698 20-FEB-1981 00:00:00       1600        300         30
          7521 WARD       SALESMAN         7698 22-FEB-1981 00:00:00       1250        500         30
          7566 JONES      MANAGER          7839 02-APR-1981 00:00:00       2975                    20
          7654 MARTIN     SALESMAN         7698 28-SEP-1981 00:00:00       1250       1400         30
    


    In more recent versions of the database you could even use SQL Macros (I'll let you look that one up)

  • Jan Gorkow
    Jan Gorkow Member Posts: 133 Gold Badge
    Accepted Answer

    Hi @User_3GK1M ,

    another option is to use a refcursor:

    CREATE OR REPLACE PROCEDURE display_records (joindate  IN   DATE,
                           records    OUT SYS_REFCURSOR)
    IS
      v_check  NUMBER;
    BEGIN
      SELECT CASE
           WHEN NOT EXISTS
               (SELECT 1
                FROM employees_x
                WHERE employees_x.hire_date > display_records.joindate)
           THEN
            0
           ELSE
            1
         END
       INTO v_check
       FROM DUAL;
    
      IF v_check = 0
      THEN
       dbms_output.put_line (
         'No record found after ' || TO_CHAR (joindate, 'DD-MM-YY'));
      END IF;
    
      OPEN display_records.records FOR
       SELECT employees_x.emp_id,
           employees_x.emp_name,
           employees_x.hire_date,
           employees_x.department_id
        FROM employees_x
        WHERE employees_x.hire_date > display_records.joindate;
    END display_records;
    /
    

    In sqlplus the call would look like this:

    Best regards

    Jan

  • BluShadow
    BluShadow Member, Moderator Posts: 41,383 Red Diamond

    Yep, ref cursors are definitely an option, though not so commonly used within the PL/SQL environment itself i.e. it's not common for a PL/SQL function/procedure to produce a ref cursor for other PL/SQL code to use.

    See the community document: PL/SQL 101 : Understanding Ref Cursors — oracle-tech

    You'll find they're more commonly used for 3rd party application layers e.g. Java or .NET.

    That doesn't mean you can't use them in PL/SQL code, just that it's not the norm, as PL/SQL usually has easier ways to issue queries and deal with results.