Using a function to format a single column value, and calling that function in a SQL statement

Question

I want to write a function which can be used to format a faculty member's salary (FACULTY being a table, where each row is a member) to $9,999,999.99 without hard coding the exact salary datatype (i.e the function should work even if in the future minor changes are made to the salary data size/type). I want to be able to use this function in a SQL statement for displaying a faculty member's salary.

Here is what my function code looks like right now.

CREATE OR REPLACE FUNCTION FUNC_FORMAT_SAL(fid IN FACULTY.F_ID%TYPE)
RETURN NUMBER IS

sal NUMBER;
BEGIN
SELECT FACULTY.F_SALARY INTO sal FROM FACULTY
WHERE FACULTY.F_ID = fid;


RETURN  sal;

END;

I understand that in PLSQL a function cannot return a void right? That is why I have taken the member's F_ID as an IN input into the function. Inside this function, I want to format the SALARY for that member so that it could be displayed when I call an SQL statement, and then return the formatted salary.

Am I making any sense? This is a tutorial question in class and I just can seem to understand even though easy it appears to be.

A function cannot return no type (which is what I guess you've meant with returning void). But it can return the value null. If you want an "executable object", which doesn't necessarily return something, but typically changes something in the database, you might be looking for a PROCEDURE. — sticky bit
– sticky bit, Commented May 8, 2018 at 23:07
so have function return a varchar2 and call to_char() to format. — OldProgrammer
– OldProgrammer, Commented May 9, 2018 at 0:53

Kaushik Nayak · Accepted Answer · 2018-05-09 04:01:13Z

1

I would say your function should return a VARCHAR2 if you are going to use it for display purpose. Taking a step further, it would be better to parameterise the format model as well. It should include exception handling such that when there is no data for the id it should return NULL and raise an exception for other errors( such as invalid format model).

CREATE OR REPLACE FUNCTION func_format_sal(p_fid IN faculty.f_id%TYPE, 
p_format IN VARCHAR2 DEFAULT '$9,999,999.99') 
RETURN VARCHAR2 
IS 
  v_sal VARCHAR2(40); 
BEGIN 
    SELECT TO_CHAR(f.f_salary, p_format) 
    INTO   v_sal 
    FROM   faculty f 
    WHERE  f.f_id = p_fid; 

    RETURN v_sal; 
EXCEPTION 
  WHEN no_data_found THEN 
             RETURN NULL; 
  WHEN OTHERS THEN 
             RAISE; 
END; 

/

You may call this function in 2 ways, assuming 1,2 are valid f_ids

select func_format_sal(1)  FROM dual;
select func_format_sal(2,'$99999')  FROM dual;

Here's a dbfiddle demo

answered May 9, 2018 at 4:01

Kaushik Nayak

32k6 gold badges36 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Brandon Tupiti Over a year ago

Thanks so much. :)

Collectives™ on Stack Overflow

Using a function to format a single column value, and calling that function in a SQL statement

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related