Oracle SQL笔记

声明:本文转自 http://www.blogjava.net/cheneyfree/archive/2009/01/26/252582.html

【1】批量插入数据:
对于从表A的数据要迁移到表B中，一般可以建议用insert into select from来实现比较快。一般大数据量就用它来实现数据的迁移，迁移前将目的表的索引先去掉，导完数据之后再将该表的索引建起。SQL执行速度可以在Oracle客户端来查看sql语句的执行时间进度。

insert into susr_basic(usercode)
select t1.usercode from v3_sub_1 t1
where not exists (select t2.usercode from susr_basic t2 where t1.usercode = t2.usercode)
and substr(t1.usercode,1,2) <> '86'

 

【2】联合查询条件不相等的情况:
将v3_sub_1中86开头的号码以及用户表中已存在号码过滤掉再查询
select t1.usercode from v3_sub_1 t1 where not exists (select t2.usercode from susr_basic t2 where t1.usercode = t2.usercode )and substr(t1.usercode,1,2) <> ''86'' ';

删除和清理中间表的写法，对于表中有大量数据的时候:
begin
   loop
     delete from xsmp102.sms_subscription_query  where query_time <(sysdate-1) and rownum < 10000;
     exit when sql%rowcount = 0;
     commit;
   end loop;
   exception when others then
    rollback;
    v_message := sqlcode||':'||sqlerrm;
    insert into sms_log(id,object_name,message,record_time) values

('job','sms_clear:sms_subscription_query',v_message,sysdate);
    commit;
end;

不建议用:
   --每次生成完订购关系记录后将中间表v3_sub_2数据全部清空  
   delete from zxdbm_ismp.v3_sub_2;
   commit;
因为在执行delete from一次执行完后会在日志表中写入很多回滚信息，负荷比较大。

【3】存储过程中的commit是否需要显示的写，看外部调用它的时候是否已经有commit了，如果有则不用写，如果没有则必须在存储过程中显示的写。

 

【4】LOG表中的时间字段名称不要用time直接命名，建议用createtime 类型不要用varchar，建议用date类型

 

【5】对于下面逻辑：
begin
     --1 短信 2 彩信 从zxdbm_ismp.v_ssrv_product视图查询servicetype
     --执行下面这句sql语句查询后，v_servicetype为null时，会转向异常区域
     select servicetype into v_servicetype from zxdbm_ismp.v_ssrv_product where productid = v_productid;
  exception when others then
     --将cpid,productid和flag以及当前操作的视图 入日志表 以便跟踪是哪个入参没有查询到servicetype
     v_message := sqlcode ||':'|| sqlerrm;
     insert into v3_sub_2_log values('zxdbm_ismp.v_ssrv_product',v_message,sysdate,v_cpid,v_productid,v_flag);
     commit;
     v_retvalue :='not find servicetype with productid... '; 
     return;
end;

如果在exception中insert into 后面没有显示的写commit语句，执行完存储过程后，会等几秒钟,Oracle缓冲区满后才能在v3_sub_2_log中查询到值。而如果insert 语句是在exception外，begin内执行的话，也是一样，要根据Oracle的系统参数配置，同样，看缓冲区是否已满，否则是不会将数据真正插入到v3_sub_2_log表中的。

【6】
delete
update
select
insert
在什么情况下执行会进入异常区域
例：  当selcect查询不到值会进异常区域
      当update更新不到值不会进异常
      当delete删除不到值不会进异常 
      当insert的值不满足sql语句insert条件是会进入异常区域 
CREATE OR REPLACE PROCEDURE MyTest
(
   i             varchar2,
   o_retvalue    out varchar2
)
AS
  v_username     varchar2(100);
begin  
        select username into v_username from zxdbm_ismp.ismp_oper_temp where spcode = i;
        update zxdbm_ismp.ismp_oper_temp set username = 'cheng' where spcode = i;
        delete from zxdbm_ismp.ismp_oper_temp where spcode = i;
        insert into zxdbm_ismp.ismp_oper_temp values(100,1,'sp','01014849',0,88);
        o_retvalue := 1;
        commit;
exception when others then
        rollback;
        o_retvalue  := 0;

end MyTest;

 

【7】两表联合查询：
select t1.usercode from v3_sub_1 t1
       where not exists (select t2.usercode from susr_basic t2
       where t1.usercode = t2.usercode ) --表示usercode键不相等的情况

