Difference between revisions of "Stored procedure"

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

Revision as of 21:10, 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 (at marker <<< 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 (at marker <<< 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 (at marker <<< 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 preceding statement.

 Create or Replace Procedure IM_MAINTENANCE
 (
      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;