oracle相关总结2

1.创建、修改、删除表，增加索引、删除索引，增加外键关联，对表及字段增加comment说明

  create table Settle_mgt_deposit(
  company_Id VARCHAR2(15) not null,
  deposit    NUMBER(12,2)

) ;

--注释

comment on table Settle_mgt_deposit is '表A';

 comment on column Settle_mgt_deposit.company_id is 'ID';

--主键、外键约束
 ALTER TABLE Settle_mgt_deposit ADD CONSTRAINT PK_SETTLEMGT_COMPANY_ID PRIMARY KEY(COMPANY_ID);
 ALTER TABLE Settle_mgt_deposit ADD CONSTRAINT FK_SETTLEMGT_COMPANY_ID FOREIGN KEY(COMPANY_ID) REFERENCES COMPANY(COMPANY_ID);

---外键级联删除，SETTLE_REPORT主表的SETTLE_REPORT_ID删除，则SETTLE_REPORT_ITEM表的SETTLE_REPORT_ID与之相等的数据全部会自动级联删除
alter table SETTLE_REPORT_ITEM add constraint FK_SETTLE_REPORT_ITEM foreign key (SETTLE_REPORT_ID)  references SETTLE_REPORT (SETTLE_REPORT_ID) on delete cascade;

--删除表的某一列
alter table  settle_mgt_mid drop column PRODUCT_CN_NAME;

--增加列

ALTER table settle_mgt add  SELF_VALUE number(12,2) ;

--修改列

ALTER TABLE TABLENAME MODIFY COLUMN NUMBER(7,2);

2.oracle 排序问题

有时排序时候需要把为Null的值放在最前或者最后，可以用下面这个方法。
order  by 字段名 asc/desc nulls first/last

还有在一些排序时如果按照某一列排序，在设置了翻页时会可能出现重复数据，这时候只要在排序的字段后加上主键即可。

----oracle  char与varchar2区别
char 只有中文和全角字符占2位字符，varchar2始终占2位。同样长度20的列，如果都插入2位字符,char会自动填充满20位，而varchar2只会占用2位空间。char插入Null值也会占空间，但是varchar2不占位。在扩增列的宽度时，varchar2效率高。查询速度方面如果某一列有索引则varchar2效率高，否则一样。

3.聚合函数使用
-------------------------test1-----------------
<select id="GradeImportentSp_listGradeImportentCompanyCount" resultClass="java.lang.Integer" parameterClass="company">
      with t as
 (select license_no,
         cn_name,
         company_id,
         law_man,
         short_name,
         lag(license_no) over(partition by cn_name order by license_no) as pval1
    from (SELECT distinct a.company_id,
                          a.cn_name,
                          a.short_name,
                          a.law_man,
                          c.license_no
            FROM company a, company_aptitude c, sp s
           WHERE a.company_id = s.company_id
             and s.aptitude_id = c.aptitude_id
             and a.company_status = 'A'))
select count(1) from
(select cn_name,company_id,law_man,short_name, Ltrim(MAX(sys_connect_by_path(license_no, ',')),',') as license_no
  from t
start with pval1 is null
connect by prior license_no = pval1
group by cn_name,company_id,law_man,short_name
    <dynamic prepend="having">
            <isNotEmpty property="cnName" prepend="" >
                cn_name      like '%' ||#cnName# || '%'
            </isNotEmpty>
    </dynamic>
    )
</select>

-----------------test2------------------------ora9i用这种方法，ORA10G直接用wm_concat方法即可----------------------------------------
表名t ,列名 a,b. 根据 a 分组 ,order by b.

with ta as(
select a,lag(b) over(partition by a order by b) ba
from t)

select a,ltrim(max(sys_connect_by_path(b,',')),',') b
from ta
start with ba is null
connect by b = ba
group by a
------------------test3 ----------------------------------------------
select x.node_id
              from (select t.*, level lev
                      from ooooo_test t
                    connect by prior t.next_node_id = t.node_id
                     start with t.node_id ='2') x;

查询节点Node_id为2下的所有节点的子节点
--------------------级联查询---------------------------------------------
    字段A    字段B
    1        3
    2        4    
    3        5
    4        6
    5        8
select x.node_id
              from (select t.*, level lev
                      from ooooo_test t
                    connect by prior t.next_node_id = t.node_id
                     start with t.node_id ='1') x;
查询结果                    
    字段A   字段B
    1        3
    3        5
    5        8
