User's Guide

Part III. Using SQL Anywhere

Chapter 20. Using Procedures, Triggers, and Batches

In order to use procedures you need to understand how to do the following:

• Create procedures
• Drop, or remove, procedures
• Call procedures from a database application
• Control who has permission to use procedures

This section discusses each of these aspects of using procedures, and also describes some of the different uses of procedures.

Creating procedures

Procedures are created using the CREATE PROCEDURE statement. You must have RESOURCE authority in order to create a procedure.

The following simple example creates a procedure that carries out an insert into the department table of the sample database, creating a new department.

You can create the example procedure new_dept by connecting to the sample database from the ISQL utility as user ID DBA, using password SQL, and typing the statement in the command window.

If you are using a tool other than ISQL or Sybase Central, you may need to change the command delimiter away from the semicolon before entering the CREATE PROCEDURE statement.

     CREATE PROCEDURE new_dept ( IN id INT,
     IN name CHAR(35),
     IN head_id INT )
     BEGIN
     INSERT
     INTO "dba".department ( dept_id,
     dept_name,
     dept_head_id )
     VALUES ( id, name, head_id );
     END

  For a complete description of the CREATE PROCEDURE syntax, see "CREATE PROCEDURE statement".

The body of a procedure is a compound statement (see "Using compound statements"). The compound statement starts with a BEGIN statement and concludes with an END statement. In the case of new_dept, the compound statement is a single INSERT bracketed by BEGIN and END statements.

Parameters to procedures are marked as one of IN, OUT, or INOUT. All parameters to the new_dept procedure are IN parameters, as they are not changed by the procedure.

Calling procedures

A procedure is invoked with a CALL statement (see "CALL statement".) Procedures can be called by an application program or they can be called by other procedures and triggers.

The following statement calls the new_dept procedure to insert an Eastern Sales department:

     CALL new_dept( 210, 'Eastern Sales', 902 );

After this call, you may wish to check the department table to see that the new department has been added.

To list all departments:

• Type the following:

       SELECT *
       FROM department
  

dept_id	dept_name	dept_head_id
100	R & D	501
200	Sales	902
300	Finance	1293
400	Marketing	1576
500	Shipping	703
210	Eastern Sales	902

The new_dept procedure can be called by any user who has been granted EXECUTE permission for the procedure, even if they have no permissions on the department table.

Dropping procedures

Once a procedure is created, it remains in the database until it is explicitly removed. Only the owner of the procedure or a user with DBA authority can drop the procedure from the database.

The following statement removes the procedure new_dept from the database:

     DROP PROCEDURE new_dept

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".

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 to the procedure
• 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
     DECIMAL(20,3) )
     BEGIN
     SELECT AVG( salary ) INTO avgsal FROM employee;
     END

To run this procedure and display its output from the ISQL utility, carry out the following steps:

1. Connect to the sample database from the ISQL utility as user ID DBA using password SQL.
2. Create the procedure.
3. Create a variable to hold the procedure output. In this case, the output variable is numeric, with three decimal places, so create a variable as follows:

        CREATE VARIABLE Average NUMERIC(20,3)
   

4. Call the procedure, using the created variable to hold the result: CALL AverageSalary(Average) The ISQL statistics window displays the message "Procedure completed" if the procedure was created and run properly.
5. Look at the value of the output variable Average. The ISQL data window displays the value 49988.623 for this variable; the average employee salary.

Returning procedure results in result sets

In addition to returning results to the calling environment in individual parameters, procedures can return information in result sets. A result set is typically the result of a query. The following procedure returns a result set containing the salary for each employee in a given department:

     CREATE PROCEDURE SalaryList ( IN department_id INT)
     RESULT ( "Employee ID" INT, "Salary" NUMERIC(20,3) )
     BEGIN
         SELECT emp_id, salary
         FROM employee
         WHERE employee.dept_id = department_id;
     END

If called from ISQL, the names in the RESULT clause are matched to the results of the query and used as column headings in the displayed results.

To test this procedure from within ISQL you can CALL it, specifying one of the departments of the company. The results are displayed in the ISQL data window. For example:

To list the salaries of employees in the R & D department (department ID 100):

• Type the following:

       CALL SalaryList (100)
  

Employee ID	Salary
102	45700.000
105	62000.000
160	57490.000
243	72995.000
247	48023.690

To execute a CALL of a procedure that returns a result set, ISQL opens a cursor.

The cursor is left open after the CALL in case a second result set is returned. The ISQL statistics window displays the plan of the SELECT query in the procedure and then displays the line:

     Procedure is executing. Use RESUME to continue.

You need to execute the RESUME statement from the ISQL command window before you can, for example, DROP the procedure. Alternatively, the ISQL CLEAR statement clears the window.

  For more information about using cursors in procedures, see "Using cursors in procedures and triggers".