The db2 tutorial you are watching is: DB2 programming skills (IV). 1.10 Prevent field null values processing
SELECT DEPTNO ,DEPTNAME ,COALESCE(MGRNO ,'ABSENT'),ADMRDEPT
FROM DEPARTMENT
The COALESCE function returns the first expression in the expression list in () that is not empty, and can have multiple expressions.
Similar to the isnull of oracle, but it seems that there can only be two expressions.
1.11 Get the number of records processed
declare v_count int;
update tb_test set t1='0'
where t2='2';
--Check the number of modified rows and determine whether the specified record exists
get diagnostics v_ count=ROW_COUNT;
Only work for update, insert, delete.
Not valid for select into
1.12 Usage of returning result sets (cursors) from stored procedures
1. Create a sp to return the result set
CREATE PROCEDURE DB2INST1.Proc1 ( )
LANGUAGE SQL
result sets 2 (return two result sets)
------------------------------------------------------------------------
--SQL Stored Procedures
------------------------------------------------------------------------
P1: BEGIN
declare c1 cursor with return to caller for
select market_code
from tb_market_code;
--Specify this result set to be returned to the caller
declare c2 cursor with return to caller for
select market_code
from tb_market_code;
open c1;
open c2;
END P1
2. Create a SP to adjust the sp and use its result set
CREATE PROCEDURE DB2INST1.Proc2 (
out out_market_code char(1))
LANGUAGE SQL
------------------------------------------------------------------------
--SQL Stored Procedures
------------------------------------------------------------------------
P1: BEGIN
declare loc1,loc2 result_set_locator varying;
--Create an array of result sets
call proc1;
--Call this SP and return the result set.
associate result set locator(loc1,loc2) with procedure proc1;
--Associate the result set and the result set array
allocate cursor1 cursor for result set loc1;
allocate cursor2 cursor for result set loc2;
-- Assign the result set array to cursor
fetch cursor1 into out_market_code;
-- Assign values directly from the result set
close cursor1;
END P1
3. Dynamic SQL writing method
DECLARE CURSOR C1 FOR STMT1;
PREPARE STMT1 FROM
'ALLOCATE C2 CURSOR FOR RESULT SET ?';
4. Note:
1. If a sp is called several times, the result set of the last call can only be retrieved.
2. The cursor of allocate cannot be opened again, but it can be closed, which is the corresponding cursor in close sp.
1.13 Type conversion function
select cast ( current time as char(8)) from tb_market_code
1.14 Mutual calls of stored procedures
Currently, c sp can call each other.
Sql sp can call each other,
Sql sp can call C sp,
But C sp cannot call Sql sp (the latest statement is ok)
1.15 C stored procedure parameters note
create procedure pr_clear_task_ctrl(
IN IN_BRANCH_CODE char(4),
 
IN IN_TASK_ID char(2),
IN IN_SUB_TASK_ID char(4),
OUT OUT_SUCCESS_FLAG INTEGER )
DYNAMIC RESULT SETS 0
LANGUAGE C
PARAMETER STYLE GENERAL WITH NULLS (If this is not the case, the sql sp will not be able to call the stored procedure written in c, resulting in a protective error)
NO DBINFO
FENCED
MODIFIES SQL DATA
EXTERNAL NAME 'pr_clear_task_ctrl!pr_clear_task_ctrl'@
SELECT DEPTNO ,DEPTNAME ,COALESCE(MGRNO ,'ABSENT'),ADMRDEPT
FROM DEPARTMENT
The COALESCE function returns the first expression in the expression list in () that is not empty, and can have multiple expressions.
Similar to the isnull of oracle, but it seems that there can only be two expressions.
1.11 Get the number of records processed
declare v_count int;
update tb_test set t1='0'
where t2='2';
--Check the number of modified rows and determine whether the specified record exists
get diagnostics v_ count=ROW_COUNT;
Only work for update, insert, delete.
Not valid for select into
1.12 Usage of returning result sets (cursors) from stored procedures
1. Create a sp to return the result set
CREATE PROCEDURE DB2INST1.Proc1 ( )
LANGUAGE SQL
result sets 2 (return two result sets)
------------------------------------------------------------------------
--SQL Stored Procedures
------------------------------------------------------------------------
P1: BEGIN
declare c1 cursor with return to caller for
select market_code
from tb_market_code;
--Specify this result set to be returned to the caller
declare c2 cursor with return to caller for
select market_code
from tb_market_code;
open c1;
open c2;
END P1
2. Create a SP to adjust the sp and use its result set
CREATE PROCEDURE DB2INST1.Proc2 (
out out_market_code char(1))
LANGUAGE SQL
------------------------------------------------------------------------
--SQL Stored Procedures
------------------------------------------------------------------------
P1: BEGIN
declare loc1,loc2 result_set_locator varying;
--Create an array of result sets
call proc1;
--Call this SP and return the result set.
associate result set locator(loc1,loc2) with procedure proc1;
--Associate the result set and the result set array
allocate cursor1 cursor for result set loc1;
allocate cursor2 cursor for result set loc2;
-- Assign the result set array to cursor
fetch cursor1 into out_market_code;
-- Assign values directly from the result set
close cursor1;
END P1
3. Dynamic SQL writing method
DECLARE CURSOR C1 FOR STMT1;
PREPARE STMT1 FROM
'ALLOCATE C2 CURSOR FOR RESULT SET ?';
4. Note:
1. If a sp is called several times, the result set of the last call can only be retrieved.
2. The cursor of allocate cannot be opened again, but it can be closed, which is the corresponding cursor in close sp.
1.13 Type conversion function
select cast ( current time as char(8)) from tb_market_code
1.14 Mutual calls of stored procedures
Currently, c sp can call each other.
Sql sp can call each other,
Sql sp can call C sp,
But C sp cannot call Sql sp (the latest statement is ok)
1.15 C stored procedure parameters note
create procedure pr_clear_task_ctrl(
IN IN_BRANCH_CODE char(4),
 
[1] [2] Next page
The db2 tutorial you are watching is: DB2 programming skills (IV). ; IN IN_TRADEDATE char(8),IN IN_TASK_ID char(2),
IN IN_SUB_TASK_ID char(4),
OUT OUT_SUCCESS_FLAG INTEGER )
DYNAMIC RESULT SETS 0
LANGUAGE C
PARAMETER STYLE GENERAL WITH NULLS (If this is not the case, the sql sp will not be able to call the stored procedure written in c, resulting in a protective error)
NO DBINFO
FENCED
MODIFIES SQL DATA
EXTERNAL NAME 'pr_clear_task_ctrl!pr_clear_task_ctrl'@
Previous page [1] [2]