[Lone solitary Nine swords] Oracle Knowledge Point Grooming (iv) DML and DDL for SQL statements

Link navigation in this series:

[Lone solitary Nine swords] Oracle Knowledge Point Grooming (a) table space, user

[Lone solitary Nine swords] Oracle Knowledge Point Grooming (ii) connection of databases

[Lone solitary Nine swords] Oracle Knowledge Point Grooming (iii) Import, export

[Lone solitary Nine swords] Oracle Knowledge Point Grooming (iv) DML and DDL for SQL statements

[Lone solitary Nine swords] Oracle Knowledge Point Grooming (v) table, View of common objects in database

[Lone solitary Nine swords] Oracle Knowledge Point Grooming (vi) procedure, function and Sequence of common objects in database

[Lone solitary Nine swords] Oracle Knowledge Point Grooming (vii) Cursor of common objects in database

[Lone solitary Nine swords] Oracle Knowledge Point Grooming (eight) common exception

[Lone solitary Nine swords] Oracle Knowledge Point Grooming (ix) database common objects of the package

[Lone solitary Nine swords] Oracle Knowledge Point Grooming (10)%type and%rowtype and common functions

4. SQL statements

Oracle divides the SQL statements into several categories:

A) Data manipulation language statements [data manipulation LANGUAGE,DML], such as SELECT, INSERT, UPDATE, delete, merge, lock, FETCH, etc.

b) Data definition language statements [data definition LANGUAGE,DDL], such as CREATE, alter, DROP, rename,truncate, GRANT, REVOKE, audit, Noaudit, Comment, etc.

c) Transaction control statements [transaction control statement], such as commit, rollback, savepoint, set transaction, etc.

D) Session Control statements [session controls statement]
D.1 perform specific actions to modify the current session, such as enabling or disabling SQL Trace facility [SQL Trace] (ALTER session);
D.2 enable or disable roles for the current session [role] (that is, a set of permissions) (set role)

e) System control statements [System control Statement],alter systems are unique

f) Embedding SQL statements [Embedded SQL statement]
F.1 CURSOR definition (DECLARE CURSOR), opening (open), closing (close),
F.2 Select an Oracle database and connect declare database,connect
F.3 assigning variable names Declare statement
F.4 initialization Descriptor [descriptor] (DESCRIBE)
F.5 setting how to handle errors and warnings (whenever)
F.6 parsing and executing SQL statements (Prepare,execute,execute IMMEDIATE)
F.7 retrieving data from the database (FETCH)

What I do most is the parts of A,b,c and F.

4.1, DML4.1.1, increase the syntax of the check to use standard SQL statements can:

 1  select  *  from   table1;  2  insert  into  table1 (f1,f2,f3)  (V1,V2,V3);  3  set  t.f1=  v1 where  T.f2=  V2;  4  where  t.f2=  v2; 

rownum, a unique keyword in Oracle, when select a table, Oracle will mark the query results, rownum is number type, each time starting from 1, if the query out 10 rows of data, Then RowNum will be marked from 1 to 10, in the order of the query results.

RowNum is not jumping, that is, where rownum=1 can be used, but where rownum=2 can not be used; You can use RowNum <5, but you cannot use rownum>5, where rownum=0 can get the table structure.

4.1.2, paging query:

Select TT. *  from (select T.*fromwhere rownum<=where tt.rn  >=1-- This is the best example of using RowNum

4.1.3, joint query

• Where Mode association:

1 SelectT1.*, T2.*  fromTable1 T1,table2 T2whereT1.f1=T2.f2--Join with inner2 SelectT1.*, T2.*  fromTable1 T1,table2 T2whereT1.F1 (+)=T2.f2--same as left join3 SelectT1.*, T2.*  fromTable1 T1,table2 T2whereT1.f1=T2.F2 (+)--with Right Join

• Join Mode Association:

1 Select t1. *  from table1 T1 2 Join table2 T2 3  on T1.f1=t2.f24where t1.fx>  and T2.fy>

Left joins, right joins, outer join methods are consistent with standard SQL, slightly.

4.1.4, Merge merging

This command uses a single statement to complete updating and inserting data from one or more data sources to the table.

1  　　 / * Related to two table-related examples * /2Merge intoToTable A--tables that need to be updated3Using fromtable b--Association tables (data source tables)4  　　  on(a.id=b.ID)--Association conditions5  　　  whenMetched Then    --match related conditions, do update processing6  　　　　 Update SetA.f1=B.F1, A.F2=B.f2+17  　　  when  notMetched Then    --do not match association conditions, do insert processing8  　　　　 Insert Values(b.f1,b.f2,b.f3 ...);

1 /* Related to Multiple table association examples, there are 3 tables, Table1 is the target table, table2 and table3 need to be associated, as a Table1 data source; and just do the update */2Merge intotable1 a3using (SelectT2.id,t2.f1,t2.f2,t3.f1 from TableT2Jointable2 T3 onT2.id=t3.id) b4  on(a.id=b.id)5  　　  whenMatched Then6  　　　　 Update SetA.f1=B.f1,a.f2=B.f2+1;

Note:

1. When using merge, the update or Insert field cannot be the field used for matching in on
2. You can use the Where condition for both update and INSERT statements, where it can be used for a table, or for B table filtering

4.2. DDL

Create is used for creating table, view, procedure, function, sequence, and so on.
Alter is used to modify
Drop is used to delete table, view, procedure, function, sequence, and so on.
Grant, revoke to assign permissions to users and remove permissions
Audit, Noaudit for auditing functions
Comment for adding note information
Truncate is used to truncate tables and cannot be rolled back
Rename for renaming

In the 5th section, when we introduce table, view, procedure, function, sequence, we use DDL statements

4.3, other types of SQL statements (￣▽￣) "

[Lone solitary Nine swords] Oracle Knowledge Point Grooming (iv) DML and DDL for SQL statements