HomeOracle Page 5 - Database Interaction with PL/SQL, part 1
Using Other DML Commands - Oracle
This first article in a series focusing on database interactions with Oracle PL/SQL serves as a good introduction to the topic. It mainly focuses on the basics of retrieving information from the Oracle database using the SELECT..INTO statement, as well as two attributes, namely "%TYPE" and "%ROWTYPE." Readers should have some knowledge of RDBMS, Oracle SQL, and some very fundamental grounding in PL/SQL.
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."
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;