Home arrow Oracle arrow 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.

TABLE OF CONTENTS:
  1. Database Interaction with PL/SQL, part 1
  2. SELECT…INTO Statement
  3. %TYPE attribute
  4. %ROWTYPE attribute
  5. Using Other DML Commands
By: Jagadish Chatarji
Rating: starstarstarstarstar / 36
May 24, 2005

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

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;



 
 
>>> More Oracle Articles          >>> More By Jagadish Chatarji
 

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort
   

ORACLE ARTICLES

- Oracle Java Security Woes Continue
- Oracle's New IaaS Cloud Option: There's a Ca...
- Oracle Acquires Eloqua to Boost Cloud Presen...
- Choosing Innovation: Oracle Survey Insights
- Oracle Fixes Privilege Escalation Bug
- Oracle`s Communications Service Availability...
- Oracle Releases Exalytics, Taleo Plans
- Oracle Releases Communications Network Integ...
- Oracle Releases Communications Data Model 11...
- Oracle Releases PeopleSoft PeopleTools 8.52
- Oracle Integrates Cloudera Apache Distro, My...
- Oracle Releases MySQL 5.5.18
- Oracle Announces NoSQL Database Availability
- Sorting Database Columns With the SELECT Sta...
- Retrieving Table Data with the LIKE Operator

Developer Shed Affiliates

 


Dev Shed Tutorial Topics: