Oracle PL/SQL/Stored Procedure Function/Default Parameter Value

Demonstrates default parameters.

    
SQL> CREATE TABLE book (
  2    isbn      CHAR(10) PRIMARY KEY,
  3    category  VARCHAR2(20),
  4    title     VARCHAR2(100),
  5    num_pages NUMBER,
  6    price     NUMBER,
  7    copyright NUMBER(4),
  8    emp1   NUMBER,
  9    emp2   NUMBER,
 10    emp3   NUMBER
 11  );
Table created.
SQL>
SQL> CREATE OR REPLACE PROCEDURE AddNewBook(
  2    p_ISBN IN book.ISBN%TYPE,
  3    p_Category IN book.category%TYPE := "Oracle Server",
  4    p_Title IN book.title%TYPE,
  5    p_NumPages IN book.num_pages%TYPE,
  6    p_Price IN book.price%TYPE,
  7    p_Copyright IN book.copyright%TYPE DEFAULT TO_NUMBER(TO_CHAR(SYSDATE, "YYYY")),
  8    p_emp1 IN book.emp1%TYPE,
  9    p_emp2 IN book.emp2%TYPE := NULL,
 10    p_emp3 IN book.emp3%TYPE := NULL) AS
 11
 12  BEGIN
 13    INSERT INTO book (isbn, category, title, num_pages, price,copyright, emp1, emp2, emp3)
 14    VALUES (p_ISBN, p_Category, p_Title, p_NumPages, p_Price,p_Copyright, p_emp1, p_emp2, p_emp3);
 15  END AddNewBook;
 16  /
Procedure created.
SQL>
SQL> BEGIN
  2    AddNewBook("0", "Database", "A Book",5, 34.99, 2004, 1);
  3  END;
  4  /
PL/SQL procedure successfully completed.
SQL>
SQL> drop table book;
Table dropped.

Parameter with default NULL value

  
SQL>
SQL> CREATE OR REPLACE PROCEDURE CallProc1(p1 IN VARCHAR2 := NULL) AS
  2  BEGIN
  3    DBMS_OUTPUT.PUT_LINE("CallProc1 called with " || p1);
  4  END CallProc1;
  5  /
Procedure created.
SQL>
SQL> CALL CallProc1("Hello!");
CallProc1 called with Hello!
Call completed.
SQL> CALL CallProc1();
CallProc1 called with
Call completed.
SQL>
SQL>

Parameter with default value

    
SQL> create table myTable(
  2    myKey  primary key,
  3    myValue
  4  )as
  5  select object_name, max( owner||"_"||object_id )from all_objects group by object_name
  6  /
Table created.
SQL>
SQL> create table myTableCopy as select * from myTable where 1=0;
Table created.
SQL>
SQL> create or replace procedure row_fetch_row_select( p_arraysize in number default 100 )
  2  as
  3      l_myKey myTableCopy.myKey%type;
  4      l_myValue myTableCopy.myValue%type;
  5      cursor c is select object_name from all_objects;
  6  begin
  7      open c;
  8      loop
  9          fetch c into l_myKey;
 10          exit when c%notfound;
 11          begin
 12             select myValue into l_myValue from myTable where myKey = l_myKey;
 13          exception
 14              when no_data_found then l_myValue := null;
 15          end;
 16          insert into myTableCopy ( myKey, myValue )values( l_myKey, l_myValue );
 17      end loop;
 18      close c;
 19  end;
 20  /
Procedure created.
SQL>
SQL> drop table myTable;
Table dropped.
SQL> drop table myTableCopy;
Table dropped.
SQL>

Procedure with default parameter value

 
SQL>
SQL>
SQL> create or replace
  2    procedure default_values(
  3    p_parm1 varchar2,
  4    p_parm2 varchar2 default "AAA",
  5    p_parm3 varchar2 default "BBB" ) as
  6  begin
  7    dbms_output.put_line( p_parm1 );
  8    dbms_output.put_line( p_parm2 );
  9    dbms_output.put_line( p_parm3 );
 10  end default_values;
 11  /
Procedure created.
SQL>
SQL>  set serverout on
SQL>
SQL>  exec default_values( "Tom", p_parm3 => "Joe" );
Tom
AAA
Joe
PL/SQL procedure successfully completed.
SQL>