main.dfm

jvcl driver development envionment

      ''
      
        'CREATE EXCEPTION ORDER_ALREADY_SHIPPED '#39'Order status is "shipped' +
        '."'#39';'
      ''
      
        'CREATE EXCEPTION REASSIGN_SALES '#39'Reassign the sales records befo' +
        're deleting this employee.'#39';'
      ''
      
        'CREATE EXCEPTION UNKNOWN_EMP_ID '#39'Invalid employee number or proj' +
        'ect id.'#39';'
      ''
      ''
      ''
      ''
      
        '/***************************************************************' +
        '***************/'
      
        '/****                          Stored Procedures                ' +
        '           ****/'
      
        '/***************************************************************' +
        '***************/'
      ''
      'CREATE PROCEDURE ADD_EMP_PROJ ('
      '    EMP_NO SMALLINT,'
      '    PROJ_ID CHAR(5))'
      'AS'
      'BEGIN'
      '  EXIT;'
      'END;'
      ''
      ''
      'CREATE PROCEDURE ALL_LANGS'
      'RETURNS ('
      '    CODE VARCHAR(5),'
      '    GRADE VARCHAR(5),'
      '    COUNTRY VARCHAR(15),'
      '    LANG VARCHAR(15))'
      'AS'
      'BEGIN'
      '  EXIT;'
      'END;'
      ''
      ''
      'CREATE PROCEDURE DELETE_EMPLOYEE ('
      '    EMP_NUM INTEGER)'
      'AS'
      'BEGIN'
      '  EXIT;'
      'END;'
      ''
      ''
      'CREATE PROCEDURE DEPT_BUDGET ('
      '    DNO CHAR(3))'
      'RETURNS ('
      '    TOT NUMERIC(15,2))'
      'AS'
      'BEGIN'
      '  EXIT;'
      'END;'
      ''
      ''
      'CREATE PROCEDURE GET_EMP_PROJ ('
      '    EMP_NO SMALLINT)'
      'RETURNS ('
      '    PROJ_ID CHAR(5))'
      'AS'
      'BEGIN'
      '  EXIT;'
      'END;'
      ''
      ''
      'CREATE PROCEDURE MAIL_LABEL ('
      '    CUST_NO INTEGER)'
      'RETURNS ('
      '    LINE1 CHAR(40),'
      '    LINE2 CHAR(40),'
      '    LINE3 CHAR(40),'
      '    LINE4 CHAR(40),'
      '    LINE5 CHAR(40),'
      '    LINE6 CHAR(40))'
      'AS'
      'BEGIN'
      '  EXIT;'
      'END;'
      ''
      ''
      'CREATE PROCEDURE ORG_CHART'
      'RETURNS ('
      '    HEAD_DEPT CHAR(25),'
      '    DEPARTMENT CHAR(25),'
      '    MNGR_NAME CHAR(20),'
      '    TITLE CHAR(5),'
      '    EMP_CNT INTEGER)'
      'AS'
      'BEGIN'
      '  EXIT;'
      'END;'
      ''
      ''
      'CREATE PROCEDURE SHIP_ORDER ('
      '    PO_NUM CHAR(8))'
      'AS'
      'BEGIN'
      '  EXIT;'
      'END;'
      ''
      ''
      'CREATE PROCEDURE SHOW_LANGS ('
      '    CODE VARCHAR(5),'
      '    GRADE SMALLINT,'
      '    CTY VARCHAR(15))'
      'RETURNS ('
      '    LANGUAGES VARCHAR(15))'
      'AS'
      'BEGIN'
      '  EXIT;'
      'END;'
      ''
      ''
      'CREATE PROCEDURE SUB_TOT_BUDGET ('
      '    HEAD_DEPT CHAR(3))'
      'RETURNS ('
      '    TOT_BUDGET NUMERIC(15,2),'
      '    AVG_BUDGET NUMERIC(15,2),'
      '    MIN_BUDGET NUMERIC(15,2),'
      '    MAX_BUDGET NUMERIC(15,2))'
      'AS'
      'BEGIN'
      '  EXIT;'
      'END;'
      ''
      ''
      'CREATE PROCEDURE VALEUR_STOCK2'
      'RETURNS ('
      '    S_INDEX INTEGER,'
      '    S_RAYON SMALLINT,'
      '    S_DISPO DECIMAL(12,2),'
      '    S_INVENTAIRE DECIMAL(12,2))'
      'AS'
      'BEGIN'
      '  EXIT;'
      'END;'
      ''
      ''
      'CREATE PROCEDURE VALEUR_STOCK3'
      'RETURNS ('
      '    S_INDEX INTEGER,'
      '    S_RAYON SMALLINT,'
      '    S_DISPO DECIMAL(12,2),'
      '    S_INVENTAIRE DECIMAL(12,2))'
      'AS'
      'BEGIN'
      '  EXIT;'
      'END;'
      ''
      ''
      'CREATE PROCEDURE VALEUR_STOCK4'
      'RETURNS ('
      '    S_INDEX INTEGER,'
      '    S_RAYON SMALLINT,'
      '    S_DISPO DECIMAL(12,2),'
      '    S_INVENTAIRE DECIMAL(12,2))'
      'AS'
      'BEGIN'
      '  EXIT;'
      'END;'
      ''
      ''
      ''
      ''
      ''
      
        '/***************************************************************' +
        '***************/'
      
        '/****                                Tables                     ' +
        '           ****/'
      
        '/***************************************************************' +
        '***************/'
      ''
      'CREATE TABLE COUNTRY ('
      '    COUNTRY   COUNTRYNAME NOT NULL,'
      '    CURRENCY  VARCHAR(10) NOT NULL'
      ');'
      ''
      ''
      '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'
      ');'
      ''
      ''
      '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 '#39'555-1234'#39
      ');'
      ''
      ''
      '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 '#39'NOW'#39' 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    COMPUTED BY (last_name || '#39', '#39' || first_name)'
      ');'
      ''
      ''
      'CREATE TABLE EMPLOYEE_PROJECT ('
      '    EMP_NO   EMPNO NOT NULL,'
      '    PROJ_ID  PROJNO NOT NULL'
      ');'
      ''
      ''
      '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 1 SEGMENT SIZE 400,'
      '    LANGUAGE_REQ     VARCHAR(15) [1:5]'
      ');'
      ''
      ''
      'CREATE TABLE PROJ_DEPT_BUDGET ('
      '    FISCAL_YEAR       INTEGER NOT NULL,'
      '    PROJ_ID           PROJNO NOT NULL,'
      '    DEPT_NO           DEPTNO NOT NULL,'
      '    QUART_HEAD_CNT    INTEGER [1:4],'
      '    PROJECTED_BUDGET  BUDGET'
      ');'
      ''
      ''
      'CREATE TABLE PROJECT ('
      '    PROJ_ID      PROJNO NOT NULL,'
      '    PROJ_NAME    VARCHAR(20) NOT NULL,'
      '    PROJ_DESC    BLOB SUB_TYPE 1 SEGMENT SIZE 800,'
      '    TEAM_LEADER  EMPNO,'
      '    PRODUCT      PRODTYPE'
      ');'
      ''
      ''
      'CREATE TABLE SALARY_HISTORY ('
      '    EMP_NO          EMPNO NOT NULL,'
      '    CHANGE_DATE     TIMESTAMP DEFAULT '#39'NOW'#39' NOT NULL,'
      '    UPDATER_ID      VARCHAR(20) NOT NULL,'
      '    OLD_SALARY      SALARY NOT NULL,'
      '    PERCENT_CHANGE  DOUBLE PRECISION DEFAULT 0 NOT NULL,'
      
        '    NEW_SALARY      COMPUTED BY (old_salary + old_salary * perce' +
        'nt_change / 100)'
      ');'
      ''
      ''
      'CREATE TABLE SALES ('
      '    PO_NUMBER     PONUMBER NOT NULL,'
      '    CUST_NO       CUSTNO NOT NULL,'
      '    SALES_REP     EMPNO,'
      '    ORDER_STATUS  VARCHAR(7) DEFAULT '#39'new'#39' NOT NULL,'
      '    ORDER_DATE    TIMESTAMP DEFAULT '#39'NOW'#39' NOT NULL,'
      '    SHIP_DATE     TIMESTAMP,'
      '    DATE_NEEDED   TIMESTAMP,'
      '    PAID          CHAR(1) DEFAULT '#39'n'#39','
      '    QTY_ORDERED   INTEGER DEFAULT 1 NOT NULL,'
      '    TOTAL_VALUE   NUMERIC(9,2) NOT NULL,'
      '    DISCOUNT      FLOAT DEFAULT 0 NOT NULL,'
      '    ITEM_TYPE     PRODTYPE,'
      '    AGED          COMPUTED BY (ship_date - order_date)'
      ');'
      ''
      ''
      ''
      ''
      ''
      
        '/***************************************************************' +
        '***************/'
      
        '/****                                Views                      ' +
        '           ****/'
      
        '/***************************************************************' +
        '***************/'
      ''
      ''
      '/* View: PHONE_LIST */'
      'CREATE VIEW PHONE_LIST('
      '    EMP_NO,'