User-Defined Functions «Prev  Next»

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:
  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 return a Boolean value

Glossary

In this module you were introduced to the following glossary terms:
  1. Function
  2. Stored function
The next module looks at another type of stored PL/SQL object: the procedure.
Create Function

Better Performance

Without PL/SQL, Oracle must process SQL statements one at a time. Programs that issue many SQL statements require multiple calls to the database, resulting in significant network and performance overhead. With PL/SQL, an entire block of statements can be sent to Oracle at one time. This can drastically reduce network traffic between the database and an application.
  1. You can use PL/SQL blocks and subprograms to group SQL statements before sending them to the database for execution.
  2. PL/SQL also has language features to further speed up SQL statements that are issued inside a loop.
  3. PL/SQL stored procedures are compiled once and stored in executable form, so procedure calls are efficient.
  4. Because stored procedures execute in the database server, a single call over the network can start a large job.
  5. This division of work reduces network traffic and improves response times.
  6. Stored procedures are cached and shared among users, which lowers memory requirements and invocation overhead.

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
  1. exception handling,
  2. encapsulation,
  3. data hiding, and
  4. 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.