Home » SQL & PL/SQL » SQL & PL/SQL » Cursor Within a Cursor
Cursor Within a Cursor [message #310755] Wed, 02 April 2008 05:35 Go to next message
saharookiedba
Messages: 56
Registered: September 2007
Location: PUNE
Member
Hi,

Please let me know, how to use a Cursor Within a Cursor.
Below are the two cursors which i am using...

1)
SELECT ProductVersionID, SwtypeID
  FROM toolset_products
  WHERE toolsetID = In_ToolsetID;


2)
SELECT t.ID toolset, tp.ID toolset_products,tp.ProductVersionID ProVer,  tpswo.swoptionID toolset_products_sw_option
  FROM toolset t, toolset_products tp, toolset_product_sw_option tpswo
  WHERE tpswo.TPID = tp.ID
  AND tp.TOOLSETID = t.ID
  AND tp.ToolsetID = In_ToolSetID
  AND tp.ProductVersionID = In_ProductVersionID;


The First Cursor gives me the productversionID and for each productversionID i get multiple swoptionID from the second cursor.
My Requirement is that, i take each swoptionID according to the
ProductVersionID and insert into a table...

Please help me out in this, it would be great if i can get a example for the same.
Thank you
Re: Cursor Within a Cursor [message #310761 is a reply to message #310755] Wed, 02 April 2008 05:49 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
What's wrong with a join on the two tables?

MHE
Re: Cursor Within a Cursor [message #310803 is a reply to message #310755] Wed, 02 April 2008 08:07 Go to previous messageGo to next message
sispk6
Messages: 164
Registered: November 2006
Location: pakistan
Senior Member
please look at it, and then perform your requirement urself
begin
  for c1 in select deptno , dname from emp where LOC = :LOC 
    loop
      for c2 in select empno from emp where  DEPTNO = C1.DEPTNO  
       LOOP
        MESSAGE('EMPNO'||EMPNO);
       END  LOOP;
    end loop ;  
end ;
Re: Cursor Within a Cursor [message #310840 is a reply to message #310803] Wed, 02 April 2008 10:13 Go to previous message
saharookiedba
Messages: 56
Registered: September 2007
Location: PUNE
Member
Below is the Procedure where i have to use Cursors...

create or replace
PROCEDURE sp_planning_toolset(Operation VARCHAR2,
                                                In_PlanningID planning.ID%TYPE,
                                                In_ToolsetID toolset.ID%TYPE,
                                                In_ProjectPhaseID project_phase.ID%TYPE, 
                                                --In_SwTypeID sw_types.ID%TYPE,
                                                In_UserID users.ID%TYPE,
                                                In_PlanningUserID users.ID%TYPE,
                                                In_Utilization planning.Utilization%TYPE
                                                --In_ProductVersionID product_versions.ID%TYPE
                                               ) IS
                   
temp_Utilization NUMBER;
i NUMBER := 1;
j NUMBER := 1;
Counter NUMBER;

TYPE ARRAY_SWOID_TYPE IS TABLE OF VARCHAR2(500)
INDEX BY BINARY_INTEGER;
ARRAY_SWOID ARRAY_SWOID_TYPE ;

TYPE ARRAY_PROVERID_TYPE IS TABLE OF NUMBER
INDEX BY BINARY_INTEGER;
ARRAY_PROVERID ARRAY_PROVERID_TYPE ;

TYPE ARRAY_SWTID_TYPE IS TABLE OF NUMBER
INDEX BY BINARY_INTEGER;
ARRAY_SWTID ARRAY_SWTID_TYPE ;

PRAGMA AUTONOMOUS_TRANSACTION;
--Autonomous Transaction ensures that Commit saves the changes made by the transactions
--in the Procedure, it is independent of the Commit to the Database.

--Cursor to get all the ProductVersionID and SWTypeID belonging to that toolset.
CURSOR toolset_products_cur IS
  SELECT ProductVersionID, SwtypeID
  FROM toolset_products
  WHERE toolsetID = In_ToolsetID;
  
BEGIN

  IF(In_Utilization = -1) THEN
   temp_Utilization := 100;
  ELSE
   temp_Utilization := In_Utilization;
  END IF;
  
[COLOR=skyblue]FOR toolset_products_rec IN toolset_products_cur
  LOOP
     BEGIN 
       ARRAY_PROVERID(i) := toolset_products_rec.ProductVersionId ;
       ARRAY_SWTID(i) := toolset_products_rec.SWTypeID;
       
       SELECT tpswo.swoptionID toolset_products_sw_option
       INTO ARRAY_SWOID(i)
       FROM toolset t, toolset_products tp, toolset_product_sw_option tpswo
       WHERE tpswo.TPID = tp.ID
       AND tp.TOOLSETID = t.ID
       AND  tp.ToolsetID = In_ToolSetID
       AND tp.ProductVersionID = ARRAY_PROVERID(i);

       [COLOR=red]--The Above Query gives me multiple sw_options...e.g 3 sw_options
       -- I want to store these sw_options in an array, to insert in a table
       -- The Problem is when i get 3 sw_options i am not able to store these values one by one in the ARRAY.   [/COLOR]  
        
       i := i+1;
     END;
  END LOOP;[/COLOR]  i:= 1;
  
/*FOR tset_prod_sw_option_rec IN tset_prod_sw_option_cur
  LOOP
     BEGIN 
       ARRAY_SWOID(i) := tset_prod_sw_option_rec.toolset_products_sw_option;
       i := i+1;
     END;
  END LOOP;*/    
  i:= 1;
  --Below are the insert statements.. 
  IF (Operation='Add') THEN 
    FOR i IN 1..ARRAY_PROVERID.LAST LOOP
         INSERT INTO planning(ID, 
                              ProjectPhaseID,
                              SWType,
                              UserID,
                              PlanningUserID,
                              ProductVersionID,
                              Utilization) 	
                      VALUES (seq_planning_id.nextval,
                              In_ProjectPhaseID,
                              ARRAY_SWTID(i),
                              In_UserID,
                              In_PlanningUserID,
                              ARRAY_PROVERID(i),
                              In_Utilization);
                         
      
   --Inserting into planning_sw_options
   FOR i IN 1..ARRAY_SWOID.LAST LOOP
     INSERT INTO planning_sw_options (ID,
                                      PlanningID,
                                      SWOptionID)
                             VALUES  (seq_planning_sw_options_id.nextval,
                                      seq_planning_id.currval,
                                      ARRAY_SWOID(i));
    END LOOP;                                
   END LOOP;
   ELSIF (Operation = 'Delete')THEN
        DELETE FROM planning_sw_options WHERE PlanningID = In_PlanningID;
        DELETE FROM planning where ID=In_PlanningID;
	END IF;
COMMIT;
END sp_planning_toolset;


I have mentioned the code which is the main concern area in Sky Blue & my description in Red.
Please let me know
Thank You...
Previous Topic: use of logminer in a procedure
Next Topic: EXTERNAL TABLE
Goto Forum:
  


Current Time: Sat Dec 10 01:10:55 CST 2016

Total time taken to generate the page: 0.08048 seconds