Mimer SQL Documentation TOC PREV NEXT INDEX

Mimer SQL Developer Site


Manipulating Data


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

Write Operations

You can use INSERT, UPDATE and DELETE statements in a function or procedure provided MODIFIES SQL DATA has been specified for the access clause, see Routine Access Clause.

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

Example
 CREATE PROCEDURE mimer_store_book.add_title(IN p_book_title VARCHAR(48),
                                             IN p_authors VARCHAR(128),
                                             IN p_published_by VARCHAR(48),
                                             IN p_format VARCHAR(20),
                                             IN p_isbn CHAR(18),
                                             IN p_date_released CHAR(10),
                                             IN p_price NUMERIC(7, 2),
                                             IN p_stock SMALLINT,
                                             IN p_reorder_level SMALLINT)
 -- Add the details for a book entity; inserts against the join view which fires
 -- the instead of trigger
 MODIFIES SQL DATA
 BEGIN
    -- Insert into join view
    INSERT INTO mimer_store_book.details(title, authors_list, publisher,
                                         format,isbn, release_date,
                                         price, stock, reorder_level)
       VALUES (p_book_title, p_authors, p_published_by, p_format,
               p_isbn, p_date_released, p_price, p_stock,
               p_reorder_level);
 END  -- of routine mimer_store_book.add_title

ROW_COUNT Option

You can use 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 v_rows INTEGER;
 ...
 INSERT INTO mimer_store_book.details ...;
 GET DIAGNOSTICS v_rows = ROW_COUNT;
 IF v_rows > 0 THEN
 
Note: All SQL statements except GET DIAGNOSTICS will overwrite the information in the diagnostics area.

Using Cursors

You can declare and use cursors in a compound SQL statement to receive a result set from a select-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
 DECLARE NREC ROW AS (SOMETABLE);
 DELARE C CURSOR FOR SELECT * FROM SOMETABLE;
 BEGIN
   DECLARE EXIT HANDLER FOR NOT FOUND CLOSE C;
   OPEN C;
   LOOP
     FETCH C INTO NREC;
     ...
   END LOOP;
 END;
Example 2
 DECLARE D DATE DEFAULT CURRENT_DATE;
 DECLARE C1,C2 CHAR(5);
 DECLARE Z SCROLL CURSOR FOR CALL 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;
 

Example 1 demonstrates detection of the NOT FOUND exception as a method of detecting 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.

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 ROWCNT = ROW_COUNT;
    IF ROWCNT = 0 THEN
       LEAVE L1;
    END IF;
 END LOOP;
 CLOSE X;

Restrictions

The following specific restrictions apply to cursors used in routines:

Using FETCH to get result set data from a result set procedure may cause parts of the result set procedure to execute, see Result 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 one single row is fetched from the database. If more than one row fulfills the search criteria, an exception condition is raised. If no data is found, a not found condition is raised.

Example
 SELECT currency, v_price * exchange_rate
    INTO p_local_currency, p_local_price
    FROM mimer_store.customers
        JOIN mimer_store.countries AS cnt ON cnt.code = country_code
            JOIN mimer_store.currencies AS crn ON crn.code = currency_code
    FETCH 1;

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 are closed.

Example

 START;
 UPDATE table
    SET ...
    WHERE col = v_str, ...
 ...
 COMMIT;
 

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.

Mimer
Mimer Information Technology AB
Voice: +46 18 780 92 00
Fax: +46 18 780 92 40
info@mimer.se
Mimer SQL Documentation TOC PREV NEXT INDEX