相关函数以及技巧

--创建job 相关及job执行存储过程查询，表执行最后操作时间，相关重要视图
declare  --job创建
  job1 number;
begin
  dbms_job.submit(job1, 'p_erp_wms_test1;', sysdate, 'trunc(sysdate)'); --每10 插入一条记录
end;   ---创建job时一定需要注意最后一个存放的时间间隔为字符型 一定需要加单引号 
--trunc(sysdate) 每天凌晨0时执行
--trunc(sysdate+1)+(8*60+30)/(24*60) 每天早上8点30分
--trunc(next_day(sysdate,'星期二'))+12/24; --每周二12点
--trunc(sysdate+1)+next_day(trunc(sysdate),'TUESDAY')+12/24 每星期二中午12点  MONDAY TUESDAY WEDNESDAY THURSDAY FRIDAY SATURDAY SUNDAY
--trunc(last_day(sysdate)+1) 没月第一天晚上12点
--trunc(last_day(sysdate))+1+1/24 每月1日凌晨一点执行

select * from user_jobs s where  s.WHAT = 'p_erp_wms_test1;' --job执行存储过程查询
select * from all_jobs;
select * from dba_jobs;
select * from dba_jobs_running n;  --正在执行的job query
select * from v$parameter;
select * from v$session;
select * from v$bgprocess;

begin
  dbms_job.run(85); --和select * from user_jobs;中的job值对应，看what对应的过程 --job执行
end;

begin
  dbms_job.remove(84); --job 删除
end;

begin
  dbms_job.broken(84,true); --job中断
end;

--同理使用 job 
begin
  dbms_job.broken(84,false);
end;

--重要使用 命令窗口的使用 （命令窗口有很大的作用） 执行exec 语句就需要使用到命令窗口
--上述同样的作用
exec dbms_job.broken(84,true) --回车即是执行 --支持commit 与rollback


select * from v$parameter;  --当锁job 时候需要看的view
select name, description sec from v$bgprocess; 
select * from v$session;--重要视图

select * from user_all_tables;  
select * from user_objects;  --表信息view
select uat.table_name 表名,
       (select max(last_ddl_time)
          from user_objects
         where object_name = uat.table_name) 最后修改日期
  from user_all_tables uat where uat.table_name='TAB_TIME'; --查询所有表最后修改的时间 查询具体一个表的

select * from user_all_tables s where s.table_name like '%TAB_TIME%';
select * from user_objects s where s.OBJECT_NAME like '%TAB_TIME%'; --可以查到  一定需要注意：表名对象存的是大写，

select * from dba_tablespaces s where s.contents='TEMPORARY';
select * from dba_tables s where s.table_name='TESTTABLE';
select * from dba_tables s where s.temporary='Y';--临时表查询

--常用函数
select last_day(sysdate) from dual;
select to_date(to_char(add_months(last_day(sysdate),-1)+1,'yyyy/mm/dd'),'yyyy/mm/dd') firstdayAmonth from dual; --每月第一天
select to_char(last_day(sysdate),'yyyy/mm/dd') lastdayAmonth from dual;  --每月最后一天
select add_months(sysdate,-6) from dual; --近6个月
select substr('E701',0,2) from dual; --截取字符
select 'zj&t' from dual;
select 'zj'||'&'||'t' from dual;
select 'zj'||chr(38)||'t' from dual; --特殊字符& 需要使用chr(38)转义
select 'a'||''''||'s' from dual;
select 'test''' from dual;--这里的‘字符比较特殊，其中前一个是特定转义字符，所以，切记这个形式
select * from wl.t_owner t where t.deptid like '%/%%' escape '/';  --查包含%条件的转义情况 ,类似的'_' 转义类似
select * from wl.t_owner t where instr(t.deptid,'%',1)>0;  --'%' 的ASCII值（十进制）为37 ，chr(37) 
select ceil(24.13) from dual; --取上整 25
select floor(24.13) from dual; --取下整 24
select cast(to_date('2017/12/07','yyyy/mm/dd hh24:mi:ss') as timestamp) date_to_timestamp from dual; --转换成时间戳
select count(distinct substr(t.tablename,0,instr(t.tablename,'_',1,2)-1)) from t_sys_table t ; --字符串截取
select to_timestamp('2017/12/07','yyyy/mm/dd hh24:mi:ss') from dual; --转时间戳
select greatest(to_date('20171212','yyyymmdd'),to_date('00010101','yyyymmdd')) from dual; --几个数比较大小，大
select least(to_date('20171212','yyyymmdd'),to_date('00010101','yyyymmdd')) from dual;--几个数比较大小，小
--case when 、decode(,,,), nvl()
select nvl(to_char(''),' ') from dual; --将数字或者日期先转为字符，再判断是否为null，为null再使用nvl函数将其置为空，或者空格为OBI显示为空使用
--in多个条件的使用
select * from wmsdba.t_yc_ownerrk r where (r.deptid,r.kindname) in (('0301','热轧'),('0106','普冷')) and r.inserttime>sysdate-2;
select *
  from wmsdba.t_yc_ownerrk r
 where (r.deptid, r.kindname) in
       (select c.deptid, c.kindname from wmsdba.t_yc_ownerck c)
   and r.inserttime > sysdate - 2;
