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  |
|
|
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  |
 |
Barbara Boehmer
Messages: 7669
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.
|
|
|
|
 Re: Alternate for multiple cursors [message #578600 is a reply to message #578499] |
Fri, 01 March 2013 21:57 |
|
|
|
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.
|
|
|
|
Goto Forum:
Current Time: Sat May 25 13:29:02 CDT 2013
Total time taken to generate the page: 0.77474 seconds
|
] 
Members
Search
Help
Register
Login
Home
