Manipulating data within PL/SQL

1
Manipulating data within PL/SQL

• Please use speaker notes for additional
  information!

2
SQLgt DESC testplsql Name
Null? Type -------------------------------
-------- ---- IDNO
NUMBER(3) NAME
VARCHAR2(20) AMT1
NUMBER(6,2) A_CODE
CHAR(2) AMT2
NUMBER(4)
Manipulating data
These records were inserted as I created the
notes (please see the notes for additional
information).
SQLgt SELECT FROM testplsql IDNO NAME
AMT1 A_ AMT2 ---------
-------------------- --------- -- ---------
111 John Smith 112.12 MA 234
112 Ann Wilson 27.28 RI
987 113 Susan French 220.83 MA
345 114 James Rogers 500.12
RI 123
I am going to go in and run test_insert which
adds records to the table using testplsql_seq.
Note that the last record in this table had an
idno of 114 which was created by the
testplsql_seq. I have been playing with
testplsql_seq and now when I go in and use it,
you will see that there is a gap.
3
Manipulating data
Note that the idno is coming in from the
sequence, the rest of the data has been hard
coded into the PL/SQL block.
SQLgt edit test_insert
DECLARE v_idno testplsql.idnoTYPE BEGIN
SELECT testplsql_seq.NEXTVAL INTO v_idno
FROM dual INSERT INTO testplsql
VALUES(v_idno, 'Stephen York', 36.87, 'NY',
122) END /
SQLgt _at_ test_insert PL/SQL procedure successfully
completed. SQLgt SELECT FROM testplsql
IDNO NAME AMT1 A_
AMT2 --------- -------------------- --------- --
--------- 111 John Smith
112.12 MA 234 112 Ann Wilson
27.28 RI 987 113 Susan French
220.83 MA 345 114 James
Rogers 500.12 RI 123 131
Stephen York 36.87 NY 122
4
Manipulating data
SQLgt edit test_insert1
SET VERIFY OFF ACCEPT in_name PROMPT 'Enter name
' ACCEPT in_amt1 PROMPT 'Enter amount one
' ACCEPT in_a_code PROMPT 'Enter the state code
' ACCEPT in_amt2 PROMPT 'Enter amount two
' DECLARE v_idno testplsql.idnoTYPE
v_name testplsql.nameTYPE 'in_name'
v_amt1 testplsql.amt1TYPE in_amt1
v_a_code testplsql.a_codeTYPE 'in_a_code'
v_amt2 testplsql.amt2TYPE in_amt2 BEGIN
SELECT testplsql_seq.NEXTVAL INTO v_idno
FROM dual INSERT INTO testplsql
VALUES(v_idno, v_name, v_amt1, v_a_code,
v_amt2) END / SET VERIFY ON
5
Manipulating data
The ACCEPT statements shown on the previous slide
and below put out these prompts and took in the
user input data. ACCEPT in_name PROMPT 'Enter
name ' ACCEPT in_amt1 PROMPT 'Enter amount one
' ACCEPT in_a_code PROMPT 'Enter the state code
' ACCEPT in_amt2 PROMPT 'Enter amount two '
SQLgt edit test_insert1 SQLgt _at_ test_insert1 Enter
name Jennifer Ames Enter amount one
345.99 Enter the state code TX Enter amount two
456 PL/SQL procedure successfully completed.
SQLgt SELECT FROM testplsql IDNO NAME
AMT1 A_ AMT2 ---------
-------------------- --------- -- ---------
111 John Smith 112.12 MA 234
112 Ann Wilson 27.28 RI
987 113 Susan French 220.83 MA
345 114 James Rogers 500.12
RI 123 131 Stephen York
36.87 NY 122 132 Jennifer Ames
345.99 TX 456
testplsql_seq.NEXTVAL was used to put in the idno
from the testplsql_seq sequence.
6
Manipulating data
SQLgt edit test_update1
SET VERIFY OFF ACCEPT in_idno PROMPT 'Enter the
idno of the record to change amt1 in ' DECLARE
v_idno_in testplsql.idnoTYPE in_idno
v_idno testplsql.idnoTYPE v_amt1
testplsql.amt1TYPE v_new_amt1
testplsql.amt1TYPE BEGIN SELECT idno, amt1
INTO v_idno, v_amt1 FROM testplsql
WHERE idno v_idno_in v_new_amt1 v_amt1
1.1 UPDATE testplsql SET amt1
v_new_amt1 WHERE idno v_idno_in END / SET
VERIFY ON
7
Manipulating data
SQLgt SELECT FROM testplsql IDNO NAME
AMT1 A_ AMT2 ---------
-------------------- --------- -- ---------
111 John Smith 112.12 MA 234
112 Ann Wilson 27.28 RI
987 113 Susan French 220.83 MA
345 114 James Rogers 500.12
RI 123 131 Stephen York
36.87 NY 122 132 Jennifer Ames
345.99 TX 456 SQLgt edit
test_update1 SQLgt _at_ test_update1 Enter the idno
of the record to change amt1 in 131 PL/SQL
procedure successfully completed. SQLgt SELECT
FROM testplsql IDNO NAME
AMT1 A_ AMT2 --------- -------------------
- --------- -- --------- 111 John Smith
112.12 MA 234 112 Ann Wilson
27.28 RI 987 113 Susan
French 220.83 MA 345 114
James Rogers 500.12 RI 123
131 Stephen York 40.56 NY 122
132 Jennifer Ames 345.99 TX
456
The code in test_update1 selects a row/record,
increases amt1 by 10 and then updates the record.
8
Manipulating data
As I did in the notes, I am going to delete a
record and then rollback to restore the record.
The delete is done using the test_delete1 block
of code.
SQLgt edit test_delete1
SET VERIFY OFF ACCEPT in_idno PROMPT 'Enter the
idno of the record to delete ' DECLARE
v_idno_in testplsql.idnoTYPE in_idno
v_idno testplsql.idnoTYPE BEGIN COMMIT
DELETE FROM testplsql WHERE idno
v_idno_in END / SET VERIFY ON
9
Manipulating data
The prompt is the result of the ACCEPT in the
code.
SQLgt _at_ test_delete1 Enter the idno of the record
to delete 131 PL/SQL procedure successfully
completed. SQLgt SELECT FROM testplsql
IDNO NAME AMT1 A_
AMT2 --------- -------------------- --------- --
--------- 111 John Smith
112.12 MA 234 112 Ann Wilson
27.28 RI 987 113 Susan French
220.83 MA 345 114 James
Rogers 500.12 RI 123 132
Jennifer Ames 345.99 TX 456 SQLgt
ROLLBACK Rollback complete. SQLgt SELECT FROM
testplsql IDNO NAME
AMT1 A_ AMT2 --------- --------------------
--------- -- --------- 111 John Smith
112.12 MA 234 112 Ann Wilson
27.28 RI 987 113 Susan
French 220.83 MA 345 114
James Rogers 500.12 RI 123
131 Stephen York 40.56 NY 122
132 Jennifer Ames 345.99 TX
456
Note that the row/record 131 has been deleted.
After the rollback, the record is restored.