Collection of common small Oracle knowledge points

Use sqlldr to import data:

1. Write the control file:
Load data
Infile 'e: \ netbu.csv'
Insert
Into table temp_zhuren_need
Fields terminated ','
Trailing nullcols -- when the table field does not have the corresponding value, it can be blank (space, blank, null are empty)
(BELONG_CODE, RUN_NAME, PHONE_NO)

2. Command Line statement:
Sqlldr name/pass @ tnsname control = 'e: \ ctl.txt 'Log = 'e: \ log.txt'
NOTE: If it is interrupted, you can view the log and the number of rows in the database, and then use skip = the number of rows in the database to continue appending. But change insert to append.

Two Oracle CASE usage methods:

Example 1:
Select product_id, product_type_id,
Case product_type_id
When 1 then 'book'
When 2 then 'video'
Else 'magazine'
End ---- note that end cannot be less
From products
Example 2:
Select product_id, product_type_id,
Case
When product_type_id = 1 then 'book'
When product_type_id = 2 then 'video'
Else 'magazine'
End ---- note that end cannot be less
From products
Note: when the conditions here can be multiple, connected by and or

Let's look at a composite example:

Select t. phone_no1204,
Nvl (t. phone_no1201, t. phone_no1204) as "phone_no1201 ",
Nvl (t. phone_no1110, t. phone_no1204) as "phone_no1110 ",
Nvl (t. phone_no1107, t. phone_no1204) as "phone_no1107 ",
Case
When t. phone_no1204 = nvl (t. phone_no1201, t. phone_no1204)
And t. phone_no1204 = nvl (t. phone_no1110, t. phone_no1204)
And t. phone_no1204 = nvl (t. phone_no1107, t. phone_no1204)
Then '0'
Else '1'
End as phone_no1101
From mark_518_t9 t

 

 

Instr () function, special usage -- instr () function replaces like, IN

The returned value of instr () is the first position of the target character (string) in the parent character, so it is an integer.

For example:

The result of instr ("efabcdefg", "e") is of course 1.
The result of instr (2, "efabcdefg", "e") is 7.
The result of instr (5, "efabcdefg", "a") is 0.
The result of instr ("efabcdefg", "k") is also 0.

Instr is a built-in function that performs indexing and has good performance.

Not necessarily. Like '% iii %' does not go through the index. like 'ik % 'uses the index.

For example:
SELECT code, name, dept, occupation FROM staff WHERE instr (code, '001')> 0;
Equivalent to ==>
SELECT code, name, dept, occupation FROM staff WHERE code LIKE '% 100 ';

Replace in:
SELECT code, name, dept, occupation FROM staff WHERE code IN ('a10001 ', 'a10002 ');
Equivalent to ==>
SELECT code, name, dept, occupation FROM staff WHERE instr ('a10001, A10002 ', code)> 0

 

Composite Index

You can create a composite index in ORACLE and use the combination of multiple fields in the table as the key value of the index. When we perform a query, we can use "where col1 = ?", You can also use "where col1 =? And col2 = ?", Such restrictions all use indexes, but "where col2 = ?" This index is not used for queries. Therefore, this composite index is used only when the condition contains a pilot column.

How to Create a composite index:

Create index index_name on table_name (column_name1, column_name2)

 

Use of minus and intersect
Minus is A part that exists in A and does not exist in B.
Intersect is AB Communication
Eg:
Select job from accounts
Intersect/minus
Select job from sales;

ORACLE set command
SQL> set colsep ''; //-domain output Separator
SQL> set echo off; // displays each SQL command in the start script. The default value is on.
SQL> set echo on // set whether to display the statement when running the command
SQL> set feedback on; // The "XX rows selected" is displayed"
SQL> set feedback off; // display the number of records processed by this SQL command. The default value is on.
SQL> set heading off; // output domain title, on by default
SQL> set pagesize 0; // number of output lines per page. The default value is 24. To avoid paging, you can set the value to 0.
SQL> set linesize 80; // output the number of characters in a row. The default value is 80.
SQL> set numwidth 12; // The length of the output number field. The default value is 10.
SQL> set termout off; // displays the execution results of commands in the script. The default value is on.
SQL> set trimout on; // Remove trailing spaces in each row of the standard output. The default value is off.
SQL> set trimspool on; // remove the trailing space of each row from the redirection (spool) output. The default value is off.
SQL> set serveroutput on; // set to allow display of output similar to dbms_output
SQL> set timing on; // set to display "time in use: XXXX"
SQL> set autotrace on-; // set to allow analysis of executed SQL statements
Set verify off // you can close or open the prompt to confirm the display of information old 1 and new 1.

View/restore the recycle bin table

Select * from recyclebin t where t. original_name like 'mb _ G % ';
Or show recyclebin
Flashback table MB_G_2009 to before drop;

To filter whether a column in the oralce table is a Chinese character or a character:
SQL> select * from aa;

A B
--------------------
1 aaa
1 11111
1 book

Method 1:
Principle: The asciistr () function converts non-ASCII code into binary code and adds \ before it. Therefore, if asciistr (acolumn) is a Chinese character, \
SQL> select aa. * from aa where asciistr (B) like '\ % ';

A B
--------------------
1 book
Method 2:
Principle: It is determined by intercepting the length of the first character.
SQL> select aa. * from aa where lengthb (substr (B, 1, 1) = 2;

A B
--------------------
1 book

Extended GROUP
The extended group by uses the analysis functions rollup () and cube ().

Rollup (): enables grouping results to contain subtotal and total information, and can input multiple columns of fields
Cube (): subtotal is performed for each column passed as a parameter. The advantage is displayed when multiple columns are passed in.
Example:
Select id, sum (mount) from tt group by rollup (id );
Id sum (MOUNT)
--------------------
1 60
2 150
3 240
4 210
5 440
1100
Select id, sum (mount) from tt group by cube (id );
Id sum (MOUNT)
--------------------
1100
1 60
2 150
3 240
4 210
5 440

Alternatively, use nulls to display the beginning and end positions of the specified null value:
Select id, sum (mount) from tt group by cube (id) order by id nulls last;