Examples Using the SELECT...INTO Statement in PL/SQL

Use the SELECT... INTO statement to retrieve exactly one row of data. TimesTen returns an error for any query that returns no rows or multiple rows.

The section provides the following examples:

• Using SELECT... INTO to Return Sum of Salaries

• Using SELECT...INTO to Query Another User's Table

Using SELECT... INTO to Return Sum of Salaries

This example uses a SELECT...INTO statement to calculate the sum of salaries for all employees in the department where department_id is 60.

Command> DECLARE
           v_sum_sal  NUMBER (10,2);
           v_dept_no  NUMBER NOT NULL := 60;
         BEGIN
           SELECT SUM(salary) -- aggregate function
           INTO v_sum_sal FROM employees
           WHERE department_id = v_dept_no;
           DBMS_OUTPUT.PUT_LINE ('Sum is ' || v_sum_sal);
          END;
          /
Sum is 28800
 
PL/SQL procedure successfully completed.

Using SELECT...INTO to Query Another User's Table

This example provides two users, USER1 and USER2, to show one user employing SELECT...INTO to query another user's table.

The following privileges are assumed:

grant create session to user1;
grant create session to user2;
grant create table to user1;
grant select on user1.test to user2;

USER1:

Command> create table test(name varchar2(20), id number);
Command> insert into test values('posey', 363);
1 row inserted.

USER2:

Command> declare
            targetid number;
         begin
            select id into targetid from user1.test where name='posey';
            dbms_output.put_line('Target ID is ' || targetid);
         end;
         /
Target ID is 363
 
PL/SQL procedure successfully completed.