function return data type?

Question

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. ?

.

Answer 1

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).