-------------------------------- Below tables are used in CRUD activities --------------------------------


CREATE TABLE Employee (EMP_ID int , Emp_Name varchar(100), Hire_Date timestamp); 

-------------------------------- Below Tables are used in Stored procedure -------------------------------- 
CREATE TABLE Demo_table1 (EMP_ID int , Emp_Name varchar(100), Hire_Date timestamp);
CREATE TABLE Demo_table2 (Employee_ID int , Employee_Name varchar(100), Hired_Date timestamp);

-------------------------------- Stored procedure -------------------------------- 

 CREATE OR REPLACE PROCEDURE multipleCursorsQA (
   E_ID IN number,
   E_name OUT varchar,
   p_recordset1 OUT SYS_REFCURSOR,
   p_recordset2 OUT SYS_REFCURSOR) AS
BEGIN
 OPEN p_recordset1 FOR
   SELECT Employee_Name into E_name from Demo_table2 where Employee_ID=E_ID;
      OPEN p_recordset2 FOR
   SELECT EMP_NAME into E_name from demo_table1 where EMP_ID=E_ID;
END;
set serveroutput on;
DECLARE
 E_ID NUMBER;
 E_name VARCHAR2(200);
l_cursor1  SYS_REFCURSOR;
l_cursor2  SYS_REFCURSOR;
BEGIN
multipleCursorsQA(5,E_name,l_cursor1,l_cursor2);
LOOP
    FETCH l_cursor1
    INTO  E_name;
    EXIT WHEN l_cursor1%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(E_name);
  END LOOP;
  CLOSE l_cursor1;
  LOOP
    FETCH l_cursor2
    INTO  E_name;
    EXIT WHEN l_cursor2%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(E_name);
  END LOOP;
  CLOSE l_cursor2;
END;

call multipleCursorsQA(?E_ID, ?EMP_NAME, ?p_recordset1, ?p_recordset2);

-------------------------------- Below query is used to insert data into tables -------------------------------- 

INSERT INTO Demo_table1 (EMP_ID, Emp_Name, Hire_Date) VALUES (5, 'Garry', TO_TIMESTAMP('2022-01-12 17:16:05.551000000', 'YYYY-MM-DD HH24:MI:SS.FF'));
INSERT INTO Demo_table2 (Employee_ID, Employee_Name, Hired_Date) VALUES (5, 'Roso', TO_TIMESTAMP('2022-01-12 17:16:05.551000000', 'YYYY-MM-DD HH24:MI:SS.FF'));



