Features of the stored procedure
Sybase's stored procedures are pre-defined transactions stored in SQL Server. The stored procedure consists of a SQL statement and a process control statement. Its features include: accept parameters; call another process; return a status value to the calling process or batch, indicate success or failure; return several parameter values to the calling process or batch, provide the caller with dynamic results; Run in remote SQL Server.
The performance characteristics of the stored procedure are as follows:
• The stored procedure is precompiled, which means that it is different from the ordinary SQL statement or batch SQL statement, when running a stored procedure for the first time, SQL Server's query processor analyzes it, and the syntax is excluded. After the error, the executable of the store is stored in the system. Since most of the work of query processing has been completed, the stored procedure is executed very fast.
· The stored procedure and the data to be processed are placed on the same time running SQL Server, using the stored procedure to query local data, the efficiency is naturally high.
· The stored procedure is generally mostly called by the client end through the name of the stored procedure, that is, the cross-network transmission is just the name and a small amount of parameters (if any), rather than constituting a number of SQL statements that make up the stored procedure, so it can be reduced Network transmission, speed up system response speed.
· The stored procedure has the convenient characteristics of modified and return values as the C language subunies.
Therefore, the stored procedure greatly enhances the function, efficiency and flexibility of SQL language. Mastering and applying a stored procedure, there is an important significance for further playback of the Sybase database system.
Syntax rules for stored procedures
The grammatical rules for establishing a stored procedure are:
Create Procedure [oowner.] ProcedureName [; Number]
[[] @Parameter_name DataType [= default] [OUTPUT]
[, @ parameter_name datatype [= default] [output]] ... [)]]
[With recompile]
AS SQL_STATEMENTS
The syntax rules for using the stored procedure 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 introduction to the common options of these two commands and the main points of establishing and using the stored procedure, please refer to the relevant manual for a more detailed description of the option.
[[[[[[[[Server.] Database.] Oer.] Procedure_name: The name of the stored procedure.
@ Parameter_name dattype [= default] [OUTPUT]: The name of the formal parameter (ginseng), type. DF AULT is a default value (optional), and Output specifies this parameter as an output parameter (optional). Conversion is the argument in the stored procedure, there can be multiple, the name must be @ head, up to 30 characters.
· SQL_STATEMENTS: Defines the SQL statement of the stored procedure function.
@ RETURN_STATUS: The variables that accept the stored procedure return status value.
· [@ Parameter_name =] value: actual parameter (inactive), @ parameter_name is the name of the argument (optional). If a strein is provided in @ parameter_name = value, then the subsequent streptologies are also available in this form.
· [@Parameter_name =] @ Varialbe [output]: Auto the value in the variable @varialbe is passed as a stream ginseng @Parameter_name (optional) if the variable @varialbe is used to accept the returned parameter value, the option Output is not lack.
The establishment and use of the stored procedure will be introduced through several examples.
Suppose there is a skill salary table with the following statement RS-LS-GZ-JINENG: CREATE TABLE RS_LS_GZ_JINENG / * Skill Ware Table * /
(Geren_id Char (4), / * Personal Code * /
RIQI SmallDateTime, / * Perform Date * /
Yuanyin_id Char (1) NULL, / * Change reason code * /
Jine SmallMoney) / * Skill Wage Amount * /
The table stores a historical file for a unit employee for many years.
Example 1. If you want to query all employees' skill salary changes history, you can create a stored procedure P-RSGZ-Jineg-all:
Create Procedure P_RSGZ_JINENG_ALL AS
SELECT *
From rs_ls_gz_jineng
Order by Gelenid, RIQI
The stored procedure P_RSGZ_JINENG_ALL is then called:
Execute p_rsgz_jineng_all
This example only shows the queryed data, no input, output parameters, is the simplest stored procedure.
Example 2. If you want to query a change history of someone's skill wage, 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
After the batch statement calls the stored procedure P_RS_GZ_JINENG to query:
Declare @gerenid char (4)
SELECT @ Gerenid = "0135" / * Set the personal code to find employees to "0135" * /
Execute p_rsgz_jeneng @ c_gerenid = @ GerenID
The stored procedure p_rsgz_jineng defines a formal parameter @c_gelenid, which is a character variable. In the batch of the process, the specific value can be used as an argument. When using variables (such as this example), you must use the DEL ARE statement to explain. It is worth noting that @ c_gelenid = @ gelenid is in the calling process statement in the batch, @ c_gerenid is the shape reference nature in the stored procedure p_rsgz_jineng, which is not a variable in the batch, so it cannot be included in the D ECLARE statement Variables.
Example 3. If you want to calculate the salary of the month, you must find out the results of the currently recent skill salary 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 out the current date in history * /
Call the stored procedure p_rsgz_jineng_slt to query:
Declare @gerenid char (4), @Jine Smallmoney
SELECT @ Gerenid = "0135" / * Set the personal code to find employees 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 of procedure-based @SM_JINE. In the calling process statement, @ SM_JIE = @Jine Output is invalidated. Otherwise, the variable @Jine will not be zero (equal to the initial value). Example 4. Isolated the skill salary of the personal code "0135", showing its historical record, and find an error message.
Create Procedure P_RSGZ_JINENG_RTN
@c_gerenid char (4)
AS
Declare @errcode smallint
SELECT @ errcode = 0
IF EXISTS (SELECT * ROM 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 @ gerenid = "0135"
SELECT @ rtNCode = 0
Execute @ rtNCode = p_rsgz_jineng_rtn @ c_gerenid = @ GerenID
IF @ rthcode = 1
Print "No this one!"
The stored procedure P_RSGZ_JINENG_RTN returns a value stored in the variable @errcode to the caller, which refers to the status of the stored procedure to the caller. In this example, if you do not find any records of the specified employee skill salary, it is considered "inspected no such person", and returns the error status value 1. Otherwise, the success status value is returned.
The batch statement of the calling process uses the variable @rtncode stores the returned state value, once the stored procedure P_RSG_ JINENG_RTN returns the error flag (@ RTNCode = 1), display a message "No this one!".
summary
The above four examples briefly describe several forms commonly used in stored procedures, which we can enjoy its programming characteristics and flexibility and convenience of use.
Although the above example is implemented with SQL batch statements when calling the stored procedure, it does not mean that this is the only way. For example, the phenomenon of calling a stored procedure (ie, so-called process nested) during the stored procedure is common. In addition, the stored procedure that calls Sybase in the Script statement of other Sybase database development systems (such as PowerBuilder) is also very common.
