Overview
This lecture covers enhancements to a basic PL/SQL program, focusing on controlling loop execution, filtering records to process, conditional output, and displaying formatted results.
Controlling Loop Execution
- The
exit statement is used inside loops to terminate them and prevent infinite execution.
- Regularly running code after small additions helps detect errors early, a key programming habit.
Filtering Records with WHERE Clause
- Processing every record in the order transaction table is unnecessary if they've already been handled.
- A
flag field in the table marks if a transaction has been processed (flag = 1) or not (flag = 0).
- Adding
WHERE o.flag = 0 ensures only unprocessed transactions are included.
Conditional Actions Inside Loops
- Inside the loop, use an
if statement to check if a customer’s number of trades is not zero.
- Only customers with at least one trade have their balances updated and information printed.
Printing Output in PL/SQL
- The
dbms_output.put() procedure formats what to print, such as customer number, closing balance, and number of trades.
- Separators (
||) concatenate output components, with formatting like TO_CHAR for balances.
dbms_output.put_line(''); is used to execute the print action.
Output and Results
- Output displays customer number, updated closing balance, and number of trades for customers with trades.
- Customers without trades are omitted from the output due to the filtering logic.
Summary of Enhancements
- Added a
WHERE clause to skip already processed transactions.
- Implemented conditional logic to process only customers with trades.
- Used PL/SQL output commands to display formatted results during loop execution.
Key Terms & Definitions
- exit — PL/SQL command to break out of a loop.
- flag — Table field marking processed (
1) or unprocessed (0) transactions.
- dbms_output.put — Procedure for constructing output strings in PL/SQL.
- dbms_output.put_line — Procedure that outputs the constructed string to the screen.
- WHERE clause — SQL condition to filter query results.
Action Items / Next Steps
- Prepare to update customer balances and set the processed
flag to 1 on transactions in the next lesson.