--in多个条件可以替代，中间使用符号拼接或者隔开 再拼接其想要得到的条件
select * from wmsdba.t_yc_ownerrk r where r.deptid||'*'||r.kindname in ('0301'||'*'||'热轧','0106*普冷') and r.inserttime>sysdate-2;
   
--经典 like 使用 用 || 符号拼接条件 
--chr(9) tab空格 chr(10)换行 chr(13) 换行  chr(10)&chr(13)回车换行  chr(32) 全角空格 
select t.firstdis from wmsdba.t_common_dept t where t.firstdis like '%'||chr(10)||'%';
--这种拼接 like 还可以使用另一种方法替代 instr
select t.firstdis from wmsdba.t_common_dept t where instr(t.firstdis,chr(10))>0 or instr(t.firstdis,chr(13))>0;
--trim 的替代函数 replace 更实用
select distinct trim(replace(t.firstdis,chr(10),'')) from wmsdba.t_common_dept t where t.firstdis like '安徽%';
select distinct replace(t.firstdis,chr(32),'') from wmsdba.t_common_dept t where t.firstdis like '安徽%';
select replace(' 测 试 ',chr(32),'') from dual;
select chr(9)||'001' from dual;--导出csv格式问题前面拼接字符 chr(9) 最佳解决办法
select trunc(1213.14) from dual;--直接舍弃小数取整
select round(1213.14,2) from dual;--四舍五入取几位小数
select to_char(sysdate,'d') from dual;--每周第几天
select to_char(sysdate,'dd') from dual;--每月第几天
select to_char(sysdate,'ddd') from dual;--每年第几天
select to_char(sysdate,'ww') from dual;--每年第几周
select to_char(sysdate,'iw') from dual;--每年第几周
select to_char(sysdate,'mm') from dual;--每年第几月
select to_char(sysdate,'q') from dual;--每年第几季度
select to_char(sysdate,'yyyy') from dual;--年
select to_char(1210.73,'9999.9') from dual;--return '1210.7'
select trim(to_char(1210.73,'9999.9')) from dual;--return '1210.7'  之后再使用 trim,去掉带来的多个空格
select to_char(1210.73,'9,999.99') from dual;--return '1,210.73'
select to_char(1210.73,'$9,999.00') from dual;--return '1,210.73'
select to_char(21,'000099') from dual;--return '000021'
select to_date('2017-12','yyyy-mm') from dual;--return '2017-12-01' date型只有精确到天
select to_char(sysdate,'day') from dual; --return 星期几
select 1 flag from dual where to_number(to_char(sysdate,'hh24')) not between 9 and 18; --not between
select last_day(sysdate) from dual; --月末一天
select add_months(sysdate,-1) from dual; --上一月 
select to_char(trunc(sysdate, 'MONTH'), 'yyyy-mm-dd') First_DayOfMonth,
       to_char(last_day(trunc(sysdate, 'MONTH')), 'yyyy-mm-dd') Last_DayOfMonth
  from dual;
select rpad('jxtest',10,'*') from dual; --rpad不够的指定位的使用'*' 来补齐至指定长度rpad函数
select rpad(substr(g.packnum, 0, length(g.packnum) / 2),
            length(g.packnum),
            '*') rpadStr
  from wmsdba.t_gg_sj_goods g
 where g.goodsstateno = '20'
   and g.ownername = '上海欧冶材料技术有限责任公司成都分公司'
   and g.packnum is not null;
