SET SQL DIALECT 3;

/* CREATE DATABASE '/home/job/python/projects/firebird-driver/tests/fbtest40.fdb' PAGE_SIZE 8192 DEFAULT CHARACTER SET NONE; */

/* Collations */

CREATE COLLATION TEST_COLLATE FOR WIN1250 FROM EXTERNAL ('WIN_CZ') CASE INSENSITIVE ACCENT INSENSITIVE 'DISABLE-COMPRESSIONS=0;DISABLE-EXPANSIONS=0';

/* Generators or sequences */

CREATE GENERATOR CUST_NO_GEN;

CREATE GENERATOR EMP_NO_GEN;

/* Domain definitions */

CREATE DOMAIN ADDRESSLINE AS VARCHAR(30);

CREATE DOMAIN BUDGET AS DECIMAL(12, 2)

DEFAULT 50000;

CREATE DOMAIN COUNTRYNAME AS VARCHAR(15);

CREATE DOMAIN CUSTNO AS INTEGER;

CREATE DOMAIN DEPTNO AS CHAR(3);

CREATE DOMAIN EMPNO AS SMALLINT;

CREATE DOMAIN FIRSTNAME AS VARCHAR(15);

CREATE DOMAIN JOBCODE AS VARCHAR(5);

CREATE DOMAIN JOBGRADE AS SMALLINT;

CREATE DOMAIN LASTNAME AS VARCHAR(20);

CREATE DOMAIN PHONENUMBER AS VARCHAR(20);

CREATE DOMAIN PONUMBER AS CHAR(8);

CREATE DOMAIN PRODTYPE AS VARCHAR(12)

DEFAULT 'software' NOT NULL;

CREATE DOMAIN PROJNO AS CHAR(5);

CREATE DOMAIN SALARY AS NUMERIC(10, 2)

DEFAULT 0;

COMMIT WORK;

/* Table: AR, Owner: SYSDBA */

CREATE TABLE AR (C1 INTEGER,

C2 INTEGER[4, 0:3, 2],

C3 VARCHAR(15)[0:5, 2],

C4 CHAR(5)[5],

C5 TIMESTAMP[2],

C6 TIME[2],

C7 DECIMAL(10, 2)[2],

C8 NUMERIC(10, 2)[2],

C9 SMALLINT[2],

C10 BIGINT[2],

C11 FLOAT[2],

C12 DOUBLE PRECISION[2],

C13 DECIMAL(10, 1)[2],

C14 DECIMAL(10, 5)[2],

C15 DECIMAL(18, 5)[2],

C16 BOOLEAN[3]);

/* Table: COUNTRY, Owner: SYSDBA */

CREATE TABLE COUNTRY (COUNTRY COUNTRYNAME NOT NULL,

CURRENCY VARCHAR(10) NOT NULL,

PRIMARY KEY (COUNTRY));

/* Table: CUSTOMER, Owner: SYSDBA */

CREATE TABLE CUSTOMER (CUST_NO CUSTNO NOT NULL,

CUSTOMER VARCHAR(25) NOT NULL,

CONTACT_FIRST FIRSTNAME,

CONTACT_LAST LASTNAME,

PHONE_NO PHONENUMBER,

ADDRESS_LINE1 ADDRESSLINE,

ADDRESS_LINE2 ADDRESSLINE,

CITY VARCHAR(25),

STATE_PROVINCE VARCHAR(15),

COUNTRY COUNTRYNAME,

POSTAL_CODE VARCHAR(12),

ON_HOLD CHAR(1) DEFAULT NULL,

PRIMARY KEY (CUST_NO));

/* Table: DEPARTMENT, Owner: SYSDBA */

CREATE TABLE DEPARTMENT (DEPT_NO DEPTNO NOT NULL,

DEPARTMENT VARCHAR(25) NOT NULL,

HEAD_DEPT DEPTNO,

MNGR_NO EMPNO,

BUDGET BUDGET,

LOCATION VARCHAR(15),

PHONE_NO PHONENUMBER DEFAULT '555-1234',

PRIMARY KEY (DEPT_NO),

UNIQUE (DEPARTMENT));

/* Table: EMPLOYEE, Owner: SYSDBA */

CREATE TABLE EMPLOYEE (EMP_NO EMPNO NOT NULL,

FIRST_NAME FIRSTNAME NOT NULL,

LAST_NAME LASTNAME NOT NULL,

PHONE_EXT VARCHAR(4),

HIRE_DATE TIMESTAMP DEFAULT 'NOW' NOT NULL,

DEPT_NO DEPTNO NOT NULL,

JOB_CODE JOBCODE NOT NULL,

JOB_GRADE JOBGRADE NOT NULL,

JOB_COUNTRY COUNTRYNAME NOT NULL,

SALARY SALARY NOT NULL,

FULL_NAME VARCHAR(37) COMPUTED BY (NULL),

PRIMARY KEY (EMP_NO));

/* Table: EMPLOYEE_PROJECT, Owner: SYSDBA */

CREATE TABLE EMPLOYEE_PROJECT (EMP_NO EMPNO NOT NULL,

PROJ_ID PROJNO NOT NULL,

PRIMARY KEY (EMP_NO, PROJ_ID));

