The following sections discuss how to use write operations, cursors and SELECT INTO when manipulating data.
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
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.
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 1BEGIN 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 2DECLARE 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;
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, see Result Set Procedures. The result set procedure will be in use until the associated cursor is closed.
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: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;
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.
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.
Upright Database Technology AB
Voice: +46 18 780 92 00
Fax: +46 18 780 92 40