select cast('1090' as int) f1testt from dual; --直接转换类型
select trunc(last_day(add_months(sysdate, -1)) + 1) from dual; --上个月故小于本月第一天
select trunc(last_day(add_months(sysdate, -2)) + 1) from dual; --上个月大于等于上月第一天
select to_timestamp('2018-02-16 11:10:20','YYYY-MM-DD HH:MI:SS') from dual;


--开窗函数 常用 row_number() over(partition by   order by ) rn 还有其它开窗函数 row_rank()等见存的例子使用
--嵌套子查询中不能使用order by 内联视图中可以使用 order by
--join 与group by 连用禁忌，这样会导致数据重复（reason：oracle 机制），解决方法：先取出，再外面一层子查询进行group by
declare
maxconfig wmsdba.t_bd_deptconfig.configid%type; --引用类型 字段定义
maxdepyconfig wmsdba.t_bd_deptconfig%rowtype; --行引用类型定义
begin
dbms_output.put_line('this string breaks here.'||'test');--输出For 测试使用的打印方法
end;
select f.deptid,f.configid from wmsdba.t_bd_deptconfig f where 1=1; --程序拼接使用  --技巧
select f.deptid,f.configid,rowid from wmsdba.t_bd_deptconfig f where 1=2; --刷数据新增数据使用  --技巧
--group by rollup函数的使用
select g.deptid,grouping(g.deptid),sum(g.netweight) from wmsdba.t_gg_sj_goods g where g.goodsstateno='20' and g.r_record_create_date>sysdate-1 group by rollup(g.deptid);
select lpad('str1',length('substring'),'teststr') from dual; --默然空格，给字符串左边添加字符至指定长度函数，默认添加空格
select to_char(sysdate-level,'yyyy-mm-dd') from dual connect by level<=7; --connect by 使用的子分节点常用
select rownum from dual connect by rownum<=10; --其中的rownum 等同于level 可以替换
select regexp_substr('01#02#03#04','[^#]+',1,3,'i') from dual; --需要使用简单正则表达式（表示出的分割符来进行分割字符串成数组）
--针对 regexp_substr 函数还有regexp_count 不过regexp_count 目前oracle 11g之后才有  --其中的最后一个参数 'i' 不区分大小写检索 'c' 区分大小写检索 
select (trunc(sysdate-to_date('20170701','yyyymmdd'))) days from dual; --时间间隔多少天
select min(s.dates) from (select to_char(sysdate-level+1,'yyyy-mm-dd') dates from dual connect by level<=208) s;
select count(1) from (select to_char(sysdate-level+1,'yyyy-mm-dd') dates from dual connect by level<=208) s;--connect by 所取时间验证
--connect by 间隔半小时取得一天的时间
select to_char(to_date('00:00', 'hh24:mi') + 30 * (level - 1) / 24 / 60,
               'hh24:mi') stime,
       to_char(to_date('00:00', 'hh24:mi') + 30 * level / 24 / 60,
               'hh24:mi') etime
  from dual
connect by level < 49;
--connect by 取得间隔三小时时间段 
select to_char(to_date('00:00', 'hh24:mi') + 180 * (level - 1) / 24 / 60,
               'hh24') || '-' ||
       to_char(to_date('00:00', 'hh24:mi') + 180 * level / 24 / 60, 'hh24') intervalhours
  from dual
connect by level < 9;
select to_number(to_char(sysdate,'hh24')) from dual; --时间点转数字可以更好使用避免字符比较出错
select decode(substr(to_char(0.312),1,1),'.','0','')||to_char(0.312) a from dual; --第一位为0的数字转字符不去掉0 复杂的方法
select rtrim(to_char(0.1234,'0.99999999'),'0') A from dual; --可以先做四舍五入、也可以rtrim 然后统一 --oracle10g有时候不会识别这种转换方式
select rtrim(to_char(0.1234,'99999999.99999999'),'0') A from dual;
--decode函数挺有用
select decode(sign(100-90),-1,100,90) from dual; --decode除了行转列与case when 功能大致相一使用，还可以与sign结合使用
--sign取得符号函数 实用
select decode(sign(numTest), -1, 0, numTest) numTest
  from (select -64 numTest
          from dual
        union all
        select 32 numTest from dual);

