How do I declare and use variables in PL/SQL like I do in T-SQL?

Revised Answer

If you're not calling this code from another program, an option is to skip PL/SQL and do it strictly in SQL using bind variables:

var myname varchar2(20);exec :myname := 'Tom';SELECT *FROM   CustomersWHERE  Name = :myname;

In many tools (such as Toad and SQL Developer), omitting the var and exec statements will cause the program to prompt you for the value.

Original Answer

A big difference between T-SQL and PL/SQL is that Oracle doesn't let you implicitly return the result of a query. The result always has to be explicitly returned in some fashion. The simplest way is to use DBMS_OUTPUT (roughly equivalent to print) to output the variable:

DECLARE   myname varchar2(20);BEGIN     myname := 'Tom';     dbms_output.print_line(myname);END;

This isn't terribly helpful if you're trying to return a result set, however. In that case, you'll either want to return a collection or a refcursor. However, using either of those solutions would require wrapping your code in a function or procedure and running the function/procedure from something that's capable of consuming the results. A function that worked in this way might look something like this:

CREATE FUNCTION my_function (myname in varchar2)     my_refcursor out sys_refcursorBEGIN     open my_refcursor for     SELECT *     FROM   Customers     WHERE  Name = myname;     return my_refcursor;END my_function;

In Oracle PL/SQL, if you are running a query that may return multiple rows, you need a cursor to iterate over the results. The simplest way is with a for loop, e.g.:

declare  myname varchar2(20) := 'tom';begin  for result_cursor in (select * from mytable where first_name = myname) loop    dbms_output.put_line(result_cursor.first_name);    dbms_output.put_line(result_cursor.other_field);  end loop;end;

If you have a query that returns exactly one row, then you can use the select...into... syntax, e.g.:

declare   myname varchar2(20);begin  select first_name into myname     from mytable     where person_id = 123;end;

Variables are not defined, but declared.

This is possible duplicate of declare variables in a pl/sql block

But you can look here :

http://docs.oracle.com/cd/B19306_01/appdev.102/b14261/fundamentals.htm#i27306

http://docs.oracle.com/cd/B19306_01/appdev.102/b14261/overview.htm

UPDATE:

Refer here : How to return a resultset / cursor from a Oracle PL/SQL anonymous block that executes Dynamic SQL?