Establishment and use of stored procedures in Sybase database

Sybase's stored procedures are pre-defined and compiled transactions that are centrally stored in SQL Server. Stored procedures consist of SQL statements and Process Control statements. Its functions include: Accept parameters, call another procedure, return a status value to the calling procedure or batch, indicate that the call succeeded or failed, return several parameter values to the calling procedure or batch, provide the caller with dynamic results, run in remote SQL Server, and so on.

The performance characteristics of stored procedures are as follows:

• The stored procedure is precompiled, which means that it differs from the normal SQL statement or the SQL statement of the batch, which is parsed by SQL Server's query processor when it first runs a stored procedure, and then forms the executable scheme stored in the system after the syntax error has been eliminated. Because most of the work on query processing has been completed, the stored procedure executes very fast.

• Stored procedures and pending data are placed on the same computer running SQL Server, and it is highly efficient to use stored procedures to query local data.

• Stored procedures are generally called by the client side through the name of the stored procedure, that is, the network transmission is only the name of the stored procedure and a small number of parameters (if any), rather than constitute a stored procedure of many SQL statements, so you can reduce the amount of traffic, speed up the system response.

• Stored procedures also have the convenience characteristic of being called and returned values like the C language child functions.

As a result, stored procedures greatly enhance the functionality, efficiency, and flexibility of the SQL language. Mastering and applying the stored procedure is of great significance to further exert the powerful function of Sybase database system.

Syntax rules for stored procedures

The syntax rules for establishing a stored procedure are:

　　　　　　　　CREATE PROCedure[owner.]procedurename[;number]
　　　　　　　　　　 [[(]@parameter_name datatype[=default][OUTput]
　　　　　　　　　　 [,@parameter_name datatype[=default][OUTput]]...[)]]
　　　　　　　　　　 [WITH RECOMPILE]
　　　　　　　　　　 AS SQL_statements

The syntax rules for using stored procedures are:

　　　　　　　　[EXECute][@return-status=]
　　　　　　　　　　 [[[server.]database.]owner.]procedurename[;number]
　　　　　　　　　　 [[@parameter_name=]value|[@parameter_name=]@varialbe[OUTput]
　　　　　　　　　　 [,[@parameter_name=]value|[@parameter_name=]@variable[OUTput]...]]
　　　　　　　　　　 [WITH RECOMPILE]

The following is a brief overview of the common options for these two commands, as well as the key points for establishing and using stored procedures, please refer to the manual for a more detailed description of the options.

· [[[Server.] Database.] Owner.] Procedure_name: The name of the stored procedure.

• @parameter_name Datatype[=default][output]: The name, type of the formal parameter (formal parameters). DF Ault is a given default value (optional), output specifies this parameter to be an input parameter (optional). A formal parameter is an argument in a stored procedure that can have multiple names that must begin with a @ and a maximum of 30 characters.

· Sql_statements: An SQL statement that defines the functionality of a stored procedure.

• @return_status: A variable that accepts a stored procedure return status value.

· [@parameter_name =]value: The actual argument (argument), @parameter_name is the name of the argument (optional). If an argument is provided in @parameter_name=value, then the subsequent arguments are also provided in this form.

· [@parameter_name =] @varialbe [OUTput]: Pass the value in the variable @varialbe as an argument to the formal parameter @parameter_name (optional) If the variable @varialbe is used to accept the returned parameter values. The option output is not missing.