Home
Resources
Library
How Can I?
Presentations
Links
Book Reviews
Downloads
User Groups
General
Contact Us
About Us
Services
Legal Notices & Terms of Use
Privacy Statement |
| Working with the IF condition in nested cursor loops |
The following demonstration, that I have modified only very slightly to make it fully generic, was provided to me by J.P. Vijaykumar [jp_vijaykumar @ yahoo.com] and I think is an excellent demonstration of the technique.
--TO FIND THE MODE OF A SET OF VALUES FROM A TABLE.
IF THE MODE IS "0" THEN TAKE THE NEXT HIGHER VALUE.
IF TWO VALUES EXIST EQUAL NUMBER OF TIMES, THEN
TAKE THE HIGHER NUMBER AS THE MODE.
If you use this in production my recommendation would be that you replace the nested cursor loops with nested BULK COLLECTs as demonstrated here [Click Here].
First let's build the demo tables and load the sample data. |
CREATE TABLE t1 (
ename VARCHAR2(20),
barcode CHAR(5),
month_abbrev CHAR(3),
code NUMBER);
CREATE TABLE t2 AS SELECT * FROM t1;
CREATE TABLE t3 AS SELECT * FROM t1;
CREATE TABLE t4 AS SELECT * FROM t1;
CREATE TABLE t5 AS SELECT * FROM t1;
INSERT INTO t1 VALUES ('JP', '06-17', 'JUN', 15);
INSERT INTO t1 VALUES ('LAKSHMI', '06-17', 'OCT', 5);
INSERT INTO t1 VALUES ('VINAY', '00-00', 'SEP', 14);
INSERT INTO t1 VALUES ('VENKATA', '09-22', 'AUG', 12);
INSERT INTO t1 VALUES ('VINEELA', '09-22', 'JAN', 4);
INSERT INTO t1 VALUES ('VEEKSHA', '00-00', 'SEP', 14);
INSERT INTO t2 VALUES ('SUBBARAO', NULL, NULL, 0);
INSERT INTO t2 VALUES ('PAPADEVI', NULL, NULL, 0);
INSERT INTO t2 VALUES ('BABU', '05-31', 'JUL', 7);
INSERT INTO t2 VALUES ('DURGA', '05-31', 'JUL', 7);
INSERT INTO t2 VALUES ('KIRAN', '00-00', 'SEP', 22);
INSERT INTO t2 VALUES ('ADITHYA', '00-00', 'SEP', 5);
INSERT INTO t2 VALUES ('NAGAMANI', '09-15', 'APR', 18);
INSERT INTO t2 VALUES ('SHAMBHU', '09-15', 'JAN', 20);
INSERT INTO t2 VALUES ('BHARADWAJ', '00-00', 'JAN', 27);
INSERT INTO t2 VALUES ('SUBHASH', '00-00', 'NOV', 3);
INSERT INTO t2 VALUES ('NAGESWARI', '02-24', 'SEP', 19);
INSERT INTO t2 VALUES ('SRINIVAS', '02-24', 'SEP', 19);
INSERT INTO t2 VALUES ('MAHESH', '00-00', 'JAN', 29);
INSERT INTO t2 VALUES ('MANASA', '00-00', 'APR', 12);
INSERT INTO t3 VALUES ('SUNDARAM', NULL, NULL, 0);
INSERT INTO t3 VALUES ('SAVITRI', NULL, NULL, 0);
INSERT INTO t3 VALUES ('VIJAYA', NULL, 'JAN', 1);
INSERT INTO t3 VALUES ('BALU', NULL, NULL, 0);
INSERT INTO t4 VALUES ('RAMAMOHAN', '05-11', 'MAY', 14);
INSERT INTO t4 VALUES ('MAHALAKSHMI', '05-11', 'APR', 26);
INSERT INTO t4 VALUES ('MALIKA', '08-28', 'DEC', 7);
INSERT INTO t4 VALUES ('KARTHIK', '08-28', 'DEC', 7);
INSERT INTO t5 VALUES ('SURYANARAYANA', NULL, 'JAN', 1);
INSERT INTO t5 VALUES ('SUBHADRA', NULL, NULL, 0);
INSERT INTO t5 VALUES ('RAMA', NULL, 'SEP', 8);
INSERT INTO t5 VALUES ('SRINIVAS', NULL, 'SEP', 8);
INSERT INTO t5 VALUES ('KARTHIK', NULL, 'SEP', 8);
INSERT INTO t5 VALUES ('DIVYA', NULL, 'SEP', 8);
INSERT INTO t5 VALUES ('NAGU', NULL, 'SEP', 8);
INSERT INTO t5 VALUES ('SIVA', NULL, 'SEP', 8);
INSERT INTO t5 VALUES ('SHREYA', NULL, 'SEP', 8);
INSERT INTO t5 VALUES ('MARUTHI', NULL, 'SEP', 8);
INSERT INTO t5 VALUES ('GAYATRY', NULL, 'SEP', 8);
COMMIT; |
| Now that the tables have been created and loaded take a look at the data and how it groups |
SELECT * FROM t1;
SELECT * FROM t2;
SELECT * FROM t3;
SELECT * FROM t4;
SELECT * FROM t5;
SELECT code, COUNT(*)
FROM t1
GROUP BY code
ORDER BY 2 DESC, 1 DESC;
SELECT code, COUNT(*)
FROM t2
GROUP BY code
ORDER BY 2 DESC, 1 DESC;
SELECT code, COUNT(*)
FROM t3
GROUP BY code
ORDER BY 2 DESC, 1 DESC;
SELECT code, COUNT(*)
FROM t4
GROUP BY code
ORDER BY 2 DESC, 1 DESC;
SELECT code, COUNT(*)
FROM t5
GROUP BY code
ORDER BY 2 DESC, 1 DESC; |
|
There is a smal view that is required by the code so build it next and examine its output. |
CREATE OR REPLACE VIEW tview AS
SELECT 1 ename, 2 code
FROM dual;
SELECT * FROM tview; |
|
And finally the anonymous block |
set serverout on
DECLARE
v_strg VARCHAR2(1024);
v_mode NUMBER;
v_rows NUMBER;
BEGIN
FOR c1 IN (
SELECT table_name FROM user_tables WHERE table_name LIKE 'T%' ORDER BY 1
) LOOP
v_strg:='CREATE OR REPLACE VIEW tview AS SELECT ename, code FROM ' || c1.table_name;
execute immediate v_strg;
dbms_output.put_line(c1.table_name);
v_mode:=0;
v_rows:=0;
FOR c2 IN (
SELECT code, COUNT(*) num_rows FROM tview GROUP BY code ORDER BY 2 DESC, 1 DESC
) LOOP
IF ((v_mode = 0) AND (v_rows = 0)) THEN
v_mode := c2.code;
v_rows := c2.num_rows;
ELSIF ((v_mode = 0) AND (v_rows <> 0)) THEN
v_mode :=c2.code;
v_rows :=c2.num_rows;
END IF;
END LOOP;
dbms_output.put_line(v_mode||' '||v_rows);
END LOOP;
END;
/ |
| J.P. also provided the following references that will likely be of great value if you wish to look deeper into the subject. |
Reference:
http://www.purplemath.com/modules/meanmode.htm
http://www.mathgoodies.com/lessons/vol8/mode.html
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:17329730362010
http://www.dbasupport.com/oracle/ora10g/multi_table_loop.shtml |
|
J.P. Provided two alternative solutions here is the second example in which creates a view and makes use of ROWNUM to obtain the solution. |
CREATE OR REPLACE VIEW tview AS
SELECT 1 ename, 2 code
FROM dual;
set serveroutput on
DECLARE
v_strg VARCHAR2(1024);
v_mode NUMBER;
v_rows NUMBER;
BEGIN
FOR c1 IN (
SELECT table_name FROM user_tables WHERE table_name LIKE 'T%' ORDER BY 1
) LOOP
v_strg:='CREATE OR REPLACE VIEW tview AS SELECT code, COUNT(*) num_rows ' ||
'FROM ' || c1.table_name || ' GROUP BY code ORDER BY num_rows DESC, code DESC';
execute immediate v_strg;
dbms_output.put_line(c1.table_name);
execute immediate 'SELECT code, num_rows FROM tview WHERE code <> 0 AND rownum =1'
INTO v_mode, v_rows;
dbms_output.put_line(v_mode||' '||v_rows);
END LOOP;
END;
/ |
|
And here is the third making use of the CASE function |
CREATE OR REPLACE VIEW tview AS
SELECT 1 ename, 2 code
FROM dual;
set serveroutput on
DECLARE
v_strg VARCHAR2(1024);
v_mode NUMBER;
v_rows NUMBER;
BEGIN
FOR c1 IN (
SELECT table_name FROM user_tables WHERE table_name LIKE 'T%' ORDER BY 1
) LOOP
v_strg:='CREATE OR REPLACE VIEW tview AS SELECT ename, code FROM ' || c1.table_name;
execute immediate v_strg;
dbms_output.put_line(c1.table_name);
FOR c2 IN (
SELECT CASE WHEN (
SELECT code FROM (
SELECT rownum row_num, t.* FROM (
SELECT code, COUNT(*) num_rows FROM tview GROUP BY code ORDER BY num_rows DESC) t)
WHERE row_num=1) = 0
AND (
SELECT COUNT(*) FROM (
SELECT code, COUNT(*) num_rows
FROM tview
GROUP BY code
ORDER BY num_rows DESC)) > 1 THEN
(
SELECT code FROM (
SELECT rownum row_num, t.* FROM (
SELECT code, COUNT(*) num_rows
FROM tview
GROUP BY code
ORDER BY num_rows desc) t)
WHERE row_num=2)
ELSE
(
SELECT code FROM (
SELECT rownum row_num, t.* FROM (
SELECT code, COUNT(*) num_rows
FROM tview
GROUP BY code
ORDER BY num_rows desc) t)
WHERE row_num=1) END code FROM dual) LOOP
SELECT num_rows
INTO v_rows
FROM(
SELECT code, COUNT(*) num_rows
FROM tview
GROUP BY code
ORDER BY num_rows DESC, code DESC)
WHERE code = c2.code;
dbms_output.put_line(c2.code || ' ' || v_rows);
END LOOP;
END LOOP;
END;
/ |
|
I really appreciate the donation of this code and hope everyone finds it of value to gain a deeper appreciation of the power and flexibility of PL/SQL. |
|
