1.txt

Oracle Database 10g PL／SQL入门教程的全部例子

CREATE OR REPLACE PROCEDURE do_commissions IS
     commission_rate NUMBER  := 2   ;
     total_sale      NUMBER  := 0   ;
     current_person  CHAR(3) := ' ' ;
     next_person     CHAR(3)        ;
     quantity_sold   NUMBER  := 0   ;
     item_price      NUMBER  := 0   ;
     CURSOR sales_cur IS
          SELECT purc.salesperson,
                 purc.quantity,
                 prod.product_price
          FROM   plsql101_purchase purc,
                 plsql101_product  prod
          WHERE  purc.product_name = prod.product_name
          ORDER BY salesperson;
BEGIN
     OPEN sales_cur;
     LOOP
          FETCH sales_cur INTO
                next_person, quantity_sold, item_price;
          WHILE (next_person = current_person
                 AND
                 sales_cur%FOUND)
          LOOP
               total_sale := 
                     total_sale + (quantity_sold * item_price);
                FETCH sales_cur INTO 
                     next_person, quantity_sold, item_price;
          END LOOP;
          IF (sales_cur%FOUND)
          THEN
               IF (current_person != next_person)
               THEN
                    IF (current_person != ' ' )
                    THEN
                         dbms_output.put_line
                            (current_person ||
                             ' ' ||
                             total_sale ||
                             ' ' ||
                             total_sale * commission_rate / 100);
                    END IF;
                    total_sale := quantity_sold * item_price;
                    current_person := next_person;
               END IF;
          ELSE IF (current_person != ' ')
          THEN
                 dbms_output.put_line(current_person ||
                               ' ' ||
                               total_sale ||
                               ' ' ||
                               total_sale * commission_rate / 100);
               END IF;
          END IF;
          EXIT WHEN sales_cur%NOTFOUND;
          END LOOP;
          CLOSE sales_cur;
     END do_commissions;