Home arrow Oracle arrow Page 2 - Database Interaction with PL/SQL, part 1

SELECT…INTO Statement - 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.

  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



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.

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

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:

     v_empno number(4) := &empno;
     v_ename varchar2(10);
     v_sal number(4);
     v_job varchar2(20);
     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);

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.

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

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort


- 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: