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

Question

In Sql Server, often times when I'm testing the body of a stored procedure, I copy the body into SSMS, DECLARE the variables at the top of the page, set them to some sample values, and execute the body as-is.

For Example, if my proc is

CREATE PROC MySampleProc     @Name   VARCHAR(20) AS     SELECT @Name 

Then my test sql would be

DECLARE @Name VARCHAR(20) SET     @Name = 'Tom'      SELECT @Name 

What is the Oracle PL/SQL equivalent to this?

This is the closest that I've come up with, but I'm getting "PLS-00428: an INTO clause is expected in this SELECT statement"

DECLARE    myname varchar2(20); BEGIN      myname := 'Tom';       select myname from DUAL; END; 

This is a better example of what I'm really trying to do:

DECLARE    myname varchar2(20); BEGIN      myname := 'Tom';       SELECT *      FROM   Customers      WHERE  Name = myname; END; 

But again, it wants an 'INTO' when really I just want the records printed on the screen, not stored in another table....

RESOLVED:

Thanks to @Allan, I've got it working well enough. Oracle SQL Developer apparently remembers the parameter values you supply it with. PL/SQL Developer, however, wants nothing to do with this....

If you "Run As Script", it will abide by your defaults, but it will only return results as ASCI text, not in a grid/spreadsheet

Answer 1

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; 

Answer 2

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;