在Oracle和SQLServer中这种情况不能用下面的sql语句来做：
select t1.usercode from v3_sub_1 t1,susr_basic t2  where t1.usercode <> t2.usercode 

SQLServer中可以用：
select t1.usercode from v3_sub_1 t1,susr_basic t2  where t1.usercode not in t2.usercode

 

【8】truncate table loaddata和delete from loaddata
execute immediate "truncate table AAA" 动态sql

【9】PRAGMA AUTONOMOUS_TRANSACTION
当前的存储过程作为已有事务的子事务运行，子事务的commit，rollback操作不影响父事务的状态。

ex:
PROCEDURE write_log (
code IN INTEGER, text IN VARCHAR2)
IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO log VALUES (
code, text,
USER, SYSDATE
);
COMMIT:
END;
因为这个procedure的作用是写log，
无论操作成功与否（即无名块是否执行rollback），
log当然是都应该记录的，所以要把它写成自治事务，
让这个procedure的提交独立于调用它的无名块，在rollback时也不会影响到log的记录。

一定要用commit or rollback, 如果不用的结果就是:
可以编译,但是在执行的时候会提示:
ORA-06519: active autonomous transaction detected and rolled back
ORA-06512: at "SYS.LOGGING_INFO", line 10
ORA-06512: at "SYS.BOOK_INSERT", line 15
ORA-06512: at line 2

 

 
【10】
SQL> insert into test_char values ('abc', '123', ',fd');
SQL> commit;
没有commit之前，其他的session并不能访问到这条数据
在本次session结束时，数据库会自动commit。
commit 是提交事务的语句
commit之前其他用户读到的还是旧数据，可以用sql plus worksheet试验一下。
commit 之前，数据并没有真正插入到物理表。
commit之后，lgwr进程会将redo缓冲中的内容写入redo log里边，但是不一定会真的写到数据文件里边。
DBWR进程，在下述条件下会将缓冲中的数据写入数据文件：
1.当服务进程扫描一定数目的缓冲以后，找不到干净的可用的缓冲，它会通知DBWn进程写入dirty缓冲中的数据.
2.DBWn进程会在检查点之前，周期地将缓冲区中的数据写入到文件，而检查点正是redo进程恢复时使用的起点。

oracle中insert一条记录后,没有执行commit在同一个session可以查询到。
例:
1:先执行
insert into Table1(A1,A2) values(1, 2)
不执行commit
2:在同一个session可以查询到
select * from Table1
结果:刚insert进去的那条记录可以查询到
A1,A2
1,2

【11】SQL是用于访问ORACLE数据库的语言，PL/SQL扩展和加强了SQL的功能，它同时引入了更强的程序逻辑。 PL/SQL支持DML命令和SQL的事务控制语句。DDL在PL/SQL中不被支持，这就意味作在PL/SQL程序块中不能创建表或其他任何对象。较好的PL/SQL程序设计是在PL/SQL块中使用象DBMS_SQL这样的内建包或执行EXECUTE IMMEDIATE命令建立动态SQL来执行DDL命令，PL/SQL编译器保证对象引用以及用户的权限。

 

【12】
唯一索引
create unique index idxsrvusrtotalamt02usridx on ssrv_user_totalamount02(userindex,amounttype,amountindex,productindex)

非唯一索引
create index idxsrvusrtotalamt02cpidx on ssrv_user_totalamount02(userindex,cpindex)

【13】
将循环体中查询出来的N个productid累加到v_productIDList变量中
for i IN
(select productid from zxdbm_ismp.ssrv_product_pkg_map where productpkgindex = i_commonindex)
loop
    if v_productIDList is null then
         v_productIDList := i.productid;
    else
         v_productIDList := v_productIDList||' '||i.productid;
    end if;
end loop; 

【14】
--将v3_sub_2表中余下userindex,usercode字段值insert到v3_sub_4用户表,批量update    
update v3_sub_4 v4 set
       (v4.userindex,v4.usercode) =
       (select zxdbm_200.sp_getmaxvalue_func('susr_basic',1),v2.usercode
       from zxdbm_ismp.v3_sub_2 v2,zxdbm_ismp.susr_info s
       where v2.customerindex = s.customerindex and v2.customerindex = v4.customerindex);
      

update v3_sub_4 v4 set
       (v4.userindex,v4.usercode) =
       (select zxdbm_200.sp_getmaxvalue_func('susr_basic',1),v2.usercode
       from zxdbm_ismp.v3_sub_2 v2 where v2.customerindex = v4.customerindex);
