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.
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?
Good Web Site
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?
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.
Here is the pseudo code
CREATE PROCEDURE procedure_name(p_sdate DATE, p_edate DATE)
CURSOR cursor_name(c_sdate DATE, c_edate DATE) IS
SELECT C.EMID "EMID",
FROM HR_M_CINF C,
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;
FOR c_myCursor IN cursor_name(p_sdate,p_edate) LOOP
WHEN OTHERS THEN
[Edited by sambavan on 11-28-2001 at 02:44 PM]
Life is a journey, not a destination!
Thanks a lot!
I tried to create a package which includes this procedure and it ran successful!
Click Here to Expand Forum to Full Width