/* Table: FB4, Owner: SYSDBA */

CREATE TABLE FB4 (PK INTEGER,

T_TZ TIME WITH TIME ZONE,

TS_TZ TIMESTAMP WITH TIME ZONE,

T TIME,

TS TIMESTAMP,

DF DECFLOAT(34),

DF16 DECFLOAT(16),

DF34 DECFLOAT(34),

N128 NUMERIC(34, 6),

D128 DECIMAL(34, 6),

ADF DECFLOAT(34)[2],

ADF16 DECFLOAT(16)[2],

ADF34 DECFLOAT(34)[2],

AN128 NUMERIC(34, 6)[2],

AD128 DECIMAL(34, 6)[2],

AT_TZ TIME WITH TIME ZONE[2],

ATS_TZ TIMESTAMP WITH TIME ZONE[2]

);

/* Table: JOB, Owner: SYSDBA */

CREATE TABLE JOB (JOB_CODE JOBCODE NOT NULL,

JOB_GRADE JOBGRADE NOT NULL,

JOB_COUNTRY COUNTRYNAME NOT NULL,

JOB_TITLE VARCHAR(25) NOT NULL,

MIN_SALARY SALARY NOT NULL,

MAX_SALARY SALARY NOT NULL,

JOB_REQUIREMENT BLOB SUB_TYPE TEXT SEGMENT SIZE 400,

LANGUAGE_REQ VARCHAR(15)[5],

PRIMARY KEY (JOB_CODE, JOB_GRADE, JOB_COUNTRY));

/* Table: PROJECT, Owner: SYSDBA */

CREATE TABLE PROJECT (PROJ_ID PROJNO NOT NULL,

PROJ_NAME VARCHAR(20) NOT NULL,

PROJ_DESC BLOB SUB_TYPE TEXT SEGMENT SIZE 800,

TEAM_LEADER EMPNO,

PRODUCT PRODTYPE,

PRIMARY KEY (PROJ_ID),

UNIQUE (PROJ_NAME));

/* Table: PROJ_DEPT_BUDGET, Owner: SYSDBA */

CREATE TABLE PROJ_DEPT_BUDGET (FISCAL_YEAR INTEGER NOT NULL,

PROJ_ID PROJNO NOT NULL,

DEPT_NO DEPTNO NOT NULL,

QUART_HEAD_CNT INTEGER[4],

PROJECTED_BUDGET BUDGET,

PRIMARY KEY (FISCAL_YEAR, PROJ_ID, DEPT_NO));

/* Table: SALARY_HISTORY, Owner: SYSDBA */

CREATE TABLE SALARY_HISTORY (EMP_NO EMPNO NOT NULL,

CHANGE_DATE TIMESTAMP DEFAULT 'NOW' NOT NULL,

UPDATER_ID VARCHAR(20) NOT NULL,

OLD_SALARY SALARY NOT NULL,

PERCENT_CHANGE DOUBLE PRECISION DEFAULT 0 NOT NULL,

NEW_SALARY DOUBLE PRECISION COMPUTED BY (NULL),

PRIMARY KEY (EMP_NO, CHANGE_DATE, UPDATER_ID));

/* Table: SALES, Owner: SYSDBA */

CREATE TABLE SALES (PO_NUMBER PONUMBER NOT NULL,

CUST_NO CUSTNO NOT NULL,

SALES_REP EMPNO,

ORDER_STATUS VARCHAR(7) DEFAULT 'new' NOT NULL,

ORDER_DATE TIMESTAMP DEFAULT 'NOW' NOT NULL,

SHIP_DATE TIMESTAMP,

DATE_NEEDED TIMESTAMP,

PAID CHAR(1) DEFAULT 'n',

QTY_ORDERED INTEGER DEFAULT 1 NOT NULL,

TOTAL_VALUE DECIMAL(9, 2) NOT NULL,

DISCOUNT FLOAT DEFAULT 0 NOT NULL,

ITEM_TYPE PRODTYPE,

AGED NUMERIC(18, 9) COMPUTED BY (NULL),

PRIMARY KEY (PO_NUMBER));

/* Table: T, Owner: SYSDBA */

CREATE TABLE T (C1 INTEGER);

/* Table: T2, Owner: SYSDBA */

CREATE TABLE T2 (C1 SMALLINT,

C2 INTEGER,

C3 BIGINT,

C4 CHAR(5),

C5 VARCHAR(10),

C6 DATE,

C7 TIME,

C8 TIMESTAMP,

C9 BLOB SUB_TYPE TEXT SEGMENT SIZE 80,

C10 NUMERIC(18, 2),

C11 DECIMAL(18, 2),

C12 FLOAT,

C13 DOUBLE PRECISION,

C14 NUMERIC(8, 4),

C15 DECIMAL(8, 4),

C16 BLOB SUB_TYPE 0 SEGMENT SIZE 80,

C17 BOOLEAN);

/* Table: T3, Owner: SYSDBA */

CREATE TABLE T3 (C1 INTEGER,

C2 CHAR(10) CHARACTER SET UTF8,

C3 VARCHAR(10) CHARACTER SET UTF8,

C4 BLOB SUB_TYPE TEXT SEGMENT SIZE 80 CHARACTER SET UTF8,

C5 BLOB SUB_TYPE 0 SEGMENT SIZE 80);

