Manipulating data within PL/SQL

Speaker Notes

Slide 1:

Individual records can be manipulated in PL/SQL. Some notes describing data manipulation on individual records is the topic of this slide show.

Slide 2:

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.

Slide 3:

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.

Slide 4:

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.

Slide 5:

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).

Slide 6:

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.

Slide 7:

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.

Slide 8:

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.

Slide 9:

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.