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

IN Parameters in action

   <source lang="sql">

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></source>

Mixed notation

   <source lang="sql">

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  /</source>
   
  

myProc procedure with a default value for both parameters

   <source lang="sql">

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></source>

myProc procedure with a default value for one parameter

   <source lang="sql">

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></source>

Named notation

   <source lang="sql">

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  /</source>