Oracle PL/SQL – Function Return Data Type

oracleplsql

create or replace function Total_sal
 return varchar2
  is  
 Total number;
begin
select sum(salary)into total from employees;
return total;
 end;  
  /

 BEGIN
 DBMS_OUTPUT.PUT_LINE(TOTAL_SAL);
 END;
 /

Please correct if my understanding of this
?first we mention a return varchar2 data type
and then we pass salary into total (which is number datatype) as salary is number datatype and again return total which is varchar2
which is after the select statement ?
do we need to bother about the datatype of the return total statement. ?

.

Best Answer

Yes, you need to bother about datatype, always know your datatypes and use the proper ones. The database can implicitly convert compatible datatypes, so it is not always mandatory to specify and convert to the correct datatype, but it is a really bad practice to rely on implicit conversion.

The below is a typical scenario I saw countless times:

select column1 from table1 where indexed_number_column = '12345';

Here you compare a number to a string. The database will convert this query to:

select column1 from table1 where indexed_number_column = to_number('12345');

or

select column1 from table1 where to_char(indexed_number_column) = '12345';

The second one can not use the existing index on the column indexed_number_column, so it will perform much slower. For the second one, you would need a function-based index on to_char(indexed_number_column).

Related Question