Examples The following procedure, SUB_TOT_BUDGET, takes a department number as its input parameter, and returns the total, average, minimum, and maximum budgets of departments with the specified HEAD_DEPT.
/* Compute total, average, smallest, and largest department budget.
*Parameters:
* department id
*
*Returns:
* total budget
* average budget
* min budget
* max budget */
Código SQL
[-]
SET TERM !! ;
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;
EXIT;
END !!
SET TERM ; !!
The following select procedure, ORG_CHART, displays an organizational chart:
/* Display an org-chart.
*
* Parameters:
* --
* Returns:
* parent department
* department name
* department manager
* manager's job title
* number of employees in the department */
Código SQL
[-]
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 !!
When ORG_CHART is invoked, for example in the following isql statement:
it displays the department name for each department, which department it is in, the department manager's name and title, and the number of employees in the department.
Código:
HEAD_DEPT DEPARTMENT MGR_NAME TITLE EMP_CNT
===================== =================== ================ ==== =======
Corporate Headquarters Bender, Oliver H. CEO 2
Corporate Headquarters Sales and Marketing MacDonald, Mary S. VP 2
Sales and Marketing Pacific Rim Headquarters Baldwin, Janet Sales 2
Pacific Rim Headquarters Field Office: Japan Yamamoto, Takashi SRep 2
Pacific Rim Headquarters Field Office: Singapore --TBH-- 0