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;