What Is PL/SQL Good For?

PL/SQL is the language to use when writing code that resides in the database. In the following article, you'll see different situations in which you'll find PL/SQL useful.

Using database triggers

A trigger is an event within the DBMS that can cause some code to execute automatically. There are four types of database triggers:

• Table-level triggers can initiate activity before or after an INSERT, UPDATE, or DELETE event. These are most commonly used to track history information and database changes, to keep redundant data synchronized, or to enhance security by preventing certain operations from occurring.

• View-level triggers are very useful. A view is a stored SQL statement that developers can query as if it were a database table itself. By placing INSTEAD OF triggers on a view, the INSERT, MODIFY, and DELETE commands can be applied to the view regardless of its complexity, because the INSTEAD OF trigger defines what can be done to the view.

• Database-level triggers can be activated at startup and shutdown. For example, when the database starts up you might want to test the availability of other databases or Web services. Before a database shutdown, you might want to notify other databases and Web services that the database is going offline.

• Session-level triggers can be used to store specific information. For example, when a user logs on or off, you might want to execute code that contains the user's preferences and loads them into memory for rapid access. When the session closes, a trigger can save the preferences for future use.

Scripting with speed

When writing code, the ability to type a portion of code and execute it without first saving it to the database is useful. Oracle provides this capability, which is supported by all PL/SQL IDEs.

Keeping code server-side

The majority of PL/SQL code is stored as program units in the server. A typical application has many lines of code.

Some programmers, particularly Web-based developers working in the J2EE or .NET environments, try to write most of their code in the application server in Java (for J2EE developers) or VB.NET (for .NET developers). This isn't good practice. In a database application, much of the logic is devoted to retrieving and updating information. If the code to accomplish this task resides in an application server, it must send a request to the database over a network. Then the database must process the request and send the information back across the network for the application to process. Because networks and computers are now very fast, you might think that this would take only fractions of a second. Although this is the case for a single request, if a very complex application requires millions or even hundreds of millions of interactions with the database, multiplying the number of interactions by even fractions of a second can lead to very poor performance.

Even relatively simple operations requiring only a few database requests can be problematic if the application is being accessed by hundreds, thousands, or tens of thousands of users simultaneously. It is much more difficult to build a database-intensive application without using server-side coding than it is to write all the code to run in an application server.

One of the arguments against writing server-side code is that the application won't be portable (can't be moved from one platform to another). However, most organizations using Oracle have been using it for a very long time (ten or more years) and aren't contemplating a switch to a different platform. Also, Web development is currently in a state of rapid flux. Organizations frequently change between .NET, J2EE, and other environments for their Web-based application development.

Both the .NET and J2EE environments are in flux, as well. In the J2EE environment, the industry standard for Web development a year or so ago was to create JavaServer pages (JSPs). Currently, the industry standard is to work in the JSP/Struts environment. In the next year or so, JavaServer Faces (JSFs) will likely become the industry standard. Therefore, code written in the middle-tier runs a high risk of needing to be rewritten in the future.

Server-side code runs faster, is easier to maintain and test, and is less susceptible to change than code placed in the middle tier. Therefore, creating significant portions of an application in the database is a better approach.

There are a number of places where you can write code that your applications can use:

• Portions of applications: PL/SQL program units can return a set of values (functions), or PL/SQL routines can perform database operations (procedures). These functions and procedures may be called by other functions and procedures or (in the case of functions) used in SQL statements. PL/SQL routines may be as large and complex as you need them to be. Some complex routines may contain thousands of lines of code. Entire systems may contain millions of lines of code.

• PL/SQL code embedded in views: Oracle allows you to embed code in database views. The code might actually be located in one of two places in the view. First, you can place correctly crafted functions returning a value in the SELECT portion of a SQL statement to retrieve additional information, which might or might not be part of the tables being queried.

You can also embed PL/SQL in INSTEAD OF triggers on a view. These triggers allow you to perform INSERT, UPDATE, and DELETE operations on complex views, with PL/SQL programmatically handling how these operations should be handled.

• Batch routines: Batch routines run code that processes a large number of records at the same time. Generating invoices for every customer in a system or processing payroll checks for an entire organization are examples of batch routines. These routines are usually large, complex, and database intensive. This type of routine should assuredly be written in PL/SQL.

dummies

Source:http://www.dummies.com/how-to/content/what-is-plsql-good-for.html