Stored Procedure
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Stored Procedure

  1. #1
    Join Date
    Feb 2001
    Posts
    15
    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

  2. #2
    Join Date
    Sep 2001
    Posts
    112
    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?

  3. #3
    Join Date
    Feb 2001
    Posts
    15

    Good Web Site

    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

  4. #4
    Join Date
    Jun 2001
    Location
    NJ
    Posts
    118
    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.

  5. #5
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    Here is the pseudo code
    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]
    Thanx
    Sam



    Life is a journey, not a destination!


  6. #6
    Join Date
    Feb 2001
    Posts
    15

    stored procedure

    Dear Experts,

    Thanks a lot!

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

    BR,

    Grace ^_^

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width