Declaring a variable and setting its value from a SELECT query in Oracle

In SQL Server we can use this:

DECLARE @variable INT;
SELECT @variable= mycolumn from myTable;

How can I do the same in Oracle? I'm c开发者_运维百科urrently attempting the following:

DECLARE COMPID VARCHAR2(20);
SELECT companyid INTO COMPID from app where appid='90' and rownum=1;

Why this is not working?

---

SELECT INTO

DECLARE
   the_variable NUMBER;

BEGIN
   SELECT my_column INTO the_variable FROM my_table;
END;

Make sure that the query only returns a single row:

By default, a SELECT INTO statement must return only one row. Otherwise, PL/SQL raises the predefined exception TOO_MANY_ROWS and the values of the variables in the INTO clause are undefined. Make sure your WHERE clause is specific enough to only match one row

If no rows are returned, PL/SQL raises NO_DATA_FOUND. You can guard against this exception by selecting the result of an aggregate function, such as COUNT(*) or AVG(), where practical. These functions are guaranteed to return a single value, even if no rows match the condition.

A SELECT ... BULK COLLECT INTO statement can return multiple rows. You must set up collection variables to hold the results. You can declare associative arrays or nested tables that grow as needed to hold the entire result set.

The implicit cursor SQL and its attributes %NOTFOUND, %FOUND, %ROWCOUNT, and %ISOPEN provide information about the execution of a SELECT INTO statement.

---

Not entirely sure what you are after but in PL/SQL you would simply

DECLARE
  v_variable INTEGER;
BEGIN
  SELECT mycolumn
    INTO v_variable
    FROM myTable;
END;

Ollie.

---

One Additional point:

When you are converting from tsql to plsql you have to worry about no_data_found exception

DECLARE
   v_var NUMBER;
BEGIN
   SELECT clmn INTO v_var FROM tbl;
Exception when no_data_found then v_var := null; --what ever handle the exception.
END;

In tsql if no data found then the variable will be null but no exception

---

ORA-01422: exact fetch returns more than requested number of rows

if you don't specify the exact record by using where condition, you will get the above exception

DECLARE
     ID NUMBER;
BEGIN
     select eid into id from employee where salary=26500;
     DBMS_OUTPUT.PUT_LINE(ID);
END;

---

For storing a single row output into a variable from the select into query :

declare v_username varchare(20); SELECT username into v_username FROM users WHERE user_id = '7';

this will store the value of a single record into the variable v_username.

---

For storing multiple rows output into a variable from the select into query :

you have to use listagg function. listagg concatenate the resultant rows of a coloumn into a single coloumn and also to differentiate them you can use a special symbol. use the query as below SELECT listagg(username || ',' ) within group (order by username) into v_username FROM users;