Java 1.2 How-To/JavaHowTo.iso/3rdParty/jbuilder/TRIAL/WINCLNT/DATA.Z/PROCS.SQL

Text File | 1998-03-15 | 7.7 KB | 342 lines

/*
    THIS FILE IS FOR USE WITH THE TUTORIAL IN "GETTING STARTED",
    IT IS NOT MEANT TO BE USED WITHOUT REFERRING TO THE MANUAL.

*   You must change the parameters below to match your
*   server name, database name, username, and password.
*   This file creates stored procedures for the EMPLOYEE database.
*/

CONNECT "server:\dir\mydb.gdb"
USER "USERNAME" PASSWORD "password";

SET TERM ^ ;

CREATE PROCEDURE GET_EMP_PROJ (EMP_NO SMALLINT)
RETURNS (PROJ_ID CHAR(5))
AS
BEGIN
  FOR SELECT PROJ_ID
  FROM EMPLOYEE_PROJECT
  WHERE EMP_NO = :EMP_NO
  INTO :PROJ_ID
  DO
    SUSPEND;
END ^


CREATE EXCEPTION UNKNOWN_EMP_ID "Invalid employee number or project id."^

CREATE PROCEDURE ADD_EMP_PROJ (EMP_NO SMALLINT, PROJ_ID CHAR(5))
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 ^


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
  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 ^

CREATE EXCEPTION REASSIGN_SALES
  "Reassign the sales records before deleting this employee."^

CREATE PROCEDURE DELETE_EMPLOYEE (EMP_NUM INTEGER)
AS
  DECLARE VARIABLE any_sales INTEGER;
BEGIN
  any_sales = 0;

  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

  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 ^

CREATE PROCEDURE DEPT_BUDGET (DNO CHAR(3))
RETURNS (TOT NUMERIC(15, 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 ^

CREATE PROCEDURE ORG_CHART
RETURNS (head_dept CHAR(25), department CHAR(25), mngr_name CHAR(20),
title CHAR(5), 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 ^

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
  DECLARE VARIABLE customer VARCHAR(25);
  DECLARE VARIABLE first  VARCHAR(15);
  DECLARE VARIABLE last  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, :last, :addr1, :addr2, :city, :state,
    :country, :postcode;

  IF (CUSTOMER IS NOT NULL) THEN
    line1 = customer;
  IF (first IS NOT NULL) THEN
    line2 = first || " " || last;
  ELSE
    line2 = last;
  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 ^

CREATE EXCEPTION ORDER_ALREADY_SHIPPED "Order status is 'shipped.'"^
CREATE EXCEPTION CUSTOMER_ON_HOLD "This customer is on hold."^
CREATE EXCEPTION CUSTOMER_CHECK "Overdue balance -- can't ship."^

CREATE PROCEDURE SHIP_ORDER (PO_NUM CHAR(8))
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 < 'NOW' - 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 ^

CREATE PROCEDURE SHOW_LANGS (CODE VARCHAR(5),
GRADE SMALLINT,
CTY VARCHAR(15))
RETURNS (LANGUAGES VARCHAR(15))
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 ^

CREATE PROCEDURE ALL_LANGS RETURNS (CODE VARCHAR(5),
GRADE VARCHAR(5),
COUNTRY VARCHAR(15),
LANG VARCHAR(15))
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 ^
SET TERM ; ^
COMMIT WORK ;
SET TERM ^ ;