Oracle Common Syntax

1,case usage

select case when T.flag = 0 ' from xtxmxx T       

2,decode usage

-- if FLAG equals 1, it is converted to USERID, if 0, then to ORGID, the other 2SELECT DECODE (T.flag,1,t.userid,0,t.orgid,from Xtxmxx T;    

3, create sequence

-- Create sequence createdsequence199999999999999102201;      

4, three kinds of connections

--Right connect opposite direction connectionSELECT*FromSx02_program T,sx03_gl_accass LWHERE T.programno (+)=l.citem_id;--Zolin ConnectionSELECT* from sx02_program t,sx03_gl_accass LWHERE t.programno=l.citem_id (+);  -- Special connection Select t.programno, (select P.bondno from sx05_contract P WHERE P.programno= T.programno) From  sx02_program t,sx03_gl_accass LWHERE t.programno=l.citem_id;      

5, update multiple columns

-- Multi-column update SET (t.zjqhzjrq,t.zjqhzjje,t.ljqhzjje)=(--- only a single value can be queried here, so to correlate the from  qjgl TT  WHERE T.xtxmbh=tt.xtxmbh);          

6. Quickly create a table

-- quickly create a table statement 1=0;   

7, delete table

TABLE table_name;

8, delete user

Cascade

9.row_number Grouping sorting

Select Cc.serialno,       Cc.objectno,       cc.corpuspaysum,       --PARTITION by: Group   ORDER BY: Sort  can be positive sequence reverse as from       sx01_contract_paysource cc;    

10. Converting a number to a string remains 0. format

-- Take two decimal places, and convert the number to a string remains 0. format   Select To_char (round (0.326,2),'% fromdual;  

-- do not retain 0. format Select To_char (round (0.326,'% fromdual;