Chapter 6. Developing Stored Subprograms and Triggers

The way you give your name to others is a measure of how much you like and respect yourself. -Brian Tracy

This chapter brings you the power of PL/SQL within your database. Stored subprograms offer distinct advantages over embedding queries in your applications as stored subprograms are more modular and tuneable. We will learn about stored procedures, functions, and packages in this chapter. Error handling is a very critical part of a program and hence is of greater importance. The following topics are discussed in this chapter:

• PL/SQL data types

• Stored subprograms

• Creating stored subprograms

• About packages

• Creating packages

• Wrapping the PL/SQL code

• Exception handling

• PL/SQL triggers

PL/SQL data types

PL/SQL provides many predefined data types. The most commonly used PL/SQL data types include NUMBER, INTEGER, VARCHAR2, DATE, and BOOLEAN. You assign values to variables using ":=". An example PL/SQL block, that declares variables and assigns some...

PL/SQL data types

PL/SQL provides many predefined data types. The most commonly used PL/SQL data types include NUMBER, INTEGER, VARCHAR2, DATE, and BOOLEAN. You assign values to variables using ":=". An example PL/SQL block, that declares variables and assigns some values, is as follows:

DECLARE
l_hire_date DATE;
l_ssn NUMBER;
l_marital_flag BOOLEAN; -- True -> Married, False -> Unmarried
l_name VARCHAR2(30);
BEGIN
l_hire_date := TO_DATE('02-03-2001', 'dd-mm-yyyy');
l_ssn := 12345678;
l_marital_flag := TRUE;
l_name := 'Tom';
END;
/

Stored subprograms

Subprograms are named PL/SQL blocks that can take parameters and be invoked. Subprograms are either functions or procedures and can be compiled and stored in an Oracle database, ready to be executed. Once compiled it is a schema object known as a stored procedure or stored function. Generally, you use a function to compute a value and a procedure to perform a business action.

Creating stored subprograms

You create a stored subprogram using the CREATE PROCEDURE command. In this section, let us create a sample stored procedure in the HR schema and name it salary_increment. The stored procedure does the following:

1. 1. Accepts EMP_NO as input.

2. 2. Fetches the current salary of the employee.

3. 3. Calculates the increment on the salary.

4. 4. If the salary is greater than 1000 then raise it by 2 percent. If the salary is between 501 and 999, increment it by 5 percent, and if the salary is less than or equal to 500, increment it by 10 percent.

5. 5. Increment the salary of the given employee in the EMP table.

6. 6. Commits the changes.

The following is a screenshot of a PL/SQL stored procedure:

At this point you should be able to successfully create the salary_increment procedure. Before we execute this procedure, let us make a note of the salary of "Tom Green" by querying the EMP table. The following screenshot shows the query:

From the query shown in the preceding screenshot, we know...

About packages

A package is a group of logically related procedures, functions, variables, and SQL statements created as a single unit. A package is a schema object just like a table and a sequence. A package has two parts, package specification and package body.

A package specification does not contain any code. You typically declare variables, constants, cursors, procedures, and functions in a package. A package body is used to provide the implementation details. All the program logic is coded in the body. Within the package specification, we can specify which subprograms are visible to the public and which are not (private). By hiding implementation details from users, you can protect the integrity of the package.

Stored subprograms defined within a package are known as packaged subprograms.

Creating packages

You create a package specification using the CREATE PACKAGE or CREATE OR REPLACE PACKAGE command. As mentioned earlier, a package is a group of related procedures and functions, so...

Exception handling

An exception is an error which arises during the program execution. When an exception is raised, the normal program execution stops and the control transfers to the exception handling section if it exists; otherwise the program stops abruptly. There are two types of exceptions found in Oracle: predefined exceptions and user defined exception.

The predefined exceptions are raised automatically whenever there is a violation of Oracle coding rules, such as NO_DATA_FOUND being raised if a SELECT INTO statement returns no rows. For a complete list of predefined exceptions refer to Oracle Database PL/SQL Language Reference 11g Release 2.

Apart from the predefined exceptions, we can explicitly define exceptions based on business rules. These are known as user-defined exceptions. For example, an employee should be at least 18 years old; if he/she is less than 18 then the application should raise an error.

The stored procedure and function created in the previous sections do not...

Wrapping up the PL/SQL stored programs

You do not always want your code to be displayed in clear text to the outside world. The code may contain proprietary information which needs to be guarded from external sources. Or you may simply want your code to be hidden from the end users to avoid any unofficial code modifications. Oracle provides a PL/SQL wrapper utility for hiding your code. The following steps are required to wrap your code:

1. 1. Save your code (procedure, function, or package) in a text file.

2. 2. Wrap the code using the PL/SQL wrapper utility. A wrapped PL/SQL code file is created by the utility.

Save the salary_increment procedure in a text file (say, c:\salary_increment) and invoke the PL/SQL wrapper utility in the operating system as shown in the following screenshot:

You can run the output file (c:\temp\salary_increment_wrapped.sql) as a script in SQL*Plus. For example:

-- Execute the wrapped SQL code
SQL> @c:\temp\salary_increment_wrapped.sql

PL/SQL triggers

Triggers are SQL and PL/SQL blocks which are implicitly executed by Oracle when a INSERT, UPDATE, or DELETE statement is issued against the associated table. You cannot explicitly invoke a trigger; however, you can enable and disable a trigger.

A trigger has three basic parts:

• Triggering event or statement: This can be a INSERT, UPDATE, or DELETE statement on a table

• Timing point: Determines whether the trigger fires before or after the triggering statement and whether it fires for each row that the triggering statement affects

• Trigger action: This is the procedure that contains the SQL & PL/SQL statements and code to be run

There are two ways of firing a trigger. Firstly, fire the trigger once for the triggering statement irrespective of how many rows it affects and secondly, once for every row affected. A row trigger is fired for each row while a statement trigger is fired once on behalf of the triggering statement. For example, if the UPDATE statement modifies five...

Summary

In this chapter, we have learned about stored procedures, functions, and packages. We know how to create, modify, and drop the stored procedures. Also, in this chapter you were exposed to error handling techniques. Wrapping your code is another great feature of the Oracle database to protect your hard work from getting copied by unauthorized users. We have also seen database triggers in action.

The next chapter will introduce you to Oracle's free development tool Oracle Application Express (APEX). In this chapter, we will understand APEX components. We will use APEX to browse and manage schema objects. We will also develop a small application using APEX.

References

• Oracle Database PL/SQL Language Reference 11g Release 2