18th Chapter-delphi Client Server application development (d) (3)

18.4.2.4 Stored Procedure Programming

A stored procedure is also a program on a SQL Server that receives input parameters, executes on the server side, and returns the results to the client, which must be explicitly invoked in the client application.

Stored procedures are useful for statistical and functional calculations of a large number of records in a database table, so that repetitive computing tasks can be converted to servers to improve the performance of database applications.

There are two components in Delphi that can operate stored procedures on a remote database server: Tquery and Tstoredproc.

1. tquery Stored Procedure Programming

The form in Csdemo that demonstrates calling a stored procedure with Tquery is Tfrmqueryproc.

There are two Tdbgrid parts in the Tfrmqueryproc. DBGRID1 displays the data in the employeetable. DBGRID2 displays the data in the project table. The tquery part of the stored procedure, named Employeeprojectsquery, is used to establish a connection between the employee table and the project table to enable the corresponding changes in the data in the DBGrid2 when the record changes in DBGrid1. The specific connection task is completed by the stored procedure get_emp_proj on the server. Here is the Get_emp_proj program:

PROCEDURE get_emp_proj

BEGIN

For SELECT proj_id

From Employee_project

WHERE Emp_no =: emp_no

Into:p roj_id

Todo

SUSPEND;

End

Emp_no INPUT SMALLINT

PROJ_ID OUTPUT CHAR (5)

The procedure takes two parameters:

Emp_no is an input parameter and the type is smallint.

PROJ_ID is an output parameter, the type is char (5)

Accordingly, the main attributes of the employeeprojectsquery are as follows:

Table 18. The value of the main property of the Employeeprojectsquery part

━━━━━━━━━━━━━━━━━━━━━━━━━━

Property property Value

──────────────────────────

DatabaseName Employeedemodb

Params emp_no (input parameters, smallint type)

SQL Select * FROM

Get_emp_proj (: Emp_no)

━━━━━━━━━━━━━━━━━━━━━━━━━━

The tquery part is a direct call to a stored procedure in an SQL statement.

The following are the client's programs:

Procedure Tfrmqueryproc.formshow (Sender:tobject);

Begin

DmEmployee.EmployeeTable.Open;

employeesource.enabled: = True;

With Employeeprojectsquery does if not Active then Prepare;

End

It is not necessary to explicitly prepare SQL statements with prepare, but you can optimize SQL execution.

Procedure Tfrmqueryproc.employeedatachange (Sender:tobject; Field:tfield);

Begin

Employeeprojectsquery.close;

Employeeprojectsquery.params[0]. Asinteger: =

DmEmployee.EmployeeTableEmp_No.Value;

Employeeprojectsquery.open;

Writemsg (' Employee ' + DmEmployee.EmployeeTableEmp_No.AsString +

' is assigned to ' + INTTOSTR (employeeprojectsquery.recordcount) +

' Project (s). ');

End

The event-handling process is associated with the Employeesource OnDataChange property. Used to revise the input parameters of the stored procedure and execute the SQL statement when the employeetable data record changes.

2. Tstoredproc Part's stored procedure programming

Tstoredproc Delphi is designed to use parts of a server stored procedure. The form in Csdemo that demonstrates calling a stored procedure with Tstoredproc is TFRMEXECPR

In the program running, when the Shiporder button is pressed, the contents of the fields such as ored_sta_tus are required to be modified to maintain the consistency of the database. The modification task for the field contents is completed by the stored procedure Ship_order on the server. The Ship_orde procedure is as follows:

PROCEDURE Ship_order

DECLARE VARIABLE Ord_stat CHAR (7);

DECLARE VARIABLE hold_stat CHAR (1);

DECLARE VARIABLE cust_no INTEGER;

DECLARE VARIABLE Any_po CHAR (8);

BEGIN

SELECT S.order_status, C.on_hold, C.cust_no

From sales s, customer C

WHERE Po_number =:p o_num

and s.cust_no = C.cust_no

Into:ord_stat,: Hold_stat,: cust_no;

IF (Ord_stat = "shipped") THEN

BEGIN

EXCEPTION order_already_shipped;

SUSPEND;

End

ELSE IF (hold_stat = "*") THEN

BEGIN

EXCEPTION Customer_on_hold;

SUSPEND;

End

For SELECT Po_number

From sales

WHERE Cust_no =: cust_no

and Order_status = "Shipped"

and paid = "n"

and Ship_date < ' now '-60

Into:any_po

Todo

BEGIN

EXCEPTION Customer_check;

UPDATE Customer

SET on_hold = "*"

WHERE cust_no =: cust_no;

SUSPEND;

End

UPDATE Sales

SET order_status = "Shipped", Ship_date = ' Now '

WHERE po_number =:p o_num;

SUSPEND;

End

Parameters:

Po_num INPUT CHAR (8)

The procedure takes only one input parameter: Po_num, and the type is char (8).

The Tstoreproc part that uses the procedure on the client is Shiporderproc, whose main properties are the following table:

Table 18.19 Shiporderproc The value of the main property of the part

━━━━━━━━━━━━━━━━━━━━━━━━━━━━

Property Name property value

────────────────────────────

DatabaseName Employeedemodb

Parambindmode Pbbyname

Params po_num (input parameter, string type)

Storedprocname Ship_order

━━━━━━━━━━━━━━━━━━━━━━━━━━━━

The client executes the Ship_order program as follows:

Procedure Tfrmexecproc.btnshiporderclick (Sender:tobject);

Begin

With Dmemployee do

Begin

Shiporderproc.params[0]. Asstring: = salestable[' Po_number '];

Shiporderproc.execproc;

Salestable.refresh;

End

End

This program is executed when the user presses the Shiporder button. In the program, first prepare the input parameters, using the Execproc side

Method executes the stored procedure. Call the Salestable.refresh method to refresh the data display.