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 Customers
WHERE 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_refcursor
BEGIN
open my_refcursor for
SELECT *
FROM Customers
WHERE Name = myname;
return my_refcursor;
END my_function;