Parameter with default value : Default Parameter Value « Stored Procedure Function « Oracle PL / 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>

   
    
    
    
  








Related examples in the same category

1.Procedure with default parameter value
2.Parameter with default NULL value
3.Demonstrates default parameters.