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 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 data ROW AS (mimer_store_book.details(title, authors_list, format,
                                                  price, item_id, product_id,
                                                  display_order));
    DECLARE c_1 CURSOR FOR SELECT title, authors_list, format,
                                  price, item_id, product_id,
                                  display_order
                              FROM mimer_store_book.details
                              NATURAL JOIN mimer_store_book.authors
                              NATURAL JOIN mimer_store_book.keywords
                              WHERE title_search LIKE TRIM(TRAILING '0' FROM
                                 mimer_store.product_search_code(p_book_title))
                                 || '%'
                              AND keyword LIKE REPLACE(
                                 mimer_store_book.authors_name(p_author),
                                 ',', '%,')
                                 || '%'
                              ORDER BY title, authors_list, product_id,
                                       display_order;
 
    DECLARE EXIT HANDLER FOR NOT FOUND
       CLOSE c_1;
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;

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 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 record is fetched from the database. If more (or less) than one record fulfills the search criteria, an exception condition is raised.

Example: 
 SELECT currency, v_euros * 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
    WHERE customer_id = p_customer_id
    AND currency_code <> 'EUR'
    AND exchange_rate IS NOT NULL;

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