Oracle stored procedure-getting started and debugging

Oracle stored procedure-getting started and debugging

Example:

Create or replace procedure pro_one (p_one in number, p_two out varchar2, p_three in out varchar2) is -- as -- variable declaration part v_str1 varchar2 (32): = 'initial Value Method 1 '; v_str2 varchar2 (32) default 'initial Value Method 2'; v_str3 varchar2 (32); -- null value v_num4 number default 0; -- part of the code block/* The code block must have at least one executable code, otherwise, an error is returned. If nothing is done, null can be written. */begin dbms_output.put_line ('Passing parameter: '| 'P _ One:' | p_one); dbms_output.put_line ('variable: '| v_str1 |', '| v_str2 | ',' | V_str3 | ',' | v_num4); p_three: = p_three | 'changed '; select count (*) into v_num4 from lsy_codebook where id = p_one; if v_num4 <= 0 then begin p_two: = ''; p_three: = 'database does not have this record! '; Dbms_output.put_line ('the database does not have this record! '); End; elsif v_num4> 1 then raise too_many_rows; -- throw an exception else begin update lsy_codebook set description = p_three where id = p_one; commit; p_two: = p_three; p_three: = p_one | 'Update SUCCESS '; end if; -- Exception capture exception when no_data_found then begin dbms_output.put_line (' not found, update failed. '); p_three: = 'exception info:' | p_one | 'not found, update failed. '; end; When too_many_rows then begin dbms_output.put_line ('too _ many_rows, update failed. '); p_three: = 'exception info:' | p_one | 'too_many_rows, update failed. '; end; when others then begin rollback; p_three: = 'exception info:' | p_one | 'oss exception, update failed. '; end pro_one;

The definition structure of a stored procedure consists of three parts. First, the declaration part. Here, the initial resume stored procedure is best to use create procedure XXX, because once your name already exists in the database, you will overwrite the existing one. This probability is very small, but the error is very low. Create or replace procedure XXX after creation. Second, the variable declaration part. This part does not exist, if you do not need the variable. Compared with Java classes, this part is like declaring global variables. The third part is the code block. Between the names of the in end stored procedures. In the stored procedure, we use begin and end to replace our habits {}. I personally think {} is better, but the Oracle team has their own ideas.

Note that the Stored Procedure method does not have any parameters, such as create or replace procedure pro_one is. The input and output types of parameters include in, out, and in out. Simply put, an in-type parameter has a value when it comes in. It can only be read but cannot be assigned a value. The out type can only be assigned a value and cannot be assigned to other variables. The in out type includes the above two features. Why is it designed like this? Compared with Java, stored procedures have parameters, so they are like methods. The returned values of stored procedures do not exist. Therefore, they cannot be obtained using equal signs in Java methods. Therefore, the returned values can only be assigned to parameter variables to save the results. In the parameter list, the parameter type cannot have a length definition. In the variable declaration block, the variable must have a length definition.

The syntax is very simple. This article mainly describes how to use PL/SQL for debugging.

You can use SQL windows to create a stored procedure. It is best to use program window. Alternatively, right-click procedures in the object panel and choose new. After the program is created, an error message (right-click Edit) is displayed when it is opened using the program window ).

After F8 is executed, an error or warning will be prompted.

Procedure for debugging a stored procedure:

Right-click Add debug infomation. Right-click test. The test window is displayed.

Run the gear or F9 in the upper-left corner.

The variable is listed below. We can enter the variable value below to see the variable name in the Code pointing to below.

This is a convenient function provided by PLSQL. We can pass in the code without using its own life variables.

Breakpoint settings;

You can click on the left of the code line to add a breakpoint as needed (the breakpoint is red and the bookmarks are green ).

Or right-click Set breakpoint.

For the above input, we can look at the print and variable results.

Now we do not need to associate PLSQL variables with input. We can customize variables in the code to test.

 

Declarep_one number: = 501; p_two varchar2 (32): = ''; p_three varchar2 (32): = 'new value 333 '; begin -- call the procedure -- NULL; dbms_output.put_line ('before calling: p_one: '| p_one |', p_two: '| p_two |', p_three: '| p_three); pro_one (p_one, p_two, p_three); dbms_output.put_line ('call: p_one: '| p_one |', p_two: '| p_two |', p_three: '| p_three); end;

The result display has nothing to do with the following Variable list. We can view the information from the print: