注册 登录  
 加关注
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

无线时代辐射无穷

抓紧生宝宝,小心辐射

 
 
 

日志

 
 

Oracle SQL笔记  

2009-09-24 10:23:52|  分类: oracle |  标签: |举报 |字号 订阅

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

Java代码 复制代码
  1. insert into susr_basic(usercode)   
  2. select t1.usercode from v3_sub_1 t1   
  3. where not exists (select t2.usercode from susr_basic t2 where t1.usercode = t2.usercode)   
  4. and substr(t1.usercode,1,2) <> '86'  
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开头的号码以及用户表中已存在号码过滤掉再查询
Java代码 复制代码
  1. select t1.usercode from v3_sub_1 t1    
  2. where not exists    
  3. (select t2.usercode from susr_basic t2 where t1.usercode = t2.usercode )   
  4. and substr(t1.usercode,1,2) <> ''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'' ';  


删除和清理中间表的写法,对于表中有大量数据的时候:
Java代码 复制代码
  1. begin   
  2.    loop   
  3.      delete from xsmp102.sms_subscription_query  where query_time <(sysdate-1) and rownum < 10000;   
  4.      exit when sql%rowcount = 0;   
  5.      commit;   
  6.    end loop;   
  7.    exception when others then   
  8.     rollback;   
  9.     v_message := sqlcode||':'||sqlerrm;   
  10.     insert into sms_log(id,object_name,message,record_time) values   
  11.   
  12. ('job','sms_clear:sms_subscription_query',v_message,sysdate);   
  13.     commit;   
  14. end;  
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】对于下面逻辑:
Java代码 复制代码
  1. begin   
  2.      --1 短信 2 彩信 从zxdbm_ismp.v_ssrv_product视图查询servicetype   
  3.      --执行下面这句sql语句查询后,v_servicetype为null时,会转向异常区域   
  4.      select servicetype into v_servicetype from zxdbm_ismp.v_ssrv_product where productid = v_productid;   
  5.   exception when others then   
  6.      --将cpid,productid和flag以及当前操作的视图 入日志表 以便跟踪是哪个入参没有查询到servicetype   
  7.      v_message := sqlcode ||':'|| sqlerrm;   
  8.      insert into v3_sub_2_log values('zxdbm_ismp.v_ssrv_product',v_message,sysdate,v_cpid,v_productid,v_flag);   
  9.      commit;   
  10.      v_retvalue :='not find servicetype with productid... ';    
  11.      return;   
  12. end;  
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条件是会进入异常区域
Java代码 复制代码
  1. CREATE OR REPLACE PROCEDURE MyTest   
  2. (   
  3.    i             varchar2,   
  4.    o_retvalue    out varchar2   
  5. )   
  6. AS   
  7.   v_username     varchar2(100);   
  8. begin     
  9.         select username into v_username from zxdbm_ismp.ismp_oper_temp where spcode = i;   
  10.         update zxdbm_ismp.ismp_oper_temp set username = 'cheng' where spcode = i;   
  11.         delete from zxdbm_ismp.ismp_oper_temp where spcode = i;   
  12.         insert into zxdbm_ismp.ismp_oper_temp values(100,1,'sp','01014849',0,88);   
  13.         o_retvalue := 1;   
  14.         commit;   
  15. exception when others then   
  16.         rollback;   
  17.         o_retvalue  := 0;   
  18.   
  19. end MyTest;  
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:
Java代码 复制代码
  1. PROCEDURE write_log (   
  2. code IN INTEGER, text IN VARCHAR2)   
  3. IS   
  4. PRAGMA AUTONOMOUS_TRANSACTION;   
  5. BEGIN   
  6. INSERT INTO log VALUES (   
  7. code, text,   
  8. USER, SYSDATE   
  9. );   
  10. COMMIT:   
  11. END;  
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变量中
Java代码 复制代码
  1. for i IN   
  2. (select productid from zxdbm_ismp.ssrv_product_pkg_map where productpkgindex = i_commonindex)   
  3. loop   
  4.     if v_productIDList is null then   
  5.          v_productIDList := i.productid;   
  6.     else  
  7.          v_productIDList := v_productIDList||' '||i.productid;   
  8.     end if;   
  9. end loop;   
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   
Java代码 复制代码
  1. update v3_sub_4 v4 set   
  2.        (v4.userindex,v4.usercode) =   
  3.        (select zxdbm_200.sp_getmaxvalue_func('susr_basic',1),v2.usercode   
  4.        from zxdbm_ismp.v3_sub_2 v2,zxdbm_ismp.susr_info s   
  5.        where v2.customerindex = s.customerindex and v2.customerindex = v4.customerindex);   
  6.          
  7.   
  8. update v3_sub_4 v4 set   
  9.        (v4.userindex,v4.usercode) =   
  10.        (select zxdbm_200.sp_getmaxvalue_func('susr_basic',1),v2.usercode   
  11.        from zxdbm_ismp.v3_sub_2 v2 where 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,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信息存储外键约束:
Java代码 复制代码
  1. --业务表   
  2. alter table WAP_ISMP_SERVICE   
  3.   add constraint FK_SERVICE_REF_SCP_BASIC foreign key (CPINDEX)   
  4.   references WAP_SCP_BASIC (CPINDEX);   
  5. /   
  6.   
  7. --产品表   
  8. alter table WAP_ISMP_PRODUCT   
  9.   add constraint FK_PRODUCT_REF_SERVICE foreign key (SERVICEINDEX)   
  10.   references WAP_ISMP_SERVICE (SERVICEINDEX);   
  11. alter table WAP_ISMP_PRODUCT   
  12.   add constraint FK_PRODUCT_REF_SERVICED foreign key (CPINDEX)   
  13.   references WAP_SCP_BASIC (CPINDEX);   
  14. /   
  15.   
  16. 当有下面几个SQL语句时:   
  17. --sp   
  18. delete from wap_scp_basic where cpid = i_spid;   
  19. --sp下的产品   
  20. delete from wap_ismp_product where cpid = i_spid;   
  21. --sp下的产品下的业务   
  22. delete from wap_ismp_service where cpid = i_spid;  
--业务表  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 碰到函数后不起作用
Java代码 复制代码
  1. select  distinct sys_guid(),12,'K', t.parent_cat_id,sysdate,sysdate from wap_category1 t   
  2.                         where t.cat_id in   
  3.                       ((select cat_id from wap_ssrv_servicecatgroup_map1 where serviceindex = 1))  
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


解决方案:
Java代码 复制代码
  1.   
  2. select sys_guid(), x.*   
  3.   from (select distinct 12 c1,   
  4.                         'k' c2,   
  5.                         t.parent_cat_id,   
  6.                         sysdate c3,   
  7.                         sysdate c4   
  8.           from wap_category1 t   
  9.          where t.cat_id in ((select cat_id   
  10.                                from wap_ssrv_servicecatgroup_map1   
  11.                               where serviceindex = 1))) x;  
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如下:
Java代码 复制代码
  1. select servicetype, subcapability, srvtypeshortname, status   
  2.   from zxdbm_ismp.ssys_service_type   
  3.  where servicetype < 101  
  4.    and status = 1  
  5.    and subcapability != 2  
  6.    and servicetype not in (select servicetype   
  7.                              from zxdbm_ismp.v_ssrv_sp_service   
  8.                             where cpindex = 103005  
  9.                               and status != 4)  
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如下:
Java代码 复制代码
  1. select ss.servicetype, ss.subcapability, ss.srvtypeshortname, ss.status   
  2. from zxdbm_ismp.ssys_service_type ss    
  3. where not exists (select vs.servicetype,vs.subcapability   
  4.                              from zxdbm_ismp.v_ssrv_sp_service vs   
  5.                             where vs.cpindex = 100443  
  6.                               and vs.status != 4 and ss.servicetype = vs.servicetype   
  7.                               and ss.subcapability = vs.subcapability   
  8.                               )   
  9.                              and ss.servicetype < 101  
  10.                              and ss.status = 1 and ss.subcapability != 2   
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 
      评论这张
     
    阅读(661)| 评论(0)
    推荐 转载

    历史上的今天

    评论

    <#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
     
     
     
     
     
     
     
     
     
     
     
     
     
     

    页脚

    网易公司版权所有 ©1997-2017