OracleSQL操作总结

• 目录

  Oracle修改number类型字段的小数位数 

  MySQL Oracle判断字段是否存在不存在添加字段

  with as更高效的使用场景

  oracle存储过程

  批量插入（insert all...和merge into...）

  plsql中SQL查询条件带&符号的处理方法

• ---

  Oracle修改number类型字段的小数位数 

 需要注意的是，如下操作，是在表中的每条数据有唯一标识的情况下执行的，其他情况不建议；

• 第一步：创建备份表market_index_bak，并将原表数据复制到备份表

create table market_index_bak as select * from market_index;

• 第二步：将原表需要修改字段类型的值更新为null

update market_index MI set MI.TOTALPROFIT = null, MI.DAILYPROFIT = null;

• 第三步：修改字段类型

alter table market_index modify TOTALPROFIT number(20,6);
alter table market_index modify DAILYPROFIT number(20,6);

• 第四步：将第二步置为null的字段，恢复到原有的值

update market_index MI set MI.TOTALPROFIT = (
    select MB.TOTALPROFIT from market_index_bak  MB where MB.GROUPNAME = MI.GROUPNAME
    and MB.CREATETIME = MI.CREATETIME
 ),
  MI.DAILYPROFIT = (
    select MB.DAILYPROFIT from market_index_bak  MB where MB.GROUPNAME = MI.GROUPNAME
    and MB.CREATETIME = MI.CREATETIME
 );

•  第五步：清空、删除备份表

truncate table  market_index_bak ;
drop table market_index_bak ;

• MySQL Oracle判断字段是否存在不存在添加字段

• MySQL（Call、Drop语句要各占一行，再跑ALM流水线数据流水线时，需要以这种格式，否则新增字段不生效）

DROP PROCEDURE
IF EXISTS pro_AddColumn;
DELIMITER $$


CREATE PROCEDURE pro_AddColumn ()
BEGIN

IF NOT EXISTS (
	SELECT
		1
	FROM
		information_schema.`COLUMNS`
	WHERE
		table_name = 'sc_user'
	AND column_name = 'test_varchar'
) THEN
	ALTER TABLE sc_user ADD test_varchar VARCHAR (255) ;
END
IF ; END$$

DELIMITER ; 
CALL pro_AddColumn ;
DROP PROCEDURE pro_AddColumn ;

• Oracle（最后一行要加上反斜杠，再跑ALM流水线数据流水线时，需要以这种格式，否则新增字段不生效）

DECLARE
  num NUMBER;
BEGIN
  SELECT COUNT(1)
    INTO num
    from cols
   where table_name = upper('sc_user')
     and column_name = upper('test_varchar');
  IF num = 0 THEN
    dbms_output.put_line('column do not exist,start add column......');
    execute immediate 'alter table sc_user add test_varchar varchar(100)';    
  else
    dbms_output.put_line('column exist...... ');
  END IF;
END;
/

• with as更高效的使用场景

定义一个SQL查询结果，可以被整个SQL使用，查询结果使用两次及以上会把查询结果放到TEMP表中，
使用了临时表，就会增加额外的I/O开销，因此，表变量的方式并不太适合数据量大且频繁查询的情况
适合数据量大且不是频繁查询的情况
适用场景
1. 子查询过多时，且同一个子查询需要被多次使用的时候
2. UNION ALL使用场景，每隔被UNION ALL的查询，有部分查询条件重复时，可以用with as查询中间表，避免重复查询
缺点
3. with as的查询结果集相对于中间表不支持索引，而且with as查询的结果数据量大时，消耗的内存就大

• oracle存储过程

create or replace proceduce  R_OVERDUE_PRO(datadate String) is
    --游标
    Cursor Cur is
        select * from tableName;
    --变量
    username varchar2(200);
begin
    --循环游标
    For Temp In Cur Loop
        username := '';
        if (Temp.flag = 1) then
            --逻辑代码
        elsif (Temp.flag = 2) then
            --逻辑代码
        end if;
    End Loop;
    --循环输出1到10,1 .. 10 指的是1到100
    For j in 1 .. 10 Loop
        dbms_output.put_line('>>>>>>>>j='||j)
    End Loop;
    commit;
end R_OVERDUE_PRO;

• 批量插入（insert all...和merge into...）

  <insert id="insertUserBatch">
    INSERT ALL
    <foreach collection="list" item="map" index="index">
      INTO
      sc_user (username,user_age,gender,work_date)
      VALUESA
      (#{map.username},#{map.userAge},#{map.gender},#{map.workDate})
    </foreach>
    SELECT 1 FROM DUAL
  </insert>
  <!--单条记录merge，可以只执行更新或新增操作-->
  <insert id="MergeUserSingle">
    MERGE INTO sc_user A1 USING (select #{username} as username,#{userAge} as user_age,#{gender} as gender,#{workDate} as work_date from dual) A2
    ON (A2.username = A1.username)
    WHEN MATCHED THEN UPDATE SET A1.user_age=A2.user_age, A1.gender=A2.gender, A1.work_date=A2.work_date
    WHEN NOT MATCHED THEN INSERT (A1.username,A1.user_age,A1.gender,A1.work_date) VALUES (A2.username,A2.user_age,A2.gender,A2.work_date)
  </insert>
  <!--多条记录merge-->
  <insert id="MergeUserBatch">
    MERGE INTO sc_user A1 USING
    <foreach collection="list" index="index" item="map" separator=" union " open="(" close=")">
      select #{map.username} as username , #{map.userAge} as user_age,#{map.gender} as gender, #{map.workDate} as work_date from dual
    </foreach> A2
    ON (A2.username = A1.username)
    WHEN MATCHED THEN UPDATE SET A1.user_age=A2.user_age, A1.gender=A2.gender, A1.work_date=A2.work_date
    WHEN NOT MATCHED THEN INSERT (A1.username,A1.user_age,A1.gender,A1.work_date) VALUES (A2.username,A2.user_age,A2.gender,A2.work_date)
  </insert>

• plsql中SQL查询条件带&符号的处理方法

如：select * from tablename where column=chr(38)||'字符串'||chr(38)||'字符串'

oracle中chr(38)代表的是&符号