Database Interaction with PL/SQL, part 1

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.