HP NonStop SQL/MP User Manual

Data Retrieval and Modification
HP NonStop SQL/MP Programming Manual for C—429847-008
4-23
Multirow DELETE Statement
...
EXEC SQL OPEN get_by_partnum;
... /* Set values of the host variables. */
EXEC SQL FETCH get_by_partnum INTO ... ;
... /* Test the value(s) in the current row. */
/* Update the current row */
EXEC SQL UPDATE sales.parts
SET parts.partdesc = :new_partdesc,
parts.price = :new_price,
parts.qty_available = :new_qty
WHERE CURRENT OF get_by_partnum;
... /* Branch back to FETCH to get the next row. */
EXEC SQL CLOSE get_by_partnum;
Multirow DELETE Statement
When used with a cursor, a DELETE statement deletes multiple rows one row at a time
from a table or protection view. You identify the set of rows to delete (or test) in the
associated SELECT statement. Before you delete a row, you can test one or more
column values, and then if you decide to delete the row, specify the WHERE
CURRENT OF clause in the DELETE statement.
If you delete all rows from a table, the table still exists until it is deleted from the catalog
by a DROP TABLE statement.
To run a DELETE statement, a process started by the program must have read and
write access to the table or view and to tables or views specified in subqueries of the
search condition. For more information about process access, see Required
Access Authority on page 7-1.
A DELETE statement must run within the scope of all other SQL statements, including
the DECLARE CURSOR, OPEN, FETCH, INSERT, and CLOSE statements, that refer
to the cursor. For audited tables and views, the DELETE statement must run within the
same TMF transaction as the OPEN and FETCH statements for the cursor.
This example declares a cursor get_by_partnum, fetches data from the PARTS
table, tests the data, and then deletes specific rows:
EXEC SQL DECLARE get_by_partnum CURSOR FOR
SELECT partnum,
partdesc,
price,
qty_available
Note. Do not use a stand-alone DELETE statement to delete a row that has been retrieved
using a FETCH statement. A stand-alone DELETE statement can invalidate the cursor’s
buffering for the table and degrade performance.

HP NonStop SQL/MP Specifications

General

Platform	HP NonStop Servers
Data Model	Relational
SQL Standard	ANSI SQL
Concurrency Control	Multi-Version Concurrency Control (MVCC)
Fault Tolerance	Yes
High Availability	Yes
Category	Database Software
Initial Release	1980s
Operating System	HP NonStop OS
License	Proprietary
Type	Relational Database Management System
Transaction Management	ACID Compliance
Developer	Hewlett Packard Enterprise

Summary

1. Introduction

Developing a C Program

Outlines the process of creating C programs with embedded SQL statements.

Dynamic SQL

Describes how to construct, compile, and run SQL statements at runtime.

2. Host Variables

3. SQL/MP Statements and Directives

4. Data Retrieval and Modification

Single-Row SELECT Statement

Retrieves a single row of data from tables or views into host variables.

Multirow SELECT Statement

Returns multiple rows from tables or views, one row at a time, using cursors.

INSERT Statement

Inserts one or more rows into a table or protection view using host variables.

Using SQL Cursors

Explains how to use named pointers to access sets of rows one at a time.

5. SQL/MP System Procedures

6. Explicit Program Compilation

Explicit Program Compilation

Outlines the general steps for compiling C programs with embedded SQL.

Developing a C Program in the Guardian Environment

Details C program development within the Guardian environment.

Developing a C Program in the OSS Environment

Developing a C Program in a PC Host Environment

7. Program Execution

Required Access Authority

Specifies the access permissions needed to run NonStop SQL program files.

Entering the TACL RUN Command

Running a Program in the OSS Environment

8. Program Invalidation and Automatic SQL Recompilation

Program Invalidation

Explains how SQL program files can become invalid due to changes or errors.

Causes of Program Invalidation

Automatic SQL Recompilation

Describes the run-time recompilation process invoked by the SQL executor.

9. Error and Status Reporting

Returning Error and Warning Information

Using the WHENEVER Directive

10. Dynamic SQL Operations

Uses for Dynamic SQL

Identifies scenarios where dynamic SQL programs are beneficial.

Dynamic SQL Statements

Summarizes dynamic SQL statements usable in a C program.

Dynamic SQL Features

11. Character Processing Rules (CPRL) Procedures

Appendix B: Memory Considerations

Estimating Memory Requirements

Avoiding Memory Stack Overflows

Provides guidelines to prevent stack overflows for SQL statements.