PL/SQL Parameters   «Prev  Next»

Convert Procedure to Function in Oracle

  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

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.

Oracle PL/SQL Programming

Formal Parameters

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.