Oracle PL/SQL Tutorial/Function Procedure Packages/IN Parameters

IN Parameters in action

SQL>
SQL> create or replace function getArea (i_rad NUMBER)
  2  return NUMBER is
  3  begin
  4      return 3.14*(i_rad**2);
  5  end;
  6  /
Function created.
SQL>
SQL> declare
  2     v_out NUMBER;
  3     v_in1  CONSTANT NUMBER :=5;
  4     v_in2   NUMBER :=4;
  5
  6  begin
  7      v_out:=getArea(10);       -- literal
  8      v_out:=getArea(v_in1); -- constant
  9      v_out:=getArea(v_in1); -- variable
 10      v_out:=getArea(2+3);      -- expression
 11      v_out:=getArea(abs(2/3)); -- another function
 12  end;
 13  /
PL/SQL procedure successfully completed.
SQL>
SQL>

Mixed notation

SQL>
SQL>
SQL> CREATE OR REPLACE PROCEDURE show_line(ip_line_length IN NUMBER,ip_separator IN VARCHAR2)
  2  IS
  3    myString VARCHAR2(150);
  4  BEGIN
  5
  6    FOR idx in 1..ip_line_length LOOP
  7
  8      myString := myString || ip_separator;
  9
 10    END LOOP;
 11
 12    DBMS_OUTPUT.PUT_LINE(myString);
 13
 14  EXCEPTION WHEN OTHERS THEN
 15
 16    dbms_output.put_line(SQLERRM);
 17  END;
 18  /
Procedure created.
SQL>
SQL>
SQL>
SQL>
SQL> DECLARE
  2
  3    v_length NUMBER :=50;
  4
  5    v_separator VARCHAR2(1):="=";
  6
  7  BEGIN
  8
  9    show_line(v_length,ip_separator=>v_separator);
 10
 11  END;
 12  /

myProc procedure with a default value for both parameters

SQL> CREATE OR REPLACE PROCEDURE myProc(ip_line_length IN NUMBER DEFAULT 50,ip_separator IN VARCHAR2 DEFAULT "=")
  2  IS
  3
  4    myString VARCHAR2(150);
  5
  6  BEGIN
  7
  8    FOR idx in 1..ip_line_length LOOP
  9
 10      myString :=myString ||ip_separator;
 11
 12    END LOOP;
 13
 14    DBMS_OUTPUT.PUT_LINE(myString);
 15
 16  EXCEPTION WHEN OTHERS THEN
 17    dbms_output.put_line(SQLERRM);
 18  END;
 19  /
Procedure created.
SQL>

myProc procedure with a default value for one parameter

SQL> CREATE OR REPLACE PROCEDURE myProc(ip_line_length IN NUMBER,ip_separator IN VARCHAR2 DEFAULT "=")
  2  IS
  3    myString VARCHAR2(150);
  4  BEGIN
  5
  6    FOR idx in 1..ip_line_length LOOP
  7
  8      myString :=myString ||ip_separator;
  9
 10    END LOOP;
 11
 12    DBMS_OUTPUT.PUT_LINE(myString);
 13
 14  EXCEPTION WHEN OTHERS THEN
 15
 16    dbms_output.put_line(SQLERRM);
 17  END;
 18  /
Procedure created.
SQL>
SQL>

Named notation

SQL>
SQL>
SQL> CREATE OR REPLACE PROCEDURE show_line(ip_line_length IN NUMBER,ip_separator IN VARCHAR2)
  2  IS
  3    myString VARCHAR2(150);
  4  BEGIN
  5
  6    FOR idx in 1..ip_line_length LOOP
  7
  8      myString := myString || ip_separator;
  9
 10    END LOOP;
 11
 12    DBMS_OUTPUT.PUT_LINE(myString);
 13
 14  EXCEPTION WHEN OTHERS THEN
 15
 16    dbms_output.put_line(SQLERRM);
 17
 18  END;
 19  /
Procedure created.
SQL>
SQL>
SQL>
SQL>
SQL> DECLARE
  2
  3    v_length NUMBER :=50;
  4
  5    v_separator VARCHAR2(1):="=";
  6
  7  BEGIN
  8
  9    show_line(ip_line_length=>v_length,ip_separator=>v_separator);
 10
 11  END;
 12  /