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;
GRANT EXECUTE ON PROCEDURE SUB_TOT_BUDGET TO PUBLIC WITH GRANT OPTION;