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

Demonstrates default parameters.

   <source lang="sql">
   

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.

 </source>
   
  

Parameter with default NULL value

   <source lang="sql">
 

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>

 </source>
   
  

Parameter with default value

   <source lang="sql">
   

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>

 </source>
   
  

Procedure with default parameter value

   <source lang="sql">

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>

 </source>