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

in sql server, times when i'm testing body of stored procedure, copy body ssms, declare variables @ top of page, set them sample values, , execute body as-is.

for example, if proc is

create proc mysampleproc     @name   varchar(20)     select @name 

then test sql be

declare @name varchar(20) set     @name = 'tom'      select @name 

what oracle pl/sql equivalent this?

this closest i've come with, i'm getting "pls-00428: clause expected in select statement"

declare    myname varchar2(20); begin      myname := 'tom';       select myname dual; end; 

this better example of i'm trying do:

declare    myname varchar2(20); begin      myname := 'tom';       select *        customers       name = myname; end; 

but again, wants 'into' when want records printed on screen, not stored in table....

resolved:

thanks @allan, i've got working enough. oracle sql developer apparently remembers parameter values supply with. pl/sql developer, however, wants nothing this....

if "run script", abide defaults, return results asci text, not in grid/spreadsheet

revised answer

if you're not calling code program, option skip pl/sql , strictly in sql using bind variables:

var myname varchar2(20);  exec :myname := 'tom';  select *   customers  name = :myname; 

in many tools (such toad , sql developer), omitting var , exec statements cause program prompt value.

---

original answer

a big difference between t-sql , pl/sql oracle doesn't let implicitly return result of query. result has explicitly returned in fashion. simplest way use dbms_output (roughly equivalent print) output variable:

declare    myname varchar2(20); begin      myname := 'tom';       dbms_output.print_line(myname); end; 

this isn't terribly helpful if you're trying return result set, however. in case, you'll either want return collection or refcursor. however, using either of solutions require wrapping code in function or procedure , running function/procedure that's capable of consuming results. function worked in way might this:

create function my_function (myname in varchar2)      my_refcursor out sys_refcursor begin      open my_refcursor      select *        customers       name = myname;       return my_refcursor; end my_function;