Jak zadeklarować zmienną i użyć jej w tym samym skrypcie SQL? (Oracle SQL)
Chcę napisać kod wielokrotnego użytku i muszę zadeklarować pewne zmienne na początku i ponownie je wykorzystać w skrypcie, takie jak:
DEFINE stupidvar = 'stupidvarcontent';
SELECT stupiddata
FROM stupidtable
WHERE stupidcolumn = &stupidvar;
Jak mogę zadeklarować zmienną i użyć jej ponownie w instrukcjach, które następują po niej, np. przy użyciu SQLDeveloper.
Próby
- użyj sekcji DECLARE i wstaw następującą instrukcję SELECT w
BEGIN
iEND;
. Uzyskuje dostęp do zmiennej za pomocą&stupidvar
. - Użyj słowa kluczowego
DEFINE
i uzyskaj dostęp do zmienna. - używając słowa kluczowego
VARIABLE
i uzyskaj dostęp do zmiennej.
Ale podczas moich prób dostaję różnego rodzaju błędy (zmienna niezwiązana, błąd składni, oczekiwany SELECT INTO
...).
7 answers
Istnieje kilka sposobów deklarowania zmiennych w skryptach SQL*Plus.
Pierwszym jest użycie VAR. Mechanizm przypisywania wartości do VAR jest wywołaniem 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 jest szczególnie przydatny, gdy chcemy wywołać procedurę składowaną, która ma parametry lub funkcję.
Alternativley możemy użyć zmiennych substytucyjnych. Są one dobre dla trybu interaktywnego:
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>
Kiedy piszemy skrypt, który wywołuje Inne skrypty, może się przydać Definiowanie zmiennych z góry:
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>
Wreszcie jest anonimowy blok PL / SQL. Jak widzisz, nadal możemy przypisać wartości do zadeklarowanych zmiennych interaktywnie:
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>
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2012-11-09 04:48:04
Spróbuj użyć podwójnych cudzysłowów, jeśli jest zmienną char:
DEFINE stupidvar = "'stupidvarcontent'";
Lub
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
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2010-08-25 09:19:45
In PL / SQL V. 10
Słowo kluczowe declare służy do deklarowania zmiennej
DECLARE stupidvar varchar(20);
Aby przypisać wartość, możesz ją ustawić, deklarując
DECLARE stupidvar varchar(20) := '12345678';
Lub aby wybrać coś do tej zmiennej używasz instrukcji INTO
, jednak musisz zawinąć instrukcję w BEGIN
i END
, musisz również upewnić się, że zwracana jest tylko pojedyncza wartość i nie zapomnij o średnikach.
Więc pełna wypowiedź wyjdzie następująco:
DECLARE stupidvar varchar(20);
BEGIN
SELECT stupid into stupidvar FROM stupiddata CC
WHERE stupidid = 2;
END;
Twoja zmienna jest użyteczna tylko w obrębie BEGIN
i END
więc jeśli chcesz użyć więcej niż jednego, musisz wykonać wiele BEGIN END
owijek
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;
Mam nadzieję, że to zaoszczędzi ci trochę czasu
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2017-08-12 01:43:43
Jeśli chcesz zadeklarować datę, a następnie użyć jej w SQL Developer.
DEFINE PROPp_START_DT = TO_DATE('01-SEP-1999')
SELECT *
FROM proposal
WHERE prop_start_dt = &PROPp_START_DT
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2015-07-29 17:41:17
Chcę tylko dodać Matas ' odpowiedź. Może to oczywiste, ale długo szukałem, aby dowiedzieć się, że zmienna jest dostępna tylko wewnątrz konstrukcji BEGIN-END , więc jeśli chcesz użyć jej później w jakimś kodzie, musisz Umieścić ten kod wewnątrz bloku BEGIN-END .
Zauważ, że te bloki mogą być zagnieżdżone :
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;
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2017-07-25 10:00:28
Pytanie o użycie zmiennej w skrypcie oznacza dla mnie, że będzie ona używana w SQL * Plus.
Problem polega na tym, że przeoczyłeś cudzysłowy i Oracle nie może przetworzyć wartości na liczbę.
SQL> DEFINE num = 2018
SQL> SELECT &num AS your_num FROM dual;
old 1: SELECT &num AS your_num FROM dual
new 1: SELECT 2018 AS your_num FROM dual
YOUR_NUM
----------
2018
Elapsed: 00:00:00.01
Ta próbka działa dobrze ze względu na automatyczną konwersję typu (lub jak to się nazywa).
Jeśli zaznaczysz wpisując DEFINE w SQL * Plus, wyświetli się, że num zmienna jest CHAR.
SQL>define
DEFINE NUM = "2018" (CHAR)
To nie jest problem w tym przypadku, ponieważ Oracle może poradzić sobie z parsowaniem łańcucha do numer, jeśli byłby to prawidłowy numer.
Gdy łańcuch nie może parsować do number, to Oracle nie może sobie z tym poradzić.
SQL> DEFINE num = 'Doh'
SQL> SELECT &num AS your_num FROM dual;
old 1: SELECT &num AS your_num FROM dual
new 1: SELECT Doh AS your_num FROM dual
SELECT Doh AS your_num FROM dual
*
ERROR at line 1:
ORA-00904: "DOH": invalid identifier
Z cytatem, więc nie zmuszaj Oracle do parsowania do liczby, będzie dobrze:
17:31:00 SQL> SELECT '&num' AS your_num FROM dual;
old 1: SELECT '&num' AS your_num FROM dual
new 1: SELECT 'Doh' AS your_num FROM dual
YOU
---
Doh
Więc, aby odpowiedzieć na oryginalne pytanie, należy zrobić tak jak ta próbka:
SQL> DEFINE stupidvar = 'X'
SQL>
SQL> SELECT 'print stupidvar:' || '&stupidvar'
2 FROM dual
3 WHERE dummy = '&stupidvar';
old 1: SELECT 'print stupidvar:' || '&stupidvar'
new 1: SELECT 'print stupidvar:' || 'X'
old 3: WHERE dummy = '&stupidvar'
new 3: WHERE dummy = 'X'
'PRINTSTUPIDVAR:'
-----------------
print stupidvar:X
Elapsed: 00:00:00.00
Istnieje inny sposób przechowywania zmiennej w SQL * Plus za pomocą wartość kolumny zapytania.
COL [UMN] posiada opcję new_value do przechowywania wartości z zapytania przez nazwę pola.
SQL> COLUMN stupid_column_name new_value stupid_var noprint
SQL> SELECT dummy || '.log' AS stupid_column_name
2 FROM dual;
Elapsed: 00:00:00.00
SQL> SPOOL &stupid_var.
SQL> SELECT '&stupid_var' FROM DUAL;
old 1: SELECT '&stupid_var' FROM DUAL
new 1: SELECT 'X.log' FROM DUAL
X.LOG
-----
X.log
Elapsed: 00:00:00.00
SQL>SPOOL OFF;
Jak widać, wartość X. log została ustawiona w zmiennej stupid_var , więc możemy znaleźć plik X. log w bieżącym katalogu ma jakiś log.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2018-02-07 18:16:50
Oto Twoja odpowiedź:
DEFINE num := 1; -- The semi-colon is needed for default values.
SELECT &num FROM dual;
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2018-04-11 08:39:56