Using Select Procedures in isql
Go Up to Using Stored Procedures
A select procedure is used in place of a table or view in a SELECT
statement and can return a single row or multiple rows.
The advantages of select procedures over tables or views are:
- They can take input parameters that can affect the output.
- They can contain logic not available in normal queries or views.
- They can return rows from multiple tables using
UNION
.
The syntax of SELECT
from a procedure is:
SELECT <col_list> from name ([param [, param …]])
WHERE <search_condition>
ORDER BY <order_list>;
The procedure <name> must be specified, and in isql each <param> is a constant passed to the corresponding input parameter. All input parameters required by the procedure must be supplied. The <col_list> is a comma-delimited list of output parameters returned by the procedure, or * to select all rows.
The WHERE
clause specifies a <search_condition> that selects a subset of rows to return. The ORDER BY
clause specifies how to order the rows returned. For more information on SELECT
, see the Language Reference.
The following code defines the procedure,
GET_EMP_PROJ,
which returns EMP_PROJ
, the project numbers assigned to an employee, when it is passed the employee number, EMP_NO
, as the input parameter.CREATE PROCEDURE GET_EMP_PROJ (EMP_NO SMALLINT)
RETURNS (EMP_PROJ SMALLINT) AS
BEGIN
FOR SELECT PROJ_ID
FROM EMPLOYEE_PROJECT
WHERE EMP_NO = :EMP_NO
INTO :EMP_PROJ
DO
SUSPEND;
END ;
The following statement selects from GET_EMP_PROJ
in isql:
SELECT * FROM GET_EMP_PROJ(24);
The output is:
PROJ_ID ======= DGPII GUIDE
The following select procedure, ORG_CHART
, displays an organizational chart:
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 ;
ORG_CHART
is invoked from
isql
as follows:
SELECT * FROM ORG_CHART;
For each department, the procedure displays the department name, the department’s “head department” (managing department), the department manager’s name and title, and the number of employees in the department.
HEAD_DEPT |
DEPARTMENT |
MNGR_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 |
ORG_CHART
must be used as a select procedure to display the full organization. If called with EXECUTE PROCEDURE
, then the first time it encounters the SUSPEND
statement, the procedure terminates, returning the information for Corporate Headquarters only.
SELECT
can specify columns to retrieve from a procedure. For example, if ORG_CHART
is invoked as follows:
SELECT DEPARTMENT FROM ORG_CHART;
then only the second column, DEPARTMENT
, is displayed.