/* Table: T4, Owner: SYSDBA */

CREATE TABLE T4 (C1 INTEGER,

C_OCTETS CHAR(5) CHARACTER SET OCTETS,

V_OCTETS VARCHAR(30) CHARACTER SET OCTETS,

C_NONE CHAR(5),

V_NONE VARCHAR(30),

C_WIN1250 CHAR(5) CHARACTER SET WIN1250,

V_WIN1250 VARCHAR(30) CHARACTER SET WIN1250,

C_UTF8 CHAR(5) CHARACTER SET UTF8,

V_UTF8 VARCHAR(30) CHARACTER SET UTF8);

/* Table: T5, Owner: SYSDBA */

CREATE TABLE T5 (ID NUMERIC(10, 0) GENERATED BY DEFAULT AS IDENTITY NOT NULL,

C1 VARCHAR(15),

UQ BIGINT GENERATED BY DEFAULT AS IDENTITY (START WITH 100) NOT NULL,

PRIMARY KEY (ID));

COMMIT WORK;

SET AUTODDL OFF;

SET TERM ^ ;

/* Stored functions headers */

CREATE OR ALTER FUNCTION F2 (X INTEGER)

RETURNS INTEGER

AS

BEGIN END ^

CREATE OR ALTER FUNCTION FN RETURNS INTEGER

AS

BEGIN END ^

CREATE OR ALTER FUNCTION FX (F TYPE OF FIRSTNAME,

L TYPE OF COLUMN CUSTOMER.CONTACT_LAST)

RETURNS VARCHAR(35) CHARACTER SET NONE

AS

BEGIN END ^

SET TERM ; ^

COMMIT WORK;

SET AUTODDL ON;

COMMIT WORK;

SET AUTODDL OFF;

SET TERM ^ ;

/* Stored procedures headers */

CREATE OR ALTER PROCEDURE ADD_EMP_PROJ (EMP_NO SMALLINT,

PROJ_ID CHAR(5) CHARACTER SET NONE)

AS

BEGIN SUSPEND; END ^

CREATE OR ALTER PROCEDURE ALL_LANGS RETURNS (CODE VARCHAR(5) CHARACTER SET NONE,

GRADE VARCHAR(5) CHARACTER SET NONE,

COUNTRY VARCHAR(15) CHARACTER SET NONE,

LANG VARCHAR(15) CHARACTER SET NONE)

AS

BEGIN SUSPEND; END ^

CREATE OR ALTER PROCEDURE DELETE_EMPLOYEE (EMP_NUM INTEGER)

AS

BEGIN SUSPEND; END ^

CREATE OR ALTER PROCEDURE DEPT_BUDGET (DNO CHAR(3) CHARACTER SET NONE)

RETURNS (TOT DECIMAL(12, 2))

AS

BEGIN SUSPEND; END ^

CREATE OR ALTER PROCEDURE GET_EMP_PROJ (EMP_NO SMALLINT)

RETURNS (PROJ_ID CHAR(5) CHARACTER SET NONE)

AS

BEGIN SUSPEND; END ^

CREATE OR ALTER PROCEDURE MAIL_LABEL (CUST_NO INTEGER)

RETURNS (LINE1 CHAR(40) CHARACTER SET NONE,

LINE2 CHAR(40) CHARACTER SET NONE,

LINE3 CHAR(40) CHARACTER SET NONE,

LINE4 CHAR(40) CHARACTER SET NONE,

LINE5 CHAR(40) CHARACTER SET NONE,

LINE6 CHAR(40) CHARACTER SET NONE)

AS

BEGIN SUSPEND; END ^

CREATE OR ALTER PROCEDURE ORG_CHART RETURNS (HEAD_DEPT CHAR(25) CHARACTER SET NONE,

DEPARTMENT CHAR(25) CHARACTER SET NONE,

MNGR_NAME CHAR(20) CHARACTER SET NONE,

TITLE CHAR(5) CHARACTER SET NONE,

EMP_CNT INTEGER)

AS

BEGIN SUSPEND; END ^

CREATE OR ALTER PROCEDURE PROC_TEST (I INTEGER)

AS

BEGIN EXIT; END ^

CREATE OR ALTER PROCEDURE SHIP_ORDER (PO_NUM CHAR(8) CHARACTER SET NONE)

AS

BEGIN SUSPEND; END ^

CREATE OR ALTER PROCEDURE SHOW_FB4 AS

BEGIN EXIT; END ^

CREATE OR ALTER PROCEDURE SHOW_LANGS (CODE VARCHAR(5) CHARACTER SET NONE,

GRADE SMALLINT,

CTY VARCHAR(15) CHARACTER SET NONE)

RETURNS (LANGUAGES VARCHAR(15) CHARACTER SET NONE)

AS

BEGIN SUSPEND; END ^

CREATE OR ALTER PROCEDURE SUB_TOT_BUDGET (HEAD_DEPT CHAR(3) CHARACTER SET NONE)

RETURNS (TOT_BUDGET DECIMAL(12, 2),

AVG_BUDGET DECIMAL(12, 2),

MIN_BUDGET DECIMAL(12, 2),

MAX_BUDGET DECIMAL(12, 2))

