NVL (), InStr (), and multiple SQL transaction operations in Oracle

Oracle's NVL function

NVL () function

Returns a non-null value from two expressions.

Grammar

NVL (EExpression1, EExpression2)

Parameters

EExpression1, EExpression2

If the eExpression1 evaluates to a null value, NVL () returns EEXPRESSION2. If the EExpression1 evaluates to a value other than NULL, the EExpression1 is returned. EExpression1 and EExpression2 can be any data type. If the result of both EExpression1 and EExpression2 is null, then NVL () returns. Null..

return value type

character, date, datetime, numeric, currency, logical, or null values

Description

You can use NVL () to remove null values from a calculation or operation in cases where null values are not supported or if NULL values do not matter.

Select NVL (a.name, ' empty ') as name from student a Joinschool B on a.id=b.id

Note: Two parameter types to match

------------------------------------------------

The InStr (Strsource,strtarget) function is available in Oracle, which is much more efficient than using the '% keyword% ' mode.

The InStr function is also available in three different situations:
InStr (field, ' keyword ') >0 equivalent to field like '% keyword% '
InStr (field, ' keyword ') =1 equivalent to field like ' keyword% '
InStr (field, ' keyword ') =0 equivalent to field not like '% ' keyword% '
Example:
SELECT * FROM [user] Whereinstr (uname, ' three ') >0
Use the reference above like to

Special usage:
Select ID, namefrom user where InStr (' 101914, 104703 ', id) > 0;
It is equivalent to
Select ID, namefrom user where id = 101914 or id = 104703;

The above two methods can be used directly when the amount of data is low, but when the amount of data is particularly large, we should consider the problem of efficiency. Although the efficiency of instr than the LIKE keyword method more efficient, but this is only to a certain extent, far from meeting our needs.

Why are keyword queries so inefficient? This is due to the use of these keyword queries, the database system is not indexed to query, but by sequential scanning method to query. Obviously, this technical feature has caused the inefficiency of the LIKE keyword query. Especially in complex queries or large table queries, the user can obviously feel slower speed.

---------------------------------------------------------

Problem, execute a piece of code back to a string that is going to execute my multiple SQL statements. How to get it through Oracle. Think about it. Dynamic Sql,execute IMMEDIATE This is a syntax for parsing and executing standard SQL statements. Just format the statement we want to execute in this string, such as the string of multiple SQL to be executed:

Delete TableA where aid=1;update TableB where Bid=2;insert into TableC values (id,3,4);

SQL statements like this are not allowed to execute together, as long as the dot format is OK,

to do this: BEGIN EXECUTE IMMEDIATE ' delete tableA where aid=1 '; EXECUTE IMMEDIATE ' update tableB where bid=2 '; EXECUTE IMMEDIATE ' insert into TableC values (id,3,4) '; END;

Note that you must write a good format, such as the semicolon after end. This makes it possible to execute this string in Oracle.

Excerpt from: 70226396/

NVL (), InStr (), and multiple SQL transaction operations in Oracle