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:
List the uses of a function and identify correct syntax
Create and execute a function that returns a number, text, or date value
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.
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:
SET SERVEROUTPUT ON
Other PL/SQL APIs for processing I/O are:
HTF and HTP for displaying output on a web page
DBMS_PIPE for passing information back and forth between PL/SQL and operating-system commands
UTL_FILE for reading and writing operating-system files
UTL_HTTP for communicating with web servers
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.