, , ,

Subprograms

Subprograms are named PL/SQL blocks that can take parameters and be invoked. PL/SQL has two types of subprograms called procedures and functions. Generally, you use a procedure to perform an action and a function to compute a value.


 

Uses of Procedures/Functions
Procedures are excellent for defining a PL/SQL code block that you know you will need to call more than once, and whose work may produce results largely seen in the database or perhaps some module, like an Oracle Form, or a client-side form, as opposed to work whose result is some single answer; that would probably be more appropriate for a function.

In addition, an anonymous PL/SQL block is parsed each time it is submitted for execution. But if that same anonymous block is assigned a name and created as a procedure, then Oracle will parse the procedure once, at the time it is created. Each subsequent call to that procedure will not require reparsing; it will simply execute, saving time over an anonymous block.
A PL/SQL procedure can be invoked from a single executable statement in another PL/SQL statement. These other PL/SQL statements could be in an anonymous PL/SQL block or in a named program unit, such as another procedure. A PL/SQL procedure can also be invoked from a single command-line executable statement in a SQL*Plus session.

A function‘s main purpose is to return a single value of some sort, as opposed to a procedure, whose main purpose is to perform some particular business process. Like a procedure, a function is a PL/SQL block that’s been assigned a name; but unlike a procedure, the function will always return one—and only one—value of some sort. This returned value is embodied in the function call in such a way that the function becomes, in essence, a variable.
When you create a function, you must consider how you intend to use the function. There are two major categories of functions you can create:

  • Functions that are called from expressions in other PL/SQL program units. Any function can be used this way.
  • Functions that are called from within SQL statements, whether the SQL statement is part of a PL/SQL program unit or not. Some functions you create in PL/SQL can be used in this way.

It’s possible to create functions that can be invoked in both manners. However, if you intend to make a function that can be called from a valid SQL statement, there are some restrictions you have to consider. For example, a function that returns a BOOLEAN datatype, which is perfectly acceptable in PL/SQL, cannot be invoked from a SQL statement, where BOOLEAN datatypes are not recognized.

Functions versus Procedures
Functions can be used in places where procedures cannot be used. Whereas a procedure call is a statement unto itself, a call to a function is not; a function call is part of an expression. This means that functions can be used as a part, or all, of the right side of the assignment statement. Functions can be used as part, or perhaps all, of the Boolean expression in an IF statement. In short, wherever you might use a variable, you can use a function. Functions always return a single value, embodied in the function call itself. In other words, contrary to the optional OUT parameter feature in procedures, which you may or may not use to return multiple values from a procedure, a function must always return one—and only one—value through the very call to the function itself. This value is not returned in the form of an OUT parameter, but instead it is returned in the body of the function itself, so that the function call behaves like a variable. Technically, functions can use IN, OUT, and IN OUT parameters. In practice, functions are generally only given IN parameters.
Where Can You Store Procedures?
Procedures can be stored in the database, alongside tables and other database objects. Once a procedure is stored in the database, it can be invoked from any process with database access. If a process, such as a SQL*Plus window, Java program, Oracle Form, or another PL/SQL procedure, has access to the database, it can execute the procedure, provided that the proper privileges have been granted on the procedure (more on this later) to the schema under which the process is running.

/by
0 replies

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply