Two iseries Stored Procedure examples

1. External Storage Process-think this is better and can save the encapsulated RPG or CL Program . The efficiency is also significant,
After all, it is direct Io.
It is relatively simple to bind Cl. sqlrpg hasn't done it yet. Some people know it, because I have already set DS according to the parameter, and the result still cannot work.
Create   Procedure Emplib. sp_eqchkln (
In Ckdate Char ( 8 ),
In Ckdept Char ( 2 ),
In Ckusrnm Char ( 10 ))
Result Set   0
External name emplib. eqchklncl
Language Cl
Parameter Style General

Explanation:
Standard syntax create procedure database name. Stored Procedure name
There are three types of parameters: In out inout
The parameter type is common-it may be the default value of DB2, but it is not clear.
Specify the external program name emplib. eqchklncl -- three parameters, which are also ckdate ckdept usrnm
Specify the language used by the external program. :) simple. We can call the server program by encapsulating cl a little.
You can also use the call method, but it is troublesome. I think it is better to use this method.

2. SQL stored procedures-use parameters in the form of pre-Compilation Create   Procedure Emplib. sp_getchktx (
  In Ckdate Char ( 8 ),
  In Ckdept Char ( 2 ),
  In Ckusrnm Char ( 10 ))
Result Sets 1  
Language SQL
Specific emplib. sp_getchktx
Reads SQL data
  Begin  
Declare Sqlstring Varchar ( 200 );
Declare C1 Cursor   With   Return   For S1;
Set Sqlstring =   ' Select * From emflib. eqchktxpf where eqchkdt =? And eqdept =? ' ;
If Trim (ckusrnm) <>   ''   Then  
Set Sqlstring = Sqlstring |   ' And eqoprt =? ' ;
End   If ;
Prepare S1 From Sqlstring;
  If Trim (ckusrnm) <>   ''   Then  
  Open C1 using ckdate, ckdept, ckusrnm;
  Else  
  Open C1 using ckdate, ckdept;
  End   If ;
  End

This is a standard stored procedure. If SQL statements are fixed, they are the simplest. Create   Procedure Getcusname ()
Result Sets 1  
Language SQL
Begin
Declare C1 Cursor   With   Return   For  
Select Cusnam From Customer Order   By Cusnam;
Open C1;
  End

You only need to define the number of results sets returned = several cursors
Define a cursor with Return --- v5r1 and specify the SELECT statement,
Open.
For v5r1 Create   Procedure Getcusname ()
Result Sets 1  
Language SQL
Begin
Declare C1 Cursor   For   Select Cusnam From Customer Order   By Cusnam;
Open C1;
Set Result Sets Cursor C1;
End ;

Set manually. If there are multiple result sets, you must set the result sets cursor C1, C2, c3.

If the SQL statement is not fixed, you can preprepare one and then set the SQL statement-it is more convenient to use the parameter method.
(Use? As a placeholder), then prapare the SQL statement, and finally open the Using Variable list to assign values to the statement.