Using Other DML Commands - Oracle

Until now, we examined how to retrieve information from tables in PL/SQL. Now we shall see how to manipulate data using PL/SQL. As everybody knows, manipulating information within tables is possible only through DML commands (insert, update, delete, and so forth). We will have an example for each of these commands in a very simple manner.

Specifically speaking, DDL commands do not have direct support to let them work within PL/SQL. We need to use special packages to work with DDL commands in PL/SQL; that topic will be introduced in upcoming parts of this series. All types of TCL (Transaction Control Language) statements (COMMIT, ROLLBACK, SAVEPOINT, and so forth)are supported within PL/SQL, just as they are in Oracle SQL.

Before we proceed, you should understand that the syntax of DML (or TCL) commands used in PL/SQL is identical to that of SQL, and we can substitute variables in the places of values.

The following is the example which can be used to insert a row into the table "emp."

declare
     v_empno emp.empno%type := &empno;
     v_ename emp.ename%type := '&ename';
     v_deptno emp.deptno%type := &deptno;
begin
     insert into emp(empno,ename,deptno)
     values(v_empno,v_ename,v_deptno);
     dbms_output.put_Line('Inserted Successfully');
end;

The following is an example which can be used to update "ename" and "deptno" based on a given "empno" for the table "emp."

declare
     v_empno emp.empno%type := &empno;
     v_ename emp.ename%type := '&ename';
     v_deptno emp.deptno%type := &deptno;
begin
     update emp set ename=v_ename, deptno=v_deptno
     where empno=v_empno;
     dbms_output.put_line('Updated Successfully');
end;

The following is an example which can be used to delete a row from "emp" table based on the given "empno."

declare
     v_empno emp.empno%type := &empno; 
begin
     delete from emp where empno=v_empno;
     dbms_output.put_line('Deleted Successfully');
end;

We can even insert several rows into a table automatically using a FOR loop. Let us consider a table "sample," with columns "code" and "description." The following program inserts 100 rows into that table with automated values.

begin
     For I in 1..100
     loop
          insert into sample(code,description)
               values (I, 'Item ' || I);
     end loop;
end;

Observe that the above program doesn't need a "declare" statement, because the variable "I" doesn't need to be declared. If there are no variable declarations, no declaration section is necessary. We can even commit at every 10 insertions by modifying the program as follows:

Declare
     V_numIterations number;
Begin
     For I in 1..100
     Loop
          V_numIterations := V_numIterations + 1;
          Insert into sample(code,description)
               Values (I, 'Item ' || I);
          If v_numIterations = 10 then
               Commit;
               V_numIterations := 0;
          End if;
     End loop;
End;