employee_ib6.sql

East make Tray Icon in delphi

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

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 firstone  VARCHAR(15);
 DECLARE VARIABLE lastone  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, :firstone, :lastone, :addr1, :addr2,
  :city, :state, :country, :postcode;

 IF (customer IS NOT NULL) THEN
  line1 = customer;
 IF (firstone IS NOT NULL) THEN
  line2 = firstone || ' ' || lastone;
 ELSE
  line2 = lastone;
 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