В чем разница между объявлениями переменных VARIABLE, DEFINE и DECLARE?

#sql #oracle #plsql

В чем разница между объявлениями переменных?  

1 Инструкция var:

var id number;
exec :id := 1;
SELECT * FROM table_a WHERE id= :id ;


2 и 3 DEFINE и DECLARE 

DEFINE id =1;
SELECT * FROM table_a WHERE id= &id;

DECLARE
    v_text VARCHAR2(10); -- declare
BEGIN
    v_text := 'Hello';  --assign
    dbms_output.Put_line(v_text); --display
END; 


http://ss64.com/ora/syntax-variables.html
    

Ответы

Ответ 1

1.  var[iable] - это способ объявления переменных в SQL*Plus, которые должны иметь
какой-либо тип из указанных в справке. Их можно использовать в sql и в pl/sql как для
подстановки каких-либо значений так и для сохранения значений, например:

variable value varchar2(10);

begin
  select 'a'
  into :value
  from dual;
end;
/

select :value from dual
/


2.  def[ine] - это способ объявления переменных в SQL*Plus, в которых можно указать
текст, который будет подставлен вместо них в те места, где они используются. Так же
с помощью этой команды можно получить список всех существующих переменных которые можно
использовать для подстановки.

Если объявить переменную заранее и присвоить ей значение, то оно просто будет подставлено
в тексте запроса:

define value = dual
select * from &value
/


Этот запрос выдаст такой результат:


old   1: select * from &value
new   1: select * from dual

D
-
X


В переменной можно указать почти любой текст:

define value ='23 from dual'
select 1&value
/
old   1: select 1&value
new   1: select 123 from dual

       123
----------
       123


Если переменной заранее не присвоить какое-либо текстовое значение, а просто использовать
в тексте запроса, то SQL*Plus предложит ввести ее значение:

select * from &another_value
/


После выполнения этого текста SQL*Plus отобразит на экране просьбу указать значения
для переменной:


Enter value for another_value: 


Указав которое (в нашем случае dual) и нажав Enter мы увидим такой результат:


old   1: select * from &another_value
new   1: select * from dual

D
-
X


При вызове просто команды


define


Выведется примерно такой список уже существующих переменных:


DEFINE _DATE           = "03-AUG-15" (CHAR)
DEFINE _CONNECT_IDENTIFIER = "orcl" (CHAR)
DEFINE _USER           = "SYS" (CHAR)
DEFINE _PRIVILEGE      = "AS SYSDBA" (CHAR)
DEFINE _SQLPLUS_RELEASE = "1102000200" (CHAR)
DEFINE _EDITOR         = "Notepad" (CHAR)
DEFINE _O_VERSION      = "Oracle Database 11g Enterprise Edition Release 11.2.0.2.0
- Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options" (CHAR)
DEFINE _O_RELEASE      = "1102000200" (CHAR)
DEFINE VALUE           = "dual" (CHAR)


3.  declare - это часть объявления pl/sql блока кода define ... begin ... end после
которой и до begin идет объявление переменных, которые можно использовать внутри блока
begin ... end Например:

declare
  val1 number;
  val2 varchar2(10);
  val3 date;
begin
  select 1, 'a'
  into val1, val2
  from dual;
  val3 := sysdate;
  dbms_output.put_line(val1);
  dbms_output.put_line(val2);
  dbms_output.put_line(val3);
end;
/


Ответ 2

Разница объявляемых переменных заключается в месте их применения и, соответственно,
в месте хранения значений этих переменных.


Подстановочные переменные  хоста (host or bind  variables) и переменные замещения
(substitute variable) объявляются и хранятся в клиентской программе.
Обявление переменных в клиенте зависит от клинтской программы и может несколько отличаться
или вообще отсутствовать. Объявления def[ine] и var[iable] присходят от SQL*Plus, но
поддерживаются также многими другими программными продуктами для работы с БД Oracle,
например: SQL Developer, TOAD.   


var[iable] - подстановочные переменные с указанием типа данных.
Инициализировать эти переменные возможно только в PL/SQL блоке. Могут использоваться
для подстановки как входных так и выходных значений полей в DML запросах. Подстановочные
переменные могут так же быть в анонимных PL/SQL блоках как для передачи так и для чтения.
Подстановка переменных осуществляется на этапе подстановки (bind) для входных значений,
или определения результата (define output) для выходных значений, непосредственно перед
выполнением (execute).
Важно: Подстановочные переменные не могут быть использованы для имён полей, таблиц,
представлений и других объектов БД, так как они необходимы на этапе подготовки к выполнению
(parse).
def[ine] - переменные замещения. Они объявляются и сразу же инициализируются символьным
значением. Все встретившиеся имена переменных с префиксом & будут замещены символьным
значением этих переменных. Замещение произойдёт в клиенте ешё до отправки DML/DDL/DCL
выражения или PL/SQL блока на выполнение серверу БД, поэтому каких либо ограничений,
какая их часть может быть замещена, не существует.            

Переменные языка PL/SQL обьявляются в програмном блоке. Они не зависят от клиентской
программы, так как в клиенте это только текст программы, который должен быть отправлен
на сервер БД. Инициализируются переменные этого типа при выполнении програмного блока
на сервере БД. Хранятся эти переменные в UGA (user global area) так же на сервере БД. 


PL/SQL переменные обьявляются в блоке после ключевого слова declare. В именных блоках
переменные могут быть объявлены сразу после заголовка create|alter ... is|as объявления/изменения
именного объекта. PL/SQL блоки могут содержать вложенные блоки. Зона видимости переменных
ограничена блоком, в котором они объявлены. Время жизни переменных объявленых в пакетах
(packaged variables) - сессия, во всех остальных случаях - время выполнения именного
объекта или анонимного блока.        



Пример исполъзующий все виды вышеописаных переменных для динамического выполнения
скриптов в SQL*Plus - есть некое клолличество скриптов, но зарение неизвестно какой
из них вызывать, т.е. его надо определить динамически и тут же вызвать. Все виды переменных
и алиас колонки умышлено используют одно и то же имя sqlfile:

define sqlfile='default';
variable sqlfile varchar2(100);
declare
    sqlfile varchar2(100);
begin
    <> declare
         -- в этом блоке динамически определяем имя скрипта
         localFile constant varchar2(100) := 'my_sqlscript_01';
    begin
        sqlfile := localFile; 
    end;
    :sqlfile := sqlfile;
end;
/
column sqlfile new_value sqlfile noprint format A100;
select nvl(:sqlfile, '&sqlfile') sqlfile from dual;

host echo "prompt # &sqlfile running ..." >sql/&sqlfile\.sql
@sql/&sqlfile


Вывод:

# my_sqlscript_01 running ...