Database Interaction with PL/SQL, part 1

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.

Introduction

This is part one of a series of articles focusing on database interactions with Oracle PL/SQL. The series starts from the very basics of PL/SQL. As part of this series, I will try to cover even very advanced programming in PL/SQL (with almost all important pre-defined packages available) through the latest, Oracle 10g, in a practical manner.

Another issue to keep in mind is that the series mainly focuses on database interactions. We will not consider any Web development or web services development using PL/SQL, as they require knowledge of several other technologies such as XML, Java, and so forth. 

I am assuming that the readers of this series will have some knowledge of RDBMS, Oracle SQL and some very fundamental grounding in PL/SQL such as data types, variable declarations, control structures (such as conditional statements and loops), and so forth. I am trying to keep all of the articles of this series as simple as possible to give beginners a very good kick start. All of the examples in this series can be executed on the fly just by using copy and paste (unless otherwise specified).

This article 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.” We conclude this article with some DML statements working within the PL/SQL program. By the end of this article, you should understand the following topics: the block structure of PL/SQL, variable declarations, how to accept input from the user, the SELECT..INTO statement, how to use %TYPE and %ROWTYPE wisely, and finally, how to manipulate information using DML statements.

{mospagebreak title=SELECT…INTO Statement}

This is a special statement; it’s a bit different from an ordinary SQL SELECT statement. It retrieves data from a table and places the values into variables. Why is this mechanism necessary? In general, a SELECT statement retrieves some information from tables and directly displays it on the screen. But in PL/SQL, we need to control the output in whatever way we like.

So, after placing the values of the SELECT..INTO statement into the variables, we display them using the DBMS_OUTPUT package. The SELECT…INTO statement should return one and only one row. It raises an error (or exception) if it returns more than one row or even none. So, it is our responsibility to design the SELECT..INTO statement in such a way that it should return one and only one row.

Let us consider our first example.

Declare
     v_empno number(4) := &empno;
     v_ename varchar2(10);
begin
     Select ename into v_ename
     from emp
     Where empno=v_empno;
     dbms_output.put_line(‘Name: ‘|| v_ename);
end;

The above program illustrates a simple PL/SQL block structure with variable declarations, PL/SQL body and even DBMS_OUTPUT package.

From the above program, we declare two variables, “v_empno” and “v_ename.” We accept input from the user just by putting the”&empno” into place. All this happens within the declaration section. Within the body (between “begin” and “end”), we fetch the “ename” and place it in “v_ename” from the table “emp.” based on the “where” condition framed from the user input. To display the value available in “v_ename,” we use DBMS_OUTPUT.PUT_LINE statement.

Please note that we need to execute the “set serveroutput on” statement at the sql prompt prior to the execution of the program, as we are working with DBMS_OUTPUT package.

Let us consider another example:

Declare
     v_empno number(4) := &empno;
     v_ename varchar2(10);
     v_sal number(4);
     v_job varchar2(20);
begin
     Select ename,sal,job into v_ename,v_sal,v_job
     from emp
     Where empno=v_empno;
     dbms_output.put_line(‘Name: ‘|| v_ename);
     dbms_output.put_line(‘Salary: ‘|| v_sal);
     dbms_output.put_line(‘Job: ‘|| v_job);
end;

I hope that this example is very clear in all of its aspects. The only difference is that we added few more fields (sal and job) and variables (v_sal and v_job) and displayed them. The most important issue to remember is that the order of the fields should coincide with the order of the variables.

{mospagebreak title=%TYPE attribute}

In the above program, we declared a variable “v_ename” as being of type “varchar(10).” What if we don’t know the data type (or data length) of the field “ename”? In general, we will switch back to the SQL prompt and issue a DESCRIBE statement to show all of the columns and datatypes (and of course the lengths, too) of a particular table. What if DBA increases the width of “ename” column without informing you (which happens very often)? Your program may not work correctly.

So, we need to have a special mechanism with which a variable should find the data type and width dynamically and automatically at runtime. This is where %TYPE comes in. Let us take a closer look at it, with a simple example.

Declare
     v_ename emp.ename%Type;
     v_empno emp.empno%type := &empno;
begin
     select ename into v_ename
     from emp
     where empno=v_empno;
     dbms_output.put_line(‘Name: ‘||v_ename);
end;

This is very similar to the first example, except for the declaration section. “v_ename” is declared as being of type “emp.ename%type.” What does it mean? It means that the data type of “v_ename” would be the same as the data type of the “ename” column in the “emp” table. Similarly, the other variable, “v_empno,” is also declared.

We can declare multiple variables and fetch accordingly, as shown in the following example:

Declare
     v_empno emp.empno%type := &empno;
     v_ename emp.ename%type;
     v_sal emp.sal%type;
     v_job emp.job%type;
begin
     select ename,sal,job into v_ename,v_sal,v_job
     from emp
     Where empno=v_empno;
     dbms_output.put_line(‘Name: ‘|| v_ename);
     dbms_output.put_line(‘Salary: ‘|| v_sal);
     dbms_output.put_line(‘Job: ‘|| v_job);
end;

{mospagebreak title=%ROWTYPE attribute}

What if we want more and more columns of values to be retrieved and pushed into variables? Consider a table of fifteen columns. Is it necessary to declare all fifteen variables to retrieve those values? This is where %ROWTYPE comes in. It can be used to declare a variable to store an entire row from a specified table. Let us go through a simple example again.

Declare
     v_empRec emp%rowtype;
     v_empno emp.empno%type := &empno;
begin
     select * into v_emprec
     from emp
     where empno=v_empno;
     dbms_output.put_line(‘Name: ‘||v_emprec.ename);
     dbms_output.put_line(‘Salary: ‘||v_emprec.sal);
     dbms_output.put_line(‘Job: ‘||v_emprec.job);
end;

In the above example, we declared a variable “v_empRec,” which can hold an entire row from the table “emp” (which is what “emp%rowtype” means). Within the body, we fetch an entire row (all of the columns) of information (using “*” in the “select” statement) and place it in “v_emprec.” To fetch the value of each and every column, we specify the column name with the “%rowtype” variable separated with a dot (watch the dbms_output statements).

Note that it is not compulsory to use all of the columns in our program. This concept of “%rowtype” is not suitable for retrieving information based on joins, but indeed it helps a lot by excluding unnecessary variable declarations. Let us conclude this section with one more example.

Declare
     v_empRec emp%rowtype;
     v_deptRec dept%rowtype;
     v_empno emp.empno%type := &empno;
begin
     select * into v_emprec
     from emp where empno=v_empno;
     Select * into v_deptRec
     from dept where deptno=v_empRec.deptno;
     dbms_output.put_line(‘Name: ‘||v_emprec.ename);
     dbms_output.put_line(‘Salary: ‘||v_emprec.sal);
     dbms_output.put_line(‘Job: ‘||v_emprec.job);
     dbms_output.put_line(‘Department: ‘||v_deptrec.dname);
     dbms_output.put_line(‘Location: ‘||v_deptrec.loc);
end;

Since this is not very complicated, I leave it to the readers to understand the above program.

{mospagebreak title=Using Other DML Commands}

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;

[gp-comments width="770" linklove="off" ]

chat sex hikayeleri Ensest hikaye