Oracle =:: = and variable-bound Oracle wildcard and operator

This is a 7788-piece:
": =" is an assignment statement such as: l_name: = ' sky ';
"=" is the determination of equality. such as: if 1=1 then ...
":" is a variable binding such as: if:P _name = ' sky ' then ...

Variable binding refers to the use of variables rather than constants in the conditions of an SQL statement. For example, there are two SQL statements in the shared pool ,
SELECT * from Tab1 where col1=1;
SELECT * from Tab1 where col1=2;
For an Oracle database, this is two completely different SQL, which requires hard parse for all two statements. Because Oracle calculates the hash value of each character in memory based on the text of the SQL statement, Oracle does not have the same hash address in memory as the hash algorithm, although only one character is different from the above two SQL. So Oracle would consider this to be two completely different statements. And if the above SQL is rewritten to select * from Tab1 where col1=:var1, and then through the assignment of the variable var1 to query, then Oracle for the first time this statement will be hard parse, and then only soft Parse Assuming that a statement has been repeatedly executed hundreds of thousands of times, the benefits of using bind Var are enormous. An application if bind Var is not fully used, it will almost certainly be accompanied by a serious performance problem.

Binding variables are relative text variables, the so-called text variables refers to the SQL directly write query conditions, such SQL in different conditions need to be resolved repeatedly, binding variable refers to the use of variables instead of direct writing conditions, query bind value is passed at run time, and then binding execution. The advantage is to reduce the hard parsing, reduce the contention of CPU, save Shared_pool; The disadvantage is that it is difficult to use Histogram,sql optimization

Oracle wildcard characters and operators

The:  used for the Where comparison condition equals: =, <, <=, >, >=, <>  contains: In, not in exists, not exists  range: Between...and , not between....and  match test: like, not like  null test: Is NULL, is not null  Boolean link: And, or, not   www.2ct o.com   Wildcard characters:  in the WHERE clause, wildcards can be used with the like condition. : % (Percent semicolon) in Oracle: used to represent any number of characters, or there may not be any characters at all.   _ (Underscore): Indicates the exact unknown character.  ? (question mark): Used to represent the exact unknown character.   # (pound sign): used to denote the exact Arabic numerals, 0 to 9.   [a-d] (square brackets): Used to represent the range of characters, here is from a to D.   Single quotation mark ('): In Oracle, you should enclose text and characters and dates in only single quotation marks, and you cannot enclose numbers in quotes         (including single-double quotes).    double Quotes ("): In Oracle, single and double quotes have different meanings. Double quotation marks are used to enclose column aliases that contain specific characters or spaces. Double quotation marks are also used to put text in a date format.    Apostrophe ('): In Oracle, apostrophes can also be written as two single quotes adjacent to each other. In order to find all the vendor names with apostrophes in the middle of the supplier name, you can write code:  SELECT * from l_suppliers where supplier_name like '% '% '    & symbol: In Oracle,& symbols are commonly used to indicate a variable. For example, &fox is a variable, slightly different kind of a && fox. Whenever &fox appears in an Oracle script, you will be asked to provide a value for it. With &&fox, you only need to provide the variable value for & &fox The first time it appears. If you want to use the & symbol as a normal symbol, you should turn off this feature. To turn this feature off, you can shipLine the following command: Set define off, which is a sqlplus command, not an SQL command. Sqlplus sets the environment in which SQL runs in Oracle.    Double vertical bar (| | ): Oracle uses a double vertical bar to represent string join functions.    asterisk (*): SELECT * means that all columns are selected, and count (*) means that all rows are calculated, representing 0 or any number of characters when the wildcard character is represented.    forward slash (/): Used in Oracle to terminate SQL statements. More precisely, it means "Run the SQL code that is now in the buffer." The forward slash is also used as a separator.    does not equal: There are many ways to express:! =, ^=, <>, not xxx=yyy, not (XXX=YYY)

Oracle =:: = and variable bound Oracle wildcard and operator