Database Interaction with PL/SQL, part 1 - Using Other DML Commands
(Page 5 of 5 )
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;
| DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware. |
