带游标的存储过程

 

DECLARE
/* Declare a cursor explicitly */
     CURSOR csr_org IS
           SELECT h.hrc_descr, o.org_short_name
           FROM   org_tab o, hrc_tab h
           WHERE o.hrc_code = h.hrc_code
            ORDER by 2;
            v_org_rec csr_org%ROWTYPE;
   BEGIN
       /* Open the cursor */
        OPEN csr_org;
        /* Format headings */
        dbms_output.put_line('Organization Details with Hierarchy');
        dbms_output.put_line('------------------------');
        dbms_output.put_line(rpad('Hierarchy',20,' ')||' '||
                                              rpad('Organization',30,' '));
        dbms_output.put_line(rpad('-',20,'-')||' '||rpad('-',30,'-'));
       /* Fetch from the cursor resultset in a loop and display the results
*/
        LOOP
             FETCH csr_org INTO v_org_rec;
             EXIT WHEN csr_org%NOTFOUND;
             dbms_output.put_line(rpad(v_org_rec.hrc_descr,20,' ')||' '||
                             rpad(v_org_rec.org_short_name,30,' '));
        END LOOP;
      /* CLose the cursor */
        CLOSE csr_org;
 END;
/

DECLARE
     CURSOR csr_org IS
           SELECT h.hrc_descr, o.org_short_name
           FROM   org_tab o, hrc_tab h
           WHERE o.hrc_code = h.hrc_code
            ORDER by 2;
            v_org_rec csr_org%ROWTYPE;
   BEGIN
        OPEN csr_org;
        dbms_output.put_line('Organization Details with Hierarchy');
        dbms_output.put_line('------------------------');
        dbms_output.put_line(rpad('Hierarchy',20,' ')||' '||
                                              rpad('Organization',30,' '));
        dbms_output.put_line(rpad('-',20,'-')||' '||rpad('-',30,'-'));
        FETCH csr_org INTO v_org_rec;
        WHILE (csr_org%FOUND) LOOP
               dbms_output.put_line(rpad(v_org_rec.hrc_descr,20,' ')||' '||
                                      rpad(v_org_rec.org_short_name,30,' '));
               FETCH csr_org INTO v_org_rec;
        END LOOP;
        CLOSE csr_org;
 END;
/
 
 
 
DECLARE
     CURSOR csr_org IS
           SELECT h.hrc_descr, o.org_short_name
           FROM   org_tab o, hrc_tab h
           WHERE o.hrc_code = h.hrc_code
            ORDER by 2;
BEGIN
        dbms_output.put_line('Organization Details with Hierarchy');
        dbms_output.put_line('------------------------');
        dbms_output.put_line(rpad('Hierarchy',20,' ')||' '||
                                              rpad('Organization',30,' '));
        dbms_output.put_line(rpad('-',20,'-')||' '||rpad('-',30,'-'));
        FOR idx IN csr_org LOOP
                dbms_output.put_line(rpad(idx.hrc_descr,20,' ')||' '||
                                rpad(idx.org_short_name,30,' '));
        END LOOP;
END;
/
由此可见FOR LOOP循环比较好。