DBM405B Lab 3 in PL/SQL – Using Cursors – PL/SQL Script with 13 Screenshots – Instant Delivery – Perfect Solution

Lab Price = $24
Please feel free to send us your queries at: support@iqrajavaid.com
Payment methods

Add to Cart

Buy Now

View Cart


Problem Statement

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: $<>

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

Relevant Material
Lab Price = $22
Please feel free to send us your queries at: support@iqrajavaid.com
Screenshots
Lab3: Step3_Output
Lab3: Step3_Output

Lab3: Step4_Output
Lab3: Step4_Output
Lab3: Step2
Lab3: Step2
Instructions
* If you want to purchase multiple products then click on “Buy Now” button which will give you ADD TO CART option.Please note that the payment is done through PayPal.
* You can also use 2CO option if you want to purchase through Credit Cards/Paypal but make sure you put the correct billing information otherwise you wont be able to receive any download link.
* Your paypal has to be pre-loaded in order to complete the purchase or otherwise please discuss it with us at support@iqrajavaid.com.
* As soon as the payment is received, download link of the solution will automatically be sent to the address used in selected payment method.
* Please check your junk mails as the download link email might go there and please be patient for the download link email. Sometimes, due to server congestion, you may receive download link with a delay.
* All the contents are compressed in one zip folder.
* In case if you get stuck at any point during the payment process, please immediately contact us at support@iqrajavaid.com and we will fix it with you.
* We try our best to reach back to you on immediate basis. However, please wait for atleast 8 hours for a response from our side. Afterall, we are humans.
* Comments/Feedbacks are truely welcomed and there might be some incentives for you for the next lab/quiz/assignment.
* In case of any query, please donot hesitate to contact us at support@iqrajavaid.com.
* MOST IMPORTANT Please use the tutorials as a guide and they need NOT to be used for any submission. Just take help from the material.
******************************************** Good Luck ***************************************************
Privacy Policy
We take your privacy seriously and will take all measures to protect your personal information.
Any personal information received will only be used to fill your order. We will not sell or redistribute your information to anyone.
Refund Policy
Incase you face any issues with the tutorial, please free to contact us on support@iqrajavaid.com
We will try our best to resolve the issue and if still persists we can discuss for a refund in case its required.
Payment Details
Lab Price = $24
Please feel free to send us your queries at: support@iqrajavaid.com

Payment methods

Add to Cart

Buy Now

View Cart

Leave a Reply