Home sql What is the difference between variable declarations VARIABLE, DEFINE and DECLARE?

What is the difference between variable declarations VARIABLE, DEFINE and DECLARE?




What is the difference between variable declarations?

1 var statement:

var id number;
exec: id: = 1;
SELECT * FROM table_a WHERE id =: id;

2 and 3 DEFINE and DECLARE

DEFINE id = 1;
SELECT * FROM table_a WHERE id = & amp; id;
  v_text VARCHAR2 (10); - declare
  v_text: = 'Hello'; --assign
  dbms_output.Put_line (v_text); --display


Answer 1, authority 100%

1. var [iable] is a way of declaring variables in SQL * Plus, which must be of any type specified in the help. They can be used in sql and in pl / sql both for substitution of any values ​​and for storing values, for example:

variable value varchar2 (10);
 select 'a'
 into: value
 from dual;
select: value from dual

2. def [ine ] is a way of declaring variables in SQL * Plus, in which you can specify text that will be substituted for them where they are used. Also using this command you can get a list all existing variables that can be used for substitution.

If you declare a variable in advance and assign a value to it, then it will simply be substituted in the request text:

define value = dual
select * from & amp; value

This query will return the following result:

old 1: select * from & amp; value
new 1: select * from dual

Almost any text can be specified in a variable:

define value = '23 from dual '
select 1 & amp; value
old 1: select 1 & amp; value
new 1: select 123 from dual

If a variable is not previously assigned any text value, but simply used in the query text, then SQL * Plus will prompt you to enter its value:

select * from & amp; another_value

After executing this text, SQL * Plus will prompt you to specify values ​​for the variable:

Enter value for another_value:

By specifying which (in our case, dual ) and pressing Enter , we will see the following result:

old 1: select * from & amp; another_value
new 1: select * from dual

When just calling a command


The following list of already existing variables will be displayed:

DEFINE _O_VERSION = "Oracle Database 11g Enterprise Edition Release - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options "(CHAR)
DEFINE _O_RELEASE = "1102000200" (CHAR)

3. Declare – This is part of the PL / SQL class ad code Define ... Begin ... End After which the Begin comes with Variables that can be used inside the BEGIN ... END for example:

 Val1 Number;
 Val2 varchar2 (10);
 Val3 Date;
 SELECT 1, 'A'
 from dual;
 Val3: = sysdate;
 dbms_output.put_line (VAL1);
 dbms_output.put_line (val2);
 dbms_output.put_line (val3);

Answer 2, Authority 109%

The difference of the declared variables is the place of their use and, accordingly, in the place of storage of the values ​​of these variables.

  • Environmental variable variables (host or bind variables ) and substitution variables (Substitute Variable ) are declared and stored in the client program.
    The privilessment of variables in the client depends on the clint program and may differ somewhat or generally. Announcements def [ine] and var [IABLE] Go from SQL * Plus, but also supported by many other software products to work with database oracle , For example: SQL Developer, Toad.

    1. VAR [IABLE] – wildcard variables with data type.
      Initialize these variables are possible only in PL / SQL block. Can be used to substitutions both input and output fields of fields in DML queries. The wildcard variables can also be in anonymous PL / SQL blocks as for transmission and read. Variable substitution is carried out at the substitution phase (bind ) for input values, or define output (define output) for output values, immediately before execution (Execute ).
      IMPORTANT: wildcard variables cannot be used for field names, tables, views and other database objects, as they are necessary at the preparation rate (Parse ).

    2. DEF [INE] – variables of substitution. They are declared and immediately initialized by the symbol value. All encountered variables with the prefix & amp; will be replaced by the symbol value of these variables. Replacement will occur in the client of the Mesho before sending a DML / DDL / DCL expression or PL / SQL block to perform the database server, so any restrictions, which part of them can be replaced, does not exist.

  • Variables PL / SQL are applied in the software block. They do not depend on the client program, as in the client it is only the text of the program that must be sent to the database server. The variables of this type are initialized when the software block is executed on the database server. These variables are stored in UGA (User Global Area ) also on the database server.

    1. PL / SQL Variables are applied in the block after the keyword Declare . In the nominal blocks, variables can be announced immediately after the CREATE header ... IS | AS ads / change of the name object. PL / SQL blocks may contain nested blocks. The variable visibility zone is limited to the unit in which they are declared. The lifetime of variables announced in packets (packaged variables ) is a session, in all other cases – the execution time of the name object or an anonymous block.

An example of the executing all kinds of the above variables for dynamic execution of scripts in SQL * Plus – there is some kind of clollicity of scripts, but the origin is unknown which one to call, i.e. It must be determined dynamically and immediately call. All types of variables and alias columns are delighted using the same name SQLFile :

define sqlfile = 'default';
Variable SQLFile Varchar2 (100);
  & lt; & lt; defineScript & gt; & gt; Declare.
     - in this block dynamically define the script name
     LocalFile Constant Varchar2 (100): = 'My_sqlscript_01';
    SQLFILE: = LocalFile;
  : sqlfile: = sqlfile;
Column SQLFile New_Value SQLFile Noprint Format A100;
SELECT NVL (: SQLFILE, '& AMP; SQLFile') SQLFile From Dual;
Host Echo "Prompt # & amp; sqlfile running ..." & gt; sql / & amp; sqlfile \ .sql
@ SQL / & amp; sqlfile


# my_sqlscript_01 running ...

Programmers, Start Your Engines!

Why spend time searching for the correct question and then entering your answer when you can find it in a second? That's what CompuTicket is all about! Here you'll find thousands of questions and answers from hundreds of computer languages.

Recent questions