CODEWITHSUNDEEP
sqloracle-databaseplsql
John Wick
John Wick

Reputation: 745

Better way to assign MAX values to variables?

I am trying to define a Variable ("COUNT") as the count of a query so that I can use it in my conditional statement later in my PLSQL block. I get the count using the following syntax: 

Select count(value_tx) from value where trunc(date) = trunc(sysdate)

The only way I know how to do this currently is to use a cursor. Is there a better way to do this? My approach using the cursor is as follows: 

Create or Replace Procedure TEST IS
CURSOR C1 is
select count(value_tx) as COUNTE
  from value
  where trunc(date) = trunc(sysdate)
  group by hr_utc;
l_var c1%ROWTYPE;
BEGIN
Open c1;
        FETCH c1 into l_var;
        IF l_var.counte > 0 THEN  DBMS_OUTPUT.PUT_LINE(l_var.COUNTE);
        END IF;
Close c1;
END TEST;

thanks in advance.

Upvotes: 0

Views: 295

Answers (2)

krokodilko
krokodilko

Reputation: 36127

Is there a better way to do this?

Sure it is.

The problem is this condition: where trunc(date) = ..... This prevent RDBMS from using an an index on date column. If the table is big, this can cause performance problems. I am not going to explain the reason, you can find an explanation elsewhere, for example here: Why do functions on columns prevent the use of indexes?

You need to replace this condition with:

 `where date >= trunc(sysdate) AND date < trunc(sysdate) + 1

or

where date >= trunc(sysdate) AND date < trunc(sysdate) + interval '1' day

Upvotes: 1

sticky bit
sticky bit

Reputation: 37477

Depends on what is a "better way" for you. An alternative is a FOR loop with an implicit cursor over a SELECT .

FOR R IN (SELECT COUNT(VALUE_TX) AS COUNTE
                 FROM VALUE
                 WHERE TRUNC(DATE) = TRUNC(SYSDATE)
                 GROUP BY HR_UTC) LOOP
  IF R.COUNTE > 0 THEN
    DBMS_OUTPUT.PUT_LINE(R.COUNTE);
  END IF;
END LOOP;

It's a convenient syntactical shortcut, if that's something you count as "better" here.

Upvotes: 2

Related Questions