How to use variables in Oracle PL / SQL where clause

Question

I cannot get the variables to work in the Oracle PL / SQL where clause. I came from the background of Microsoft SQL Server and it was easy there. For example, what steps would you have to take to do something similar to the following?

declare @var int set @var = 1

select * from SomeTable where SomeField = @var

It doesn't seem like it should be complicated in PL / SQL, but obviously it is: - / I heard that I need to use cursors, etc. In PL / SQL?

Any help would be greatly appreciated. Thank.

Answer 1

What do you want to do with the data returned by SELECT? If you just want to see it, you don't need PL / SQL at all, just do it in SQL Plus:

variable var number
exec :var := 1

select * from SomeTable where SomeField = :var;

, SQL Developer Toad, :

select * from SomeTable where SomeField = :var;

: var.

Answer 2

var WHERE, result, , PL/SQL.

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;

DBMS_OUTPUT.PUT_LINE :

1
123

Answer 3

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;

, ( ) , no_data_found, .

. pl/sql. , varrays. , . .

, .

Answer 4

select * from sec_mainmenu where serno = '&MENU_ID';

When you run it, pl / sql will ask for the value of MENU_ID.