Netezza存储过程开发者指南

需积分: 9 56 浏览量更新于2024-07-22 收藏 829KB PDF 举报

"Netezza_stored_procedures_guide - Netezza procedures 开发指南 - DocumentNumber: 20470-02Rev.1 - SoftwareRelease: 6.0.x" Netezza Stored Procedures Developer’s Guide是针对Netezza数据库系统的一份详细开发文档，旨在帮助开发者理解和创建在Netezza环境中使用的存储过程。这份指南适用于软件版本6.0.x，由Netezza Corporation发布，日期为2010年10月7日。虽然文档内容可能会随着产品的更新而变化，但其提供的信息和建议被认为在发布时是准确的。存储过程是数据库系统中的一个重要组成部分，它们是一组预编译的SQL语句和控制结构，可以作为一个单元执行。在Netezza中，存储过程允许用户封装复杂的业务逻辑，提高数据处理的效率，并减少网络流量。通过使用存储过程，开发者可以创建自定义函数，用于处理特定的数据库操作，比如数据转换、复杂查询或事务管理。本指南可能涵盖以下几个关键知识点： 1. **语法和结构**：介绍Netezza存储过程的创建语法，包括声明参数、返回值、控制流程（如IF-ELSE，WHILE循环）等。 2. **调用和执行**：解释如何在SQL查询中调用存储过程，以及如何在应用程序中执行它们，包括使用各种编程接口（如JDBC, ODBC）。 3. **参数传递**：描述如何在存储过程中传递参数，包括输入参数、输出参数和双向参数。 4. **错误处理和调试**：提供关于如何在存储过程中捕获和处理错误，以及如何进行调试的指导。 5. **性能优化**：讨论如何编写高效的存储过程，可能涉及并行处理、使用物化视图和索引等技术。 6. **安全性**：介绍如何管理和控制对存储过程的访问权限，以确保数据安全。 7. **示例和最佳实践**：包含多个示例代码，演示存储过程的实际应用，以及推荐的编码和设计规范。 8. **存储过程的版本控制和管理**：说明如何版本化存储过程，以便于跟踪和回滚更改。请注意，尽管Netezza Corporation在其文档中尽力保证信息的准确性，但他们不承担任何直接或间接的损失，包括因使用该手册或产品产生的商业利润损失、业务中断、数据丢失或损坏等后果。这份开发者指南对于那些需要在Netezza数据库上构建高效、可靠和可维护的应用程序的开发者来说，是一个不可或缺的资源。它不仅提供了技术细节，还包含了实践经验，有助于提升开发者的技能水平和工作效率。

1-1

C H A P T E R 1

Introduction to Stored Procedures

What’s in this chapter

X Stored Procedures

X How to Create and Use a Stored Procedure

X How to Execute a Stored Procedure

X Best Practices for Developers

X Best Practices for Netezza Administrators

This chapter provides an overview of the support for stored procedures in the Netezza

family of data warehouse appliances. Stored procedure support is available in Netezza

Release 4.6 and later. This guide describes changes available in Release 6.0.x and later.

Stored Procedures

Netezza stored procedures combine the benefits of SQL to query and manipulate database

information with the benefits of a procedural programming language to handle data pro-

cessing, 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 actions such as the following:

X 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.

X 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.

X If the inventory is discontinued, the application saves the order request and returns a

message that the item is no longer available. It may also query for related or replace-

ment inventory to report alternative merchandise.

While such an application could be managed with a third-party business application that

accesses the Netezza database to perform these tasks, you can also use Netezza stored pro-

cedures to encapsulate this application as an object in the Netezza database. SQL provides

the power to access and update the database information on the host, and the procedure

language provides the logic for if-then-else branching and application processing.

1-2 20470-02 Rev.1

Netezza Stored Procedures Developer’s Guide

Because the application resides on the Netezza host, the application performance can ben-

efit from its location “onsite” by avoiding the network time between an application client

system and the Netezza host. The application itself also becomes easier to maintain, as it

