[Import] creation and use of Sybase stored procedures

Creation and use of Sybase stored procedures
2000-08-16 14:52:02

Features of Stored Procedures

Sybase stored procedures are pre-defined and compiled transactions centrally stored in SQL Server. Stored procedures consist of SQL statements and flow control statements. Its functions include accepting parameters, calling another process, and returning a status value to the call process or batch process, indicating that the call is successful or failed; return several parameter values for the call process or batch processing to provide dynamic results for the caller, and run in a remote SQL Server.

The performance characteristics of stored procedures are as follows:

· The stored procedure is pre-compiled, which means it is different from a common SQL statement or a batch-processed SQL statement. When a stored procedure is run for the first time, the SQL Server Query processor analyzes the SQL Server and generates an executable solution stored in the system after a syntax error is ruled out. Because most of the query processing has been completed, the storage process execution speed is very fast.

· Both stored procedures and data to be processed are stored on the same computer running SQL Server. Using Stored Procedures to query local data is naturally very efficient.

· A stored procedure is generally called by the client through the name of the stored procedure, that is, the name of the stored procedure and a small number of parameters (if any) are transmitted across the network ), instead of many SQL statements that constitute the stored procedure, you can reduce the amount of network transmission and speed up system response.

· The stored procedure also has the convenience of being called and returned values like C-language subfunctions.

Therefore, stored procedures greatly enhance the functions, efficiency, and flexibility of the SQL language. Mastering and applying the stored procedures is of great significance for further exerting the powerful functions of the Sybase Database System.

Stored Procedure syntax rules

The syntax rules for creating a stored procedure are as follows:

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 as follows:

