【围观】麒麟芯片遭打压成绝版，华为亿元投入又砸向了哪里？>>>
1.将一张表的某一个字段（多条单据）合并成一个字符串输出（逗号分隔） + ",(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.根据表的某个字段查询出最新的一条单据 ——根据supplierId查询最新的一条单据 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; --控制只输出最新一条记录
3.前30天的数据 select * from 表 where 日期=to_date(to_char(sysdate-30,'yyyy/mm/dd'),'yyyy/mm/dd');
4.删除指定的表，模糊查询 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.创建表空空间 ----查看表空间 select * from Dba_Tablespaces; ----创建表空间 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. 拆分以逗号分隔的字符串为多行 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、oracle的年份比较——去年年份 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); --取年份 Select to_number(to_char(sysdate,'yyyy')) from dual; select extract (year from sysdate) from dual; --取月份 select to_number(to_char(sysdate,'mm')) from dual; select extract (month from sysdate) from dual; --取日期 Select to_number(to_char(sysdate,'dd')) from dual; select extract (day from sysdate) from dual; --获取当前年月日： 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.取两个日期中的最大日期 select decode(sign(t.last_update_date-sysdate),-1,sysdate,t.last_update_date) from srm_pos_scene_manage t;
9.取上一个月，去年的日期 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;
10.批量生成表的所有字段注释SQL declare --创建时间 creation_date varchar2(20) := '2020-04-15'; --创建人 created_by varchar2(20) :='创建者'; ---业务描述 description varchar2(100) :='品类保存方法'; --开发方式——创建、modify method varchar2(30) :='创建'; ---输入表名 cursor vdata is select t.column_name,t.comments,t.TABLE_NAME from all_col_comments t where t.table_name = upper('srm_base_categories') ---表名 and instr(lower(t.column_name), 'attribute') = 0 and instr(lower(t.COLUMN_NAME),'last_update') = 0 and instr(lower(t.COLUMN_NAME),'version_num') = 0 and instr(lower(t.COLUMN_NAME),'created_by') = 0 and instr(lower(t.COLUMN_NAME),'creation_date') = 0; column_name varchar2(100); data_type varchar2(20); nullable varchar2(1); --声明变量,记录一行数 v vdata%rowtype; begin dbms_output.put_line('/**'); dbms_output.put_line('* Description：'||description); dbms_output.put_line('*'); dbms_output.put_line('* ======================================================================='); dbms_output.put_line('* 参数名称 参数描述 数据类型 是否必填'); --打开游标 open vdata; --循环遍历取数据 loop fetch vdata into v; exit when vdata%NOTFOUND; --处理驼峰式字段 select substr(replace(initcap('a' || lower(v.column_name)), '_', ''), 2) into column_name from dual; --获取字段类型与是否必填 select t.DATA_TYPE,case when t.NULLABLE = 'Y' then 'N' ELSE 'Y' END into data_type,nullable from ALL_TAB_COLUMNS t where t.TABLE_NAME=upper(v.table_name) and t.COLUMN_NAME=v.column_name and rownum<=1; dbms_output.put_line('* '||column_name|| ' ' ||v.comments || ' '||data_type || ' '|| nullable); end loop; --关闭游标 close vdata; dbms_output.put_line('*'); dbms_output.put_line('* Update History'); dbms_output.put_line('* ======================================================================='); dbms_output.put_line('* Version Date Updated By Description'); dbms_output.put_line('* ------- ----------- ----------- ---------------'); dbms_output.put_line('* V1.0 '||creation_date||' '||created_by||' '||method); dbms_output.put_line('* ======================================================================='); dbms_output.put_line('*/'); end;