Difference between revisions of "Stored procedure"

From MidrangeWiki
Jump to: navigation, search
Line 30: Line 30:
 
* Canonical documentation for parameter lists
 
* Canonical documentation for parameter lists
 
* Consume a result set.  Java?
 
* Consume a result set.  Java?
 +
 +
=== Error Handling in MAIN Stored Procedures ===
 +
The following is an abbreviates SQL stored procedure that demonstrates three methods of error handling.
 +
 +
The first method <<< 1 >>> is to declare a global error handler.  The global handler can be for either exit processing or continuation processing.  It can also handle any combination of exception, warning, and/or not found conditions.
 +
 +
The second method <<< 2 >>> is to declare a local error handler.  This one must be inside of a BEGIN ... END statement block.  When the statement block ends the local error handler is no longer in effect.
 +
 +
The third method <<< 3 >>> is to explicitly check the SQL state from the last statement executed.  This involves coding a Get Current Diagnostics Condition 1 statement to retrieve the returned SQL state.  The presence of this statement prevents SQL from performing default error handling for the preceeding statement.
 +
<pre>
 +
Create or Replace Procedure IM_MAINT                                                 
 +
(                                                                                                 
 +
      In    CompNumb    Char(5)                                                                   
 +
    , In    ItemNumb    Char(18)                                                                   
 +
)                                                                                                 
 +
            Dynamic Result Sets 1                                                                 
 +
            Language SQL                                                                           
 +
            Deterministic                                                                         
 +
            Modifies SQL Data                                                                     
 +
            Program Type MAIN                                                                     
 +
            New Savepoint Level                                                                   
 +
            Specific IMMAINTSP                                                                     
 +
                                                                                                   
 +
Set Option AlwBlk    = *AllRead                                                                   
 +
          , AlwCpyDta = *Optimize                                                                 
 +
          , CloSqlCsr = *EndActGrp                                                                 
 +
          , Commit    = *CHG
 +
          , DatFmt    = *ISO                                                                       
 +
          , DbgView  = *Source                                                                   
 +
          , DecResult = (31,31,00)                                                                 
 +
          , DftRdbCol = *None                                                                     
 +
          , DynDftCol = *No                                                                       
 +
          , DynUsrPrf = *User                                                                     
 +
          , ExtInd    = *YES
 +
          , LangId    = ENU                                                                       
 +
          , Output    = *Print                                                                     
 +
          , SrtSeq    = *Hex                                                                       
 +
          , UsrPrf    = *User                                                                     
 +
                                                                                                   
 +
MyProc: Begin                                                                                     
 +
---------------------------------------------------------------------------                       
 +
-- variable definitions                                                                           
 +
---------------------------------------------------------------------------                       
 +
Declare CompId    BigInt;            -- company master id                                       
 +
Declare DtaLib    Char(10);                                                                       
 +
Declare ItmTid    BigInt;            -- item number id                                           
 +
Declare SqlStmt  VarChar(3000);                                                                 
 +
                                                                                                   
 +
Declare IGNORE_VALUE  SmallInt Default -7;
 +
                                                                                                   
 +
Declare CurState  Char(5);                                                                       
 +
Declare ErrState  Char(5);
 +
Declare ErrPrfx  Char(11) Default 'IMMAINTSP: ';                                                 
 +
Declare ErrText  VarChar(300);
 +
Declare IMMAINTSP_CURSOR Cursor With Return For IMMAINTSP_STMT;                                   
 +
                                                                                                   
 +
---------------------------------------------------------------------------                       
 +
-- global exit handler                                                                             
 +
---------------------------------------------------------------------------                       
 +
Declare Exit Handler for SQLEXCEPTION, SQLWARNING, NOT FOUND              <<< 1 >>>
 +
  Begin                                                                                           
 +
    Get Current Diagnostics Condition 1                                                           
 +
        ErrState = Returned_SqlState;                                                             
 +
    Set ErrText = ErrPrfx || ErrText || '  State=' || ErrState;                                   
 +
    Signal SqlState '88W00' Set Message_Text = ErrText;                                           
 +
  End;                                                                                           
 +
                                                                                                   
 +
---------------------------------------------------------------------------                       
 +
-- clean up alias definition if needed                                                             
 +
---------------------------------------------------------------------------                       
 +
Begin                                                                                             
 +
  Declare Continue Handler for SQLSTATE '42704' Begin End; -- notfnd      <<< 2 >>>
 +
  Set ErrText = 'Initial cleanup of alias failed.';                                               
 +
  Drop Alias QTEMP/IMMAINTA;                                                                     
 +
End;                                                                                             
 +
                                                                                                   
 +
---------------------------------------------------------------------------                       
 +
-- validate company master using static SQL                                                       
 +
---------------------------------------------------------------------------                       
 +
Set ErrText = 'Company number validation failed.';                                               
 +
