Stored Procedure

[gracetam]

Dear Experts,

Hello! I'm stored procedure beginner. I want to create a procedure to retrieve the follow data. How can i do?

SELECT C.EMID, C.ENNM, C.CNNM, A.ATRC_IN01, A.ATRC_IN02,
A.ATRC_IN03, A.ATRC_IN04, A.ATRC_OU01, A.ATRC_OU02,
A.ATRC_OU03, A.ATRC_OU04, A.ATRC_DATE
FROM HR_M_CINF C, HR_T_ATRC A
WHERE C.EMID = A.ATRC_EMID AND A.ATRC_COMP = 'N'
AND A.ATRC_DATE BETWEEN SDATE AND EDATE
ORDER BY C.EMID, A.ATRC_DATE

SDATE and EDATE are passed parameters.

Thanks a lot.

BR,
Grace

[UNIX DBA]

You are basically asking us to write a whole procedure for you.

In brief you could simply create a record type cursor and select those rows into it.

Then loop through printing each row to the screen in a good format.

However you must have some knowledge of PL/SQL to do this.

How much do you know?

[gracetam]

Dear Expert,

Thanks for your reply.

Indeed, I'm not so good in stored procedure. I didn't take any course or read any books about it. Recently, I need to meet my project urgently. So, I don't have any time to learn it. I didn't find more samples and details about it in web.

Do you know which web site has more information about it?

BR,

Grace

[gd_1976]

Hi Grace,
Do you have oracle documentation? If not Download it from the Oracle web site. It will be very useful.
It has got all the hints and support for beginners - experts.

Hope this helps.

GD_1976.

[sambavan]

Here is the pseudo code

  CREATE PROCEDURE procedure_name(p_sdate DATE, p_edate DATE)
  IS
       CURSOR cursor_name(c_sdate DATE, c_edate DATE) IS
          SELECT C.EMID "EMID", 
                      C.ENNM "ENNM", 
                      C.CNNM "CNNM", 
                      A.ATRC_IN01 "ATRC_IN01", 
                      A.ATRC_IN02 "ATRC_IN02",  
                      A.ATRC_IN03 "ATRC_IN03",  
                      A.ATRC_IN04 "ATRC_IN04",  
                      A.ATRC_OU01 "ATRC_OU01", 
                      A.ATRC_OU02 "ATRC_OU02", 
                      A.ATRC_OU03 "ATRC_OU03",  
                      A.ATRC_OU04 "ATRC_OU04",  
                      A.ATRC_DATE "ATRC_DATE"
             FROM HR_M_CINF C, 
                      HR_T_ATRC A 
             WHERE C.EMID = A.ATRC_EMID 
                 AND A.ATRC_COMP = 'N' 
                 AND A.ATRC_DATE BETWEEN c_sdate AND c_edate 
              ORDER BY C.EMID, A.ATRC_DATE;

   BEGIN
            
          FOR c_myCursor IN cursor_name(p_sdate,p_edate) LOOP
               DBMS_OUT.PUT_LINE(c_myCursor.EMID);
               :
               .
          END LOOP;
   EXCEPTION
    WHEN OTHERS THEN
       DBMS_OUT.PUT_LINE(SQLERRM);

   END;
   show errors;
    /

Ref: http://oradoc.photo.net/ora816/appde...a77069/toc.htm

Sam

[Edited by sambavan on 11-28-2001 at 02:44 PM]

[gracetam]

Dear Experts,

Thanks a lot!

I tried to create a package which includes this procedure and it ran successful!

BR,

Grace ^_^