Manipulating data within PL/SQL
Individual records can be manipulated in PL/SQL. Some notes describing data manipulation on individual records is the topic of this slide show.
As shown in the notes, I will be using a table called testplsql and a sequence called testplsql_seq. The sequence has the clauses INCREMENT BY 1, START WITH 111 AND MAXVALUE 999.
Note that the sequence (testplsql_seq) has been used for repeatedly since the last entry generated 114. The latest entry generated 131 for the identification number.
You should ONLY use variables for data entry when you are inputting one record. If you want to do multiple records, you should re-execute the code. Do not try to put loops in with user input.
The testplsql_seq was used to put in the idno. The last entry put in the row/ record with idno 131. Therefore, this row/ record will have an idno of 132 (one larger).
This code takes in an identification number, selects the row/record that matches that identification number, makes changes to the data and then updates the row/record.
Note that there are three commands in the block on the previous slide.
The first is SELECT. It finds the row/record that matches the idno that is inputted. In this example, record 131 was found.
The second increments the AMT1 by 10%.
The third is UPDATE. It puts the record with the increased AMT1 onto the table.
This code commits the table as it is prior to the update and then deletes a record based on the idno that is entered at the user prompt.
The DELETE command is in the block of PL/SQL code. Because I am using user input, I am only handling 1 record each time the code is executed.