mirror of
https://github.com/FirebirdSQL/firebird.git
synced 2025-01-22 21:23:04 +01:00
942 lines
23 KiB
SQL
942 lines
23 KiB
SQL
/*
|
|
* The contents of this file are subject to the Interbase Public
|
|
* License Version 1.0 (the "License"); you may not use this file
|
|
* except in compliance with the License. You may obtain a copy
|
|
* of the License at http://www.Inprise.com/IPL.html
|
|
*
|
|
* Software distributed under the License is distributed on an
|
|
* "AS IS" basis, WITHOUT WARRANTY OF ANY KIND, either express
|
|
* or implied. See the License for the specific language governing
|
|
* rights and limitations under the License.
|
|
*
|
|
* The Original Code was created by Inprise Corporation
|
|
* and its predecessors. Portions created by Inprise Corporation are
|
|
* Copyright (C) Inprise Corporation.
|
|
*
|
|
* All Rights Reserved.
|
|
* Contributor(s): ______________________________________.
|
|
*/
|
|
/*create database "employee.fdb";*/
|
|
/**
|
|
** Create a sample employee database.
|
|
**
|
|
** This database keeps track of employees, departments, projects, and sales
|
|
** for a small company.
|
|
**
|
|
**/
|
|
|
|
|
|
/*
|
|
* Define domains.
|
|
*/
|
|
/* set echo on;
|
|
*/
|
|
|
|
CREATE DOMAIN firstname AS VARCHAR(15);
|
|
|
|
CREATE DOMAIN lastname AS VARCHAR(20);
|
|
|
|
CREATE DOMAIN phonenumber AS VARCHAR(20);
|
|
|
|
CREATE DOMAIN countryname AS VARCHAR(15);
|
|
|
|
CREATE DOMAIN addressline AS VARCHAR(30);
|
|
|
|
|
|
CREATE DOMAIN empno
|
|
AS SMALLINT;
|
|
|
|
CREATE DOMAIN deptno
|
|
AS CHAR(3)
|
|
CHECK (VALUE = '000' OR (VALUE > '0' AND VALUE <= '999') OR VALUE IS NULL);
|
|
|
|
CREATE DOMAIN projno
|
|
AS CHAR(5)
|
|
CHECK (VALUE = UPPER (VALUE));
|
|
|
|
CREATE DOMAIN custno
|
|
AS INTEGER
|
|
CHECK (VALUE > 1000);
|
|
|
|
/* must begin with a letter */
|
|
CREATE DOMAIN jobcode
|
|
AS VARCHAR(5)
|
|
CHECK (VALUE > '99999');
|
|
|
|
CREATE DOMAIN jobgrade
|
|
AS SMALLINT
|
|
CHECK (VALUE BETWEEN 0 AND 6);
|
|
|
|
/* salary is in any currency type */
|
|
CREATE DOMAIN salary
|
|
AS NUMERIC(10,2)
|
|
DEFAULT 0
|
|
CHECK (VALUE > 0);
|
|
|
|
/* budget is in US dollars */
|
|
CREATE DOMAIN budget
|
|
AS DECIMAL(12,2)
|
|
DEFAULT 50000
|
|
CHECK (VALUE > 10000 AND VALUE <= 2000000);
|
|
|
|
CREATE DOMAIN prodtype
|
|
AS VARCHAR(12)
|
|
DEFAULT 'software' NOT NULL
|
|
CHECK (VALUE IN ('software', 'hardware', 'other', 'N/A'));
|
|
|
|
CREATE DOMAIN PONUMBER
|
|
AS CHAR(8)
|
|
CHECK (VALUE STARTING WITH 'V');
|
|
|
|
|
|
/*
|
|
* Create generators.
|
|
*/
|
|
|
|
CREATE GENERATOR emp_no_gen;
|
|
|
|
CREATE GENERATOR cust_no_gen;
|
|
SET GENERATOR cust_no_gen to 1000;
|
|
|
|
COMMIT;
|
|
|
|
/*
|
|
* Create tables.
|
|
*/
|
|
|
|
|
|
/*
|
|
* Country name, currency type.
|
|
*/
|
|
CREATE TABLE country
|
|
(
|
|
country COUNTRYNAME NOT NULL PRIMARY KEY,
|
|
currency VARCHAR(10) NOT NULL
|
|
);
|
|
|
|
|
|
/*
|
|
* Job id, job title, minimum and maximum salary, job description,
|
|
* and required languages.
|
|
*
|
|
* A job is defined by a multiple key, consisting of a job_code
|
|
* (a 5-letter job abbreviation), a job grade, and a country name
|
|
* indicating the salary currency type.
|
|
*
|
|
* The salary range is expressed in the appropriate country's currency.
|
|
*
|
|
* The job requirement is a text blob.
|
|
*
|
|
* The job may also require some knowledge of foreign languages,
|
|
* stored in a character array.
|
|
*/
|
|
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(400,1),
|
|
language_req VARCHAR(15) [5],
|
|
|
|
PRIMARY KEY (job_code, job_grade, job_country),
|
|
FOREIGN KEY (job_country) REFERENCES country (country),
|
|
|
|
CHECK (min_salary < max_salary)
|
|
);
|
|
|
|
CREATE ASCENDING INDEX minsalx ON job (job_country, min_salary);
|
|
CREATE DESCENDING INDEX maxsalx ON job (job_country, max_salary);
|
|
|
|
|
|
/*
|
|
* Department number, name, head department, manager id,
|
|
* budget, location, department phone number.
|
|
*
|
|
* Each department is a sub-department in some department, determined
|
|
* by head_dept. The head of this tree is the company.
|
|
* This information is used to produce a company organization chart.
|
|
*
|
|
* Departments have managers; however, manager id can be null to allow
|
|
* for temporary situations where a manager needs to be hired.
|
|
*
|
|
* Budget is allocated in U.S. dollars for all departments.
|
|
*
|
|
* Foreign key mngr_no is added after the employee table is created,
|
|
* using 'alter table'.
|
|
*/
|
|
CREATE TABLE department
|
|
(
|
|
dept_no DEPTNO NOT NULL,
|
|
department VARCHAR(25) NOT NULL UNIQUE,
|
|
head_dept DEPTNO,
|
|
mngr_no EMPNO,
|
|
budget BUDGET,
|
|
location VARCHAR(15),
|
|
phone_no PHONENUMBER DEFAULT '555-1234',
|
|
|
|
PRIMARY KEY (dept_no),
|
|
FOREIGN KEY (head_dept) REFERENCES department (dept_no)
|
|
);
|
|
|
|
CREATE DESCENDING INDEX budgetx ON department (budget);
|
|
|
|
|
|
/*
|
|
* Employee id, name, phone extension, date of hire, department id,
|
|
* job and salary information.
|
|
*
|
|
* Salary can be entered in any country's currency.
|
|
* Therefore, some of the salaries can appear magnitudes larger than others,
|
|
* depending on the currency type. Ex. Italian lira vs. U.K. pound.
|
|
* The currency type is determined by the country code.
|
|
*
|
|
* job_code, job_grade, and job_country reference employee's job information,
|
|
* illustrating two tables related by referential constraints on multiple
|
|
* columns.
|
|
*
|
|
* The employee salary is verified to be in the correct salary range
|
|
* for the given job title.
|
|
*/
|
|
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 COMPUTED BY (last_name || ', ' || first_name),
|
|
|
|
PRIMARY KEY (emp_no),
|
|
FOREIGN KEY (dept_no) REFERENCES
|
|
department (dept_no),
|
|
FOREIGN KEY (job_code, job_grade, job_country) REFERENCES
|
|
job (job_code, job_grade, job_country),
|
|
|
|
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))
|
|
);
|
|
|
|
CREATE INDEX namex ON employee (last_name, first_name);
|
|
|
|
CREATE VIEW phone_list AS SELECT
|
|
emp_no, first_name, last_name, phone_ext, location, phone_no
|
|
FROM employee, department
|
|
WHERE employee.dept_no = department.dept_no;
|
|
|
|
COMMIT;
|
|
|
|
SET TERM !! ;
|
|
|
|
CREATE TRIGGER set_emp_no FOR employee
|
|
BEFORE INSERT AS
|
|
BEGIN
|
|
if (new.emp_no is null) then
|
|
new.emp_no = gen_id(emp_no_gen, 1);
|
|
END !!
|
|
|
|
SET TERM ; !!
|
|
|
|
|
|
/*
|
|
* Add an additional constraint to department: check manager numbers
|
|
* in the employee table.
|
|
*/
|
|
ALTER TABLE department ADD FOREIGN KEY (mngr_no) REFERENCES employee (emp_no);
|
|
|
|
|
|
/*
|
|
* Project id, project name, description, project team leader,
|
|
* and product type.
|
|
*
|
|
* Project description is a text blob.
|
|
*/
|
|
CREATE TABLE project
|
|
(
|
|
proj_id PROJNO NOT NULL,
|
|
proj_name VARCHAR(20) NOT NULL UNIQUE,
|
|
proj_desc BLOB(800,1),
|
|
team_leader EMPNO,
|
|
product PRODTYPE,
|
|
|
|
PRIMARY KEY (proj_id),
|
|
FOREIGN KEY (team_leader) REFERENCES employee (emp_no)
|
|
);
|
|
|
|
CREATE UNIQUE INDEX prodtypex ON project (product, proj_name);
|
|
|
|
|
|
/*
|
|
* Employee id, project id, employee's project duties.
|
|
*
|
|
* Employee duties is a text blob.
|
|
*/
|
|
CREATE TABLE employee_project
|
|
(
|
|
emp_no EMPNO NOT NULL,
|
|
proj_id PROJNO NOT NULL,
|
|
|
|
PRIMARY KEY (emp_no, proj_id),
|
|
FOREIGN KEY (emp_no) REFERENCES employee (emp_no),
|
|
FOREIGN KEY (proj_id) REFERENCES project (proj_id)
|
|
);
|
|
|
|
|
|
/*
|
|
* Fiscal year, project id, department id, projected head count by
|
|
* fiscal quarter, projected budget.
|
|
*
|
|
* Tracks head count and budget planning by project by department.
|
|
*
|
|
* Quarterly head count is an array of integers.
|
|
*/
|
|
CREATE TABLE proj_dept_budget
|
|
(
|
|
fiscal_year INTEGER NOT NULL CHECK (FISCAL_YEAR >= 1993),
|
|
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),
|
|
FOREIGN KEY (dept_no) REFERENCES department (dept_no),
|
|
FOREIGN KEY (proj_id) REFERENCES project (proj_id)
|
|
);
|
|
|
|
|
|
/*
|
|
* Employee number, salary change date, updater's user id, old salary,
|
|
* and percent change between old and new salary.
|
|
*/
|
|
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
|
|
CHECK (percent_change between -50 and 50),
|
|
new_salary COMPUTED BY
|
|
(old_salary + old_salary * percent_change / 100),
|
|
|
|
PRIMARY KEY (emp_no, change_date, updater_id),
|
|
FOREIGN KEY (emp_no) REFERENCES employee (emp_no)
|
|
);
|
|
|
|
CREATE INDEX updaterx ON salary_history (updater_id);
|
|
CREATE DESCENDING INDEX changex ON salary_history (change_date);
|
|
|
|
COMMIT;
|
|
|
|
SET TERM !! ;
|
|
|
|
CREATE TRIGGER save_salary_change FOR employee
|
|
AFTER UPDATE 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 !!
|
|
|
|
SET TERM ; !!
|
|
|
|
COMMIT;
|
|
|
|
|
|
/*
|
|
* Customer id, customer name, contact first and last names,
|
|
* phone number, address lines, city, state or province, country,
|
|
* postal code or zip code, and customer status.
|
|
*/
|
|
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
|
|
DEFAULT NULL
|
|
CHECK (on_hold IS NULL OR on_hold = '*'),
|
|
PRIMARY KEY (cust_no),
|
|
FOREIGN KEY (country) REFERENCES country (country)
|
|
);
|
|
|
|
CREATE INDEX custnamex ON customer (customer);
|
|
CREATE INDEX custregion ON customer (country, city);
|
|
|
|
SET TERM !! ;
|
|
|
|
CREATE TRIGGER set_cust_no FOR customer
|
|
BEFORE INSERT AS
|
|
BEGIN
|
|
if (new.cust_no is null) then
|
|
new.cust_no = gen_id(cust_no_gen, 1);
|
|
END !!
|
|
|
|
SET TERM ; !!
|
|
|
|
COMMIT;
|
|
|
|
|
|
/*
|
|
* Purchase order number, customer id, sales representative, order status,
|
|
* order date, date shipped, date need to ship by, payment received flag,
|
|
* quantity ordered, total order value, type of product ordered,
|
|
* any percent discount offered.
|
|
*
|
|
* Tracks customer orders.
|
|
*
|
|
* sales_rep is the ID of the employee handling the sale.
|
|
*
|
|
* Number of days passed since the order date is a computed field.
|
|
*
|
|
* Several checks are performed on this table, among them:
|
|
* - A sale order must have a status: open, shipped, waiting.
|
|
* - The ship date must be entered, if order status is 'shipped'.
|
|
* - New orders can't be shipped to customers with 'on_hold' status.
|
|
* - Sales rep
|
|
*/
|
|
CREATE TABLE sales
|
|
(
|
|
po_number PONUMBER NOT NULL,
|
|
cust_no CUSTNO NOT NULL,
|
|
sales_rep EMPNO,
|
|
order_status VARCHAR(7)
|
|
DEFAULT 'new'
|
|
NOT NULL
|
|
CHECK (order_status in
|
|
('new', 'open', 'shipped', 'waiting')),
|
|
order_date TIMESTAMP
|
|
DEFAULT 'NOW'
|
|
NOT NULL,
|
|
ship_date TIMESTAMP
|
|
CHECK (ship_date >= order_date OR ship_date IS NULL),
|
|
date_needed TIMESTAMP
|
|
CHECK (date_needed > order_date OR date_needed IS NULL),
|
|
paid CHAR
|
|
DEFAULT 'n'
|
|
CHECK (paid in ('y', 'n')),
|
|
qty_ordered INTEGER
|
|
DEFAULT 1
|
|
NOT NULL
|
|
CHECK (qty_ordered >= 1),
|
|
total_value DECIMAL(9,2)
|
|
NOT NULL
|
|
CHECK (total_value >= 0),
|
|
discount FLOAT
|
|
DEFAULT 0
|
|
NOT NULL
|
|
CHECK (discount >= 0 AND discount <= 1),
|
|
item_type PRODTYPE,
|
|
aged COMPUTED BY
|
|
(ship_date - order_date),
|
|
|
|
PRIMARY KEY (po_number),
|
|
FOREIGN KEY (cust_no) REFERENCES customer (cust_no),
|
|
FOREIGN KEY (sales_rep) REFERENCES employee (emp_no),
|
|
|
|
CHECK (NOT (order_status = 'shipped' AND ship_date IS NULL)),
|
|
|
|
CHECK (NOT (order_status = 'shipped' AND
|
|
EXISTS (SELECT on_hold FROM customer
|
|
WHERE customer.cust_no = sales.cust_no
|
|
AND customer.on_hold = '*')))
|
|
);
|
|
|
|
CREATE INDEX needx ON sales (date_needed);
|
|
CREATE INDEX salestatx ON sales (order_status, paid);
|
|
CREATE DESCENDING INDEX qtyx ON sales (item_type, qty_ordered);
|
|
|
|
SET TERM !! ;
|
|
|
|
CREATE TRIGGER post_new_order FOR sales
|
|
AFTER INSERT AS
|
|
BEGIN
|
|
POST_EVENT 'new_order';
|
|
END !!
|
|
|
|
SET TERM ; !!
|
|
|
|
COMMIT;
|
|
|
|
|
|
|
|
|
|
|
|
/****************************************************************************
|
|
*
|
|
* Create stored procedures.
|
|
*
|
|
*****************************************************************************/
|
|
|
|
|
|
SET TERM !! ;
|
|
|
|
/*
|
|
* Get employee's projects.
|
|
*
|
|
* Parameters:
|
|
* employee number
|
|
* Returns:
|
|
* project id
|
|
*/
|
|
|
|
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 !!
|
|
|
|
|
|
|
|
/*
|
|
* Add an employee to a project.
|
|
*
|
|
* Parameters:
|
|
* employee number
|
|
* project id
|
|
* Returns:
|
|
* --
|
|
*/
|
|
|
|
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
|
|
END !!
|
|
|
|
|
|
|
|
/*
|
|
* Select one row.
|
|
*
|
|
* Compute total, average, smallest, and largest department budget.
|
|
*
|
|
* Parameters:
|
|
* department id
|
|
* Returns:
|
|
* total budget
|
|
* average budget
|
|
* min budget
|
|
* max budget
|
|
*/
|
|
|
|
CREATE PROCEDURE sub_tot_budget (head_dept CHAR(3))
|
|
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 !!
|
|
|
|
|
|
|
|
/*
|
|
* Delete an employee.
|
|
*
|
|
* Parameters:
|
|
* employee number
|
|
* Returns:
|
|
* --
|
|
*/
|
|
|
|
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;
|
|
|
|
/*
|
|
* 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;
|
|
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;
|
|
END !!
|
|
|
|
|
|
|
|
/*
|
|
* Recursive procedure.
|
|
*
|
|
* Compute the sum of all budgets for a department and all the
|
|
* departments under it.
|
|
*
|
|
* Parameters:
|
|
* department id
|
|
* Returns:
|
|
* total budget
|
|
*/
|
|
|
|
CREATE PROCEDURE dept_budget (dno CHAR(3))
|
|
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 !!
|
|
|
|
|
|
|
|
/*
|
|
* Display an org-chart.
|
|
*
|
|
* Parameters:
|
|
* --
|
|
* Returns:
|
|
* parent department
|
|
* department name
|
|
* department manager
|
|
* manager's job title
|
|
* number of employees in the department
|
|
*/
|
|
|
|
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 !!
|
|
|
|
|
|
|
|
/*
|
|
* Generate a 6-line mailing label for a customer.
|
|
* Some of the lines may be blank.
|
|
*
|
|
* Parameters:
|
|
* customer number
|
|
* Returns:
|
|
* 6 address lines
|
|
*/
|
|
|
|
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_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 !!
|
|
|
|
|
|
|
|
/*
|
|
* Ship a sales order.
|
|
* First, check if the order is already shipped, if the customer
|
|
* is on hold, or if the customer has an overdue balance.
|
|
*
|
|
* Parameters:
|
|
* purchase order number
|
|
* Returns:
|
|
* --
|
|
*
|
|
*/
|
|
|
|
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 not 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;
|
|
END
|
|
|
|
/* Customer is on hold. */
|
|
ELSE IF (hold_stat = '*') THEN
|
|
BEGIN
|
|
EXCEPTION customer_on_hold;
|
|
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;
|
|
END
|
|
|
|
/*
|
|
* Ship the order.
|
|
*/
|
|
UPDATE sales
|
|
SET order_status = 'shipped', ship_date = 'NOW'
|
|
WHERE po_number = :po_num;
|
|
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 ; !!
|
|
|
|
/* Privileges */
|
|
|
|
GRANT ALL PRIVILEGES ON country TO PUBLIC WITH GRANT OPTION;
|
|
GRANT ALL PRIVILEGES ON job TO PUBLIC WITH GRANT OPTION;
|
|
GRANT ALL PRIVILEGES ON department TO PUBLIC WITH GRANT OPTION;
|
|
GRANT ALL PRIVILEGES ON employee TO PUBLIC WITH GRANT OPTION;
|
|
GRANT ALL PRIVILEGES ON phone_list TO PUBLIC WITH GRANT OPTION;
|
|
GRANT ALL PRIVILEGES ON project TO PUBLIC WITH GRANT OPTION;
|
|
GRANT ALL PRIVILEGES ON employee_project TO PUBLIC WITH GRANT OPTION;
|
|
GRANT ALL PRIVILEGES ON proj_dept_budget TO PUBLIC WITH GRANT OPTION;
|
|
GRANT ALL PRIVILEGES ON salary_history TO PUBLIC WITH GRANT OPTION;
|
|
GRANT ALL PRIVILEGES ON customer TO PUBLIC WITH GRANT OPTION;
|
|
GRANT ALL PRIVILEGES ON sales TO PUBLIC WITH GRANT OPTION;
|
|
GRANT EXECUTE ON PROCEDURE get_emp_proj TO PUBLIC WITH GRANT OPTION;
|
|
GRANT EXECUTE ON PROCEDURE add_emp_proj TO PUBLIC WITH GRANT OPTION;
|
|
GRANT EXECUTE ON PROCEDURE sub_tot_budget 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 org_chart TO PUBLIC WITH GRANT OPTION;
|
|
GRANT EXECUTE ON PROCEDURE mail_label 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 all_langs TO PUBLIC WITH GRANT OPTION;
|
|
|
|
|