DB2 Programming Skills (II)

1 DB2 Programming
1.1 When you create a stored procedure, you must not use the TAB key
CREATE PROCEDURE
The create can only use a space, but not to use the tab, otherwise compiled.
Remember, remember.

1.2 Using temporary tables

Note that temporary tables can only be built on user tempory tables space, and temporary tables cannot be built if the database is only system Tempory table space.
In addition, the temporary table of DB2 is not quite the same as that of Sybase and Oracle, and the temporary table of DB2 is valid in a session. Therefore, if the program has multiple threads, it is best not to use temporary tables, difficult to control.
When you build a temporary table, it is best to add the WITH REPLACE option so that you can create a temporary table without the drop temporary table, and an error occurs if the temporary table is created without drop in that session without the option to be displayed.
1.3 Take the first few records from the datasheet
SELECT * FROM Tb_market_code fetch a 1 rows only

But the following way does not allow
Select Market_code into V_market_code
From Tb_market_code to fetch 1 rows only;

Select the field of the first record to a variable in the following way instead
Declare V_market_code char (1);
Declare cursor1 cursor FOR select Market_code from Tb_market_code
Fetch 1 rows only for update;
Open Cursor1;
Fetch cursor1 into V_market_code;
Close Cursor1;

1.4 Use of Cursors
Pay attention to commit and rollback
Use cursors with special attention if you do not have the with HOLD option, the cursor will be closed when commit and rollback. There are many things to be aware of in Commit and rollback. Special care

Two ways to define cursors
One for
Declare continue handler for not found
Begin
Set v_notfound = 1;
End

Declare cursor1 cursor with hold for select Market_code from Tb_market_code for update;
Open Cursor1;
Set v_notfound=0;
Fetch cursor1 into V_market_code;
While V_notfound=0 do
--work
Set v_notfound=0;
Fetch cursor1 into V_market_code;
End while;
Close Cursor1;
This approach is more complex to use, but also more flexible. In particular, you can use the WITH HOLD option. You can only use this method if you have a commit or rollback in the loop and you want to keep the cursor from being closed.