ViewVC Help
View File | Revision Log | Show Annotations | Download File | View Changeset | Root Listing
root/public/ibx/trunk/testsuite/resources/Test10.sql
(Generate patch)

Comparing ibx/trunk/testsuite/resources/Test10.sql (file contents):
Revision 348 by tony, Wed Oct 6 09:38:14 2021 UTC vs.
Revision 349 by tony, Mon Oct 18 08:39:40 2021 UTC

# Line 3 | Line 3 | SET AUTODDL ON;
3  
4   CREATE DATABASE 'inet://localhost//tmp/ibx-testsuite/testsuite1.fdb' PAGE_SIZE 8192
5  
6 < DEFAULT CHARACTER SET NONE;
6 > DEFAULT CHARACTER SET NONE;
7   COMMENT ON DATABASE  IS 'Employee Test DB';
8  
9   /* Domain definitions */
10  
11   CREATE DOMAIN ADDRESSLINE AS VARCHAR(30);
12 < CREATE DOMAIN BUDGET AS DECIMAL(12, 2)
12 > CREATE DOMAIN BUDGET AS DECIMAL(12,2)
13           DEFAULT 50000;
14   CREATE DOMAIN COUNTRYNAME AS VARCHAR(15);
15   CREATE DOMAIN CUSTNO AS INTEGER;
# Line 25 | Line 25 | CREATE DOMAIN PONUMBER AS CHAR(8);
25   CREATE DOMAIN PRODTYPE AS VARCHAR(12)
26           DEFAULT 'software' NOT NULL;
27   CREATE DOMAIN PROJNO AS CHAR(5);
28 < CREATE DOMAIN SALARY AS NUMERIC(10, 2)
28 > CREATE DOMAIN SALARY AS NUMERIC(10,2)
29           DEFAULT 0;
30  
31   /* Table: COUNTRIES, Owner: SYSDBA */
32  
33 < CREATE TABLE COUNTRIES
33 > CREATE TABLE COUNTRIES
34   (
35          COUNTRY VARCHAR(64) NOT NULL,
36          NONEU   SMALLINT,
# Line 43 | Line 43 | COMMENT ON COLUMN COUNTRIES.ISO2 IS 'Two
43  
44   /* Table: COUNTRY, Owner: SYSDBA */
45  
46 < CREATE TABLE COUNTRY
46 > CREATE TABLE COUNTRY
47   (
48          COUNTRY COUNTRYNAME NOT NULL,
49          CURRENCY        VARCHAR(10) NOT NULL,
# Line 52 | Line 52 | PRIMARY KEY (COUNTRY)
52  
53   /* Table: CUSTOMER, Owner: SYSDBA */
54  
55 < CREATE TABLE CUSTOMER
55 > CREATE TABLE CUSTOMER
56   (
57          CUST_NO CUSTNO NOT NULL,
58          CUSTOMER        VARCHAR(25) NOT NULL,
# Line 72 | Line 72 | PRIMARY KEY (CUST_NO)
72  
73   /* Table: DEPARTMENT, Owner: SYSDBA */
74  
75 < CREATE TABLE DEPARTMENT
75 > CREATE TABLE DEPARTMENT
76   (
77          DEPT_NO DEPTNO NOT NULL,
78          DEPARTMENT      VARCHAR(25) NOT NULL,
# Line 87 | Line 87 | PRIMARY KEY (DEPT_NO)
87  
88   /* Table: EMPLOYEE, Owner: SYSDBA */
89  
90 < CREATE TABLE EMPLOYEE
90 > CREATE TABLE EMPLOYEE
91   (
92          EMP_NO  EMPNO NOT NULL,
93          FIRST_NAME      FIRSTNAME NOT NULL,
# Line 105 | Line 105 | PRIMARY KEY (EMP_NO)
105  
106   /* Table: EMPLOYEE_PROJECT, Owner: SYSDBA */
107  
108 < CREATE TABLE EMPLOYEE_PROJECT
108 > CREATE TABLE EMPLOYEE_PROJECT
109   (
110          EMP_NO  EMPNO NOT NULL,
111          PROJ_ID PROJNO NOT NULL,
# Line 114 | Line 114 | PRIMARY KEY (EMP_NO, PROJ_ID)
114  
115   /* Table: FB$OUT_TABLE, Owner: SYSDBA */
116  
117 < CREATE GLOBAL TEMPORARY TABLE FB$OUT_TABLE
117 > CREATE GLOBAL TEMPORARY TABLE FB$OUT_TABLE
118   (
119          LINE_NUM        INTEGER,
120          CONTENT FB$OUT_TYPE
# Line 122 | Line 122 | CREATE GLOBAL TEMPORARY TABLE FB$OUT_TAB
122  
123   /* Table: ITEST, Owner: SYSDBA */
124  
125 < CREATE TABLE ITEST
125 > CREATE TABLE ITEST
126   (
127          "KEY"   INTEGER GENERATED BY DEFAULT AS IDENTITY (START WITH 0) NOT NULL,
128          SOMETEXT        VARCHAR(64),
# Line 131 | Line 131 | PRIMARY KEY ("KEY")
131  
132   /* Table: ITEST2, Owner: SYSDBA */
133  
134 < CREATE TABLE ITEST2
134 > CREATE TABLE ITEST2
135   (
136          "KEY"   INTEGER GENERATED BY DEFAULT AS IDENTITY (START WITH 0 INCREMENT BY 1) NOT NULL,
137          SOMETEXT        VARCHAR(64),
# Line 140 | Line 140 | PRIMARY KEY ("KEY")
140  
141   /* Table: JOB, Owner: SYSDBA */
142  
143 < CREATE TABLE JOB
143 > CREATE TABLE JOB
144   (
145          JOB_CODE        JOBCODE NOT NULL,
146          JOB_GRADE       JOBGRADE NOT NULL,
# Line 155 | Line 155 | PRIMARY KEY (JOB_CODE, JOB_GRADE, JOB_CO
155  
156   /* Table: PROJECT, Owner: SYSDBA */
157  
158 < CREATE TABLE PROJECT
158 > CREATE TABLE PROJECT
159   (
160          PROJ_ID PROJNO NOT NULL,
161          PROJ_NAME       VARCHAR(20) NOT NULL,
# Line 168 | Line 168 | PRIMARY KEY (PROJ_ID)
168  
169   /* Table: PROJ_DEPT_BUDGET, Owner: SYSDBA */
170  
171 < CREATE TABLE PROJ_DEPT_BUDGET
171 > CREATE TABLE PROJ_DEPT_BUDGET
172   (
173          FISCAL_YEAR     INTEGER NOT NULL,
174          PROJ_ID PROJNO NOT NULL,
# Line 180 | Line 180 | PRIMARY KEY (FISCAL_YEAR, PROJ_ID, DEPT_
180  
181   /* Table: SALARY_HISTORY, Owner: SYSDBA */
182  
183 < CREATE TABLE SALARY_HISTORY
183 > CREATE TABLE SALARY_HISTORY
184   (
185          EMP_NO  EMPNO NOT NULL,
186          CHANGE_DATE     TIMESTAMP DEFAULT 'NOW' NOT NULL,
# Line 193 | Line 193 | PRIMARY KEY (EMP_NO, CHANGE_DATE, UPDATE
193  
194   /* Table: SALES, Owner: SYSDBA */
195  
196 < CREATE TABLE SALES
196 > CREATE TABLE SALES
197   (
198          PO_NUMBER       PONUMBER NOT NULL,
199          CUST_NO CUSTNO NOT NULL,
# Line 204 | Line 204 | CREATE TABLE SALES
204          DATE_NEEDED     TIMESTAMP,
205          PAID    CHAR(1) DEFAULT 'n',
206          QTY_ORDERED     INTEGER DEFAULT 1 NOT NULL,
207 <        TOTAL_VALUE     DECIMAL(9, 2) NOT NULL,
207 >        TOTAL_VALUE     DECIMAL(9,2) NOT NULL,
208          DISCOUNT        FLOAT DEFAULT 0 NOT NULL,
209          ITEM_TYPE       PRODTYPE,
210          AGED COMPUTED BY (ship_date - order_date),
# Line 217 | Line 217 | DECLARE EXTERNAL FUNCTION ADDDAY
217   TIMESTAMP, INTEGER
218   RETURNS TIMESTAMP
219   ENTRY_POINT 'addDay' MODULE_NAME 'fbudf'
220 –
220   ;
221  
222  
223   DECLARE EXTERNAL FUNCTION I64TRUNCATE
224 < NUMERIC(18, 0) BY DESCRIPTOR, NUMERIC(18, 0) BY DESCRIPTOR
224 > NUMERIC(18,0) BY DESCRIPTOR, NUMERIC(18,0) BY DESCRIPTOR
225   RETURNS PARAMETER 2
226   ENTRY_POINT 'fbtruncate' MODULE_NAME 'fbudf'
228 –
227   ;
228  
229  
# Line 233 | Line 231 | DECLARE EXTERNAL FUNCTION TESTIT
231   TIMESTAMP, INTEGER NULL, CSTRING(10) CHARACTER SET NONE, CSTRING(10) CHARACTER SET NONE BY DESCRIPTOR
232   RETURNS CSTRING(32) CHARACTER SET NONE FREE_IT
233   ENTRY_POINT 'testit' MODULE_NAME 'fbudf'
236 –
234   ;
235  
236  
# Line 975 | Line 972 | ALTER SEQUENCE FB$OUT_SEQ RESTART WITH 1
972   /* View: DEPTLIST, Owner: SYSDBA */
973  
974   CREATE VIEW DEPTLIST (
975 <  EMP_NO,
976 <  FIRST_NAME,
977 <  LAST_NAME,
978 <  PHONE_EXT,
979 <  HIRE_DATE,
980 <  DEPT_NO,
981 <  JOB_CODE,
982 <  JOB_GRADE,
983 <  JOB_COUNTRY,
984 <  SALARY,
985 <  FULL_NAME,
986 <  DEPT_PATH,
975 >  EMP_NO,
976 >  FIRST_NAME,
977 >  LAST_NAME,
978 >  PHONE_EXT,
979 >  HIRE_DATE,
980 >  DEPT_NO,
981 >  JOB_CODE,
982 >  JOB_GRADE,
983 >  JOB_COUNTRY,
984 >  SALARY,
985 >  FULL_NAME,
986 >  DEPT_PATH,
987    DEPT_KEY_PATH,
988    "Payment Status"
989   ) AS
# Line 1003 | Line 1000 | JOIN Depts On D.HEAD_DEPT = Depts.DEPT_N
1000  
1001   Select distinct A.EMP_NO, A.FIRST_NAME, A.LAST_NAME, A.PHONE_EXT, A.HIRE_DATE, A.DEPT_NO, A.JOB_CODE,
1002   A.JOB_GRADE, A.JOB_COUNTRY, A.SALARY, A.FULL_NAME, D.DEPT_PATH, D.DEPT_KEY_PATH,
1003 < CASE When A.SALARY > 10000 then 'higher paid' else 'lower paid' End
1003 > CASE When A.SALARY > 10000 then 'higher paid' else 'lower paid' End
1004   From EMPLOYEE A
1005   JOIN Depts D On D.DEPT_NO = A.DEPT_NO
1006   ;
# Line 1011 | Line 1008 | JOIN Depts D On D.DEPT_NO = A.DEPT_NO
1008   /* View: PHONE_LIST, Owner: SYSDBA */
1009  
1010   CREATE VIEW PHONE_LIST (
1011 <  EMP_NO,
1012 <  FIRST_NAME,
1013 <  LAST_NAME,
1014 <  PHONE_EXT,
1015 <  LOCATION,
1011 >  EMP_NO,
1012 >  FIRST_NAME,
1013 >  LAST_NAME,
1014 >  PHONE_EXT,
1015 >  LOCATION,
1016    PHONE_NO
1017   ) AS
1018   SELECT
# Line 1117 | Line 1114 | CREATE EXCEPTION REASSIGN_SALES 'Reassig
1114   CREATE EXCEPTION UNKNOWN_EMP_ID 'Invalid employee number or project id.';
1115   COMMIT WORK;
1116   SET AUTODDL OFF;
1117 < SET TERM ^ ;
1117 > SET TERM ^;
1118  
1119   /* Package Definitions */
1120  
1121 < CREATE PACKAGE FB$OUT                                                        
1121 > CREATE PACKAGE FB$OUT
1122   AS
1126 –
1123   begin
1124          procedure enable;
1125          procedure disable;
# Line 1135 | Line 1131 | begin
1131   end
1132   ^
1133  
1134 < CREATE PACKAGE SELECT$TEST                                                    
1134 > CREATE PACKAGE SELECT$TEST
1135   AS
1140 –
1136   Begin
1137    Procedure ShowItems(IPARAM Integer) RETURNS (OutParam integer);
1138   End
# Line 1148 | Line 1143 | COMMIT WORK;
1143   SET AUTODDL ON;
1144   COMMIT WORK;
1145   SET AUTODDL OFF;
1146 < SET TERM ^ ;
1146 > SET TERM ^;
1147  
1148   /* Stored procedures Definitions*/
1149  
1150 < CREATE PROCEDURE SHOW_LANGS
1150 > CREATE PROCEDURE SHOW_LANGS
1151   (
1152    CODE VARCHAR(5) CHARACTER SET NONE,
1153    GRADE SMALLINT,
# Line 1164 | Line 1159 | RETURNS
1159   )
1160   AS
1161   BEGIN SUSPEND; EXIT; END
1167 –
1162   ^
1163  
1164 < CREATE PROCEDURE ADD_EMP_PROJ
1164 > CREATE PROCEDURE ADD_EMP_PROJ
1165   (
1166    EMP_NO SMALLINT,
1167    PROJ_ID CHAR(5) CHARACTER SET NONE
1168   )
1169   AS
1170   BEGIN EXIT; END
1177 –
1171   ^
1172  
1173 < CREATE PROCEDURE ALL_LANGS
1173 > CREATE PROCEDURE ALL_LANGS
1174   RETURNS
1175   (
1176    CODE VARCHAR(5) CHARACTER SET NONE,
# Line 1187 | Line 1180 | RETURNS
1180   )
1181   AS
1182   BEGIN SUSPEND; EXIT; END
1190 –
1183   ^
1184  
1185 < CREATE PROCEDURE DELETE_EMPLOYEE
1185 > CREATE PROCEDURE DELETE_EMPLOYEE
1186   (
1187    EMP_NUM INTEGER
1188   )
1189   AS
1190   BEGIN EXIT; END
1199 –
1191   ^
1192  
1193 < CREATE PROCEDURE DEPT_BUDGET
1193 > CREATE PROCEDURE DEPT_BUDGET
1194   (
1195    DNO CHAR(3) CHARACTER SET NONE
1196   )
1197   RETURNS
1198   (
1199 <  TOT DECIMAL(12, 2)
1199 >  TOT DECIMAL(12,2)
1200   )
1201   AS
1202   BEGIN SUSPEND; EXIT; END
1212 –
1203   ^
1204  
1205   CREATE PROCEDURE EMPLOYEE_PAY_STATUS
1206   (
1207 <  EMP_NO SMALLINT
1207 >  EMP_NUM SMALLINT
1208   )
1209   RETURNS
1210   (
1211 <  PAY_STATUS VARCHAR(6) CHARACTER SET NONE
1211 >  PAY_STATUS VARCHAR(6) CHARACTER SET NONE,
1212 >  SALARY NUMERIC(10,2)
1213   )
1214   AS
1215   BEGIN EXIT; END
1225 –
1216   ^
1217  
1218 <
1229 < CREATE PROCEDURE GET_EMP_PROJ
1218 > CREATE PROCEDURE GET_EMP_PROJ
1219   (
1220    EMP_NO SMALLINT
1221   )
# Line 1236 | Line 1225 | RETURNS
1225   )
1226   AS
1227   BEGIN SUSPEND; EXIT; END
1239 –
1228   ^
1229  
1230 < CREATE PROCEDURE "Has Space"
1230 > CREATE PROCEDURE "Has Space"
1231   (
1232    ARG1 INTEGER
1233   )
1234   AS
1235   BEGIN EXIT; END
1248 –
1236   ^
1237  
1238 < CREATE PROCEDURE MAIL_LABEL
1238 > CREATE PROCEDURE MAIL_LABEL
1239   (
1240    CUST_NO INTEGER
1241   )
# Line 1263 | Line 1250 | RETURNS
1250   )
1251   AS
1252   BEGIN SUSPEND; EXIT; END
1266 –
1253   ^
1254  
1255 < CREATE PROCEDURE ORG_CHART
1255 > CREATE PROCEDURE ORG_CHART
1256   RETURNS
1257   (
1258    HEAD_DEPT CHAR(25) CHARACTER SET NONE,
# Line 1277 | Line 1263 | RETURNS
1263   )
1264   AS
1265   BEGIN SUSPEND; EXIT; END
1280 –
1266   ^
1267  
1268 < CREATE PROCEDURE SHIP_ORDER
1268 > CREATE PROCEDURE SHIP_ORDER
1269   (
1270    PO_NUM CHAR(8) CHARACTER SET NONE
1271   )
1272   AS
1273   BEGIN EXIT; END
1289 –
1274   ^
1275  
1276 < CREATE PROCEDURE SUB_TOT_BUDGET
1276 > CREATE PROCEDURE SUB_TOT_BUDGET
1277   (
1278    HEAD_DEPT CHAR(3) CHARACTER SET NONE
1279   )
1280   RETURNS
1281   (
1282 <  TOT_BUDGET DECIMAL(12, 2),
1283 <  AVG_BUDGET DECIMAL(12, 2),
1284 <  MIN_BUDGET DECIMAL(12, 2),
1285 <  MAX_BUDGET DECIMAL(12, 2)
1282 >  TOT_BUDGET DECIMAL(12,2),
1283 >  AVG_BUDGET DECIMAL(12,2),
1284 >  MIN_BUDGET DECIMAL(12,2),
1285 >  MAX_BUDGET DECIMAL(12,2)
1286   )
1287   AS
1288   BEGIN SUSPEND; EXIT; END
1305 –
1289   ^
1290  
1291 < CREATE PROCEDURE "UC SPACE"
1291 > CREATE PROCEDURE "UC SPACE"
1292   (
1293    ARG1 INTEGER
1294   )
1295   AS
1296   BEGIN EXIT; END
1314 –
1297   ^
1298  
1299 < CREATE PROCEDURE "iCASE"
1299 > CREATE PROCEDURE "iCASE"
1300   AS
1301   BEGIN EXIT; END
1320 –
1302   ^
1303  
1304   SET TERM ;^
# Line 1325 | Line 1306 | COMMIT WORK;
1306   SET AUTODDL ON;
1307   COMMIT WORK;
1308   SET AUTODDL OFF;
1309 < SET TERM ^ ;
1309 > SET TERM ^;
1310  
1311   /* Stored Function declarations */
1312  
1313   CREATE FUNCTION F (X INTEGER)
1314   RETURNS INTEGER
1315   AS BEGIN END
1335 –
1316   ^
1317  
1338 –
1318   SET TERM ;^
1319   COMMIT WORK;
1320   SET AUTODDL ON;
1321 < SET TERM ^ ;
1321 > SET TERM ^;
1322  
1323  
1324   /* Triggers only will work for SQL triggers */
# Line 1350 | Line 1329 | as
1329   begin
1330   End
1331   ^
1332 +
1333   CREATE TRIGGER "BEFORE CREATE TABLE"
1334   ACTIVE BEFORE CREATE TABLE POSITION 0
1335   AS BEGIN END
1336   ^
1337 +
1338   CREATE TRIGGER "AFTER CREATE TABLE"
1339   ACTIVE AFTER CREATE TABLE POSITION 0
1340   AS BEGIN END
1341   ^
1342 +
1343   CREATE TRIGGER "BEFORE ALTER TABLE"
1344   ACTIVE BEFORE ALTER TABLE POSITION 0
1345   AS BEGIN END
1346   ^
1347 +
1348   CREATE TRIGGER "AFTER ALTER TABLE"
1349   ACTIVE AFTER ALTER TABLE POSITION 0
1350   AS BEGIN END
1351   ^
1352 +
1353   CREATE TRIGGER "AFTER CREATE/Alter TABLE"
1354   ACTIVE AFTER CREATE TABLE OR ALTER TABLE POSITION 0
1355   AS BEGIN END
1356   ^
1357 +
1358   CREATE TRIGGER "BEFORE DROP TABLE"
1359   ACTIVE BEFORE Drop TABLE POSITION 0
1360   AS BEGIN END
1361   ^
1362 +
1363   CREATE TRIGGER "AFTER DROP TABLE"
1364   ACTIVE AFTER Drop TABLE POSITION 0
1365   AS BEGIN END
1366   ^
1367 +
1368   CREATE TRIGGER "BEFORE CREATE PROCEDURE"
1369   ACTIVE BEFORE CREATE PROCEDURE POSITION 0
1370   AS BEGIN END
1371   ^
1372 +
1373   CREATE TRIGGER "AFTER CREATE PROCEDURE"
1374   ACTIVE AFTER CREATE PROCEDURE POSITION 0
1375   AS BEGIN END
1376   ^
1377 +
1378   CREATE TRIGGER "BEFORE ALTER PROCEDURE"
1379   ACTIVE BEFORE ALTER PROCEDURE POSITION 0
1380   AS BEGIN END
1381   ^
1382 +
1383   CREATE TRIGGER "AFTER ALTER PROCEDURE"
1384   ACTIVE AFTER ALTER PROCEDURE POSITION 0
1385   AS BEGIN END
1386   ^
1387 +
1388   CREATE TRIGGER "BEFORE DROP PROCEDURE"
1389   ACTIVE BEFORE Drop PROCEDURE POSITION 0
1390   AS BEGIN END
1391   ^
1392 +
1393   CREATE TRIGGER "AFTER DROP PROCEDURE"
1394   ACTIVE AFTER Drop PROCEDURE POSITION 0
1395   AS BEGIN END
1396   ^
1397 +
1398   CREATE TRIGGER "BEFORE CREATE FUNCTION"
1399   ACTIVE BEFORE CREATE FUNCTION POSITION 0
1400   AS BEGIN END
1401   ^
1402 +
1403   CREATE TRIGGER "AFTER CREATE FUNCTION"
1404   ACTIVE AFTER CREATE FUNCTION POSITION 0
1405   AS BEGIN END
1406   ^
1407 +
1408   CREATE TRIGGER "BEFORE ALTER FUNCTION"
1409   ACTIVE BEFORE ALTER FUNCTION POSITION 0
1410   AS BEGIN END
1411   ^
1412 +
1413   CREATE TRIGGER "AFTER ALTER FUNCTION"
1414   ACTIVE AFTER ALTER FUNCTION POSITION 0
1415   AS BEGIN END
1416   ^
1417 +
1418   CREATE TRIGGER "BEFORE DROP FUNCTION"
1419   ACTIVE BEFORE Drop FUNCTION POSITION 0
1420   AS BEGIN END
1421   ^
1422 +
1423   CREATE TRIGGER "AFTER DROP FUNCTION"
1424   ACTIVE AFTER Drop FUNCTION POSITION 0
1425   AS BEGIN END
1426   ^
1427 +
1428   CREATE TRIGGER "BEFORE CREATE TRIGGER"
1429   ACTIVE BEFORE CREATE TRIGGER POSITION 0
1430   AS BEGIN END
1431   ^
1432 +
1433   CREATE TRIGGER "AFTER CREATE TRIGGER"
1434   ACTIVE AFTER CREATE TRIGGER POSITION 0
1435   AS BEGIN END
1436   ^
1437 +
1438   CREATE TRIGGER "BEFORE ALTER TRIGGER"
1439   ACTIVE BEFORE ALTER TRIGGER POSITION 0
1440   AS BEGIN END
1441   ^
1442 +
1443   CREATE TRIGGER "AFTER ALTER TRIGGER"
1444   ACTIVE AFTER ALTER TRIGGER POSITION 0
1445   AS BEGIN END
1446   ^
1447 +
1448   CREATE TRIGGER "BEFORE DROP TRIGGER"
1449   ACTIVE BEFORE Drop TRIGGER POSITION 0
1450   AS BEGIN END
1451   ^
1452 +
1453   CREATE TRIGGER "AFTER DROP TRIGGER"
1454   ACTIVE AFTER Drop TRIGGER POSITION 0
1455   AS BEGIN END
1456   ^
1457 +
1458   CREATE TRIGGER "BEFORE CREATE EXCEPTION"
1459   ACTIVE BEFORE CREATE EXCEPTION POSITION 0
1460   AS BEGIN END
1461   ^
1462 +
1463   CREATE TRIGGER "AFTER CREATE EXCEPTION"
1464   ACTIVE AFTER CREATE EXCEPTION POSITION 0
1465   AS BEGIN END
1466   ^
1467 +
1468   CREATE TRIGGER "BEFORE ALTER EXCEPTION"
1469   ACTIVE BEFORE ALTER EXCEPTION POSITION 0
1470   AS BEGIN END
1471   ^
1472 +
1473   CREATE TRIGGER "AFTER ALTER EXCEPTION"
1474   ACTIVE AFTER ALTER EXCEPTION POSITION 0
1475   AS BEGIN END
1476   ^
1477 +
1478   CREATE TRIGGER "BEFORE DROP EXCEPTION"
1479   ACTIVE BEFORE Drop EXCEPTION POSITION 0
1480   AS BEGIN END
1481   ^
1482 +
1483   CREATE TRIGGER "AFTER DROP EXCEPTION"
1484   ACTIVE AFTER Drop EXCEPTION POSITION 0
1485   AS BEGIN END
1486   ^
1487 +
1488   CREATE TRIGGER "BEFORE CREATE VIEW"
1489   ACTIVE BEFORE CREATE VIEW POSITION 0
1490   AS BEGIN END
1491   ^
1492 +
1493   CREATE TRIGGER "AFTER CREATE VIEW"
1494   ACTIVE AFTER CREATE VIEW POSITION 0
1495   AS BEGIN END
1496   ^
1497 +
1498   CREATE TRIGGER "BEFORE ALTER VIEW"
1499   ACTIVE BEFORE ALTER VIEW POSITION 0
1500   AS BEGIN END
1501   ^
1502 +
1503   CREATE TRIGGER "AFTER ALTER VIEW"
1504   ACTIVE AFTER ALTER VIEW POSITION 0
1505   AS BEGIN END
1506   ^
1507 +
1508   CREATE TRIGGER "BEFORE DROP VIEW"
1509   ACTIVE BEFORE Drop VIEW POSITION 0
1510   AS BEGIN END
1511   ^
1512 +
1513   CREATE TRIGGER "AFTER DROP VIEW"
1514   ACTIVE AFTER Drop VIEW POSITION 0
1515   AS BEGIN END
1516   ^
1517 +
1518   CREATE TRIGGER "BEFORE CREATE DOMAIN"
1519   ACTIVE BEFORE CREATE DOMAIN POSITION 0
1520   AS BEGIN END
1521   ^
1522 +
1523   CREATE TRIGGER "AFTER CREATE DOMAIN"
1524   ACTIVE AFTER CREATE DOMAIN POSITION 0
1525   AS BEGIN END
1526   ^
1527 +
1528   CREATE TRIGGER "BEFORE ALTER DOMAIN"
1529   ACTIVE BEFORE ALTER DOMAIN POSITION 0
1530   AS BEGIN END
1531   ^
1532 +
1533   CREATE TRIGGER "AFTER ALTER DOMAIN"
1534   ACTIVE AFTER ALTER DOMAIN POSITION 0
1535   AS BEGIN END
1536   ^
1537 +
1538   CREATE TRIGGER "BEFORE DROP DOMAIN"
1539   ACTIVE BEFORE Drop DOMAIN POSITION 0
1540   AS BEGIN END
1541   ^
1542 +
1543   CREATE TRIGGER "AFTER DROP DOMAIN"
1544   ACTIVE AFTER Drop DOMAIN POSITION 0
1545   AS BEGIN END
1546   ^
1547 +
1548   CREATE TRIGGER "BEFORE CREATE ROLE"
1549   ACTIVE BEFORE CREATE ROLE POSITION 0
1550   AS BEGIN END
1551   ^
1552 +
1553   CREATE TRIGGER "AFTER CREATE ROLE"
1554   ACTIVE AFTER CREATE ROLE POSITION 0
1555   AS BEGIN END
1556   ^
1557 +
1558   CREATE TRIGGER "BEFORE ALTER ROLE"
1559   ACTIVE BEFORE ALTER ROLE POSITION 0
1560   AS BEGIN END
1561   ^
1562 +
1563   CREATE TRIGGER "AFTER ALTER ROLE"
1564   ACTIVE AFTER ALTER ROLE POSITION 0
1565   AS BEGIN END
1566   ^
1567 +
1568   CREATE TRIGGER "BEFORE DROP ROLE"
1569   ACTIVE BEFORE Drop ROLE POSITION 0
1570   AS BEGIN END
1571   ^
1572 +
1573   CREATE TRIGGER "AFTER DROP ROLE"
1574   ACTIVE AFTER Drop ROLE POSITION 0
1575   AS BEGIN END
1576   ^
1577 +
1578   CREATE TRIGGER "BEFORE CREATE INDEX"
1579   ACTIVE BEFORE CREATE INDEX POSITION 0
1580   AS BEGIN END
1581   ^
1582 +
1583   CREATE TRIGGER "AFTER CREATE INDEX"
1584   ACTIVE AFTER CREATE INDEX POSITION 0
1585   AS BEGIN END
1586   ^
1587 +
1588   CREATE TRIGGER "BEFORE ALTER INDEX"
1589   ACTIVE BEFORE ALTER INDEX POSITION 0
1590   AS BEGIN END
1591   ^
1592 +
1593   CREATE TRIGGER "AFTER ALTER INDEX"
1594   ACTIVE AFTER ALTER INDEX POSITION 0
1595   AS BEGIN END
1596   ^
1597 +
1598   CREATE TRIGGER "BEFORE DROP INDEX"
1599   ACTIVE BEFORE Drop INDEX POSITION 0
1600   AS BEGIN END
1601   ^
1602 +
1603   CREATE TRIGGER "AFTER DROP INDEX"
1604   ACTIVE AFTER Drop INDEX POSITION 0
1605   AS BEGIN END
1606   ^
1607 +
1608   CREATE TRIGGER "BEFORE CREATE SEQUENCE"
1609   ACTIVE BEFORE CREATE SEQUENCE POSITION 0
1610   AS BEGIN END
1611   ^
1612 +
1613   CREATE TRIGGER "AFTER CREATE SEQUENCE"
1614   ACTIVE AFTER CREATE SEQUENCE POSITION 0
1615   AS BEGIN END
1616   ^
1617 +
1618   CREATE TRIGGER "BEFORE ALTER SEQUENCE"
1619   ACTIVE BEFORE ALTER SEQUENCE POSITION 0
1620   AS BEGIN END
1621   ^
1622 +
1623   CREATE TRIGGER "AFTER ALTER SEQUENCE"
1624   ACTIVE AFTER ALTER SEQUENCE POSITION 0
1625   AS BEGIN END
1626   ^
1627 +
1628   CREATE TRIGGER "BEFORE DROP SEQUENCE"
1629   ACTIVE BEFORE Drop SEQUENCE POSITION 0
1630   AS BEGIN END
1631   ^
1632 +
1633   CREATE TRIGGER "AFTER DROP SEQUENCE"
1634   ACTIVE AFTER Drop SEQUENCE POSITION 0
1635   AS BEGIN END
1636   ^
1637 +
1638   CREATE TRIGGER "BEFORE CREATE USER"
1639   ACTIVE BEFORE CREATE USER POSITION 0
1640   AS BEGIN END
1641   ^
1642 +
1643   CREATE TRIGGER "AFTER CREATE USER"
1644   ACTIVE AFTER CREATE USER POSITION 0
1645   AS BEGIN END
1646   ^
1647 +
1648   CREATE TRIGGER "BEFORE ALTER USER"
1649   ACTIVE BEFORE ALTER USER POSITION 0
1650   AS BEGIN END
1651   ^
1652 +
1653   CREATE TRIGGER "AFTER ALTER USER"
1654   ACTIVE AFTER ALTER USER POSITION 0
1655   AS BEGIN END
1656   ^
1657 +
1658   CREATE TRIGGER "BEFORE DROP USER"
1659   ACTIVE BEFORE Drop USER POSITION 0
1660   AS BEGIN END
1661   ^
1662 +
1663   CREATE TRIGGER "AFTER DROP USER"
1664   ACTIVE AFTER Drop USER POSITION 0
1665   AS BEGIN END
1666   ^
1667 +
1668   CREATE TRIGGER "BEFORE CREATE COLLATION"
1669   ACTIVE BEFORE CREATE COLLATION POSITION 0
1670   AS BEGIN END
1671   ^
1672 +
1673   CREATE TRIGGER "AFTER CREATE COLLATION"
1674   ACTIVE AFTER CREATE COLLATION POSITION 0
1675   AS BEGIN END
1676   ^
1677 +
1678   CREATE TRIGGER "BEFORE DROP COLLATION"
1679   ACTIVE BEFORE Drop COLLATION POSITION 0
1680   AS BEGIN END
1681   ^
1682 +
1683   CREATE TRIGGER "AFTER DROP COLLATION"
1684   ACTIVE AFTER Drop COLLATION POSITION 0
1685   AS BEGIN END
1686   ^
1687 +
1688   CREATE TRIGGER "BEFORE ALTER CHARACTER SET"
1689   ACTIVE BEFORE ALTER CHARACTER SET POSITION 0
1690   AS BEGIN END
1691   ^
1692 +
1693   CREATE TRIGGER "AFTER ALTER CHARACTER SET"
1694   ACTIVE AFTER ALTER CHARACTER SET POSITION 0
1695   AS BEGIN END
1696   ^
1697 +
1698   CREATE TRIGGER "BEFORE CREATE PACKAGE"
1699   ACTIVE BEFORE CREATE PACKAGE POSITION 0
1700   AS BEGIN END
1701   ^
1702 +
1703   CREATE TRIGGER "AFTER CREATE PACKAGE"
1704   ACTIVE AFTER CREATE PACKAGE POSITION 0
1705   AS BEGIN END
1706   ^
1707 +
1708   CREATE TRIGGER "BEFORE ALTER PACKAGE"
1709   ACTIVE BEFORE ALTER PACKAGE POSITION 0
1710   AS BEGIN END
1711   ^
1712 +
1713   CREATE TRIGGER "AFTER ALTER PACKAGE"
1714   ACTIVE AFTER ALTER PACKAGE POSITION 0
1715   AS BEGIN END
1716   ^
1717 +
1718   CREATE TRIGGER "BEFORE DROP PACKAGE"
1719   ACTIVE BEFORE Drop PACKAGE POSITION 0
1720   AS BEGIN END
1721   ^
1722 +
1723   CREATE TRIGGER "AFTER DROP PACKAGE"
1724   ACTIVE AFTER Drop PACKAGE POSITION 0
1725   AS BEGIN END
1726   ^
1727 +
1728   CREATE TRIGGER "BEFORE CREATE PACKAGE BODY"
1729   ACTIVE BEFORE CREATE PACKAGE BODY POSITION 0
1730   AS BEGIN END
1731   ^
1732 +
1733   CREATE TRIGGER "AFTER CREATE PACKAGE BODY"
1734   ACTIVE AFTER CREATE PACKAGE BODY POSITION 0
1735   AS BEGIN END
1736   ^
1737 +
1738   CREATE TRIGGER "BEFORE DROP PACKAGE BODY"
1739   ACTIVE BEFORE Drop PACKAGE BODY POSITION 0
1740   AS BEGIN END
1741   ^
1742 +
1743   CREATE TRIGGER "AFTER DROP PACKAGE BODY"
1744   ACTIVE AFTER Drop PACKAGE BODY POSITION 0
1745   AS BEGIN END
1746   ^
1747 +
1748   CREATE TRIGGER "BEFORE ANY DDL STATEMENT"
1749   ACTIVE BEFORE ANY DDL STATEMENT POSITION 0
1750   AS BEGIN END
1751   ^
1752 +
1753   CREATE TRIGGER "AFTER ANY DDL STATEMENT"
1754   ACTIVE AFTER ANY DDL STATEMENT POSITION 0
1755   AS BEGIN END
1756   ^
1757 +
1758   CREATE TRIGGER SET_CUST_NO FOR CUSTOMER
1759   ACTIVE BEFORE INSERT POSITION 0
1760   AS
# Line 1698 | Line 1763 | BEGIN
1763      new.cust_no = gen_id(cust_no_gen, 1);
1764   END
1765   ^
1766 +
1767   CREATE TRIGGER SET_EMP_NO FOR EMPLOYEE
1768   ACTIVE BEFORE INSERT POSITION 0
1769   AS
# Line 1706 | Line 1772 | BEGIN
1772      new.emp_no = gen_id(emp_no_gen, 1);
1773   END
1774   ^
1775 +
1776   CREATE TRIGGER SAVE_SALARY_CHANGE FOR EMPLOYEE
1777   ACTIVE AFTER UPDATE POSITION 0
1778   AS
# Line 1721 | Line 1788 | BEGIN
1788              (new.salary - old.salary) * 100 / old.salary);
1789   END
1790   ^
1791 +
1792   CREATE TRIGGER POST_NEW_ORDER FOR SALES
1793   ACTIVE AFTER INSERT POSITION 0
1794   AS
# Line 1728 | Line 1796 | BEGIN
1796      POST_EVENT 'new_order';
1797   END
1798   ^
1799 +
1800   COMMIT WORK^
1801   SET TERM ;^
1802   COMMIT WORK;
1803   SET AUTODDL OFF;
1804 < SET TERM ^ ;
1804 > SET TERM ^;
1805  
1806   /* Package Definitions */
1807  
1808 < CREATE PACKAGE BODY FB$OUT                                                        
1808 > CREATE PACKAGE BODY FB$OUT
1809   AS
1741 –
1810   begin
1811          procedure enable
1812          as
# Line 1795 | Line 1863 | begin
1863   end
1864   ^
1865  
1866 < CREATE PACKAGE BODY SELECT$TEST                                                    
1866 > CREATE PACKAGE BODY SELECT$TEST
1867   AS
1800 –
1868   Begin
1869    Procedure ShowItems(IPARAM Integer) RETURNS (OutParam integer)
1870    As
# Line 1812 | Line 1879 | COMMIT WORK;
1879   SET AUTODDL ON;
1880   COMMIT WORK;
1881   SET AUTODDL OFF;
1882 < SET TERM ^ ;
1882 > SET TERM ^;
1883  
1884   /* Stored procedure Bodies */
1885  
1886 <
1820 < ALTER PROCEDURE SHOW_LANGS
1886 > ALTER PROCEDURE SHOW_LANGS
1887   (
1888    CODE VARCHAR(5) CHARACTER SET NONE,
1889    GRADE SMALLINT,
# Line 1838 | Line 1904 | BEGIN
1904             AND (language_req IS NOT NULL))
1905      INTO :languages;
1906      IF (languages = ' ') THEN  /* Prints 'NULL' instead of blanks */
1907 <       languages = 'NULL';        
1907 >       languages = 'NULL';
1908      i = i +1;
1909      SUSPEND;
1910    END
1911   END
1912   ^
1913  
1914 <
1849 < ALTER PROCEDURE ADD_EMP_PROJ
1914 > ALTER PROCEDURE ADD_EMP_PROJ
1915   (
1916    EMP_NO SMALLINT,
1917    PROJ_ID CHAR(5) CHARACTER SET NONE
# Line 1861 | Line 1926 | BEGIN
1926   END
1927   ^
1928  
1929 < ALTER PROCEDURE ALL_LANGS
1929 > ALTER PROCEDURE ALL_LANGS
1930   RETURNS
1931   (
1932    CODE VARCHAR(5) CHARACTER SET NONE,
# Line 1871 | Line 1936 | RETURNS
1936   )
1937   AS
1938   BEGIN
1939 <        FOR SELECT job_code, job_grade, job_country FROM job
1939 >        FOR SELECT job_code, job_grade, job_country FROM job
1940                  INTO :code, :grade, :country
1941  
1942          DO
1943          BEGIN
1944 <            FOR SELECT languages FROM show_langs
1944 >            FOR SELECT languages FROM show_langs
1945                      (:code, :grade, :country) INTO :lang DO
1946                  SUSPEND;
1947              /* Put nice separators between rows */
# Line 1889 | Line 1954 | BEGIN
1954      END
1955   ^
1956  
1957 <
1893 < ALTER PROCEDURE DELETE_EMPLOYEE
1957 > ALTER PROCEDURE DELETE_EMPLOYEE
1958   (
1959    EMP_NUM INTEGER
1960   )
# Line 1949 | Line 2013 | BEGIN
2013   END
2014   ^
2015  
2016 <
1953 < ALTER PROCEDURE DEPT_BUDGET
2016 > ALTER PROCEDURE DEPT_BUDGET
2017   (
2018    DNO CHAR(3) CHARACTER SET NONE
2019   )
2020   RETURNS
2021   (
2022 <  TOT DECIMAL(12, 2)
2022 >  TOT DECIMAL(12,2)
2023   )
2024   AS
2025 < DECLARE VARIABLE sumb DECIMAL(12, 2);
2025 > DECLARE VARIABLE sumb DECIMAL(12,2);
2026          DECLARE VARIABLE rdno CHAR(3);
2027          DECLARE VARIABLE cnt INTEGER;
2028   BEGIN
# Line 1996 | Line 2059 | RETURNS
2059    SALARY NUMERIC(10,2)
2060   )
2061   AS
2062 < BEGIN
2063 <  Select SALARY, Case
2064 <   When SALARY > 10000 and SALARY <= 100000 then 'higher'
2065 <   When SALARY > 100000 then 'gross'
2066 <   else 'lower' End
2067 <  From employee
2068 <    WHERE emp_no = :EMP_NUM
2062 > Declare aCursor CURSOR FOR (
2063 >    Select EMP_NO,SALARY From EMPLOYEE WHERE LAST_NAME = 'unknown');
2064 > BEGIN
2065 >  Select SALARY, Case
2066 >   When SALARY > 10000 and SALARY <= 100000 then 'higher'
2067 >   When SALARY > 100000 then 'gross'
2068 >   else 'lower' End
2069 >  From employee
2070 >    WHERE emp_no = :EMP_NUM
2071     into :SALARY, :PAY_STATUS;
2072   END
2073   ^
2074  
2075 < ALTER PROCEDURE GET_EMP_PROJ
2075 > ALTER PROCEDURE GET_EMP_PROJ
2076   (
2077    EMP_NO SMALLINT
2078   )
# Line 2026 | Line 2091 | BEGIN
2091   END
2092   ^
2093  
2094 <
2030 < ALTER PROCEDURE "Has Space"
2094 > ALTER PROCEDURE "Has Space"
2095   (
2096    ARG1 INTEGER
2097   )
# Line 2035 | Line 2099 | AS
2099   Begin End
2100   ^
2101  
2102 <
2039 < ALTER PROCEDURE MAIL_LABEL
2102 > ALTER PROCEDURE MAIL_LABEL
2103   (
2104    CUST_NO INTEGER
2105   )
# Line 2106 | Line 2169 | BEGIN
2169   END
2170   ^
2171  
2172 <
2110 < ALTER PROCEDURE ORG_CHART
2172 > ALTER PROCEDURE ORG_CHART
2173   RETURNS
2174   (
2175    HEAD_DEPT CHAR(25) CHARACTER SET NONE,
# Line 2149 | Line 2211 | BEGIN
2211   END
2212   ^
2213  
2214 <
2153 < ALTER PROCEDURE SHIP_ORDER
2214 > ALTER PROCEDURE SHIP_ORDER
2215   (
2216    PO_NUM CHAR(8) CHARACTER SET NONE
2217   )
# Line 2209 | Line 2270 | BEGIN
2270   END
2271   ^
2272  
2273 <
2213 < ALTER PROCEDURE SUB_TOT_BUDGET
2273 > ALTER PROCEDURE SUB_TOT_BUDGET
2274   (
2275    HEAD_DEPT CHAR(3) CHARACTER SET NONE
2276   )
2277   RETURNS
2278   (
2279 <  TOT_BUDGET DECIMAL(12, 2),
2280 <  AVG_BUDGET DECIMAL(12, 2),
2281 <  MIN_BUDGET DECIMAL(12, 2),
2282 <  MAX_BUDGET DECIMAL(12, 2)
2279 >  TOT_BUDGET DECIMAL(12,2),
2280 >  AVG_BUDGET DECIMAL(12,2),
2281 >  MIN_BUDGET DECIMAL(12,2),
2282 >  MAX_BUDGET DECIMAL(12,2)
2283   )
2284   AS
2285   BEGIN
# Line 2231 | Line 2291 | BEGIN
2291   END
2292   ^
2293  
2294 <
2235 < ALTER PROCEDURE "UC SPACE"
2294 > ALTER PROCEDURE "UC SPACE"
2295   (
2296    ARG1 INTEGER
2297   )
# Line 2240 | Line 2299 | AS
2299   Begin End
2300   ^
2301  
2302 <
2244 < ALTER PROCEDURE "iCASE"
2302 > ALTER PROCEDURE "iCASE"
2303   AS
2304   Begin End
2305   ^
# Line 2251 | Line 2309 | COMMIT WORK;
2309   SET AUTODDL ON;
2310   COMMIT WORK;
2311   SET AUTODDL OFF;
2312 < SET TERM ^ ;
2312 > SET TERM ^;
2313  
2314   /* Stored Function Body */
2315  
2316   ALTER FUNCTION F (X INTEGER)
2317   RETURNS INTEGER
2318 < AS
2318 > AS
2319   BEGIN
2320   RETURN X+1;
2321   END
2322   ^
2323  
2266 –
2324   SET TERM ;^
2325   COMMIT WORK;
2326   SET AUTODDL ON;

Diff Legend

– Removed lines
+ Added lines
< Changed lines
> Changed lines