Как объявлять и использовать переменные в PL/SQL, как это делается в T-SQL?

В Sql Server часто, когда я тестирую тело хранимой процедуры, я копирую тело в SSMS, РАЗРЕШАЮ переменные в верхней части страницы, устанавливаю их в некоторые примерные значения и выполняю тело как есть.

Например, если моя процедура

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

Тогда мой тестовый sql будет выглядеть следующим образом

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

    SELECT @Name

Каков эквивалент этого в Oracle PL/SQL?

Это самое близкое, что я придумал, но я получаю "PLS-00428: в этом операторе SELECT ожидается предложение INTO"

DECLARE
   myname varchar2(20);
BEGIN
     myname := 'Tom';

     select myname from DUAL;
END;

Вот лучший пример того, что я пытаюсь сделать:

DECLARE
   myname varchar2(20);
BEGIN
     myname := 'Tom';

     SELECT *
     FROM   Customers
     WHERE  Name = myname;
END;

Но опять же, он хочет 'INTO', в то время как на самом деле я просто хочу, чтобы записи печатались на экране, а не хранились в другой таблице.....

РАЗРЕШЕНО:

Благодаря @Allan, я добился того, что все работает достаточно хорошо. Oracle SQL Developer, очевидно, запоминает значения параметров, которые вы ему предоставляете. PL/SQL Developer, однако, не хочет иметь ничего общего с этим.....

Если вы "Запустите как сценарий", он будет подчиняться вашим значениям по умолчанию, но результаты будут возвращаться только в виде ASCI-текста, а не в виде сетки/таблицы.

Tom Halladay
Редактировал вопрос 6-го июня 2012 в 2:58
Программирование
oracle sql-server tsql plsql oracle-sqldeveloper
4-го июня 2012 в 6:48
37 просмотров
Решение

Пересмотренный ответ

Если вы не вызываете этот код из другой программы, то есть вариант пропустить PL/SQL и сделать это строго на SQL, используя переменные привязки:

var myname varchar2(20);

exec :myname := 'Tom';

SELECT *
FROM   Customers
WHERE  Name = :myname;

Во многих программах (таких как Toad и SQL Developer), если опустить операторы var и exec, программа запросит у вас значение.

Оригинальный ответ

Большое различие между T-SQL и PL/SQL заключается в том, что Oracle не позволяет вам неявно возвращать результат запроса. Результат всегда должен быть явно возвращен каким-то образом. Самый простой способ - использовать DBMS_OUTPUT (примерно эквивалентно print) для вывода переменной:

DECLARE
   myname varchar2(20);
BEGIN
     myname := 'Tom';

     dbms_output.print_line(myname);
END;

Однако это не очень удобно, если вы пытаетесь вернуть набор результатов. В этом случае вы захотите вернуть либо коллекцию, либо рефкурсор. Однако использование любого из этих решений потребует обернуть ваш код в функцию или процедуру и запустить функцию/процедуру из чего-то, что способно получить результаты. Функция, работающая таким образом, могла бы выглядеть примерно так:

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;
Allan
Редактировал ответ 12-го августа 2013 в 4:12
Комментарии (6)

В Oracle PL/SQL, если вы выполняете запрос, который может вернуть несколько строк, вам нужен курсор для итерации результатов. Самый простой способ - цикл for, например:

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;

Если запрос возвращает ровно одну строку, то можно использовать синтаксис select...into..., например:

declare 
  myname varchar2(20);
begin
  select first_name into myname 
    from mytable 
    where person_id = 123;
end;
Комментарии (0)