Using Cursors (Lab 3 of 7) This iLab is due at the end of Week 3. Scenario/Summary: You will be developing SQL PL procedures that use cursors to...

Using Cursors (Lab 3 of 7)

This iLab is due at the end of Week 3.

Scenario/Summary:

You will be developing SQL PL procedures that use cursors to process result sets on a record-at-a-time basis. You will also demonstrate the use of positioned updates and develop a cursor that maintains its position across transactions.

Upon completion of this lab, students will have hands-on experience using various cursor management techniques in SQL PL.

Deliverables:

    Lab Steps (to be completed in Power Systems)    Points

Step 1    Create a new table.     5 Points

Step 2    Populate the table with data.    5 Points

Step 3    Create a procedure that uses a cursor to apply logic on a row-by-row basis.    15 Points

Step 4    Create a procedure that uses a cursor to perform positioned updates.    15 Points

Step 5    Create a procedure using a cursor that maintains position after a transaction.    20 Points

    Total Lab Points    60 Points

You should submit a Word document to the Dropbox containing:

•    SQL PL code for each task; and

•    screenshots of the output from each task (each screenshot immediately follows the associated code).

iLab Steps

Step 1:  Create a new table

a.    Create a table called Transactions using the following data definition parameters:

Attribute    Data Type    Restrictions

TransactionNumber    INTEGER    Primary Key

Amount     DECIMAL(6,2)    

TransType     CHAR(1)    

Step 2: Populate the table with data

a. Add the following data to your Transactions table. Use the COMMIT statement when you are done. (This need not be done inside of a procedure - just adding the records in straight SQL is fine.)

TransactionNumber    Amount    TransType

1    47.00    C

2    22.50    C

3    39.82    D

4    44.28    C

5    24.36    C

6    58.16    D

7    44.52    C

8    31.74    C

9    56.78    C

10    11.40    D

11    67.90    C

12    55.14    D

a.     Use a SELECT statement to display the rows you have added.

Step 3: Row-by-Row Processing

a. Write an SQL PL procedure that uses a cursor to work through these records in transaction number order (lowest to highest). Records with a TransType of 'C' are credits, whereas those with a type of 'D' are debits. Your procedure should maintain a running total based on the transactions it encounters, adding to the total for credit transactions, and subtracting from it for debit transactions. Once the running total reaches 100 or more, subsequent credits will only be added at half their stated value, whereas subsequent debits will be subtracted as usual (at their stated value). This formula will still apply even if the balance later drops below 100 again. When finished with its calculations, your procedure should output a message of the following form:

Running total is now: $<<value of running total>>

 Make sure that it would still work correctly if the data in the table were different.

b. Show the results of running your procedure.

Step 4: Positioned Updates

a.    Create an SQL PL procedure that uses a cursor to examine all of the records in the Transactions table. If it encounters a debit transaction for less than $15, it should use a positioned update to double the amount of that transaction. Issue a COMMIT statement after the cursor has looped through all records.

Note: Make your procedure flexible so that it can accommodate whatever values are found. Don't hard-code for records that happen to be in the table right now.

b.    Execute your procedure, and then run a SELECT statement to show all of the values now in the Transactions table.

Step 5: Cursor that Maintains Position

a. Create an SQL PL procedure that uses a cursor to examine all of the records in the Transactions table. For each record, it should use a positioned update to add one to the amount of the transaction. Issue a COMMIT statement immediately after each update - don't wait until the cursor has finished looping through all records, as you did for the previous step in this lab.

Note: Make your procedure flexible so that it can accommodate whatever values are found. Don't hard-code for records that happen to be in the table right now.

b. Execute your procedure, and then run a SELECT statement to show all of the values now in the Transactions table.

END OF LAB 3