Stored Procedures

Introduction

An SQL function that you define and may call is known as a user-defined function (UDF). A UDF’s logic usually extends or improves SQL by adding capabilities that SQL lacks or can’t accomplish adequately, just like with built-in functions you may call from SQL. You may also encapsulate functionality with a UDF and call it again from different places within your code.

Stored procedure types

There are two categories into which stored procedures can be divided:

SQL stored procedures External stored procedures.

• SQL stored procedures
  • SQL stored procedures are written in the SQL language. This makes it easier to port stored procedures from other database management systems (DBMS) to the iSeries server and from the iSeries server to other DBMS. Implementation of the SQL stored procedures is based on procedural SQL standardized in SQL99.

  Example 1: Create a stored procedure to update a balance for customer using input parameters.

  • Create a table as BANK using RUNSQLSTM command.

    CREATE OR REPLACE TABLE BANK
    (
     CUSTNO      NUMERIC(10),
     CUSTNAME    VARCHAR(30),
     BALANCE     DECIMAL(9,2),
     PRIMARY KEY(CUSTNO)
     )
     RCDFMT RBANK;
    

  • Create a SQL Stored procedure to update BALANCE column in BANK table based on input parameter:

    CREATE OR REPLACE PROCEDURE UPDATECUSTBANK
       (IN IN_CUSTNO   NUMERIC(10),
        IN IN_BALANCE  DECIMAL(9,2))
    LANGUAGE SQL MODIFIES SQL DATA
    UPDATE BANK
       SET BALANCE = BALANCE + IN_BALANCE
       WHERE CUSTNO = IN_CUSTNO
    
    

  • Now add the BALANCE as 500 for CUSTNO-100,003 in the BANK table:

                            CUSTNO   CUSTNAME                BALANCE
    100,001   JAMES        100.15
    100,003   BRYAN     500.00
    100,002   JOHN     214.00
    100,004   RICHS     854.00
    100,005   RHONDA      10,000.00
    
    

  • Now add the BALANCE as 500 for CUSTNO-100,003 in the BANK table using CALL statement in STRSQL:

    CALL UPDATECUSTBANK(100003,500)
     CALL statement complete.
    

    CUSTNO   CUSTNAME                     BALANCE
    100,001   JAMES                       100.15
    100,003   BRYAN                       1000.00
    100,002   JOHN                        214.00
    100,004   RICHS                       854.00
    100,005   RHONDA                      10,000.00
    
    

  Example 2: Create a stored procedure with CASE condition to update a balance based on a performance for customer using input parameters.

  • Create a stored procedures using RUNSQLSTM command.

    CREATE OR REPLACE PROCEDURE INCREMENTSALARY
       (IN IN_CUSTNO    NUMERIC(10),
        IN IN_RATING    CHAR(10) )
    LANGUAGE SQL MODIFIES SQL DATA
    CASE IN_RATING
      WHEN 'GOOD' THEN
         UPDATE BANK
            SET BALANCE = BALANCE * 10 WHERE CUSTNO = IN_CUSTNO;
      WHEN 'AVG ' THEN
         UPDATE BANK
            SET BALANCE = BALANCE * 5  WHERE CUSTNO = IN_CUSTNO;
      WHEN 'BAD ' THEN
         UPDATE BANK
            SET BALANCE = BALANCE * 1  WHERE CUSTNO = IN_CUSTNO;
    END CASE
    
    

  • Now increment the BALANCE as 10x for CUSTNO-100,001 in the BANK table using CALL statement in STRSQL:

    CALL INCREMENTSALARY(100001,’GOOD’)
    CALL statement complete.
    

  • CUSTNO 100001 balance updated successfully.

    CUSTNO   CUSTNAME                             BALANCE
    100,001   JAMES                               1,001.50
    100,003   BRYAN                               1,000.00
    100,002   JOHN                                  214.00
    100,004   RICHS                                 854.00
    100,005   RHONDA                             10,000.00
    
    

• External stored procedures
  • An external stored procedure is written by the user in one of the programming languages on the iSeries server. You can compile the host language programs to create *PGM objects. To create an external stored procedure, the source code for the host language must be compiled so that a program object is created. Then the CREATE PROCEDURE statement is used to tell the system where to find the program object that implements this stored procedure. The stored procedure registered in the following example returns the name of the supplier with the highest sales in a given month and year.The procedure is implemented in ILE RPG with embedded SQL:

    c/EXEC SQL
    c+ CREATE PROCEDURE HSALE
    c+ (IN YEAR INTEGER ,
    c+ IN MONTH INTEGER ,
    c+ OUT SUPPLIER_NAME CHAR(20) ,
    c+ OUT HSALE DECIMAL(11,2))
    c+ EXTERNAL NAME SPROCLIB.HSALES
    c+ LANGUAGE RPGLE
    c+ PARAMETER STYLE GENERAL
    c/END_EXEC
    
    

  • The following SQL CALL statement calls the external stored procedure, which returns a supplier name with the highest sales:

    c/EXEC SQL
    c+ CALL HSALE(:PARM1, :PARM2, :PARM3, :PARM4) c/END-EXEC