分类:数据库


Oracle参考A表新建B表


--复制表结构和数据,但不会创建索引
create table B as select * from A;

--只复制表结构
create table B as select * from A where 1=2;

--复制数据的SQL已写入其他文章,搜索关键字Oracle Insert就可找到

Oracle批量insert数据


--表结构相等
insert into A select * from B where B.type='A';

--部分字段
insert into A(id,name) select B.id,B.name from B where B.type='A';

Oracle实现批量动态更新数据


DECLARE
end_num number(30);
BEGIN
select count(*) INTO end_num from TABLE_A;
   for i in 1 .. end_num loop
UPDATE TABLE_A SET name_=('justin' || i) where table_id=
	(SELECT table_id FROM 
		(select table_id from TABLE_A 
		where ROWNUM<=i ORDER BY ROWNUM DESC) 
	where ROWNUM=1);
       sys.dbms_output.put_line('justin' || i);
   end loop;
   commit;
END;

DECLARE:声明变量关键字
end_num number(30); 变量名及类型
BEGIN :相当于{
select count(*) INTO end_num from TABLE_A; 通过into关键字将查询结果赋值给变量
for i in 1 .. end_num loop :循环语法,变量为i,从1开始,从end_num结束
内层查询:根据行号倒序获取i条数据
外层查询:只获取提一条内容查询结果
table_id=第i条数据的table_id的值
end loop; 循环结束语法
commit :提交
END :相当于 }

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最强大的函数之一,还有很多用法,暂时就介绍到这里

Oracle查询某表的所有字段



select column_name from user_tab_cols where table_name='TAB';

--TAB为要查的表名,必须为大写

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可以说是效率较高的一种

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文本类型

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
);

ORA-00911: 无效字符


一般情况下是由于JDBC要执行的SQL语句尾部多结束符造成的,即“;”