有則改 無則加

                 --表名
merge into BIAOMING t1
        USING (select '李俊鎮(zhèn)衛(wèi)生院' 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 ('李俊鎮(zhèn)衛(wèi)生院','123','640121101000', '2020/6/1')

相同列名的表連接

select r.o_name 機構(gòu)名稱,r.b_name 業(yè)務(wù)分類,sum(r.b_value) 業(yè)務(wù)數(shù)量
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

閃回數(shù)據(jù)

--查詢時間之前的數(shù)據(jù)是否正確
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;
--閃回數(shù)據(jù)
FLASHBACK TABLE BIAO_MING TO TIMESTAMP to_timestamp('2020-06-30 15:00:00','yyyy-mm-dd hh24:mi:ss')

創(chuàng)建同義詞賦予權(quán)限

create or replace synonym biao_name --用這個名字
for yonghu_name.biao_name --獲得這個表的權(quán)限(用戶名.表名)

CASE WHEN THEN END

--如果orgcode 為 640121101000 則為 李俊鎮(zhèn)衛(wèi)生院
CASE ORGCODE WHEN '640121101000' THEN '李俊鎮(zhèn)衛(wèi)生院'
        WHEN '640121104000' THEN '閩寧鎮(zhèn)衛(wèi)生院'
        WHEN '640121400000' THEN '黃羊灘農(nóng)場衛(wèi)生院'
        WHEN '640121200000' THEN '勝利鄉(xiāng)衛(wèi)生院'
        WHEN '640121102000' THEN '望遠鎮(zhèn)衛(wèi)生院'
        WHEN '640121401000' THEN '玉泉營農(nóng)場衛(wèi)生院'
        WHEN '640121100000' THEN '楊和鎮(zhèn)衛(wèi)生院'
        WHEN '640121001000' THEN '團結(jié)西路街道'
        WHEN 'ERR_640121104000' THEN '閩寧鎮(zhèn)衛(wèi)生院'
        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循環(huán)與判斷

declare--聲明變量
i number :=1;--id
y number := 0;--task_id數(shù)量
r number :=1; --task_id
begin 
  while i<=82--循環(huán)到i為82時停止
  loop
    --sql
    insert into 
    table_name(id,task_id,num)
    values(i,lpad(r,3,'0'),10000);--將1轉(zhuǎn)化為001(lpad)
    commit;
    i := i+1;
    r := r+1;
    DBMS_LOCK.SLEEP(5);--執(zhí)行后停止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數(shù)量 (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 (要執(zhí)行多少次+最大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;

調(diào)用存儲過程
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;  ---循環(huán)i
y number := 0;--task_id數(shù)量
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;

調(diào)用
declare num_temp number;
begin
   TEST_UPDATE('EMR_PRESCRIPTION_DETAIL','20210328');
end;