AS

BEGIN SUSPEND; END ^

SET TERM ; ^

COMMIT WORK;

SET AUTODDL ON;

COMMIT WORK;

SET AUTODDL OFF;

SET TERM ^ ;

/* Package headers */

/* Package header: TEST, Owner: SYSDBA */

CREATE PACKAGE TEST AS

BEGIN

PROCEDURE P1(I INT) RETURNS (O INT); -- public procedure

FUNCTION F(X INT) RETURNS INT;

END^

/* Package header: TEST2, Owner: SYSDBA */

CREATE PACKAGE TEST2 AS

BEGIN

FUNCTION F3(X INT) RETURNS INT;

END^

SET TERM ; ^

COMMIT WORK;

SET AUTODDL ON;

/* Index definitions for all user tables */

CREATE INDEX CUSTNAMEX ON CUSTOMER (CUSTOMER);

CREATE INDEX CUSTREGION ON CUSTOMER (COUNTRY, CITY);

CREATE DESCENDING INDEX BUDGETX ON DEPARTMENT (BUDGET);

CREATE INDEX NAMEX ON EMPLOYEE (LAST_NAME, FIRST_NAME);

CREATE DESCENDING INDEX MAXSALX ON JOB (JOB_COUNTRY, MAX_SALARY);

CREATE INDEX MINSALX ON JOB (JOB_COUNTRY, MIN_SALARY);

CREATE UNIQUE INDEX PRODTYPEX ON PROJECT (PRODUCT, PROJ_NAME);

CREATE DESCENDING INDEX CHANGEX ON SALARY_HISTORY (CHANGE_DATE);

CREATE INDEX UPDATERX ON SALARY_HISTORY (UPDATER_ID);

CREATE INDEX NEEDX ON SALES (DATE_NEEDED);

CREATE DESCENDING INDEX QTYX ON SALES (ITEM_TYPE, QTY_ORDERED);

CREATE INDEX SALESTATX ON SALES (ORDER_STATUS, PAID);

ALTER TABLE CUSTOMER ADD FOREIGN KEY (COUNTRY) REFERENCES COUNTRY (COUNTRY);

ALTER TABLE DEPARTMENT ADD FOREIGN KEY (HEAD_DEPT) REFERENCES DEPARTMENT (DEPT_NO);

ALTER TABLE DEPARTMENT ADD FOREIGN KEY (MNGR_NO) REFERENCES EMPLOYEE (EMP_NO);

ALTER TABLE EMPLOYEE ADD FOREIGN KEY (DEPT_NO) REFERENCES DEPARTMENT (DEPT_NO);

ALTER TABLE EMPLOYEE ADD FOREIGN KEY (JOB_CODE, JOB_GRADE, JOB_COUNTRY) REFERENCES JOB (JOB_CODE, JOB_GRADE, JOB_COUNTRY);

ALTER TABLE EMPLOYEE_PROJECT ADD FOREIGN KEY (EMP_NO) REFERENCES EMPLOYEE (EMP_NO);

ALTER TABLE EMPLOYEE_PROJECT ADD FOREIGN KEY (PROJ_ID) REFERENCES PROJECT (PROJ_ID);

ALTER TABLE JOB ADD FOREIGN KEY (JOB_COUNTRY) REFERENCES COUNTRY (COUNTRY);

ALTER TABLE PROJECT ADD FOREIGN KEY (TEAM_LEADER) REFERENCES EMPLOYEE (EMP_NO);

ALTER TABLE PROJ_DEPT_BUDGET ADD FOREIGN KEY (DEPT_NO) REFERENCES DEPARTMENT (DEPT_NO);

ALTER TABLE PROJ_DEPT_BUDGET ADD FOREIGN KEY (PROJ_ID) REFERENCES PROJECT (PROJ_ID);

ALTER TABLE SALARY_HISTORY ADD FOREIGN KEY (EMP_NO) REFERENCES EMPLOYEE (EMP_NO);

ALTER TABLE SALES ADD FOREIGN KEY (CUST_NO) REFERENCES CUSTOMER (CUST_NO);

ALTER TABLE SALES ADD FOREIGN KEY (SALES_REP) REFERENCES EMPLOYEE (EMP_NO);

/* View: PHONE_LIST, Owner: SYSDBA */

CREATE VIEW PHONE_LIST (EMP_NO, FIRST_NAME, LAST_NAME, PHONE_EXT, LOCATION, PHONE_NO) AS

SELECT

emp_no, first_name, last_name, phone_ext, location, phone_no

FROM employee, department

WHERE employee.dept_no = department.dept_no;

/* View: V_SHOW_FB4, Owner: SYSDBA */

CREATE VIEW V_SHOW_FB4 (T_TZ, TS_TZ, T, TS, DF, DF16, DF34, N128, D128) AS

select T_TZ, TS_TZ, T, TS, DF, DF16, DF34, N128, D128 from fb4;

/* Exceptions */

CREATE EXCEPTION CUSTOMER_CHECK 'Overdue balance -- can not ship.';

CREATE EXCEPTION CUSTOMER_ON_HOLD 'This customer is on hold.';

CREATE EXCEPTION ORDER_ALREADY_SHIPPED 'Order status is "shipped."';

