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?
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.
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;
/
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.