-------------------------------------------------------------------------

4.oracle游标的试用，比如初始化数据及存储过程

例1：

 --这里cur_mp_month和cur_mp_quarter分别表示一个List,而r_mp_month是List中循环的对象。
CREATE OR REPLACE PROCEDURE PROC_MPREQ_MPEXAM_DATA_GEN(V_CUR_MONTH  IN VARCHAR2,
 V_EXAM_CYCLE IN VARCHAR2,V_QUARTER_MONTH_STR IN VARCHAR2) is

  CURSOR cur_mp_month IS
  select mp.* from mp mp,mp_settle mps
  where mp.mp_id= mps.mp_id
        and mps.check_cycle='1';

  CURSOR cur_mp_quarter IS
  select mp.* from mp mp,mp_settle mps
  where mp.mp_id= mps.mp_id
        and mps.check_cycle='2'
        and mps.start_month in(Select * From Table (fn_split(V_QUARTER_MONTH_STR,',')));
BEGIN
  -- 如果是 按月份考核的话 则循环 月份数据 否则 季度数据
   if  V_EXAM_CYCLE ='month' then

    for r_mp_month in  cur_mp_month loop
          -- DBMS_OUTPUT.PUT_LINE('mp_id='||r_mp_month.mp_id );
   insert into mp_exam
     (mp_exam_id,
      check_month,
      exam_cycle,
      mp_id,
      exam_status,
      create_date,
      last_update,
      sims_id)
   values
     ( '8' || (select C.CONFIG_VALUE from sims_config c where c.config_name = 'SYSTEM_SIMS_ID') ||
       ltrim(to_char(Seq_MP_exam_Next_Id.Nextval, '0000000000')) ,
      V_CUR_MONTH,
      V_EXAM_CYCLE,
      r_mp_month.mp_id,
      '0',
      sysdate,
      sysdate,
      (select C.CONFIG_VALUE from sims_config c where c.config_name = 'SYSTEM_SIMS_ID')
      );
      end loop;

   else

    for r_mp_quarter in  cur_mp_quarter loop
          -- DBMS_OUTPUT.PUT_LINE('mp_id='||r_mp_quarter.mp_id );

           insert into mp_exam
     (mp_exam_id,
      check_month,
      exam_cycle,
      mp_id,
      exam_status,
      create_date,
      last_update,
      sims_id)
   values
     ( '8' || (select C.CONFIG_VALUE from sims_config c where c.config_name = 'SYSTEM_SIMS_ID') ||
       ltrim(to_char(Seq_MP_exam_Next_Id.Nextval, '0000000000')) ,
      V_CUR_MONTH,
      V_EXAM_CYCLE,
      r_mp_quarter.mp_id,
      '0',
      sysdate,
      sysdate,
      (select C.CONFIG_VALUE from sims_config c where c.config_name = 'SYSTEM_SIMS_ID')
      );

      end loop;


    end if;


END;

例2：

