How to write a procedure that accepts optional parameters?

baytrix
baytrix used Ask the Experts™
on
I'm trying to create a procedure that can acccept at least three but up to 20 parameters...does anyone know how I can do that?
Basically what I want to do is to write a procedure that helps the users to insert records to 2 tables:
Project (PID, Name...)
Project_Employee (PID, Emp_ID)

So I'm trying to write a procedure, something like:

insertProject (PID, Name, Emp_ID1, Emp_ID2,....)

How do I declare a procedure that accepts 3 to 20 parameters? An example would be extremely helpful.

Thanks much for your help.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Hi Maytrix

Your problem can be solved by the "named notation" and the default values for the parameters. Have a look at the following example package written for the default emp table in scott schema.

create or replace package emp_pkg is
procedure insertEmp (
  pi_empno     in     emp.empno     %type
, pi_ename     in     emp.ename     %type
, pi_job     in     emp.job          %type default null
, pi_mgr     in     emp.mgr          %type default null
, pi_hiredate     in     emp.hiredate     %type default null
, pi_sal     in     emp.sal          %type default null
, pi_comm     in     emp.comm     %type default null
, pi_deptno     in     emp.deptno     %type default null
);
end emp_pkg;
/
show errors;
create or replace package body emp_pkg is
procedure insertEmp (
  pi_empno     in     emp.empno     %type
, pi_ename     in     emp.ename     %type
, pi_job     in     emp.job          %type default null
, pi_mgr     in     emp.mgr          %type default null
, pi_hiredate     in     emp.hiredate     %type default null
, pi_sal     in     emp.sal          %type default null
, pi_comm     in     emp.comm     %type default null
, pi_deptno     in     emp.deptno     %type default null
) is
begin
     insert into emp (
                 empno
               , ename
               , job
               , mgr
               , hiredate
               , sal
               , comm
               , deptno
               ) values (
                 pi_empno
               , pi_ename
               , pi_job
               , pi_mgr
               , pi_hiredate
               , pi_sal
               , pi_comm
               , pi_deptno
               );
     end insertEmp;
end emp_pkg;
/
show errors;

You could invoke the insertEmp procedure in the following way. If you write a package for ur tables like the one shown above and make the call as shown below, ur problem could be solved....

declare
begin
     emp_pkg.insertEmp (
            pi_empno     =>     7777
          , pi_ename     =>     'My Name'
          , pi_deptno     =>     10
          );
end;
/

In the same way, whatever the parameters known to u can be passed as parameters and make it work as long as u follow the named notation.

Regards
Kannan

Author

Commented:
Thanks Kannan, I learned a new trick.
But I'm sorry I wasn't clear on my question. I have the following 3 tables:

Project (Project_id, Project_Name, Project_Budget)
Employee (Emp_id, Emp_Name)
Project_Employee (Project_id, Emp_id)

So each project can be worked on by multiple employees.

So I'm trying to write a procedure to insert multiple rows into Project_Employee. Something like:

Create procedure InsertProjectEmployee (PID1, EID1, EID2, EID3...)
BEGIN

==> And I'll insert rows into the Project_Employee table:
values(PID1, EID1)
values(PID1, EID2)
values(PID1, EID3)....

END;

Does that make sense? Since I want the procedure to accept from 1 to 20 Employee ID...so I wonder how I can do that?
Thanks again for your kind help. =)
Commented:
create or replace procedure InsertProjectEmp(
pid1 number(10),
eid1 number(10),
eid2 number(10) default null,
...
eid20 number(10) default null
) is
begin
 if eid1 is not null
  insert into proj_emp (PID, EID) values (PID1, EID1);
  if eid2 is not null
   insert into proj_emp (PID, EID) values (PID1, EID2);
   if eid3 is not null
    insert into proj_emp (PID, EID) values (PID1, EID3);
......
     if eid20 is not null
      insert into proj_emp (PID, EID) values (PID1, EID20);
     end if;
   end if;
  end if;
 end if;
end;

good luck,
Kanan
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Baytrix

I just gave an example. My "bottom line" solution for your problem is "Try using Named Notation and Default Values for the parameters".

Once u have a procedure with 20 parameters and the default values, u can have any kind of logic depending on ur need in the procedure body. Mine was just an example.

Kanan

Ur answer must be clearer to Baytrix, but there is a catch in ur solution. Unless the eid3 is not null, eid4 wont be checked. And the same case for all the subsequent eids. Instead if you correct ur code as shown below, it should work fine.

create or replace procedure InsertProjectEmp(
pid1 number(10),
eid1 number(10),
eid2 number(10) default null,
...
eid20 number(10) default null
) is
begin
  if eid1 is not null
   insert into proj_emp (PID, EID) values (PID1, EID1);
  end if;
  if eid2 is not null
    insert into proj_emp (PID, EID) values (PID1, EID2);
  end if;
  if eid3 is not null
    insert into proj_emp (PID, EID) values (PID1, EID3);
  end if;
  ......
  if eid20 is not null
    insert into proj_emp (PID, EID) values (PID1, EID20);
  end if;

end;

Commented:
Kannan,
you are absolutely right saying "Unless the eid3 is not null, eid4 wont be checked. And the same case for all the subsequent eids."
I wrote so because of default parameters. I think empid is not nullable. I wrote so in order not to check subsequent params after getting first null EID.

good, luck,
Kanan
Kanan

Ur code / logic would be perfectly ok for positional notation. Mine is a more generic one with named notation. For e.g.

If u invoke the procedure as follows:

declare
begin
  insertProjectEmp( 'PROJ01'
                  , 'EMP01'
                  , 'EMP02'
                 );
end;

or

declare
begin
  insertProjectEmp( 'PROJ01'
                  , 'EMP01'
                  , 'EMP02'
                  , 'EMP03' );
end;

In my case, you could invoke as follows:

declare
begin
  insertProjectEmp( pid1 => 'PROJ01'
                  , eid1 => 'EMP01'
                  , eid2 => 'EMP02'
                  , eid5 => 'EMP03'
                  );
end;

You may ask what is the need to pass EMP03 in eid5 rather than eid3. In this example, it may not be perfect. But, to make ur code more generic in an application, following a named notation is a better way than positional notation. So, I replied that way.

Anyway, hope both the solutions work for Baytrix.

Cheers
Kannan

Commented:
baytrix:

The solution that best fit your problem is to use just one CLOB parameter to pass an XML.

Then, using the xdk for PL/SQL you can easily parse the paramater doing the desired actions.

Hope this helps, SIDCAP.

Commented:
Or,

use a VARRAY:

-- create TYPE
TYPE VAR_EMPIDS IS VARRAY(20) OF NUMBER;

-- Assign Variable to Type
MyEmpIds VAR_EMPIDS;

-- Populate MyEmpIds
MyEmpIds(1) := 1000
MyEmpIds(2) := 1001
...

-- Make Procedure Call
insertProject (PID, Name, MyEmpIds)

...
Within insertProject, iterate through MyEmpIds and test values. If non-null, process.

insertProject would be interface would be declared as:

insertProject(PID IN Number,
   Name IN Varchar2,
   MyEmpIds IN VAR_EMPIDS)
...

HTH,
Mark

 
   

Author

Commented:
Thank you all of you!!! You guys are amazing! =)

Commented:
Create a procedure with a in parameter as a plsql table..or varray../.

No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:
split between kannankumara & kanan
Please leave any comments here within the next seven days.

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!

patelgokul
EE Cleanup Volunteer

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial