Stored Procedure experience 2

Source: Internet
Author: User

1. three methods to obtain the result: return value, select return record set

2.Compiling Process

1)Parameters

2) set environment: Display and set some environment switches: such as the locking method, set nocount on . Otherwise, the user environment is different, the running effect is affected.

3)Framework: Conceptual Structure

4)Variables: Put them in front and comment them out.

5)Note: Comment andCodeThe synchronization is complete and consistent.

6)Write

7)Discussion record

8)Test

3.Considerations

3.1Readability

1)Clear Logic

2)The statement should not be too complex. The temporary table transition should be generated.

3)Note

4)One screen visualization for easy thinking

5)Artistic Sense

3.2Efficiency

The execution efficiency of a method and statement must be considered. Note the following:

1)AvoidanceNot inQuery

2)Use cursor nesting with caution

3)Use of indexes: good at using indexes, greatly improving the speed. It must be explicitly stated in statements.

4) use transaction nesting with caution.

Program General 30 the main reason is that not in queries, nested cursors, and no indexes. After transformation, the same data volume 9 seconds.

4.Transactions

1)To maintain consistency, you must consciously consider when to use transactions.

2) pay attention to the locking feature and the locking attribute switch status. In Liaoyang, Informix the default locking return mechanism is not noticed, causing occasional crashes, the problem is very hidden.

3)Improper handling may lead to system deadlocks.

4)Test it carefully, otherwise the disaster will be irrecoverable.

5) the transaction recommendation method of a large number of statements embeds the stored procedure in the transaction, which can prevent unexpected errors and cause deadlocks. See Attachment 1 .

5. test

5.1 to build a test environment, you need to save a corresponding program because it will not end easily. See Attachment 3 .

5.2Writing and testing take the same time.

5.3Any changes must be tested.

5.4 Don't take it for granted, because there are problems with system software. For example, the error number status is the error of the last statement, Sqls 7.0 Of BUG: Statement" If @ error <> " never gets the expected result .

5.5Debugging tool

 

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.