Como declarar a variável e usá-la no mesmo script SQL? (Oracle SQL)

Eu estou fazendo alguns testes escritos em SQL. Eu quero escrever código reutilizável e, portanto, quero declarar algumas variables ​​no início e reutilizá-las no script, assim:

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

Como posso declarar uma variável e reutilizá-la nas instruções a seguir? (Estou usando o SQLDeveloper para acessar um database Oracle.)

Eu tentei até agora:

  • Use uma seção DECLARE e insira a seguinte instrução SELECT em BEGIN e END; . &stupidvar a variável usando &stupidvar .
  • Use a palavra-chave DEFINE e acesse a variável.
  • Usando a palavra-chave VARIABLE e acesse a variável.

Mas estou recebendo todos os tipos de erros durante minhas tentativas (variável Unbound, erro de syntax, Expected SELECT INTO …).

Enquanto pesquisava na rede, encontrei muitas explicações diferentes sobre SQL, PL / SQL.

Agradeço antecipadamente!

Existem várias formas de declarar variables ​​em scripts SQL * Plus.

O primeiro é usar o VAR. O mecanismo para atribuir valores a um VAR é com uma chamada 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> 

Um VAR é particularmente útil quando queremos chamar um procedimento armazenado que tenha parâmetros OUT ou uma function.

Alternativamente, podemos usar variables ​​de subsituição. Estes são bons para o modo interativo:

 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> 

Quando estamos escrevendo um script que chama outros scripts, pode ser útil definir DEFINIR as variables ​​antecipadamente:

 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> 

Finalmente, há o bloco PL / SQL anônimo. Como você pode ver, ainda podemos atribuir valores a variables ​​declaradas de forma interativa:

 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> 

Tente usar aspas duplas se for uma variável char:

 DEFINE stupidvar = "'stupidvarcontent'"; 

ou

 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 

Em PL / SQL v.10

palavra-chave declare é usada para declarar variável

 DECLARE stupidvar varchar(20); 

para atribuir um valor que você pode definir quando você declara

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

ou para selecionar algo na variável que você usar instrução INTO , no entanto, você precisa quebrar instrução em BEGIN e finalizar, também você precisa se certificar de que apenas um único valor é retornado e não se esqueça de ponto e vírgula.

então a declaração completa sairia seguinte:

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

Sua variável só é utilizável dentro de BEGIN e END isso, se você quiser usar mais de um, terá que BEGIN END vários 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; 

Espero que isso economize algum tempo

Se você quiser declarar a data e, em seguida, usá-lo no SQL Developer.

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

Aqui está sua resposta:

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

Só quero adicionar a resposta da Matas . Talvez seja óbvio, mas eu procurei por um longo tempo para descobrir que a variável é acessível apenas dentro da construção BEGIN-END , então se você precisar usá-la em algum código mais tarde, você precisa colocar este código dentro do BEGIN. -END bloco .

Note que estes blocos podem ser nesteds :

 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; 

A questão está prestes a usar uma variável em um script significa que ela será usada no SQL * Plus.

O problema é que você perdeu as cotações e Oracle não pode analisar o valor para o número.

 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 

Este exemplo funciona bem por causa da conversão automática de tipo (ou seja o que for chamado).

Se você verificar digitando DEFINE no SQL * Plus, ele mostrará que a variável num é CHAR.

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

Não é um problema neste caso, porque o Oracle pode lidar com a string de análise para number se for um número válido.

Quando a string não pode analisar o número, o Oracle não pode lidar com isso.

 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 

Com uma citação, por isso não force o Oracle a analisar o número, vai ficar bem:

 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 

Então, para responder a pergunta original, deve ser como esta amostra:

 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 

Existe uma outra maneira de armazenar variables ​​no SQL * Plus usando o Valor da Coluna de Consulta .

O COL [UMN] tem a opção new_value para armazenar o valor da consulta pelo nome do campo.

 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; 

Como você pode ver, o valor de X.log foi definido na variável stupid_var , então podemos encontrar um arquivo X.log no diretório atual que tenha algum log nele.