CREATE EXCEPTION REASSIGN_SALES 'Reassign the sales records before deleting this employee.';

CREATE EXCEPTION UNKNOWN_EMP_ID 'Invalid employee number or project id.';

COMMIT WORK;

SET AUTODDL OFF;

SET TERM ^ ;

/* Stored functions bodies */

ALTER FUNCTION F2 (X INTEGER)

RETURNS INTEGER

AS

BEGIN

RETURN X+1;

END ^

ALTER FUNCTION FN RETURNS INTEGER

AS

BEGIN

RETURN 0;

END ^

ALTER FUNCTION FX (F TYPE OF FIRSTNAME,

L TYPE OF COLUMN CUSTOMER.CONTACT_LAST)

RETURNS VARCHAR(35) CHARACTER SET NONE

AS

BEGIN

RETURN L || ', ' || F;

END ^

SET TERM ; ^

COMMIT WORK;

SET AUTODDL ON;

COMMIT WORK;

SET AUTODDL OFF;

SET TERM ^ ;

/* Stored procedures bodies */

ALTER PROCEDURE ADD_EMP_PROJ (EMP_NO SMALLINT,

PROJ_ID CHAR(5) CHARACTER SET NONE)

AS

BEGIN

BEGIN

INSERT INTO employee_project (emp_no, proj_id) VALUES (:emp_no, :proj_id);

WHEN SQLCODE -530 DO

EXCEPTION unknown_emp_id;

END

SUSPEND;

END ^

ALTER PROCEDURE ALL_LANGS RETURNS (CODE VARCHAR(5) CHARACTER SET NONE,

GRADE VARCHAR(5) CHARACTER SET NONE,

COUNTRY VARCHAR(15) CHARACTER SET NONE,

LANG VARCHAR(15) CHARACTER SET NONE)

AS

BEGIN

FOR SELECT job_code, job_grade, job_country FROM job

INTO :code, :grade, :country

DO

BEGIN

FOR SELECT languages FROM show_langs

(:code, :grade, :country) INTO :lang DO

SUSPEND;

/* Put nice separators between rows */

code = '=====';

grade = '=====';

country = '===============';

lang = '==============';

SUSPEND;

END

END ^

ALTER PROCEDURE DELETE_EMPLOYEE (EMP_NUM INTEGER)

AS

DECLARE VARIABLE any_sales INTEGER;

BEGIN

any_sales = 0;

/*

* If there are any sales records referencing this employee,

* can't delete the employee until the sales are re-assigned

* to another employee or changed to NULL.

*/

SELECT count(po_number)

FROM sales

WHERE sales_rep = :emp_num

INTO :any_sales;

IF (any_sales > 0) THEN

BEGIN

EXCEPTION reassign_sales;

SUSPEND;

END

/*

* If the employee is a manager, update the department.

*/

UPDATE department

SET mngr_no = NULL

WHERE mngr_no = :emp_num;

/*

* If the employee is a project leader, update project.

*/

UPDATE project

SET team_leader = NULL

WHERE team_leader = :emp_num;

/*

* Delete the employee from any projects.

*/

DELETE FROM employee_project

WHERE emp_no = :emp_num;

/*

* Delete old salary records.

*/

DELETE FROM salary_history

WHERE emp_no = :emp_num;

/*

* Delete the employee.

*/

DELETE FROM employee

WHERE emp_no = :emp_num;

SUSPEND;

END ^

ALTER PROCEDURE DEPT_BUDGET (DNO CHAR(3) CHARACTER SET NONE)

RETURNS (TOT DECIMAL(12, 2))

AS

DECLARE VARIABLE sumb DECIMAL(12, 2);

DECLARE VARIABLE rdno CHAR(3);

DECLARE VARIABLE cnt INTEGER;

BEGIN

tot = 0;

SELECT budget FROM department WHERE dept_no = :dno INTO :tot;

SELECT count(budget) FROM department WHERE head_dept = :dno INTO :cnt;

IF (cnt = 0) THEN

SUSPEND;

FOR SELECT dept_no

FROM department

WHERE head_dept = :dno

INTO :rdno

DO

BEGIN

EXECUTE PROCEDURE dept_budget :rdno RETURNING_VALUES :sumb;

tot = tot + sumb;

END

SUSPEND;

END ^

ALTER PROCEDURE GET_EMP_PROJ (EMP_NO SMALLINT)

RETURNS (PROJ_ID CHAR(5) CHARACTER SET NONE)

AS

BEGIN

FOR SELECT proj_id

FROM employee_project

WHERE emp_no = :emp_no

INTO :proj_id

DO

SUSPEND;

END ^

ALTER PROCEDURE MAIL_LABEL (CUST_NO INTEGER)

RETURNS (LINE1 CHAR(40) CHARACTER SET NONE,

LINE2 CHAR(40) CHARACTER SET NONE,

LINE3 CHAR(40) CHARACTER SET NONE,

LINE4 CHAR(40) CHARACTER SET NONE,

LINE5 CHAR(40) CHARACTER SET NONE,

LINE6 CHAR(40) CHARACTER SET NONE)

AS

DECLARE VARIABLE customer VARCHAR(25);

