Chapter 10, this chapter, this chapter describes how to use stored procedures. The stored procedure is a program of the database server side, which has two types. A similar to the SELECT query, used to retrieve data, and the retrieved data can be returned to the customer in the form of a dataset. Another similar to INSERT or DELETE query, does not return data, just performing an action. Some servers allow the same stored procedure to return data and perform actions. 10.1 Overview on different types of servers, the mode of operation is different. For example, for an Interbase server, it can return data in the form of an output parameter, and for other servers such as MicrosoftSQL Server and Sybase, data and information can be returned in the form of a data set. In Delphi 4, to access and manipulate the stored procedures on the server, you can use the TSTOREDPROC component or the TQuery component. As for which one is selected, how is the stored procedure itself written, how the data returns and uses which server. The TSTOREDPROC components and Tquery components are inherited from TDataSet. The TSTOREDPROC component is adapted to perform stored processes that do not need to return data and return information by outputting parameters. The Params property of the TSTOREDPROC component is used to manage these parameters, and the GetResults function of the TSTOREDPROC component can explicitly apply for return results. In summary, the TSTOREDPROC component is suitable for performing stored processes that do not need to return results or only by outputting the output parameter. The TQUERY component is suitable for performing stored procedures that can return the data set, including the stored procedure for returning the data set by output parameters on the InterBase server. Of course, the TQUERY member is also suitable for performing stored processes that do not need to return results or return results by output parameters. The parameters can be passed from the stored procedure to the client, or may be passed to the stored procedure by a client program, and the former is called an output parameter, and the latter is called an input parameter. For some servers, the output parameters can only pass a value, while the server allows the output parameters to pass a data set. 10.2 When you need a stored procedure If the server defines the stored procedure, you should decide whether to use the stored procedure as needed. The stored procedure is usually some tasks that are often executed, which are often done for a large number of records. Perform a stored procedure on the server to improve the performance of the application. This is because: The server often has powerful computing power and speed. Avoid downloading a lot of data to the client, reducing the amount of transmission on the network. For example, suppose one application needs to calculate a data, which needs to involve many records. If you do not use the stored procedure, download these data to the client, resulting in a sharp increase in traffic on the network. Not only that, the client may be an old-fashioned computer, which is very slow. After switching to the stored procedure, the server will quickly calculate the data, and simply pass a data to the client, the efficiency is very obvious. 10.3 How to use the stored procedure application how to use the stored procedure, depending on how the stored procedure itself is written, how the data returns and uses which server. 10.3.1 General steps using the stored procedure To access the stored procedures on the server, usually such a few steps: first step, put a TSTOREDPROC component on the form or data module. Step 2, set the DatabaseName property to specify a database, which can be set to the BDE alias or application-specific alias (if you connect the database with the TDATABASE component). In the third step, set the StoredProcName property to specify the name of the stored procedure. If the DatabaseName property is set correctly, you can select a stored procedure from a drop-down list.
Since different stored processes are often performed at runtime, StoredProcName properties are typically set at runtime. In the fourth step, click the Ovotible button on the edge of Params to open an editor. If the second step and third step are set correctly, all input and output parameters will be displayed in this editor, otherwise this editor is empty. To explain, not all servers can provide information about parameters. If the server does not provide information about parameters, you have to build these parameters yourself. 10.3.2 Preparing and executing the stored procedure Before performing the stored procedure, it is best to inform the server before you are ready, this is to call the prepare function of the TSTOREDPROC component, for example: storedProc1.prepare; Note: If the application changes parameters in the running period Information must be reused in the prepare function. To perform a stored procedure, you can call the execproc function of the TSTOREDPROC component. Sample example is as follows: StoredProc1.Params [0] .sstriguration: = edit1.text; storedproc1.prepare; StoredProc1.Execproc; Note: If you do not call Prepare before calling Execproc, The TSTOREDPROC component will automatically prepare the parameters. After the stored procedure is executed, the preparation is automatically canceled. However, if a stored procedure is repeatedly executed repeatedly, it is best to call prepare, and the unprepare function is called when the stored procedure is not required. After performing the stored procedure, it is possible to return such data: First, the data set can be displayed with standard data controls. Second, the output parameters. The third is status information. 10.4 Creating a stored procedure stored procedure is generally written in a special tool. However, what is it necessary to introduce how to create a stored procedure in the running period with a SQL statement. For different servers, even the stored procedures of the same function, the SQL statement may also be different, so the documentation of the server must be checked in advance. 10.4.1 Creating a stored procedure using the SQL statement To create a stored procedure using the SQL statement, you should use the SQL property of the TQUERY component. If you want to use the parameters during the stored procedure, you must set the ParamCheck property of the TQuery component to false. The following example demonstrates how to create a stored procedure with the SQL statement: with query1 dobegin paramcheck: = false; with sql dobeginclear; add ('create procedure get_max_emp_name'); add ('returns (max_name char (15))'); add ('As'); add ('SELECT MAX (Last_name)'); add ('from employee'); add ('INTO: max_name;'); add ('suspend;'); Add ('end'); end; execSql; end; of course, you can also create a stored procedure with SQL Explorer. 10.4.2 Retrieving the data set with the TQUERY component To retrieve the data set from the stored procedure with the TQuery component, you must set the SQL property correctly. In the SELECT statement, you want to replace the name of the table with the name of the stored procedure. If the stored procedure needs to pass the input parameters, the value of the parameter is enclosed in the stored proximity by the stored procedure. If there are multiple input parameters, there is a comma with a comma between each other.
For example, there is a stored procedure on the InterBase server called get_emp_proj, which needs to pass an input parameter called EMP_NO, and pass the execution result via an output parameter called Proj_ID. The following is the code for the stored procedure: CREATE PROCEDURE GET_EMP_PROJ (EMP_NO SMALLINT) RETURNS (PROJ_ID CHAR (5)) AS BEGIN FOR SELECT PROJ_ID FROM EMPLOYEE_PROJECT WHERE EMP_NO =: EMP_NO INTO: PROJ_ID DO SUSPEND; END Accordingly, through the above the storage Process retrieval data set, SQL statement can be written this: select * from get_emp_proj (52) 10.4.3 Retrieving data sets with TSTOREDPROC components To retrieve datasets from stored procedures from stored procedures, you must set the StoredProcName property to specify a name of a stored procedure. If the stored procedure needs to pass the input parameters, you can provide parameters via the params property or parambyname function. For example, there is a stored procedure on the Sybase server called get_employees, which has an input parameter called @EMP_NO. The following is the code for the stored procedure: CREATE PROCEDURE GET_EMPLOYEES @EMP_NO SMALLINT AS SELECT EMP_NAME, EMPLOYEE_NO FROM EMPLOYEE_TABLE WHERE (EMPLOYEE_NO = @EMP_NO) Accordingly, through the above process retrieves the stored data set, the program should be written: With StoredProc1 DoBeginClose; PARAMBYNAME ('@ Emp_no'). Assmallint: = 52; Active: = true; end; 10.4.4 Retrieving data with the TQuery component The data returned by the TQUERY component via the parameter is a record, even if the stored procedure has only one output parameter. Therefore, the application needs to retrieve the value of each field from the returned data. First, to replace the name of the table with the name of the store in the SELECT statement. If there are multiple output parameters, you can select some of the output parameters, or you can use an asterisk to indicate all output parameters. If the stored procedure needs to pass the input parameters, enclose the value of the parameters after the stored procedure is used. If there are multiple input parameters, there is a comma with a comma between each other. For example, there is a stored procedure on the InterBase server called get_high_emp_name, and returns the last_name field of the EMPLOYEE table via an output parameter called high_last_name. The following is the code for the stored procedure: CREATE PROCEDURE GET_HIGH_EMP_NAMERETURNS (High_Last_Name CHAR (15)) ASBEGINSELECT MAX (LAST_NAME) FROM EMPLOYEEINTO: High_Last_Name; SUSPEND; END Correspondingly, SQL statements, it should be written: SELECT High_Last_Name FROM GET_HIGH_EMP_NAME10.4.5 member by using TStoredProc Parameter retrieval data To retrieve data with the TSTOREDPROC component through the parameter, first set the StoredProcName property to specify a stored procedure. If the stored procedure needs to pass the input parameters, you can provide parameters via the params property or parambyname function.
After calling ExecProc executing the stored procedure, the output parameters can be accessed via the params attribute or parambyname function. For example, there is a stored procedure on the InterBase server called get_high_emp_name, and returns the last_name field of the Employee table via an output parameter called HIGH_LAST_NAME. The following is the code for the stored procedure: CREATE PROCEDURE GET_HIGH_EMP_NAMERETURNS (High_Last_Name CHAR (15)) AS BEGINSELECT MAX (LAST_NAME) FROM EMPLOYEE INTO: High_Last_Name; SUSPEND; END Accordingly, to retrieve data through the stored procedure above, the program should be written: with StoredProc1 DoBeginStoredProcName: = 'GET_HIGH_EMP_NAME'ExecProc; Edit1.Text: = ParamByName (' High_Last_Name ') AsString; End; 10.4.6 TQuery member performs an action with the stored procedure does not return any data, but they perform some action. For example, to delete a record, you can use the delete statement to delete records, or a stored procedure can also be performed. To perform an action with the TQUERY component, you need to include the name of the stored procedure to be executed in the SQL statement. If the stored procedure needs to pass the input parameters, enclose the value of the parameters after the stored procedure is used. If there are multiple input parameters, there is a comma with a comma between each other. For example, there is a stored procedure called add_emp_proj on the InterBase server to add a record to the Employee_Project table. The following is the code for the stored procedure: CREATE PROCEDURE ADD_EMP_PROJ (EMP_NO SMALLINT, PROJ_ID CHAR (5)) ASBEGINBEGININSERT INTO EMPLOYEE_PROJECT (EMP_NO, PROJ_ID) VALUES (: EMP_NO,: PROJ_ID); WHEN SQLCODE -530 DOEXCEPTION UNKNOWN_EMP_ID; ENDSUSPEND; END accordingly The SQL statement should write: Execute Procedure add_emp_proj (20, 'guide'); 10.4.7 Execute an action with a TSTOREDPROC component To perform an action with the TSTOREDPROC component, first set the StoredProcName property Specify a stored procedure. You can provide input parameters (if needed) via the params property or parambyname function. For example, there is a stored procedure called add_emp_proj on the InterBase server to add a record to the Employee_Project table. See the previous section, please refer to the previous section. To perform this stored procedure, the program should write: with storedproc1 dobeginstoredProcName: = 'add_emp_proj'; execProc; END; 10.5 Stored procedure parameters To perform stored procedures on the server, often to pass some parameters. These parameters are divided into four types: the first type is called input parameters, transmitted by the client program to the stored procedure. The second type is called an output parameter, and the result is returned from the client program. The third type is called an input / output parameter, which can be transmitted from the client program to the stored procedure or by the stored procedure to return a result. The fourth type is called a status parameter, and the error message is returned from the user program to the client program.
To explain, not all servers support the above four types of parameters, for example, InterBase does not support status parameters. The parameters of the stored procedure can be accessed through the params attribute of the TSTOREDPROC component (TPARAM object). If the StoredProcName property is set correctly at the design period, the parameters of the stored procedure will be automatically included in the params property, otherwise you need to create parameters yourself. 10.5.1 Input Parameter Input Parameters are used to transfer values to stored procedures from the client program, and the value is actually transmitted to the SQL statement during the stored procedure. If a stored procedure has an input parameter, you must assign a value to the input parameter before performing the stored procedure. If the stored procedure is performed with the TQuery component, the input parameters can be enclosed with a pair of parenthesis, separated from each other, just like the process of calling Object Pascal. For example, suppose to perform a stored procedure called get_emp_proj, it needs to pass an input parameter, its value is 52, the SQL statement is as follows: select proj_id from get_emp_proj (52) If the stored procedure is executed with the TSTOREDPROC component, you can pass the params attribute or parambyname function. Access each input parameter. To assign the input parameter before performing the stored procedure. For example, suppose to perform a stored procedure called get_emp_proj, it needs to pass an input parameter called EMP_NO, its data type is smallint, its value is 52, the corresponding program code should be written: with storedproc1 dobeginparambyname ('EMP_NO'). Assmallint: = 52; EXECPROC; END; 10.5.2 Output parameter output parameter is used to pass the result to the client by the stored procedure. The output parameter is assigned by the stored procedure, and the client can only access the value of the output parameter after executing the stored procedure. To access the value of the output parameter, you can pass the params attribute or parambyname function of the TSTOREDPROC component or the PARAMBYNAME function. For example, the following code displays the value of the output parameter to an edit box: with storedProc1 dobeginexecproc; edit1.text: = params [0] .sstring; end; Most stored procedures have one or more output parameters, output parameters You can return a separate value or return a dataset. Note: Some servers such as Informix may not provide information about parameters, only from the code of the stored procedure, there is no output parameters. 10.5.3 Input / Output Parameter Input / Output Parameters can be used by the customer program to store the stored procedure transmission value, or by the stored procedure to return the result, that is, the same parameters have both roles. As an input parameter, it must be assigned to it before the stored procedure is executed. As an output parameter, it can only be accessed after the stored procedure is performed. For example, there is a stored procedure in the Oracle server, and its IN_outVar parameter is an input / output parameter.
Code for the stored procedure is as follows: CREATE OR REPLACE PROCEDURE UPDATE_THE_TABLE (IN_OUTVAR IN OUT INTEGER) ASBEGINUPDATE ALLTYPETABLESET NUMBER82FLD = IN_OUTVARWHERE KEYFIELD = 0; IN_OUTVAR: = 1; END UPDATE_THE_TABLE; Accordingly, to execute the above storage process, the program code should be written : With storedProc1 dobeginparambyname ('in_outvar'). Asinteger: = 103; Execproc; Integer: = parambyname ('in_outvar'). Asinteger; END; 10.5.4 Status parameters In addition to returning data sets or output parameters, some stored procedures You can return a status parameter. The status parameter does not need to be assigned first, and only the value can be accessed after the stored procedure is executed. To access the value of the output parameter, you can pass the params attribute or parambyname function of the TSTOREDPROC component or the PARAMBYNAME function. For example, the following code Access byoutputParam parameters: datevar: = storedProc1.Parambyname ('byoutputParam'). Asdate; 10.5.5 How to access parameters in design period If the DatabaseName and StoredProcName properties are properly set in the design period, you can see To these parameters, for the input parameters therein, they can be set. However, some database servers do not provide parameter information for the stored procedure, in which case only SQLExplorer can only view the code of the stored procedure, find the name and type of the parameters, and then manually establish these parameters in the object viewer. To access the parameters in the design period, you can click the etholical button on the edge of the Params property to open the editor shown in Figure 10.1: Figure 10.1 Stored Procedure Parameters Click the button on the toolbar to create a new parameter, click the button You can delete a parameter, click the button to shift the order of the parameters, click the button to move the order of the parameters. Select one of the parameters, the object viewer will synchronize the properties of the parameter. Where the paramtype property must be set to specify the type of use of the parameter, you can set it to input, Output, Input / Output, or Result. DataType properties must also be set to specify the data type of the parameter. Note: For Oracle's stored procedures, to return data sets, you must set the DataType property to FTCursor. For input parameters or input / output, the value value must be set to the parameter. You cannot assign an output parameter and status parameter. 10.5.6 How to access parameters in the running period If the server does not provide information about parameters, you must establish a parameter yourself. At runtime, a parameter can be created via TParam's Create or TParams' addParam. For example, there is a stored procedure on the InterBase server called get_emp_proj, which has an input parameter called EMP_NO and an output parameter called Proj_ID.
