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;
DECLARE
v_text VARCHAR2 (10); - declare
BEGIN
v_text: = 'Hello'; --assign
dbms_output.Put_line (v_text); --display
END;
http://ss64.com/ora/syntax-variables.html
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);
begin
select 'a'
into: value
from dual;
end;
/
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 D - X
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
123
----------
123
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 D - X
When just calling a command
define
The following list of already existing variables will be displayed:
DEFINE _DATE = "03-AUG-15" (CHAR) DEFINE _CONNECT_IDENTIFIER = "orcl" (CHAR) DEFINE _USER = "SYS" (CHAR) DEFINE _PRIVILEGE = "AS SYSDBA" (CHAR) DEFINE _SQLPLUS_RELEASE = "1102000200" (CHAR) DEFINE _EDITOR = "Notepad" (CHAR) DEFINE _O_VERSION = "Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options "(CHAR) DEFINE _O_RELEASE = "1102000200" (CHAR) DEFINE VALUE = "dual" (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:
declare
Val1 Number;
Val2 varchar2 (10);
Val3 Date;
Begin.
SELECT 1, 'A'
INTO VAL1, VAL2
from dual;
Val3: = sysdate;
dbms_output.put_line (VAL1);
dbms_output.put_line (val2);
dbms_output.put_line (val3);
end;
/
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. Announcementsdef [ine]
andvar [IABLE]
Go from SQL * Plus, but also supported by many other software products to work with database oracle , For example: SQL Developer, Toad.-
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 ). -
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.
- PL / SQL Variables are applied in the block after the keyword
Declare
. In the nominal blocks, variables can be announced immediately after theCREATE 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.
- PL / SQL Variables are applied in the block after the keyword
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);
Declare.
SQLFILE VARCHAR2 (100);
Begin.
& lt; & lt; defineScript & gt; & gt; Declare.
- in this block dynamically define the script name
LocalFile Constant Varchar2 (100): = 'My_sqlscript_01';
Begin.
SQLFILE: = LocalFile;
end;
: sqlfile: = sqlfile;
end;
/
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
Conclusion:
# my_sqlscript_01 running ...