Oracle PL/SQL - PL SQL Function Procedure Package IN Parameters

Introduction

IN parameters are used to pass values into the subprogram.

A variable serving as a formal parameter can be referenced inside the subprogram.

The scope of a formal parameter is the same as that of any local variable, but can't be changed.

The formal parameter of type IN is really a constant and works in only one direction from the main program to subprogram.

The actual parameter of the subprogram could be any PL/SQL element that contains a value:literal, constant, initialized variable, expression, result of function:

Demo

SQL>
SQL>-- w w  w. j a  va2  s.  c  o  m
SQL> create or replace function f_getArea_Nr (i_rad_nr NUMBER)
SQL> return NUMBER
SQL> is
SQL> begin
  2      if i_rad_nr is null            -- legal
  3      then
  4         -- i_rad_nr:=10;             -- ILLEGAL
  5         return null;
  6      end if;
  7      return 3.14*(i_rad_nr**2);  -- legal
  8  end;
  9  /

SQL>
SQL> declare
  2     v_out_nr NUMBER;
  3     v_in1_nr  CONSTANT NUMBER :=5;
  4     v_in2_nr   NUMBER :=4;
  5
  6     function f_getArea_Nr (i_rad_nr NUMBER)
  7     return NUMBER is
  8     begin
  9          return 3.14*(i_rad_nr**2);
 10     end;
 11  begin
 12      v_out_nr:=f_getArea_Nr(10);       -- literal
 13      v_out_nr:=f_getArea_Nr(v_in1_nr); -- constant
 14      v_out_nr:=f_getArea_Nr(v_in1_nr); -- variable
 15      v_out_nr:=f_getArea_Nr(2+3);      -- expression
 16      v_out_nr:=f_getArea_Nr(abs(2/3)); -- another function
 17  end;
 18  /

PL/SQL procedure successfully completed.

• Previous
• Next