DB2 programming skills (III)

The db2 tutorial is: DB2 programming skills (3 ). The other is
Pcursor1: for loopcs1 as cousor1 cursor
Select market_code as market_code
From tb_market_code
For update
Do
End;
The advantage of this method is that it is relatively simple, and open, fetch, and close are not used (or allowed.
However, the with hold option cannot be used. If you want to use commit in a cursor loop, rollback cannot use this method. If you do not have the commit or rollback requirements, we recommend this method (it seems that there is a problem with the For method ).

How to modify the current record of a cursor
Update tb_market_code set market_code = '0' where current of cursor1;
However, be sure to define cursor1 as a modifiable cursor.
Declare cursor1 cursor for select market_code from tb_market_code
For update;

For update cannot be used together with group by, DISTINCT, order by, for read only, UNION, EXCEPT for union all.



1.5 decode-like transcoding operations
Oracle has a function select decode (a1, '1', 'n1 ', '2', 'n2', 'n3 ') aa1 from
Db2 does not have this function, but it can be modified.
Select case a1
When '1' then 'n1'
When '2' then 'n2'
Else 'n3'
End as aa1 from

1.6 search for the position of a character in a string similar to charindex
Locate ('y', 'dfdasfay ')
Find the location of 'y' in 'dfdasfay.

1.7 calculate the number of different days for two dates similar to datedif
Days (date ('1970-06-05 ')-days (date ('1970-04-01 '))
Days returns the number of days calculated from 0001-01-01.
1.8 UDF writing example
For C, see sqllib \ samples \ cli \ udfsrv. c.

1.9 create a table with the identity value (automatically generated ID)
Writing such a table
Create table test
(T1 SMALLINT NOT NULL
GENERATED ALWAYS AS IDENTITY
(Start with 500, increment by 1 ),
T2 CHAR (1 ));
Only one column of identity is allowed in a table.


<