resides in only one location (the Netezza 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 stored procedures, you can also take advantage of security and access bene-

fits. 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.

NZPLSQL Language

You implement stored procedures on the Netezza host by creating applications using the

NZPLSQL language. NZPLSQL is an interpreted language which is based on Postgres’

PL/pgSQL language and designed for the Netezza host environment.

NZPLSQL is a scripting language embedded in SQL. As a procedural language, it has

branch, loop, and subprogram structures while SQL provides the main program. The sub-

programs, known as procedures, can take arguments and declare internal variables. Once

stored in a database, these procedures can be called from within other databases on the

same Netezza host.

The NZPLSQL language provides for the following types of procedural logic:

X Conditionals (if/else)

X Looping (while, for)

X Execution of SQL (including dynamic SQL)

X Variables

X Returning a scalar result or a result set

X Input arguments

X Execution in the calling context (session and transaction)

X Extending functionality (adding NZPLSQL library routines)

SQL Commands for Stored Procedures

To support stored procedures in the Netezza database, the Netezza SQL language has been

extended to include new commands for stored procedures:

X ALTER PROCEDURE

X CALL or EXEC[UTE[ PROCEDURE]]

X CREATE [OR REPLACE] PROCEDURE

X DROP PROCEDURE

X SHOW PROCEDURE

You can use any SQL tool that supports ODBC, JDBC, OLE-DB to enter these commands.

This document contains examples that use the nzsql command line tool.

20470-02 Rev.1 1-3

How to Create and Use a Stored Procedure

Appendix A, “SQL Reference,” describes these commands and their syntax in detail. In

addition to these procedure-specific commands, you can also use the commands GRANT

and REVOKE to permit or deny access to procedures, as well as COMMENT ON to add

descriptions and details to the procedure definitions. For more information about managing

procedures, refer to Chapter 3, “Creating and Managing Stored Procedures.”

Stored Procedures Input and Return Types

Netezza stored procedures are a hybrid between functions and procedures (as defined by

the SQL:2003 standard for SQL-invoked routines). In the Netezza implementation, an

NZPLSQL stored procedure takes only input arguments and supports a return value as

functions do. Unlike functions, a stored procedure must be called using CALL or a similar

construct, and it is not allowed in the usual query locations where a built-in or standard

function is allowed.

Note: A stored procedure can also be designed to return a result set. For more information,

see “Returning a Result Set” on page 2-23.

How to Create and Use a Stored Procedure

As a high-level summary, follow these steps to create and use a stored procedure. These

steps are described in more detail in Chapter 3, “Creating and Managing Stored

Procedures.”

1. Design the stored procedure logic and operation.

2. Define the stored procedure object using the CREATE [OR REPLACE] PROCEDURE

command.

3. Run or execute the procedure from the SQL command prompt.

As with other Netezza objects, the Netezza admin user can create, list, and execute stored

procedures by default. The admin user may also grant other users permission to create, list,

and execute stored procedures on the Netezza system. For more information about permis-

sions, see “Managing User Account Permissions” on page 3-1.

How to Execute a Stored Procedure

You execute a stored procedure in the SQL command using either the CALL or EXEC[UTE[

PROCEDURE]] commands. For example, if you have a stored procedure named update-

acct(), you can run it using any of the following commands:

MYDB(USER)=> CALL updateacct();

MYDB(USER)=> EXEC updateacct();

MYDB(USER)=> EXECUTE updateacct();

MYDB(USER)=> EXECUTE PROCEDURE updateacct();

You can also use the SELECT command to execute a procedure; however, you cannot spec-

ify a FROM clause. For example:

MYDB(USER)=> SELECT updateacct();

To execute the procedure, the user must be the owner of or have permission to execute the

updateacct() procedure. For more information about the CALL and EXEC[UTE[ PROCE-

DURE]] commands, refer to Appendix A, “SQL Reference.”

1-4 20470-02 Rev.1

Netezza Stored Procedures Developer’s Guide

Best Practices for Developers

The following sections describe some best practices for users who develop stored

procedures.

Security Considerations

When you define a stored procedure, you can specify whether the Netezza system should

execute the procedure using the ID of the owner who created the stored procedure or the ID

of the user who runs the procedure. This allows you an additional layer of security (or

expanded access) for the data processed by the stored procedure.

For example, if the admin user created the stored procedure and specified “execute as

owner” permissions, which is the default, then any other user who is permitted to execute

the procedure will run the procedure “as” the admin user. This could, for example, allow

the user to see results from tables or views that he or she does not have permission to

access using SQL directly.

If the procedure should access only the data that the user is permitted to see, the stored

procedure should be defined as “execute as caller.” In this case, the procedure uses the

user ID of the calling user, and thus the access permissions of that user.

Cross-Database Access to Stored Procedures

When you create a stored procedure using the CREATE [OR REPLACE] PROCEDURE com-

mand, the command adds the procedure to the database to which you are connected. You

can define the stored procedure in more than one database if necessary.

Typically, most users are logged in to the database that contains the stored procedure that

they plan to run. However, the Netezza system allows for cross-database access of stored

procedures using two methods:

X Using fully-qualified object names when calling a procedure object that resides within

a different database, for example:

MYDB(ADMIN)=> EXEC OTHERDB..UPDATEACCT();

Using the PATH SQL session variable to specify the databases to search to find a proce-

dure. To use the PATH session variable, you enter a command similar to the following:

MYDB(ADMIN)=> SET PATH = <elem> [, <elem>];

The <elem> value can be a database name or the variables CURRENT_CATALOG,

CURRENT_USER, CURRENT_SCHEMA or CURRENT_PATH. Anything you specify as

<elem> must resolve to a database name.

For example:

MYDB(ADMIN)=> SET PATH = mydb, nzdb, customer;

SET VARIABLE

To display the PATH value, use the following command:

MYDB(ADMIN)=> SELECT CURRENT_PATH;

CURRENT_PATH

------------------

MYDB,NZDB,CUSTOMER

(1 row)

剩余79页未读，继续阅读

伴生伴熟

粉丝: 43
资源: 8

Netezza存储过程开发者指南

Netezza学习指导

netezza培训讲义

Netezza 数据库入门

Netezza_data_loading_guide

Netezza_system_admin_guide.pdf

Netezza_getting_started_tips.pdf 英文版

Spoon_User_Guide_3_0

Netezza调优

netezza技术

Netezza Telecommunications

最新资源