2 Replies Latest reply: Jan 10, 2013 10:09 PM by rp0428 RSS

    store procedure

    984226
      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
          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
            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