DECLARE VARIABLE first_name VARCHAR(15);

DECLARE VARIABLE last_name VARCHAR(20);

DECLARE VARIABLE addr1 VARCHAR(30);

DECLARE VARIABLE addr2 VARCHAR(30);

DECLARE VARIABLE city VARCHAR(25);

DECLARE VARIABLE state VARCHAR(15);

DECLARE VARIABLE country VARCHAR(15);

DECLARE VARIABLE postcode VARCHAR(12);

DECLARE VARIABLE cnt INTEGER;

BEGIN

line1 = '';

line2 = '';

line3 = '';

line4 = '';

line5 = '';

line6 = '';

SELECT customer, contact_first, contact_last, address_line1,

address_line2, city, state_province, country, postal_code

FROM CUSTOMER

WHERE cust_no = :cust_no

INTO :customer, :first_name, :last_name, :addr1, :addr2,

:city, :state, :country, :postcode;

IF (customer IS NOT NULL) THEN

line1 = customer;

IF (first_name IS NOT NULL) THEN

line2 = first_name || ' ' || last_name;

ELSE

line2 = last_name;

IF (addr1 IS NOT NULL) THEN

line3 = addr1;

IF (addr2 IS NOT NULL) THEN

line4 = addr2;

IF (country = 'USA') THEN

BEGIN

IF (city IS NOT NULL) THEN

line5 = city || ', ' || state || ' ' || postcode;

ELSE

line5 = state || ' ' || postcode;

END

ELSE

BEGIN

IF (city IS NOT NULL) THEN

line5 = city || ', ' || state;

ELSE

line5 = state;

line6 = country || ' ' || postcode;

END

SUSPEND;

END ^

ALTER PROCEDURE ORG_CHART RETURNS (HEAD_DEPT CHAR(25) CHARACTER SET NONE,

DEPARTMENT CHAR(25) CHARACTER SET NONE,

MNGR_NAME CHAR(20) CHARACTER SET NONE,

TITLE CHAR(5) CHARACTER SET NONE,

EMP_CNT INTEGER)

AS

DECLARE VARIABLE mngr_no INTEGER;

DECLARE VARIABLE dno CHAR(3);

BEGIN

FOR SELECT h.department, d.department, d.mngr_no, d.dept_no

FROM department d

LEFT OUTER JOIN department h ON d.head_dept = h.dept_no

ORDER BY d.dept_no

INTO :head_dept, :department, :mngr_no, :dno

DO

BEGIN

IF (:mngr_no IS NULL) THEN

BEGIN

mngr_name = '--TBH--';

title = '';

END

ELSE

SELECT full_name, job_code

FROM employee

WHERE emp_no = :mngr_no

INTO :mngr_name, :title;

SELECT COUNT(emp_no)

FROM employee

WHERE dept_no = :dno

INTO :emp_cnt;

SUSPEND;

END

END ^

ALTER PROCEDURE PROC_TEST (I INTEGER)

AS

begin

insert into T (C1) values (:I);

end ^

ALTER PROCEDURE SHIP_ORDER (PO_NUM CHAR(8) CHARACTER SET NONE)

AS

DECLARE VARIABLE ord_stat CHAR(7);

DECLARE VARIABLE hold_stat CHAR(1);

DECLARE VARIABLE cust_no INTEGER;

DECLARE VARIABLE any_po CHAR(8);

BEGIN

SELECT s.order_status, c.on_hold, c.cust_no

FROM sales s, customer c

WHERE po_number = :po_num

AND s.cust_no = c.cust_no

INTO :ord_stat, :hold_stat, :cust_no;

/* This purchase order has been already shipped. */

IF (ord_stat = 'shipped') THEN

BEGIN

EXCEPTION order_already_shipped;

SUSPEND;

END

/* Customer is on hold. */

ELSE IF (hold_stat = '*') THEN

BEGIN

EXCEPTION customer_on_hold;

SUSPEND;

END

/*

* If there is an unpaid balance on orders shipped over 2 months ago,

* put the customer on hold.

*/

FOR SELECT po_number

FROM sales

WHERE cust_no = :cust_no

AND order_status = 'shipped'

AND paid = 'n'

AND ship_date < CAST('NOW' AS TIMESTAMP) - 60

INTO :any_po

DO

BEGIN

EXCEPTION customer_check;

UPDATE customer

SET on_hold = '*'

WHERE cust_no = :cust_no;

SUSPEND;

END

/*

* Ship the order.

*/

UPDATE sales

SET order_status = 'shipped', ship_date = 'NOW'

WHERE po_number = :po_num;

SUSPEND;

END ^

ALTER PROCEDURE SHOW_FB4 AS

DECLARE VARIABLE P_PK INTEGER;

DECLARE VARIABLE P_T_TZ TIME WITH TIME ZONE;

DECLARE VARIABLE P_TS_TZ TIMESTAMP WITH TIME ZONE;

DECLARE VARIABLE P_T TIME;

DECLARE VARIABLE P_TS TIMESTAMP;

DECLARE VARIABLE P_DF DECFLOAT(34);

DECLARE VARIABLE P_DF16 DECFLOAT(16);

DECLARE VARIABLE P_DF34 DECFLOAT(34);

DECLARE VARIABLE P_N128 NUMERIC(34, 6);

