Manipulating data within PL/SQL
Within PL/SQL you can manipulate individual records as shown in the examples below. This
is the description of the table that I will be using. I also have created a sequence that
I will be using.
SQL CODE:
SQL> DESC testplsql;
Name Null? Type
------------------------------- -------- ----
IDNO NUMBER(3)
NAME VARCHAR2(20)
AMT1 NUMBER(6,2)
A_CODE CHAR(2)
AMT2 NUMBER(4)
SQL> CREATE SEQUENCE
2 testplsql_seq
3 INCREMENT BY 1
4 START WITH 111
5 MAXVALUE 999;
Sequence created.
Next, I wrote the code as test_insert to put a record into the file using the sequence
number generator for the idno. First, I got the number and stored in as a variable. Then
I did the insert. Please remember the / at the end of the code. I get into trouble when
I don't. I ran test_insert once, got the results, went in and changed the data and ran it
again! Cumbersome!
SQL CODE:
SQL> edit test_insert
PL/SQL CODE:
DECLARE
v_idno testplsql.idno%TYPE;
BEGIN
SELECT testplsql_seq.NEXTVAL
INTO v_idno
FROM dual;
INSERT INTO testplsql
VALUES(v_idno, 'John Smith', 112.12, 'MA', 234);
END;
/
SQL CODE:
SQL> @ test_insert
Input truncated to 1 characters
PL/SQL procedure successfully completed.
SQL> SELECT * FROM testplsql;
IDNO NAME AMT1 A_ AMT2
--------- -------------------- --------- -- ---------
111 John Smith 112.12 MA 234
SQL> edit test_insert
PL/SQL CODE:
DECLARE
v_idno testplsql.idno%TYPE;
BEGIN
SELECT testplsql_seq.NEXTVAL
INTO v_idno
FROM dual;
INSERT INTO testplsql
VALUES(v_idno, 'Ann Wilson', 27.28, 'RI', 987);
END;
/
SQL CODE:
SQL> @ test_insert
Input truncated to 1 characters
PL/SQL procedure successfully completed.
SQL> SELECT * FROM testplsql;
IDNO NAME AMT1 A_ AMT2
--------- -------------------- --------- -- ---------
111 John Smith 112.12 MA 234
112 Ann Wilson 27.28 RI 987
Now, I want to adapt the code so that the user can enter the data rather than having to go
into the code and make the changes. To do this I am using ACCEPT statements outside the
PL/SQL block to take in the data. Then I am assigning the variable that came in as the
initial value to the variables I declared in the DECLARE section. Notice that I do this
with the assignment sign (:=) followed by the &name in accept. If it is non numeric, I
enclose in quotes. The user is then prompted to enter the data, the data is stored in
variables which are used in the insert. The identification number is still being generated
by the sequence generator used in the examples above. I ran the PL/SQL twice to input two
records.
SQL CODE:
SQL> edit test_insert1
PL/SQL CODE:
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.idno%TYPE;
v_name testplsql.name%TYPE :='&in_name';
v_amt1 testplsql.amt1%TYPE :=&in_amt1;
v_a_code testplsql.a_code%TYPE :='&in_a_code';
v_amt2 testplsql.amt2%TYPE :=&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
SQL CODE:
SQL> @ test_insert1
Enter name: Susan French
Enter amount one: 200.75
Enter the state code: MA
Enter amount two: 345
PL/SQL procedure successfully completed.
SQL> 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 200.75 MA 345
SQL> @ test_insert1
Enter name: James Rogers
Enter amount one: 500.12
Enter the state code: RI
Enter amount two: 123
PL/SQL procedure successfully completed.
SQL> 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 200.75 MA 345
114 James Rogers 500.12 RI 123
Now I am going to update the field AMT1 for whatever identification number the user enters.
The code first does the ACCEPT to take in the identification number which is assigned as an
initial value to v_idno-in. Then I did a select to bring in the idno and amt1 where the
record matched the input id stored in v_idno_in. The data that was brought in was stored
in v_idno and v_amt1. Next I multiplied v_amt1 by 1.1 and stored the answer in v_new_amt1
Next I did the update to set the amt1 on the file equal to v_new_amt1 where the idno on the
file matched the idno that was keyed in and stored in v_idno-in. The update was successful
as shown in the output below.
SQL CODE:
SQL> edit test_update1
PL/SQL CODE:
SET VERIFY OFF
ACCEPT in_idno PROMPT 'Enter the idno of the record to change amt1 in: ';
DECLARE
v_idno_in testplsql.idno%TYPE :=&in_idno;
v_idno testplsql.idno%TYPE;
v_amt1 testplsql.amt1%TYPE;
v_new_amt1 testplsql.amt1%TYPE;
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
SQL CODE:
SQL> @ test_update1
Enter the idno of the record to change amt1 in: 113
PL/SQL procedure successfully completed.
SQL> 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
The next thing is to delete a record. I am actually going to delete the record and then at
the SQL> prompt, I will recall the record. To do this I am going to commit within the code
before I do the delete so the rollback will only go back through the delete and restore the
record that was deleted. Note that the rollback is issued at the SQL prompt, or I would not
be able to see the results of the delete.
SQL CODE:
SQL> edit delete1
PL/SQL CODE:
SET VERIFY OFF
ACCEPT in_idno PROMPT 'Enter the idno of the record to delete: ';
DECLARE
v_idno_in testplsql.idno%TYPE :=&in_idno;
v_idno testplsql.idno%TYPE;
BEGIN
COMMIT;
DELETE FROM testplsql
WHERE idno = v_idno_in;
END;
/
SET VERIFY ON
SQL CODE:
SQL> @ test_delete1
Enter the idno of the record to delete: 113
PL/SQL procedure successfully completed.
SQL> SELECT * FROM testplsql;
IDNO NAME AMT1 A_ AMT2
--------- -------------------- --------- -- ---------
111 John Smith 112.12 MA 234
112 Ann Wilson 27.28 RI 987
114 James Rogers 500.12 RI 123
SQL> ROLLBACK;
Rollback complete.
SQL> 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
This PL/SQL code is another example of doing an insert. It takes the identification number,
name, hours and pay per hour through ACCEPT statements. Then the PL/SQL block calculates
the pay and inserts the identification number, name and gross pay into the table testpay.
The first SQL code creates the table called testpay. The second command - edit pay2 -
creates an edit file where I include the ACCEPT statements and the PL/SQL block.
SQL CODE:
SQL> create table testpay
2 (idno NUMBER(3), name VARCHAR2(20), grosspay NUMBER(6,2));
Table created.
SQL> edit pay2
First I set verify off so that the old, new reporting will not come after the ACCEPTs. The
ACCEPT statements take in the identification number, the name,he hours worked and the pay
per hour. Next I coded the PL/SQL block. In the declare section I set up variables for
the pay per hour, the hours and the pay where the result of the calculation will be stored.
For the v_hrs and the V_payhr , I assigned initial values that came in through the ACCEPT
statements. In the DECLARE, assigned the initial value through the :=. In the BEGIN, I
first multiply the contents of v_hrs by the contents of v_payhr and assigned the results to
v_pay. Then I INSERT the identification number and name that came in through the accepts
and the result of the calculation into the testpay table. Note that the values from the
accept are preceded by the & and the string name is surrounded in quotes. The calculated
pay is put in using the answer field. The END; ends the block. The / is to execute and
the SET VERIFY ON returns the condition to its default status.
PL/SQL CODE:
SET VERIFY OFF
ACCEPT in_id PROMPT 'Enter id #: '
ACCEPT in_name PROMPT 'Enter name: '
ACCEPT in_hrs PROMPT 'Enter hours worked: '
ACCEPT in_payhr PROMPT 'Enter pay per hour: '
DECLARE
v_pay NUMBER(6,2);
v_hrs NUMBER(2) :=&in_hrs;
v_payhr NUMBER(5,2) := &in_payhr;
BEGIN
v_pay := v_hrs * v_payhr;
INSERT INTO testpay
VALUES(&in_id,'&in_name', v_pay);
END;
/
SET VERIFY ON
After saving pay2 with the code I wrote, I exited the editor. Then, at the SQL prompt, I
executed pay2. The accepts displayed the prompts and I entered in the data. The code then
Inserts the data into the table testpay. I selected the contents of testpay to show the
insertion of the first record. I then ran pay2 again and a second record was inserted in
the table. Notice also the haunting Input truncated...
SQL CODE:
SQL> @ pay2
Enter id #: 123
Enter name: John Doe
Enter hours worked: 40
Enter pay per hour: 12
PL/SQL procedure successfully completed.
Input truncated to 13 characters
SQL> SELECT * FROM testpay;
IDNO NAME GROSSPAY
--------- -------------------- ---------
123 John Doe 480
SQL> @ pay2
Enter id #: 234
Enter name: Ann Smith
Enter hours worked: 40
Enter pay per hour: 10.75
PL/SQL procedure successfully completed.
Input truncated to 13 characters
SQL> SELECT * FROM testpay;
IDNO NAME GROSSPAY
--------- -------------------- ---------
123 John Doe 480
234 Ann Smith 430