Common db2 stored procedure statements

I believe everyone knows about the db2 stored procedure. The following describes some common statements for the db2 stored procedure. If you are interested in this, take a look.

---- Definition
Declare cc varchar (4000 );
Declare sqlstr varchar (4000 );
DECLARE st STATEMENT;
Declare cur cursor with return to client for cc;

---- No dynamic SQL statement is returned.
PREPARE st from sqlstr;
EXECUTE st;

---- Execute dynamic SQL to return
Prepare cc from sqlstr;
Open cur;

---- Judge whether it is null and use value substitution
COALESCE (Judgment object, substitution value)

---- Define a temporary table
Declare global temporary table session. TempResultTable
(
Organization int,
OrganizationName varchar (100 ),
AnimalTypeName varchar (20 ),
ProcessType int,
OperatorName varchar (100 ),
OperateCount int
)
With replace -- if this temporary table exists, REPLACE
Not logged;

---- String functions
Substr

---- Implicit cursor Iteration
For cursor name as select... do
Use cursor name. Field name
Content Block
End;

---- Direct return value or variable
Declare rs1 cursor with return to caller for select 0 from sysibm. sysdummy1;

---- Determine whether a table exists
Select count (*) into @ exists from syscat. tables where tabschema = current schema and tabname = 'zy _ processlog ';
---- Obtain the first N records
FETCH FIRST N ROWS ONLY
---- Define the return value
Declare rs0 cursor with return to caller for select 0 from sysibm. sysdummy1;
Declare rs1 cursor with return to caller for select 1 from sysibm. sysdummy1;
---- Get the maximum value of the inserted auto-increment Column
VALUES IDENTITY_VAL_LOCAL () INTO variable
 
 

Usage of DB2 Merge statements

Overview of DB2 Flow Control clauses

Principle of DB2 cursor

Two ways to store db2 logs

Four Common DB2 cycle statements