Select value from aggregate function to variable : Select Into « PL SQL « Oracle PL / SQL






  1. Oracle PL / SQL
  2. PL SQL
  3. Select Into

Select value from aggregate function to variable

    

SQL> -- create demo table
SQL> create table emp(
  2    ID                 VARCHAR2(4 BYTE)         NOT NULL,
  3    fname         VARCHAR2(10 BYTE),
  4    lname          VARCHAR2(10 BYTE),
  5    Start_Date         DATE,
  6    End_Date           DATE,
  7    Salary             Number(8,2),
  8    City               VARCHAR2(10 BYTE),
  9    Description        VARCHAR2(15 BYTE)
 10  )
 11  /

Table created.

SQL>
SQL>
SQL>
SQL> -- prepare data
SQL> insert into emp(ID,  fname, lname, Start_Date,                     End_Date,                       Salary,  City,       Description)
  2               values ('01','Jason',    'Martin',  to_date('19960725','YYYYMMDD'), to_date('20060725','YYYYMMDD'), 1234.56, 'Toronto',  'Programmer')
  3  /

1 row created.

SQL> insert into emp(ID,  fname, lname, Start_Date,                     End_Date,                       Salary,  City,       Description)
  2                values('02','Alison',   'Mathews', to_date('19760321','YYYYMMDD'), to_date('19860221','YYYYMMDD'), 6661.78, 'Vancouver','Tester')
  3  /

1 row created.


SQL>
SQL>
SQL>   declare
  2       empCount number;
  3       i           number;
  4    begin
  5       select count(*) into empCount from emp;
  6
  7       FOR i IN 1 .. empCount  LOOP
  8          dbms_output.put_line('emp ' || i);
  9       END LOOP;
 10   end;
 11  /
emp 1
emp 2

PL/SQL procedure successfully completed.

SQL>
SQL> drop table emp;

Table dropped.

SQL>
SQL>








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.Select value to variable one by one
14.If no records are retrieved for a SELECT - INTO statement the following error is returned
15.If too many records are returned for a SELECT - INTO statement the following error is returned
16.no_data_found from select ... into
17.select bulk collect into table collection
18.Use subquery in pl/sql block
19.This script demonstrates how to do a non-bulk select into elements of a PL/SQL table.
20.TOO_MANY_ROWS exception and select into command
21.Store max(salary) to a variable
22.Store max(tableName.column) to tableName.column.type variable
23.Bulk Collection: fetch a single row from the ALL_OBJECTS table.
24.Calculate salary by adding salary with max(salary)
25.Select data for update