关于Oracle–sql

小小编

2 年前

Oracle数据库和schema关系：

一个数据库包含多个schema,schema是数据库对象（table,view,sequence...）的集合,schema一般默认和用户名相同。schema无法单独创建，
在创建用户的时候数据库默认新增一个同名schema。A schema下的表可以授权给B schema。

主键：

oracle数据库表没有自增主键，mysql数据库有，oracle表主键自增可以通过序列或者触发器实现。

序列：

--删除序列
drop sequence seq_name;
--创建序列,从1开始，步长为1
create sequence seq_name increment by 1 start with 1;
--使用，每次调用seq_name.nextval，序列值加1
select seq_name.nextval from dual;
insert into table_name(id,name,age) values(seq_name.nextval,'Hello',20);

触发器：

--创建一个触发器         
CREATE TRIGGER ContestDB_trigger  
    BEFORE INSERT ON ContestDB  
    FOR EACH ROW  
    WHEN (new.TID is null) --只有在tid为空时，启动该触发器生成tid号  
  begin  
    select ContestDB_sequence.nextval into :new.TID from sys.dual;  
  end;  
   
insert into ContestDB(TEAMNUM,MARKNUM) values('A20007013','A002');   
insert into ContestDB(TEAMNUM,MARKNUM) values('A20007014','A003');

批量插入（借助中间表）–同一事务：

--包含序列
insert into table_name (id,name,age,create_time)
select seq_name.nextval,t.* from(
    select 'John',20,sysdate from dual
    union all
    select 'Anna',18,sysdate from dual
)t

insert all into 是指把同一批数据插入到不同的表中，insert into 是插入同一个表里。

--无条件插入
INSERT ALL
   INTO t1(object_id, object_name)
   INTO t2(object_id, object_name)
;
--有条件插入
--insert first：对于每一行数据，只插入到 第一个when 条件成立的表，不继续检查其他条件。
INSERT FIRST 
  WHEN s_id <= 2 THEN 
     INTO stu1 (s_id, s_xm) 
  WHEN s_id >= 2 THEN 
     INTO stu2 (s_id, s_xm)
;
--对于每一行数据，对 每一个when 条件都进行检查，如果满足条件就执行插入操作。
INSERT ALL
  WHEN s_id <= 2 THEN 
     INTO stu1 (s_id, s_xm) 
  WHEN s_id >= 2 THEN 
     INTO stu2 (s_id, s_xm)
;

批量更新：

--把某表类型为a的记录，name字段更新为大写
UPDATE table_name t1 set t1.name=(SELECT upper(name) FROM table_name t2 WHERE t1.ID=t2.ID) WHERE t1.type='a'

Start With(树查询)：

SELECT ... FROM  table_name 
WHERE              + 条件3
START WITH         + 条件1
CONNECT BY PRIOR   + 条件2

--查询自身和子集
Select * From table_name
WHERE id = '00001' or pid ='00001'
Start With id = '00001'
Connect By Prior id = pid

union all 和 union区别：

union all不会去掉重复数据，union会去掉重复数据。

exists和in区别：

--前置条件：A表为大表，B表是小表。
方法一：
select * from tableA a,tableB b where a.aa = b.aa;//效率最差，数据量少差别不大，书写简单
方法二：使用in,小表作为主表
select * from tableB b where b.aa in(select a.aa from tableA a);
方法三：使用in,大表作为主表，效率低于方法二
select * from tableA b where a.aa in(select b.aa from tableB b);
方法四：使用exists，大表作为主表
select * from tableA a where EXISTS (SELECT * FROM tableB b WHERE b.aa = a.aa)

not exists和not in区别：

--not exists效率永远高于not in,因为not in全表扫描，而not exists子查询使用索引了。
Select name from employee where name not in (select name from student);
Select name from employee where not exists (select name from student);

分页查询：

select a.* from(select *,rownum  r from table_name)
where r>20 and r<50

死锁判断和解决方法：

1）执行以下语句：
select username,lockwait,status,machine,program from v$session where sid in
(select session_id from v$locked_object)
如果有输出的结果，则说明有死锁，且能看到死锁的机器是哪一台。字段说明：
Username：死锁语句所用的数据库用户；
Lockwait：死锁的状态，如果有内容表示被死锁。
Status： 状态，active表示被死锁
Machine： 死锁语句所在的机器。
Program： 产生死锁的语句主要来自哪个应用程序。

2）执行以下语句，可以查看到被死锁的语句：
select sql_text from v$sql where hash_value in
(select sql_hash_value from v$session where sid in
(select session_id from v$locked_object))

3）查找死锁的进程：
SELECT s.username,l.OBJECT_ID,l.SESSION_ID,s.SERIAL#,
l.ORACLE_USERNAME,l.OS_USER_NAME,l.PROCESS
FROM V$LOCKED_OBJECT l,V$SESSION S WHERE l.SESSION_ID=S.SID;

4）kill掉这个死锁的进程：
alter system kill session ‘session_id,serial#’; （引号内列名换为对应id）

Oracle插入/更新CLOB字段报ORA-01704（字符串文字太长）：原因是sql在执行之前会把所有字符类型的数据转换成VARCHAR2类型，而VARCHAR2类型的最大长度为4000，所以当字符串超过这个长度就会转换失败。

declare
    content clob;
begin
  content := '长字符串';
  insert into article(id,title,content) values(1,'标题',content );
  update article set content = content  where id = 1;
end;

条件索引：同一个父节点下不允许出现相同的有效节点

create unique index MYDB.UNIQUE_NAME
  on MYDB.T_GOODS (CASE  WHEN ("STATUS"='A' AND "END_TS"=TO_DATE(' 9999-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')) 
                              THEN UPPER("NAME")||'_'||TO_CHAR(NVL("PARENT_ID",0)) ELSE NULL END)

Merge Into：注意：USING 后面的表或者子查询语句必须有数据，否则insert不生效

MERGE INTO schema. table alias
USING { schema. table | views | query} alias
ON {(condition) }
WHEN MATCHED THEN
  UPDATE SET {clause}
WHEN NOT MATCHED THEN
  INSERT VALUES {clause};