Oracle_sql-复制表，拼接，函数

Oracle_sql-复制表，拼接，函数

1. 选择一张表内容插入到表中 --- 要求目标表存在
   INSERT INTO table1 (字段1，字段2，字段3......)

   SELECT 字段1，字段2，字段3......  FROM table2 ;
   
   commit;
   

2. 选择一张表内容插入到表中 --- 要求目标表不存在
   SELECT v1,v2 INTO table2 FROM table1 ;
   commit;

3. 拼接字符串
   变量||'%'

4. 自定义函数：
   先执行 ---> 再编译

5. 左外连接 left outer join on 条件
   select a.riskcode, a.agentcode, a.standprem, b.rate1, b.startdate
   from fsinsureprem a
   left outer join labankwagerate b
   on a.riskcode = b.riskcode
   and a.managecom = b.MANAGECOM
   and a.signdate = b.startdate

   select a.riskcode, a.agentcode, a.standprem, b.rate1, b.startdate
     from fsinsureprem a ,labankwagerate b
    where a.riskcode = b.riskcode(+)
      and a.managecom = b.MANAGECOM(+)
      and a.signdate = b.startdate(+)
      
   select a.riskcode,a.agentcode, a.standprem, b.rate1, b.startdate
   	from fsinureprem a, labankwagerate b
   	left outer join labankwagerate b
   where a.riskcode = b.riskcode
   and a.managecom = b.managecom
   and a.signdate = b.startdate
   

6. sql函数中的条件判断
   if 条件 then XXX elsif 条件 then XXX end if

7. sql计算两个字符串日期之间的年数差值
   select
   trunc(months_between(to_date(substr('20170201',0,6),'yyyymm'),
   to_date('201202','yyyymm'))/12)
   from dual;

8. 计算一个月的所有天数
   select to_date('200809','yyyymm')+(rownum-1) s_date from dual connect by rownum<=last_day(to_date('200809','yyyymm')) - to_date('200809','yyyymm')+1

9. 函数add_months()
   TO_CHAR(ADD_MONTHS(TO_DATE(vEmploydate, 'yyyymm'), 1), 'yyyymm')

10. 判断是不是为空
    eg：case when 字段 is null then ... end

11. with select from --- 可直接运行
    with cr as (select 1 as a, 2 as b from dual), cr2 as (select 3 as c, 4 as d from dual)
    select * from cr, cr2