CODEWITHSUNDEEP
sqloracle-databaseplsql
Nitesh
Nitesh

Reputation: 31

Need PL/SQL block to retrieve data as per my requirement

I'm having a table ABC. In this table, on the basis of AGE, I need to find Duplicates and for those duplicate records, I need some selected colums in dbms_output.put_line.

DECLARE 
CURSOR C_AGE IS 
SELECT AGE FROM ABC
GROUP BY AGE
HAVING COUNT(1)>1 ;
BEGIN
FOR RECS IN C_AGE
LOOP
DBMS_OUTPUT.PUT_LINE (recs.age);
END LOOP;
end;

In above code, instead of displaying AGE, I need ID, Name, AGE columns in output. Pls suggest.

Below is alternative select statement for the same. But I don't wanna use sub-query. Bcoz in Prod I have one heavy table where I wanna apply same logic. So need to go with cursor.

select ID,Name,AGE from abc where age in (
SELECT age FROM ABC
GROUP BY AGE
HAVING COUNT(1)>1);

Upvotes: 0

Views: 121

Answers (1)

Kaushik Nayak
Kaushik Nayak

Reputation: 31696

But I don't wanna use sub-query. Bcoz in Prod I have one heavy table where I wanna apply same logic. So need to go with cursor.

Why do you presume that looping through the CURSOR to display in a PL/SQL block is efficient than a simple select statement?

I could give you this query as another alternative to your sub-query select, using COUNT analytic function.

SELECT ID
    ,Name
    ,AGE
FROM (
    SELECT a.*
        ,COUNT(*) OVER (PARTITION BY AGE) AS c
    FROM abc a
    ) where c > 1;

If you still want to apply same logic in PL/SQL using a loop, do it only if you are performing any DMLs (INSERT,UPDATE etc, dynamic DDLs etc. Otherwise, pure sql select or single open cursor with select would suffice for displaying output or passing a CURSOR to external application resp.

Upvotes: 1

Related Questions