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....
![enter image description here](https://i.stack.imgur.com/Hodah.png)
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
![enter image description here](https://i.stack.imgur.com/3L2IA.png)
See Question&Answers more detail:
os 与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…