Three ways to define Oracle variables (define,variable,declare) Learning notes

1. Define (that is, host variable)

The main role of the host variable is to act as an alternative variable, a medium in which the host environment can interact with Oracle . When you define a host variable by define, you must specify the variable name and the value of the variable, and if the variable name already exists in the host variable, it is automatically overwritten, and the value cannot be specified as a type and is stored as Char . The DEFINE variable is valid only in the current session environment.

(1). Syntax:

define variable_name = value

(2). Declaring and initializing define variables

//声明definedefine num=1;

You can also use the Define command to display the values and types (types are char) for a single (define Variable_name, cannot display multiple) or all (define) host variables.

(3). Displays the specified define variable value and type

//使用define def_name命令显示指定DEFINE变量的值和类型(DEFINE变量类型都为char)SQL>define num;DEFINE num              = "1" (CHAR)

(4). Show all define variable values and types

//Use define to display the values and types of all define variables (define variable types are char)Sql>  definedefine _connect_identifier = "topprod"(CHAR) DEFINE _sqlplus_release = "902000100"(CHAR) DEFINE _editor = "Notepad"(CHAR) DEFINE _o_version = "Oracle Database oneg Enterprise Edi tion Release  11.2.0.3.0-64bit producti with the Partitioning, OLAP, Data Mining  and Real  Application testing options"(CHAR) DEFINE _o_release = "1102000300"(CHAR)

Learn about the commands that are enabled and closed for host variables: set define on and set define off.

(5). Turn off define variable definition function

//关闭defineset define off;SQL> define num=1selectfromwhere rownum=#selectfromwhere rownum=&numORA-01008: 并非所有变量都已绑定

(6). Turn on the Define variable definition function

//打开defineseton;SQL> define num=1selectfromwhere rownum=#OCC01      OCC02---------- ---------------020040     松荣五金-SRWJ

(7). Referencing the Define Variable

This can be used in the command window of the Sqlplus and plsql developer, referencing the host variable in SQL or Plsql, using the & notation, which is equivalent to a simple substitution action , such as

//错误案例selectfrom‘&x‘where rownum=1;selectfrom‘occ_file‘where rownum=1ORA-00903: 表名无效SQL>

The error is because X is replaced with Occ_file, the statement changes to select * from ' Occ_file ', the ' occ_file ' table is nonexistent, and a single quote is more.

//正确案例selectfromwhere rownum=1;OCC01      OCC02---------- ---------------020040     松荣五金-SRWJSQL>

(8). Referencing temporary define variables

You can initialize the define variable without prejudging it, precede the string with the & symbol directly in SQL or PLSQL, prompting you to enter the value of the variable and replace it. In this case it is a temporary variable and the Define command cannot be found .

//不预先声明和初始化define变量直接使用&引用selectfromwhere rownum=1;//弹框提示输入变量y的值，输入occ_file,后打印出一下信息OCC01      OCC02---------- ---------------020040     松荣五金-SRWJ SQL>

The host variable is often used when writing scripts, some parts of the script changes frequently, other places fixed, then you can use & reference.

2, Variable (that is, the state variable)

Binding variables are mainly used in SQL to achieve SQL parsing once and execute multiple times to improve efficiency. Binding variables, like host variables, are also defined and temporary. (Temporary use of placeholders and using in dynamic SQL or dynamic Plsql), other cases, such as SQL, that automatically use bound variables, are not specifically discussed here. The defined binding variable is also valid in the current session. Binding variables The following features:

• Binding variables must be prefixed by direct reference in SQL and Plsql:. To refer to the binding variable A, it is: A;

• In the real running of the Plsql program, such as automatic operation, has its own set of mechanisms;

• Initializes and applies binding variables, initializes the defined binding variables, and can use procedures and functions, where the procedure can internally assign a value to the bound variable, or the parameter is a bound variable, and the parameter pattern is out. Using functions and procedures, you can also use the call
  function to assign a value.

Sqlplus can use greater than or equal to 3 characters to represent a command , here we use the Var,var command is to declare a binding variable, only given the name and type, defined by the time can not be assigned , the assignment may be in the Plsql or function assignment ( You must assign a value when the host variable is defined).

(1). Syntax:

type 

(2). Declaring binding variables

//使用var声明两number类型的变量num1、num2varvar num2 number;

(3). Display the specified binding variable

//var num1命令显示指定绑定变量名称、数据类型SQL>var num1variable num1datatype NUMBERSQL>

(4). Show all bound variables