--经典连接 cross 笛卡尔连接（交叉集合）  anti join 发连接（结果集与inner join相反）

--经典update  --2018年01月11日做过如下数据更改 去掉省市后面有tab换行符的数据
update wmsdba.t_common_dept t set t.firstdis=trim(replace(t.firstdis,chr(10),'')) where t.firstdis like '%'||chr(10)||'%';

--删除语句 两种都OK --测试
delete from adminus a where a.admin_id='YT';
delete adminus a where a.admin_id='YT';

--注意:存储过程调用同函数相似
--表的管理及其信息
select b.owner 所有者,
       b.tablespace_name 表空间,
       b.table_name 表名,
       c.comments 表备注
       from
       all_all_tables b,
       all_tab_comments c
 where b.table_name = c.table_name
 and b.owner = c.owner
 and b.owner in ('WMSDBA','WMSHISTORY')
 order by b.OWNER asc,b.table_name asc;

--建表注意 主键有创建日期两个字段 必备一般 可为空选项 索引创建
--新增数据使用 技巧 select rowid,dbpro1.t_sys_table.*from dbpro1.t_sys_table where 1=2 技巧必备

sql高级实用扩展：

• 闪回(可以用于数据恢复，功能强大)；

--oracle数据闪回技术数据库恢复技术
select VERSIONS_XID,
       versions_operation,
       to_char(versions_starttime, 'yyyymmddhh24miss'),
       versions_starttime,
       versions_endtime,
       rowid,
       packnum,
       goodsstatename,
       pregoodsid,
       netweight,
       goodsid
  from wmsdba.t_goods versions between timestamp minvalue and maxvalue
 where deptid = 'QTest01'
   and pregoodsid = '67291607812b4d5a700904f75d2';

• 强走索引；有效提高sql执行效率

• 程序块循环写操作等小积累

--循环小积累 写法
--三种常用循环：
-- for循环
declare
begin
 for counter in 1..5 loop
    dbms_output.put_line('for循环：'||counter);
 end loop;
end;    

--do...while循环  特点：先执行，后判断
declare
i number(2);
begin
    i:=1;
    loop 
        dbms_output.put_line('do...while循环：'||i);
        i:=i+1;
    exit when(i>5);
    end loop;
end;  

-- while循环  特点：先判断，再执行
declare
i number(2);
begin
    i:=1;
    while i<5 loop
    dbms_output.put_line('while循环：'||i);
    i:=i+1;
    end loop;
end;

begin
  for id in 1 .. 2 loop
    dbms_output.put_line(id);
  end loop;
end;

--使用while循环打印数字的1-10  
--set serveroutput on  
  
declare  
    --定义循环变量赋初值为1  
    pnum number := 1;  
begin  
    while pnum<=10 loop  
          --循环体  
          dbms_output.put_line(pnum);  
          --使得该变量+1  
          --pnum++;是错误的写法，oracle不支持这种写法  
          pnum := pnum+1;  
      end loop;  
end;  

--使用loop循环输出1-10  
--注意loop循环和while循环相反  
  
declare  
  pnum number:=1;  
begin  
  loop  
    exit when pnum>10;--当pnum变量大于10的时候退出loop循环  
    --输出循环变量  
    dbms_output.put_line(pnum);  
    --循环变量自增  
    pnum := pnum+1;  
      
  end loop;  
end; 

--使用for循环打印1-10  
--set serveroutput on  
  
declare  
    --定义循环变量  
    pnum number:=1;  
begin  
    for pnum in 1..10 loop  
      dbms_output.put_line(pnum);  
      end loop;  
end;


--加字段
alter table wmsdba.t_wx_bluetooth add distype varchar2(2); --给最初的表数据分段 加距离范围类型字段
--修改表字段名
alter table wmsdba.t_wx_bluetooth_parameter rename column avgdistance to avgrssi;  
--建表
create table wmsdba.t_wx_bluetooth 
(macpostion varchar2(20),measuredate date,rssi number(9,2),distance number(9,2),create_date date default sysdate);
--修改字段类型
alter table wmsdba.t_wx_bluetooth_temp modify (measuredate varchar2(20));--修改列类型

