plsql - What is pass by reference in oracle? -

i read on internet , found in parameter in procedure pass reference. can please explain example? in advance.

to better understand, let's review how pl/sql handles parameters in 2 ways:

by reference
in case, pointer actual parameter passed corresponding formal parameter. both actual , formal parameters point same location in memory holds value of parameter.

by value
in case, value of actual parameter copied corresponding formal parameter. if program terminates without exception, formal parameter value copied actual parameter. if error occurs, changed values not copied actual parameter.

by default out , in out parameters passed value , in parameters passed reference. when out or in out parameter modified inside procedure procedure modifies copy of parameter value. when procedure has finished without exception result value copied formal parameter.

procedure add_numbers( first_val in number, second_val in out number ) begin second_val := first_val + second_val; --some other statements  end; / 

test:

begin par_in = 124; par_inout = 76; add_numbers(par_in, par_inout); dbms_output.put_line(par_inout); end; / 

the example above, print 200 on screen. there 2 parameters passed add_numbers procedure:

1. the first 1 par_in of mode in. parameter passed reference. hence, in add_numbers procedure, no other copy of parameter created. rather, same copy used. if allowed modify value first_val inside add_numbers procedure (but not), value reflect directly par_in. because both first_val , par_in 2 names same memory location.
2. the second 1 par_inout of mode in out. parameter passed value default. hence, in add_numbers procedure, new (memory location) allocated store copy of parameter. hence, par_inout name different location in memory second_val. 2 names 2 different locations.

the implication of second parameter that, after line second_val := first_val + second_val; executed, value of second_val different value of par_inout until end of procedure. @ end of procedure, value of second_val passed par_inout.

now, if pass large collection out or in out parameter passed value, in other words entire collection copied formal parameter when entering procedure , again when exiting procedure. if collection large can lead unnecessary cpu , memory consumption.

the nocopy parameter mode hint alleviates problem because can use instruct runtime engine try pass out or in out parameters reference instead of value

the hint requests pl/sql runtime engine pass in out argument reference rather value. can speed performance of programs, because by-reference arguments not copied within program unit. when pass large, complex structures collections, records, or objects, copy step can expensive.