The db2 tutorial I am watching is: DB2 programming skills (III). Another type is
pcursor1: for loopcs1 as cousor1 cursor as
select market_code as market_code
from tb_market_code
for update
do
end for;
The advantage of this method is that it is relatively simple, and it does not use (and does not allow) open, fetch, and close.
But the with hold option cannot be used. If you want to use commit in the cursor loop, rollback cannot be used. If there is no commit or rollback requirement, it is recommended to use this method (it seems that there is a problem with For).
Methods to modify the cursor's current record
update tb_market_code set market_code='0' where current of cursor1;
However, be careful 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 with GROUP BY, DISTINCT, ORDER BY, FOR READ ONLY and UNION, EXCEPT, or INTERSECT, except UNION ALL).
1.5 Decode-like transcoding operations
There is a function in oracle select decode(a1,'1','n1','2','n2','n3') aa1 from
db2 does not have this function, but a workaround can be used
select case a1
when '1' then 'n1'
when '2' then 'n2'
else 'n3'
end as aa1 from
1.6 Similar to charindex to find the position of characters in a string
Locate(‘y','dfdasfay')
Find the location of 'y' in 'dfdasfay'.
1.7 Similar to datedif to calculate the number of days difference between two dates
days(date(‘2001-06-05')) – days(date(‘2001-04-01'))
days Returns the number of days calculated from 0001-01-01
1.8 Examples of writing UDFs
C write to sqllib\samples\cli\
1.9 Create a table with identity value (i.e., the automatically generated ID)
How to write 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 identity column is allowed in a table.
pcursor1: for loopcs1 as cousor1 cursor as
select market_code as market_code
from tb_market_code
for update
do
end for;
The advantage of this method is that it is relatively simple, and it does not use (and does not allow) open, fetch, and close.
But the with hold option cannot be used. If you want to use commit in the cursor loop, rollback cannot be used. If there is no commit or rollback requirement, it is recommended to use this method (it seems that there is a problem with For).
Methods to modify the cursor's current record
update tb_market_code set market_code='0' where current of cursor1;
However, be careful 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 with GROUP BY, DISTINCT, ORDER BY, FOR READ ONLY and UNION, EXCEPT, or INTERSECT, except UNION ALL).
1.5 Decode-like transcoding operations
There is a function in oracle select decode(a1,'1','n1','2','n2','n3') aa1 from
db2 does not have this function, but a workaround can be used
select case a1
when '1' then 'n1'
when '2' then 'n2'
else 'n3'
end as aa1 from
1.6 Similar to charindex to find the position of characters in a string
Locate(‘y','dfdasfay')
Find the location of 'y' in 'dfdasfay'.
1.7 Similar to datedif to calculate the number of days difference between two dates
days(date(‘2001-06-05')) – days(date(‘2001-04-01'))
days Returns the number of days calculated from 0001-01-01
1.8 Examples of writing UDFs
C write to sqllib\samples\cli\
1.9 Create a table with identity value (i.e., the automatically generated ID)
How to write 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 identity column is allowed in a table.