employee.sql

jvcl driver development envionment

/******************************************************************************/
/***           Generated by IBExpert 2.5.0.22 07/03/2003 18:57:49           ***/
/******************************************************************************/



/******************************************************************************/
/***                                Domains                                 ***/
/******************************************************************************/

CREATE DOMAIN ADDRESSLINE AS
VARCHAR(30) CHARACTER SET NONE;

CREATE DOMAIN BUDGET AS
NUMERIC(15,2)
DEFAULT 50000
CHECK (VALUE > 10000 AND VALUE <= 2000000);

CREATE DOMAIN COUNTRYNAME AS
VARCHAR(15) CHARACTER SET NONE;

CREATE DOMAIN CUSTNO AS
INTEGER
CHECK (VALUE > 1000);

CREATE DOMAIN DEPTNO AS
CHAR(3) CHARACTER SET NONE
CHECK (VALUE = '000' OR (VALUE > '0' AND VALUE <= '999') OR VALUE IS NULL);

CREATE DOMAIN EMPNO AS
SMALLINT;

CREATE DOMAIN FIRSTNAME AS
VARCHAR(15) CHARACTER SET NONE;

CREATE DOMAIN JOBCODE AS
VARCHAR(5) CHARACTER SET NONE
CHECK (VALUE > '99999');

CREATE DOMAIN JOBGRADE AS
SMALLINT
CHECK (VALUE BETWEEN 0 AND 6);

CREATE DOMAIN LASTNAME AS
VARCHAR(20) CHARACTER SET NONE;

CREATE DOMAIN PHONENUMBER AS
VARCHAR(20) CHARACTER SET NONE;

CREATE DOMAIN PONUMBER AS
CHAR(8) CHARACTER SET NONE
CHECK (VALUE STARTING WITH 'V');

CREATE DOMAIN PRODTYPE AS
VARCHAR(12) CHARACTER SET NONE
DEFAULT 'software'
NOT NULL
CHECK (VALUE IN ('software', 'hardware', 'other', 'N/A'));

CREATE DOMAIN PROJNO AS
CHAR(5) CHARACTER SET NONE
CHECK (VALUE = UPPER (VALUE));

CREATE DOMAIN SALARY AS
NUMERIC(15,2)
DEFAULT 0
CHECK (VALUE > 0);



/******************************************************************************/
/***                               Generators                               ***/
/******************************************************************************/

CREATE GENERATOR CUST_NO_GEN;
SET GENERATOR CUST_NO_GEN TO 1015;

CREATE GENERATOR EMP_NO_GEN;
SET GENERATOR EMP_NO_GEN TO 145;



/******************************************************************************/
/***                               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.';



SET TERM ^ ; 



/******************************************************************************/
/***                           Stored Procedures                            ***/
/******************************************************************************/

CREATE PROCEDURE ADD_EMP_PROJ (
    EMP_NO SMALLINT,
    PROJ_ID CHAR(5) CHARACTER SET NONE)
AS
BEGIN
  EXIT;
END^


CREATE 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
  EXIT;
END^


CREATE PROCEDURE DELETE_EMPLOYEE (
    EMP_NUM INTEGER)
AS
BEGIN
  EXIT;
END^


CREATE PROCEDURE DEPT_BUDGET (
    DNO CHAR(3) CHARACTER SET NONE)
RETURNS (
    TOT NUMERIC(15,2))
AS
BEGIN
  EXIT;
END^


CREATE PROCEDURE GET_EMP_PROJ (
    EMP_NO SMALLINT)
RETURNS (
    PROJ_ID CHAR(5) CHARACTER SET NONE)
AS
BEGIN
  EXIT;
END^


CREATE 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
  EXIT;
END^


CREATE 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
  EXIT;
END^


CREATE PROCEDURE SHIP_ORDER (
    PO_NUM CHAR(8) CHARACTER SET NONE)
AS
BEGIN
  EXIT;
END^


CREATE 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
  EXIT;
END^


CREATE PROCEDURE SUB_TOT_BUDGET (
    HEAD_DEPT CHAR(3) CHARACTER SET NONE)
RETURNS (
    TOT_BUDGET NUMERIC(15,2),