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