Select CiCompPTid, LibCde                                                                         
 +
  Into CompId, DtaLib                                                                             
 +
  From SHR460/CICOMPPV02                                                                         
 +
  Where CIORGKY = CompNumb;                                                                       
 +
                                                                                                   
 +
IF CompId is NULL                                                                                 
 +
Or DtaLib is NULL Then                                                                           
 +
    Set ErrText = 'Company number not on file or data library missing.';                           
 +
    Signal SqlState '88W01' Set Message_Text = ErrText;                                           
 +
End If;                                                                                           
 +
                                                                                                   
 +
---------------------------------------------------------------------------                       
 +
-- point to local company data library                                                             
 +
---------------------------------------------------------------------------                       
 +
Set ErrText = 'Set path failed.';                                                                 
 +
Set Path    = "QSYS","QSYS2","SYSPROC","SYSIBMADM"                                               
 +
              ,"QTEMP",DtaLib,"SHR460","PGM460","PTF460"                                           
 +
              ,"QGPL";                -- for dynamic SQL                                         
 +
                                                                                                   
 +
Set ErrText = 'Setting schema '||DtaLib||' failed.';                                       
 +
Set Schema DtaLib;                                                                         
 +
                                                                                                   
 +
Get Current Diagnostics Condition 1 CurState = Returned_SqlState;        <<< 3 >>>
 +
If Substr(CurState,1,2) <> '00'                                                                   
 +
Or Not Exists (Select 1 from QSYS2/SYSSCHEMAS                                                     
 +
                where System_Schema_Name = BackupLibrary) Then                                   
 +
  Signal SqlState '88W02' Set Message_Text = ErrText;                                             
 +
End If;
 +
 +
-- ...snip...
 +
 +
---------------------------------------------------------------------------                       
 +
-- return to caller                                                                               
 +
---------------------------------------------------------------------------                       
 +
Return;
 +
End;                                                                                                 
 +
</pre>

Revision as of 21:00, 1 March 2019

Stored procedures are a way to extend database functionality. Wikipedia calls a stored procedure a subroutine, which is as good a definition as any. Stored procedures (SP from here on) can be used to:

  • Encapsulate complex processing
  • Update a table. Sure, UPDATE does that, but then the end user (web developer) needs to be familiar with your database.
  • Return data to SQL such as a result set. Again, isolate the database from the UI. A result set can consolidate columns from several tables, in essence hiding the JOINS from the ultimate end user of the data.
  • Provide a service to SQL like RTVSYSVAL.

IBM have a 2006 Redbook entitled Stored Procedures, Triggers, and User-Defined Functions on DB2 Universal Database for iSeries. It's old but it provides a foundation for understanding stored procedures.

SPs are an inherent part of DB2; they are part of the database. On other platforms, the logic section of SPs is written using SQL. That's a choice on DB2 for i as well, and this is called an SQL (or MAIN) Stored Procedure. We have another choice as well, and that is to write the logic using an HLL like RPG. This is called an External stored procedure. In either case, you register a SP with DB2 via the SQL CREATE PROCEDURE statement. To use a stored procedure in your SQL, use the CALL statement.

While all programs on the IBM i can be called as stored procedures, note that CL commands are not programs. One way around this is to use QSYS.QCMDEXEC() or QSYS.QCMDEXEC2() stored procedures. You can also use the CL: prefix to use CL commands directly in your own SQL stored procedure. For example: 

CL: CHGCURLIB CURLIB(MYLIB);

An external SP interfaces with DB2 via the parameter list. There are several parameter styles, depending on whether you will need to handle NULL values or not.

TODO:

  • Example tables
  • Example SQL SP
    • One return variable
    • Result set
  • Example RPGLE SP
    • One return variable
    • Result set
  • Multiple parameter list usage
    • GENERAL
    • WITH NULLS
  • Canonical documentation for parameter lists
  • Consume a result set. Java?

Error Handling in MAIN Stored Procedures

The following is an abbreviates SQL stored procedure that demonstrates three methods of error handling.

The first method <<< 1 >>> is to declare a global error handler. The global handler can be for either exit processing or continuation processing. It can also handle any combination of exception, warning, and/or not found conditions.

The second method <<< 2 >>> is to declare a local error handler. This one must be inside of a BEGIN ... END statement block. When the statement block ends the local error handler is no longer in effect.

