oracle sql

有则改无则加

                 --表名
merge into BIAOMING t1
        USING (select '李俊镇卫生院' AS a, '2020/6/1' AS b
                   --临时表
               from dual) t2
        on (t1.org_name = t2.a and t1.bus_date = t2.b)
        when matched then
            update
            set t1.bus_value = '123'
        when not matched then
            insert (org_name, bus_value, org_code, bus_date)
            VALUES ('李俊镇卫生院','123','640121101000', '2020/6/1')

相同列名的表连接

select r.o_name 机构名称,r.b_name 业务分类,sum(r.b_value) 业务数量
from (
select * from aaa t
union 
select * from bbb t
union 
select * from ccc t
union 
select * from ddd t
) r
group by r.o_name,r.b_name

闪回数据

--查询时间之前的数据是否正确
select * from BIAO_MING as of timestamp to_timestamp('2020-06-30 15:00:00','yyyy-mm-dd hh24:mi:ss')
--如果报出未启用行移动功能, 不能闪回表则启用
alter table BIAO_MING enable row movement;
--闪回数据
FLASHBACK TABLE BIAO_MING TO TIMESTAMP to_timestamp('2020-06-30 15:00:00','yyyy-mm-dd hh24:mi:ss')

创建同义词赋予权限

create or replace synonym biao_name --用这个名字
for yonghu_name.biao_name --获得这个表的权限(用户名.表名)

CASE WHEN THEN END

--如果orgcode 为 640121101000 则为 李俊镇卫生院
CASE ORGCODE WHEN '640121101000' THEN '李俊镇卫生院'
        WHEN '640121104000' THEN '闽宁镇卫生院'
        WHEN '640121400000' THEN '黄羊滩农场卫生院'
        WHEN '640121200000' THEN '胜利乡卫生院'
        WHEN '640121102000' THEN '望远镇卫生院'
        WHEN '640121401000' THEN '玉泉营农场卫生院'
        WHEN '640121100000' THEN '杨和镇卫生院'
        WHEN '640121001000' THEN '团结西路街道'
        WHEN 'ERR_640121104000' THEN '闽宁镇卫生院'
        ELSE ORGNAME
--否则为orgname
        END ORG_NAME --别名为org_name

decode

DECODE(command,
0,’None’,
2,’Insert’,
3,’Select’,
6,’Update’,
7,’Delete’,
8,’Drop’,
‘Other’) command

oracle循环与判断

declare--声明变量
i number :=1;--id
y number := 0;--task_id数量
r number :=1; --task_id
begin 
  while i<=82--循环到i为82时停止
  loop
    --sql
    insert into 
    table_name(id,task_id,num)
    values(i,lpad(r,3,'0'),10000);--将1转化为001(lpad)
    commit;
    i := i+1;
    r := r+1;
    DBMS_LOCK.SLEEP(5);--执行后停止5秒
    --查询该条taskid为r的有几条 赋值给y
    select  count(1)  into  y from table_name
      where task_id = r;
      --如果y不等10000则再等待5秒
    if y =10000 then dbms_output.put_line('成功一次');
  elsif i<10000 then 
    dbms_output.put_line('没够1W');--输出
    DBMS_LOCK.SLEEP(5);
  end if;
    end loop;
end;

从后往前截取1位
UPDATE A_JGDMXXDZ ORG_NAME SET ORG_NAME =  SUBSTR(ORG_NAME,1,length(org_name)-1)
where REGEXP_LIKE(org_name,'(")+')

从第二位往后截取全部
UPDATE A_JGDMXXDZ ORG_NAME SET ORG_NAME = SUBSTR(ORG_NAME,2)
where REGEXP_LIKE(org_name,'(")+') --查询org_name 带“符号的

存储过程

采集存储过程
CREATE OR REPLACE PROCEDURE test1(domain_code varchar2,table_name varchar2,business_date varchar2,taskid_count number) AS
i number := 0;--id (目前库里最大的id+1)
y number := 0;--task_id数量 (0)
r number := 1; --r:task_id (要开始抽取的taskid 比如从001开始就填1)
begin
  select max(to_number(id))+1 into i from  UPLOAD_DATA_DECLARE t;
  select max(to_number(id))+taskid_count+1 into y from  UPLOAD_DATA_DECLARE t;
  while i< y--最大tid (要执行多少次+最大id)
  loop
    insert into UPLOAD_DATA_DECLARE(id,DOMAIN_CODE,TABLE_NAME,BUSINESS_DATE,DATA_PROCESS_START_TIME,DATA_PROCESS_END_TIME,DATA_COUNT,PACKAGE_NUM,batch_num,SC_TASK_ID,UPLOAD_DESC,SCAN_CODE,UPLOAD_DATA_COUNT,task_id,UPLOAD_FLAG,ORGANIZATION_NAME,ORGANIZATION_CODE,BATCH_SIZE)
    values(i,domain_code,table_name,business_date,'','','','','','','','','10000',lpad(r,3,'0'),'0','1','1','1000');
    commit;
    i := i+1;
    r := r+1;
    DBMS_LOCK.SLEEP(5);
    end loop;
end;

调用存储过程
declare num_temp number;
begin
   TEST1('002','emr_prescription_detail','20201106','3');
end;





修改
CREATE OR REPLACE PROCEDURE test_update(table_name varchar2,taskid varchar2) AS
i number := 1;  ---循环i
y number := 0;--task_id数量
sqlstr varchar2(200):='';
sqlstr2 varchar2(200):='';
begin
  sqlstr :=   'select ceil(count(1)/10000) from '||table_name||' where task_id = '||taskid;
execute immediate sqlstr into y;
  while i<=y
  loop
    sqlstr2:=
    'update '||table_name||' set task_id = lpad('||i
    ||',3,0) where task_id = '
    ||taskid||' and rownum < 10001';
    execute immediate sqlstr2;
    commit;
    i :=i+1;
    DBMS_LOCK.SLEEP(2);
    end loop;
end;

调用
declare num_temp number;
begin
   TEST_UPDATE('EMR_PRESCRIPTION_DETAIL','20210328');
end;