常用sql语句(3)
insert into t_e values(101, ‘name1′);
insert into t_e values(102, ‘name3′);
insert into t_a(id,
code,
name,
c_id,
d_num
)
select t_a_sequence.nextval,b.code,b.name,c.c_id,d.d_num
from t_b b,t_c c,(select b1.code code,d1.d_num d_num from t_b b1,t_d d1 where b1.name3=d1.name3) d
where b.code=d.code(+) and b.name2=c.name2(+);
commit;
select t_a_sequence.nextval, b.code, c.c_id
from t_b b, t_c c
where b.name2 = c.name2(+);
select * from t_a;
delete from t_a;
commit;
select table_name from user_tables;
select * from t_a;
– 两表(多表)关联update — 被修改值由另一个表运算而来
select * from t_a;
select * from t_b;
select * from t_c;
select * from t_d;
select * from t_e;
update t_a a
set d_num = (select e.e_num from t_e e where e.name = a.name)
where exists (select 1 from t_e e where e.name = a.name);
SELECT * FROM t_a;
SELECT * FROM t_e;
/*
update ( select a.d_num d_num, e.e_num e_num
from t_a a, t_e e
where a.name = e.name
)
set d_num = e_num;
*/
UPDATE t_a a
SET a.d_num =(select e.e_num
from t_e e where e.name=a.name)
where a.rowid=(select a.rowid FROM t_e e
where e.name=a.name
)
select * from t_a;
select t_e.e_num from t_e, t_a where t_e.name = t_a.name;
rollback;
———————————————————————————————–
– oracle 查看索引
select * from user_ind_columns@sr2 ;
select * from user_ind_columns@sr2 where table_name = ‘HOTLINE_HEAD’;
select * from user_ind_columns;
———————————————————————————————–
– oracle 查看主键
select * from user_part_tables;
———————————————————————————————–
– 查看语句执行所用的时间 sql语句
set timing on
select count(*) from t;
SELECT sql_text “SQL”, executions “运行次数”, buffer_gets / decode(executions, 0, 1, executions) / 4000 “响应时间”
FROM v$sql
WHERE buffer_gets / decode(executions, 0,1, executions) / 4000 > 10
AND executions > 0;
select object_name, owner object_owner, status, object_type, created, last_ddl_time
from sys.all_objects o
where object_type = ‘PROCEDURE’
and object_name not like ‘BIN$%’
order by decode(owner, user, 0, 1), owner, object_name;
select s.synonym_name object_name, o.object_type
from sys.all_synonyms s, sys.all_objects o
where s.owner in (’PUBLIC’, user)
and o.owner = s.table_owner
and o.object_name = s.table_name
and o.object_type
in (’TABLE’, ‘VIEW’, ‘PACKAGE’,'TYPE’, ‘PROCEDURE’, ‘FUNCTION’, ‘SEQUENCE’)
SELECT object_name, object_type
FROM sys.all_objects
WHERE object_name NOT LIKE ‘BIN$%’
AND owner = ”;
SELECT text FROM all_source WHERE owner = ”;
———————————————————————————————–
– 绑定变量测试
select table_name from user_tables;
select * from t;
drop table t cascade constraint;
create table t(x int);
select * from t for update nowait;
create or replace procedure proc1
as
begin
for i in 1 .. 10000 loop
execute immediate
‘insert into t values(:x)’ using i;
end loop;
end;
/
create or replace procedure proc2
as
begin
for i in 1 .. 10000 loop
execute immediate
‘insert into t values (’||i||’)';
end loop;
end;
/
———————————————————————————————–
– 使用merge (oracle9i 以上才支持)
create table inventory (part_no integer,part_count integer);
insert into inventory values(1,5);
insert into inventory values(3,6);
create table shipment (part_no integer,part_count integer);
insert into shipment values(1,2);
insert into shipment values(2,2);
MERGE INTO inventory
USING shipment
ON (inventory.part_no = shipment.part_no)
WHEN MATCHED THEN
UPDATE SET part_count = part_count + shipment.part_count
WHEN NOT MATCHED THEN
INSERT VALUES (shipment.part_no,shipment.part_count);
commit;
select * from inventory;
———————————————————————————————–
– 解决ORA-00054 系统忙, 进程
–用select sid,serial#,status,username from v$session查找状态为
– INACTIVE的session,用
–alter system kill session ‘14,3534′ 查杀。
- 最新评论