Home » SQL & PL/SQL » SQL & PL/SQL » Need Help on Loop Program in PL/Sql (Oracle 10.2.0)
Need Help on Loop Program in PL/Sql [message #364480] Mon, 08 December 2008 16:09 Go to next message
malladipavan
Messages: 11
Registered: December 2008
Location: Nashville
Junior Member
Hi

I got a question...

I have a table called Persons;

there are five columns in it;

1) first_Name 2) Middle_Name 3) Last_Name 4) address

5) PhoneNo

I want to run a query on those 5 columns

1) select Count(first_name) from Persons;

2) select count(last_name) from persons;

|
|
|
5) select count(PhoneNo) from Persons;

Instead of writing all the times the same query by changing the columnname.....Is there any chance to run all at the same time
kinda loop program... if anybody have any idea please let me Know..

(Don't say tht it's just 5 columns Just write the same queries for all the 5...I am just saying it as a example i have 5000 columns to work the same query thts y i am asking is there any loop kinda program that i can just mention the column names and get the result for all the 5000 columns at a time)


Thanks
Re: Need Help on Loop Program in PL/Sql [message #364483 is a reply to message #364480] Mon, 08 December 2008 16:15 Go to previous messageGo to next message
asmurali
Messages: 2
Registered: December 2008
Location: dallas
Junior Member
are you looking for count of distinct values for all the columns in the table? If so count(column name ) will not work. you get same number for all the columns for the same table.

Please specify what exactly you want to achieve.

Thanks
asm
Re: Need Help on Loop Program in PL/Sql [message #364484 is a reply to message #364480] Mon, 08 December 2008 16:22 Go to previous messageGo to next message
malladipavan
Messages: 11
Registered: December 2008
Location: Nashville
Junior Member
Srry bro u didn't get my point...

Even if i give

Count(columnname) from tablename;

it picks only not null values so there will be differnce because it's not sqlserver it's oracle...

i am not running count(*) to get the same result for all the columns

in oracle when you give count(colname) it picks only notnull values ...so there will be difference in the numbers

thanks
Re: Need Help on Loop Program in PL/Sql [message #364493 is a reply to message #364480] Mon, 08 December 2008 18:17 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8633
Registered: November 2002
Location: California, USA
Senior Member
There are various ways that you could go about this. Most methods involve some method of looping through the user_tab_columns data dictionary view to obtain the column names to form the select statements and execute them dynamically. You could either:

1. Run a query to generate the queries, either spooling them to a sql file to run them as a script or copying and pasting them, or

2. Loop a pl/sql cursor, execute each select dynamically and output the results using dbms_output or

3. Create a function to do the dynamic execution so that you could call it from a sql select statement, or

4. Gather table statistics or schema statistics to populate the num_rows and num_nulls columns in the data dictionary views, then select directly from them.

I have demonstrated all four methods mentioned above using the Oracle dept and emp demo tables.

1. Run a query to generate the queries, either spooling them to a sql file to run them as a script or copying and pasting them:
SCOTT@orcl_11g> SELECT 'SELECT COUNT (' || column_name || ') FROM ' || table_name || ';'
  2  FROM   user_tab_columns
  3  WHERE  table_name IN ('DEPT', 'EMP')
  4  ORDER  BY table_name, column_name
  5  /

'SELECTCOUNT('||COLUMN_NAME||')FROM'||TABLE_NAME||';'
--------------------------------------------------------------------------------
SELECT COUNT (DEPTNO) FROM DEPT;
SELECT COUNT (DNAME) FROM DEPT;
SELECT COUNT (LOC) FROM DEPT;
SELECT COUNT (COMM) FROM EMP;
SELECT COUNT (DEPTNO) FROM EMP;
SELECT COUNT (EMPNO) FROM EMP;
SELECT COUNT (ENAME) FROM EMP;
SELECT COUNT (HIREDATE) FROM EMP;
SELECT COUNT (JOB) FROM EMP;
SELECT COUNT (MGR) FROM EMP;
SELECT COUNT (SAL) FROM EMP;

11 rows selected.
SCOTT@orcl_11g> 


2. Loop a pl/sql cursor, execute each select dynamically and output the results using dbms_output:
SCOTT@orcl_11g> SET SERVEROUTPUT ON
SCOTT@orcl_11g> DECLARE
  2    v_count NUMBER;
  3  BEGIN
  4    DBMS_OUTPUT.PUT_LINE
  5  	 ('TABLE_NAME			   COLUMN_NAME			   NON_NULL_VALUES');
  6    DBMS_OUTPUT.PUT_LINE
  7  	 ('------------------------------  ------------------------------  ---------------');
  8    FOR r IN
  9  	 (SELECT table_name, column_name
 10  	  FROM	 user_tab_columns
 11  	  WHERE  table_name IN ('DEPT', 'EMP')
 12  	  ORDER  BY table_name, column_name)
 13    LOOP
 14  	 EXECUTE IMMEDIATE
 15  	   'SELECT COUNT (' || r.column_name || ') FROM ' || r.table_name
 16  	   INTO v_count;
 17  	   DBMS_OUTPUT.PUT_LINE
 18  	     (RPAD (r.table_name, 32) ||
 19  	      RPAD (r.column_name, 32) ||
 20  	      LPAD (v_count, 15));
 21    END LOOP;
 22  END;
 23  /
TABLE_NAME                      COLUMN_NAME                     NON_NULL_VALUES
------------------------------  ------------------------------  ---------------
DEPT                            DEPTNO                                        4
DEPT                            DNAME                                         4
DEPT                            LOC                                           4
EMP                             COMM                                          4
EMP                             DEPTNO                                       14
EMP                             EMPNO                                        14
EMP                             ENAME                                        14
EMP                             HIREDATE                                     14
EMP                             JOB                                          14
EMP                             MGR                                          13
EMP                             SAL                                          14

PL/SQL procedure successfully completed.


3. Create a function to do the dynamic execution so that you could call it from a sql select statement
SCOTT@orcl_11g> CREATE OR REPLACE FUNCTION col_count
  2    (p_table_name  IN VARCHAR2,
  3  	p_column_name IN VARCHAR2)
  4    RETURN NUMBER
  5  AS
  6    v_count		 NUMBER;
  7  BEGIN
  8    EXECUTE IMMEDIATE
  9  	 'SELECT COUNT (' || p_column_name || ') FROM ' || p_table_name
 10  	 INTO v_count;
 11    RETURN v_count;
 12  END col_count;
 13  /

Function created.

SCOTT@orcl_11g> SHOW ERRORS
No errors.
SCOTT@orcl_11g> SELECT table_name, column_name,
  2  	    col_count (table_name, column_name) AS non_null_values
  3  FROM   user_tab_columns
  4  WHERE  table_name IN ('DEPT', 'EMP')
  5  ORDER  BY table_name, column_name
  6  /

TABLE_NAME                     COLUMN_NAME                    NON_NULL_VALUES
------------------------------ ------------------------------ ---------------
DEPT                           DEPTNO                                       4
DEPT                           DNAME                                        4
DEPT                           LOC                                          4
EMP                            COMM                                         4
EMP                            DEPTNO                                      14
EMP                            EMPNO                                       14
EMP                            ENAME                                       14
EMP                            HIREDATE                                    14
EMP                            JOB                                         14
EMP                            MGR                                         13
EMP                            SAL                                         14

11 rows selected.


4. Gather table statistics or schema statistics to populate the num_rows and num_nulls columns in the data dictionary views, then select directly from them
SCOTT@orcl_11g> -- EXEC DBMS_STATS.GATHER_SCHEMA_STATS (USER) or
SCOTT@orcl_11g> EXEC DBMS_STATS.GATHER_TABLE_STATS (USER, 'DEPT')

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> EXEC DBMS_STATS.GATHER_TABLE_STATS (USER, 'EMP')

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> SELECT c.table_name, c.column_name,
  2  	    t.num_rows - c.num_nulls AS non_null_values
  3  FROM   user_tables t, user_tab_columns c
  4  WHERE  t.table_name = c.table_name
  5  AND    c.table_name IN ('DEPT', 'EMP')
  6  ORDER  BY c.table_name, c.column_name
  7  /

TABLE_NAME                     COLUMN_NAME                    NON_NULL_VALUES
------------------------------ ------------------------------ ---------------
DEPT                           DEPTNO                                       4
DEPT                           DNAME                                        4
DEPT                           LOC                                          4
EMP                            COMM                                         4
EMP                            DEPTNO                                      14
EMP                            EMPNO                                       14
EMP                            ENAME                                       14
EMP                            HIREDATE                                    14
EMP                            JOB                                         14
EMP                            MGR                                         13
EMP                            SAL                                         14

11 rows selected.

SCOTT@orcl_11g>




Re: Need Help on Loop Program in PL/Sql [message #364497 is a reply to message #364480] Mon, 08 December 2008 20:04 Go to previous messageGo to next message
wmgonzalbo
Messages: 98
Registered: November 2008
Member
Great stuff! I was about to use #3.. Nice! Smile
Re: Need Help on Loop Program in PL/Sql [message #364500 is a reply to message #364497] Mon, 08 December 2008 20:31 Go to previous messageGo to next message
cmerry
Messages: 109
Registered: November 2005
Location: Idaho
Senior Member
A different twist on the function solution is to use a table-valued function instead.

CREATE OR REPLACE TYPE col_count_obj IS OBJECT
 ( col_name  VARCHAR2(30)
 , cnt       NUMBER );
/

CREATE OR REPLACE TYPE col_count_tbl IS TABLE OF col_count_obj;
/

CREATE OR REPLACE FUNCTION col_count ( p_tbl  IN VARCHAR2 ) 
RETURN col_count_tbl PIPELINED IS
  CURSOR cur IS 
  SELECT column_name 
    FROM user_tab_columns 
   WHERE table_name = UPPER(p_tbl);
  l_cnt  NUMBER;
BEGIN
  FOR rec IN cur LOOP
    EXECUTE IMMEDIATE 'SELECT COUNT(' || rec.column_name || ') FROM ' || UPPER(p_tbl)
    INTO l_cnt;
    PIPE ROW (col_count_obj(rec.column_name, l_cnt));
  END LOOP;
  RETURN;
END col_count;
/
 
CREATE TABLE test
 ( c1  NUMBER
 , c2  NUMBER
 , c3  NUMBER );

INSERT INTO test VALUES (1, 2, 3);
INSERT INTO test VALUES (1, NULL, 3);
INSERT INTO test VALUES (1, NULL, NULL);

SELECT * FROM TABLE(col_count('test'));

COL_NAME                              CNT
------------------------------ ----------
C1                                      3
C2                                      1
C3                                      2
Re: Need Help on Loop Program in PL/Sql [message #364521 is a reply to message #364480] Tue, 09 December 2008 00:08 Go to previous messageGo to next message
panyam
Messages: 146
Registered: May 2008
Senior Member

Hi

Barbara ...a really Nice Explanation.

hat's offf ....to you
Re: Need Help on Loop Program in PL/Sql [message #364533 is a reply to message #364497] Tue, 09 December 2008 00:34 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
wmgonzalbo wrote on Tue, 09 December 2008 03:04
Great stuff! I was about to use #3.. Nice! Smile

These are examples, don't just copy and paste them, take care of SQL injection.

Regards
Michel

Re: Need Help on Loop Program in PL/Sql [message #364542 is a reply to message #364533] Tue, 09 December 2008 00:55 Go to previous messageGo to next message
wmgonzalbo
Messages: 98
Registered: November 2008
Member
Oh what i meant was i was about to post my solution like #3 of Barbara Boehmer, but she already explained it well, more than what i can think of at the moment.. Smile
Re: Need Help on Loop Program in PL/Sql [message #364585 is a reply to message #364542] Tue, 09 December 2008 03:46 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You could use a query like this to generate all the counts as a single piee of SQL, which should execute substantially faster than running multiple SQLs on the same table:
select case when column_id = 1 then 'SELECT count('||column_name||')'
            when lead(column_id) over (order by column_id) is null then ', count('||column_name||') FROM '||table_name
            else ', count('||column_name||')'
            end
from user_tab_columns
where table_name = '<table_name>';
Previous Topic: Deadlock Issue in the trigger .......please do help
Next Topic: dates between two dates using select statement
Goto Forum:
  


Current Time: Tue Dec 06 08:38:10 CST 2016

Total time taken to generate the page: 0.14116 seconds