DECLARE VARIABLE P_D128 DECIMAL(34, 6);

BEGIN

for SELECT PK, T_TZ, TS_TZ, T, TS, DF, DF16, DF34, N128, D128 from FB4 into :P_PK, :P_T_TZ, :P_TS_TZ, :P_T, :P_TS, :P_DF, :P_DF16, :P_DF34, :P_N128, :P_D128 DO

begin

end

END ^

ALTER PROCEDURE SHOW_LANGS (CODE VARCHAR(5) CHARACTER SET NONE,

GRADE SMALLINT,

CTY VARCHAR(15) CHARACTER SET NONE)

RETURNS (LANGUAGES VARCHAR(15) CHARACTER SET NONE)

AS

DECLARE VARIABLE i INTEGER;

BEGIN

i = 1;

WHILE (i <= 5) DO

BEGIN

SELECT language_req[:i] FROM joB

WHERE ((job_code = :code) AND (job_grade = :grade) AND (job_country = :cty)

AND (language_req IS NOT NULL))

INTO :languages;

IF (languages = ' ') THEN /* Prints 'NULL' instead of blanks */

languages = 'NULL';

i = i +1;

SUSPEND;

END

END ^

ALTER PROCEDURE SUB_TOT_BUDGET (HEAD_DEPT CHAR(3) CHARACTER SET NONE)

RETURNS (TOT_BUDGET DECIMAL(12, 2),

AVG_BUDGET DECIMAL(12, 2),

MIN_BUDGET DECIMAL(12, 2),

MAX_BUDGET DECIMAL(12, 2))

AS

BEGIN

SELECT SUM(budget), AVG(budget), MIN(budget), MAX(budget)

FROM department

WHERE head_dept = :head_dept

INTO :tot_budget, :avg_budget, :min_budget, :max_budget;

SUSPEND;

END ^

SET TERM ; ^

COMMIT WORK;

SET AUTODDL ON;

COMMIT WORK;

SET AUTODDL OFF;

SET TERM ^ ;

/* Package bodies */

/* Package body: TEST, Owner: SYSDBA */

CREATE PACKAGE BODY TEST AS

BEGIN

FUNCTION F1(I INT) RETURNS INT; -- private function

PROCEDURE P1(I INT) RETURNS (O INT)

AS

BEGIN

END

FUNCTION F1(I INT) RETURNS INT

AS

BEGIN

RETURN F(I)+10;

END

FUNCTION F(X INT) RETURNS INT

AS

BEGIN

RETURN X+1;

END

END^

/* Package body: TEST2, Owner: SYSDBA */

CREATE PACKAGE BODY TEST2 AS

BEGIN

FUNCTION F3(X INT) RETURNS INT

AS

BEGIN

RETURN TEST.F(X)+100+FN();

END

END^

SET TERM ; ^

COMMIT WORK;

SET AUTODDL ON;

/* Domain constraints */

ALTER DOMAIN BUDGET ADD CONSTRAINT

CHECK (VALUE > 10000 AND VALUE <= 2000000);

ALTER DOMAIN CUSTNO ADD CONSTRAINT

CHECK (VALUE > 1000);

ALTER DOMAIN DEPTNO ADD CONSTRAINT

CHECK (VALUE = '000' OR (VALUE > '0' AND VALUE <= '999') OR VALUE IS NULL);

ALTER DOMAIN JOBCODE ADD CONSTRAINT

CHECK (VALUE > '99999');

ALTER DOMAIN JOBGRADE ADD CONSTRAINT

CHECK (VALUE BETWEEN 0 AND 6);

ALTER DOMAIN PONUMBER ADD CONSTRAINT

CHECK (VALUE STARTING WITH 'V');

ALTER DOMAIN PRODTYPE ADD CONSTRAINT

CHECK (VALUE IN ('software', 'hardware', 'other', 'N/A'));

ALTER DOMAIN PROJNO ADD CONSTRAINT

CHECK (VALUE = UPPER (VALUE));

ALTER DOMAIN SALARY ADD CONSTRAINT

CHECK (VALUE > 0);

/* Table constraints */

ALTER TABLE JOB ADD

CHECK (min_salary < max_salary);

ALTER TABLE EMPLOYEE ADD

CHECK ( salary >= (SELECT min_salary FROM job WHERE

job.job_code = employee.job_code AND

job.job_grade = employee.job_grade AND

job.job_country = employee.job_country) AND

salary <= (SELECT max_salary FROM job WHERE

job.job_code = employee.job_code AND

job.job_grade = employee.job_grade AND

job.job_country = employee.job_country));

ALTER TABLE PROJ_DEPT_BUDGET ADD

CHECK (FISCAL_YEAR >= 1993);

ALTER TABLE SALARY_HISTORY ADD

CHECK (percent_change between -50 and 50);

ALTER TABLE CUSTOMER ADD

CHECK (on_hold IS NULL OR on_hold = '*');

ALTER TABLE SALES ADD

CHECK (order_status in

('new', 'open', 'shipped', 'waiting'));

ALTER TABLE SALES ADD

CHECK (ship_date >= order_date OR ship_date IS NULL);

ALTER TABLE SALES ADD

