CODEWITHSUNDEEP
sqloracle-database
Stefan
Stefan

Reputation: 113

How can I assign a variable a select?

I have the following piece of code:

DECLARE
  v_oras locations.city%TYPE;
BEGIN
  SELECT l.city FROM locations l,departments d WHERE l.location_id = d.location_id AND d.department_id = 30;
END;
/

How can I assign the select to the variable previous declared?

Upvotes: 0

Views: 66

Answers (3)

Kamil Gosciminski
Kamil Gosciminski

Reputation: 17137

Use a SELECT INTO statement.

DECLARE
  v_oras locations.city%TYPE;
BEGIN
  SELECT l.city 
  INTO v_oras 
  FROM locations l
  JOIN departments d ON l.location_id = d.location_id
  WHERE d.department_id = 30;
END;

This could be used with multiple values being returned from your SELECT statement and assigned INTO declared variables as well.

Upvotes: 5

zschallz
zschallz

Reputation: 103

SELECT INTO is the simplest way. https://docs.oracle.com/cd/B19306_01/appdev.102/b14261/selectinto_statement.htm

Just understand that: 1. If the query returns more than one row, the TOO_MANY_ROWS exception will be raised. 2. If the query returns no data, the NO_DATA_FOUND exception will be raised.

If you can't guarantee neither of those will happen, you may need to handle these exceptions.

See the usage notes section in the documentation linked above.

Upvotes: 3

Vampiro
Vampiro

Reputation: 335

DECLARE
  v_oras locations.city%TYPE;
BEGIN
  SELECT l.city 
    INTO v_oras
    FROM locations l,departments d 
   WHERE l.location_id = d.location_id 
     AND d.department_id = 30;
END;
/

Upvotes: 3

Related Questions