Who Can Alter a Procedure
The ALTER PROCEDURE
statement can be executed by:
-
The owner of the stored procedure
-
Users with the
ALTER ANY PROCEDURE
privilege
The ALTER PROCEDURE
statement can be executed by:
The owner of the stored procedure
Users with the ALTER ANY PROCEDURE
privilege
ALTER PROCEDURE
ExampleGET_EMP_PROJ
stored procedure.ALTER PROCEDURE GET_EMP_PROJ (
EMP_NO SMALLINT)
RETURNS (
PROJ_ID VARCHAR(20))
AS
BEGIN
FOR SELECT
PROJ_ID
FROM
EMPLOYEE_PROJECT
WHERE
EMP_NO = :emp_no
INTO :proj_id
DO
SUSPEND;
END
CREATE OR ALTER PROCEDURE
Creates a stored procedure if it does not exist, or alters a stored procedure
DSQL
CREATE OR ALTER PROCEDURE procname [ ( [ <in_params> ] ) ]
[RETURNS (<out_params>)]
{<psql_procedure> | <external-module-body>}
!! See syntax of CREATE PROCEDURE
for further rules !!
The CREATE OR ALTER PROCEDURE
statement creates a new stored procedure or alters an existing one.If the stored procedure does not exist, it will be created by invoking a CREATE PROCEDURE
statement transparently.If the procedure already exists, it will be altered and compiled without affecting its existing privileges and dependencies.
CREATE OR ALTER PROCEDURE
ExampleGET_EMP_PROJ
procedure.CREATE OR ALTER PROCEDURE GET_EMP_PROJ (
EMP_NO SMALLINT)
RETURNS (
PROJ_ID VARCHAR(20))
AS
BEGIN
FOR SELECT
PROJ_ID
FROM
EMPLOYEE_PROJECT
WHERE
EMP_NO = :emp_no
INTO :proj_id
DO
SUSPEND;
END