2种方式都可以

 

【15】对于
--产品套餐关系解除
select productindex into v_productindex from wap_ismp_product where cpid = i_spid;
当查询出来的productindex值有多个时，此sql会抛出异常：ORA-01422: exact fetch returns more than requested number of rows

 

【16】关于ORACLE中外键的设计的好处，以及设计外键的时候，对于多表关联的删除操作需要注意的问题的总结。
对于SP信息，其中业务信息和产品信息都与SP信息存储外键约束：

--业务表
alter table WAP_ISMP_SERVICE
  add constraint FK_SERVICE_REF_SCP_BASIC foreign key (CPINDEX)
  references WAP_SCP_BASIC (CPINDEX);
/

--产品表
alter table WAP_ISMP_PRODUCT
  add constraint FK_PRODUCT_REF_SERVICE foreign key (SERVICEINDEX)
  references WAP_ISMP_SERVICE (SERVICEINDEX);
alter table WAP_ISMP_PRODUCT
  add constraint FK_PRODUCT_REF_SERVICED foreign key (CPINDEX)
  references WAP_SCP_BASIC (CPINDEX);
/

当有下面几个SQL语句时：
--sp
delete from wap_scp_basic where cpid = i_spid;
--sp下的产品
delete from wap_ismp_product where cpid = i_spid;
--sp下的产品下的业务
delete from wap_ismp_service where cpid = i_spid;

只要其中一个表下面挂的关联表中有数据，就必须先(删除)这个关联子表，然后再删除主表，否则ORACLE会提示：
ORA-02292: 违反完整约束条件 (WAP.FK_SERVICE_REF_SCP_BASIC) - 已找到子记录日志

备注：Update操作和Insert以及Select操作不受此限制!

 

【17】当distinct 碰到函数后不起作用
select  distinct sys_guid(),12,'K', t.parent_cat_id,sysdate,sysdate from wap_category1 t
                        where t.cat_id in
                      ((select cat_id from wap_ssrv_servicecatgroup_map1 where serviceindex = 1))

结果：
1 52E623097A30C3D6E040280ABA987A06 12 K 100 2008-7-26 12:27:37 2008-7-26 12:27:37
2 52E623097A31C3D6E040280ABA987A06 12 K 200 2008-7-26 12:27:37 2008-7-26 12:27:37
3 52E623097A32C3D6E040280ABA987A06 12 K 100 2008-7-26 12:27:37 2008-7-26 12:27:37

期望结果;
1 52E623097A30C3D6E040280ABA987A06 12 K 100 2008-7-26 12:27:37 2008-7-26 12:27:37
2 52E623097A31C3D6E040280ABA987A06 12 K 200 2008-7-26 12:27:37 2008-7-26 12:27:37

解决方案：

select sys_guid(), x.*
  from (select distinct 12 c1,
                        'k' c2,
                        t.parent_cat_id,
                        sysdate c3,
                        sysdate c4
          from wap_category1 t
         where t.cat_id in ((select cat_id
                               from wap_ssrv_servicecatgroup_map1
                              where serviceindex = 1))) x;

 

【18】放开页面限制（SAG接入的SP可以同时申请SAG和3G两种业务能力的sql语句）

一家SP只要申请SAG或3G中的一种业务能力，就不能再申请余下的一种业务能力，其SQL如下：
select servicetype, subcapability, srvtypeshortname, status
  from zxdbm_ismp.ssys_service_type
 where servicetype < 101
   and status = 1
   and subcapability != 2
   and servicetype not in (select servicetype
                             from zxdbm_ismp.v_ssrv_sp_service
                            where cpindex = 103005
                              and status != 4)
SAG接入的SP即可以申请SAG又可以申请3G业务能力，其SQL如下：
select ss.servicetype, ss.subcapability, ss.srvtypeshortname, ss.status
from zxdbm_ismp.ssys_service_type ss 
where not exists (select vs.servicetype,vs.subcapability
                             from zxdbm_ismp.v_ssrv_sp_service vs
                            where vs.cpindex = 100443
                              and vs.status != 4 and ss.servicetype = vs.servicetype
                              and ss.subcapability = vs.subcapability
                              )
                             and ss.servicetype < 101
                             and ss.status = 1 and ss.subcapability != 2