I come from an SQL Server background and I'm trying to figure out how to use Oracle PL/SQL variables. I'm trying to declare a variable and then select that variable. It sounds simple, but I am doing something wrong. Here's my code:
declare
num number;
begin
num := 64;
select num from dual; --error here
end;
/
This gives me an error message that says: "PLS-00428: an INTO clause is expected in this SELECT statement".
I tried a few other permutations of this but each gives me an error:
declare
num number;
begin
num := 64;
end;
select num from dual; --"num" invalid identifier
/
And this one also gives me an error.
declare
num number;
begin
num := 64;
end;
/
select num from dual; --"num" invalid identifier
Based on Mihai's comment, I tried putting the declare after the begin, but that threw errors as well:
begin
declare
num number;
num := 64; --encountered symbol "=" when expecting ...
end;
/
select num from dual; --"num" invalid identifier
For reference: here's how I would do this in SQL Server. It may not be clear, but the scope of the @num variable below is the file containing the declaration. This variable is not global.
declare @num int = 64
select @num
I am doing this is DBArtisan. Note that this question is similar to this one here, but I'm also asking how to use the variable. And I'm not interested in bind variables (I don't think). I want to write one-off queries like this: select * from employee where manager_id = mgrID
with the mgrID being a variable that I declare.
Best Answer
I think these answers are all getting a) way too complicated, and b) off-track relative to what user2023861 wants to accomplish:
"I am trying to declare a variable, then select that variable." means two different things in either PL/SQL or in SQLplus, and perhaps two more things in DBArtisan. To accomplish the objective in SQLplus, use "Substitution variables"...excellent background is found here.
My example, doesn't precisely follow your SQL Server T-SQL example, but it's close and is NOT procedural (read: no anonymous PL/SQL block.)
PL/SQL with Substitution variables:
All at the SQL-Plus prompt....from my fictional employees table, I got this result:
If you want to make this semi-procedural, then you use BIND Variables and the declarative syntax of PL/SQL similar to T-SQL. (In my opinion this is a HUGE waste of effort, but is included to help clarify variable types.)
SQLPlus using PL/SQL Bind variables:
Fully-procedural approach using PL/SQL:
So, when do I use each case? I use SQLplus Substitution variables 75% of the time when I write SQL scripts that I want to parameterize the input for. Executing a script at the SQLplus prompt allows passing inbound arguments, so my favorite Substitution variables are &1 and &2 as in the following example:
and executes SQL code similar to
Notice how the inbound arguments are assigned into substitution variables, then used in the SQL statement. I never use the middle example, and I always use the 3rd examples (procedural PL/SQL) when writing functions, procs, and packages.