The db2 tutorial I am watching is: DB2 programming skills (1).
1 DB2 programming
1.1 Do not use the TAB key after creating a stored procedure
create procedure
Only spaces can be used after creating, and tabs are not available, otherwise the compilation will not be passed.
Remember, remember.
1.2 Use temporary tables
It should be noted that temporary tables can only be built on user tempory tables space. If the database only has system tempory table space, temporary tables cannot be built.
In addition, the temporary tables of DB2 are different from the temporary tables of sybase and oracle. The temporary tables of DB2 are valid in a session. Therefore, if the program has multiple threads, it is best not to use temporary tables, as it is difficult to control.
It is best to add the with and replace option when creating a temporary table, so that the drop temporary table can be displayed without display. If the option is not added when creating a temporary table and the temporary table is created in the session and there is no drop, an error will occur.
1.3 Take the first few records from the data table
select * from tb_market_code fetch first 1 rows only
But the following method does not allow
select market_code into v_market_code
from tb_market_code fetch first 1 rows only;
Select the field of the first record to a variable and replace it with the following method
declare v_market_code char(1);
declare cursor1 cursor for select market_code from tb_market_code
fetch first 1 rows only for update;
open cursor1;
fetch cursor1 into v_market_code;
close cursor1;
1.4 The use of cursor
Pay attention to commit and rollback
When using cursors, pay special attention to the cursor if the with hold option is not added, the cursor will be closed when Commit and Rollback. Commit and Rollback have a lot to pay attention to. Be especially careful
Two ways to define cursors
One is
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 method is more complicated to use, but it is also more flexible. In particular, you can use the with hold option. If there is commit or rollback in the loop and you want to keep the cursor from being closed, you can only use this method.
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 you cannot use the with hold option. 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 the 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
1 DB2 programming
1.1 Do not use the TAB key after creating a stored procedure
create procedure
Only spaces can be used after creating, and tabs are not available, otherwise the compilation will not be passed.
Remember, remember.
1.2 Use temporary tables
It should be noted that temporary tables can only be built on user tempory tables space. If the database only has system tempory table space, temporary tables cannot be built.
In addition, the temporary tables of DB2 are different from the temporary tables of sybase and oracle. The temporary tables of DB2 are valid in a session. Therefore, if the program has multiple threads, it is best not to use temporary tables, as it is difficult to control.
It is best to add the with and replace option when creating a temporary table, so that the drop temporary table can be displayed without display. If the option is not added when creating a temporary table and the temporary table is created in the session and there is no drop, an error will occur.
1.3 Take the first few records from the data table
select * from tb_market_code fetch first 1 rows only
But the following method does not allow
select market_code into v_market_code
from tb_market_code fetch first 1 rows only;
Select the field of the first record to a variable and replace it with the following method
declare v_market_code char(1);
declare cursor1 cursor for select market_code from tb_market_code
fetch first 1 rows only for update;
open cursor1;
fetch cursor1 into v_market_code;
close cursor1;
1.4 The use of cursor
Pay attention to commit and rollback
When using cursors, pay special attention to the cursor if the with hold option is not added, the cursor will be closed when Commit and Rollback. Commit and Rollback have a lot to pay attention to. Be especially careful
Two ways to define cursors
One is
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 method is more complicated to use, but it is also more flexible. In particular, you can use the with hold option. If there is commit or rollback in the loop and you want to keep the cursor from being closed, you can only use this method.
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 you cannot use the with hold option. 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 the 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