Declaring Number variable

[NayaDBA]

Hi guys

DECLARE

V_num1 NUMBER := 0;
V_num2 NUMBER (4) := 0;

BEGIN

...

END;

Is there any performance gain if declare a variable as declared for V_num2. Bcos for in a annonymous block, I need a number of such (more than 100) variables to declare.

Is it V_num2 take less memory than V_num1. Or is it really does'nt matter.

Pls enlighten me.
Regards
Bhaskar

[slimdave]

According to PL/SQL User's Guide ...

PLS_INTEGER

You use the PLS_INTEGER datatype to store signed integers. Its magnitude range is -2**31 .. 2**31. PLS_INTEGER values require less storage than NUMBER values.

Also, PLS_INTEGER operations use machine arithmetic, so they are faster than NUMBER and BINARY_INTEGER operations, which use library arithmetic. For efficiency, use PLS_INTEGER for all calculations that fall within its magnitude range.

Maybe if you are looking for efficiency, this would be a good choice.

[NayaDBA]

Hi Slimdave

Thanks. I'm exactly looking for such solution. Only 4 to 5 digits will be stored in the variables.

Best regards
Bhaskara

[rigatoni]

It's probably not going to matter which way you go. They (number and pls_integer) are most likely going to be converted to the same thing internally. Also consider if you need to use a number or an integer.

See this link for a discussion on that and some benchmarks cases:

http://asktom.oracle.com/pls/ask/f?p...5340131040835,

pr