Home » SQL & PL/SQL » SQL & PL/SQL » Multi Array - MultiSet - or other?? (10.2)
| Multi Array - MultiSet - or other?? [message #613002] |
Mon, 28 April 2014 14:03  |
 |
lott42_gmail
Messages: 146
Registered: June 2010
|
Senior Member |
|
|
I'm not sure what type of "data structure" that I should be researching. Is my need for a Multi dim. array, a Multiset(not sure how to use yet) or some other "structure". I would like to be able to store all "order" data relating to each individual order.
I have a parent table(t_order) with two child tables(t_operator_orders, t_equipment_orders). The two child table actually contain more columns than this. The other two tables(t_operator, t_equipment) just hold data indirectly related to an "order".
I would like to select & display the parent and child info in a report type format.
Ex.
Order 1
Operator: Equipment:
Jane Camera
John
Order 2
Operator: Equipment:
John Camera
TV
Order 3
I created a quick Proc. below for displaying data using multi cursors. NOTE: The output is not exactly what I'm looking for
Order 1
Operator:
Jane
John
Equipment:
Camera
Order 2
Operator:
John
Equipment:
Camera
TV
Order 3
Proc:
CREATE OR REPLACE PROCEDURE MultiSet1 IS
v_cnt NUMBER;
v_old_order_id t_order.order_id%TYPE := 0;
v_old_operator_id t_operator.operator_id%TYPE := 0;
v_old_equipment_id t_equipment.equipment_id%TYPE := 0;
BEGIN
FOR r_order IN (SELECT o.order_id, o.order_descr
FROM t_order o
ORDER BY o.order_id) LOOP
IF v_old_order_id <> r_order.order_id THEN
v_old_operator_id := 0;
v_old_equipment_id := 0;
DBMS_OUTPUT.PUT_LINE(r_order.order_descr);
FOR r_operator IN (SELECT op.operator_id, op.operator_descr
FROM t_operator op, t_operator_orders oo
WHERE oo.operator_id = op.operator_id(+)
AND oo.order_id = r_order.order_id
ORDER BY oo.order_id, op.operator_descr) LOOP
--First row
IF v_old_operator_id = 0 THEN
DBMS_OUTPUT.PUT_LINE(chr(9) || 'Operator: ');
END IF;
DBMS_OUTPUT.PUT_LINE(chr(9) || r_operator.operator_descr);
v_old_operator_id := r_operator.operator_id;
END LOOP;
FOR r_equipment IN (SELECT eo.equipment_id, e.equipment_descr
FROM t_order o,
t_equipment e,
t_equipment_orders eo
WHERE o.order_id = eo.order_id(+)
AND eo.equipment_id = e.equipment_id(+)
AND eo.order_id = r_order.order_id
ORDER BY o.order_id) LOOP
--First row
IF v_old_equipment_id = 0 THEN
DBMS_OUTPUT.PUT_LINE(chr(9) || chr(9) || 'Equipment: ');
END IF;
DBMS_OUTPUT.PUT_LINE(chr(9) || chr(9) ||
r_equipment.equipment_descr);
v_old_equipment_id := r_equipment.equipment_id;
END LOOP;
END IF;
v_cnt := v_cnt + 1;
v_old_order_id := r_order.order_id;
END LOOP;
END MultiSet1;
Here is my DDL/DML
/*
--DDL
create table t_order(
order_id number(10,0),
order_descr varchar2(10),
constraint pk_order_id primary key (order_id)
);
create table t_operator(
operator_id number(10,0),
operator_descr varchar2(10),
constraint pk_operator_id primary key (operator_id)
);
create table t_operator_orders(
operator_id number(10,0),
order_id number(10,0),
constraint pk_operator_id_order_id primary key (operator_id, order_id)
);
create table t_equipment(
equipment_id number(10,0),
equipment_descr varchar2(10),
constraint pk_equipment_id primary key (equipment_id)
);
create table t_equipment_orders(
equipment_id number(10,0),
order_id number(10,0),
constraint pk_equipment_id_order_id primary key (equipment_id, order_id)
);
*/
/*
--DML
INSERT INTO t_order VALUES (1, 'Order 1');
INSERT INTO t_order VALUES (2, 'Order 2');
INSERT INTO t_order VALUES (3, 'Order 3');
INSERT INTO t_operator VALUES (1, 'Jane');
INSERT INTO t_operator VALUES (2, 'John');
INSERT INTO t_operator_orders VALUES (1, 1);
INSERT INTO t_operator_orders VALUES (2, 1);
INSERT INTO t_operator_orders VALUES (2, 2);
INSERT INTO t_equipment VALUES (1, 'Camera');
INSERT INTO t_equipment VALUES (2, 'TV');
INSERT INTO t_equipment_orders VALUES (1, 1);
INSERT INTO t_equipment_orders VALUES (1, 2);
INSERT INTO t_equipment_orders VALUES (2, 2);
*/
/*
--DDL Multiset (is this even correct??)
CREATE OR REPLACE TYPE t_operator_orders_typ AS OBJECT
(
operator_id NUMBER(10, 0),
operator_descr VARCHAR(10)
)
;
CREATE OR REPLACE TYPE t_operator_info_typ AS TABLE OF t_operator_orders_typ;
CREATE OR REPLACE TYPE t_equipment_orders_typ AS OBJECT
(
equipment_id NUMBER(10, 0),
equipment_descr VARCHAR(10)
);
CREATE OR REPLACE TYPE t_equipment_info_typ AS TABLE OF t_equipment_orders_typ;
*/
***
***
Using Multiset sql
/*
--Using MultiSet?? - Not sure if what I'm doing here is correct??
SELECT o.order_id,
o.order_descr,
CAST(MULTISET (SELECT o.order_id, op.operator_descr
FROM t_order o, t_operator op, t_operator_orders oo
WHERE o.order_id = oo.order_id
AND oo.operator_id = op.operator_id
ORDER BY o.order_id, op.operator_descr) AS
t_operator_info_typ) operator_info,
CAST(MULTISET (SELECT o.order_id, e.equipment_descr
FROM t_order o, t_equipment e, t_equipment_orders eo
WHERE o.order_id = eo.order_id
AND eo.equipment_id = e.equipment_id
ORDER BY eo.order_id, e.equipment_descr) AS
t_equipment_info_typ) equipment_info
FROM t_order o;
*/
Here's how the data looks like when it's returned
ORDER_ID ORDER_DESCR OPERATOR_INFO EQUIPMENT_INFO
1 Order 1 <Collection> <Collection>
2 Order 2 <Collection> <Collection>
3 Order 3 <Collection> <Collection>
order_id 1 - "Operator Info" (doesn't "seem" correct to me??)
OPERATOR_ID OPERATOR_DESCR
1 Jane
1 John
2 John
|
|
|
|
| Re: Multi Array - MultiSet - or other?? [message #613006 is a reply to message #613002] |
Mon, 28 April 2014 15:10  |
 |
Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
I might use just plain SQL with some outer joins, as demonstrated below.
-- test data:
SCOTT@orcl12c> SELECT * FROM t_order
2 /
ORDER_ID ORDER_DESC
---------- ----------
1 Order 1
2 Order 2
3 Order 3
3 rows selected.
SCOTT@orcl12c> SELECT * FROM t_operator
2 /
OPERATOR_ID OPERATOR_D
----------- ----------
1 Jane
2 John
2 rows selected.
SCOTT@orcl12c> SELECT * FROM t_operator_orders
2 /
OPERATOR_ID ORDER_ID
----------- ----------
1 1
2 1
2 2
3 rows selected.
SCOTT@orcl12c> SELECT * FROM t_equipment
2 /
EQUIPMENT_ID EQUIPMENT_
------------ ----------
1 Camera
2 TV
2 rows selected.
SCOTT@orcl12c> SELECT * FROM t_equipment_orders
2 /
EQUIPMENT_ID ORDER_ID
------------ ----------
1 1
1 2
2 2
3 rows selected.
-- query:
SCOTT@orcl12c> SET UNDERLINE OFF
SCOTT@orcl12c> COLUMN "Order:" HEADING ' '
SCOTT@orcl12c> BREAK ON "Order:" SKIP 1
SCOTT@orcl12c> SELECT NVL (o.order_descr, e.order_descr) "Order:",
2 o.operator_descr "Operator:",
3 e.equipment_descr "Equipment:"
4 FROM (SELECT ord.order_id, ord.order_descr, op.operator_descr,
5 ROW_NUMBER () OVER (PARTITION BY ord.order_id ORDER BY op.operator_id) rn
6 FROM t_order ord, t_operator_orders opord, t_operator op
7 WHERE ord.order_id = opord.order_id(+)
8 AND opord.operator_id = op.operator_id(+)) o
9 FULL OUTER JOIN
10 (SELECT ord.order_id, ord.order_descr, eq.equipment_descr,
11 ROW_NUMBER () OVER (PARTITION BY ord.order_id ORDER BY eq.equipment_id) rn
12 FROM t_order ord, t_equipment_orders eord, t_equipment eq
13 WHERE ord.order_id = eord.order_id(+)
14 AND eord.equipment_id = eq.equipment_id(+)) e
15 ON o.order_id = e.order_id AND o.rn = e.rn
16 ORDER BY 1, 2, 3
17 /
Operator: Equipment:
Order 1 Jane Camera
John
Order 2 John Camera
TV
Order 3
5 rows selected.
|
|
|
|
|
|
| Re: Multi Array - MultiSet - or other?? [message #613008 is a reply to message #613007] |
Mon, 28 April 2014 16:02 |
|
Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
lott42 wrote on Mon, 28 April 2014 13:42
Was I close on how to use the "Multiset" or am I totally off on that concept??
SCOTT@orcl12c> SELECT o.order_id,
2 o.order_descr,
3 CAST(MULTISET (SELECT oo.order_id, op.operator_descr
4 FROM t_operator op, t_operator_orders oo
5 WHERE o.order_id = oo.order_id
6 AND oo.operator_id = op.operator_id
7 ORDER BY op.operator_descr) AS
8 t_operator_info_typ) operator_info,
9 CAST(MULTISET (SELECT eo.order_id, e.equipment_descr
10 FROM t_equipment e, t_equipment_orders eo
11 WHERE o.order_id = eo.order_id
12 AND eo.equipment_id = e.equipment_id
13 ORDER BY e.equipment_descr) AS
14 t_equipment_info_typ) equipment_info
15 FROM t_order o;
ORDER_ID ORDER_DESC
---------- ----------
OPERATOR_INFO(OPERATOR_ID, OPERATOR_DESCR)
--------------------------------------------------------------------------------
EQUIPMENT_INFO(EQUIPMENT_ID, EQUIPMENT_DESCR)
--------------------------------------------------------------------------------
1 Order 1
T_OPERATOR_INFO_TYP(T_OPERATOR_ORDERS_TYP(1, 'Jane'), T_OPERATOR_ORDERS_TYP(1, '
John'))
T_EQUIPMENT_INFO_TYP(T_EQUIPMENT_ORDERS_TYP(1, 'Camera'))
2 Order 2
T_OPERATOR_INFO_TYP(T_OPERATOR_ORDERS_TYP(2, 'John'))
T_EQUIPMENT_INFO_TYP(T_EQUIPMENT_ORDERS_TYP(2, 'Camera'), T_EQUIPMENT_ORDERS_TYP
(2, 'TV'))
3 Order 3
T_OPERATOR_INFO_TYP()
T_EQUIPMENT_INFO_TYP()
3 rows selected.
|
|
|
|
| Re: Multi Array - MultiSet - or other?? [message #613010 is a reply to message #613008] |
Mon, 28 April 2014 16:38 |
|
lott42_gmail
Messages: 146
Registered: June 2010
|
Senior Member |
|
|
Thanks again Barbara. I see where my mistake was now. I created a quick Proc. using Multiset. Hopefully, this will serve as an example for how I can utilize this structure in the future. Of course, I need to clean it up some more....
CREATE OR REPLACE PROCEDURE MultiSet2 IS
BEGIN
FOR r_order IN (SELECT o.order_id,
o.order_descr,
CAST(MULTISET
(SELECT op.operator_id, op.operator_descr
FROM t_operator op, t_operator_orders oo
WHERE o.order_id = oo.order_id
AND oo.operator_id = op.operator_id
ORDER BY op.operator_descr) AS
t_operator_info_typ) operator_info,
CAST(MULTISET
(SELECT e.equipment_id, e.equipment_descr
FROM t_equipment e, t_equipment_orders eo
WHERE o.order_id = eo.order_id
AND eo.equipment_id = e.equipment_id
ORDER BY e.equipment_descr) AS
t_equipment_info_typ) equipment_info
FROM t_order o) LOOP
DBMS_OUTPUT.PUT_LINE(r_order.order_descr);
IF r_order.operator_info IS NOT EMPTY THEN
DBMS_OUTPUT.PUT_LINE('Operator: ');
FOR i IN r_order.operator_info.first .. r_order.operator_info.last LOOP
DBMS_OUTPUT.PUT_LINE('(' || r_order.operator_info(i).operator_id || ') ' || r_order.operator_info(i)
.operator_descr);
END LOOP;
END IF;
IF r_order.equipment_info IS NOT EMPTY THEN
DBMS_OUTPUT.PUT_LINE('Equipment: ');
FOR i IN r_order.equipment_info.first .. r_order.equipment_info.last LOOP
DBMS_OUTPUT.PUT_LINE('(' || r_order.equipment_info(i).equipment_id || ') ' || r_order.equipment_info(i)
.equipment_descr);
END LOOP;
END IF;
END LOOP;
END MultiSet2;
|
|
|
|
| Re: Multi Array - MultiSet - or other?? [message #613011 is a reply to message #613008] |
Mon, 28 April 2014 16:42 |
|
Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
The following uses the multiset method, but you have to put it back into tables to display it and I would prefer not to hassle with the types if I don't need to. I am providing this just to answer your question and for comparison, but don't recommend it.
SCOTT@orcl12c> SELECT NVL (t1.order_descr, t2.order_descr) "Order:",
2 t1.operator_descr "Operator:",
3 t2.equipment_descr "Equipment:"
4 FROM (SELECT o.order_id, o.order_descr, operator_info.operator_descr,
5 ROW_NUMBER () OVER (PARTITION BY o.order_id ORDER BY operator_descr) rn
6 FROM t_order o,
7 TABLE (CAST(MULTISET (SELECT oo.order_id, op.operator_descr
8 FROM t_operator op, t_operator_orders oo
9 WHERE o.order_id = oo.order_id
10 AND oo.operator_id = op.operator_id
11 ORDER BY op.operator_descr) AS
12 t_operator_info_typ))(+) operator_info) t1
13 FULL OUTER JOIN
14 (SELECT o.order_id, o.order_descr, equipment_info.equipment_descr,
15 ROW_NUMBER () OVER (PARTITION BY o.order_id ORDER BY equipment_descr) rn
16 FROM t_order o,
17 TABLE (CAST(MULTISET (SELECT eo.order_id, e.equipment_descr
18 FROM t_equipment e, t_equipment_orders eo
19 WHERE o.order_id = eo.order_id
20 AND eo.equipment_id = e.equipment_id
21 ORDER BY e.equipment_descr) AS
22 t_equipment_info_typ))(+) equipment_info) t2
23 ON t1.order_id = t2.order_id AND t1.rn = t2.rn
24 ORDER BY 1, 2, 3
25 /
Order: Operator: Equipment:
---------- ---------- ----------
Order 1 Jane Camera
Order 1 John
Order 2 John Camera
Order 2 TV
Order 3
5 rows selected.
|
|
|
|
Goto Forum:
Current Time: Wed Apr 24 07:19:30 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
)