Hoe variabele declareren en gebruiken in hetzelfde Oracle SQL-script?

Ik wil herbruikbare code schrijven en moet aan het begin enkele variabelen declareren en ze opnieuw gebruiken in het script, zoals:

DEFINE stupidvar = 'stupidvarcontent';
SELECT stupiddata
FROM stupidtable
WHERE stupidcolumn = &stupidvar;

Hoe kan ik een variabele declareren en deze opnieuw gebruiken in instructies die volgen, zoals bij het gebruik van SQLDeveloper.

Pogingen

• Gebruik een DECLARE-sectie en voeg de volgende SELECT-instructie in BEGINen END;in. Opent de variabele met &stupidvar.
• Gebruik het trefwoord DEFINEen open de variabele.
• Gebruik het trefwoord VARIABLEen open de variabele.

Maar ik krijg allerlei fouten tijdens mijn pogingen (ongebonden variabele, syntaxisfout, verwachte SELECT INTO…).

Antwoord 1, autoriteit 100%

Er zijn verschillende manieren om variabelen in SQL*Plus-scripts te declareren.

De eerste is om VAR te gebruiken, om een bindvariabele te declareren. Het mechanisme voor het toewijzen van waarden aan een VAR is met een EXEC-aanroep:

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>

Een VAR is vooral handig als we een opgeslagen procedure willen aanroepen die OUT-parameters of een functie heeft.

Als alternatief kunnen we substitutievariabelen gebruiken. Deze zijn goed voor de interactieve modus:

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>

Als we een script schrijven dat andere scripts aanroept, kan het handig zijn om de variabelen vooraf te DEFINIEREN. Dit fragment wordt uitgevoerd zonder mij te vragen een waarde in te voeren:

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>

Eindelijk is er het anonieme PL/SQL-blok. Zoals u ziet, kunnen we nog steeds interactief waarden toewijzen aan gedeclareerde variabelen:

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>

Antwoord 2, autoriteit 21%

Probeer dubbele aanhalingstekens te gebruiken als het een char-variabele is:

DEFINE stupidvar = "'stupidvarcontent'";

of

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

Antwoord 3, autoriteit 14%

In PL/SQL v.10

keyword declare wordt gebruikt om variabele te declareren

DECLARE stupidvar varchar(20);

om een waarde toe te wijzen, kunt u deze instellen wanneer u declareert

DECLARE stupidvar varchar(20) := '12345678';

of om iets in die variabele te selecteren, gebruikt u de INTO-instructie, maar u moet de instructie in BEGINen ENDplaatsen, ook moet u om ervoor te zorgen dat er slechts één waarde wordt geretourneerd, en vergeet puntkomma’s niet.

dus de volledige verklaring zou als volgt uitkomen:

DECLARE stupidvar varchar(20);
BEGIN
    SELECT stupid into stupidvar FROM stupiddata CC 
    WHERE stupidid = 2;
END;

Uw variabele is alleen bruikbaar binnen BEGINen END, dus als u er meer dan één wilt gebruiken, moet u meerdere BEGIN ENDdoen omhulsels

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;

Hopelijk bespaart dit je wat tijd

Antwoord 4, autoriteit 5%

Als u de datum wilt declareren en deze vervolgens wilt gebruiken in SQL Developer.

DEFINE PROPp_START_DT = TO_DATE('01-SEP-1999')
SELECT * 
FROM proposal 
WHERE prop_start_dt = &PROPp_START_DT

Antwoord 5, autoriteit 3%

De vraag staat op het punt een variabele in een script te gebruiken, wat voor mij betekent dat het in SQL*Plus zal worden gebruikt.

Het probleem is dat je de aanhalingstekens hebt gemist en Oracle kan de waarde niet ontleden naar een getal.

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

Dit voorbeeld werkt prima vanwege automatische typeconversie (of hoe het ook heet).

Als u dit controleert door DEFINE in SQL*Plus te typen, ziet u dat de variabele num CHAR is.

SQL>define
DEFINE NUM             = "2018" (CHAR)

Het is in dit geval geen probleem, omdat Oracle kan omgaan met het ontleden van string naar nummer als het een geldig nummer zou zijn.

Als de string niet naar nummer kan worden geparseerd, dan kan Oracle er niet mee omgaan.

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

Met een aanhalingsteken, dus dwing Oracle niet om naar een getal te ontleden, komt goed:

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

Dus, om de oorspronkelijke vraag te beantwoorden, zou het moeten zijn zoals dit voorbeeld:

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

Er is een andere manier om variabelen in SQL*Plus op te slaan door Query Column Valuete gebruiken.

De COL[UMN] heeft de optie new_valueom de waarde van de zoekopdracht op te slaan op veldnaam.

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;

Zoals je kunt zien, is de X.log-waarde ingesteld in de variabele stupid_var, dus we kunnen een X.log-bestand in de huidige map vinden met een logbestand erin.

Antwoord 6, autoriteit 3%

Ik wil gewoon het antwoord van Matastoevoegen.
Misschien ligt het voor de hand, maar ik heb lang gezocht om erachter te komen dat de variabele alleen toegankelijk is binnen de BEGIN-ENDconstructie, dus als je het later in een code wilt gebruiken, moet je om deze code in het BEGIN-END-blok te plaatsen.

Houd er rekening mee dat deze blokken genest kunnen worden:

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;

Antwoord 7, autoriteit 2%

In Toad gebruik ik deze werken:

declare 
    num number;
begin 
    ---- use 'select into' works 
    --select 123 into num from dual;
    ---- also can use :=
    num := 123;
    dbms_output.Put_line(num);
end;

Vervolgens wordt de waarde afgedrukt naar het venster DBMS Output.

Verwijzing naar hieren hier2.

Antwoord 8

Hier is uw antwoord:

DEFINE num := 1;       -- The semi-colon is needed for default values.
SELECT &num FROM dual;

Antwoord 9

Een mogelijke benadering, als u een parameter maar één keer hoeft op te geven en op verschillende plaatsen te repliceren, is om zoiets als dit te doen:

SELECT
  str_size  /* my variable usage */
  , LPAD(TRUNC(DBMS_RANDOM.VALUE * POWER(10, str_size)), str_size, '0') rand
FROM
  dual  /* or any other table, or mixed of joined tables */
  CROSS JOIN (SELECT 8 str_size FROM dual);  /* my variable declaration */

Deze code genereert een reeks van 8 willekeurige cijfers.

Merk op dat ik een soort alias maak met de naam str_sizedie de constante 8bevat. Het is gekruist om meer dan één keer in de zoekopdracht te worden gebruikt.

Antwoord 10

Soms moet u een macrovariabele gebruiken zonder de gebruiker te vragen een waarde in te voeren. Meestal moet dit worden gedaan met optionele scriptparameters. De volgende code is volledig functioneel

column 1 noprint new_value 1
select '' "1" from dual where 2!=2;
select nvl('&&1', 'VAH') "1" from dual;
column 1 clear
define 1

Vergelijkbare code is op de een of andere manier gevonden in de directory rdbms/sql.