The DB2 tutorial being looked at is: DB2 programming skills (iv). 1.10 Prevention of field null value processing
SELECT DEPTNO, Deptname, Coalesce (Mgrno, ' absent '), admrdept
From DEPARTMENT
The COALESCE function returns () The first expression in the list of expressions that is not empty and can take multiple expressions.
Similar to Oracle's IsNull, but IsNull is like a two-expression.
1.11 Number of records obtained for processing
declare v_count int;
Update tb_test set t1= ' 0 '
where t2= ' 2 ';
--Check the number of rows modified to determine if the specified record exists
Get diagnostics v_ Count=row_count;
only works on Update,insert,delete.
Not valid for SELECT INTO
1.12 Use of a result set (cursor) returned from a stored procedure
1. Build one SP return result set
CREATE PROCEDURE Db2inst1. Proc1 ()
LANGUAGE SQL
Result sets 2 (two results set returned)
------------------------------------------------------------------------
--SQL stored procedures
------------------------------------------------------------------------
P1:begin
Declare c1 cursor with return to caller for
Select Market_code
From Tb_market_code;
--Specifies that the result set is used to return 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, build an SP to tune 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;
--invokes the SP to return the result set.
Associate result set Locator (LOC1,LOC2) with procedure Proc1;
--associating the returned result set and the result set array
Allocate CURSOR1 cursor for result set loc1;
Allocate CURSOR2 cursor for result set loc2;
--Assign a result set array to cursor
Fetch cursor1 into Out_market_code;
--assigning values directly from the result set
Close Cursor1;
End P1
3. Dynamic SQL notation
DECLARE CURSOR C1 for STMT1;
PREPARE STMT1 from
' ALLOCATE C2 CURSOR for result SET? ';
4. Note:
If one SP calls several times, only the result set of the most recent call can be taken.
Second, the allocate cursor cannot be open again, but can close, 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 invocation of stored procedures
Currently, the C SP can call each other.
The SQL SP can call each other,
The SQL SP can invoke the C SP,
However, the C SP can not invoke the SQL SP (the latest version 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
Looking at the DB2 tutorial 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 not, the SQL SP will not be able to invoke 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 ' @
prev [1] [2]
