Stepwise Development of a PLSQL procedure

1. Stepwise Development of a PLSQL procedure

2. The following SQL produces the data to go into the cursor SELECT tab1.salesperson, tab1.quantity, tab2.product_price FROM plsql101_purchase tab1, plsql101_product tab2 WHERE tab1.product_name = tab2.product_name ORDER BY salesperson; The Cursor - sales_cur

3. Results of the procedure

4. STEP 1:The data in the cursor Contents of sales_cur.

5. STEP 2 Simple do_commissions procedure using the sales_cur cursor. Nothing is done with this procedure other than creating a cursor and producing ********* output

6. STEP 3 Simple output from the cursor Cursor WHILE LOOP Places data from sales_cur into three variables Fetches the next record from sales_cur. Needs to be in a loop in order to fetch each record in sequence From dbms_output.put_line

7. STEP 4 Calculating sales for each salesperson The loop now includes the calculation of sales.

8. STEP 5 Calculating commissions on each sale. Calculating the commission

9. Figuring out the procedure logic Have to be able to compare two consecutive records in the cursor, the old, previous record and the new record just retrieved. The must be able to distinguish between old and new records in the cursor. The distinguishing feature in each record in the salesperson, e.g. BB or CA. Thus use old_sales_person for the previous record and new_sales_person for the record just retrieved. When two cursor records are compared and they are the dame salesperson: e.g. old_sales_person =new_sales_person then sales from each record have to be added. This will require looping while: old_sales_person =new_sales_person. And adding the sales: LOOP total_sale:=total_sale+(quantity_sold*item_price); When old_sales_person not= new_sales_person: Then the old_sales_person record is printed out along with calculated sales commissions.

10. STEP 6 Equality of salesperson and Looping total sales. Salesperson variables added Only loops when old_sales_person = new_sales_person. Do not want to have output here as it will only produce output when there is equality of salesperson. Thus must move it out of the loop so it can give output for both equality and inequality of salesperson. Procedure runs successfully but nothing is output. It only outputs when old_sales_person = new_sales_person. And old_sales_person is never set equal to new_sales_person. t this point in the procedure development

11. STEP 7 Inequality of salesperson and output. Condition for salesperson equality. No output in the loop. Condition for salesperson inequality. Note that the logic now works but that the first output does not make sense. This means that no ouput should be printed when old_sales_person = ‘*’. Also note that the last record, LB, is not printed

12. STEP 8 More efficient inequality of salesperson and output. Use one statement instead of two.

13. STEP 9 Removing the * record The output when old_sales_person = ‘*’ has now been removed. But there is still not output for LB. No output for the last salesperson, LB

14. STEP 10 Output for the last record This handles the output for the last record, LB Final output

15. Inserting tags to see control flow Tags

16. Results of control flow