[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 describes the common options of these two commands and the key points for creating and using stored procedures. For more details about the options, see the relevant manual.

· [[[Server.] database.] owner.] procedure_name: name of the stored procedure.

· @ Parameter_name datatype [= default] [Output]: name and type of the formal parameter (form parameter. DF ault is the default value (Optional). Output specifies this parameter as the output parameter (optional ). The parameter is an independent variable in the stored procedure and can contain multiple parameters. The name must start with @ and have a maximum of 30 characters.

· SQL _ statements: SQL statement that defines the stored procedure function.

· @ Return_status: the variable that accepts the status value returned by the stored procedure.

· [@ Parameter_name =] value: actual parameter (real parameter). @ parameter_name is the name of the real parameter (optional ). If a real parameter is provided in the form of @ parameter_name = value, the subsequent real parameters will also be provided in this form.

· [@ Parameter_name =] @ Varialbe [Output]: Pass the value in the variable @ Varialbe as a real parameter to the form parameter @ parameter_name (Optional). If the variable @ Varialbe is used to accept the returned parameter value, the output option is indispensable.

We will introduce the creation and use of stored procedures through several examples.

Suppose there is a skill payroll table RS-ls-GZ-jineng generated using the following statement:

Create Table rs_ls_gz_jineng/* skill payroll */

(Geren_id char (4),/* individualCode*/

Riqi smalldatetime,/* execution date */

Yuanyin_id char (1) null,/* cause code */

Jine smallmoney)/* skill salary */

This table stores historical records of employee skill wages for a certain unit over the years.

Example 1. If you want to query the skill salary change history of all employees, you can first create a stored procedure p-rsgz-jineg-ALL:

Create procedure p_rsgz_jineng_all

Select *

From rs_ls_gz_jineng

Order by gerenid, riqi

Then, use the batch processing statement to call the Stored Procedure p_rsgz_jineng_all for query:

Execute p_rsgz_jineng_all

In this example, only the queried data is displayed. It is the simplest stored procedure without input or output parameters.

Example 2. If you want to query the change history of a person's skill salary, you can create another Stored Procedure p_rsgz_jineng:

Create procedure p_rsgz_jineng @ c_gerenid char (4)

As

Select * From rs_ls_gz_jineng

Where geren_id = @ c_gerenid

Order by riqi

Then, call the Stored Procedure p_rs_gz_jineng using the batch processing statement for query:

Declare @ gerenid char (4)

Select @ gerenid = "0135"/* set the personal code of the employee to be queried to "0135 "*/

Execute p_rsgz_jeneng @ c_gerenid = @ gerenid

In the Stored Procedure p_rsgz_jineng, a parameter @ c_gerenid is defined, which is a struct variable. In batch processing that calls this process, you can use either a specific value or a variable as a real parameter. When using variables as real parameters (in this example), you must use the Del are statement to describe them. It is worth noting that in the call process statement of the batch processing, @ c_gerenid in @ c_gerenid = @ gerenid is the form parameter name in the Stored Procedure p_rsgz_jineng, not the variable in the batch processing, therefore, it cannot be included in the Variable list of the d eclare statement.

Example 3. If you want to calculate the current month's salary, you must find out the result of the latest skill wage change from the salary history:

Create procedure p_rsgz_jineng_slt

(@ C_gerenid char (4), @ sm_jine smallmoney output)

As

Select @ sm_jine = jine

From rs_ls_gz_jineng

Where riqi = (select max (riqi)

From rs_ls_gz_jineng

Where gerenid = @ C-gerenid)/* Find the date closest to the current date in the history */

Call the Stored Procedure p_rsgz_jineng_slt for query:

Declare @ gerenid char (4), @ jine smallmoney

Select @ gerenid = "0135"/* set the personal code of the employee to be queried to "0135 "*/

Select @ jine = 0

Execute p_rsgz_jineng_slt @ c_gerenid = @ gerenid, @ sm_jine = @ jine output

Here, the variable @ jine is used to store the amount returned by the process parameter @ sm_jine. In the call process statement, the output in @ sm_jie = @ jine output cannot be omitted. Otherwise, the variable @ jine will not get the value returned by the form parameter and always be zero (equal to the initial value ).

Example 4: the employee's skill salary with the personal code "0135" is displayed as a historical record. If the employee's skill salary is not found, an error message is displayed.

Create procedure p_rsgz_jineng_rtn

@ C_gerenid char (4)

As

Declare @ errcode smallint

Select @ errcode = 0

If exists (select * from RS-ls-GZ-jineng

Where gerenid = @ C-gerenid)

Begin

Select *

From rs_ls_gz_jineng

Whrer geren_id = @ c_gerenid

Order by riqi

Return @ errcode

End

Esle

Begin

Select @ errcode = 1

Return @ errcode

End

Call the Stored Procedure p_rsgz_jineng_rtn:

Declare @ gerenid char (4), @ rtncode smallint

Select @ maid = "0135"

Select @ rtncode = 0

Execute @ rtncode = p_rsgz_jineng_rtn @ c_gerenid = @ gerenid

If @ rtncode = 1

Print "No this one! "

The stored procedure p_rsgz_jineng_rtn returns a value stored in the variable @ errcode to the caller. This value is called a status value, which reflects the success or failure of the stored procedure execution to the caller. In this example, if no records of the specified employee skill salary are found, the system considers "No such person is found" and Returns Error status value 1. Otherwise, the success status value is 0.

The batchcompute statement of the call process uses the variable @ rtncode to store the returned status value. Once the Stored Procedure p_rsg _ jineng_rtn is detected, an error mark (@ rtncode = 1) is returned ), A message "No this one!" is displayed! ".

Summary

The above four examples briefly introduce several common forms of stored procedures, from which we can see their programming features and the flexibility and convenience of use.

Although the above example is implemented by using the SQL batch processing statement when calling the stored procedure, it does not mean this is the only method. For example, it is common to call a stored procedure (namely, process nesting) in a stored procedure. In addition, it is also common to call Sybase stored procedures in script statements of other sybase database development systems (such as PowerBuilder.

From CCID/Text
Http://tech.china.com/zh_cn/netschool/programme/sybase/4021/20000816/220629.htm

ArticleSource: http://computer.mblogger.cn/wucountry/posts/41603.aspx