DB2 programming skills (4)

The db2 tutorial is: DB2 programming skills (4 ). 1.10 prevent handling of Null Field Values
Select deptno, DEPTNAME, COALESCE (MGRNO, 'absent'), ADMRDEPT
FROM DEPARTMENT
The COALESCE function returns the first non-empty expression in the () expression list. Multiple Expressions can be included.
It is similar to isnull in oracle, but isnull seems to have only two expressions.

1.11 retrieve the number of processed records
Declare v_count int;
Update tb_test set t1 = '0'
Where t2 = '2 ';
-- Check the number of modified rows to determine whether the specified record exists
Get diagnostics v _ count = ROW_COUNT;
Only applies to update, insert, and delete.
Invalid select

1.12 usage of returning a result set (cursor) from a stored procedure
1. Create an sp returned result set
Create procedure DB2INST1. Proc1 ()
LANGUAGE SQL
Result sets 2 (two result sets are returned)
------------------------------------------------------------------------
-- SQL stored procedure
------------------------------------------------------------------------
P1: BEGIN
Declare c1 cursor with return to caller
Select market_code
From tb_market_code;
-- Specify the result set to be returned to the caller.
Declare c2 cursor with return to caller
Select market_code
From tb_market_code;
Open c1;
Open c2;
END P1

2. Create an SP to call the sp and use its result set.

Create procedure DB2INST1. Proc2 (
Out out_market_code char (1 ))
LANGUAGE SQL
------------------------------------------------------------------------
-- SQL stored procedure
------------------------------------------------------------------------
P1: BEGIN

Declare loc1, loc2 result_set_locator varying;
-- Create a result set
Call proc1;
-- Call this SP to return the result set.
Associate result set locator (loc1, loc2) with procedure proc1;
-- Associate the returned result set with the number of result sets
Allocate cursor1 cursor for result set loc1;
Allocate cursor2 cursor for result set loc2;
-- Assign the number of result sets to cursor
Fetch cursor1 into out_market_code;
-- Assign values directly from the result set
Close cursor1;

END P1

3. dynamic SQL statement writing
Declare cursor C1 FOR STMT1;
PREPARE STMT1 FROM
'Allocate C2 cursor for result set? ';
4. Note:
1. If an sp is called several times, the result set of the last call can only be obtained.
2. The allocate cursor cannot be opened again, but can be closed, which is the corresponding cursor in the close sp.

1.13 type conversion functions
Select cast (current time as char (8) from tb_market_code

1.14 mutual calling of Stored Procedures
Currently, c sp can call each other.
SQL sp can call each other,
SQL sp can call C sp,
However, C sp cannot call SQL sp (the latest statement is yes)

1.15 C stored procedure parameters
Create procedure pr_clear_task_ctrl (
IN IN_BRANCH_CODE char (4 ),
& Nbsp

[1] [2] Next page

The db2 tutorial is: DB2 programming skills (4 ).; 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 not, the SQL sp will not be able to call the stored procedure written in c, resulting in protective errors)
NO DBINFO
FENCED
MODIFIES SQL DATA
External name 'pr _ clear_task_ctrl! Pr_clear_task_ctrl '@

Previous Page [1] [2]