Analysis of Oracle and SqlServer Stored Procedure debugging and error handling

In large databases, Procedure is often called because of development needs. Procedure is different in Oracle and Sqlserver due to different syntaxes, but in debugging, is relatively difficult, especially in some error handling.

First, debug:
1. for Oracle debugging, you can use a third-party tool, such as Pl/SQL Developer. the version I am using is 6.0.5.926. First, right-click the Procedure, add "add debug information" and select "test" to open a new test window. add test data in the corresponding Input and Output Variables below. Note: the data input here does not require quotation marks, and the output parameters do not need to be input. Then, click "start debugger" or press F9 to perform the test. You can select the test steps, such as "step, then we can see the intermediate variable in the script window below;
2. for SQL Server debugging, I have not found a good third-party tool. Currently, we use the method of inserting variable values or SQL statements in the middle into another table or directly printing them out. The first method requires the exec ('') method. Pay attention to the writing method of getting the variable value in it. refer to the following example:

Exec ('Update SupplyplanLack set Completedate = GetDate ()
From supplyPlanLack a where '+ @ ssTmp +' and
'+ @ ICompleteQty +'> = (Select s. RequestQty * a. RationQty as ReqQty
From Balance a, SupplyPlan s where a. Vehicle = s. Vehicle
And a. Part = s. Part and a. SupplyPlanNo = s. SupplyPlanNo
And '+ @ ssTmp + ')')

In print, you need to pay attention to the type conversion. It is generally implemented using the convert (varchar (11), @ spNOTo) method. Otherwise, a type conversion error is prompted!

3. For error handling, you can start to customize variables for Sqlserver, and then change the value according to different judgments and then introduce the method to handle the error. See the following example.

Set @ exec_num = 0

If (@ spNOFrom = 0 or @ spNOTo = 0 or @ reuseUser = NULL)
Begin
Set @ exec_num = 1
Goto the_end
End

The_end:
Return

Or this:

Set nocount on

If (@ property is null) or (@ property = '')
Begin
Raiserror ('ust specify a property name. ',-1,-1)
Return (1)
End

Or determine the value of the global variable @ error for the error.