Oracle中decode函数的使用

需求:
如果A表中的item_1的值为1,则判断A表中的Item_A是否为null,如果为null,则返回0,否则返回其本身
如果item_1的值不为1,则判断A表中的Item_B是否为null,如果为null,则返回0,否则返回其本身
--case when实现
select case 
when item_1=1 and Item_A is not null then Item_A 
when item_1=1 and Item_A is null then 0
when item_1!=1 and Item_B is not null then Item_B
else 0 end 
as result_value
  from A

--decode实现
select decode(item_1,1,decode(Item_A ,null,0,Item_A ),decode(Item_B,null,0,Item_B)) 
from A
decode说明:
decode(item,值1,值2,值3);
如果item等于值1,则返回值2,否则返回值3
且decode可实现多层嵌套

其它用法:
decode(item,值1,值2,值3,值4,...,default)
如果item等于值1,返回值2,等于值3,返回值4,...
如果没有匹配上,返回default

decode身为oracle最强大的函数之一,还有很多用法,暂时就介绍到这里

OracleMerge的使用

背景:
需要实现大量的如果有就更新,如果没有就添加的操作
-- 常规写法
if exists(select 1 from T where T.a='1001' )
    update T set T.b=2 Where T.a='1001' 
else 
    insert into T(a,b) values('1001',2); 

--Merge写法
MERGE INTO T T1
USING (SELECT '1001' AS a,2 AS b FROM dual) T2
ON ( T1.a=T2.a)
WHEN MATCHED THEN
    UPDATE SET T1.b = T2.b
WHEN NOT MATCHED THEN 
    INSERT (a,b) VALUES(T2.a,T2.b);
--在批量处理更新Or添加操作时,merge可以说是效率较高的一种

Java实现Excel读取

背景:
每个月公司都会有发两个表,
一个是考勤表
另一个是工时表
需要我们对照考勤表把工时表完善
写了两次实在是感觉很浪费时间,于是就写了个代码代替人工
pom引用

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>3.14</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>3.14</version>
</dependency>
package com.justin.excel;


import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.*;
import java.text.SimpleDateFormat;
import java.util.HashMap;
import java.util.Map;

/**
 * 描述:
 * 读写Excel
 *
 * @author Justin.Sun
 * @create 2018-07-30 17:34
 */
public class ExcelUtil {
    private static final String EXCEL_XLS = "xls";
    private static final String EXCEL_XLSX = "xlsx";
    private static Map<Integer,Integer> time=new HashMap<Integer, Integer>();
    private static Map<Integer,String> data=new HashMap<Integer, String>();
    private static FileInputStream in;
    private static Workbook workBook = null;
    private static  FileOutputStream out;

    /**
     * 读取考勤表并获取userName相关数据
     * @param fileName 文件路径
     * @param userName 名字
     * @throws IOException
     */
    public static void readExcel(String fileName,String userName) throws IOException {
        Sheet sheet=getSheet(fileName);
        // 第一行从0开始算
        int rowNumber = sheet.getLastRowNum();
        //逐行循环
        for (int i = 1; i <= rowNumber; i++) {
            Row row = sheet.getRow(i);
            if (row==null){
                System.out.println("获取到的行数"+i);
                break;
            }
            Cell cell;
            boolean arg=false;
            //循环行内的列
            for (int j=0;j<=row.getLastCellNum();j++){
                cell=row.getCell(j);
                if (cell==null){
                    break;
                }
                //获取数据的规则
                if (i==2){
                    String value=cell.toString();
                    try{
                        int day=Integer.parseInt(value);
                        time.put(day,j);
                    }catch (Exception e){
                        continue;
                    }
                }else if (i>2&&j==0&&userName.equals(cell.toString())){
                    arg=true;
                    for (int key:time.keySet()){
                        cell=row.getCell(time.get(key));
                        data.put(key,cell.toString().replace("\n","&").replaceAll("  ",""));
                    }
                }
            }
            //得到了我想要的数据,可以结束循环了
            if (arg){
                System.out.println(data);
                System.out.println("获取 "+userName+" 数据完成");
                break;
            }
        }
        close();
    }

