Я делаю несколько тестов, написанных на SQL. Я хочу написать многоразовый код и поэтому хочу объявить некоторые переменные в начале и повторно использовать их в script, например:
DEFINE stupidvar = 'stupidvarcontent';
SELECT stupiddata
FROM stupidtable
WHERE stupidcolumn = &stupidvar;
Как объявить переменную и повторно использовать ее в следующих инструкциях? (Я использую SQLDeveloper для доступа к Oracle DB.)
Я пробовал до сих пор:
BEGIN
и END;
. Возвращает переменную с помощью &stupidvar
.DEFINE
и получите доступ к переменной.VARIABLE
и доступа к переменной.Но во время моих попыток возникают всевозможные ошибки (Unbound variable, Syntax error, Expected SELECT INTO
...).
При поиске в сети я нахожу много разных объяснений, касающихся SQL, PL/SQL.
Заранее благодарю вас!
Существует несколько способов объявления переменных в сценариях SQL * Plus.
Во-первых, используйте VAR. Механизм назначения значений VAR имеет вызов EXEC:
SQL> var name varchar2(20)
SQL> exec :name := 'SALES'
PL/SQL procedure successfully completed.
SQL> select * from dept
2 where dname = :name
3 /
DEPTNO DNAME LOC
---------- -------------- -------------
30 SALES CHICAGO
SQL>
VAR особенно полезен, когда мы хотим вызвать хранимую процедуру с параметрами OUT или функцией.
Alternativley мы можем использовать переменные-подстановки. Они хороши для интерактивного режима:
SQL> accept p_dno prompt "Please enter Department number: " default 10
Please enter Department number: 20
SQL> select ename, sal
2 from emp
3 where deptno = &p_dno
4 /
old 3: where deptno = &p_dno
new 3: where deptno = 20
ENAME SAL
---------- ----------
CLARKE 800
ROBERTSON 2975
RIGBY 3000
KULASH 1100
GASPAROTTO 3000
SQL>
Когда мы пишем script, который вызывает другие скрипты, может быть полезно ОПРЕДЕЛИТЬ переменные upfront:
SQL> def p_dno = 40
SQL> select ename, sal
2 from emp
3 where deptno = &p_dno
4 /
old 3: where deptno = &p_dno
new 3: where deptno = 40
no rows selected
SQL>
Наконец, есть анонимный блок PL/SQL. Как вы видите, мы все равно можем назначать значения объявленным переменным в интерактивном режиме:
SQL> set serveroutput on size unlimited
SQL> declare
2 n pls_integer;
3 l_sal number := 3500;
4 l_dno number := &dno;
5 begin
6 select count(*)
7 into n
8 from emp
9 where sal > l_sal
10 and deptno = l_dno;
11 dbms_output.put_line('top earners = '||to_char(n));
12 end;
13 /
Enter value for dno: 10
old 4: l_dno number := &dno;
new 4: l_dno number := 10;
top earners = 1
PL/SQL procedure successfully completed.
SQL>
Попробуйте использовать двойные кавычки, если это переменная char:
DEFINE stupidvar = "'stupidvarcontent'";
или
DEFINE stupidvar = 'stupidvarcontent';
SELECT stupiddata
FROM stupidtable
WHERE stupidcolumn = '&stupidvar'
UPD:
SQL*Plus: Release 10.2.0.1.0 - Production on Wed Aug 25 17:13:26 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
SQL> conn od/od@etalon
Connected.
SQL> define var = "'FL-208'";
SQL> select code from product where code = &var;
old 1: select code from product where code = &var
new 1: select code from product where code = 'FL-208'
CODE
---------------
FL-208
SQL> define var = 'FL-208';
SQL> select code from product where code = &var;
old 1: select code from product where code = &var
new 1: select code from product where code = FL-208
select code from product where code = FL-208
*
ERROR at line 1:
ORA-06553: PLS-221: 'FL' is not a procedure or is undefined
ORA-01008: not all variables bound
.
В PL/SQL v.10
keyword declare используется для объявления переменной
DECLARE stupidvar varchar(20);
чтобы назначить значение, которое вы можете установить, когда вы объявляете
DECLARE stupidvar varchar(20) := '12345678';
или чтобы выбрать что-то в этой переменной, вы используете оператор INTO
, однако вам нужно сделать оператор wrap в BEGIN
и END
, также вам нужно убедиться, что возвращается только одно значение, и не забывайте точка с запятой.
поэтому полный оператор выйдет следующим образом:
DECLARE stupidvar varchar(20);
BEGIN
SELECT stupid into stupidvar FROM stupiddata CC
WHERE stupidid = 2;
END;
Ваша переменная доступна только в BEGIN
и END
, поэтому, если вы хотите использовать более одного, вам придется делать несколько оберток BEGIN END
DECLARE stupidvar varchar(20);
BEGIN
SELECT stupid into stupidvar FROM stupiddata CC
WHERE stupidid = 2;
DECLARE evenmorestupidvar varchar(20);
BEGIN
SELECT evenmorestupid into evenmorestupidvar FROM evenmorestupiddata CCC
WHERE evenmorestupidid = 42;
INSERT INTO newstupiddata (newstupidcolumn, newevenmorestupidstupidcolumn)
SELECT stupidvar, evenmorestupidvar
FROM dual
END;
END;
Надеюсь, это сэкономит вам время.
Если вы хотите объявить дату и затем использовать ее в SQL Developer.
DEFINE PROPp_START_DT = TO_DATE('01-SEP-1999')
SELECT *
FROM proposal
WHERE prop_start_dt = &PROPp_START_DT
Просто хочу добавить ответ Matas. Возможно, это очевидно, но я долго искал, что переменная доступна только внутри конструкции BEGIN-END, поэтому, если вам нужно использовать ее в некотором коде позже, вам нужно поместите этот код внутри блока BEGIN-END.
Обратите внимание, что эти блоки могут быть вложенными:
DECLARE x NUMBER;
BEGIN
SELECT PK INTO x FROM table1 WHERE col1 = 'test';
DECLARE y NUMBER;
BEGIN
SELECT PK INTO y FROM table2 WHERE col2 = x;
INSERT INTO table2 (col1, col2)
SELECT y,'text'
FROM dual
WHERE exists(SELECT * FROM table2);
COMMIT;
END;
END;
Вот ваш ответ:
DEFINE num: = 1; - Для значений по умолчанию требуется двоеточие. SELECT & num FROM dual;