User-Defined Functions «Prev  Next»
Lesson 1

Introduction to User Defined Functions in Oracle using PL/SQL

In this module, you get to create user-defined functions in PL/SQL. These functions are stored inside the database and called within SQL queries or other SQL script.
By the end of this module you will be able to:
  1. List the uses of a function and identify correct syntax
  2. Create and execute a function that returns a number, text, or date value
  3. List uses of a function that returns a Boolean value
The next lesson describes what it means to create a function using PL/SQL.

Create Function

Use the CREATE FUNCTION statement to create a standalone stored function or a call specification.
A stored function (also called a user function or user-defined function) is a set of PL/SQL statements you can call by name.
Stored functions are very similar to procedures, except that a function returns a value to the environment in which it is called.
User functions can be used as part of a SQL expression.
A call specification declares a Java method or a third-generation language (3GL) routine so that it can be called from SQL and PL/SQL. The call specification tells Oracle Database which Java method, or which named function in which shared library, to invoke when a call is made. It also tells the database what type conversions to make for the arguments and return value.

Inputting and Outputting Data with PL/SQL

Most PL/SQL input and output is through SQL statements, to store data in database tables or query those tables. All other PL/SQL I/O is done through APIs that interact with other programs. For example, the DBMS_OUTPUT package has procedures such as PUT_LINE. To see the result outside of PL/SQL requires another program, such as SQL*Plus, to read and display the data passed to DBMS_OUTPUT. SQL*Plus does not display DBMS_OUTPUT data unless you first issue the SQL*Plus command SET SERVEROUTPUT ON as follows:
For information on the SEVEROUTPUT setting, see the "SQL*Plus Command Reference" chapter in SQL*Plus User's Guide and Reference. Other PL/SQL APIs for processing I/O are:
  1. HTF and HTP for displaying output on a web page
  2. DBMS_PIPE for passing information back and forth between PL/SQL and operating-system commands
  3. UTL_FILE for reading and writing operating-system files
  4. UTL_HTTP for communicating with web servers
  5. UTL_SMTP for communicating with mail servers
Although some of these APIs can accept input as well as output, there is no built-in language facility for accepting data directly from the keyboard. For that, you can use the PROMPT and ACCEPT commands in SQL*Plus.