oracle--Basic Knowledge

One, the difference between Oracle and SQL Server:

Category	Oracle	Sql server
Connection string	||	+
Variable	Variable name	@ Variable Name
Initial assignment	: =	=
SQL statement Assignment	Into	=
Binding variables	=: variable Name	[email protected] Variable name

1. DECLARE variables:

Declare variable name variable type; Unlike SQL Server, variables do not require @

Example declare a VARCHAR2 (20);

=: should be equivalent to   a  =   : b   indicates that B is a bound variable and needs to be bound at execution time
: = equivalent to assignment in a general programming language  A: = 1  assigns the number 1 to the variable a

=:   
: =   is the constant value in Procdure

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--Basic Knowledge