//var命令显示所有绑定变量的变量名称、数据类型SQL>varvariable num1datatype NUMBERvariable num2datatype NUMBER

(5). Initialize binding variables with PL/SQL

//给绑定变量赋值     :num1:=1;     :num2:=2;     end;     /PL/SQL procedure successfully completednum1---------1num2---------2SQL>

(6). Initialize binding variables with Execute

//使用execute初始化，初始化多个用分号隔开 SQL> exec :num1:=1;:num2:=2PL/SQL procedure successfully completednum1---------1num2---------

(7). Displays the value of the specified binding variable

//使用print var_name命令输出指定的绑定变量值SQL> print num1;num1---------1SQL> print num2;num2---------2

(8). Show values for all bound variables

//print 命令输出所有绑定变量的值SQL> printnum1---------1num2---------

(9). Referencing bound variables

//使用:var_name引用绑定变量SQL> select :num1 from dual;     :NUM1----------         1num1---------

(10). Stored Procedure Initialization binding variable

//Declare binding variable MSql>varM number;//Create a stored procedure with output parameters Test_proSql>Create orReplace procedure test_pro(num  out number)asbeginnum :               =Ten;End; / Procedure created//use stored procedure to return a variable (reference binding variable remember to bring it on:)SQL> exec test_ Pro(num=>:m);PL/SQL procedure successfully completedm---------10//Output binding variable m value SQL> print mm---------

(11). Cursor initialization binding variable

The binding variable is the Refcursor type. One parameter uses refcursor, defined by the binding variable type as Refcursor, and then passes in the procedure, printing the binding variable (the effect is the same as the query statement).

//Declare a binding variable of the cursor type//Note: The following declaration cannot be used in command window of Plsql Dev, it needs to be sqlplus in order to be validSql>varCurinfo Refcursor//Create a stored procedure with an output parameter with a parameter type of Sys_refcursorSql>Create orReplace procedure cur_pro(cur_msg  out sys_refcursor)asbegin  Open cur_msg  for select occ01,occ02  from OCC _file where rownum<3;                    End; / Procedure created//exec executes stored procedure SQL> exec cur_pro( Cur_msg=>:cur_info);The PL/SQL process has completed successfully.//Output bound variable Cur_info valuesql> Print CUR_INFOOCC01 OCC02---------------------------020040Pine Wing Hardware-SRWJ---------------------------020041Luke-ldj.

As can be known above, print can directly refcursor the results of printing, do not need to iterate to find.

(12). Function initialization binding than there

//Create functionSql>Create orReplace function test_fun(a  in Varchar2,b  in varchar2) return  VARCHAR2asc varchar2(255);               beginc:=a| |     b return C;End; / Function created//Declaration binding variable fun_infoSQL> var fun_info varchar2 (255);//Use the Call function to assign the return value to the bound variableSql> Call Test_fun (' A ',' B ') into: Fun_info; Method calledfun_info---------ab//Display the value of the bound variable SQL> Print  fun_infofun_info---------ab 

Of course, you can also define the parameters of the function as out mode to initialize, we use the call command to invoke the function to pass the result to the bound variable,

Syntax :

function(参数列表) into ：绑定变量

parameters in PL/SQL and defined variables, including global variables and temporary variables, are internally converted to bound variables, so try to use static SQL as much as possible in PL/SQL instead of dynamic SQL, and if you use dynamic SQL, try to add bound variables as much as possible.

(13). The basic role of bound variables

The binding variable is mainly in the execution of SQL, after parsing the SQL will be a shared pool (SGA) check, see if the optimizer has analyzed the optimization of this SQL, the environment must be fully consistent (including the consistency of case, the session is consistent, etc.). Then can achieve an analysis, the purpose of multiple executions, this is soft parsing , otherwise, through parsing, optimization, row resource generation and other a series of SQL execution process, because SQL optimization requires a lot of resources, if hard parsing, SQL performance will be degraded.

If the query runs for several hours, it is not necessary to do the binding variable, because the consumption of the parsing is negligible, and the binding variable has a negative effect on the optimizer's judgment of the execution path.

See if the SQL in the SGA is soft parse is called multiple times, you can view the V sql or v Sqlarea view, view column sql_text,executions, such as:

selectfromwherelike‘%trademark%‘; SQL_TEXT                              selectfromwhere id=:tid        6

3, declare

View Oracle PL/SQL Statements Basic Learning notes (UP)-3, Declarations section

Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.

Three ways to define Oracle variables (define,variable,declare) Learning notes