Creating Functions: An Example

Example

In this example, the get_sal function is created with a single input parameter and returns the salary as a number.

The get_sal function uses the common programming practice of assigning a returning value to a local variable and uses a single RETURN statement in the executable section of the code to return the value stored in the local variable. If your function has an exception section, then it may also contain a RETURN statement.

You should invoke a function as part of a PL/SQL expression, because the function will return a value to the calling environment, such as Application Express. The second code box uses the EXECUTE command to call the DBMS_OUTPUT.PUT_LINE procedure, whose argument is the return value from the function get_sal. In this case, get_sal is invoked first to calculate the salary of the employee with ID 100. The salary value returned is supplied as the value of the DBMS_OUTPUT.PUT_LINE parameter, which displays the result.