PL/SQL Parameters   «Prev  Next»

Lesson 6Parameters and functions
Objective Use parameters and the RETURN command in a function appropriately.

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.
Let's begin with the procedure that was demonstrated in a previous module. This procedure has one incoming and one outgoing parameter we will convert it to a function that has one incoming parameter and no outgoing parameter. Instead we will use the RETURN command to send the results back from the function
1) Let's begin with the procedure that was demonstrated in a previous module. This procedure has one incoming and one outgoing parameter we will convert it to a function that has one incoming parameter and no outgoing parameter. Instead we will use the RETURN command to send the results back from the function
I have highlighted the portion of the first line that needs to be changed. The word PROCEDURE changes to FUNCTION and the name of the procedure is revised to a new name: ADDRESS_FORMAT .
2) I have highlighted the portion of the first line that needs to be changed. The word PROCEDURE changes to FUNCTION and the name of the procedure is revised to a new name: ADDRESS_FORMAT .
Now that the name has changed the outgoing parameter must be removed and replaced with a RETURN statement as required when defining a function. I have highlighted the next set of text that will be replaced.
3) Now that the name has changed the outgoing parameter must be removed and replaced with a RETURN statement as required when defining a function. I have highlighted the next set of text that will be replaced.
As you can see I have removed the second parameter and added RETURN VARCHAR 2 in the definition of the function. The next step is to define a local variable to take the place of the outgoing parameter. This means that I do not have to modify much of the code in the body of the function.
4) As you can see I have removed the second parameter and added RETURN VARCHAR 2 in the definition of the function. The next step is to define a local variable to take the place of the outgoing parameter. This means that I do not have to modify much of the code in the body of the function.
The ADDRESS_LINE2 variable has been defined now. The only thing left to do is to add their RETURN command and at the end of the body of the function I have opened new line just before the END command for the RETURN command.
5) The ADDRESS_LINE2 variable has been defined now. The only thing left to do is to add their RETURN command and at the end of the body of the function I have opened new line just before the END command for the RETURN command.
The file is now complete the only thing left to do is exit save and execute the file. I click the X in the top right corner to exit.
6) The file is now complete the only thing left to do is exit save and execute the file. I click the X in the top right corner to exit.
Then click the <strong>Yes</strong> button to save the changes
7) Then click the Yes button to save the changes
I am now back in SQL*plus. I type a slash to execute the CREATE FUNCTION command
8) I am now back in SQL*plus. I type a slash to execute the CREATE FUNCTION command
Now that the function has been created I type a query to test it, as you see in this window
9) Now that the function has been created I type a query to test it, as you see in this window
This shows the results of the query and the function
10) This shows the results of the query and the function


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.

Convert Procedure to Function

Click the link below to read about converting a procedure to a function.
Convert Procedure to Function