    /**
     * 写入数据到工时表
     * @param fileName 文件路径
     * @param userName 名字
     * @throws IOException
     */
    public static void writeExcel(String fileName,String userName) throws IOException {
        SimpleDateFormat sdf=new SimpleDateFormat("d");
        Sheet sheet=getSheet(fileName);
        int rowNumber = sheet.getLastRowNum();
        for (int i = 1; i <= rowNumber; i++) {
            Row row = sheet.getRow(i);
            if (row==null){
                System.out.println("获取到的行数"+i);
                break;
            }
            Cell cell;
            //行循环
            for (int j=0;j<=row.getLastCellNum();j++){
                cell=row.getCell(j);
                if (cell==null){
                    break;
                }
                //名字替换
                if ("某某".equals(cell.toString())){
                    cell.setCellValue(userName);
                }
                //数据处理
                if (i>3&&j==1){
                    try{
                        int day= Integer.parseInt(sdf.format(cell.getDateCellValue()));
                        String value=data.get(day);
                        int index=value.indexOf("&");
                        String startTime="";
                        String endTime="";
                        if (index>0){
                            startTime=value.substring(0,index);
                            endTime=value.substring(index+1,value.length());
                        }
                        cell=row.getCell(3);
                        cell.setCellValue(startTime);
                        cell=row.getCell(4);
                        cell.setCellValue(endTime);
                        if (startTime.equals("")&&endTime.equals("")){
                            cell=row.getCell(5);
                            cell.setCellValue("");
                        }
                    }catch (Exception e){
                        cell=row.getCell(5);
                        //找到引用函数的单元格
                        if (cell.getCellType()==Cell.CELL_TYPE_FORMULA){
                            //执行函数并得到返回值
                            FormulaEvaluator evaluator = workBook.getCreationHelper().createFormulaEvaluator();
                            CellValue cellValue=evaluator.evaluate(cell);
                            //替换新值到单元格
                            cell.setCellValue(cellValue.getNumberValue());
                        }
                    }
                }
            }
        }
        out=new FileOutputStream(fileName);
        workBook.write(out);
        close();
        System.out.println("写入 "+userName+" 数据完成");
    }

    /**
     * 创建连接并获取工作薄
     * @param fileName 文件路径
     * @return 工作薄对象
     * @throws IOException
     */
    private static Sheet getSheet(String fileName) throws IOException {
        File file=new File(fileName);
        in = new FileInputStream(file);
        //Excel&nbsp;2003
        if(file.getName().endsWith(EXCEL_XLS)){
            workBook = new HSSFWorkbook(in);
            // Excel 2007/2010
        }else if(file.getName().endsWith(EXCEL_XLSX)){
            workBook = new XSSFWorkbook(in);
        }
        //默认获取第一个工作薄
        return workBook.getSheetAt(0);
    }

    /**
     * 关闭连接
     * @throws IOException
     */
    private static void close() throws IOException {
        if (workBook!=null){
            workBook.close();
        }
        if (in!=null){
            in.close();
        }
        if (out!=null){
            out.close();
        }
    }
}

public class Application {
    private static String readFileName="E:/考勤表.xlsx";
    private static String writeFileName="E:/空白工时表.xlsx";
    private static String userName="Justin";
    public static void main(String[] args) throws IOException {
        //执行
        ExcelUtil.readExcel( readFileName,userName);
        ExcelUtil.writeExcel( writeFileName,userName);
    }
}

Oracle数据库中instr函数的使用

INSTR(C1,C2,I,J) 

在一个字符串中搜索指定的字符,返回发现指定的字符的位置;

C1 被搜索的字符串 
C2 希望搜索的字符串 
I 搜索的开始位置,默认为1 
J 出现的位置,默认为1 

SELECT instr('hello world','world') FROM dual;

还可用于代替like,并使模糊更强大

在我们使用like时一般都是:
已知字段 like 关键字

如果反过来呢
例如我有一个地址:A省B市C区D街xxx号

数据库中有一个表Table,已知字段:
A:省
B:市
C:区
D:街
E:地区信息

那么我怎么通过已知地址获取到E字段的信息?

引用instr函数实现:
select E from table 
where
instr('A省B市C区D街xxx号',A)>1
and instr('A省B市C区D街xxx号',B)>1
and instr('A省B市C区D街xxx号',C)>1
and instr('A省B市C区D街xxx号',D)>1

Oracle修改表字段类型报错

错误信息:
[Err] ORA-22858: invalid alteration of datatype

原因:
oracle对该字段类型进行了限制

解决方案:
1.删除掉该字段保存
2.重新添加该字段并设置为想要修改的值
相关java错误信息:
ORA-01461:仅可以插入LONG列的LONG值赋值

原因:
字段类型错误导致数据无法添加

解决方案:
举例:
原字段类型为varchar2改为clob文本类型

SpringBatch读取excel异常

摘取关键报错信息:
1.Failed to initialize the reader
2.Failed to find end of row/cell records
3.Step failure: the delegate Job failed in JobStep.

根据异常信息可以得出,引起异常的原因为读取文件时有空行或空列出现
此处以excel为例:
解决途径:
1.Ctrl+A查看覆盖范围是否超出有效值部分
例如有效列只有1列,Ctrl+A后覆盖了两列,那么第二列就是引起错误的原因
清空第二列的内容重新执行
2.如没有1中情况或1中无效
复制有效值到新建excel中并对新建excel进行批处理操作

Oracle同步A表某字段值到B表某字段

-- 查询两字段值
select A.ITEM,B.ITEM from TABLE_A A 
LEFT JOIN TABLE_B B ON A.NAME=B.NAME
-- WHERE B.ITEM IS NOT NULL
;
--同步B表字段值到A表
UPDATE TABLE_A A
SET A.ITEM =(
select B.ITEM from TABLE_B B 
WHERE A.NAME=B.NAME
);

--将A表中多个字段值映射到B表多个对应字段
UPDATE TABLE_A A
SET (A.ITEM,A.ITEM_2) =(
select B.ITEM,B.ITEM_2 from TABLE_B B 
WHERE A.NAME=B.NAME
);