Lesson 7
PL/SQL Blocks | User Defined Functions
This module covered the creation of a function using PL/SQL blocks and SQL commands.
You learned the basic syntax of a function and reviewed a detailed account of creating an example.
You created your own function for the course project and then used it within an UPDATE command.
In addition, you practiced your documentation skills. Now that you completed this module, you should 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 return a Boolean value
Glossary
In this module you were introduced to the following glossary terms:
- Function
- Stored function
The next module looks at another type of stored PL/SQL object: the procedure.
Create Function
Better Performance
Higher Productivity
PL/SQL lets you write very compact code for manipulating data. In the same way that scripting languages such as Perl can read, transform, and write data from files, PL/SQL can query, transform, and update data in a database. PL/SQL saves time on design and debugging by offering a full range of software-engineering features, such as
- exception handling,
- encapsulation,
- data hiding, and
- object-oriented datatypes.
PL/SQL extends tools such as Oracle Forms. With PL/SQL in these tools, you can use familiar language constructs to build applications.
For example, you can use an entire PL/SQL block in an Oracle Forms trigger, instead of multiple trigger steps, macros, or user exits. PL/SQL is the same in all environments. After you learn PL/SQL with one Oracle tool, you can transfer your knowledge to other tools.
Full Portability
Applications written in PL/SQL can run on any operating system and platform where the Oracle database runs. With PL/SQL, you can write portable program libraries and reuse them in different environments.
Tight Security
PL/SQL stored procedures move application code from the client to the server, where you can protect it from tampering, hide the internal details, and restrict who has access. For example, you can grant users access to a procedure that updates a table, but not grant them access to the table itself or to the text of the UPDATE statement. Triggers written in PL/SQL can control or record changes to data, making sure that all changes obey your business rules.