Calling an SQL Procedure

RPG can access data via stored procedures

The term stored procedure is not a reference to some esoteric object. A stored procedure is program that can be called from an application using an SQL CALL statement. Typically, developers tend to think of stored procedures in terms of SQL, RPG programs can serve as stored procedures as well.

The example below is an SQL stored procedure designed to return a subset of a database table.

Median Result Set


--  ------------------------------------------------------------------------ --
--  Proj: Median Premium Quote                                              --
--  Pgmr: S. CROY       Date: 2015-09-11                                    --
--  ------------------------------------------------------------------------ --
	
  CREATE PROCEDURE MEDIAN_RESULT_SET (OUT MEDPREM DECIMAL(7,2))

  LANGUAGE SQL
  DYNAMIC RESULT SETS 1

  BEGIN

  DECLARE V_NUMRECORDS INTEGER DEFAULT 1 ;
  DECLARE V_COUNTER INTEGER DEFAULT 0    ;

  DECLARE MEDIANPREMIUMC1 CURSOR FOR
  SELECT PMQPRM
  FROM PAPOMF
  ORDER BY PMQPRM ;

  DECLARE PREMIUMPOLICYC2 CURSOR WITH RETURN FOR
  SELECT POLNUM,POLTYP,PMQPRM
  FROM PAPOMF
  WHERE PMQPRM > MEDPREM
  ORDER BY PMQPRM;

  DECLARE EXIT HANDLER FOR NOT FOUND
  SET MEDPREM = 6666 ;

  SET MEDPREM = 0 ;

  SELECT COUNT(*) INTO V_NUMRECORDS FROM PAPOMF ;

  OPEN MEDIANPREMIUMC1 ;

  WHILE V_COUNTER < (V_NUMRECORDS / 2 + 1)

  DO FETCH MEDIANPREMIUMC1 INTO MEDPREM ;
  SET V_COUNTER = V_COUNTER + 1 ;
  END WHILE ;

  CLOSE MEDIANPREMIUMC1  ;

  OPEN  PREMIUMPOLICYC2   ;

  END       ;                     )

Create Syntax

>>-CREATE--+------------+--PROCEDURE--procedure-name------------>
           '-OR REPLACE-'                              

>--+-----------------------------------------+------------------>
   '-(--+-------------------------------+--)-'   
        | .-,-------------------------. |        
        | V                           | |        
        '-----parameter-declaration---+-'        

>--+-procedure-definition---------------+----------------------><
   '-WRAPPED--obfuscated-statement-text-'   

procedure-definition

|--option-list--+----------------------+--SQL-routine-body------|
                '-SET OPTION-statement-'                     

Read syntax diagramSkip visual syntax diagram
parameter-declaration

   .-IN----.                                                  
|--+-------+--parameter-name--data-type--+----------------+-----|
   +-OUT---+                             '-default-clause-'   
   '-INOUT-'                                                  

data-type

|--+-built-in-type------+---------------------------------------|
   +-distinct-type-name-+   
   '-array-type-name----'   

default-clause

|--DEFAULT--+-NULL-------------+--------------------------------|
            +-constant---------+   
            +-special-register-+   
            +-global-variable--+   
            '-(--expression--)-'   

Read syntax diagramSkip visual syntax diagram
option-list

   .-LANGUAGE SQL-. [1]  .-NOT DETERMINISTIC-.   
|--+--------------+------+-------------------+------------------>
                         '-DETERMINISTIC-----'   

   .-MODIFIES SQL DATA-.  .-CALLED ON NULL INPUT .   
>--+-------------------+--+----------------------+-------------->
   +-READS SQL DATA----+                             
   '-CONTAINS SQL------'                             

   .-INHERIT SPECIAL REGISTERS-.   
>--+---------------------------+-------------------------------->

   .-DYNAMIC RESULT SETS--0-------.   
>--+------------------------------+----------------------------->
   '-DYNAMIC RESULT SETS--integer-'   

>--+-------------------------+--+---------------------+--------->
   '-SPECIFIC--specific-name-'  +-DISALLOW DEBUG MODE-+   
                                +-ALLOW DEBUG MODE----+   
                                '-DISABLE DEBUG MODE--'   

   .-FENCED-----.  .-PROGRAM TYPE MAIN-.   
>--+------------+--+-------------------+------------------------>
   '-NOT FENCED-'  '-PROGRAM TYPE SUB--'   

   .-OLD SAVEPOINT LEVEL-.  .-COMMIT ON RETURN NO--.   
>--+---------------------+--+----------------------+------------>
   '-NEW SAVEPOINT LEVEL-'  '-COMMIT ON RETURN YES-'   

>--+---------------------------------------------------------------+--|
   |                               .-DEFAULT---------------------. |   
   '-CONCURRENT ACCESS RESOLUTION--+-+-USE CURRENTLY COMMITTED-+-+-'   
                                   | '-U-----------------------' |     
                                   '-+-WAIT FOR OUTCOME-+--------'     
                                     '-W----------------'