Common SQL statements of oracle

1. Merge a field (multiple documents) of a table into a string output (comma separated)

 

+ ",(select LISTAGG(mo.model_code,',') within group(order by mo.model_code) from srm_pos_resource_model mo where mo.supplier_id=t.supplier_id) modelCodeList\n"
2. Query the latest document according to a field in the table

 

-according to supplierId Query the latest document
select * from (
select t.supplier_id,t.introduction_audit_id,t.creation_date,row_number()
over(partition by t.supplier_id order by t.creation_date desc) AS code_id
from srm_pos_introduction_audit t 
)
where code_id=1; --Control output only the latest record
3. Data of the first 30 days
select * from surface where date=to_date(to_char(sysdate-30,'yyyy/mm/dd'),'yyyy/mm/dd');
4. Delete the specified table and fuzzy query

 

declare
  v_sql varchar2(200);
  v_cnt number(10);
begin
  for v_tab in (select table_name from user_tables t where table_name like 'Test%') loop
    v_sql := 'drop table '|| v_tab.table_name;
    execute immediate v_sql ;
  end loop;
end;
5. Create a table space
----View tablespace
select * from Dba_Tablespaces;
----Create tablespace
create tablespace APPS_TS_TX_DATA logging datafile 'D:/software/softwareWorkspace/professionalSoftwareWorkspace/Oracle/oraclexe/app/oracle/oradata/XE/APPS_TS_TX_DATA.dbf' 
 size 200m autoextend on next 100m maxsize 20480m extent management local;
6. Split comma separated strings into multiple lines

 

SELECT REGEXP_SUBSTR ('26238,26239,55198', '[^,]+', 1,rownum)
from dual connect by rownum<=LENGTH ('26238,26239,55198') - LENGTH (regexp_replace('26238,26239,55198', ',', ''))+1;
7. Year comparison of oracle last year

 

select t.supplier_id,t.supplier_type,t.creation_date from srm_pos_supplier_info t
where 1=1
and to_char(t.creation_date,'yyyy')<to_char(sysdate,'yyyy')
and to_char(t.creation_date,'yyyy')>=(SELECT to_char(sysdate,'yyyy')-1 FROM dual);

--Take year
Select to_number(to_char(sysdate,'yyyy')) from dual;
select extract (year from sysdate) from dual;

--Take month
select to_number(to_char(sysdate,'mm')) from dual;
select extract (month from sysdate) from dual;

--Take date
Select to_number(to_char(sysdate,'dd')) from dual;
select extract (day from sysdate) from dual;
--Get current date:
Select to_char(sysdate,'yyyy') from dual;
Select to_char(sysdate,'MM') from dual;
Select to_char(sysdate,'dd') from dual;
Select to_char(sysdate,'yyyy-mm') from dual;
Select to_char(sysdate,'yyyy-MM-dd') from dual;
Select Extract(year from sysdate) from dual;
select extract (month from sysdate) from dual;
select extract (day from sysdate) from dual;
8. Take the maximum of two dates

 

select decode(sign(t.last_update_date-sysdate),-1,sysdate,t.last_update_date) from srm_pos_scene_manage t;
9. Take last month, last year's date

 

select to_char(add_months(sysdate,-1), 'yyyy-MM') from dual;
select to_char(add_months(sysdate,-1), 'MM') from dual;
SELECT to_char(sysdate,'yyyy')-1 FROM dual;

Tags: Database Oracle

Posted on Wed, 06 May 2020 07:37:41 -0700 by sspatel82