CODEWITHSUNDEEP
plsqloracle9iplsqldeveloper
dhawannaval
dhawannaval

Reputation: 3

How do you extract number of rows in Count(*), and send to a variable. (Pl/SQL)

I'm trying extract the number of rows using the count(*) with the respect of it's condition and sending it to a variable for later use.

How do I create variables for the number of rows within a table. Thank you.

VARIABLE 
VAR CHAR(10)

SELECT COUNT(*) INTO :VAR 

FROM [Database]
WHERE [condition 1]
AND [condition 2]

Upvotes: 0

Views: 12547

Answers (2)

Tony Andrews
Tony Andrews

Reputation: 132620

You need to use PL/SQL to do the select:

SQL> variable var number
SQL> begin
  2     select count(*)
  3     into :var
  4     from all_tables
  5     where owner = user;
  6  end;
  7  /

PL/SQL procedure successfully completed.

SQL> print var

       VAR
----------
       163

Upvotes: 2

Aycan Yaşıt
Aycan Yaşıt

Reputation: 2114

What about passing the result of the whole query to the variable?

VARIABLE 
VAR CHAR(10)

(
SELECT COUNT(*) 
FROM [Database]
WHERE [condition 1]
AND [condition 2]
)
INTO :VAR

Upvotes: 0

Related Questions