I can't seem to get variables to work in an Oracle PL/SQL where clause. I come from a Microsoft SQL Server background and there it was easy. For example, what would be all steps needed to do something similar to the following?
declare @var int set @var = 1
select * from SomeTable where SomeField = @var
This doesn't seem like it should be hard in PL/SQL, but evidently it is. :-/ I hear I need to use cursors and the like in PL/SQL?
Any help would be greatly appreciated. Thanks.
What do you want to do with the data that the SELECT returns? If you just want to see it you don't need PL/SQL at all, just do this in SQL Plus:
variable var number
exec :var := 1
select * from SomeTable where SomeField = :var;
Or in a tool like SQL Developer or Toad, just do this:
select * from SomeTable where SomeField = :var;
and it will prompt you to enter the value for :var.
The following code declares a variable var to use in the WHERE clause, and a variable result to put the result in then executes it inside a PL/SQL block.
DECLARE
var INT := 1;
result INT;
BEGIN
SELECT 123
INTO result
FROM DUAL
WHERE var = 1;
DBMS_OUTPUT.put_line (var);
DBMS_OUTPUT.put_line (result);
END;
The DBMS_OUTPUT.PUT_LINE calls make it produce this DBMS output:
1
123
declare
type t_rec is record
(
col1 number,
col2 myTable.col2%type
);
v_rec t_rec;
type t_tab is table of v_rec%type index by binary_integer;
v_tab t_tab;
begin
select col1, col2
bulk collect into v_tab
from myTable
where col3 = 'BLAH';
-- do something great with v_tab...
end;
Also know that if you try to select into (or bulk collect into) a variable and no rows are returned, you'll get a no_data_found exception, so you may want to handle that situation.
See more here on pl/sql collections. Above uses an associative array, but there are nested tables and varrays as well. Again, see the link.
Hope that helps.
I use it like this
select * from sec_mainmenu where serno = '&MENU_ID';
When you run it, pl/sql will prompt for MENU_ID value.
