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

User · Answer

Variables are not defined  but declared   This is possible duplicate of declare variables in a pl sql block  But you can look here     http   docs oracle com cd B19306 01 appdev 102 b14261 fundamentals htm i27306  http   docs oracle com cd B19306 01 appdev 102 b14261 overview htm  UPDATE   Refer here   How to return a resultset   cursor from a Oracle PL SQL anonymous block that executes Dynamic SQL

User · Answer

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

User · 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

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

Examples related to sql-server

Examples related to oracle

Examples related to tsql

Examples related to plsql

Examples related to oracle-sqldeveloper