PL/SQL objects, functions, and procedures in Oracle 10g

Learning objective

After completing this topic, you should be able to identify the characteristics of PL/SQL objects, functions, and procedures.

1. Introducing PL/SQL

PL/SQL is Oracle's proprietary fourth-generation programming language, which provides procedural extensions to SQL.

It provides a common programming environment for developing Oracle databases and applications across different operating systems and hardware platforms.

You can use PL/SQL and SQL statements to

• manipulate data
• use IF THEN, CASE, and LOOP procedural constructs to control program flow
• declare constants and variables
• define procedures and functions
• use collections and object types
• trap runtime errors

In addition, you can use PL/SQL programs to call other programs written in languages such as C, C++, and Java.

PL/SQL code performs data-intensive operations efficiently, because it runs inside the database. This minimizes the amount of network traffic in applications.

There are many types of PL/SQL database objects:

• function
• package
• package body
• type body
• procedure
• trigger

function

A function is a PL/SQL block, or logical piece of code, that returns a value by using the PL/SQL RETURN command.

package

A package is a collection of logically related procedures and functions. The package specification, or spec, declares the types, variables, constants, exceptions, cursors, and subprograms available for use.

package body

The package body implements the package specification by fully defining cursors and subprograms. It also holds implementation details and private declarations.

type body

The type body is a collection of procedures and functions, or methods, associated with user-defined data types.

procedure

A procedure is a PL/SQL block, or logical piece of code, that performs a specific action.

trigger

A trigger is a PL/SQL block, or logical piece of code, that is executed when a specified event occurs in the database. These events can be table events, such as a row being inserted into a table, or database events, for example a user logging into the database.

While DBAs do not usually need to write PL/SQL applications, you should familiarize yourself with the different PL/SQL objects so that you can identify problem objects and advise developers on the appropriate use of PL/SQL objects.

You can access PL/SQL objects in the Administration tabbed page of Enterprise Manager.

You click the Packages hyperlink in the Schema section.

The Packages page displays, allowing you to search for a specific object by type, schema, and object name.

You can view, modify, and create specific PL/SQL objects by selecting the object type from the Object Type drop-down list.

Question

Match the PL/SQL objects with their characteristics.

Options:

1. Trigger
2. Procedure
3. Function
4. Type body
5. Package

Targets:

1. Is used to return a value
2. A collection of procedures and functions
3. Performs a specific action
4. Is executed when a specified event occurs in the database
5. Holds methods associated with user-defined data types

Answer

The PL/SQL objects have been matched with their characteristics.

Trigger events can be table events, such as a row being inserted into a table, or database events, for example a table being dropped from the database.

Procedures are composed of PL/SQL blocks.

Functions must include the PL/SQL RETURN command.

Procedures and functions are types of methods.

Packages declare the types, variables, constants, exceptions, cursors, and subprograms that are available for use.

2. PL/SQL functions

You use PL/SQL functions to compute values. PL/SQL contains many built-in functions, such as AVG, SUM, SYSDATE, and TO_DATE.

Developers can also create their own functions when writing applications.

Functions can accept input values. They can also perform conditional statements such as IF THEN, CASE, and LOOP.

The code for a PL/SQL function must contain a RETURN statement.

Here is an example of a PL/SQL function called "computetax".

It uses an IF THEN construct to return the amount of tax due on salaries, where different tax rates are applicable to different salaries.

CREATE OR REPLACE FUNCTION computetax (salary NUMBER)
RETURN NUMBER
AS
BEGIN
IF salary<5000 THEN
RETURN salary*.15;
ELSE
RETURN salary*.33;
END IF;
END;

Question

Identify the characteristics of PL/SQL functions.

Options:

1. They are either built into PL/SQL or created by developers
2. They are typically used to compute values
3. They cannot perform conditional statements
4. They must include a RETURN statement

Answer

PL/SQL functions are either built into PL/SQL or created by developers. They are typically used to compute values, and must include a RETURN statement.

Option 1 is correct. Built-in PL/SQL functions include AVG, SUM, SYSDATE, and TO_DATE.

Option 2 is correct. Functions can compute values and return them using the PL/SQL RETURN statement.

Option 3 is incorrect. PL/SQL functions can perform conditional statements such as IF THEN, CASE, and LOOP.

Option 4 is correct. If a function doesn't include a RETURN statement, the computed value will not be returned.

3. PL/SQL procedures

PL/SQL procedures are used to perform a specific action. They can also transfer values through an argument list, and perform conditional statements.

You call PL/SQL procedures with the CALL command.

Here is an example of a PL/SQL procedure called "giveraise" that updates the salary column to give a raise of 5 percent.

AS
BEGIN
UPDATE hr.employees SET salary=salary*1.05;
END;

In this code you call the giveraise procedure, which adds 5 percent to the salary sum shown.

SELECT sum(salary) FROM hr.employees;
SUM(SALARY) 691400
call giveraise();
Call completed.
SELECT sum(salary) FROM hr.employees;
SUM(SALARY) 725970

Question

Identify characteristics of PL/SQL procedures.

Options:

1. They are called with the INVOKE command
2. They can accept input values
3. They can perform conditional statements

Answer

PL/SQL procedures can accept input values and perform conditional statements.

Option 1 is incorrect. PL/SQL procedures are called with the CALL command.

Option 2 is correct. PL/SQL procedures can also transfer values through an argument list.

Option 3 is correct. Examples of conditional statements are IF THEN, CASE, and LOOP.

Summary

PL/SQL is Oracle's proprietary fourth-generation programming language, which provides procedural extensions to SQL. You can use PL/SQL to manipulate data in several ways. PL/SQL is efficient, because it runs inside the database. There are six types of PL/SQL database objects.

You use PL/SQL functions to compute values. PL/SQL contains many built-in functions, and developers can also create their own functions. Functions can accept input values and perform conditional statements such as IF THEN, CASE, and LOOP. The code for a PL/SQL function must contain a RETURN statement.

PL/SQL procedures are used to perform a specific action. They can also transfer values through an argument list. You call PL/SQL procedures with the CALL command.