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

1 All of the following are steps to follow when developing a eulogy except:

you should always celebrate the life of the deceased

you should always use humor

you should always bring the group together through language

you may use quotes

2 An example of an informative speech topic which would be

organized to describe would be

none of the above

to explain how schizophrenia develops”

the history of the Sioux Indians”

the layout of the American Indian museum in Washington DC”

all of the above

3 A participant who says, “Committees are boring; why don’t we have a party instead?”

    is engaged in what type of negative behavior?

 clowning

attacking

confessing

4  A presentation which alters the beliefs and judgments of an audience is known as a speech to

actuate

describe

stimulate

convince

5  A presentation which intensifies listeners’ feelings is known as a speech to

 define

convince

stimulate

actuate

6 A presentation which persuades the audience to perform a specific action related to the solution is called a speech to

demonstrate

convince

stimulate

actuate

7 A speaker who presents arguments in a biased and unfair manner, deliberately

     ignoring opposite information is guilty of

name-calling

plagiarizing

advocating harm

card-stacking