CHECK (date_needed > order_date OR date_needed IS NULL);

ALTER TABLE SALES ADD

CHECK (paid in ('y', 'n'));

ALTER TABLE SALES ADD

CHECK (qty_ordered >= 1);

ALTER TABLE SALES ADD

CHECK (total_value >= 0);

ALTER TABLE SALES ADD

CHECK (discount >= 0 AND discount <= 1);

ALTER TABLE SALES ADD

CHECK (NOT (order_status = 'shipped' AND ship_date IS NULL));

ALTER TABLE SALES ADD

CHECK (NOT (order_status = 'shipped' AND

EXISTS (SELECT on_hold FROM customer

WHERE customer.cust_no = sales.cust_no

AND customer.on_hold = '*')));

/* Computed fields */

ALTER TABLE EMPLOYEE

ALTER FULL_NAME TYPE VARCHAR(37) COMPUTED BY (last_name || ', ' || first_name);

ALTER TABLE SALARY_HISTORY

ALTER NEW_SALARY TYPE DOUBLE PRECISION COMPUTED BY (old_salary + old_salary * percent_change / 100);

ALTER TABLE SALES

ALTER AGED TYPE NUMERIC(18, 9) COMPUTED BY (ship_date - order_date);

SET TERM ^ ;

/* Triggers only will work for SQL triggers */

CREATE TRIGGER TR_CONNECT

ACTIVE ON CONNECT POSITION 0

AS

BEGIN

/* enter trigger code here */

END ^

CREATE TRIGGER TR_MULTI FOR COUNTRY

ACTIVE AFTER INSERT OR UPDATE OR DELETE POSITION 0

AS

BEGIN

/* enter trigger code here */

END ^

CREATE TRIGGER SET_CUST_NO FOR CUSTOMER

ACTIVE BEFORE INSERT POSITION 0

AS

BEGIN

if (new.cust_no is null) then

new.cust_no = gen_id(cust_no_gen, 1);

END ^

CREATE TRIGGER SET_EMP_NO FOR EMPLOYEE

ACTIVE BEFORE INSERT POSITION 0

AS

BEGIN

if (new.emp_no is null) then

new.emp_no = gen_id(emp_no_gen, 1);

END ^

CREATE TRIGGER SAVE_SALARY_CHANGE FOR EMPLOYEE

ACTIVE AFTER UPDATE POSITION 0

AS

BEGIN

IF (old.salary <> new.salary) THEN

INSERT INTO salary_history

(emp_no, change_date, updater_id, old_salary, percent_change)

VALUES (

old.emp_no,

'NOW',

user,

old.salary,

(new.salary - old.salary) * 100 / old.salary);

END ^

CREATE TRIGGER POST_NEW_ORDER FOR SALES

ACTIVE AFTER INSERT POSITION 0

AS

BEGIN

POST_EVENT 'new_order';

END ^

SET TERM ; ^

COMMIT WORK;

/* Grant roles for this database */

/* Role: TEST_ROLE, Owner: SYSDBA */

CREATE ROLE TEST_ROLE;

/* Grant permissions for this database */

GRANT ALL ON COUNTRY TO PUBLIC WITH GRANT OPTION;

GRANT ALL ON CUSTOMER TO PUBLIC WITH GRANT OPTION;

GRANT ALL ON DEPARTMENT TO PUBLIC WITH GRANT OPTION;

GRANT ALL ON EMPLOYEE TO PUBLIC WITH GRANT OPTION;

GRANT ALL ON EMPLOYEE_PROJECT TO PUBLIC WITH GRANT OPTION;

GRANT ALL ON JOB TO PUBLIC WITH GRANT OPTION;

GRANT ALL ON PHONE_LIST TO PUBLIC WITH GRANT OPTION;

GRANT ALL ON PROJECT TO PUBLIC WITH GRANT OPTION;

GRANT ALL ON PROJ_DEPT_BUDGET TO PUBLIC WITH GRANT OPTION;

GRANT ALL ON SALARY_HISTORY TO PUBLIC WITH GRANT OPTION;

GRANT ALL ON SALES TO PUBLIC WITH GRANT OPTION;

GRANT EXECUTE ON PROCEDURE ADD_EMP_PROJ TO PUBLIC WITH GRANT OPTION;

GRANT EXECUTE ON PROCEDURE ALL_LANGS TO PUBLIC WITH GRANT OPTION;

GRANT EXECUTE ON PROCEDURE DELETE_EMPLOYEE TO PUBLIC WITH GRANT OPTION;

GRANT EXECUTE ON PROCEDURE DEPT_BUDGET TO PUBLIC WITH GRANT OPTION;

GRANT EXECUTE ON PROCEDURE GET_EMP_PROJ TO PUBLIC WITH GRANT OPTION;

GRANT EXECUTE ON PROCEDURE MAIL_LABEL TO PUBLIC WITH GRANT OPTION;

GRANT EXECUTE ON PROCEDURE ORG_CHART TO PUBLIC WITH GRANT OPTION;

GRANT EXECUTE ON PROCEDURE SHIP_ORDER TO PUBLIC WITH GRANT OPTION;

GRANT EXECUTE ON PROCEDURE SHOW_LANGS TO PUBLIC WITH GRANT OPTION;