Lesson 1

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.

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.

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:

SET SERVEROUTPUT ON

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.

The PL/SQL compilation and run-time system is an engine that compiles and executes PL/SQL blocks and subprograms. The engine can be installed in an Oracle server or in an application development tool such as Oracle Forms. In either environment, the PL/SQL engine accepts as input any valid PL/SQL block or subprogram. Figure 2-1 shows the PL/SQL engine processing an anonymous block. The PL/SQL engine executes procedural statements but sends SQL statements to the SQL engine in the Oracle database.