The third method <<< 3 >>> is to explicitly check the SQL state from the last statement executed. This involves coding a Get Current Diagnostics Condition 1 statement to retrieve the returned SQL state. The presence of this statement prevents SQL from performing default error handling for the preceeding statement. 

 Create or Replace Procedure IM_MAINT                                                  
 (                                                                                                  
      In    CompNumb    Char(5)                                                                     
    , In    ItemNumb    Char(18)                                                                    
 )                                                                                                  
            Dynamic Result Sets 1                                                                   
            Language SQL                                                                            
            Deterministic                                                                           
            Modifies SQL Data                                                                       
            Program Type MAIN                                                                       
            New Savepoint Level                                                                     
            Specific IMMAINTSP                                                                      
                                                                                                    
 Set Option AlwBlk    = *AllRead                                                                    
          , AlwCpyDta = *Optimize                                                                   
          , CloSqlCsr = *EndActGrp                                                                  
          , Commit    = *CHG
          , DatFmt    = *ISO                                                                        
          , DbgView   = *Source                                                                     
          , DecResult = (31,31,00)                                                                  
          , DftRdbCol = *None                                                                       
          , DynDftCol = *No                                                                         
          , DynUsrPrf = *User                                                                       
          , ExtInd    = *YES
          , LangId    = ENU                                                                         
          , Output    = *Print                                                                      
          , SrtSeq    = *Hex                                                                        
          , UsrPrf    = *User                                                                       
                                                                                                    
MyProc: Begin                                                                                       
---------------------------------------------------------------------------                         
-- variable definitions                                                                             
---------------------------------------------------------------------------                         
 Declare CompId    BigInt;             -- company master id                                         
 Declare DtaLib    Char(10);                                                                        
 Declare ItmTid    BigInt;             -- item number id                                            
 Declare SqlStmt   VarChar(3000);                                                                   
                                                                                                    
 Declare IGNORE_VALUE  SmallInt Default -7;
                                                                                                    
 Declare CurState  Char(5);                                                                         
 Declare ErrState  Char(5);
 Declare ErrPrfx   Char(11) Default 'IMMAINTSP: ';                                                  
 Declare ErrText   VarChar(300);
 Declare IMMAINTSP_CURSOR Cursor With Return For IMMAINTSP_STMT;                                    
                                                                                                    
---------------------------------------------------------------------------                         
-- global exit handler                                                                              
---------------------------------------------------------------------------                         
 Declare Exit Handler for SQLEXCEPTION, SQLWARNING, NOT FOUND              <<< 1 >>>
   Begin                                                                                            
     Get Current Diagnostics Condition 1                                                            
         ErrState = Returned_SqlState;                                                              
     Set ErrText = ErrPrfx || ErrText || '  State=' || ErrState;                                    
     Signal SqlState '88W00' Set Message_Text = ErrText;                                            
   End;                                                                                             
                                                                                                    
---------------------------------------------------------------------------                         
-- clean up alias definition if needed                                                              
---------------------------------------------------------------------------                         
 Begin                                                                                              
   Declare Continue Handler for SQLSTATE '42704' Begin End; -- notfnd      <<< 2 >>>
   Set ErrText = 'Initial cleanup of alias failed.';                                                
   Drop Alias QTEMP/IMMAINTA;                                                                       
 End;                                                                                               
                                                                                                    
---------------------------------------------------------------------------                         
-- validate company master using static SQL                                                         
---------------------------------------------------------------------------                         
 Set ErrText = 'Company number validation failed.';                                                 
 Select CiCompPTid, LibCde                                                                          
   Into CompId, DtaLib                                                                              
   From SHR460/CICOMPPV02                                                                           
  Where CIORGKY = CompNumb;                                                                         
                                                                                                    
 IF CompId is NULL                                                                                  
 Or DtaLib is NULL Then                                                                             
    Set ErrText = 'Company number not on file or data library missing.';                            
    Signal SqlState '88W01' Set Message_Text = ErrText;                                             
 End If;                                                                                            
                                                                                                    
---------------------------------------------------------------------------                         
-- point to local company data library                                                              
---------------------------------------------------------------------------                         
 Set ErrText = 'Set path failed.';                                                                  
 Set Path    = "QSYS","QSYS2","SYSPROC","SYSIBMADM"                                                 
              ,"QTEMP",DtaLib,"SHR460","PGM460","PTF460"                                            
              ,"QGPL";                 -- for dynamic SQL                                           
                                                                                                    
 Set ErrText = 'Setting schema '||DtaLib||' failed.';                                        
 Set Schema DtaLib;                                                                          
                                                                                                    
 Get Current Diagnostics Condition 1 CurState = Returned_SqlState;         <<< 3 >>>
 If Substr(CurState,1,2) <> '00'                                                                    
 Or Not Exists (Select 1 from QSYS2/SYSSCHEMAS                                                      
                 where System_Schema_Name = BackupLibrary) Then                                     
   Signal SqlState '88W02' Set Message_Text = ErrText;                                              
 End If;

-- ...snip...

---------------------------------------------------------------------------                         
-- return to caller                                                                                 
---------------------------------------------------------------------------                         
 Return;
 End;
Retrieved from "https://wiki.midrange.com/index.php?title=Stored_procedure&oldid=13502"