Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

Tags:

oracle

plsql

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 currently attempting the following:

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

Why this is not working?

enter image description here

like image 568
Imran Qadir Baksh - Baloch Avatar asked Sep 26 '11 10:09

Imran Qadir Baksh - Baloch


People also ask

How do you declare and assign value to a variable in Oracle?

To assign a default value to a variable, you use the assignment operator ( := ) or the DEFAULT keyword. In this example, instead of using the assignment operator := , we used the DEFAULT keyword to initialize a variable.

How do you assign the result of a query to a variable in SQL?

The syntax for assigning a value to a SQL variable within a SELECT query is @ var_name := value , where var_name is the variable name and value is a value that you're retrieving. The variable may be used in subsequent queries wherever an expression is allowed, such as in a WHERE clause or in an INSERT statement.

How can values be assigned to a variable in PL SQL?

After the declaration, PL/SQL allocates memory for the variable's value and the storage location is identified by the variable name. Syntax for declaring variable: Following is the syntax for declaring variable: variable_name [CONSTANT] datatype [NOT NULL] [:= | DEFAULT initial_value]


1 Answers

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.

like image 108
Thilo Avatar answered Oct 04 '22 02:10

Thilo