Home » SQL & PL/SQL » SQL & PL/SQL » Alternate for multiple cursors (Oracle 10.2.0.4.0)
Alternate for multiple cursors [message #576251] Fri, 01 February 2013 23:42 Go to next message
arif_md2009
Messages: 732
Registered: May 2009
Location: United Arab Emirates
Senior Member

Dear All, i have one requirement which i want to resolve it in easier way than writing multiple cursors , as there are two table one is om_mach_formula which is my machine master where the capacity of each machine is defined , based on which and operation code in transaction table cut_machine , machine codes will be updated. i am putting the test case below and what i have tried but actually i need to write more cursors as the ranges will be increasing. I need some alternate method which is easier than what i am using.


CREATE TABLE OM_MACH_FORMULA
(
  MACH_CODE      VARCHAR2(12 BYTE),
  MACH_NAME      VARCHAR2(20 BYTE),
  MIN_LEN        NUMBER,
  MAX_LEN        NUMBER,
  MIN_WIDTH      NUMBER,
  MAX_WIDTH      NUMBER,
  MIN_HIEGHT     NUMBER,
  MAX_HIEGHT     NUMBER,
  REF_OPERATION  VARCHAR2(20 BYTE),
  PROFILE        VARCHAR2(12 BYTE),
  MACH_TYPE      VARCHAR2(1 BYTE),
  MACH_STAGE     NUMBER
);


INSERT INTO OM_MACH_FORMULA ( MACH_CODE, MACH_NAME, MIN_LEN, MAX_LEN, MIN_WIDTH, MAX_WIDTH,
MIN_HIEGHT, MAX_HIEGHT, REF_OPERATION, PROFILE, MACH_TYPE,
MACH_STAGE ) VALUES ( 
'PL1', 'PLATE1', 1, 250, 1, 10, 1, 100, 'P1', 'PL', 'P', 1); 
INSERT INTO OM_MACH_FORMULA ( MACH_CODE, MACH_NAME, MIN_LEN, MAX_LEN, MIN_WIDTH, MAX_WIDTH,
MIN_HIEGHT, MAX_HIEGHT, REF_OPERATION, PROFILE, MACH_TYPE,
MACH_STAGE ) VALUES ( 
'PL2', 'PLATE2', 251, 500, 11, 20, 101, 200, 'P2', 'PL', 'P', 2); 
INSERT INTO OM_MACH_FORMULA ( MACH_CODE, MACH_NAME, MIN_LEN, MAX_LEN, MIN_WIDTH, MAX_WIDTH,
MIN_HIEGHT, MAX_HIEGHT, REF_OPERATION, PROFILE, MACH_TYPE,
MACH_STAGE ) VALUES ( 
'AM1', 'Angles1', 1, 12000, 40, 100, 1, 15, 'RE,IR', 'L', 'S', 1); 
INSERT INTO OM_MACH_FORMULA ( MACH_CODE, MACH_NAME, MIN_LEN, MAX_LEN, MIN_WIDTH, MAX_WIDTH,
MIN_HIEGHT, MAX_HIEGHT, REF_OPERATION, PROFILE, MACH_TYPE,
MACH_STAGE ) VALUES ( 
'AM2', 'Angles2', 1, 12000, 101, 150, 1, 15, 'RE,IR', 'L', 'S', 1); 
INSERT INTO OM_MACH_FORMULA ( MACH_CODE, MACH_NAME, MIN_LEN, MAX_LEN, MIN_WIDTH, MAX_WIDTH,
MIN_HIEGHT, MAX_HIEGHT, REF_OPERATION, PROFILE, MACH_TYPE,
MACH_STAGE ) VALUES ( 
'B1', 'BEAM_LINE1', 1700, 18000, 75, 610, 75, 1020, 'RE', 'H', 'S', 1); 
INSERT INTO OM_MACH_FORMULA ( MACH_CODE, MACH_NAME, MIN_LEN, MAX_LEN, MIN_WIDTH, MAX_WIDTH,
MIN_HIEGHT, MAX_HIEGHT, REF_OPERATION, PROFILE, MACH_TYPE,
MACH_STAGE ) VALUES ( 
'B2', 'BEAM_LINE2', 19000, 21000, 75, 610, 75, 1020, 'HO', 'H', 'S', 1); 
INSERT INTO OM_MACH_FORMULA ( MACH_CODE, MACH_NAME, MIN_LEN, MAX_LEN, MIN_WIDTH, MAX_WIDTH,
MIN_HIEGHT, MAX_HIEGHT, REF_OPERATION, PROFILE, MACH_TYPE,
MACH_STAGE ) VALUES ( 
'TH1', 'THERMAL', 19000, 21000, 75, 610, 75, 1020, 'B1', 'H', 'S', 2); 
INSERT INTO OM_MACH_FORMULA ( MACH_CODE, MACH_NAME, MIN_LEN, MAX_LEN, MIN_WIDTH, MAX_WIDTH,
MIN_HIEGHT, MAX_HIEGHT, REF_OPERATION, PROFILE, MACH_TYPE,
MACH_STAGE ) VALUES ( 
'TH2', 'THERMAL2', 19000, 21000, 75, 610, 75, 1020, 'B2', 'H', 'S', 2); 
COMMIT;

CREATE TABLE CUT_MACHINE
(
  POS_NO     VARCHAR2(12 BYTE),
  POS_DESC   VARCHAR2(20 BYTE),
  WT         NUMBER,
  LENGTH     NUMBER,
  WIDTH      NUMBER,
  HEIGHT     NUMBER,
  OPER_CODE  VARCHAR2(12 BYTE),
  MACH_CODE  VARCHAR2(12 BYTE),
  STAGE      NUMBER
);


INSERT INTO CUT_MACHINE ( POS_NO, POS_DESC, WT, LENGTH, WIDTH, HEIGHT, OPER_CODE, MACH_CODE,
STAGE ) VALUES ( 
'2', 'H140', 10, 1800, 500, 200, 'RE', NULL, 1); 
INSERT INTO CUT_MACHINE ( POS_NO, POS_DESC, WT, LENGTH, WIDTH, HEIGHT, OPER_CODE, MACH_CODE,
STAGE ) VALUES ( 
'2', 'PL8', 10, 400, 13, 144, 'HO', NULL, 2); 
INSERT INTO CUT_MACHINE ( POS_NO, POS_DESC, WT, LENGTH, WIDTH, HEIGHT, OPER_CODE, MACH_CODE,
STAGE ) VALUES ( 
'3', 'PL12', 30, 189, 9, 88, 'P1', NULL, 1); 
INSERT INTO CUT_MACHINE ( POS_NO, POS_DESC, WT, LENGTH, WIDTH, HEIGHT, OPER_CODE, MACH_CODE,
STAGE ) VALUES ( 
'3', 'L11', 33, 11000, 41, 15, 'RE', NULL, 2); 
INSERT INTO CUT_MACHINE ( POS_NO, POS_DESC, WT, LENGTH, WIDTH, HEIGHT, OPER_CODE, MACH_CODE,
STAGE ) VALUES ( 
'1', 'L10X10', 20, 11000, 102, 14, 'RE', NULL, 1); 
INSERT INTO CUT_MACHINE ( POS_NO, POS_DESC, WT, LENGTH, WIDTH, HEIGHT, OPER_CODE, MACH_CODE,
STAGE ) VALUES ( 
'1', 'L10X10', 25, 11000, 102, 15, 'HO', NULL, 2); 
COMMIT;




POS_NO	POS_DESC	WT	LENGTH	WIDTH	HEIGHT	OPER_CODE MACH_CODE	STAGE
2	H140	         10	1,800	500	200	RE			1
2	PL8	         10	400	13	144	HO			2
3	PL12	         30	189	9	88	P1			1
3	L11	         33	11,000	41	15	RE			2
1	L10X10	         20	11,000	102	14	RE			1
1	L10X10	         25	11,000	102	15	HO			2


--procedure what i wrote 

DECLARE
   CURSOR c1
   IS
      SELECT   pos_no, pos_desc, LENGTH, width, height, oper_code, stage
          FROM cut_machine
      ORDER BY pos_no, stage;

   CURSOR c_first_sec (
      p_length   IN   NUMBER,
      p_width    IN   NUMBER,
      p_height   IN   NUMBER,
      p_stage    IN   NUMBER,
      p_desc     IN   VARCHAR2
   )
   IS
      SELECT   mach_code
          FROM om_mach_formula
         WHERE mach_type = 'S'
           AND p_length BETWEEN min_len AND max_len
           AND p_height BETWEEN min_hieght AND max_hieght
           AND p_width BETWEEN min_width AND max_width
           AND NVL (mach_stage, 1) = 1
           AND UPPER (p_desc) LIKE '%' || UPPER (PROFILE) || '%'
      ORDER BY mach_code DESC;

   CURSOR c_second_sec (
      p_length   IN   NUMBER,
      p_width    IN   NUMBER,
      p_height   IN   NUMBER,
      p_stage    IN   NUMBER,
      p_desc     IN   VARCHAR2
   )
   IS
      SELECT   mach_code
          FROM om_mach_formula
         WHERE mach_type = 'S'
           AND p_length BETWEEN min_len AND max_len
           AND p_height BETWEEN min_hieght AND max_hieght
           AND p_width BETWEEN min_width AND max_width
           AND NVL (mach_stage, 1) != 1
           AND UPPER (p_desc) LIKE '%' || UPPER (PROFILE) || '%'
      ORDER BY mach_code DESC;

   CURSOR c_first_pl (
      p_length   IN   NUMBER,
      p_width    IN   NUMBER,
      p_height   IN   NUMBER,
      p_stage    IN   NUMBER,
      p_desc     IN   VARCHAR2
   )
   IS
      SELECT   mach_code
          FROM om_mach_formula
         WHERE mach_type = 'P'
           AND p_length BETWEEN min_len AND max_len
           AND p_height BETWEEN min_hieght AND max_hieght
           AND p_width BETWEEN min_width AND max_width
           AND NVL (mach_stage, 1) = 1
           AND UPPER (p_desc) LIKE '%' || UPPER (PROFILE) || '%'
      ORDER BY mach_code DESC;

   CURSOR c_second_pl (
      p_length   IN   NUMBER,
      p_width    IN   NUMBER,
      p_height   IN   NUMBER,
      p_stage    IN   NUMBER,
      p_desc     IN   VARCHAR2
   )
   IS
      SELECT   mach_code
          FROM om_mach_formula
         WHERE mach_type = 'P'
           AND p_length BETWEEN min_len AND max_len
           AND p_height BETWEEN min_hieght AND max_hieght
           AND p_width BETWEEN min_width AND max_width
           AND NVL (mach_stage, 1) != 1
           AND UPPER (p_desc) LIKE '%' || UPPER (PROFILE) || '%'
      ORDER BY mach_code DESC;

   first_mach    VARCHAR2 (20);
   second_mach   VARCHAR2 (20);
BEGIN
   FOR i IN c1
   LOOP
      IF i.pos_desc NOT LIKE 'PL%'
      THEN
         IF i.stage = 1
         THEN
            IF c_first_sec%ISOPEN
            THEN
               CLOSE c_first_sec;
            END IF;

            OPEN c_first_sec (i.LENGTH, i.width, i.height, i.stage,
                              i.pos_desc);

            FETCH c_first_sec
             INTO first_mach;

            CLOSE c_first_sec;

            UPDATE cut_machine
               SET mach_code = first_mach
             WHERE stage = i.stage AND pos_no = i.pos_no;
         ELSIF i.stage > 1
         THEN
            IF c_second_sec%ISOPEN
            THEN
               CLOSE c_second_sec;
            END IF;

            OPEN c_second_sec (i.LENGTH,
                               i.width,
                               i.height,
                               i.stage,
                               i.pos_desc
                              );

            FETCH c_second_sec
             INTO second_mach;

            CLOSE c_second_sec;

            IF second_mach IS NOT NULL
            THEN
               UPDATE cut_machine
                  SET mach_code = second_mach
                WHERE stage = i.stage AND pos_no = i.pos_no;
            ELSE
               UPDATE cut_machine
                  SET mach_code = first_mach
                WHERE stage = i.stage AND pos_no = i.pos_no;
            END IF;
         END IF;
      ELSIF i.pos_desc LIKE 'PL%'
      THEN
         IF i.stage = 1
         THEN
            IF c_first_pl%ISOPEN
            THEN
               CLOSE c_first_pl;
            END IF;

            OPEN c_first_pl (i.LENGTH, i.width, i.height, i.stage, i.pos_desc);

            FETCH c_first_pl
             INTO first_mach;

            CLOSE c_first_pl;

            UPDATE cut_machine
               SET mach_code = first_mach
             WHERE stage = i.stage AND pos_no = i.pos_no;
         ELSIF i.stage > 1
         THEN
            IF c_second_pl%ISOPEN
            THEN
               CLOSE c_second_pl;
            END IF;

            OPEN c_second_pl (i.LENGTH, i.width, i.height, i.stage,
                              i.pos_desc);

            FETCH c_second_pl
             INTO second_mach;

            CLOSE c_second_pl;

            UPDATE cut_machine
               SET mach_code = second_mach
             WHERE stage = i.stage AND pos_no = i.pos_no;
         END IF;
      END IF;
   END LOOP;
END;

--the output i am getting is.if you see below  for POS_NO 3 second line pos_desc L11 has wrong machine of PL2 as it has to be 'AM1' , i know since the range in master is set for stage 1 , the program must skip the condition if the machine is available.

select * from cut_machine order by 1,9

POS_NO	POS_DESC	WT	LENGTH	WIDTH	HEIGHT	OPER_CODE MACH_CODE	STAGE
1	L10X10		20	11,000	102	14	RE		AM2	1
1	L10X10		25	11,000	102	15	HO		AM2	2
2	H140		10	1,800	500	200	RE		B1	1
2	PL8		10	400	13	144	HO		PL2	2
3	PL12		30	189	9	88	P1		PL1	1
3	L11		33	11,000	41	15	RE		PL2	2

--required output is  

POS_NO	POS_DESC	WT	LENGTH	WIDTH	HEIGHT	OPER_CODE MACH_CODE	STAGE
1	L10X10		20	11,000	102	14	RE		AM2	1
1	L10X10		25	11,000	102	15	HO		AM2	2
2	H140		10	1,800	500	200	RE		B1	1
2	PL8		10	400	13	144	HO		PL2	2
3	PL12		30	189	9	88	P1		PL1	1
3	L11		33	11,000	41	15	RE		AM1	2





Re: Alternate for multiple cursors [message #578499 is a reply to message #576251] Thu, 28 February 2013 15:16 Go to previous messageGo to next message
Barbara Boehmer
Messages: 7937
Registered: November 2002
Location: California, USA
Senior Member
Try to keep things simple and use just SQL without PL/SQL whenever you can. All you are doing is updating one column of one table by selecting one column from another table, so all you need is one update statement with various conditions. I used nvl with two selects because you have a condition where if one select does not produce a result, then you need the other. Also note that a much simpler update would produce the same results given the data that you provided, because your data does not represent all of the conditions represented in your code.

-- starting data:
SCOTT@orcl_11gR2> SELECT mach_code,
  2  	    min_len, max_len,
  3  	    min_width, max_width,
  4  	    min_hieght, max_hieght
  5  	    profile, mach_type, mach_stage
  6  FROM   om_mach_formula
  7  /

MACH_CODE    MIN_LEN    MAX_LEN  MIN_WIDTH  MAX_WIDTH MIN_HIEGHT    PROFILE M MACH_STAGE
--------- ---------- ---------- ---------- ---------- ---------- ---------- - ----------
PL1                1        250          1         10          1        100 P          1
PL2              251        500         11         20        101        200 P          2
AM1                1      12000         40        100          1         15 S          1
AM2                1      12000        101        150          1         15 S          1
B1              1700      18000         75        610         75       1020 S          1
B2             19000      21000         75        610         75       1020 S          1
TH1            19000      21000         75        610         75       1020 S          2
TH2            19000      21000         75        610         75       1020 S          2

8 rows selected.

SCOTT@orcl_11gR2> SELECT pos_no, pos_desc, length, width, height, mach_code, stage
  2  FROM   cut_machine ORDER BY pos_no, stage
  3  /

POS_NO POS_DESC     LENGTH      WIDTH     HEIGHT MACH_CODE      STAGE
------ -------- ---------- ---------- ---------- --------- ----------
1      L10X10        11000        102         14                    1
1      L10X10        11000        102         15                    2
2      H140           1800        500        200                    1
2      PL8             400         13        144                    2
3      PL12            189          9         88                    1
3      L11           11000         41         15                    2

6 rows selected.


-- update:
SCOTT@orcl_11gR2> UPDATE cut_machine
  2  SET    mach_code =
  3  	    NVL
  4  	      ((SELECT MAX (mach_code)
  5  		FROM   om_mach_formula
  6  		WHERE  length BETWEEN min_len AND max_len
  7  		AND    width BETWEEN min_width AND max_width
  8  		AND    height BETWEEN min_hieght AND max_hieght
  9  		AND    UPPER (pos_desc) LIKE '%' || UPPER ( profile) || '%'
 10  		AND    ((pos_desc     LIKE 'PL%' AND mach_type = 'P') OR
 11  			(pos_desc NOT LIKE 'PL%' AND mach_type = 'S'))
 12  		AND    ((NVL (mach_stage, 1) = 1 AND stage = 1) OR
 13  			(NVL (mach_stage, 1) > 1 AND stage > 1))),
 14  	       (SELECT MAX (mach_code)
 15  		FROM   om_mach_formula
 16  		WHERE  length BETWEEN min_len AND max_len
 17  		AND    width BETWEEN min_width AND max_width
 18  		AND    height BETWEEN min_hieght AND max_hieght
 19  		AND    UPPER (pos_desc) LIKE '%' || UPPER ( profile) || '%'
 20  		AND    ((pos_desc     LIKE 'PL%' AND mach_type = 'P') OR
 21  			(pos_desc NOT LIKE 'PL%' AND mach_type = 'S'))
 22  		AND    ((NVL (mach_stage, 1) = 1 AND stage > 1))))
 23  /

6 rows updated.


-- results:
SCOTT@orcl_11gR2> SELECT pos_no, pos_desc, length, width, height, mach_code, stage
  2  FROM   cut_machine ORDER BY pos_no, stage
  3  /

POS_NO POS_DESC     LENGTH      WIDTH     HEIGHT MACH_CODE      STAGE
------ -------- ---------- ---------- ---------- --------- ----------
1      L10X10        11000        102         14 AM2                1
1      L10X10        11000        102         15 AM2                2
2      H140           1800        500        200 B1                 1
2      PL8             400         13        144 PL2                2
3      PL12            189          9         88 PL1                1
3      L11           11000         41         15 AM1                2

6 rows selected.

icon14.gif  Re: Alternate for multiple cursors [message #578600 is a reply to message #578499] Fri, 01 March 2013 21:57 Go to previous message
arif_md2009
Messages: 732
Registered: May 2009
Location: United Arab Emirates
Senior Member

Thanks very much for the great solution and its really very much simplified and helpful and it avoided me writing thousands of lines as i have various conditions to be implemented so only i preferred procedure.Really great work from you mam, highly appreciated.
Previous Topic: adding multiple columns to table type
Next Topic: Finding UNIQUE combination
Goto Forum:
  


Current Time: Mon Jul 28 18:57:47 CDT 2014

Total time taken to generate the page: 0.09024 seconds