variable tipos procedimientos procedimiento funciones example ejemplos ejecutar developer declarar bloques almacenado sql oracle variables declaration

tipos - ¿Cómo declarar la variable y usarla en el mismo script SQL?(Oracle SQL)



procedimientos y funciones oracle pl/sql (7)

Aquí está tu respuesta:

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

Estoy haciendo algunas pruebas escritas en SQL. Quiero escribir un código reutilizable y, por lo tanto, quiero declarar algunas variables al principio y volver a utilizarlas en el script, así:

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

¿Cómo puedo declarar una variable y volver a utilizarla en las declaraciones que siguen? (Estoy usando SQLDeveloper para acceder a Oracle DB.)

Lo intenté hasta ahora:

  • Use una sección DECLARACIÓN e inserte la siguiente instrucción SELECT en BEGIN y END; . Accede a la variable usando &stupidvar .
  • Use la palabra clave DEFINE y acceda a la variable.
  • Usando la palabra clave VARIABLE y accede a la variable.

Pero recibo todo tipo de errores durante mis intentos (variable no vinculada, error de sintaxis, SELECT INTO ...).

Al buscar en la red, encuentro muchas explicaciones diferentes sobre SQL, PL / SQL.

¡Gracias de antemano!


En PL / SQL v.10

la declaración de la palabra clave se utiliza para declarar la variable

DECLARE stupidvar varchar(20);

para asignar un valor, puede configurarlo cuando declare

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

o para seleccionar algo en esa variable, utiliza la instrucción INTO ; sin embargo, debe ajustar la instrucción en BEGIN y END , también debe asegurarse de que solo se devuelva un valor único, y no olvide los puntos y comas.

entonces la declaración completa saldría después:

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

Su variable solo se puede usar dentro de BEGIN y END por lo que si desea usar más de una, tendrá que hacer varias envolturas 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 esto te ahorre algo de tiempo


Hay varias maneras de declarar variables en scripts SQL * Plus.

El primero es usar VAR. El mecanismo para asignar valores a un VAR es con una llamada 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>

Un VAR es particularmente útil cuando queremos llamar a un procedimiento almacenado que tiene parámetros OUT o una función.

Alternativamente, podemos usar variables de sustitución. Estos son buenos para el modo interactivo:

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>

Cuando estamos escribiendo un script que llama a otros scripts, puede ser útil para DEFINIR las variables por adelantado:

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 está el bloque anónimo PL / SQL. Como puede ver, aún podemos asignar valores a las variables declaradas de forma interactiva:

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>


Intenta usar comillas dobles si se trata de una variable char:

DEFINE stupidvar = "''stupidvarcontent''";

o

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


La pregunta es acerca de usar una variable en un script significa para mí que se usará en SQL * Plus.

El problema es que te perdiste las comillas y Oracle no puede analizar el valor del 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

Esta muestra funciona bien debido a la conversión de tipo automática (o como se llame).

Si lo comprueba escribiendo DEFINE en SQL * Plus, se mostrará que la variable num es CHAR.

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

No es un problema en este caso, porque Oracle puede tratar la cadena de análisis al número si sería un número válido.

Cuando la cadena no puede analizar el número, Oracle no puede manejarlo.

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

Con una cita, así que no obligue a Oracle a analizar el número, estará bien:

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

Por lo tanto, para responder a la pregunta original, se debe hacer como esta muestra:

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 otra forma de almacenar variables en SQL * Plus utilizando el valor de la columna de consulta .

El COL [UMN] tiene la opción new_value para almacenar el valor de la consulta por nombre de 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 puede ver, el valor de X.log se estableció en la variable stupid_var , por lo que podemos encontrar que un archivo X.log en el directorio actual tiene algo de inicio de sesión.


Si desea declarar la fecha y luego usarla en SQL Developer.

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


Solo quiero agregar la respuesta de Matas . Tal vez es obvio, pero he buscado durante mucho tiempo para descubrir que la variable es accesible solo dentro de la construcción BEGIN-END , así que si necesitas usarlo en algún código más adelante, necesitas poner este código dentro de BEGIN -END bloque .

Tenga en cuenta que estos bloques se pueden anidar :

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;