Data Manipulation

www.mimer.com/developer

Data Manipulation

The following sections discuss how to use write operations, using cursors and SELECT INTO when manipulating data.

Write Operations

INSERT, UPDATE and DELETE statements may be used in a routine provided MODIFIES SQL DATA has been specified for the access clause, see Routine Access Clause.

Routine parameters and variables may be used in these statements wherever an expression can normally be used, as shown in the examples below.

Example:
 CREATE PROCEDURE INSERT_HOTEL(IN I_NAME CHAR(15),
                                  I_CITY CHAR(20),
                                  I_OVERBOOK DEC(3,2))
 MODIFIES SQL DATA
 BEGIN
    INSERT INTO HOTEL VALUES(SUBSTRING(I_NAME FROM 1 FOR 4),
                             I_NAME,I_CITY,I_OVERBOOK);
 ...
ROW_COUNT Option

The ROW_COUNT option of the GET DIAGNOSTICS statement may be used immediately after an INSERT, UPDATE, DELETE, SELECT INTO or FETCH statement to determine the number of rows affected by the preceding statement.

Example:
 DECLARE ROWS INTEGER;
 ...
 INSERT INTO ROOMS SELECT ...;
 GET DIAGNOSTICS ROWS = ROW_COUNT;
 IF ROWS > 0 THEN
 
Note: All SQL statements except GET DIAGNOSTICS will overwrite the information in the diagnostics area.

Using Cursors

Cursors may be declared and used in a compound SQL statement to receive a result set from a query-expression or from a result set procedure. A cursor may not have the same name as another cursor declared in the same scope.

Cursors in a procedural usage context are used in much the same way, in terms of the SQL statements used, as cursors declared outside routines. It is possible to open cursors, fetch data into variables and use the statements UPDATE and DELETE where current of cursor.

Example 1:
 BEGIN
   DECLARE X CURSOR FOR SELECT CHARGE_CODE,AMOUNT
                          FROM BILL FOR UPDATE;
   DECLARE I_CHARGE_CODE CHAR(3);
   DECLARE I_AMOUNT DEC(8,2);
   ...
   OPEN X;
 L1:
   BEGIN
     DECLARE EXIT HANDLER FOR NOT FOUND
     BEGIN
     END;
     LOOP
        FETCH X INTO I_CHARGE_CODE,I_AMOUNT;
        IF I_CHARGE_CODE = '270' AND ... THEN
           UPDATE BILL SET AMOUNT = AMOUNT * 1.10 WHERE CURRENT OF X;
        END IF;
     END LOOP;
   END;
   CLOSE X;
 END
 
Example 2:
 DECLARE D DATE DEFAULT CURRENT_DATE;
 DECLARE C1,C2 CHAR(5);
 DECLARE Z SCROLL CURSOR FOR CALL MY_PROC(1,D);
 DECLARE I INTEGER;
 ...
 OPEN Z;
 ...
 FETCH FIRST FROM Z INTO C1;
 ...
 FETCH ABSOLUTE I FROM Z INTO C2;
 
 FETCH ABSOLUTE I FROM Z INTO C2;
 
The example 1 demonstrates detection of the NOT FOUND exception as a method of checking that a fetch statement does not return any data. If a NOT FOUND exception occurs in the example, an exit handler is invoked. After the exit handler has finished, the flow of control leaves the compound SQL statement labeled L1.

Alternatively, the GET DIAGNOSTICS statement can be used to retrieve the number of rows affected by the FETCH statement, as shown below.

Example:
 DECLARE ROWS INTEGER;
 L1:
 LOOP
    FETCH X INTO I_CHARGE_CODE,I_AMOUNT;
    GET DIAGNOSTICS ROWS = ROW_COUNT;
    IF ROWS = 0 THEN
       LEAVE L1;
    END IF;
 END LOOP;
 CLOSE X;
Restrictions

The following specific restrictions apply to cursors used in routines:

no dynamic functions can be used (i.e. extended cursor names and the use of SQL descriptors)
REOPENABLE cursors are not allowed
the use of the keyword RELEASE with the CLOSE statement is not permitted.

Using FETCH to get result set data from a result set procedure may cause parts of the result set procedure to execute, seeResult Set Procedures. The result set procedure will be "in use" until the associated cursor is closed.

SELECT INTO

Another way of fetching data is by using a SELECT INTO statement. This can only be used when a single record is fetched from the database. If more than one record fulfills the search criteria, an exception condition is raised.

Example:
 DECLARE TOTAL INTEGER;
 SELECT SUM(AMOUNT) INTO TOTAL
   FROM BILL WHERE RESERVATION = IN_RESERVATION;
Transactions

It is possible to start and end transactions within a routine. A transaction is implicitly started when a routine that accesses the database is invoked. It is also possible to explicitly start a transaction by using the START statement. When a transaction is ended, either by a COMMIT or ROLLBACK statement, all open cursors in the routine are closed.

It is possible to affect the behavior of transactions by using the SET TRANSACTION and SET SESSION statements.

Note: If a compound SQL statement is defined as ATOMIC, a transaction cannot be terminated within it because execution of the COMMIT or ROLLBACK statements is not permitted.

Upright Database Technology AB
Voice: +46 18 780 92 00
Fax: +46 18 780 92 40
dbtechnology@upright.se