PL/I - DB2 Stored Procedure - Sample Program

The below is DB2 logic we are putting it in a Subprogram (Stored procedure). Whenever we need we USE CALL procname  in the main program => to get this DB2 code.

1. /*
2.  * PLI Stored Procedure SAMPLE.PLISAMP
3.  * @param Action
4.  * @param City
5.  * @param Country
6.  * @param Response
7.  */
8. 
   
9. /*Reference: IBM */
10. 
    
11. PLISAMP : Procedure ( Action   ,
12.                        City     ,
13.                        Country  ,
14.                        Response ) options (main);
15. 
    
16.  dcl Action   CHAR  (8) VAR;
17.  dcl City     CHAR (32) VAR;
18.  dcl Country  CHAR (32) VAR;
19.  dcl Response CHAR (80) VAR;
20. 
    
21.  dcl Sqlcode_Pic PIC'SSS999';
22. 
    
23.  EXEC SQL INCLUDE SQLCA;
24. 
    
25.  Action=TRANSLATE(Action,'ABCDEFGHIJKLMNOPQRSTUVWXYZ',
26.                          'abcdefghijklmnopqrstuvwxyz');
27. 
    
28.  select (Action);
29.    when ('S','SELECT') do;
30.      Select;
31.        When (City='' & Country='') do;
32.          EXEC SQL
33.            DECLARE C1 CURSOR WITH RETURN WITH HOLD FOR
34.              SELECT * FROM SAMPLE.CITYTABLE
35.          ;
36.          EXEC SQL
37.            OPEN C1
38.          ;
39.        end;
40.        When (City='') do;
41.          EXEC SQL
42.            DECLARE C2 CURSOR WITH RETURN WITH HOLD FOR
43.              SELECT * FROM SAMPLE.CITYTABLE
44.                WHERE COUNTRY=:COUNTRY
45.          ;
46.          EXEC SQL
47.            OPEN C2
48.          ;
49.        end;
50.        When (Country='') do;
51.          EXEC SQL
52.            DECLARE C3 CURSOR WITH RETURN WITH HOLD FOR
53.              SELECT * FROM SAMPLE.CITYTABLE
54.                WHERE CITY=:CITY
55.          ;
56.          EXEC SQL
57.            OPEN C3
58.          ;
59.        end;
60.        Otherwise do;
61.          EXEC SQL
62.            DECLARE C4 CURSOR WITH RETURN WITH HOLD FOR
63.              SELECT * FROM SAMPLE.CITYTABLE
64.                WHERE CITY=:CITY AND COUNTRY=:COUNTRY
65.          ;
66.          EXEC SQL
67.            OPEN C4
68.          ;
69.        end;
70.      end;
71.      if Sqlcode=0 then Response='Select successful';
72.      else do;
73.        Sqlcode_Pic = Sqlcode;
74.        Response='Select unsuccessful: Sqlcode '||Sqlcode_Pic;
75.      end;
76.    end;
77.    when ('I','INSERT') do;
78.      Select;
79.        When (City='' & Country='')
80.          Response='Insert unsuccessful: City and Country not specified';
81.        When (City='')
82.          Response='Insert unsuccessful: City not specified';
83.        When (Country='')
84.          Response='Insert unsuccessful: Country not specified';
85.        Otherwise do;
86.          EXEC SQL
87.            INSERT INTO SAMPLE.CITYTABLE
88.              (CITY, COUNTRY) VALUES (:CITY, :COUNTRY)
89.          ;
90.          if Sqlcode=0 then Response='Insert successful';
91.          else do;
92.            Sqlcode_Pic = Sqlcode;
93.            Response='Insert unsuccessful: Sqlcode '||Sqlcode_Pic;
94.          end;
95.        end;
96.      end;
97.    end;
98.    when ('D','DELETE') do;
99.      Select;
100.        When (City='' & Country='')
101.          Response='Delete unsuccessful: City and Country not specified';
102.        When (City='')
103.          Response='Delete unsuccessful: City not specified';
104.        When (Country='')
105.          Response='Delete unsuccessful: Country not specified';
106.        Otherwise do;
107.          EXEC SQL
108.            DELETE FROM SAMPLE.CITYTABLE
109.              WHERE CITY=:CITY AND COUNTRY=:COUNTRY
110.          ;
111.          if Sqlcode=0 then Response='Delete successful';
112.          else do;
113.            Sqlcode_Pic = Sqlcode;
114.            Response='Delete unsuccessful: Sqlcode '||Sqlcode_Pic;
115.          end;
116.        end;
117.      end;
118.    end;
119.    otherwise Response='Action "'||Action||'" not supported';
120.  end;
121. 
     
122.  END PLISAMP;

POINTS:

NO-> END EXEC in PL/I