Overview
This lecture covers how to update customer and order transaction tables in PL/SQL, track updated records, and verify changes in the database.
Updating the Customer Table
- Only update customers who have engaged in trades.
- Set the
last updated field to "01-Nov-16 09:31:00.000000 AM" for affected customers.
- Update the
closing cash balance to reflect the latest value (eachcustomer.closedbal).
- Ensure updates only affect the relevant customer using their account number.
Updating the Order Transactions Table
- Set
ordertransactions.flag to 1 for records where the flag is currently 0.
- Only update records for the specific customer being processed.
- Use conditions to avoid updating unaffected records.
Tracking Updates and Output
- Introduce a variable
updated_count initialized to 0 to count updated customers.
- Increment
updated_count by 1 each time a customer record is updated.
- After processing, print the total number of customer records updated using
DBMS_OUTPUT.PUT_LINE.
Verification and Results
- After running the process, check the customer table to confirm
last updated fields reflect the new date and time.
- Confirm in the order transactions table that all relevant flags are now set to 1.
- Rerunning the script yields 0 updates because no transactions remain unprocessed (flags are already 1).
Key Terms & Definitions
- PL/SQL — Oracle's procedural extension for SQL, enabling procedural logic within SQL scripts.
- Customer Table — Database table storing customer account information, cash balances, and last update timestamps.
- Order Transactions Table — Table recording individual customer transactions, including a
flag to indicate processing status.
- Flag (Order Transactions) — Field used to track whether a transaction has already been processed (0 = not processed, 1 = processed).
- DBMS_OUTPUT.PUT_LINE — Built-in PL/SQL procedure that prints output to the screen for debugging or reporting.
Action Items / Next Steps
- Practice updating tables using conditional logic in PL/SQL.
- Review how to use counters and output statements in PL/SQL.
- Prepare for the next session on creating applications with Oracle Application Express (APEX).