--建表
CREATE TABLE Course
(
cnumb varchar(10) PRIMARY KEY,              ---  设为主键
cname varchar(40) NOT NULL,                 ---  非空
chours int CONSTRAINT conshour CHECK(chours >= 0 and chours <= 300),
credit decimal(5,2) CONSTRAINT conscredit CHECK(credit >= 0 and credit < 20),
intro text                                  --- text字段存放一段文字
);


--insert 与 update 的 有用写法 测试ForHT
--建表
create table t_outStockDetail (outStockId varchar(20) primary key,cargoUnits varchar(100),
deliveryUnits varchar2(100),outWeight number(9,2),outCount number(9,2),totalWeight number(9,2),totalCount number(9,2));

create table t_outStockDetail_test (outStockId varchar(20) primary key,cargoUnits varchar(100),
deliveryUnits varchar2(100),outWeight number(9,2),outCount number(9,2),totalWeight number(9,2),totalCount number(9,2));
--加数据
select t.outstockid,
       t.cargounits,
       t.deliveryunits,
       t.outweight,
       t.outcount,
       t.totalweight,
       t.totalcount,
       rowid
  from wmsdba.t_outstockdetail t;
--加数据 
select t.outstockid,
       t.cargounits,
       t.deliveryunits,
       t.outweight,
       t.outcount,
       t.totalweight,
       t.totalcount,
       rowid
  from wmsdba.t_outstockdetail_test t;
--
select t.outstockid,
       t.cargounits,
       t.deliveryunits,
       t.outweight,
       t.outcount,
       t.totalweight,
       t.totalcount
  from wmsdba.t_outstockdetail t;
--插入 将t_outstockdetail_test这个list中t_outstockdetail没有它的值插入其中
insert into t_outstockdetail
  (outstockid,
   cargounits,
   deliveryunits,
   outweight,
   outcount,
   totalweight,
   totalcount)
  select t.outstockid,
         t.cargounits,
         t.deliveryunits,
         t.outweight,
         t.outcount,
         t.totalweight,
         t.totalcount
    from t_outstockdetail_test t
   where t.outstockid not in
         (select t.outstockid from t_outstockdetail t);
         
--上述同样方法 另一种写法 not exists          
insert into t_outstockdetail
  (outstockid,
   cargounits,
   deliveryunits,
   outweight,
   outcount,
   totalweight,
   totalcount)
  select t.outstockid,
         t.cargounits,
         t.deliveryunits,
         t.outweight,
         t.outcount,
         t.totalweight,
         t.totalcount
    from t_outstockdetail_test t
   where not exists
   (select 1 from t_outstockdetail d where d.outstockid = t.outstockid);

--update 
update wmsdba.t_outstockdetail t
   set (t.cargounits,
        t.deliveryunits,
        t.outweight,
        t.outcount,
        t.totalweight,
        t.totalcount) =
       (select d.cargounits,
               d.deliveryunits,
               d.outweight,
               d.outcount,
               d.totalweight,
               d.totalcount
          from wmsdba.t_outstockdetail_test d
         where d.outstockid = t.outstockid)
 where exists (select 1
          from wmsdba.t_outstockdetail_test d
         where d.outstockid = t.outstockid);

• oracle sql拼接(有异于mysql)

create global temporary table t_wms_erp_test_temp(memo varchar2(4000)) on commit preserve rows;
   alter table t_wms_erp_test_temp add (create_date date default sysdate);
drop table t_wms_erp_test_temp;
--sql拼接
declare
  str varchar2(1000) := '';
  v_1 date:=trunc(sysdate - 7);
begin
  str := 'insert into t_wms_erp_test_temp
    (memo)
    select t.deptid
      from wmsdba.t_common_dept t
     where t.r_record_create_date >= :v_1 and t.deptid not like ''03%''';
     /*dbms_sql.execute(str);*/
     
  execute immediate str using v_1;
end;

select * from wmsdba.t_wms_erp_test_temp;

--返回单条结果
declare
  str varchar2(500);
  c_1 varchar2(10);
  r_1 t_zyinbill %rowtype; --list
begin
  c_1 := '0103';
 /* select i.deptid, i.inbillownername
    from wmsdba.t_zyinbill i
   where i.deptid = :c1
     and rownum = 1;*/
  str := 'select * from wmsdba.t_zyinbill i where i.deptid=:c1 and rownum=1';
  execute immediate str
    into r_1
    using c_1;
    dbms_output.put_line(r_1.deptid||r_1.inbillownername);
end;