Select value to variable one by one

    
SQL>
SQL> create table emp(
  2           emp_id                integer         primary key
  3          ,lastname               varchar2(20)    not null
  4          ,firstname              varchar2(15)    not null
  5          ,midinit                varchar2(1)
  6          ,street                 varchar2(30)
  7          ,city                   varchar2(20)
  8          ,state                  varchar2(2)
  9          ,zip                    varchar2(5)
 10          ,shortZipCode                   varchar2(4)
 11          ,area_code              varchar2(3)
 12          ,phone                  varchar2(8)
 13          ,company_name           varchar2(50));

Table created.

SQL>
SQL>
SQL> insert into emp(emp_id,lastname,firstname,midinit,street,city,state,zip,shortZipCode,area_code,phone,company_name)values
  2                      (1,'Jones','Joe','J','1 Ave','New York','NY','11202','1111','212', '221-4333','Big Company');

1 row created.

SQL> insert into emp(emp_id,lastname,firstname,midinit,street,city,state,zip,shortZipCode,area_code,phone,company_name)values
  2                      (2,'Smith','Sue','J','1 Street','New York','NY','11444','1111','212', '436-6773','Little Company');

1 row created.

SQL> insert into emp(emp_id,lastname,firstname,midinit,street,city,state,zip,shortZipCode,area_code,phone,company_name)values
  2                      (3,'X','Peggy','J','1 Drive','New York','NY','45502','2222','212', '234-4444','Medium Company');

1 row created.

SQL>
SQL> declare
  2    v1 number;
  3  begin
  4    for x in 1..100 loop
  5    select emp_id into v1 from emp
  6      where emp_id = 1;
  7    end loop;
  8  end;
  9  /

PL/SQL procedure successfully completed.

SQL>
SQL>
SQL> drop table emp;

Table dropped.

Related examples in the same category

1.	Select value from table into variable
2.	Oracle returns an error when a SELECT statement returns more than one row
3.	Catch too_many_rows Exception for 'Select into' statement
4.	Multiple-Row SELECT Command with Several Exception-Handling Routines
5.	Select count result into a variable
6.	Output variable after 'select into'
7.	SELECT into value pair
8.	Select the number of employees into the l_emp_count variable
9.	Select into and subquery
10.	Select single value into variable
11.	Select two columns into a cursor variable
12.	Select value into a number variable in a for loop
13.	If no records are retrieved for a SELECT - INTO statement the following error is returned
14.	If too many records are returned for a SELECT - INTO statement the following error is returned
15.	no_data_found from select ... into
16.	select bulk collect into table collection
17.	Use subquery in pl/sql block
18.	This script demonstrates how to do a non-bulk select into elements of a PL/SQL table.
19.	TOO_MANY_ROWS exception and select into command
20.	Store max(salary) to a variable
21.	Store max(tableName.column) to tableName.column.type variable
22.	Bulk Collection: fetch a single row from the ALL_OBJECTS table.
23.	Calculate salary by adding salary with max(salary)
24.	Select data for update
25.	Select value from aggregate function to variable

Select value to variable one by one : Select Into « PL SQL « Oracle PL / SQL

Related examples in the same category