Stored procedures

Stored procedures combine the benefits of SQL to query and manipulate database information with the benefits of a procedural programming language to handle data processing, transaction logic, and application branching behaviors.

For example, if you have a database that contains customer information, inventory, and sales records, you might also have an application that processes the sale of an item in inventory. When an order request arrives, the application might be designed to query the database to determine how many items of that type are available in inventory, and then to take the following actions:
  • If the available inventory is less than the order number, the application processes the request for the available number and notifies an order administrator to order more inventory to complete the purchase.
  • If the available inventory is greater than the order request, the application processes the order and updates the database to show the reduction in the current inventory.
  • If the inventory is discontinued, the application saves the order request and returns a message that the item is no longer available. It can also query for related or replacement inventory to report alternative merchandise.

While such an application can be managed with a third-party business application that accesses the Netezza Performance Server database to do these tasks, you can also use Netezza Performance Server stored procedures to encapsulate this application as an object in the Netezza Performance Server database. SQL provides the power to access and update the database information about the host, and the procedure language provides the logic for if-then-else branching and application processing.

Because the application is on the Netezza Performance Server host, the application performance can benefit from its location “onsite” by avoiding the network time between an application client system and the Netezza Performance Server host. The application itself also becomes easier to maintain, as it is in only one location (the Netezza Performance Server host) and thus versioning and updates need only be made in one place to keep the application up-to-date for all users.

With Netezza Performance Server stored procedures, you can also take advantage of security and access benefits. Stored procedures can be defined to run with the credentials of the user who created the procedure or the credentials of the user who is running the procedure. The procedure manages the access to information contained in various base tables and views. You can grant a user permission to run a stored procedure without granting that user explicit access to underlying tables or views.