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.
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.