What is the difference between variable declarations?
1 var instruction:
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= &id; DECLARE v_text VARCHAR2(10); -- declare BEGIN v_text := 'Hello'; --assign dbms_output.Put_line(v_text); --display END; 
1. var [iable] is a way to declare 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 the 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 to declare variables in SQL * Plus, in which you can specify the text that will be substituted for them in the places where they are used. Also using this command you can get a list of 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 query text:
define value = dual select * from &value / This query will produce the following result:
old 1: select * from & value new 1: select * from dual D - X
In the variable, you can specify almost any text:
define value ='23 from dual' select 1&value / old 1: select 1&value new 1: select 123 from dual 123 ---------- 123 If you do not assign a text value to a variable in advance, but simply use it in the query text, SQL * Plus will prompt you to enter its value:
select * from &another_value / After executing this text, SQL * Plus will display on the screen a request to specify values for the variable:
Enter value for another_value:
Specifying that (in our case, dual ) and pressing Enter, we will see the following result:
old 1: select * from & another_value new 1: select * from dual D - X
When you call just a command
define
A list of already existing variables is 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 is part of the declaration of the pl / sql code block define ... begin ... end followed by a variable declaration, which can be used inside the begin ... end block 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; / 
The difference of declared variables is in the place of their application and, accordingly, in the place where the values of these variables are stored.
Host substitution variables ( host or bind variables ) and substitute variable variables are declared and stored in the client program.
The declaration of variables in the client depends on the client program and may be slightly different or absent altogether. The def[ine] and var[iable] from SQL * Plus, but are also supported by many other software products for working with Oracle databases, for example: SQL Developer, TOAD.
var [iable] - wildcard variables indicating the data type.
It is possible to initialize these variables only in a PL / SQL block. They can be used to substitute both input and output field values in DML queries. Wildcard variables can also be in anonymous PL / SQL blocks for both transfer and read. Variable substitution is performed at the stage of substitution ( bind ) for input values, or determining the result (define output) for output values, just before execution ( execute ).
Important: Wildcard variables cannot be used for field names, tables, views, and other database objects, as they are necessary at the stage of preparation for execution ( parse ).
def [ine] - variable substitutions. They are declared and immediately initialized with a character value. All encountered variable names prefixed with & will be replaced by the symbolic value of these variables. The replacement will take place in the client before sending the DML / DDL / DCL expression or the PL / SQL block to be executed by the database server, so there are no restrictions on what part of them can be replaced.
PL / SQL variables are declared in the program block. They do not depend on the client program, since in the client it is only the text of the program that should be sent to the database server. Variables of this type are initialized when the program block is executed on the database server. These variables are stored in the UGA ( user global area ) as well on the database server.
declare keyword. In named blocks, variables can be declared immediately after the create|alter ... is|as header of the declaration / change of the named object. PL / SQL blocks can contain nested blocks. The visibility of variables is limited to the block in which they are declared. The lifetime of variables declared in packages ( packaged variables ) is a session, in all other cases it is the execution time of a named object or an anonymous block.An example that uses all kinds of variables described above for dynamic execution of scripts in SQL * Plus - there is some sort of script scripts, but it is unknown what name of them should be called, i.e. it must be determined dynamically and immediately called. All kinds of variables and column alias intentionally use the same sqlfile name:
define sqlfile='default'; variable sqlfile varchar2(100); declare sqlfile varchar2(100); begin <<defineScript>> declare -- в этом блоке динамически определяем имя скрипта 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, '&sqlfile') sqlfile from dual; host echo "prompt # &sqlfile running ..." >sql/&sqlfile\.sql @sql/&sqlfile Conclusion:
# my_sqlscript_01 running ... Source: https://ru.stackoverflow.com/questions/439594/
All Articles