Oracle stored procedure-parameter type research (in, out, in out)

########################### In ############ ################

Create or replace procedure pro_eight (p_one in varchar2, p_two out varchar2, p_three in out varchar2) is v_str1 varchar2 (32): = ''; v_str2 varchar2 (32) default ''; v_str3 varchar2 (32); begin dbms_output.put_line ('parameter: '| 'P _ One:' | p_one | ', p_two:' | p_two | ', p_three: '| p_three); -- The in type variable cannot receive the value p_one: = 'one changed'; -- print the variable dbms_output.put_line ('variable: '| 'v _ str1: '| v_str1 |', v_str2: '| v_s Tr2 | ', v_str3:' | v_str3); Exception when others then dbms_output.put_line ('exception! '); End pro_eight;

 

############################ Out ############ ################

Create or replace procedure pro_eight (p_one in varchar2, p_two out varchar2, p_three in out varchar2) is v_str1 varchar2 (32): = ''; v_str2 varchar2 (32) default ''; v_str3 varchar2 (32); begin dbms_output.put_line ('parameter: '| 'P _ One:' | p_one | ', p_two:' | p_two | ', p_three: '| p_three); -- in type variables cannot receive values -- p_one: = 'one changed'; -- The out type can only receive values, but cannot assign values to other variables. -- The Value assignment statement does not report errors during compilation and execution, but the variable to be assigned is empty and no value is received. V_str2: = p_two; dbms_output.put_line ('variable: '|', v_str2: '| v_str2); -- print the variable dbms_output.put_line ('variable:' | 'V _ str1: '| v_str1 |', v_str2: '| v_str2 |', v_str3: '| v_str3); Exception when others then dbms_output.put_line ('exception! '); End pro_eight;

 

 

########################### In out ########### #################

Create or replace procedure pro_eight (p_one in varchar2, p_two out varchar2, p_three in out varchar2) is v_str1 varchar2 (32): = ''; v_str2 varchar2 (32) default ''; v_str3 varchar2 (32); begin dbms_output.put_line ('parameter: '| 'P _ One:' | p_one | ', p_two:' | p_two | ', p_three: '| p_three); -- in type variables cannot receive values -- p_one: = 'one changed'; -- The out type can only receive values, but cannot assign values to other variables. -- The Value assignment statement does not report errors during compilation and execution, but the variable to be assigned is empty and no value is received. -- V_str2: = p_two; -- dbms_output.put_line ('variable: '|', v_str2: '| v_str2); -- values can be assigned to the In out type, you can also assign v_str3: = p_three; v_str3: = v_str3 | 'changed '; dbms_output.put_line ('variable:' | 'v _ str3: '| v_str3) to other variables ); p_three: = v_str3 | 'again. '; dbms_output.put_line ('parameter:' | 'P _ Three: '| p_three); -- print the variable dbms_output.put_line ('variable:' | 'v _ str1: '| v_str1 |', v_str2: '| v_str2 |', v_str3: '| v_str3); Exception when Others then dbms_output.put_line ('exception! '); End pro_eight;

 

 

 

@@@@@@@@@@@@@@@@@@@

Differences between in-out parameters during testing

 

Create or replace procedure pro_nine (p_one in varchar2) is v_str1 varchar2 (32): = ''; begin dbms_output.put_line ('parameter: '| 'P _ One: '| p_one); v_str1: = p_one; -- variable value -- v_str2 => p_one; -- error, => is only used to pass parameters to the parameter list during the call. Dbms_output.put_line ('variable: '| 'v _ str1:' | v_str1); Exception when others then dbms_output.put_line ('exception! '); End pro_nine;

 

SQL windows call

Call pro_nine ('11'); -- exec pro_nine ('11'); -- an error is returned. This is not an SQL statement. It is a command and should be executed in the Command window.

 

Command window call

Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 Connected as uddrb@MYORCLSQL> exec pro_nine('11');PL/SQL procedure successfully completedSQL> 

 

If there are out and in out parameters, you cannot directly pass the parameter. You must input the variable.

Create or replace procedure pro_nine (p_one in varchar2, p_two out varchar2) isbegin dbms_output.put_line ('parameter:' | 'P _ One: '| p_one); p_two: = p_one | 'plus one. '; dbms_output.put_line (' parameter: '| 'P _ TWO:' | p_two); Exception when others then dbms_output.put_line ('exception! '); End pro_nine;

 

 

 

SQL windows call

-- The out parameter must be bound to the variable declare v_res varchar2 (32); begin pro_nine ('11', v_res); dbms_output.put_line ('result: '| 'v _ Res: '| v_res); end;

The code is correctly executed here, but to see the print statement, you need to put the code to test winddow for execution.

Command window call

Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 Connected as uddrb@MYORCLSQL> var v_str varchar2(32);SQL> exec :v_str :='222';PL/SQL procedure successfully completedv_str---------222SQL> exec pro_nine('111',p_two => :v_str);PL/SQL procedure successfully completedv_str---------111  plus one.SQL> 

 

Pay attention to the declaration of variables and the assignment method, as well as the parameter passing method.

In command window mode, the variable declaration var v_str varchar2 (32 );

Execute the Stored Procedure statement using the EXEC command.

When using the variable, you must add a colon: v_str.

For example:

Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 Connected as uddrb@MYORCLSQL> var v_str varchar2(32):='222';SQL> exec dbms_output.put_line('v_str：'||:v_str);PL/SQL procedure successfully completedv_str---------SQL> 

The above value is not printed, indicating that the assignment is incorrect. Should be used

Exec: v_str: = '000000 ';
 

Parameter List parameters use => to specify variables.

Exec pro_nine ('20140901', p_two =>: v_str );

 

----------------------------------------

In conclusion, I will not write any more. Some of my own experiments are better than reading ten books.