This discussion is archived
2 Replies Latest reply: Jan 10, 2013 8:09 PM by rp0428 RSS

store procedure

984226 Newbie
Currently Being Moderated
Dear all

I would like to create a procedure which have 3 parameters
suppese in Date_birth, in Dept , out number_of_records

if i pass null value to one of the parameter it should return all values
if a pass value to the paramater it should return as per paramerter values
can u help me plz

Create or replace Procedure Emp_Test (P_dop in Date,P_Dept in number , P_noof_records out Number,
p_recordset Out Sys_Refcursor) as
Begin
OPEN p_recordset FOR
Select Emp_cd,Emp_name, Date_of_Birth,Dept,Count(emp_Cd) noof_records
From Emp_Master Where Date_of_birth =P_date_of_Birth
and Dept=P_dept ;

End ;

Plz help .
  • 1. Re: store procedure
    Manik Expert
    Currently Being Moderated
    not sure I followed your question:
    But I assume you are asking something like below
    SELECT Emp_cd,
           Emp_name,
           Date_of_Birth,
           Dept,
           COUNT (emp_Cd) noof_records
      FROM Emp_Master
     WHERE 1 =
              (CASE
                  WHEN p_date_of_birth IS NULL OR p_dept IS NULL THEN 1
                  ELSE 0
               END)
           OR (Date_of_birth = P_date_of_Birth AND Dept = P_dept)
    group by Emp_cd,
           Emp_name,
           Date_of_Birth,
           Dept;
    The above code returns all rows if either of the parameters is null else it evaluates the condition.

    Cheers,
    Manik.

    added group by.. check this query. untested .....
  • 2. Re: store procedure
    rp0428 Guru
    Currently Being Moderated
    Welcome to forum!

    Whenever you post provide your 4 digit Oracle version (result of SELECT * FROM V$VERSION)
    >
    I would like to create a procedure which have 3 parameters
    suppese in Date_birth, in Dept , out number_of_records

    if i pass null value to one of the parameter it should return all values
    if a pass value to the paramater it should return as per paramerter values
    can u help me plz

    Create or replace Procedure Emp_Test (P_dop in Date,P_Dept in number , P_noof_records out Number,
    p_recordset Out Sys_Refcursor) as
    Begin
    OPEN p_recordset FOR
    Select Emp_cd,Emp_name, Date_of_Birth,Dept,Count(emp_Cd) noof_records
    From Emp_Master Where Date_of_birth =P_date_of_Birth
    and Dept=P_dept ;
    >
    It's not clear what you actually want as output so I'll leave that to you.

    This code will handle the null parameters like you ask about.
    create or replace procedure test (p_ename varchar2 default null,
                                      p_deptno number default null,
                                      p_hiredate date default null) as
      type emp_tab_type is table of emp%rowtype;
      emp_tab emp_tab_type;
    begin
      select * bulk collect into emp_tab from emp
      where (p_ename is null or ename = p_ename)
        and (p_deptno is null or deptno = p_deptno)
        and (p_hiredate is null or hiredate = p_hiredate);
      if emp_tab is null or emp_tab.count = 0 then
        dbms_output.put_line('No records');
        return;
      end if;
      for i in emp_tab.first..emp_tab.last loop
        dbms_output.put_line(emp_tab(i).ename);
      end loop;
    end;
    /
    
    exec test ('ALLEN');
    
    ALLEN
    
    exec test ('ALLEN', 20);
    
    No records
    
    exec test (p_deptno => 20);
    
    SMITH
    JONES
    SCOTT
    ADAMS
    FORD

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points