Ignorant Oracle's stored procedure 2

The "Ignorant Oracle stored process" has given you a lot of information about the development of stored procedures related to the basic knowledge, the author made the best efforts to summarize all the knowledge of the stored procedures, share to everyone and everyone to learn progress together. This article is not only completed in the previous article on the stored procedure call, test and other knowledge of the summary sharing, but also on the previous article stored procedure debugging error process (due to the limitations of knowledge and writing negligence, etc., before the error of the stored procedure is unavoidable, is constantly changing, If you can get everyone's correction will make this work faster and better, help people for the happiness of this!).

The following step into the topic, the introduction of this knowledge summary and sharing:

One, the call of the stored procedure (in the code sp_hll_test_20170415 this stored procedure can be seen in the ignorant Oracle stored procedures, please note that the use of the latest code).

1 Declare --If you do not need to define a parameter, you can remove this declare section and use only the Begin......end block2A Number;3 b date;4Cvarchar2( -);5D Number;6Status User_tables.status%type;7   --e varchar2 (20); too small in length8 /*9 It is important to note that when a parameter is a variable, the variable has a length limit that is not less than the maximum value that is reached in the stored procedure.Ten Otherwise, the long string cannot be stored in a variable of insufficient length, and the error is: One ora-06502:pl/sql: Numeric or value error: String buffer is too small A   */ -Evarchar2( $); - begin the  /* - when stored procedure calls, parameters can be constants, variables, or bound variables, which can be called by a positional call, named Call, or mixed call. - at the same time, the parameters and parameters are called without interference and can be mixed arbitrarily.  -  */  +   -   --Call Way 1: Locator Call +   /*  A the arguments and parameters correspond by position order one by one.  at The following example calls, the first parameter a value to the sp_hll_test_20170415 i_a, and so on.  -   */ - sp_hll_test_20170415 (A, B, C, D, status, e); -  -   --Call Way 2: named character call -   /* in in each parameter setting, you can specify parameters and arguments by using the format parameter-= argument, - In this way, the positional order of the parameters does not have to be consistent with the order specified by the stored procedure creation, and can be freely adjusted.  to in the following example, the parameter order is completely reversed.  +   */ -sp_hll_test_20170415 (io_e=E, I_status=Status, O_d=D, I_c=C, I_b=B, i_a=a); the    *   --sp_hll_test_20170415 (io_e = e, I_status = status, O_d = d, o_d = c, i_b = b, i_a = a); $   --when calling a named character, it is important to note that the parameter value is not repeated, such as the O_d parameter of the previous example, which results in the following exception:Panax Notoginseng   --PLS-00703: Multiple instances of a specified parameter in a list -  the   --Call Way 3: Locator + Name Call +   /* A The above two methods of invocation can be mixed, but note that once a parameter has the use of a named character, all subsequent arguments have to use the name, the that is, only the first x parameter is a locator, and the last y parameter is the name (x+y= total number of arguments).  + Doing so will result in an exception: "PLS-00312: A positional correlation parameter does not indicate its relevance" -   */ $Sp_hll_test_20170415 (A, B, io_e=E, I_c=C, I_status=Status, O_d=d); $  -   --parameter mode: constant + variable -A:=3; the   SelectSysdate intoB fromdual; -Sp_hll_test_20170415 (A, B,'I'm a constant C .'D'0', E); Wuyi    the   --sp_hll_test_20170415 (A, B, C, 2, status, e); -   --arguments, entry and exit parameters, cannot be constants, because they are assigned values Wu   --PLS-00363: expression ' 2 ' cannot be used as an assignment target -  About   --parameter mode: Bind variable $   /* - The binding variable, which is the colon + variable name, is also in contact with previous stored procedure learning, in Plsql - Test window, for the binding variable can be added in the "variable-type-value" area of the lower side of the window to delete the right key, etc., can be one by one set value, - At the same time, the entry and exit parameters will also be reflected in the "value" (Of course, in F9 debugging, not only bound variables, other variables can also be added, A view the values after each step).  + In Ps:plsql, right-clicking on the stored procedure name allows you to quickly open a "test window" with the "test" function in the right -click menu. the the method of calling stored procedure inside the window defaults to the bound variable + Locator call method, and there is a picture of the truth in the subsequent debugging tutorial.  -   */ $sp_hll_test_20170415 (io_e=: E1, I_status=: Status1, O_d=:d 1, I_a=: A1); the  the   --parameter mode: constant + variable + bound variable thesp_hll_test_20170415 (1, To_date ('2017-6-16','YYYY-MM-DD'), Io_e=E, I_c=C, I_status=: Status1, O_d=d); the  -   --default value is used in   --sp_hll_test_20170415 (A, B, status, e); the   /*  the When a stored procedure parameter contains a parameter with a default value, you can generally omit this parameter when you need to use the default value. About However, this is in conflict with the locator call, like the previous example call, omitted is the third to fourth bit argument, can be said to the locator character, the The parameter of the third to fourth bit is not less, the fifth to sixth bit is the parameter, and the fourth parameter is number, and the e variable is varchar2 ($) the This causes an exception: PLS-00306: The number of arguments or the type error when calling ' sp_hll_test_20170415 ' the  + so if you want to use a locator call method and you want to use the default value, you must be in the stored procedure definition, - put the parameter with the default value in the argument list at the end.  the   */Bayi   --or you can use the default value in the same way that a named character is called, as in the following example: thesp_hll_test_20170415 (io_e=: E1, I_status=Status, O_d=D, I_a=a); the  - End;

Second, the debugging of the stored procedure.

0. Debugging prerequisites, the user must have debugging permissions, otherwise "ora-01031:insufficient privileges" will find you trouble.

Workaround: Give this user permissions with an administrator account

1 Grant  any procedure  to users;

1. The "Test window" of a single stored procedure can be accessed in a quick manner.

Note: If the stored procedure can not be debugged, only display is executing, step into, exit and other buttons can not be used, can not break the point and so on, please check whether the database is load balanced, then the native TNS configured to the following case, a single node login:

1 Test_one_node =2 (Description =3 (address_list =4 # One of the machine's IP and port information5 (address = (protocol = TCP) (host = *.*.*.*) (port = *))6     )7 (Connect_data =8 (server = dedicated)9 (service_name = *)Ten # Show parameter instance_name or select instance_name from v$instance to view this value One (instance_name = *) A     )   -)

2. Alternatively, in a newly opened or existing "Test window", follow one example to write a variety of code for testing (even when testing a single stored procedure, "Create or replace procedure sp_hll_test_20170415 ..." To test this stored procedure, create the saved and defined Parameters section and replace it with the declare part of the example + add the missing parameter information. This approach and the "Add debugging Information" After this stored procedure is not much less than debugging inside the stored procedure, you can see preferences or scenes and other on-demand use.

3. Debugging needs to know (only some common items, others can explore themselves).

Step into + Single step exit = Single Step skip.

Snowball Club-Herili

Source: http://www.cnblogs.com/snowballed/

The copyright of this article is owned by the author and the blog Park, welcome reprint, but without the consent of the author must retain this paragraph, and in the article page obvious location to give the original link .
If in doubt, email ([email protected]) to inquire.

Ignorant Oracle's stored procedure 2