Description: I originally moved all the stored procedures in the Interbase Chinese Development Guide, but considering the importance of stored procedures, triggers, I believe that most people have made considerable research, so In order to save space and time, I will delete all of the content, variable declarations, statement syntax, etc.; where transaction is handled, please refer to the previous post. Please understand. Interested in refer to the relevant information. First, use the stored procedure, the benefits of the trigger 1, perform complex operation 2 in the server side without the client side, can be shared by multiple clients (code sharing) 3, greatly reduce network traffic, Improve the network utilization 4, execute at the server, fast 5, can divide complex tasks into multiple simple business logic 6, stored procedures especially useful for certain tasks that require periodic processing, stored procedures at client And the server side provides good concurrency 8, the trigger is the basic tool 9 for maintaining data consistency, which is often used to provide unique main key value 10 to insert a new line, and the trigger is often used to save some status information. 11. Storage procedures and triggers are the powerful components 12 of the Interbase event warning mechanism. The stored procedure enhances the security of data. When the stored procedure and trigger store procedure: 1, no client information is required and in the server Operating operation 2, involving a large number of operations, operation 3, frequent, periodic operation 4, those operating triggers that are co-executed by multiple modules: 1, Before INSERT / UPDATE trigger can check if the operation is completed And the consistency of the data, or provide a value of the column that requires a unique number 2, the Before delete trigger can check the relationship between the primary lists when deleting, or terminate the deletion. 3. All Before triggers can return transactions by excitation anomalous to return transactions, all of the AFTER triggers can do some audits or change other related tables after the changes are completed. Third, the stored procedure is general, Interbase's stored procedures are divided into two categories, which are divided according to different methods used by the client during calling stored procedures. One is a selective stored procedure, and the client uses the SELECT statement to call the stored procedure. At this time, the function of the stored procedure seems to be like a table, the selection stored procedure must return one or more values (on the client's behavior like data line) Or errors or parameter information. Another category is the execution stored procedure, the client uses Excute Procedure to call the stored procedure, and most of this stored procedure only perform some actions without returning the value, of course it can also return some parameter information. When you create a stored procedure, you must plan in advance. Which class is prepared to prepare, which is a good habit that makes it possible to perform properly. If you are ready to use Select calls in the client side, comply with the rules of the selection stored procedure; if you want to use the Excute Procedure to call, follow the rules of the execution stored procedure. The type of stored procedure is different, SUSPEND, EXIT, END functionality is also different. The role of Suspend, Exit, and End is in the two types of stored procedures. 1, SUSPEND statement: When the stored procedure returns data to the client, Suspend temporarily stops the execution of the stored procedure, that is, the memory process is suspended until the client gets the return value, and then SUSPEND The statement continues to execute. The role of Suspend is to prevent the client from successfully acquiring the return value when data is returned to the client, so that the stored procedure may result in the end of the stored procedure. According to the above description, you can understand that when you use SELECT or SELECT INTO and other statements to return data (rows), you must follow the SUSPEND statement after the above statement.
During complex stored procedures, only SQL statements of the SELECT nature may be implemented, there may be executable statements such as INSERT, UPDATE, DELETE, at this time, in the selection stored procedure, all executable statements after the SUSPEND statement All is executed, although there is no value to return to the client, the last END statement terminates the execution of the entire stored procedure. InterBase is not recommended to use Suspend during the executable stored procedure. Because all statements after the SUSPEND statement will never be executed during the execution stored procedure, the program will go directly to the last END statement and return to the parameters to exit the stored procedure. So if you occasionally (or have to) did not comply with the interbase recommended rules used Suspend in the executable stored process, you have to generate unexpected results, you have to think about Suspend's usage. 2, EXIT statement: During the selective store, the exit statement directly jumps to the last END statement to terminate the execution of the stored procedure. Therefore, in the selection stored procedure, the most ideal case using the EXIT statement is that the execution of the stored procedure in advance is required to meet certain conditions. During the executable stored procedure, EXIT also jumps to the last ENND statement, indicating that the work of the stored procedure has been completed, and if there is a return parameter, return them to the client. 3, end statement: In the selection stored procedure, when executed to the last end statement, end the execution of the stored procedure, return the control to the client, and set SQLCode to 100. In the executable stored procedure, the last end statement also terminates the execution of the stored procedure while returning the output parameter to the client (if any). Therefore, SUSPEND, EXIT, END three statements can be concisely summarized as follows: SUSPEND is used to return a value of the data line property to the client; EXIT is used to end the execution of the program in advance; End is used to truly terminate stored procedures And return the output parameters to the client. Third, the stored procedure, event, exception, error handling 1, event: Interbase, a significant feature is that you can perform asynchronous message processing by using events in the stored procedure, trigger. It is believed that many people have already felt the urgency of the server-side and the client for asynchronous message processing. InterBase is stored in a stored procedure, which is allowed to use post_event to help us solve the above problems. The so-called event is actually a custom message identifier to implement asynchronous communication between the server and the client. The method of using the event is: Use post_event ("xxxxx") in the stored procedure or trigger and add a registration message to the Interbase's message management queue, which is the server side. On the client, first register the corresponding message, then after the server is sent, the client responds accordingly. It should be noted that if the server is initiated with multiple identical messages, the server only sends a message to the client instead of seizes all of these messages to the client. If you write the client's IBX to write the client, you can easily use the TibEvent component to perform message processing. 2, exception: An exception is an object of the InterBase database, which is actually a naming error. But must pay attention to and distinguish, the abnormality is not used to handle the Intebase database error (such as primary key constraint errors, etc.), it is only used to implement so-called business logic. For example, if you don't allow users to delete inventory items in the current active order, you can use the stored procedure to delete inventory, indicate an exception, indicating that you cannot delete. It can be seen that an exception is completely a business logic.
