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 en…

Accepted Answer

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;
```

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

Solution

Problem

Related problems