有则改 无则加
--表名
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;
