Filter
Top Products
CHAPTER 6 Using Procedures and Batches
233
Permissions to execute procedures
A procedure is owned by the user who created it, and that user can execute it
without permission. Permission to execute it can be granted to other users using
the GRANT EXECUTE command.
For example, the owner of the procedure
new_dept could allow another_user to
execute
new_dept with the statement:
GRANT EXECUTE ON new_dept TO another_user
The following statement revokes permission to execute the procedure:
REVOKE EXECUTE ON new_dept FROM another_user
For more information on managing user permissions on procedures, see
“Granting permissions on procedures” on page 361.
Returning procedure results in parameters
Procedures can return results to the calling environment in one of the following
ways:
• Individual values are returned as OUT or INOUT parameters.
• Result sets can be returned.
• A single result can be returned using a RETURN statement.
This section describes how to return results from procedures as parameters.
The following procedure on the sample database returns the average salary of
employees as an OUT parameter.
CREATE PROCEDURE AverageSalary( OUT avgsal
NUMERIC (20,3) )
BEGIN
SELECT AVG( salary )
INTO avgsal
FROM employee;
END
To run this procedure and display its output from DBISQL, carry out the
following steps:
1 Connect to the sample database from DBISQL as user ID DBA using
password SQL.
2 Create the procedure.