The Sybase stored procedure is established and features that the stored procedure Sybase's stored procedure is a predefined translation 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 procedures 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, SQL Server query processor Analysis, the executive scheme stored in the system is formed after excluding grammatical errors. 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. The syntax rules of the stored procedure establish the syntax rules for the stored procedure are: create procedure [owner.] ProcedureName [; Number] [(] @Parameter_name Dattype [= default] [output] [, @ parameter_name dattype [= default] [Output] ] ... [)]] AS SQL_STATEMENTS The syntax rules using the stored procedure are: [EXECUTE] [@ return-status =] [[server.] Database.] Owner.] Procedurename [; number] [Number] [ [@ parameter_name =] value | [@Parameter_name =] @ varialbe [output] [, [@ parameter_name =] value | [@Parameter_name =] @ variable [output] ...] [with recompile] BRIDUTAL The common option for commands and the main points of establishing and using the stored procedure, please refer to the relevant manual for more detail for 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 generated by the following statement RS-LS-GZ-JINENG: CREINENG / * Skill Ware Table * / (Geren_ID Char (4), / * Personal Code * / RIQI SmallDateTime, / * Perform Date * / Yuanyin_id char (1) NULL, / * Change reason code * / jine smallmoney) / * Skills wage amount * / This table stores a historical file for skill salary 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 then call the stored procedure with batch statement P_RSGZ_JINENG_ALL performs query: 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 the change history of someone's skill salary, another stored procedure p_rsgz_jineng: crete procedure p_rsgz_jineng @c_gerenid char (4) AS SELECT * from RS_LS_GZ_JINENG WHERE Geren_ID = @ c_gerenid ORDER BY RIQi after batching statement call Stored procedure P_RS_GZ_JINENG inquiry: Declare @gerenid char (4) select @ gelenid = "0135" / * Settings to query employee's personal code to "0135" * / execute p_rsgz_jeneng @ c_gerenid = @ gerenid store procedure p_rsgz_jineng defines a shape @C_gelenid, 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 monthly salary, you must find the result of the currently recent skill salary from the salary history: create procedure p_rsgz_jineng_slt (@c_gerenid char (4), @ smallmoney output) AS SELECT @ smallmoney output Jine from RS_LS_GZ_JINENG WHERE RIQI = (SELECT MAX (RIQI) from RS_LS_GZ_JINENG WHERE Gelenid = @ c-gerenid) / * Find the current date * / call stored procedure P_RSGZ_JINENG_SLT in the history: Declare @gerenid char (4) @Jine SmallMoney Select @ Gerenid = "0135" / * Set the personal code to query employees "0135" * / select @ jine = 0 execute p_rsgz_jineng_slt @ c_gerenid = @ gerenid, @ sm_jine = @ jine output here, Variable @Jine The amount used to store procedure ginseng @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 * 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 a 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 @ rtNCode = 1 print "no this one!" stored procedure p_rsgz_jineng_rtn Returns a value stored in variable @errcode to the caller, which reflects the stored procedure to the caller The success or failure of the execution. 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!".
