常用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′ 查杀。