--梦网SP信用积分初始化脚本
declare
  rang_80_100 number(6);--80到100分
  rang_60_80  number(6);--60到80分
  rang_0_60   number(6);--0到60分
  rang_0      number(6);--0分以下
  group_flag_80_100  number(10);--判断标识
  group_flag_60_80  number(10);--判断标识
  group_flag_0_60  number(10);--判断标识
  group_flag_0  number(10);--判断标识
  cursor cur_checkMonth is
       
   select to_char(add_months(sysdate,-1),'yyyymm') as checkMonth from dual
          
          union
          select to_char(add_months(sysdate,-2),'yyyymm') as checkMonth from dual
          union
          select to_char(add_months(sysdate,-3),'yyyymm') as checkMonth from dual
          
          union
          select to_char(add_months(sysdate,-4),'yyyymm') as checkMonth from dual
          union
          select to_char(add_months(sysdate,-5),'yyyymm') as checkMonth from dual
          union
          select to_char(add_months(sysdate,-6),'yyyymm') as checkMonth from dual
          union
          select to_char(add_months(sysdate,-7),'yyyymm') as checkMonth from dual
          union
          select to_char(add_months(sysdate,-8),'yyyymm') as checkMonth from dual
          union
          select to_char(add_months(sysdate,-9),'yyyymm') as checkMonth from dual
          union
          select to_char(add_months(sysdate,-10),'yyyymm') as checkMonth from dual
          union
          select to_char(add_months(sysdate,-11),'yyyymm') as checkMonth from dual
          union
          select to_char(add_months(sysdate,-12),'yyyymm') as checkMonth from dual
          ;
  cursor cur_spTypeId is
          select '2' as spTypeId from dual
          union all
          select '4' as spTypeId from dual
          union all
          select '9' as spTypeId from dual ;
  cursor cur_simsId is
          select sims_id as simsId from sims;   
 
  begin
    for checkMonth in cur_checkMonth
      loop
        for spTypeId in  cur_spTypeId
          loop
            for simsId in  cur_simsId
              loop
              -- 80到100分
              select count(*)  into group_flag_80_100
                  from v_sp_credit t
                 where t.CREDIT_SCORE > 80
                   and t.CHECK_MONTH = checkMonth.checkMonth and t.sp_type_id = spTypeId.spTypeId and sims_id = simsId.simsId;
              if  group_flag_80_100>0 then   
              select count(*)  into rang_80_100
                  from v_sp_credit t
                 where t.CREDIT_SCORE > 80
                   and t.CHECK_MONTH = checkMonth.checkMonth and t.sp_type_id = spTypeId.spTypeId and sims_id = simsId.simsId
                   group by t.sp_type_id,t.CHECK_MONTH,t.SIMS_ID;
              else rang_80_100:=0;     
              end if;  
              -- 60到80分
              select count(*)  into group_flag_60_80
                  from v_sp_credit t
                 where  t.CREDIT_SCORE > 60
                   and t.CREDIT_SCORE <= 80
                   and t.CHECK_MONTH = checkMonth.checkMonth and t.sp_type_id = spTypeId.spTypeId and sims_id = simsId.simsId;
              if group_flag_60_80>0 then          
              select count(*)  into rang_60_80
                  from v_sp_credit t
                 where  t.CREDIT_SCORE > 60
                   and t.CREDIT_SCORE <= 80
                   and t.CHECK_MONTH = checkMonth.checkMonth and t.sp_type_id = spTypeId.spTypeId and sims_id = simsId.simsId
                   group by t.sp_type_id,t.CHECK_MONTH,t.SIMS_ID;
              else rang_60_80:=0;     
              end if;     
              -- 0到60分
              select count(*)  into group_flag_0_60
                  from v_sp_credit t
                 where  t.CREDIT_SCORE > 0
                   and t.CREDIT_SCORE <= 60
                   and t.CHECK_MONTH = checkMonth.checkMonth and t.sp_type_id = spTypeId.spTypeId and sims_id = simsId.simsId;
              if group_flag_0_60>0 then
              select count(*)  into rang_0_60
                  from v_sp_credit t
                 where  t.CREDIT_SCORE > 0
                   and t.CREDIT_SCORE <= 60
                   and t.CHECK_MONTH = checkMonth.checkMonth and t.sp_type_id = spTypeId.spTypeId and sims_id = simsId.simsId
                   group by t.sp_type_id,t.CHECK_MONTH,t.SIMS_ID;  
              else rang_0_60:=0;         
              end if;     
              -- 0分以下
              select count(*)  into group_flag_0
                  from v_sp_credit t
                 where  t.CREDIT_SCORE <= 0
                   and t.CHECK_MONTH = checkMonth.checkMonth and t.sp_type_id = spTypeId.spTypeId and sims_id = simsId.simsId;    
              if group_flag_0>0 then     
              select count(*)  into rang_0
                  from v_sp_credit t
                 where  t.CREDIT_SCORE <= 0
                   and t.CHECK_MONTH = checkMonth.checkMonth and t.sp_type_id = spTypeId.spTypeId and sims_id = simsId.simsId
                   group by t.sp_type_id,t.CHECK_MONTH,t.SIMS_ID;
              else rang_0:=0;      
              end if;    
              insert into COOPERATE_CREDIT_M
              (
                CHECK_MONTH,
                SP_TYPE_ID,
                SIMS_ID,
                rang_80_100,
                rang_60_80,
                rang_0_60,
                rang_0,
                Report_day
              )
              values
              (
                checkMonth.checkMonth,
                spTypeId.spTypeId,
                simsId.simsId,
                rang_80_100,
                rang_60_80,
                rang_0_60,
                rang_0,
                sysdate
              );               
      end loop;
     end loop;
    end loop;
    --增加异常处理 如果没数据，在select into时会包 no_data_found错误
    /*
    EXCEPTION
     WHEN NO_DATA_FOUND THEN
        NULL;
    */    
  end;