Example: SQL statements in PL/I programs
This example program is written in the PL/I programming language.
Note: By using the code examples, you agree to the terms
of the Code license and disclaimer information.
xxxxST1 VxRxMx yymmdd Create SQL PL/I Program PLIEX 08/06/07 12:53:36 Page 1
Source type...............PLI
Program name..............CORPDATA/PLIEX
Source file...............CORPDATA/SRC
Member....................PLIEX
To source file............QTEMP/QSQLTEMP
Options...................*SRC *XREF
Target release............VxRxMx
INCLUDE file..............*SRCFILE
Commit....................*CHG
Allow copy of data........*YES
Close SQL cursor..........*ENDPGM
Allow blocking............*READ
Delay PREPARE.............*NO
Generation level..........10
Margins...................*SRCFILE
Printer file..............*LIBL/QSYSPRT
Date format...............*JOB
Date separator............*JOB
Time format...............*HMS
Time separator ...........*JOB
Replace...................*YES
Relational database.......*LOCAL
User .....................*CURRENT
RDB connect method........*DUW
Default collection........*NONE
Dynamic default
collection..............*NO
Package name..............*PGMLIB/*PGM
Path......................*NAMING
SQL rules.................*DB2
User profile..............*NAMING
Dynamic user profile......*USER
Sort sequence.............*JOB
Language ID...............*JOB
IBM SQL flagging..........*NOFLAG
ANS flagging..............*NONE
Text......................*SRCMBRTXT
Source file CCSID.........65535
Job CCSID.................65535
Decimal result options:
Maximum precision.......31
Maximum scale...........31
Minimum divide scale....0
DECFLOAT rounding mode....*HALFEVEN
Compiler options..........*NONE
Source member changed on 07/01/96 12:53:08
xxxxST1 VxRxMx yymmdd Create SQL PL/I Program PLIEX 08/06/07 12:53:36 Page 2
Record *...+... 1 ...+... 2 ...+... 3 ...+... 4 ...+... 5 ...+... 6 ...+... 7 ...+... 8 SEQNBR Last change
1 /* A sample program that updates the salaries for those employees */ 100
2 /* whose current commission total is greater than or equal to the */ 200
3 /* value of COMMISSION. The salaries of those who qualify are */ 300
4 /* increased by the value of PERCENTAGE, retroactive to RAISE_DATE. */ 400
5 /* A report is generated showing the projects that these employees */ 500
6 /* have contributed to, ordered by project number and employee ID. */ 600
7 /* A second report shows each project having an end date occurring */ 700
8 /* after RAISE_DATE (that is, those projects potentially affected */ 800
9 /* by the retroactive raises) with its total salary expenses and a */ 900
10 /* count of employees who contributed to the project. */ 1000
11 /*********************************************************************/ 1100
12 1200
13 1300
14 PLIEX: PROC; 1400
15 1500
16 DCL RAISE_DATE CHAR(10); 1600
17 DCL WORK_DAYS FIXED BIN(15); 1700
18 DCL COMMISSION FIXED DECIMAL(8,2); 1800
19 DCL PERCENTAGE FIXED DECIMAL(5,2); 1900
20 2000
21 /* File declaration for sysprint */ 2100
22 DCL SYSPRINT FILE EXTERNAL OUTPUT STREAM PRINT; 2200
23 2300
24 /* Structure for report 1 */ 2400
25 DCL 1 RPT1, 2500
26 1%INCLUDE PROJECT (PROJECT, RECORD,,COMMA); 2600
27 15 EMPNO CHAR(6), 2700
28 15 NAME CHAR(30), 2800
29 15 SALARY FIXED DECIMAL(8,2); 2900
30 3000
31 /* Structure for report 2 */ 3100
32 DCL 1 RPT2, 3200
33 15 PROJNO CHAR(6), 3300
34 15 PROJECT_NAME CHAR(36), 3400
35 15 EMPLOYEE_COUNT FIXED BIN(15), 3500
36 15 TOTL_PROJ_COST FIXED DECIMAL(10,2); 3600
37 3700
38 2 EXEC SQL INCLUDE SQLCA; 3800
39 3900
40 COMMISSION = 2000.00; 4000
41 PERCENTAGE = 1.04; 4100
42 RAISE_DATE = '1982-06-01'; 4200
43 WORK_DAYS = 253; 4300
44 OPEN FILE(SYSPRINT); 4400
45 4500
46 /* Update the selected employees' salaries by the new percentage. */ 4600
47 /* If an error occurs during the update, roll back the changes. */ 4700
48 3 EXEC SQL WHENEVER SQLERROR GO TO UPDATE_ERROR; 4800
49 4 EXEC SQL 4900
50 UPDATE CORPDATA/EMPLOYEE 5000
51 SET SALARY = SALARY * :PERCENTAGE 5100
52 WHERE COMM >= :COMMISSION ; 5200
53 5300
54 /* Commit changes */ 5400
55 5 EXEC SQL 5500
56 COMMIT; 5600
57 EXEC SQL WHENEVER SQLERROR GO TO REPORT_ERROR; 5700
58 5800
xxxxST1 VxRxMx yymmdd Create SQL PL/I Program PLIEX 08/06/07 12:53:36 Page 3
Record *...+... 1 ...+... 2 ...+... 3 ...+... 4 ...+... 5 ...+... 6 ...+... 7 ...+... 8 SEQNBR Last change
59 /* Report the updated statistics for each project supported by one */ 5900
60 /* of the selected employees. */ 6000
61 6100
62 /* Write out the header for Report 1 */ 6200
63 put file(sysprint) 6300
64 edit('REPORT OF PROJECTS AFFECTED BY EMPLOYEE RAISES') 6400
65 (col(22),a); 6500
66 put file(sysprint) 6600
67 edit('PROJECT','EMPID','EMPLOYEE NAME','SALARY') 6700
68 (skip(2),col(1),a,col(10),a,col(20),a,col(55),a); 6800
69 6900
70 6 exec sql 7000
71 declare c1 cursor for 7100
72 select DISTINCT projno, EMPPROJACT.empno, 7200
73 lastname||', '||firstnme, salary 7300
74 from CORPDATA/EMPPROJACT, CORPDATA/EMPLOYEE 7400
75 where EMPPROJACT.empno = EMPLOYEE.empno and 7500
76 comm >= :COMMISSION 7600
77 order by projno, empno; 7700
78 7 EXEC SQL 7800
79 OPEN C1; 7900
80 8000
81 /* Fetch and write the rows to SYSPRINT */ 8100
82 8 EXEC SQL WHENEVER NOT FOUND GO TO DONE1; 8200
83 8300
84 DO UNTIL (SQLCODE ^= 0); 8400
85 9 EXEC SQL 8500
86 FETCH C1 INTO :RPT1.PROJNO, :rpt1.EMPNO, :RPT1.NAME, 8600
87 :RPT1.SALARY; 8700
88 PUT FILE(SYSPRINT) 8800
89 EDIT(RPT1.PROJNO,RPT1.EMPNO,RPT1.NAME,RPT1.SALARY) 8900
90 (SKIP,COL(1),A,COL(10),A,COL(20),A,COL(54),F(8,2)); 9000
91 END; 9100
92 9200
93 DONE1: 9300
94 10 EXEC SQL 9400
95 CLOSE C1; 9500
96 9600
97 /* For all projects ending at a date later than 'raise_date' */ 9700
98 /* (that is, those projects potentially affected by the salary */ 9800
99 /* raises), generate a report containing the project number, */ 9900
100 /* project name, the count of employees participating in the */ 10000
101 /* project, and the total salary cost of the project. */ 10100
102 10200
103 /* Write out the header for Report 2 */ 10300
104 PUT FILE(SYSPRINT) EDIT('ACCUMULATED STATISTICS BY PROJECT') 10400
105 (SKIP(3),COL(22),A); 10500
106 PUT FILE(SYSPRINT) 10600
107 EDIT('PROJECT','NUMBER OF','TOTAL') 10700
108 (SKIP(2),COL(1),A,COL(48),A,COL(63),A); 10800
109 PUT FILE(SYSPRINT) 10900
110 EDIT('NUMBER','PROJECT NAME','EMPLOYEES','COST') 11000
111 (SKIP,COL(1),A,COL(10),A,COL(48),A,COL(63),A,SKIP); 11100
112 11200
xxxxST1 VxRxMx yymmdd Create SQL PL/I Program PLIEX 08/06/07 12:53:36 Page 4
Record *...+... 1 ...+... 2 ...+... 3 ...+... 4 ...+... 5 ...+... 6 ...+... 7 ...+... 8 SEQNBR Last change
113 11 EXEC SQL 11300
114 DECLARE C2 CURSOR FOR 11400
115 SELECT EMPPROJACT.PROJNO, PROJNAME, COUNT(*), 11500
116 SUM( (DAYS(EMENDATE) - DAYS(EMSTDATE)) * EMPTIME * 11600
117 DECIMAL(( SALARY / :WORK_DAYS ),8,2) ) 11700
118 FROM CORPDATA/EMPPROJACT, CORPDATA/PROJECT, CORPDATA/EMPLOYEE 11800
119 WHERE EMPPROJACT.PROJNO=PROJECT.PROJNO AND 11900
120 EMPPROJACT.EMPNO =EMPLOYEE.EMPNO AND 12000
121 PRENDATE > :RAISE_DATE 12100
122 GROUP BY EMPPROJACT.PROJNO, PROJNAME 12200
123 ORDER BY 1; 12300
124 EXEC SQL 12400
125 OPEN C2; 12500
126 12600
127 /* Fetch and write the rows to SYSPRINT */ 12700
128 EXEC SQL WHENEVER NOT FOUND GO TO DONE2; 12800
129 12900
130 DO UNTIL (SQLCODE ^= 0); 13000
131 12 EXEC SQL 13100
132 FETCH C2 INTO :RPT2; 13200
133 PUT FILE(SYSPRINT) 13300
134 EDIT(RPT2.PROJNO,RPT2.PROJECT_NAME,EMPLOYEE_COUNT, 13400
135 TOTL_PROJ_COST) 13500
136 (SKIP,COL(1),A,COL(10),A,COL(50),F(4),COL(62),F(8,2)); 13600
137 END; 13700
138 13800
139 DONE2: 13900
140 EXEC SQL 14000
141 CLOSE C2; 14100
142 GO TO FINISHED; 14200
143 14300
144 /* Error occurred while updating table. Inform user and roll back */ 14400
145 /* changes. */ 14500
146 UPDATE_ERROR: 14600
147 13 EXEC SQL WHENEVER SQLERROR CONTINUE; 14700
148 PUT FILE(SYSPRINT) EDIT('*** ERROR Occurred while updating table.'|| 14800
149 ' SQLCODE=',SQLCODE)(A,F(5)); 14900
150 14 EXEC SQL 15000
151 ROLLBACK; 15100
152 GO TO FINISHED; 15200
153 15300
154 /* Error occurred while generating reports. Inform user and exit. */ 15400
155 REPORT_ERROR: 15500
156 PUT FILE(SYSPRINT) EDIT('*** ERROR Occurred while generating '|| 15600
157 'reports. SQLCODE=',SQLCODE)(A,F(5)); 15700
158 GO TO FINISHED; 15800
159 15900
160 /* All done */ 16000
161 FINISHED: 16100
162 CLOSE FILE(SYSPRINT); 16200
163 RETURN; 16300
164 16400
165 END PLIEX; 16500
* * * * * E N D O F S O U R C E * * * * *
xxxxST1 VxRxMx yymmdd Create SQL PL/I Program PLIEX 08/06/07 12:53:36 Page 5
CROSS REFERENCE
Data Names Define Reference
ACTNO 74 SMALL INTEGER PRECISION(4,0) COLUMN (NOT NULL) IN CORPDATA.EMPPROJACT
BIRTHDATE 74 DATE(10) COLUMN IN CORPDATA.EMPLOYEE
BONUS 74 DECIMAL(9,2) COLUMN IN CORPDATA.EMPLOYEE
COMM **** COLUMN
52 76
COMM 74 DECIMAL(9,2) COLUMN IN CORPDATA.EMPLOYEE
COMMISSION 18 DECIMAL(8,2)
52 76
CORPDATA **** SCHEMA
50 74 74 118 118 118
C1 71 CURSOR
79 86 95
C2 114 CURSOR
125 132 141
DEPTNO 26 CHARACTER(3) IN RPT1
DEPTNO 118 CHARACTER(3) COLUMN (NOT NULL) IN CORPDATA.PROJECT
DONE1 **** LABEL
82
DONE2 **** LABEL
128
EDLEVEL 74 SMALL INTEGER PRECISION(4,0) COLUMN (NOT NULL) IN CORPDATA.EMPLOYEE
EMENDATE 74 DATE(10) COLUMN IN CORPDATA.EMPPROJACT
EMENDATE **** COLUMN
116
EMPLOYEE **** TABLE IN CORPDATA
50 74 118
EMPLOYEE **** TABLE
75 120
EMPLOYEE_COUNT 35 SMALL INTEGER PRECISION(4,0) IN RPT2
EMPNO 27 CHARACTER(6) IN RPT1
86
EMPNO **** COLUMN IN EMPPROJACT
72 75 77 120
EMPNO **** COLUMN IN EMPLOYEE
75 120
EMPNO 74 CHARACTER(6) COLUMN (NOT NULL) IN CORPDATA.EMPPROJACT
EMPNO 74 CHARACTER(6) COLUMN (NOT NULL) IN CORPDATA.EMPLOYEE
EMPPROJACT **** TABLE
72 75 115 119 120 122
EMPPROJACT **** TABLE IN CORPDATA
74 118
EMPTIME 74 DECIMAL(5,2) COLUMN IN CORPDATA.EMPPROJACT
EMPTIME **** COLUMN
116
EMSTDATE 74 DATE(10) COLUMN IN CORPDATA.EMPPROJACT
EMSTDATE **** COLUMN
116
FIRSTNME **** COLUMN
73
FIRSTNME 74 VARCHAR(12) COLUMN (NOT NULL) IN CORPDATA.EMPLOYEE
HIREDATE 74 DATE(10) COLUMN IN CORPDATA.EMPLOYEE
JOB 74 CHARACTER(8) COLUMN IN CORPDATA.EMPLOYEE
LASTNAME **** COLUMN
73
LASTNAME 74 VARCHAR(15) COLUMN (NOT NULL) IN CORPDATA.EMPLOYEE
MAJPROJ 26 CHARACTER(6) IN RPT1
MAJPROJ 118 CHARACTER(6) COLUMN IN CORPDATA.PROJECT
MIDINIT 74 CHARACTER(1) COLUMN (NOT NULL) IN CORPDATA.EMPLOYEE
NAME 28 CHARACTER(30) IN RPT1
86
PERCENTAGE 19 DECIMAL(5,2)
51
PHONENO 74 CHARACTER(4) COLUMN IN CORPDATA.EMPLOYEE
xxxxST1 VxRxMx yymmdd Create SQL PL/I Program PLIEX 08/06/07 12:53:36 Page 6
CROSS REFERENCE
PRENDATE 26 DATE(10) IN RPT1
PRENDATE **** COLUMN
121
PRENDATE 118 DATE(10) COLUMN IN CORPDATA.PROJECT
PROJECT **** TABLE IN CORPDATA
118
PROJECT **** TABLE
119
PROJECT_NAME 34 CHARACTER(36) IN RPT2
PROJNAME 26 VARCHAR(24) IN RPT1
PROJNAME **** COLUMN
115 122
PROJNAME 118 VARCHAR(24) COLUMN (NOT NULL) IN CORPDATA.PROJECT
PROJNO 26 CHARACTER(6) IN RPT1
86
PROJNO 33 CHARACTER(6) IN RPT2
PROJNO **** COLUMN
72 77
PROJNO 74 CHARACTER(6) COLUMN (NOT NULL) IN CORPDATA.EMPPROJACT
PROJNO **** COLUMN IN EMPPROJACT
115 119 122
PROJNO **** COLUMN IN PROJECT
119
PROJNO 118 CHARACTER(6) COLUMN (NOT NULL) IN CORPDATA.PROJECT
PRSTAFF 26 DECIMAL(5,2) IN RPT1
PRSTAFF 118 DECIMAL(5,2) COLUMN IN CORPDATA.PROJECT
PRSTDATE 26 DATE(10) IN RPT1
PRSTDATE 118 DATE(10) COLUMN IN CORPDATA.PROJECT
RAISE_DATE 16 CHARACTER(10)
121
REPORT_ERROR **** LABEL
57
RESPEMP 26 CHARACTER(6) IN RPT1
RESPEMP 118 CHARACTER(6) COLUMN (NOT NULL) IN CORPDATA.PROJECT
RPT1 25 STRUCTURE
RPT2 32 STRUCTURE
132
SALARY 29 DECIMAL(8,2) IN RPT1
87
SALARY **** COLUMN
51 51 73 117
SALARY 74 DECIMAL(9,2) COLUMN IN CORPDATA.EMPLOYEE
SEX 74 CHARACTER(1) COLUMN IN CORPDATA.EMPLOYEE
SYSPRINT 22
TOTL_PROJ_COST 36 DECIMAL(10,2) IN RPT2
UPDATE_ERROR **** LABEL
48
WORK_DAYS 17 SMALL INTEGER PRECISION(4,0)
117
WORKDEPT 74 CHARACTER(3) COLUMN IN CORPDATA.EMPLOYEE
No errors found in source
165 Source records processed
* * * * * E N D O F L I S T I N G * * * * *