Parameters Return Command Function

A function by definition returns a value to the calling command by using the RETURN command. Because of this, it is considered good programming practice to avoid defining OUT or IN OUT parameters with a function. Use IN mode parameters within a function exclusively, in order to preserve the function's task of returning a single value via the RETURN command (instead of an OUT or IN OUT parameter).
Look at the following Slideshow to see how to change a procedure with an IN and an OUT parameter to a function with and IN parameter and a RETURN command. You begin with the procedure that was started in the Slideshow in the previous lesson.

The main reason for using a function instead of a procedure is so that you can use it within a query or other SQL command.
Remember that there are restrictions on what a function can do, so not every procedure can be converted to a function. The next lesson covers how to use parameters when working with cursors. The following section discusses converting a procedure to a function.

1. The first step was to begin an edit session. You typed EDIT at the SQL> prompt.
2. You typed over PARAMETER DO_MONTHLY_SALES to replace it with FUNCTION GET_SALES. You pressed Enter.
3. You typed over O_TOTAL_SALES OUT NUMBER) AS to replace it with the RETURN command: RETURN NUMBER AS. You pressed Enter.
4. You typed O_TOTAL_SALES NUMBER(10,2); and pressed Enter to define a local variable that replaced the outgoing parameter.
5. You typed RETURN O_TOTAL_SALES; to add the RETURN command at the end of the function body. You then clicked the X at upper right to close out of the window.
6. You saved your changes by clicking Yes.
7. You executed the command by typing a forward slash and pressing Enter.
8. You tested out your function by typing SELECT GET_SALES('MARCH') FROM DUAL; and pressed Enter.
9. Once you tested your function, you ended the simulation.

Functions and procedures are named PL/SQL blocks. You can also call them subroutines or subprograms. They have headers in place of the DECLARE statement and the header defines

1. the function or procedure name,
2. a list of formal parameters,
3. and a return datatype for functions.

Formal parameters define variables that you can send to subroutines when you call them. You use both formal parameters and local variables inside functions and procedures. While functions return a datatype, procedures do not. At least, procedures do not formally list a return datatype, because they return a void. The void is explicitly defined in other programming languages, like C, C#, Java, and C++.
Procedures can return values through their formal parameter list variables when they are passed by reference. There are four types of generic subroutines in programming languages. The four types are defined by two behaviors, whether they return a formal value or not and whether their parameter lists are passed by value or reference.

You set formal parameters when you define subroutines. You call subroutines with actual parameters. Formal parameters define the list of possible variables, and their positions and datatypes. Formal parameters do not assign values other than a default value, which makes a parameter optional. Actual parameters are the values you provide to subroutines when calling them. You can call subroutines without an actual parameter when the formal parameter has a default value. Subroutines may be called without actual parameters if all their formal parameters are defined as optional. Subroutines are black boxes. They are called that because black boxes hide their implementation details and only publish what you can send into them or receive from them.
Subroutines are functions when they return output and procedures when they do not return output. Functions return output as values represented as SQL or PL/SQL datatypes. Pass-by-value functions are sometimes called expressions because you submit values that are returned as a result. When the return datatype is a SQL type, you can call the function inside a SQL statement.